® © 2015 MapR Technologies 1 ® © 2015 MapR Technologies Apache Drill でオープンデータを分析してみる 草薙 昭彦 MapR Technologies 2015 年年 9 ⽉月 11 ⽇日

® © 2015 MapR Technologies 2 Apache Drill 1.0 リリース (5/19) http://drill.apache.org

® © 2015 MapR Technologies 3 本⽇日のトピック •  Apache Drill 概要 •  オープンデータを分析してみよう

® © 2015 MapR Technologies 4 © 2015 MapR Technologies ® Apache Drill 概要

® © 2015 MapR Technologies 5 ⾮非構造化データ 構造化データ 1980 2000 2010 1990 2020 データは２年年で倍に増える ⾮非構造化データ の割合は 企業や組織が集めたデータ の 80%以上 に増⼤大する 出典: Human-Computer Interaction & Knowledge Discovery in Complex Unstructured, Big Data 合計格納データサイズ

® © 2015 MapR Technologies 6 1980 2000 2010 1990 2020 固定スキーマ DB管理理者が構造を管理理 動的／柔軟なスキーマ アプリケーションが構造を管理理 ⾮非リレーショナルデータベース リレーショナルデータベース GB〜～TB TB〜～PB データサイズ データベース データは⾮非リレーショナルデータストアに向かう データ構造 開発スタイル 構造化 構造化、半構造化、⾮非構造化 計画的（リリースサイクル＝数ヶ⽉月〜～数年年） 反復復的（リリースサイクル＝数⽇日〜～数週間）

® © 2015 MapR Technologies 7 ⾮非構造化データの時代の SQL とは？ SQL の使いやすさ NoSQL の柔軟性 •  SQL •  BI (Tableau、MicroStrategy など) •  低レイテンシ •  スケーラビリティ •  スキーマ管理理なし –  HDFS (Parquet、JSON など) –  HBase –  … •  データ変換・複製なし

® © 2015 MapR Technologies 8 Industry's First Schema-free SQL engine for Big Data ®

® © 2015 MapR Technologies 9 セルフサービスからスキーマフリーへの拡張 迅速さ & ビジネス価値 BI のユースケース IT部⾨門主導のBI セルフサービス BI スキーマフリー データ探索索 IT部⾨門主導のBI IT部⾨門主導のBI セルフサービス BI IT 部⾨門への依存なしで アナリストが主導 IT 部⾨門のETLの⽀支援を 受け、アナリストが主導 IT 部⾨門が作成する レポート、スプレッドシート 1980年年代 -1990年年代 2000年年代 現在

® © 2015 MapR Technologies 10 即時分析により「即断可能な」ビジネスを実現 Hadoop
 データ データ モデリング 変換 データ移動 （任意） ユーザー Hadoop
 データ ユーザー 管理理された アプローチ 探索索的な アプローチ 新しいビジネスの問いかけ ソースデータの変更更 結果を得るまでの合計時間: 数週間から数ヶ⽉月 結果を得るまでの合計時間: 数分

® © 2015 MapR Technologies 11 Drill は 動的なスキーマディスカバリ をサポート •  固定スキーマ •  中央管理理されたレポジトリのスキー マを利利⽤用 (Hive メタストア) •  固定スキーマ、変化するスキーマ、 もしくはスキーマレス •  中央管理理されたレポジトリのスキーマ、 ⾃自⼰己記述型データのスキーマを利利⽤用 2 動的にスキーマを発⾒見見 事前にスキーマを宣⾔言 SCHEMA ON WRITE SCHEMA BEFORE READ SCHEMA ON THE FLY

® © 2015 MapR Technologies 12 Drill のデータモデルはフレキシブル JSON BSON HBase Parquet Avro CSV TSV 動的スキーマ 固定スキーマ 複雑 フラット 柔軟性 Name! Gender! Age! Michael! M! 6! Jennifer! F! 3! {! name: {! first: Michael,! last: Smith! },! hobbies: [ski, soccer],! district: Los Altos! }! {! name: {! first: Jennifer,! last: Gates! },! hobbies: [sing],! preschool: CCLC! }! RDBMS/SQL-on-Hadoop テーブル Apache Drill テーブル 複雑性

