Posted at

ClickHouse で null を扱う

More than 1 year has passed since last update.

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