読者です 読者をやめる 読者になる 読者になる

テキストファイルとNULLの扱い

Drill

この記事は 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)