Drillbit が使用するメモリサイズ

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

Drill クラスタを構築する場合、各ノードで Drillbit という Java プロセスを立ち上げます。Drillbit は、ノードに常駐するデーモンプロセスとしてクラスタ全体で協調して動作することで、クエリを効率的に並列処理します（ちなみに Embedded モードでも、Drillbit が1つだけローカルで起動します）。

Drillbit は Java プロセスなので当然ながら Java ヒープを確保して使用しますが、Java ヒープとは別にダイレクトメモリを確保して Drillbit が直接その内部を管理しています。クエリの処理対象のデータはダイレクトメモリ上に展開して保持され、処理はできる限りオンメモリで行うことでパフォーマンスの最大化が図られます。

MapReduce などとは異なり、クエリ処理の中間でメモリ上のデータをディスクにすべて書き出すということはありませんが、もしダイレクトメモリ上にすべてのデータが載り切らない場合には、一部のデータをディスク上に一時的に退避させることがあり、その際にはディスクアクセスが発生します。このため、ダイレクトメモリのサイズはクエリの実行パフォーマンスを大きく左右する要素です。

Drillbit が使用するメモリ設定は、Drill をインストールしたディレクトリの conf/drill-env.sh に書かれています。

$ cat apache-drill-1.3.0/conf/drill-env.sh
...
DRILL_MAX_DIRECT_MEMORY="8G"
DRILL_MAX_HEAP="4G"
...

DRILL_MAX_HEAP は Java の起動オプション -Xmx に渡される Java ヒープの最大サイズで、デフォルトで 4GB に設定されています。処理対象のデータをヒープに置くことはないため、ほとんどのケースでは変更する必要はないでしょう。

一方、DRILL_MAX_DIRECT_MEMORY はダイレクトメモリの最大値です（デフォルト 8GB）。ノード上の利用可能なメモリをできるだけ多く割り当てることで、パフォーマンスを最大化することができます。

ただし、クラスタノード上で他のプロセスが動作している場合には、設定値に注意してください。例えば Hadoop クラスタでは、HDFS や HBase などのデータストアや、YARN フレームワークを動作させているのが一般的です。Drill 1.3 の時点では、まだ Drill は YARN に対応していないため、Drill のメモリの割り当てと YARN のメモリ割り当ては、合計使用量が物理メモリ容量を超えないように別々に固定で設定しておく必要があります。例えば、64GB メモリのマシンがあった場合、YARN で 32GB、HDFS で 16GB を確保した場合は、Drill で利用可能なメモリを 12GB 程度に抑えておく必要があります。

クエリ毎のリソース制御について

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

Drill クラスタを構築して、ある程度規模の大きい SQL クエリ基盤を運用する場合、普通は複数のユーザーや複数のアプリケーションで Drill クラスタを共有する使い方をすると思います。その時に課題となるのは、個々のクエリが使うリソースをどのように制御できるかというところです。本記事では、Drill が備えるリソース制御のオプションについて見てみましょう。

まず同時に実行可能なクエリの上限について。デフォルトでは同時に実行可能なクエリの数は無制限ですが、むやみに同時実行数を増やしていくとリソースの競合が激しくなり、全体的なパフォーマンスが低下します。これを防ぐため、Drill ではクエリの「キュー」を用意し、設定した上限までのクエリをキューから取り出して同時実行するしくみがあります。

Enabling Query Queuing
https://drill.apache.org/docs/enabling-query-queuing/

exec.queue.enable プロパティを true にすると、キューが有効になります。Drill では、「large」キューと「small」キューの2つのキューが用意され、それぞれ個別に同時実行数を設定することが可能です。投入したクエリがどちらのキューに入るかは、推定される処理件数のしきい値 exec.queue.threshold プロパティによって決まります。

プロパティ	説明	デフォルト値
exec.queue.enable	キューを有効にするかどうか	false
exec.queue.large	large キューに入ったクエリの同時実行数	10
exec.queue.small	small キューに入ったクエリの同時実行数	100
exec.queue.threshold	クエリの推定される処理件数がこの値より大きければ large キュー、小さければ small キューに投入される	30000000
exec.queue.timeout_millis	クエリがキューに入ってからこの時間が経過すると失敗する（ミリ秒）	300000

また、個々のクエリ実行の並列度についても調整可能なオプションがいくつかあります。