® © 2015 MapR Technologies 13 -  サブディレクトリ -  HBase ネームスペース -  Hive データベース Drill は「SQL on Everything」を可能にする SELECT  *  FROM  dfs.yelp.`business.json`  ! ワークスペース -  パス名 -  Hive テーブル -  HBase テーブル テーブル -  DFS (Text, Parquet, JSON) -  HBase/MapR-DB -  Hive メタストアHCatalog/ - Hadoop 以外にも対応する簡単な API ストレージプラグインインスタンス

® © 2015 MapR Technologies 14 アーキテクチャの概要 •  コモディティサーバのクラスタ –  各ノード上でデーモン (drillbit) が動作 •  他の実⾏行行エンジンには⾮非依存（MapReduce、Spark、Tez） –  より優れた性能と管理理性 •  ZooKeeper が逐⼀一変化するクラスタのメンバーシップ情報を管理理 –  drillbit は ZooKeeper を利利⽤用してクラスタ内の他の drillbit を⾒見見つける –  クライアントは ZooKeeper を利利⽤用して drillbit を⾒見見つける •  データ処理理単位は カラムナレコードバッチ   –  性能への影響を最⼩小限に抑えつつスキーマの柔軟性を実現

® © 2015 MapR Technologies 15 Drill はデータ局所性を最⼤大限活⽤用する データソース ベストプラクティス HDFS または MapR-FS 各 DataNode 上の drillbit がアクセス HBase または MapR-DB 各 RegionServer 上の drillbit がアクセス MongoDB 各 mongod 上の drillbit がアクセス(レプリカ使⽤用時はレプリカノード上で稼働) drillbit   DataNode/ RegionServer/ mongod   drillbit   DataNode/ RegionServer/ mongod   drillbit   DataNode/ RegionServer/ mongod   ZooKeeper ZooKeeper ZooKeeper …

® © 2015 MapR Technologies 16 SELECT* クエリ実⾏行行 drillbit   ZooKeeper クライアント (JDBC, ODBC, REST) 1.  drillbit を⾒見見つける （セッションごと） 3.  論論理理および物理理実⾏行行プランを作成 4.  クラスタに個別のフラグメントの実⾏行行を⾏行行わ せる（完全な分散実⾏行行） ZooKeeper ZooKeeper drillbit   drillbit   2.  drillbit  に クエリを送信 5.  クライアント に結果を返す * CTAS (CREATE TABLE AS SELECT) クエリはステップ 1〜～4 を含む

® © 2015 MapR Technologies 17 drillbit 内部のコアモジュール   SQL パーサ Hive HBase 分散キャッシュ ストレージプラグイン MongoDB DFS 物理理プラン 実⾏行行エンジン 論論理理プラン オプティマイザ RPC エンドポイント

® © 2015 MapR Technologies 18 © 2015 MapR Technologies ® オープンデータを分析してみよう

® © 2015 MapR Technologies 19 Drill で政府統計情報 (e-Stat) を分析してみよう

® © 2015 MapR Technologies 20 Drill で政府統計情報 (e-Stat) を分析してみよう 政府統計情報 e-Stat を Apache Drill で分析してみる
 http://nagix.hatenablog.com/entry/2015/05/21/232526

® © 2015 MapR Technologies 21 今、もっとも注⽬目の⾼高いオープンデータ

® © 2015 MapR Technologies 22 😀

® © 2015 MapR Technologies 23 Drill インストール •  JDK 7 が必要 •  あとは簡単 $ wget http://getdrill.org/drill/download/apache-drill-1.1.0.tar.gz $ tar -xvzf apache-drill-1.1.0.tar.gz $ apache-drill-1.1.0/bin/drill-embedded 0: jdbc:drill:zk=local>

® © 2015 MapR Technologies 24 オープンデータ $ ls -l 😀

® © 2015 MapR Technologies 25 README $ cat README 😀

® © 2015 MapR Technologies 26 余談 😀

