この記事は Apache Drill Advent Calendar 2015 の2日目の記事です。
11月の Tokyo Apache Drill Meetup で出た質問の中に、「CSV ファイルのヘッダ行をカラム名に使いたいが可能か？」というものがありましたが、Drill 1.2 では先頭行をスキップするオプションはあったものの、行の内容をカラム名として使う機能は未実装でした。で、その後リリースされた Drill 1.3 の [DRILL-951] CSV header row should be parsed にて、その機能が追加されたので紹介します。
例えばこんなデータがあるとします。
$ head /tmp/personal_information.csv 連番,氏名,氏名（カタカナ）,性別,電話番号,郵便番号,住所1,住所2,住所3,住所4,住所5,生年月日 1,佐川邦男,サガワクニオ,男,0959408329,852-8007,長崎県,長崎市,江の浦町,2-18,江の浦町スカイ401,1995/03/28 2,松本冨子,マツモトトミコ,女,0957833608,855-0882,長崎県,島原市,札の元町,3-13,,1978/10/06 3,内田史織,ウチダシオリ,女,0942977483,848-0133,佐賀県,伊万里市,黒川町真手野,3-5-1,,1989/05/25 4,曽根里沙,ソネリサ,女,0271234470,378-0077,群馬県,沼田市,石墨町,1-7-1,パーク石墨町204,1985/03/22 5,徳田雪乃,トクダユキノ,女,083932164,752-0980,山口県,下関市,長府黒門町,3-6-5,,1988/03/23 6,山下直人,ヤマシタナオト,男,099655084,891-1206,鹿児島県,鹿児島市,皆与志町,2-10-9,ザ皆与志町314,1956/05/11 7,真田敬三,サナダケイゾウ,男,0766626361,936-0827,富山県,滑川市,東福寺,2-8-14,タウン東福寺202,1972/12/10 8,山形順子,ヤマガタジュンコ,女,0194190969,020-0851,岩手県,盛岡市,向中野,4-1-20,向中野マンション407,1979/11/21 9,奥照雄,オクテルオ,男,0895179833,799-1361,愛媛県,西条市,広江,2-5-17,広江シティ215,1987/03/17
単純に Drill で SELECT * するとこんな風になります。
0: jdbc:drill:zk=local> SELECT * FROM dfs.`/tmp/personal_information.csv` LIMIT 10; +--------------------------------------------------------------------------------------------------------------+ | columns | +--------------------------------------------------------------------------------------------------------------+ | ["連番","氏名","氏名（カタカナ）","性別","電話番号","郵便番号","住所1","住所2","住所3","住所4","住所5","生年月日"] | | ["1","佐川邦男","サガワクニオ","男","0959408329","852-8007","長崎県","長崎市","江の浦町","2-18","江の浦町スカイ401","1995/03/28"] | | ["2","松本冨子","マツモトトミコ","女","0957833608","855-0882","長崎県","島原市","札の元町","3-13","","1978/10/06"] | | ["3","内田史織","ウチダシオリ","女","0942977483","848-0133","佐賀県","伊万里市","黒川町真手野","3-5-1","","1989/05/25"] | | ["4","曽根里沙","ソネリサ","女","0271234470","378-0077","群馬県","沼田市","石墨町","1-7-1","パーク石墨町204","1985/03/22"] | | ["5","徳田雪乃","トクダユキノ","女","083932164","752-0980","山口県","下関市","長府黒門町","3-6-5","","1988/03/23"] | | ["6","山下直人","ヤマシタナオト","男","099655084","891-1206","鹿児島県","鹿児島市","皆与志町","2-10-9","ザ皆与志町314","1956/05/11"] | | ["7","真田敬三","サナダケイゾウ","男","0766626361","936-0827","富山県","滑川市","東福寺","2-8-14","タウン東福寺202","1972/12/10"] | | ["8","山形順子","ヤマガタジュンコ","女","0194190969","020-0851","岩手県","盛岡市","向中野","4-1-20","向中野マンション407","1979/11/21"] | | ["9","奥照雄","オクテルオ","男","0895179833","799-1361","愛媛県","西条市","広江","2-5-17","広江シティ215","1987/03/17"] | +--------------------------------------------------------------------------------------------------------------+ 10 rows selected (0.34 seconds)
各行は配列型になっているので、各カラムにアクセスするには添字が必要でした。
0: jdbc:drill:zk=local> SELECT columns[1] AS 氏名, columns[4] AS 電話番号 . . . . . . . . . . . > FROM dfs.`/tmp/personal_information.csv` . . . . . . . . . . . > WHERE columns[1] LIKE _UTF16'田中%'; +-------+-------------+ | 氏名 | 電話番号 | +-------+-------------+ | 田中優依 | 011944797 | | 田中伍朗 | 0864019970 | | 田中啓司 | 097833684 | +-------+-------------+ 3 rows selected (0.223 seconds)
が、バージョン1.3以降では、ファイルの拡張子をcsvhにしておくと・・・
$ mv /tmp/personal_information.csv /tmp/personal_information.csvh
これだけで先頭行がカラム名としてセットされ、クエリの中で利用できるようになります。こいつは便利ですね！
0: jdbc:drill:zk=local> SELECT * FROM dfs.`/tmp/personal_information.csvh` LIMIT 10; +-----+-------+-----------+-----+-------------+-----------+-------+-------+---------+---------+--------------+-------------+ | 連番 | 氏名 | 氏名（カタカナ） | 性別 | 電話番号 | 郵便番号 | 住所1 | 住所2 | 住所3 | 住所4 | 住所5 | 生年月日 | +-----+-------+-----------+-----+-------------+-----------+-------+-------+---------+---------+--------------+-------------+ | 1 | 佐川邦男 | サガワクニオ | 男 | 0959408329 | 852-8007 | 長崎県 | 長崎市 | 江の浦町 | 2-18 | 江の浦町スカイ401 | 1995/03/28 | | 2 | 松本冨子 | マツモトトミコ | 女 | 0957833608 | 855-0882 | 長崎県 | 島原市 | 札の元町 | 3-13 | | 1978/10/06 | | 3 | 内田史織 | ウチダシオリ | 女 | 0942977483 | 848-0133 | 佐賀県 | 伊万里市 | 黒川町真手野 | 3-5-1 | | 1989/05/25 | | 4 | 曽根里沙 | ソネリサ | 女 | 0271234470 | 378-0077 | 群馬県 | 沼田市 | 石墨町 | 1-7-1 | パーク石墨町204 | 1985/03/22 | | 5 | 徳田雪乃 | トクダユキノ | 女 | 083932164 | 752-0980 | 山口県 | 下関市 | 長府黒門町 | 3-6-5 | | 1988/03/23 | | 6 | 山下直人 | ヤマシタナオト | 男 | 099655084 | 891-1206 | 鹿児島県 | 鹿児島市 | 皆与志町 | 2-10-9 | ザ皆与志町314 | 1956/05/11 | | 7 | 真田敬三 | サナダケイゾウ | 男 | 0766626361 | 936-0827 | 富山県 | 滑川市 | 東福寺 | 2-8-14 | タウン東福寺202 | 1972/12/10 | | 8 | 山形順子 | ヤマガタジュンコ | 女 | 0194190969 | 020-0851 | 岩手県 | 盛岡市 | 向中野 | 4-1-20 | 向中野マンション407 | 1979/11/21 | | 9 | 奥照雄 | オクテルオ | 男 | 0895179833 | 799-1361 | 愛媛県 | 西条市 | 広江 | 2-5-17 | 広江シティ215 | 1987/03/17 | | 10 | 三木安弘 | ミキヤスヒロ | 男 | 0968649092 | 865-0008 | 熊本県 | 玉名市 | 石貫 | 2-20 | 石貫ハウス107 | 1966/06/17 | +-----+-------+-----------+-----+-------------+-----------+-------+-------+---------+---------+--------------+-------------+ 10 rows selected (0.666 seconds)
ちょっとだけ説明を付け加えると、この機能はストレージプラグインのフォーマット設定にある extractHeader というプロパティで有効になります。Embedded モードで起動している場合には、Drill シェル動作中にブラウザで http://localhost:8047 にアクセスし、「Storage」タブの「dfs」プラグインの設定を見てみると、csvh の拡張子がついている場合には機能が有効になることがわかります。
"formats": {
...
"csvh": {
"type": "text",
"extensions": [
"csvh"
],
"extractHeader": true,
"delimiter": ","
}
}
