研修/データビジュアライゼーション/Hive講習

Top / 研修 / データビジュアライゼーション / Hive講習

データビジュアライゼーションワークショップ †

↑

Hive研修 †

上級者向け
- HiveのWebインターフェース:http://nicodata.info/
- スキーマ情報:日記/2013-07-26/ニコニコデータビューアー向けテーブルスキーマ
- Hiveの関数一覧:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

↑

sqLの基礎 †

SQLはあるデータの固まりを取り出す一連の手続きです

例えば、

SELECT * FROM nicodata.tags WHERE tag="動画" LIMIT 10

これで、tagデータのなかからタグが動画の情報を取得します。

SELECT smid FROM nicodata.tags WHERE tag="動画" LIMIT 10

こうすればタグが動画のsmidをみる事が出来ます。

もしこれを、プログラムでやったなら？

var dataset = open("tagdata","rb");
var smids=new array();
foreach (dataset as data){
  if(data.tag=="動画"){
    smids.append(data.smid);
    if (smids.length>10){break;}
  }
}
print skids;

みたいな複雑な書き方になる。 SQLとは、このような複雑さを隠して、楽にデータを取られるようにしています。

↑

SQLの目的 †

目標は、多様なデーセットから、必要とするデータを取り出す事にある。

選択

SELECT * FROM nicodata.video_info WHERE to_date(updated_time) > to_date('2012-01-01 00:00:00') LIMIT 10;

動画投稿日が、2012-01-01以降の物を選択して、10件を上限に表示せよ。

集約

SELECT to_date(update_time),count(*) FROM nicodata.video_info GROUP BY to_date(update_time)

動画投稿日ごとに、集約して、投稿数を表示せよ。

↑

SQLのサンプル集 †

「病院が来い」を含むコメントを抜き出す

SELECT * FROM nicodata.comment_data_sampling100 WHERE comment_string LIKE "%病院が来い%" LIMIT 10

「病院が来い」を含むコメントがいくつあるか調べる

SELECT count(*) FROM nicodata.comment_data_sampling100 WHERE comment_string LIKE "%病院が来い%"

「病院が来い」を含むコメントが多い動画を抜き出し、多い順に並べる。
- 病院が来いというコメントを含む動画を、動画ごとに集約し、コメント数で多い順にソートする。
```
SELECT smid,count(*) as cnt FROM nicodata.comment_data_sampling100 WHERE comment_string LIKE "%病院が来い%" GROUP BY smid ORDER BY cnt DESC LIMIT 20
```

↑

調べてみよう †

8888の使用頻度は、月別にどう変化した？
他の動画idに言及したコメントを拾い上げてみよう。
言葉の始まりはいつからだろう？
最も多いタグは何かな？
動画の投稿時間と再生数に関係はあるかな？
煽りコメントから、そのコメントを主に使うユーザーの年齢は類推できるか？
コメントはいつが多い？

↑

サンプルSQL ] [#i4e89497] †

他の動画のコメントに現れた動画ID

select a.sid as asid,count(a.sid) as cnt FROM (
select regexp_extract(comment_string,"(sm[0-9]+)",0) as sid from 
nicodata.comment_data where comment_string rlike "sm[0-9]+" AND regexp_extract(comment_string,"(sm[0-9]+)",0) != smid 
) a GROUP BY a.sid ORDER By cnt DESC LIMIT 200