® © 2015 MapR Technologies 27 MySQL ダンプファイル DROP TABLE IF EXISTS `😀😀😀😀😀😀😀`; CREATE TABLE `😀😀😀😀😀😀😀` ( `id` int(11) NOT NULL AUTO_INCREMENT, `createdon` timestamp NULL DEFAULT NULL, `createdby` int(11) DEFAULT NULL, ... ) ENGINE=InnoDB AUTO_INCREMENT=36993336 DEFAULT CHARSET=utf8; LOCK TABLES `😀😀😀😀😀😀😀` WRITE; INSERT INTO `😀😀😀😀😀😀😀` VALUES (9,'2002-01-17 02:15:08',0,'2011-10 -14 13:47:31',20,2,2,1,1,0,19630, ... ),( ... ), ... ,( ... ); INSERT INTO `😀😀😀😀😀😀😀` VALUES (2297,'2002-03-19 22:13:14',0,'2011 -10-14 15:47:29',11,3,2,1,2,0,21891, ... ),( ... ), ... ,( ... ); ...

® © 2015 MapR Technologies 28 MySQL ダンプファイル DROP TABLE IF EXISTS `😀😀😀😀😀😀😀`; CREATE TABLE `😀😀😀😀😀😀😀` ( `id` int(11) NOT NULL AUTO_INCREMENT, `createdon` timestamp NULL DEFAULT NULL, `createdby` int(11) DEFAULT NULL, ... ) ENGINE=InnoDB AUTO_INCREMENT=36993336 DEFAULT CHARSET=utf8; LOCK TABLES `😀😀😀😀😀😀😀` WRITE; INSERT INTO `😀😀😀😀😀😀😀` VALUES (9,'2002-01-17 02:15:08',0,'2011-10 -14 13:47:31',20,2,2,1,1,0,19630, ... ),( ... ), ... ,( ... ); INSERT INTO `😀😀😀😀😀😀😀` VALUES (2297,'2002-03-19 22:13:14',0,'2011 -10-14 15:47:29',11,3,2,1,2,0,21891, ... ),( ... ), ... ,( ... ); ... CSVに変換できそう

® © 2015 MapR Technologies 29 MySQL ダンプファイルを CSV ファイルに変換 #!/usr/bin/perl while (<>) { s/^(--|\/\*| |\)|DROP|CREATE|LOCK).*//g; # 余計な⾏行行を削除 s/^INSERT INTO .+ VALUES \(//g; # INSERT ⽂文を削除 s/(?<=['\d])\),\((?=['\d])/\n/g; # ⾏行行を分割 s/\);$//g; # 　余計な⽂文字を削除 s/"/""/g; # " 　を 　"" 　に s/((?

