Apache Hive の ARRAY 型を試す - CUBE SUGAR CONTAINER

Apache Hive には ARRAY 型というデータタイプがある。これは一般的なプログラミング言語でいえば配列に相当するもの。ようするに、文字列や数値といったデータを一つのレコードに複数格納できる。リレーショナルデータベースのアンチパターンであるジェイ・ウォークをするために用意されたようなものだ。ただ、Apache Hive は JOIN の性能が高くないことから、こういったデータタイプを使わざるを得ない場合もあるということだろう。使ってみたところ一般的なデータタイプとはちょっと違ってクセがあったので、それについてメモしておく。

使った環境は次の通り。 Apache Hive や Hadoop のインストール部分は省略する。

$ cat /etc/redhat-release 
CentOS Linux release 7.4.1708 (Core)
$ uname -r
3.10.0-693.5.2.el7.x86_64
$ hadoop version
Hadoop 2.8.3
Subversion https://git-wip-us.apache.org/repos/asf/hadoop.git -r b3fe56402d908019d99af1f1f4fc65cb1d1436a2
Compiled by jdu on 2017-12-05T03:43Z
Compiled with protoc 2.5.0
From source with checksum 9ff4856d824e983fa510d3f843e3f19d
This command was run using /home/vagrant/hadoop-2.8.3/share/hadoop/common/hadoop-common-2.8.3.jar
$ hive --version
Hive 2.3.2
Git git://stakiar-MBP.local/Users/stakiar/Desktop/scratch-space/apache-hive -r 857a9fd8ad725a53bd95c1b2d6612f9b1155f44d
Compiled by stakiar on Thu Nov 9 09:11:39 PST 2017
From source with checksum dc38920061a4eb32c4d15ebd5429ac8a

下準備

まずは Hive の CLI を起動しておく。

$ hive
hive> set hive.cli.print.header=true;

ARRAY 型について

ARRAY 型では、以下のように ARRAY でくくって複数の値をカンマ区切りで格納できる。

hive> SELECT ARRAY("a", "b");
OK
_c0
["a","b"]
Time taken: 8.74 seconds, Fetched: 1 row(s)

テーブルを作る

ARRAY 型を使ってテーブルを作るときは、中に入るデータタイプを指定する。

hive> CREATE TABLE users (
    >   name STRING,
    >   emails ARRAY<STRING>
    > );
OK
Time taken: 0.997 seconds

このように、ちゃんとテーブルができた。

hive> SHOW CREATE TABLE users;
OK
createtab_stmt
CREATE TABLE `users`(
  `name` string, 
  `emails` array<string>)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://master:9000/user/hive/warehouse/users'
TBLPROPERTIES (
  'transient_lastDdlTime'='1514884223')
Time taken: 0.405 seconds, Fetched: 13 row(s)

レコードを追加する

レコードを追加するときは、ちょっとした一工夫が必要になる。

普通にデータを追加しようとしても、次のようにエラーになってしまう。

hive> INSERT INTO users VALUES ("Alice", ARRAY("alice@example.jp", "alice@example.com"));
FAILED: SemanticException [Error 10293]: Unable to create temp file for insert values Expression of type TOK_FUNCTION not supported in insert/values

データを追加するときは、先ほど SELECT 文であれば正しくデータが表示できたことを応用する。

hive> SELECT "Alice", ARRAY("alice@example.jp", "alice@example.com");
OK
_c0 _c1
Alice   ["alice@example.jp","alice@example.com"]
Time taken: 0.12 seconds, Fetched: 1 row(s)

INSERT INTO <table> に続けて、上記の SELECT 文を入れてやれば良い。

hive> INSERT INTO users
    >   SELECT "Alice", ARRAY("alice@example.jp", "alice@example.com");
...
OK
_c0 _c1
Time taken: 27.894 seconds

今度は、ちゃんと追加できた。

hive> SELECT * FROM users;
OK
users.name  users.emails
Alice   ["alice@example.jp","alice@example.com"]
Time taken: 1.308 seconds, Fetched: 1 row(s)

データを集計する