Configuring Resources for a Shared Drillbit: Configuring Parallelization
https://drill.apache.org/docs/configuring-resources-for-a-shared-drillbit/#configuring-parallelization

プロパティ planner.width.max_per_node はクエリあたりの1ノード（クラスタを構成するマシン1台）で処理できる並列度の最大値です。例えばフィルタリングの処理は、マルチコアマシンでは複数スレッドを使って並列に実行することで効率的に処理を行えます。この設定値はクエリあたりの上限ですが、これとは別に、すべてのクエリの合計の1ノードあたりの並列度は「ノード上のアクティブな Drillbit の数 * ノードのコア数 (Hyper-threadを考慮) * 0.7」を超えることはありません。

プロパティ planner.width.max_per_query はクエリあたりの処理の並列度の最大値です。これはクラスタ全体での並列度になります。実際には、(planner.width.max_per_node * ノード数) と planner.width.max_per_query のどちらか小さい値が、クエリあたりの最大並列度になります。

プロパティ	説明	デフォルト値
planner.width.max_per_node	クエリあたりの1ノードで処理できる並列度の最大値	3
planner.width.max_per_query	クエリあたりの処理の並列度の最大値	1000

さらに、planner.memory.max_query_memory_per_node プロパティでノード単位で各クエリが利用できるメモリの最大値を指定することが可能です。

Configuring Drill Memory
https://drill.apache.org/docs/configuring-drill-memory/

Drill はクエリプランの実行に必要なメモリが足りないことが推定される場合、少ないメモリでも実行可能なプランに作り直す場合があります。また、ウィンドウ関数のような外部ソートのための大量のメモリを必要とするクエリでエラーが起きるような場合、この設定値を大きくすることでエラーを回避することができる可能性があります。

プロパティ	説明	デフォルト値
planner.memory.max_query_memory_per_node	ノード単位で各クエリが利用できるメモリの最大値（Byte）	2147483648

上記のプロパティはシステム全体でもセッション単位でも指定できます。例えばシステム全体で指定する場合は

0: jdbc:drill:zk=local> ALTER SYSTEM SET `exec.queue.enable` = true;

セッション単位で指定する場合は

0: jdbc:drill:zk=local> ALTER SESSION SET `exec.queue.enable` = true;

を実行します。

CSV ファイルを Parquet ファイルに変換してクエリを高速化

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

Apache Drill では Apache Parquet という大規模データの分析に適したデータフォーマットを利用することができます。Row-oriented フォーマットにカテゴリ分けされる CSV、TSV といったテキストファイルや伝統的なリレーショナルデータベースのテーブルでは、データは行方向に沿って格納されますが、Column-oriented フォーマットにカテゴリ分けされる Parquet、ORC といった形式のデータは列方向に沿って格納されます。

データ分析用途には Column-oriented フォーマットが向いているとよく言われますが、データ分析では特定の列の値を集計したり、特定の列の条件を元にフィルタリングやジョインが行われることが多いため、列方向にデータが連続して格納されていると必要なデータのみを効率的に読み込むことができる、ということがその理由です。また、列方向には同じ種類のデータが並んでいるため、圧縮アルゴリズムも効きやすくなります。

必要なスキャン範囲を絞ることを目的として列方向にデータを分割して格納することを垂直パーティショニングと言いますが、Parquet ではさらに内部で行方向に一定の単位でメタ情報を保持することによりスキャン範囲を限定できる水平パーティショニングの機能も持っています。これらの組み合わせにより、クエリの高速化を実現しています。

さて、Drill で Parquet を扱うための方法ですが、ここからダウンロードした次のような CSV ファイルがあるとします。

$ cat /tmp/MonthlyPassengerData_200507_to_201506.csv
Activity Period,Operating Airline,Operating Airline IATA Code,Published Airline,Published Airline IATA Code,GEO Summary,GEO Region,Activity Type Code,Price Category Code,Terminal,Boarding Area,Passenger Count
200507,ATA Airlines,TZ,ATA Airlines,TZ,Domestic,US,Deplaned,Low Fare,Terminal 1,B,27271
200507,ATA Airlines,TZ,ATA Airlines,TZ,Domestic,US,Enplaned,Low Fare,Terminal 1,B,29131
200507,ATA Airlines,TZ,ATA Airlines,TZ,Domestic,US,Thru / Transit,Low Fare,Terminal 1,B,5415
200507,Air Canada ,AC,Air Canada ,AC,International,Canada,Deplaned,Other,Terminal 1,B,35156

