この記事は Apache Drill Advent Calendar 2015 の14日目の記事です。
CSV 形式などのテキストファイルでどのように NULL を表現するかは、CSV を出力する RDBMS やアプリケーション毎に異なっているので結構悩みのタネですね。
- Oracle（SPOOL を使用）: 引用符のない空文字
- SQL Server: 引用符のない空文字
- DB2: 引用符のない空文字
- MySQL: \N
- PostgreSQL: \N（テキスト）、引用符のない空文字（CSV）
デフォルトでは上記のような感じだと思いますが、これってオプションで形式を変えることもできますし、この他にも色々なアプリケーションがあるので、その都度表現形式を確認して処理をする必要があります。
さて、Drill では CSV 形式などのテキストファイルをデータソースとする場合は、すべて可変長文字列型として扱われます。なので、次のようなファイルがあったとしたら、
$ cat test.csv "Japan","14","0" "United States","23","3" "China",\N,"5" "France","16",
Drill で SELECT した結果は次のようになります。\N も引用符のない空文字も、NULL ではなく空文字列として扱われているのがわかります。
$ apache-drill-1.3.0/bin/drill-embedded 0: jdbc:drill:zk=local> SELECT columns[0], columns[1], columns[2] FROM dfs.`/tmp/test.csv`; +----------------+---------+---------+ | EXPR$0 | EXPR$1 | EXPR$2 | +----------------+---------+---------+ | Japan | 14 | 0 | | United States | 23 | 3 | | China | \N | 5 | | France | 16 | | +----------------+---------+---------+ 4 rows selected (0.093 seconds)
では、例えば引用符のない空文字を NULL として扱いたい場合はどうすればよいでしょうか。
方法1: CASE を使う
CASE を使用して、空文字であれば NULL に置き換えます。この方法は NULL を表現するのに他の形式が使われたときにも有効です。あとは、カラム毎に扱いを変えたいときにも柔軟に対応できますね。
0: jdbc:drill:zk=local> SELECT . . . . . . . . . . . > columns[0], . . . . . . . . . . . > CASE WHEN columns[2] = '' THEN NULL ELSE columns[1] END, . . . . . . . . . . . > FROM dfs.`/tmp/test.csv`; +----------------+---------+ | EXPR$0 | EXPR$1 | +----------------+---------+ | Japan | 0 | | United States | 3 | | China | 5 | | France | null | +----------------+---------+ 4 rows selected (0.116 seconds)
方法2: drill.exec.functions.cast_empty_string_to_null プロパティを使う
drill.exec.functions.cast_empty_string_to_null プロパティを true に設定すると、空文字列が「キャストをする時に」NULL として扱われるようになります。キャストをしない場合には、そのまま空文字列として扱われます。
0: jdbc:drill:zk=local> ALTER SYSTEM SET `drill.exec.functions.cast_empty_string_to_null` = true; +-------+----------------------------------------------------------+ | ok | summary | +-------+----------------------------------------------------------+ | true | drill.exec.functions.cast_empty_string_to_null updated. | +-------+----------------------------------------------------------+ 1 row selected (0.08 seconds) 0: jdbc:drill:zk=local> SELECT columns[0], CAST(columns[2] AS INT) FROM dfs.`/tmp/test.csv`; +----------------+---------+ | EXPR$0 | EXPR$1 | +----------------+---------+ | Japan | 0 | | United States | 3 | | China | 5 | | France | null | +----------------+---------+ 4 rows selected (0.139 seconds)
