ClickHouse

ClickHouse で null を扱う

ClickHouse は以前は null が扱えず、最近扱えるようになりました。
まだドキュメントなどに十分反映されていないため、使い方についてまとめてみました。

型の宣言・テーブル作成

一般的なDBでは、NULL / NOT NULL で宣言しますが、ClickHouseでは Nullable(元の型名) で宣言します。
次のようになります。

CREATE TABLE test1(
  col1 UInt32,
  col2 Nullable(UInt32),
  col3 String,
  col4 Nullable(String),
  col5 DateTime,
  col6 Nullable(DateTime)
) Engine = MergeTree ORDER BY col1

MergeTreeエンジンの PRIMARY KEY には Nullable なカラムは使用できません。
たとえば上記で ORDER BY col2 はエラーとなります。

NULL値の表現

SQL文の中では、普通に NULL で表記します。

FORMAT句でフォーマットを変更する場合、フォーマットにより表現が異なります。

Pretty、CSV、TSV、XMLの場合は、\N 表記となります。
JSONでは null となります。

コマンドラインでクエリを実行した場合、PrettyCompact フォーマットで表示されるので、結果としては \N 表記になります。

CSV/TSVファイルとしてインポートする場合、NULL値部分には \N を記載する必要があります。
Int 型の場合、空欄にすると、そこには NULL ではなく 0(デフォルト値)が入りますので、注が必要です。

ORDERの順序

ASC/DESC どちらでソートしても、NULL値は常に最後に返されます。

SELECT col1
FROM test2
ORDER BY col1 ASC

┌─col1─┐
   -1 
    1 
   \N 
└──────┘

SELECT col1
FROM test2
ORDER BY col1 DESC

┌─col1─┐
    1 
   -1 
   \N 
└──────┘

JOINの動作

取り出すカラムの型が Nullable ではない場合、OUTER JOIN した時に、対応するレコードがない場合、NULLではなく型のデフォルト値が返ります。
一般的なDBのように、NULL値を得たい場合は、join_use_nulls フラグをセットしてクエリを発行します。

DESCRIBE TABLE test_a

┌─name─┬─type────────────┬─default_type─┬─default_expression─┐
 id    String                                            
 data  Nullable(Int32)                                   
└──────┴─────────────────┴──────────────┴────────────────────┘

DESCRIBE TABLE test_b

┌─name──┬─type───┬─default_type─┬─default_expression─┐
 id     String                                   
 data2  Int32                                    
└───────┴────────┴──────────────┴────────────────────┘

SELECT *
FROM test_a

┌─id─┬─data─┐
 a      1 
 b     \N 
 c      1 
 d     \N 
└────┴──────┘

SELECT *
FROM test_b

┌─id─┬─data2─┐
 a       1 
 b       2 
└────┴───────┘

SELECT
    id,
    data,
    data2
FROM test_a
ANY LEFT JOIN test_b USING (id)

┌─id─┬─data─┬─data2─┐
 a      1      1 
 b     \N      2 
 c      1      0 
 d     \N      0 
└────┴──────┴───────┘

-- id=c,d のレコードは test_b にありませんが、
-- data2 のカラムが 0 になっています。

:) SET join_use_nulls = 1;

SELECT
    id,
    data,
    data2
FROM test_a
ANY LEFT JOIN test_b USING (id)

┌─id─┬─data─┬─data2─┐
 a      1      1 
 b     \N      2 
 c      1     \N 
 d     \N     \N 
└────┴──────┴───────┘

NULL関連の関数

IS NULL / IS NOT NULL

WHERE句などで、IS NULL / IS NOT NULL が利用できます。
使い方は通常のDBと一緒です。

isNull / isNotNull

Nullかどうか0/1で返します。

SELECT isNull(NULL)

┌─isNull(NULL)─┐
            1 
└──────────────┘

SELECT isNull(1)

┌─isNull(1)─┐
         0 
└───────────┘

SELECT isNotNull(NULL)

┌─isNotNull(NULL)─┐
               0 
└─────────────────┘

ifNull / nullIf

ifNull(a,b) は、a が NULL ではない場合 a を返し、a が NULLであれば b を返します。

SELECT ifNull(NULL, 0)

┌─ifNull(NULL, 0)─┐
               0 
└─────────────────┘

SELECT ifNull(3, 0)

┌─ifNull(3, 0)─┐
            3 
└──────────────┘

nullIf(a,b) は、a==bの場合はNULLを返し、それ以外はaを返します。

SELECT nullIf(1, 1)

┌─nullIf(1, 1)─┐
           \N 
└──────────────┘

SELECT nullIf(1, 2)

┌─nullIf(1, 2)─┐
            1 
└──────────────┘

coalesce

coalesce(a,b,c,...) で、指定されたもののうち、NULLではない最初のものを返します。
すべてNULLの場合や、引数がない場合もNULLを返します。

SELECT coalesce(NULL, NULL, 1)

┌─coalesce(NULL, NULL, 1)─┐
                       1 
└─────────────────────────┘

SELECT coalesce(NULL, 1, 2)

┌─coalesce(NULL, 1, 2)─┐
                    1 
└──────────────────────┘

assumeNotNull

assumeNotNull(a) で、aがNULLでないと仮定して、Nullableでない型でデータを返します。
NULLが入っていた場合、Int型なら0に、日付型なら 0000-00-00 などの値になります。

SELECT *
FROM test_null

┌─id─┐
  1 
 \N 
└────┘

SELECT id
FROM test_null
FORMAT TabSeparatedWithNamesAndTypes

id
Nullable(UInt32)
1
\N

SELECT assumeNotNull(id)
FROM test_null
FORMAT TabSeparatedWithNamesAndTypes

assumeNotNull(id)
UInt32
1
0