Azure HDInsight Spark を試してみた3

JMT西森です。

今回はデフォルトで用意されているサンプルデータを用いて、
Apache Hive(以後、Hive)を使用して色々と遊んでみたいと思います。

まず、Hiveについて少しだけ説明します。

HiveはHadoop上で動作可能な、データの集約・問い合わせ・分析が可能なデータウェアハウスソフトウェアです。
SQLによく似た記述方式(厳密にはサポートしていません)であるHiveQLを使用し、Select文をMapReduce方式に
置き換えて実行することができます。

但しあくまでもSQL”ライク”なだけなので、RDBMS上で使用するSQLクエリがそのまま使用できるわけではありません。

詳細については下記が非常に参考になります。
# 本ブログ作成にあたっても非常に参考とさせて頂いています
・Wikipedia: https://ja.wikipedia.org/wiki/Apache_Hive
・Hiveメモ: http://www.ne.jp/asahi/hishidama/home/tech/apache/hive/index.html

それでは本題。サンプルデータで遊んでみましょう。

Azure Console 画面から「SPARK DASHBOARD」を選択(ID/Pass入力)し、
表示される画面のタブから「Hive Editor」を開きます。

20151127_001

デフォルトではサンプルデータのSelect文が記載されています。サンプルデータは
既にテーブルとして作成されており、「hivesampledata」という名前になっています。

なお前回も記載しましたが、実体はStorage Account上にあるテキストファイルです。
先程の「File Browser」から辿っていくことで、実体ファイルが確認出来ます。
「File Browser」⇒「アカウント名」⇒「コンテナ名」⇒「hive」⇒「warehouse」⇒「hivesampletable」
と辿ることで「HiveSampleData.txt」が見つけられると思いますので、
まずはそれを開いて中身を見てみましょう。なお、約5MB程度のデータです。

20151127_002

20151127_003

時間や文字コード、iPhoneやSamsung、California、United States等があります。
各データ列がどのような意味を持っているのか、テーブル構造を調べます。
Hive Editorにて以下クエリを発行し、完了後に結果を見てみましょう。
> desc hivesampletable

20151127_004

ジョブが完了した(StatusがComplated)ら、「View Details」を選択します。
20151127_005

Job Outputで列の情報が色々と取得できたかと思います。
「querydwelltime」や「sessionid」、「sessionpagevieworder」等があることから、
あるWebサイトで、ページごとの接続元デバイス情報、処理時間等をまとめた
ログデータであるように読み取れるかと思います。

それでは色々とやってみましょう。以後は
HiveQLを入力 ⇒ Submitをクリック ⇒ 完了を待つ ⇒ 完了したらView Detailsで確認
という流れが続きますので、上記手順は割愛して記載します。

まず、クライアントの数を数えることにします。ただのカウントだと重複レコードもそのまま
カウントされてしまうため、clientidをdistinctしてみたいと思います。以下HiveQLを発行してみましょう。
> select count(distinct clientid) from hivesampletable;

結果は「14517」でした。ちなみに全件数は「59793」です。

20151127_006

ちなみに、どれくらい処理に時間がかかったのでしょうか?
Job Logの最後のほうを参照すると、
Time taken: 60.285 seconds, Fetched: 1 row(s)
とあるので、全体の処理時間としては約1分程だったようです。

次は、en-US以外のマーケット(国?)からのアクセスしてきたデバイス数を、
それぞれのマーケットごとに集計してみます。以下HiveQLを発行してみましょう。
> select market, count(distinct clientid) from hivesampletable where market “en-US” group by market;

20151127_007

en-GBが一番多く、616件でした。ちなみにこれ、イギリスです。

次は、querydwelltimeの平均値を取得してみます。
以下HiveQLを発行してみましょう。
> select avg(querydwelltime) from hivesampletable where querydwelltime is not null;

20151127_008

26821…単位がms(マイクロ秒)だとしても、長すぎる気がしますね。

前のテキストを眺めてみても1桁2桁が多くあるので、一部の巨大な値の影響でしょうか。

querydwelltimeが本値以上となっているレコードを探します。
以下HiveQLを発行してみましょう。
> select * from hivesampletable where querydwelltime > 26821

20151127_009

16件もありました。そりゃぁ平均値が押し上げられるわけですね…。
querydwelltime の値の分布を力技で調べてみます。
以下HiveQLを発行してみましょう。
> select 1, count(*) from hivesampletable where querydwelltime > ‘1’;
> select 10, count(*) from hivesampletable where querydwelltime > ’10’;
> select 50, count(*) from hivesampletable where querydwelltime > ’50’;
> select 100, count(*) from hivesampletable where querydwelltime > ‘100’;
> select 500, count(*) from hivesampletable where querydwelltime > ‘500’;
> select 1000, count(*) from hivesampletable where querydwelltime > ‘1000’;
> select 5000, count(*) from hivesampletable where querydwelltime > ‘5000’;
> select 10000, count(*) from hivesampletable where querydwelltime > ‘10000’;
ちなみに複数行を一度に実行することが可能です。その場合は、最後にセミコロンを入れましょう。

20151127_010

値が500より大きいレコードの総数が 827 と全体(59793)の約2%に相当するようです。
では、値が500未満のものを対象として、改めて平均値を調べてみます。
以下HiveQLを発行してみましょう。
select avg(querydwelltime) from hivesampletable where querydwelltime < 500;

20151127_011

約18と、なんとなくそれっぽい値が取得できたように思えます。

如何でしょうか。基本的なSQL構文さえ理解していれば、テキストデータに対して
様々な視点からデータ分析・調査できることが分かったかと思います。

なお今回はHiveQLを使って情報を取得しましたが、前回記載したJUPYTER NOTEBOOKや
ZEPEPLIN NOTEBOOK を使うことで、もう少しスマートに解析が出来て、かつグラフ化等の
可視化も出来るようになります。

今回はサンプルデータを使いましたが、出来れば手元にある実データを使いたいですよね。
次回は実データをこの環境にどう取り込むのか、HiveQLで実現する方法を記載したいと思います。

LINEで送る