この記事は 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 でもそれに従った表記になっているためです。
