HadoopTimes

ストリーミングアーキテクチャ Apache Kafka とMapR Streams による新しい設計手法
技術情報

Apache Drillを使用してYelpの生データを数分で分析する方法

Apache Drillは、急成長しているオープンソースプロジェクトの1つであり、その月毎のリリースに伴い、このコミュニティも飛躍的に進歩しています。最新のDrill 0.6リリースも、このプロジェクトにとって重要なマイルストーンでした。今回のリリースでは、(現在既に対応済みのファイルシステム、HBase、および、Hiveソースに加え) MongoDBに直接SQLクエリを行えるようになる等、重要な強化や、数多くのパフォーマンスおよびSQL関連の改善が行われ、パワーアップしました。

apache-drill-logo2
この記事では、Drillの主な使用法の1つである、SQLを使用してHadoop/NoSQLシステムに送られてくる生データを調査・分析する方法を簡単にご紹介したいと思います。

“ちょっと待って、それって普通に出回っているどのSQL-on-Hadoopシステムでもやっていることじゃないかって?”

その通りです。
ですが、Drillでは主に、そのスピードと柔軟性が違います。SQL-on-Hadoopの主要な特性 (大規模レベルで低遅延を実現) にも対応しながら、更に、ETLや重要なスキーマ定義の必要なくデータ分析を可能にしています。データは、テキスト、JSON、Parquetなど、どのようなファイル形式でも構いません。データのタイプは、文字列、整数、日付などシンプルなものでも、あるいは、もっと複雑なネスト化したマップや配列などのマルチ構造データなども可能です。データのファイルシステムは、ローカルでも分散型でも (HDFS、MapR FS、またはS3) 可能です。「スキーマ不要」の方法を用いたDrillならば、僅か数分でデータから値を取得できます。
簡単な例を用いて詳しく見ていきましょう。この例では、一般利用可能なYelp (ビジネスレビューサイト) からダウンロードしたJSON形式のデータセットを使用しています。

ステップ1: ご利用のマシンにApache Drillをダウンロードします。
http://incubator.apache.org/drill/download/
また、ご利用の環境に合わせてスケーリングしたい場合は、DrillをClusterモードでデプロイすることも可能です。

ステップ2 : Drill tarファイルを開きます。
tar -xvf apache-drill-0.6.0-incubating.tar

ステップ3: Drill付属のJDBCアプリケーション、SQLラインを起動します
bin/sqlline -u jdbc:drill:zk=local

これだけです!これでデータ調査の準備が整いました。
SQLのサンプルを使用し、Drillにより生データの分析がいかに簡単になるかを見ていきましょう。

1. まず、Yelpビジネスデータの内容を見てみましょう。

0: jdbc:drill:zk=local> !set maxwidth 10000
 
0: jdbc:drill:zk=local> select * from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_business.json` limit 1;
+-------------+--------------+------------+------------+------------+------------+--------------+------------+------------+------------+------------+------------+------------+------------+---------------+
| business_id | full_address |   hours    | open  | categories |  city    | review_count |  name  | longitude  |   state  |   stars |  latitude  | attributes | type  | neighborhoods |
+-------------+--------------+------------+------------+------------+------------+--------------+------------+------------+------------+------------+------------+------------+------------+---------------+
| vcNAWiLM4dR7D2nwwJ7nCA | 4840 E Indian School Rd
Ste 101
Phoenix, AZ 85018 | {"Tuesday":{"close":"17:00","open":"08:00"},"Friday":{"close":"17:00","open":"08:00"},"Monday":{"close":"17:00","open":"08:00"},"Wednesday":{"close":"17:00","open":"08:00"},"Thursday":{"close":"17:00","open":"08:00"},"Sunday":{},"Saturday":{}} | true    | ["Doctors","Health & Medical"] | Phoenix  | 7         | Eric Goldberg, MD | -111.983758 | AZ      | 3.5     | 33.499313  | {"By Appointment Only":true,"Good For":{},"Ambience":{},"Parking":{},"Music":{},"Hair Types Specialized In":{},"Payment Types":{},"Dietary Restrictions":{}} | business   | []         |
+-------------+--------------+------------+------------+------------+------------+--------------+------------+------------+------------+------------+------------+------------+------------+---------------+

Drillでは、JSON、Parquet、テキストなどの自己記述的なファイルを直接クエリできます。Hiveのメタストアでメタデータを定義する必要はありません。

2. ビジネスデータセットを更に詳しく調査します。

データセットに含まれるレビュー数の合計値
0: jdbc:drill:zk=local> select sum(review_count) as totalreviews from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_business.json` ;
+--------------+
| totalreviews |
+--------------+
| 1236445   |
+--------------+

 