® © 2015 MapR Technologies 30 CSV ファイルを SELECT •  件数を確認 –  たしかに 3197 万⼈人くらいはいます 0: jdbc:drill:zk=local> SELECT count(*) FROM dfs.`/tmp/😀😀😀.csv`; .csv`; +-----------+ | EXPR$0 | +-----------+ | 31971575 | +-----------+ 1 row selected (32.733 seconds)

® © 2015 MapR Technologies 31 CSV ファイルを SELECT •  中⾝身を⾒見見てみる –  CSV のフィールドは columns という名前のリスト（[a,b,...]）になる 0: jdbc:drill:zk=local> !set maxwidth 160 0: jdbc:drill:zk=local> SELECT * FROM dfs.`/tmp/😀😀😀.csv` LIMIT 3; +---------+ | columns | +---------+ | ["9","2002-01-17 02:15:08","0","2011-10-14 13:47:31","20","2","2","1","1","0"," 19630","😀😀😀😀😀","😀😀😀😀","😀😀😀😀😀😀","😀😀😀😀😀😀😀😀 Ave.","Suite 😀😀","To | | ["10","2002-01-17 02:22:35","0","2011-10-14 13:47:31","10","2","3","2","2","0", "19631","😀😀😀😀😀😀😀😀😀","😀😀😀","😀😀😀😀","😀😀😀😀😀 Ave","","York Region"," | | ["11","2002-01-17 20:17:27","0","2011-10-14 13:47:32","0","2","2","1","2","0"," 19632","😀😀😀😀😀😀😀😀😀","😀😀😀😀","😀😀😀😀","😀😀😀😀😀😀😀😀","","Toronto",""," | +---------+ 3 rows selected (0.564 seconds)

® © 2015 MapR Technologies 32 CSV ファイルを SELECT •  各カラムを取り出すには –  columns[0], columns[1] のように添え字をつける 0: jdbc:drill:zk=local> SELECT columns[0], columns[1], columns[2], columns[3], co lumns[4] FROM dfs.`/tmp/😀😀😀.csv` LIMIT 3; +---------+----------------------+---------+----------------------+---------+ | EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | +---------+----------------------+---------+----------------------+---------+ | 9 | 2002-01-17 02:15:08 | 0 | 2011-10-14 13:47:31 | 20 | | 10 | 2002-01-17 02:22:35 | 0 | 2011-10-14 13:47:31 | 10 | | 11 | 2002-01-17 20:17:27 | 0 | 2011-10-14 13:47:32 | 0 | +---------+----------------------+---------+----------------------+---------+ 3 rows selected (0.356 seconds)

® © 2015 MapR Technologies 33 CSV ファイルを SELECT •  カラム名をつけよう –  MySQL ダンプファイル先頭の定義を参考に 0: jdbc:drill:zk=local> SELECT columns[0] AS id, columns[1] AS createdon, columns [2] AS createdby, columns[3] AS updatedon, columns[4] AS updatedby FROM dfs.`/tmp /😀😀😀.csv` LIMIT 3; +-----+----------------------+------------+----------------------+------------+ | id | createdon | createdby | updatedon | updatedby | +-----+----------------------+------------+----------------------+------------+ | 9 | 2002-01-17 02:15:08 | 0 | 2011-10-14 13:47:31 | 20 | | 10 | 2002-01-17 02:22:35 | 0 | 2011-10-14 13:47:31 | 10 | | 11 | 2002-01-17 20:17:27 | 0 | 2011-10-14 13:47:32 | 0 | +-----+----------------------+------------+----------------------+------------+ 3 rows selected (0.327 seconds)

® © 2015 MapR Technologies 34 CSV ファイルを SELECT •  型はどうなってるの？ –  CSV ファイルの場合、すべてデフォルトで VARCHAR 型 –  CAST(値 AS データ型) を使ってキャストして使う •  エラー: 空⽂文字列列を数値型にキャストできない 0: jdbc:drill:zk=local> SELECT CAST(columns[0] AS INT) AS id, CAST(columns[1] AS TI MESTAMP) AS createdon, CAST(columns[2] AS INT) AS createdby, CAST(columns[3] AS TIM ESTAMP) AS updatedon, CAST(columns[4] AS INT) AS updatedby FROM dfs.`/tmp/😀😀😀.csv` LIMIT 3; Error: SYSTEM ERROR: NumberFormatException: Fragment 1:2 [Error Id: 33d800c9-78ea-473a-8e41-b13e38307af3 on node1:31010] (state=,code=0)

® © 2015 MapR Technologies 35 CSV の空⽂文字列列を NULL として扱うには •  ⽅方法1: CASE を使う –  カラムごとに空⽂文字の扱いを変えることができる •  ⽅方法2: システムプロパティを変更更する CASE WHEN columns[2] = '' THEN NULL ELSE CAST(columns[2] AS INT) END 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. | +-------+----------------------------------------------------------+

® © 2015 MapR Technologies 36 CSV ファイルを SELECT •  とりあえず⽅方法2で・・・ 0: jdbc:drill:zk=local> SELECT CAST(columns[0] AS INT) AS id, CAST(columns[1] AS TI MESTAMP) AS createdon, CAST(columns[2] AS INT) AS createdby, CAST(columns[3] AS TIM ESTAMP) AS updatedon, CAST(columns[4] AS INT) AS updatedby FROM dfs.`/tmp/😀😀😀.csv` LIMIT 3; +-----+------------------------+------------+------------------------+------------+ | id | createdon | createdby | updatedon | updatedby | +-----+------------------------+------------+------------------------+------------+ | 9 | 2002-01-17 02:15:08.0 | 0 | 2011-10-14 13:47:31.0 | 20 | | 10 | 2002-01-17 02:22:35.0 | 0 | 2011-10-14 13:47:31.0 | 10 | | 11 | 2002-01-17 20:17:27.0 | 0 | 2011-10-14 13:47:32.0 | 0 | +-----+------------------------+------------+------------------------+------------+ 3 rows selected (0.734 seconds)

® © 2015 MapR Technologies 37 やっぱり男ばかりなんでしょうか •  性別は 25 番のカラム –  1 は⼥女女性、2 は男性と思われる –  やはり・・・ 0: jdbc:drill:zk=local> SELECT columns[25] AS gender, count(*) AS number, TRUNC(1 00.0 * count(*) / 31971575, 2) AS percent FROM dfs.`/tmp/😀😀😀.csv` GROUP BY colu mns[25] ORDER BY columns[25]; +---------+-----------+----------+ | gender | number | percent | +---------+-----------+----------+ | | 9809 | 0.03 | | 0 | 2 | 0.0 | | 1 | 4414808 | 13.8 | | 2 | 27546956 | 86.16 | +---------+-----------+----------+ 4 rows selected (31.79 seconds)

® © 2015 MapR Technologies 38 ⼤大統領領がいらっしゃると聞きましたが •  いました 0: jdbc:drill:zk=local> SELECT columns[0] AS pnum, columns[1] AS email FROM dfs.` /tmp/😀😀😀😀😀😀😀😀😀😀😀.csv` WHERE columns[1] = '[email protected]'; +-----------+------------------------------+ | pnum | email | +-----------+------------------------------+ | 12655726 | [email protected] | +-----------+------------------------------+ 1 row selected (10.566 seconds)

® © 2015 MapR Technologies 39 ビューを作って楽をする •  毎回カラム名を指定したりキャストしたりするのは⼤大変 –  ビューの定義ファイルは /tmp に「.view.drill」という拡張⼦子のついた JSONファイルとして保存される 0: jdbc:drill:zk=local> CREATE VIEW dfs.tmp.`😀😀😀😀😀😀😀` AS SELECT . . . . . . . . . . . > CAST(columns[0] AS INT) AS id, . . . . . . . . . . . > CAST(columns[1] AS TIMESTAMP) AS createdon, . . . . . . . . . . . > CAST(columns[2] AS INT) AS createdby, . . . . . . . . . . . > CAST(columns[3] AS TIMESTAMP) AS updatedon, . . . . . . . . . . . > CAST(columns[4] AS INT) AS updatedby . . . . . . . . . . . > ... . . . . . . . . . . . > FROM . . . . . . . . . . . > dfs.`/tmp/😀😀😀.csv` . . . . . . . . . . . > ;

® © 2015 MapR Technologies 40 ディレクトリに対してまとめてクエリ •  😀😀😀😀😀😀😀😀トランザクションは CSV ファイルが 2642 個 $ ls 😀😀😀😀😀😀Transactions 2008-03-21_downloaded.csv 2010-08-19_downloaded.csv 2013-01-16_downloaded.csv 2008-03-22_downloaded.csv 2010-08-20_downloaded.csv 2013-01-17_downloaded.csv 2008-03-23_downloaded.csv 2010-08-21_downloaded.csv 2013-01-18_downloaded.csv 2008-03-24_downloaded.csv 2010-08-22_downloaded.csv 2013-01-19_downloaded.csv 2008-03-25_downloaded.csv 2010-08-23_downloaded.csv 2013-01-20_downloaded.csv 2008-03-26_downloaded.csv 2010-08-24_downloaded.csv 2013-01-21_downloaded.csv 2008-03-27_downloaded.csv 2010-08-25_downloaded.csv 2013-01-22_downloaded.csv 2008-03-28_downloaded.csv 2010-08-26_downloaded.csv 2013-01-23_downloaded.csv 2008-03-29_downloaded.csv 2010-08-27_downloaded.csv 2013-01-24_downloaded.csv 2008-03-30_downloaded.csv 2010-08-28_downloaded.csv 2013-01-25_downloaded.csv 2008-03-31_downloaded.csv 2010-08-29_downloaded.csv 2013-01-26_downloaded.csv 2008-04-01_downloaded.csv 2010-08-30_downloaded.csv 2013-01-27_downloaded.csv 2008-04-02_downloaded.csv 2010-08-31_downloaded.csv 2013-01-28_downloaded.csv 2008-04-03_downloaded.csv 2010-09-01_downloaded.csv 2013-01-29_downloaded.csv ...

® © 2015 MapR Technologies 41 ディレクトリに対してまとめてクエリ •  データソースはファイルではなくディレクトリ •  国別トランザクション数のトップ 10 0: jdbc:drill:zk=local> columns[19] AS TXT_COUNTRY, count(*) AS number from dfs.`/tmp/ 😀😀😀😀😀😀Transactions` GROUP BY columns[19] ORDER BY count(*) DESC LIMIT 10; Transactions` GROUP BY columns[19] ORDER BY count(*) DESC LIMIT 10; +--------------+----------+ | TXT_COUNTRY | number | +--------------+----------+ | US | 7591509 | | CA | 823746 | | BR | 197032 | | AU | 146745 | | TW | 118338 | | CL | 109875 | | ZA | 78126 | | AR | 75314 | | JP | 74165 | | GB | 57901 | +--------------+----------+

