ネストデータのカラム指定方法について

この記事は Apache Drill Advent Calendar 2015 の5日目の記事です。

通常の SQL はリレーショナルデータを対象とするため、テーブルは行と列からなるフラットな構造です。SELECT 文で特定のカラムの値を取り出すには、単にカラム名を指定するだけです。

しかし、Apache Drill では JSON や Parquet のようなネストデータ(入れ子構造を持つデータ)もクエリの対象にすることができます。その場合、特定の階層の特定のフィールドの値を取り出すにはどのように指定すればよいでしょうか。

サンプルとして次のような JSON 形式のデータを用意します。

$ cat /tmp/donuts.json
{
  "id": "0001",
  "type": "donut",
  "name": "Cake",
  "ppu": 0.55,
  "sales": 35,
  "topping":[
    { "id": "5001", "type": "None" },
    { "id": "5002", "type": "Glazed" },
    { "id": "5005", "type": "Sugar" },
    { "id": "5007", "type": "Powdered Sugar" },
    { "id": "5006", "type": "Chocolate with Sprinkles" },
    { "id": "5003", "type": "Chocolate" },
    { "id": "5004", "type": "Maple" }
  ]
}
{
"id": "0002",
...

トップ階層のデータを SELECT するのは、通常の SQL となんら変わりません。

0: jdbc:drill:zk=local> SELECT id, type, name, ppu
. . . . . . . . . . . > FROM dfs.`/tmp/donuts.json`;
+-------+--------+----------------+-------+
|  id   |  type  |      name      |  ppu  |
+-------+--------+----------------+-------+
| 0001  | donut  | Cake           | 0.55  |
| 0002  | donut  | Raised         | 0.69  |
| 0003  | donut  | Old Fashioned  | 0.55  |
| 0004  | donut  | Filled         | 0.69  |
| 0005  | donut  | Apple Fritter  | 1.0   |
+-------+--------+----------------+-------+
5 rows selected (0.18 seconds)

次に示すのは、「topping」をキーとする配列の中の特定の要素を取り出すクエリです。配列の要素を指定するために要素のインデックスを [ ] で囲って指定しています。ちょっと気をつけて欲しいのは、ここでの配列の要素は複数のキーと値のペアを含む Map データですが、データ全体を1カラムとして表示しており、その下の階層のキーでの分解までは行っていない点です。

0: jdbc:drill:zk=local> SELECT topping[3] AS top
. . . . . . . . . . . > FROM dfs.`/tmp/donuts.json`;
+------------+
|    top     |
+------------+
| {"id":"5007","type":"Powdered Sugar"} |
+------------+
1 row selected (0.137 seconds)

さて、下の階層のキーの値をカラムとして取り出すにはどうすればよいでしょうか。次のクエリは「tapping」配列の要素内部の「id」「type」の値を取り出すクエリです。ドット(.)でキー名をつなげて階層構造を指定しているのがわかります。

0: jdbc:drill:zk=local> SELECT tbl.topping[3].id as record,
. . . . . . . . . . . >        tbl.topping[3].type as first_topping
. . . . . . . . . . . > FROM dfs.`/tmp/donuts.json` as tbl;
+------------+---------------+
|   record   | first_topping |
+------------+---------------+
| 5007       | Powdered Sugar |
+------------+---------------+
1 row selected (0.133 seconds)

ここで一点重要なポイントは、ドットでキー名を連結してカラム名を指定する場合、先頭はテーブル名エイリアスである必要があることです(この例では tbl)。もし tbl が先頭になく、topping[3].id のような指定をしてしまうと、このクエリはエラーになります。標準 SQLカラム名にドットを使用する場合は、テーブル名が含まれている必要があり、Drill でもそれに従った表記になっているためです。