#topicpath
* データビジュアライゼーションワークショップ [#w2d20cb5]
* Hive研修 [#ofcb8175]
- 上級者向け
-- HiveのWebインターフェース:http://nicodata.info/
-- スキーマ情報:[[日記/2013-07-26/ニコニコデータビューアー向けテーブルスキーマ]]
-- Hiveの関数一覧:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
* sqLの基礎 [#m36d5510]
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の目的 [#z006e978]
目標は、多様なデーセットから、必要とするデータを取り出す事にある。
選択
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のサンプル集 [#gd8af71d]
-「病院が来い」を含むコメントを抜き出す
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
* 調べてみよう [#v041653e]
- 8888の使用頻度は、月別にどう変化した?
- 他の動画idに言及したコメントを拾い上げてみよう。
- 言葉の始まりはいつからだろう?
- 最も多いタグは何かな?
- 動画の投稿時間と再生数に関係はあるかな?
- 煽りコメントから、そのコメントを主に使うユーザーの年齢は類推できるか?
- コメントはいつが多い?
* サンプルSQL [#a4fe702b]] [#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