概要

Apache Hive の split 関数でカンマ区切りの文字列を処理するサンプルを書く
検証環境: Apache Hadoop 3.3.0 + Apache Hive 3.1.2 + Java 8 (AdoptOpenJDK 1.8.0_265) + macOS Catalina

split 関数でカンマ区切りの文字列を処理する

以下のように foo_table テーブルの foo_data カラムにカンマ区切りのデータがある場合を想定。

hive> set hive.cli.print.header=true;
hive> select * from foo_table;
OK
foo_table.foo_id    foo_table.foo_data
1    aaa,bbb,ccc
2    111,222,333,444,555
Time taken: 0.179 seconds, Fetched: 2 row(s)

split 関数を使うと区切り文字列 (正規表現も指定可能) を指定して文字列の配列 ARRAY を取得することができる。

hive> select split(foo_data, ',') from foo_table;
OK
_c0
["aaa","bbb","ccc"]
["111","222","333","444","555"]
Time taken: 0.124 seconds, Fetched: 2 row(s)

配列の最初の要素には [0] でアクセスできる。

hive> select split(foo_data, ',')[0] from foo_table;
OK
_c0
aaa
111
Time taken: 0.149 seconds, Fetched: 2 row(s)

2つ目の要素を出力。

hive> select split(foo_data, ',')[1] from foo_table;
OK
_c0
bbb
222
Time taken: 0.277 seconds, Fetched: 2 row(s)

size 関数で配列の要素の数を取得できる。

hive> select size(split(foo_data, ',')) from foo_table;
OK
_c0
3
5
Time taken: 0.278 seconds, Fetched: 2 row(s)

size 関数で配列の要素数を取得できるので、これを利用して最後の要素を取得できる。

hive> select split(foo_data, ',')[size(split(foo_data, ',')) - 1] from foo_table;
OK
_c0
ccc
555
Time taken: 0.196 seconds, Fetched: 2 row(s)

concat_ws で区切り文字と配列を指定して文字列を生成できる。

hive> select concat_ws('|', split(foo_data, ',')) from foo_table;
OK
_c0
aaa|bbb|ccc
111|222|333|444|555
Time taken: 0.386 seconds, Fetched: 2 row(s)

explode 関数を使うと配列を行に変換できる。

hive> select explode(split(foo_data, ',')) from foo_table;
OK
col
aaa
bbb
ccc
111
222
333
444
555
Time taken: 0.089 seconds, Fetched: 8 row(s)

explode 関数と lateral view を組み合わせると行に変換した配列と元の入力行を結合できる。

hive> select foo_id, foo_splited_data, foo_data
    > from foo_table
    > lateral view
    > explode(split(foo_data, ',')) foo_table as foo_splited_data;
OK
foo_id    foo_splited_data    foo_data
1    aaa    aaa,bbb,ccc
1    bbb    aaa,bbb,ccc
1    ccc    aaa,bbb,ccc
2    111    111,222,333,444,555
2    222    111,222,333,444,555
2    333    111,222,333,444,555
2    444    111,222,333,444,555
2    555    111,222,333,444,555
Time taken: 0.294 seconds, Fetched: 8 row(s)

公式ドキュメントによる情報

LanguageManual UDF - Apache Hive - Apache Software Foundation

split 関数

Return type: array
Name(Signature): split(string str, string pat)
Description: Splits str around pat (pat is a regular expression).

size 関数

Return type: int
Name(Signature): size(Map)
Description: Returns the number of elements in the map type.

explode 関数

Row-set column types: T
Name(Signature): explode(ARRAY a)
Description: Explodes an array to multiple rows. Returns a row-set with a single column (col), one row for each element from the array.

concat_ws 関数

Return type: concat_ws
Name(Signature): concat_ws(string SEP, array)
Description: Like concat_ws() above, but taking an array of strings. (as of Hive 0.9.0)

array

Operator: A[n]
Operand types: A is an Array and n is an int
Description: Returns the nth element in the array A. The first element has index 0. For example, if A is an array comprising of ['foo', 'bar'] then A[0] returns 'foo' and A[1] returns 'bar'.

lateral view

LanguageManual LateralView - Apache Hive - Apache Software Foundation

Lateral view is used in conjunction with user-defined table generating functions such as explode(). As mentioned in Built-in Table-Generating Functions, a UDTF generates zero or more output rows for each input row. A lateral view first applies the UDTF to each row of base table and then joins resulting output rows to the input rows to form a virtual table having the supplied table alias.

参考資料