Help us understand the problem. What is going on with this article?

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
ymir
独自のテクノロジーで、パフォーマンスを追求したメール配信システムを中心にメッセージングソリューションを提供するSaaSベンダーです。
https://www.ymir.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away