Skip to content
Published on

Apache Hive Query Example (MovieLens)

🇰🇷KO🇺🇸EN🇯🇵JA
Authors
  • Name
    Twitter

Overview

一般的にHDFSのデータを扱う方法は、MapReduceプログラムを作成することです。MapReduceは自由度は高いですが、適切に使いこなすには多くの学習と試行錯誤が必要です。そして最大の欠点は、難しいということです。そのため生産性が低くなります。Hiveは開発者に親しみやすいSQLを記述すると、それをMapReduceジョブに変換してくれます。（最近では、HiveのエンジンとしてMapReduceではなく、はるかに高速なSparkやTezを使用するとのことです。）そのため、SQLだけを知っている開発者でもHDFS上のデータを保存したり、照会したり、有用な情報を抽出できるようになります。MovieLensが提供するデータを使ってHive Queryでデータを操作しながら、Hiveがどれほど便利かを見ていきましょう。

データの準備

ダウンロード

GroupLensというサイトが提供するhttps://grouplens.org/datasets/movielens/から100kデータを使用します。

grouplense
wget https://files.grouplens.org/datasets/movielens/ml-100k.zip
unzip ml-100k.zip
copy-to-hdfs
hadoop fs -cp ml-100k/u.data /tmp

Hiveテーブルの作成

hive shellを起動し、以下のコマンドでu_dataテーブルを作成します。

create-table
use default

CREATE TABLE IF NOT EXISTS u_data(
    userid INT,
    movieid INT,
    rating INT,
    unixtime TIMESTAMP
    )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

LOAD DATA INPATH 'hdfs:///tmp/u.data' overwrite into table u_data;

u_dataテーブルが正常に作成されたか確認します。

hive> show databases;
OK
default
userdb
Time taken: 0.312 seconds, Fetched: 2 row(s)
hive> use default;
OK
Time taken: 0.023 seconds
hive> show tables;
OK
u_data
Time taken: 0.044 seconds, Fetched: 1 row(s)

HDFS上でも該当テーブルが正常に作成されたか確認します。

root@ubuntu01:~# hdfs dfs -ls /user/hive/warehouse
Found 2 items
drwxr-xr-x   - root supergroup          0 2022-11-22 18:52 /user/hive/warehouse/u_data
drwxr-xr-x   - root supergroup          0 2022-11-22 18:43 /user/hive/warehouse/userdb.db

Query

table schema

u_data
hive> describe  u_data;
OK
userid              	int
movieid             	int
rating              	int
unixtime            	timestamp
Time taken: 0.087 seconds, Fetched: 4 row(s)

row count

u_dataテーブルに合計何行あるかを出力するクエリを実行してみましょう。

hive> select count(*) from u_data;
OK
100000
Time taken: 20.914 seconds, Fetched: 1 row(s)

u_dataテーブルには100k（10万件の）行があることが確認できます。

distinct movieid

hive> select count(distinct movieid) from u_data;
OK
1682

映画の種類

distinctコマンドを使ってmovieidの数を求めることができます。

hive> select count(distinct movieid) from u_data;
OK
1682

映画別の評価

映画別の平均評価を求めるには、以下のコマンドを使用できます。 GROUP BYコマンドで集計演算を実行できます。

hive> select movieid, avg(rating) from u_data group by movieid;

映画別評価ランキング

SORT BY my_column (ASC|DESC)コマンドを使用すると、my_columnカラムを基準に降順でソートおよび出力が可能です。

hive> select movieid, avg(rating) as avg_rating from u_data group by movieid sort by avg_rating DESC;

Reference

Discuss on Twitter • View on GitHub