合計レビュー数がトップの州と都市
 
0: jdbc:drill:zk=local> select state, city, count(*) totalreviews from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_business.json`  group by state, city order by count(*) desc limit 10;
+------------+------------+--------------+
|   state | city    | totalreviews |
+------------+------------+--------------+
| NV      | Las Vegas  | 12021        |
| AZ      | Phoenix | 7499      |
| AZ      | Scottsdale | 3605      |
| EDH     | Edinburgh  | 2804         |
| AZ      | Mesa    | 2041      |
| AZ      | Tempe   | 2025      |
| NV      | Henderson  | 1914         |
| AZ      | Chandler   | 1637       |
| WI      | Madison | 1630         |
| AZ      | Glendale   | 1196       |
+------------+------------+--------------+

 

ビジネス格付け別の平均レビュー数
0: jdbc:drill:zk=local> select stars,trunc(avg(review_count)) reviewsavg from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_business.json` group by stars order by stars desc;
+------------+------------+
|   stars | reviewsavg |
+------------+------------+
| 5.0     | 8.0     |
| 4.5     | 28.0    |
| 4.0     | 48.0    |
| 3.5     | 35.0    |
| 3.0     | 26.0    |
| 2.5     | 16.0    |
| 2.0     | 11.0    |
| 1.5     | 9.0     |
| 1.0     | 4.0     |
+------------+------------+

 

レビュー数が多い (1000回以上) トップビジネス
0: jdbc:drill:zk=local> select name, state,city, `review_count` from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_business.json` where review_count > 1000 order by `review_count` desc limit 10;
+------------+------------+------------+--------------+
|    name |   state    |  city  | review_count |
+------------+------------+------------+--------------+
| Mon Ami Gabi | NV       | Las Vegas  | 4084         |
| Earl of Sandwich | NV       | Las Vegas  | 3655         |
| Wicked Spoon | NV       | Las Vegas  | 3408         |
| The Buffet | NV       | Las Vegas  | 2791         |
| Serendipity 3 | NV      | Las Vegas  | 2682         |
| Bouchon | NV         | Las Vegas  | 2419      |
| The Buffet at Bellagio | NV       | Las Vegas  | 2404         |
| Bacchanal Buffet | NV       | Las Vegas  | 2369         |
| The Cosmopolitan of Las Vegas | NV      | Las Vegas  | 2253         |
| Aria Hotel & Casino | NV         | Las Vegas  | 2224         |
+------------+------------+------------+--------------+

 

一部ビジネスの土曜日の開店・閉店時間を確認
0: jdbc:drill:zk=local> select b.name, b.hours.Saturday.`open`, b.hours.Saturday.`close` from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_business.json` b limit 10
+------------+------------+------------+
|    name |   EXPR$1   |   EXPR$2   |
+------------+------------+------------+
| Eric Goldberg, MD | 08:00   | 17:00   |
| Pine Cone Restaurant | null     | null    |
| Deforest Family Restaurant | 06:00    | 22:00   |
| Culver's   | 10:30    | 22:00   |
| Chang Jiang Chinese Kitchen | 11:00   | 22:00   |
| Charter Communications | null     | null    |
| Air Quality Systems | null    | null    |
| McFarland Public Library | 09:00    | 20:00   |
| Green Lantern Restaurant | 06:00    | 02:00   |
| Spartan Animal Hospital | 07:30   | 18:00   |
+------------+------------+------------+

Drillにより、マルチ構造のネスト全体を調査できることにご注目ください。

3. データセット内の各ビジネスの特典を調べます。

Yelpビジネスデータセットの属性欄には、各行ごとに異なる要素が入っており、ビジネスに別々の特典を設けられるようになっています。Drillでは、スキーマを変えて素早く簡単にデータセットを分析できます。
まず、あらゆるテキストモードで機能するようにDrillを設定します (あらゆるデータを調査できるようにするため)。

0: jdbc:drill:zk=local> alter system set `store.json.all_text_mode` = true;
+------------+------------+
|     ok  |  summary   |
+------------+------------+
| true    | store.json.all_text_mode updated. |
+------------+------------+

 