まずヘッダ行の空白をアンダースコアで置換し（Drill は空白を含むカラム名を扱えない）、tr で余分な改行コード(CR)を取り除いた後で、ヘッダ行をカラム名として利用するために、拡張子を csvh に変更します。

$ sed -e '1s/ /_/g' /tmp/MonthlyPassengerData_200507_to_201506.csv | tr -d '\r' > /tmp/MonthlyPassengerData_200507_to_201506.csvh

そして、これを Parquet ファイルに変換するには、store.format プロパティに parquet を指定した状態で  CREATE TABLE AS SELECT（通称 CTAS）を使用します。

$ apache-drill-1.3.0/bin/drill-embedded
0: jdbc:drill:zk=local> ALTER SESSION SET `store.format` = 'parquet';
+-------+------------------------+
|  ok   |        summary         |
+-------+------------------------+
| true  | store.format updated.  |
+-------+------------------------+
1 row selected (0.196 seconds)
0: jdbc:drill:zk=local> CREATE TABLE dfs.tmp.`/airport_data/` AS
SELECT * FROM dfs.`/tmp/MonthlyPassengerData_200507_to_201506.csvh`
+-----------+----------------------------+
| Fragment  | Number of records written  |
+-----------+----------------------------+
| 0_0       | 13901                      |
+-----------+----------------------------+
1 row selected (1.111 seconds)

これだけ。簡単ですね。これで、/tmp/airport_data/ ディレクトリの下に Parquet フォーマットのファイルが保存されます。クエリを実行するには、データソースとしてこのディレクトリを指定します。

0: jdbc:drill:zk=local> SELECT Activity_Period, Operating_Airline, Passenger_Count
. . . . . . . . . . . > FROM dfs.tmp.`/airport_data/`
. . . . . . . . . . . > WHERE CAST(Passenger_Count AS INT) < 5;
+------------------+-----------------------------------+------------------+
| Activity_Period  |         Operating_Airline         | Passenger_Count  |
+------------------+-----------------------------------+------------------+
| 200610           | United Airlines - Pre 07/01/2013  | 2                |
| 200611           | Ameriflight                       | 1                |
| 200611           | Ameriflight                       | 1                |
...

あと、上記の CTAS クエリによる変換だと、元の CSV ファイルから読み込む時にすべてのカラムが VARCHAR 型として扱われてしまうので、後で直接集計などをしたい場合には、次のように Parquet に変換するタイミングでカラムごとにデータ型を指定しておきましょう。

0: jdbc:drill:zk=local> CREATE TABLE dfs.tmp.`/airport_data/` AS SELECT
. . . . . . . . . . . >   CAST(SUBSTR(Activity_Period, 1, 4) AS INT) AS `Year`,
. . . . . . . . . . . >   CAST(SUBSTR(Activity_Period, 5, 2) AS INT) AS `Month`,
. . . . . . . . . . . >   Operating_Airline,
. . . . . . . . . . . >   Operating_Airline_IATA_Code,
. . . . . . . . . . . >   Published_Airline,
. . . . . . . . . . . >   Published_Airline_IATA_Code,
. . . . . . . . . . . >   GEO_Summary,
. . . . . . . . . . . >   GEO_Region,
. . . . . . . . . . . >   Activity_Type_Code,
. . . . . . . . . . . >   Price_Category_Code,
. . . . . . . . . . . >   Terminal,
. . . . . . . . . . . >   Boarding_Area,
. . . . . . . . . . . >   CAST(Passenger_Count AS INT) AS Passenger_Count
. . . . . . . . . . . > FROM dfs.`/tmp/MonthlyPassengerData_200507_to_201506.csvh`;

改行コード (CRLF) に注意

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

改行コードの取り扱いは、現時点での Drill の注意事項の一つです。Linux/Mac の環境で生成されたテキストデータであれば問題は起きませんが、Windows 環境で生成されたテキストデータ（もしくは Windows 環境で処理されることを前提としたデータ）を処理するときに、問題になるケースがあります。

例えば、Windows マシンで次のような CSV ファイルを作成します。

1,尾形金弥,オガタキンヤ,0582958162
2,三橋佑奈,ミハシユウナ,0987381855
3,鮫島憲司,サメジマケンジ,0982675562
4,宮野久寛,ミヤノヒサヒロ,0477707528

これを拡張子 .csv をつけて Drill で SELECT * してみると次のようになります。

