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

## 概要

- 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<STRING> を取得することができる。

```
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](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF)

### 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<K.V>)
- Description: Returns the number of elements in the map type.

### explode 関数

- Row-set column types: T
- Name(Signature): explode(ARRAY<T> 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<string>)
- 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](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView)

> 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.

## 参考資料

- [LanguageManual UDF \- Apache Hive \- Apache Software Foundation](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF)
- [LanguageManual LateralView \- Apache Hive \- Apache Software Foundation](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView)
- [Hiveの文字列関数など \| OpenGroove](https://open-groove.net/hive/hive-string-functions/)
- [Hive 複数要素を展開して、複数行にする\(LATERAL VIEW/split/explode\) \- Qiita](https://qiita.com/daifuku_mochi2/items/35581b05c16bc3825f7e)
- [\[Hive\] Hive で配列の集計をするときに使う LATERAL VIEW の構文がなんか頭に入ってこない \- 雑文発散\(2014\-09\-09\)](https://suzuki.tdiary.net/20140909.html#p01)