次に、その属性のデータをクエリします。
0: jdbc:drill:zk=local> select attributes from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_business.json` limit 10;
+------------+
| attributes |
+------------+
| {"By Appointment Only":"true","Good For":{},"Ambience":{},"Parking":{},"Music":{},"Hair Types Specialized In":{},"Payment Types":{},"Dietary Restrictions":{}} |
| {"Take-out":"true","Good For":{"dessert":"false","latenight":"false","lunch":"true","dinner":"false","breakfast":"false","brunch":"false"},"Caters":"false","Noise Level":"averag |
| {"Take-out":"true","Good For":{"dessert":"false","latenight":"false","lunch":"false","dinner":"false","breakfast":"false","brunch":"true"},"Caters":"false","Noise Level":"quiet" |
| {"Take-out":"true","Good For":{},"Takes Reservations":"false","Delivery":"false","Ambience":{},"Parking":{"garage":"false","street":"false","validated":"false","lot":"true","val |
| {"Take-out":"true","Good For":{},"Ambience":{},"Parking":{},"Has TV":"false","Outdoor Seating":"false","Attire":"casual","Music":{},"Hair Types Specialized In":{},"Payment Types |
| {"Good For":{},"Ambience":{},"Parking":{},"Music":{},"Hair Types Specialized In":{},"Payment Types":{},"Dietary Restrictions":{}} |
| {"Good For":{},"Ambience":{},"Parking":{},"Music":{},"Hair Types Specialized In":{},"Payment Types":{},"Dietary Restrictions":{}} |
| {"Good For":{},"Ambience":{},"Parking":{},"Wi-Fi":"free","Music":{},"Hair Types Specialized In":{},"Payment Types":{},"Dietary Restrictions":{}} |
| {"Take-out":"true","Good For":{"dessert":"false","latenight":"false","lunch":"false","dinner":"true","breakfast":"false","brunch":"false"},"Noise Level":"average","Takes Reserva |
| {"Good For":{},"Ambience":{},"Parking":{},"Music":{},"Hair Types Specialized In":{},"Payment Types":{},"Dietary Restrictions":{}} |
+------------+

すべてのテキストモードをオフにし、データに算術演算を続行できるようにします。

0: jdbc:drill:zk=local> alter system set `store.json.all_text_mode` = false;
+------------+------------+
|     ok  |  summary   |
+------------+------------+
| true    | store.json.all_text_mode updated. |

 

4. データセット内のレストランビジネスについて調査します。

データセット内にいくつのレストランが存在するか?
0: jdbc:drill:zk=local> select count(*) as TotalRestaurants from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_business.json` where true=repeated_contains(categories,'Restaurants');
+------------------+
| TotalRestaurants |
+------------------+
| 14303         |
+------------------+

 