0: jdbc:drill:zk=local> SELECT * FROM dfs.`/tmp/phone.csv`;
+----------------------------------------+
|                columns                 |
+----------------------------------------+
| ["1","尾形金弥","オガタキンヤ","0582958162\r"]   |
| ["2","三橋佑奈","ミハシユウナ","0987381855\r"]   |
| ["3","鮫島憲司","サメジマケンジ","0982675562\r"]  |
| ["4","宮野久寛","ミヤノヒサヒロ","0477707528\r"]  |
+----------------------------------------+
5 rows selected (0.325 seconds)

各行の配列の最後の要素に改行コード「\r」が含まれてしまっているのが見えます。さらに、配列要素をカラムに分解して表示してみると、

0: jdbc:drill:zk=local> SELECT columns[0] AS id,
. . . . . . . . . . . >        columns[1] AS name,
. . . . . . . . . . . >        columns[2] AS kana,
. . . . . . . . . . . >        columns[3] AS phone
. . . . . . . . . . . > FROM dfs.`/tmp/phone.csv`;
+-----+-------+-----------+--------------+
| id  | name  |   kana    |    phone     |
+-----+-------+-----------+--------------+
  |   | 尾形金弥  | オガタキンヤ    | 0582958162 ← 右端の「|」が無くなって左端のカラムを上書きしている
  |   | 三橋佑奈  | ミハシユウナ    | 0987381855
  |   | 鮫島憲司  | サメジマケンジ   | 0982675562
  |   | 宮野久寛  | ミヤノヒサヒロ   | 0477707528
+-----+-------+-----------+--------------+
5 rows selected (0.667 seconds)

このように改行(CR)のせいで表示がおかしくなる上、次のようなクエリは期待した結果を返さなくなってしまいます。

0: jdbc:drill:zk=local> SELECT columns[1] AS name
. . . . . . . . . . . > FROM dfs.`/tmp/phone.csv`
. . . . . . . . . . . > WHERE columns[3] LIKE '0987381855%';
+-------+
| name  |
+-------+
+-------+
No rows selected (0.373 seconds)

原因は、Windows 環境では改行コードが「\r\n」にもかかわらず、Drill が「\n」のみを改行コードとして扱っているためです。これを回避する単純な方法は tr の利用でしょう。

$ tr -d '\r' < /tmp/phone.csv > /tmp/phone_modified.csv

Drill コミュニティでもこの問題は認識されていて、修正されるまでにはそれほど長くはかからないはずです。

[DRILL-3149] TextReader should support multibyte line delimiters

[DRILL-3726] Drill is not properly interpreting CRLF (0d0a). CR gets read as content.

ただ、それまではちょっと注意が必要なので、念のため。

MSX-BASIC の1画面プログラミング技術

この記事は MSX Advent Calendar 2015 の6日目の記事です。

1980年代に MSX・FAN という雑誌にファンダムという MSX-BASIC プログラム投稿コーナーがあり、私も投稿して腕を磨いていたものです。で、ファンダムでは1画面プログラム部門、N画面プログラム部門、10画面プログラム部門、というようにプログラムの長さに応じてカテゴリ分けがありました。

ここで1画面というのは、MSX のスクリーンモード0、つまり幅40文字、高さ24文字のテキスト表示で1画面に収まる分量のコードのことです。当然ながら短いコードで多くの機能や面白さを実現できれば評価が高まるわけで、自然と1画面プログラム部門などは極限までのコード削減テクニックが競われておりました。それは、スパゲティプログラムの奨励事例 - スパゲティプログラム - Wikipedia に載ってしまうくらいに。

当時私は中学生でしたが、人をあっと言わせたい一心でわけもわからぬままコード圧縮技術の探求に邁進したものです。本記事では、当時から伝わる伝統芸能の保存のため（笑）、その数々のテクニックを記録しておこうと思います。

初級

変数名を1文字にする

まあ一番初めに思いつきますね。これはその後 iアプリ（携帯向け Java）が登場した時、10KBという制限の中にいかにプログラムを詰め込むかという課題に対し、1文字クラス名、1文字メソッド名を使用するというテクニックに引き継がれるわけです（笑）。

行番号を1文字にする

BASIC のプログラムは伝統的に 10 から始まって 20, 30, ... と増やしていくものですが、行番号に2文字使うのは無駄なので1文字に。

空白は全て削除