データを集計するときも一工夫が必要になる。

ひとまず、別のレコードも追加しておこう。

hive> INSERT INTO users
    >   SELECT "Bob", ARRAY("bob@example.net", "bob@example.org");
...
OK
_c0 _c1
Time taken: 20.907 seconds

テーブルには二人分のデータが入っている。ただ、中身が ARRAY 型のままでは集計できない。

hive> SELECT emails FROM users;
OK
emails
["alice@example.jp","alice@example.com"]
["bob@example.net","bob@example.org"]
Time taken: 0.203 seconds, Fetched: 2 row(s)

そんなときは LITERAL VIEW と explode() 関数を使って、中身を展開してやる。例えば以下では emails カラムの中身を email として展開している。

hive> SELECT *
    > FROM users
    > LATERAL VIEW explode(emails) users AS email;
OK
users.name  users.emails    users.email
Alice   ["alice@example.jp","alice@example.com"]  alice@example.jp
Alice   ["alice@example.jp","alice@example.com"]  alice@example.com
Bob ["bob@example.net","bob@example.org"] bob@example.net
Bob ["bob@example.net","bob@example.org"] bob@example.org
Time taken: 0.093 seconds, Fetched: 4 row(s)

展開できてしまえばこちらのもの。例えばユーザがメールアドレスをいくつ持っているのか集計してみよう。ユーザごとに GROUP BY した上で、展開した email カラムを COUNT() 関数にかける。

hive> SELECT
    >   name,
    >   COUNT(email) AS email_count
    > FROM users
    > LATERAL VIEW explode(emails) users AS email
    > GROUP BY name;
...
OK
name    email_count
Alice   2
Bob 2
Time taken: 27.477 seconds, Fetched: 2 row(s)

ばっちり。

外部ファイルからデータを読み込む

外部ファイルから ARRAY 型にデータを読み込むときはテーブルを作るときに一工夫が必要になる。具体的には COLLECTION ITEMS TERMINATED BY を使って区切り文字を指定する。

hive> CREATE TABLE users (
    >     name STRING,
    >     emails ARRAY<STRING>
    > )
    > ROW FORMAT DELIMITED 
    > FIELDS TERMINATED BY ','  
    > COLLECTION ITEMS TERMINATED BY '$'
    > STORED AS TEXTFILE;
OK
Time taken: 0.051 seconds

読み込むデータを次のように用意しておく。

$ cat << 'EOF' > users.csv 
Alice,alice@example.jp$alice@example.com
Bob,bob@example.net$bob@example.org
EOF

LOAD DATA を使って上記のファイルを users テーブルに読み込む。

hive> LOAD DATA LOCAL INPATH '/home/vagrant/users.csv' INTO TABLE users;
Loading data to table default.users
OK
Time taken: 0.916 seconds

テーブルの中身を確認するとデータが読み込まれていることが分かる。

hive> SELECT * FROM users;
OK
users.name  users.emails
Alice   ["alice@example.jp","alice@example.com"]
Bob ["bob@example.net","bob@example.org"]
Time taken: 0.095 seconds, Fetched: 2 row(s)

HDFS 上のファイルについても確認しておこう。

hive> SHOW CREATE TABLE users;
OK
createtab_stmt
CREATE TABLE `users`(
  `name` string, 
  `emails` array<string>)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES ( 
  'colelction.delim'='$', 
  'field.delim'=',', 
  'serialization.format'=',') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://master:9000/user/hive/warehouse/users'
TBLPROPERTIES (
  'transient_lastDdlTime'='1514981348')
Time taken: 0.035 seconds, Fetched: 17 row(s)

すると、先ほどのファイルがそのまま保存されていることが分かる。

$ hdfs dfs -ls /user/hive/warehouse/users/
Found 1 items
-rwxrwxr-x   2 vagrant supergroup         77 2018-01-03 12:09 /user/hive/warehouse/users/users.csv
$ hdfs dfs -cat /user/hive/warehouse/users/users.csv
Alice,alice@example.jp$alice@example.com
Bob,bob@example.net$bob@example.org

いじょう。