Apache Hive で SELECT した結果から ARRAY を作る

Apahe Hive を使っていて、テーブルから SELECT してきた結果から ARRAY 型のカラムを作る方法が分からなくて調べた。結論から先に述べると COLLECT_LIST() を使えば良い。

使った環境は次の通り。

$ cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core) 
$ uname -r
3.10.0-862.3.2.el7.x86_64
$ hive --version
Hive 2.3.3
Git git://daijymacpro-2.local/Users/daijy/commit/hive -r 8a511e3f79b43d4be41cd231cf5c99e43b248383
Compiled by daijy on Wed Mar 28 16:58:33 PDT 2018
From source with checksum 8873bba6c55a058614e74c0e628ab022
$ hadoop version
Hadoop 2.9.1
Subversion https://github.com/apache/hadoop.git -r e30710aea4e6e55e69372929106cf119af06fd0e
Compiled by root on 2018-04-16T09:33Z
Compiled with protoc 2.5.0
From source with checksum 7d6d2b655115c6cc336d662cc2b919bd
This command was run using /home/vagrant/hadoop-2.9.1/share/hadoop/common/hadoop-common-2.9.1.jar

ARRAY 型について

以前、このブログで ARRAY 型について書いたことがある。ただ、この記事では SELECT してきた結果から ARRAY を作る方法については扱わなかった。

blog.amedama.jp

上記の記事にあるけど Apache Hive における ARRAY 型というのは、公式にジェイウォークをするための方法という感じ。使うと一行のレコードに複数行の情報を詰め込むことができる。 Apache Hive はアーキテクチャ的に JOIN の処理が割と苦手なので、こういう機能を使う必要が出てくることもある。

下準備

まずは Hive のシェルを起動しておく。

$ hive

動作確認用のテーブルとして、名前 (name) と性別 (gender) の入るテーブル (users) を作っておく。

hive> CREATE TABLE users (
    >   name STRING,
    >   gender STRING
    > );
OK
Time taken: 0.06 seconds

適当な名前と性別でユーザを追加する。

hive> INSERT INTO TABLE users
    > VALUES
    >   ('alice', 'female'),
    >   ('bob', 'male'),
    >   ('carol', 'female');
...
OK
Time taken: 19.046 seconds

これで準備ができた。

SELECT した結果から ARRAY を作ってみる

ユーザを性別 (gender) で集計した上で、名前 (name) も一緒に確認したいという状況で考えてみよう。この場合、性別と一緒に名前が ARRAY で得られると嬉しい。

この場合 GROUP BY に gender を指定した上で COLLECT_LIST() を使う。

hive> SELECT
    >   gender,
    >   COLLECT_LIST(name) AS names
    > FROM users
    > GROUP BY gender;
...
OK
female  ["alice","carol"]
male    ["bob"]
Time taken: 25.689 seconds, Fetched: 2 row(s)