例えば FOR 文は「FOR I = 0 TO 100」と書くのが可読性が高いコードでしょうが、ここは黙って「FORI=0TO100」。FOR と 変数 I がくっついて心配になりますが、FORI という命令はないので全然平気。MSX-BASIC では空白はすべて削除することが可能です。ただし「X OR Y」というようなコードを「XORY」としてしまうと予約語の「XOR」と解釈されてエラーとなってしまう罠もあるので注意です。

演算子の優先順位を把握して括弧を削減

加算乗除だけでなく、関係演算子、論理演算子、剰余など、演算子の優先順位を正確に把握すれば不要な括弧を取り除くことができます。

リプレイはRUN

ゲームなどで始めからやり直す場合の処理は面倒なので、単純に RUN を実行。

中級

1行の文字数を40文字の倍数に近づける

行の途中で改行してしまうとその後の空白の分が無駄になってしまうので、画面の右端まで使い切るように命令をコロン（：）でつなげてコードを埋めます。さらに、改行してしまうと行番号＋空白の2文字分も無駄になるので、ジャンプ命令などが不要であれば、1行の上限255文字に一番近い40の倍数である240文字に近づけておきたいところです。

比較演算子の結果を制御または計算に利用

MSX-BASIC では比較演算子の結果は真なら -1、偽なら 0 が返るので例えば

IF A > 5 THEN B = 0 ELSE B = 12

なら次のように書き換えることが可能です。

B=-(A<=5)*12

また、条件付きループのコードは次のように書けます。

FORI=0TO1:（何かの処理）:I=-(A=1):NEXT

スプライトパターンの定義

スプライトの定義は普通

10 FOR I = 0 TO 7
20 A$ = A$ + CHR$(VAL("&H" + MID$("21D4D4D427B2AAB3", I*2 + 1, 2)))
30 NEXT
40 SPRITE$(0) = A$

なんて書くと、ビットパターンを16進数で直感的に表せるので便利なわけです。あ、現代プログラマはあまり慣れていないかもしれませんが、8ビットマシンプログラマはビットパターンを高速に16進数に置き換えることができるのです（笑）。ところが1画面プログラムでは文字数がもったいないのでこうです。たまたまビットパターンの文字が制御文字になってしまうと使えないのが難点ですが。

SPRITE$(0)="!ﾔﾔﾔ'ｲｪｳ"

NEXTの変数の省略

NEXTの対象のループ変数は省略。ただし2重ループを閉じる時はNEXTJ,Iのようにまとめると良い場合も。

キーの入力待ち

10 IF STRIG(0) = 0 THEN GOTO 10

によってキーの入力待ちが実現できますが、1行を消費してしまうのがもったいないので次のように行の途中に書けるようにします。

FORI=0TO1:I=-STRIG(0):NEXT

LINE ではなく DRAW

グラフィックモード（SCREEN 2 など）で垂直、水平、斜め45度の直線などを描画する場合は、LINE を使いよりも DRAW を使うことでコードが短くなる場合があります。

LINE(80,100)-(180,100),15

例えば上記のコードは下記のように書き直せます。

DRAW"S4BM80,100C15R100"

命令の省略形を使う

例えば PUT SPRITE 命令の定義は次の通りです。

PUT SRPITE <スプライト面番号>[,{(X,Y)｜STEP(X,Y)}[,<色>[,<スプライトパターン番号>]]]

あるスプライトの色を変えるだけであれば座標は省略可能なので、次のように短縮できます。

PUTSPRITE0,,8

上級

ストライプ操作にVPOKEを使う

スプライトを消すために、例えば画面外の位置にスプライト表示を行うコードは次のような感じです（スクリーンモード1〜3の場合）。

PUT SPRITE 0, (0, 192)

が、VPOKE を使って VRAM の内容を直接書き換えることでコードを短くすることができます。スプライト#0 の Y座標を格納するVRAMアドレスは 6912 番地なので、こんな感じ。

VPOKE6912,192

この他にも、画面表示関係は VPOKE の使用により効率的なコードになるケースが多く、応用が効きます。

エスケープシーケンスの利用

スクリーンモード1などで任意の文字座標に文字列を表示する場合のコードはこんな感じです。

10 LOCATE 8,10
20 PRINT"PRESS ANY KEY"

これはエスケープシーケンス CHR$(27) + "Y" + CHR$(32 + y) + CHR$(32 + x) を用いてこう書けます。多用する場合は E$ = CHR$(27) のように変数に格納しておけばさらに短くなるでしょう