® © 2015 MapR Technologies 42 ディレクトリを階層構造にしてみる •  年年と⽉月のディレクトリを作って、CSV ファイルを配置する $ cd 😀😀😀😀😀😀Transactions $ for file in `ls *.csv`; do > dir=`echo $file | cut -c 1-7 | tr - /` > 　 if [ ! -d $dir ]; then > mkdir -p $dir > fi > 　 　 mv $file $dir > done $ ls 2008 2009 2010 2011 2012 2013 2014 2015 $ ls 2008 03 04 05 06 07 08 09 10 11 12 $ ls 2008/03 2008-03-21_downloaded.csv 2008-03-25_downloaded.csv 2008-03-29_downloaded.csv 2008-03-22_downloaded.csv 2008-03-26_downloaded.csv 2008-03-30_downloaded.csv 2008-03-23_downloaded.csv 2008-03-27_downloaded.csv 2008-03-31_downloaded.csv 2008-03-24_downloaded.csv 2008-03-28_downloaded.csv

® © 2015 MapR Technologies 43 ⽉月別トランザクション総額 •  dir0,dir1はディレクトリ名を表す特別なスキーマ 0: jdbc:drill:zk=local> SELECT dir0 AS year, dir1 AS month, TRUNC(SUM(CAST(REGEXP _REPLACE(REGEXP_REPLACE(columns[2], '^\\(', '-'), ',|\\)', '') AS DOUBLE)), 2) AS amount from dfs.`/tmp/😀😀😀😀😀😀Transactions` WHERE columns[2] <> 'AMOUNT' GROUP BY dir0, dir1 ORDER BY dir0, dir1; +-------+-------+-----------------+ | dir0 | dir1 | amount | +-------+-------+-----------------+ | 2008 | 03 | 97676.25 | | 2008 | 04 | 266162.39 | | 2008 | 05 | 1330456.45 | | 2008 | 06 | 1630110.26 | | 2008 | 07 | 2590733.03 | | 2008 | 08 | 2743130.11 | | 2008 | 09 | 2436655.66 | | 2008 | 10 | 2534268.59 | | 2008 | 11 | 2934391.31 | ...

® © 2015 MapR Technologies 44 ⽉月別トランザクション総額 😀

® © 2015 MapR Technologies 45 まとめ •  Apache Drill で、データをロードすることなく、すぐに分析を始 めることができる •  多数のファイルが散在している場合でも、まとめてクエリをか けることができる

® © 2015 MapR Technologies 46 Q & A @mapr_japan maprjapan [email protected] お問い合わせはこちらまで MapR maprtech mapr-technologies