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