PRINTCHR$(27)+"Y*(PRESS ANY KEY"

表示する文字が1文字だけであれば、上記の VPOKE で VRAM を直接書き換える方法も効果的です。

変態級

マシン語直書き

BASIC プログラムにもかかわらず、コメント部分に直接バイナリのマシン語コードを記述するという荒技です。BASIC のコードはマシン語コードをロードする必要最小限のみ。まったく理解することが不可能です。しかしながら、比較的大規模なコードを非常に高速に実行できるため、1画面プログラムとは思えないほどの挙動を示します。下記のコードは、BASIC プログラムがメモリの &H8000 番地から格納され、最初のコメント（' 以降）の位置のバイナリが &H801C 番地に来ることを見越して、USR 関数のアドレスを書き換えて直接呼び出しています。

1 CLEAR9,&HD000:DEFUSR=&H801C:A=USR(0)'ﾝ!､♠!0ﾊｯGゆ>7ﾖ0W7まｸ9#ｪ...

コーディングゼロでエンディングメッセージを実現

私が昔「8192階建ての塔」という縦スクロールジャンプアクションゲームを作ったときの話。塔を登っていくとどこまで進んだかをカウントしている変数の値が増え、プレーヤーはそれをスコアとして競います。サイズに余裕がないのでゴールに到達したときの画面表示もできません。が、カウント変数が32767を超えたとき（階数は4で割った値なので8192階に達したとき）に Illegal function call エラーが起きることに気づきました。ならばそれをエンディングとして使用すればよい、ということでタイトルが決定しました。なんつって。

おまけ

参考までに1画面プログラム「8192階建ての塔」のコードを載せておきます。40x24文字びっしり埋まってます。

1 SCREEN1,1,0:COLOR15,0,0:WIDTH24:KEYOFF
:DEFINTA-Z:DEFFNA=VPEEK(6208+X¥8+(Y¥8)*3
2)ORVPEEK(6209+(X+7)¥8+(Y¥8)*32):FORI=1T
O32:VPOKE14335+I,ASC(MID$("jjｮ-kｪ}まjj.jｪ
jFfjj/-はl ｱjj.jljFJ",I,1))-46AND255:NEXT
2 A=RND(-9):VPOKE8208,50:FORI=0TO20:VPOK
E6211+i*32,133:VPOKE6236+I*32,133:NEXT:F
ORI=0TO9:LOCATERND(1)*19,2+I*2:PRINT"●●●
●●●":NEXT:LOCATE0,22:PRINTSTRING$(24,133
);:X=32:Y=160:A=1:F=1:SOUND1,0:SOUND7,62
3 X=X+A*4-2:P=PXOR1:GOSUB6:IFFNA=32THENY
=Y+8:SOUND8,0:GOTO3ELSEB=S:S=STRIG(0):IF
STHENJ=JMOD12+1:SOUND0,99-J:SOUND8,15:GO
TO3ELSESOUND8,0:IFB=0ANDS=0THENJ=0:GOTO3
4 X=X+A*4-2:Y=Y-J:J=J-1:GOSUB6:IFJ<0ANDF
NA<>32THENJ=0:Y=(Y¥8)*8:GOTO3ELSEIFY>64T
HEN4ELSEF=F+1:FORI=0TO3:C=C+1:LOCATE0,2:
PRINTCHR$(27)+"L":Y=Y+8:IFCMOD2=0THENLOC
ATERND(1)*9+(C/2MOD2)*10,2:PRINT"●●●●●●"
5 GOSUB6:NEXT:LOCATE10,0:PRINTF"F":GOTO4
6 PUTSPRITE0,(X,Y),7,A*2+P:A=AXOR-(X=32O
RX=208):IFY<176THENRETURNELSEPLAY"SM999L
64DD-C":FORI=0TO20:LOCATE,2:PRINTCHR$(27
)+"M":NEXT:FORI=0TO1:I=STICK(0):NEXT:RUN

注：2行目および4行目の「●」（文字コード133）は、GRAPHキー＋「]」で入力

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

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

UNION 型: データ型が混在するカラムのサポート

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

少し前の記事で、一つのカラムにデータ型が混在したデータを読むときの注意点を説明しました。