レビュー数トップのレストラン
0: jdbc:drill:zk=local> select name,state,city,`review_count` from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_business.json` where true=repeated_contains(categories,'Restaurants') order by `review_count` desc limit 10
. . . . . . . . . . . > ;
+------------+------------+------------+--------------+
|    name |   state    |  city  | review_count |
+------------+------------+------------+--------------+
| Mon Ami Gabi | NV       | Las Vegas  | 4084         |
| Earl of Sandwich | NV       | Las Vegas  | 3655         |
| Wicked Spoon | NV       | Las Vegas  | 3408         |
| The Buffet | NV       | Las Vegas  | 2791         |
| Serendipity 3 | NV      | Las Vegas  | 2682         |
| Bouchon | NV         | Las Vegas  | 2419      |
| The Buffet at Bellagio | NV       | Las Vegas  | 2404         |
| Bacchanal Buffet | NV       | Las Vegas  | 2369         |
| Hash House A Go Go | NV       | Las Vegas  | 2201         |
| Mesa Grill | NV       | Las Vegas  | 2004         |
+------------+------------+------------+--------------+

 

カテゴリ数トップのレストラン
0: jdbc:drill:zk=local> select name,repeated_count(categories) as categorycount, categories from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_business.json` where true=repeated_contains(categories,'Restaurants') order by repeated_count(categories) desc limit 10;
+------------+---------------+------------+
|    name | categorycount | categories |
+------------+---------------+------------+
| Binion's Hotel & Casino | 10          | ["Arts & Entertainment","Restaurants","Bars","Casinos","Event Planning & Services","Lounges","Nightlife","Hotels & Travel","American (N |
| Stage Deli | 10         | ["Arts & Entertainment","Food","Hotels","Desserts","Delis","Casinos","Sandwiches","Hotels & Travel","Restaurants","Event Planning & Services"] |
| Jillian's  | 9          | ["Arts & Entertainment","American (Traditional)","Music Venues","Bars","Dance Clubs","Nightlife","Bowling","Active Life","Restaurants"] |
| Hotel Chocolat | 9          | ["Coffee & Tea","Food","Cafes","Chocolatiers & Shops","Specialty Food","Event Planning & Services","Hotels & Travel","Hotels","Restaurants"] |
| Hotel du Vin & Bistro Edinburgh | 9           | ["Modern European","Bars","French","Wine Bars","Event Planning & Services","Nightlife","Hotels & Travel","Hotels","Restaurants" |
| Elixir  | 9             | ["Arts & Entertainment","American (Traditional)","Music Venues","Bars","Cocktail Bars","Nightlife","American (New)","Local Flavor","Restaurants"] |
| Tocasierra Spa and Fitness | 8          | ["Beauty & Spas","Gyms","Medical Spas","Health & Medical","Fitness & Instruction","Active Life","Day Spas","Restaurants"] |
| Costa Del Sol At Sunset Station | 8           | ["Steakhouses","Mexican","Seafood","Event Planning & Services","Hotels & Travel","Italian","Restaurants","Hotels"] |
| Scottsdale Silverado Golf Club | 8          | ["Fashion","Shopping","Sporting Goods","Active Life","Golf","American (New)","Sports Wear","Restaurants"] |
| House of Blues | 8          | ["Arts & Entertainment","Music Venues","Restaurants","Hotels","Event Planning & Services","Hotels & Travel","American (New)","Nightlife"] |
+------------+---------------+------------+

 

レビュー数トップのカテゴリ

0: jdbc:drill:zk=local> select categories[0], count(categories[0]) as categorycount from dfs.`/users/nrentachintala/Downloads/yelp_dataset_challenge_academic_dataset/yelp_academic_dataset_business.json` group by categories[0] order by count(categories[0]) desc limit 10;
+------------+---------------+
|   EXPR$0   | categorycount |
+------------+---------------+
| Food       | 4294          |
| Shopping   | 1885          |
| Active Life | 1676          |
| Bars       | 1366          |
| Local Services | 1351          |
| Mexican    | 1284          |
| Hotels & Travel | 1283          |
| Fast Food  | 963           |
| Arts & Entertainment | 906           |
| Hair Salons | 901           |
+------------+---------------+

 

5. Yelpレビューのデータセットを調査し、ビジネスと組み合わせます。

Yelpレビューのデータセットの内容を調査します。
0: jdbc:drill:zk=local> select * from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_review.json` limit 1;
+------------+------------+------------+------------+------------+------------+------------+-------------+
|   votes |  user_id   | review_id  |   stars    |  date  |    text | type    | business_id |
+------------+------------+------------+------------+------------+------------+------------+-------------+
| {"funny":0,"useful":2,"cool":1} | Xqd0DzHaiyRqVH3WRG7hzg | 15SdjuK7DmYqUAj6rjGowg | 5       | 2007-05-17 | dr. goldberg offers everything i look for in a general practitioner.  he's nice and easy to talk to without being patronizing; he's always on time in seeing his patients; he's affiliated with a top-notch hospital (nyu) which my parents have explained to me is very important in case something happens and you need surgery; and you can get referrals to see specialists without having to see him first.  really, what more do you need?  i'm sitting here trying to think of any complaints i have about him, but i'm really drawing a blank. | review  | vcNAWiLM4dR7D2nwwJ7nCA |
+------------+------------+------------+------------+------------+------------+------------+-------------+

 

評価の良いレビューが投稿されているビジネス

ここでは、総レビュー数 (review_count) が含まれるYelpビジネスデータセットを、各レビューの追加詳細が含まれるYelpレビューデータに組み合わせています。

0: jdbc:drill:zk=local> Select b.name from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_business.json` b where b.business_id in (SELECT r.business_id FROM dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_review.json` r
GROUP BY r.business_id having sum(r.votes.cool) > 2000 order by sum(r.votes.cool)  desc);
+------------+
|    name |
+------------+
| Earl of Sandwich |
| XS Nightclub |
| The Cosmopolitan of Las Vegas |
| Wicked Spoon |
+------------+

 

ビジネスとレビューのデータセットを組み合わせたビューを作成

Drillのビューは軽量なので、ローカルのファイルシステムで作れます。スタンドアローンモードのDrillには、dfs.tmpワークスペースが付いてきます。これをビューの作成に使用できます。 (あるいは、ご自分のローカルまたは分散型ファイルシステムで独自のワークスペースを定義しても構いません)。データを論理的ビューではなく物理的なままにしておきたい場合は、CREATE TABLE AS SELECT構文を使うことができます。

0: jdbc:drill:zk=local> create or replace view dfs.tmp.businessreviews as Select b.name,b.stars,b.state,b.city,r.votes.funny,r.votes.useful,r.votes.cool, r.`date` from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_business.json` b , dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_review.json` r where r.business_id=b.business_id
+------------+------------+
|     ok  |  summary   |
+------------+------------+
| true    | View 'businessreviews' created successfully in 'dfs.tmp' schema |
+------------+------------+

 

ビューから合計記録数を取得
0: jdbc:drill:zk=local> select count(*) as Total from dfs.tmp.businessreviews;
+------------+
|   Total   |
+------------+
| 1125458 |
+------------+

これらのクエリに加え、DrillのSQL機能を使えば、格段に詳細な分析が可能になります。マニュアルでクエリを記述したくない場合は、Tableau/MicrostrategyなどのBI/分析ツールを使って、生ファイル/Hive/HBaseデータのクエリを行い、Drill ODBC/JDBCドライバーを使って直接Drillで作成したビューをクエリすることが可能です。
Apache Drillでは、データ分析に、これまでのSQL技術では見たことのないような自由と柔軟性を提供することを目的としています。当コミュニティでは、今後のリリースに向けて、ネスト化したデータや変化するスキーマを使ったデータサポート周り対して更にユニークな機能を開発中です。
例えば、更に詳細なSQL機能を適用できるように、半構造化データを動的に関連付けするために使用できる新たな「結合」機能を開発中です (0.7の新機能)。クエリに対する結合機能の使用法をご紹介します。

それぞれのビジネスについて、結合されたカテゴリー一覧を取得
0: jdbc:drill:zk=local> select name, flatten(categories) as category from dfs.`/users/nrentachintala/Downloads/yelp/yelp_academic_dataset_business.json`  limit 20;
+------------+------------+
|    name |   category   |
+------------+------------+
| Eric Goldberg, MD | Doctors |
| Eric Goldberg, MD | Health & Medical |
| Pine Cone Restaurant | Restaurants |
| Deforest Family Restaurant | American (Traditional) |
| Deforest Family Restaurant | Restaurants |
| Culver's   | Food       |
| Culver's   | Ice Cream & Frozen Yogurt |
| Culver's   | Fast Food  |
| Culver's   | Restaurants |
| Chang Jiang Chinese Kitchen | Chinese |
| Chang Jiang Chinese Kitchen | Restaurants |
| Charter Communications | Television Stations |
| Charter Communications | Mass Media |
| Air Quality Systems | Home Services |
| Air Quality Systems | Heating & Air Conditioning/HVAC |
| McFarland Public Library | Libraries  |
| McFarland Public Library | Public Services & Government |
| Green Lantern Restaurant | American (Traditional) |
| Green Lantern Restaurant | Restaurants |
| Spartan Animal Hospital | Veterinarians |
+------------+------------+

 

ビジネスレビューで使用されるトップのカテゴリ
0: jdbc:drill:zk=local> select celltbl.catl, count(celltbl.catl) categorycnt from (select flatten(categories) catl from dfs.`/users/nrentachintala/Downloads/yelp_dataset_challenge_academic_dataset/yelp_academic_dataset_business.json` )  celltbl group by celltbl.catl order by count(celltbl.catl) desc limit 10 ;
+------------+-------------+
|    catl    | categorycnt |
+------------+-------------+
| Restaurants | 14303       |
| Shopping   | 6428        |
| Food       | 5209        |
| Beauty & Spas | 3421        |
| Nightlife  | 2870        |
| Bars       | 2378        |
| Health & Medical | 2351        |
| Automotive | 2241        |
| Home Services | 1957        |
| Fashion    | 1897        |
+------------+-------------+

追加機能やDrillコミュニティの今後の活動について、新しい情報が入り次第、随時お伝えします。

こちらの記事もおすすめです