その後リリースされた Drill 1.3 で、[DRILL-3229] Create a new EmbeddedVector にて改良が進行中のコードが取り込まれたことにより、データ型が混在するカラムの取り扱いができるようになりました。具体的には、UNION 型というデータ型が新たに追加され、個々のフィールドごとに異なるデータ型を内部で保持できるようになっています。

以前の記事同様、次のようなデータを用意します（以前の記事の時のデータとはほんの少し異なりますが、その理由は後述）。

$ cat /tmp/sensor.json
[
  {"sensor_id":15, "timestamp":"2015-10-29 08:00:00.004", "temperature":14.8},
  {"sensor_id":15, "timestamp":"2015-10-29 08:05:00.011", "temperature":14.9},
  {"sensor_id":15, "timestamp":"2015-10-29 08:10:00.002", "temperature":15},
  {"sensor_id":15, "timestamp":"2015-10-29 08:15:00.012", "temperature":15.2},
  {"sensor_id":15, "timestamp":"2015-10-29 08:20:00.009", "temperature":15.3}
]

UNION 型を有効にするために、プロパティ exec.enable_union_type を true にします。

$ apache-drill-1.3.0/bin/drill-embedded
0: jdbc:drill:zk=local> ALTER SESSION SET `exec.enable_union_type` = true;

そして SELECT * してみると、エラーなくちゃんとクエリが実行されることがわかります。このデータの temperature カラムは小数点を含むデータと含まないデータがあるので DOUBLE 型と INT 型が混在しているのですが、UNION 型を有効にすることで両方の型を格納できるようになっています。

0: jdbc:drill:zk=local> SELECT * FROM dfs.`/tmp/sensor.json`;
+------------+--------------------------+--------------+
| sensor_id  |        timestamp         | temperature  |
+------------+--------------------------+--------------+
| 15         | 2015-10-29 08:00:00.004  | 14.8         |
| 15         | 2015-10-29 08:05:00.011  | 14.9         |
| 15         | 2015-10-29 08:10:00.002  | 15           |
| 15         | 2015-10-29 08:15:00.012  | 15.2         |
| 15         | 2015-10-29 08:20:00.009  | 15.3         |
+------------+--------------------------+--------------+
5 rows selected (0.17 seconds)

ただし temperature カラムに集計関数を使おうとするとエラーがでます。今のところ、集計関数は UNION 型をサポートしていないためです。

0: jdbc:drill:zk=local> SELECT
. . . . . . . . . . . >   sensor_id,
. . . . . . . . . . . >   AVG(temperature) temperature
. . . . . . . . . . . > FROM dfs.`/tmp/sensor.json`
. . . . . . . . . . . > GROUP BY sensor_id;
Error: UNSUPPORTED_OPERATION ERROR: Union type not supported in aggregate functions

Fragment 0:0

[Error Id: 4186f733-5b00-415b-ab0a-f85eb070d2bd on 192.168.111.4:31010] (state=,code=0)

このような場合は以前の記事同様 CAST を使ってやれば OK です。

0: jdbc:drill:zk=local> SELECT
. . . . . . . . . . . >   sensor_id,
. . . . . . . . . . . >   AVG(CAST(temperature AS DOUBLE)) temperature
. . . . . . . . . . . > FROM dfs.`/tmp/sensor.json`
. . . . . . . . . . . > GROUP BY sensor_id;
+------------+---------------------+
| sensor_id  |     temperature     |
+------------+---------------------+
| 15         | 15.040000000000001  |
+------------+---------------------+
1 row selected (0.339 seconds)

ところで、冒頭に述べたように、以前の記事と完全に同じデータを使わなかった理由は、現時点では UNION 型は FLATTEN 関数をサポートしていないためです。下のような "data" をキーとする配列があった場合、FLATTEN(data) でデータが展開できることを期待したいところですが、UNION 型が扱えないのでエラーになります。[DRILL-3229] Create a new EmbeddedVector はまだ「IN PROGRESS」なステータスなので、完了すればちゃんと動くようになるでしょう。

{
  "data":[
    {"sensor_id":15, "timestamp":"2015-10-29 08:00:00.004", "temperature":14.8},
    {"sensor_id":15, "timestamp":"2015-10-29 08:05:00.011", "temperature":14.9},
    ...
  ]
}

ちなみに、[DRILL-3229] Create a new EmbeddedVector はより大きな課題である [DRILL-3228] Implement Embedded Type の一部です。UNION 型に関連する機能が揃うバージョン 1.4 が出るころには、より使いやすくなっているはずです。