HiveQL ひっかけ問題10本ノック！！(6問 ~ 10問目)

## はじめに
Hiveとは、HiveqlというSQLライクなドメイン固有言語を提供するクエリエンジンで、Hadoopというデータ処理基盤で動作するものです。この記事では、私が業務で初めてHiveを触ってみて、ハマった内容を元に、10本ノックを作ってみました。

1 ~ 5問目は[こちらページ](https://qiita.com/tobita_yoshiki/private/d8576077fcb83c5b15fc)に掲載しております。

## 環境構築
こちらの記事を参考に環境構築すると、Dockerでカンタンにbeelineが利用できるようになります。  
参考記事: https://qiita.com/letusfly85/items/b4e5fc67c9566765b794  
(記事作成者の方、ありがとうございます！！)

参考記事から[docker-compose.yml](https://github.com/letusfly85/docker-hiveserver2/blob/master/docker-compose/docker-compose.yml)を入手して以下のコマンドを打つとHiveQLの実行環境が得られます。

```
$ docker-compose -f docker-compose.yml up -d
$ docker exec -it docker-compose_hiveserver2_1 /bin/bash
$ beeline -u jdbc:hive2://localhost:10000 hive hive org.apache.hive.jdbc.HiveDriver
```

※ この記事の10本ノックは上記の環境でクエリを実行することを前提に作りました。上記の環境以外でクエリを実行すると、10本ノックの解答例が記載通りに動かない場合があります。

## 6. IN句
それでは第6問目です。
### 問題
次のようなテーブル`tbl1`, `tbl2`が存在するとします。

```sql
CREATE TEMPORARY TABLE tbl1 (x INT);
INSERT INTO tbl1 VALUES (-1), (0), (1), (2), (3), (4), (5);
CREATE TEMPORARY TABLE tbl2 (x INT);
INSERT INTO tbl2 VALUES (1), (2);

0: jdbc:hive2://localhost:10000> SELECT * FROM tbl1;
+---------+--+
| tbl1.x  |
+---------+--+
| -1      |
| 0       |
| 1       |
| 2       |
| 3       |
| 4       |
| 5       |
+---------+--+
7 rows selected (0.071 seconds)
0: jdbc:hive2://localhost:10000> SELECT * FROM tbl2;
+---------+--+
| tbl2.x  |
+---------+--+
| 1       |
| 2       |
+---------+--+
2 rows selected (0.042 seconds)
```

このとき、`tbl1`のレコードの内、`tbl2`のレコードと重複しているものをSELECTするクエリを作成してください。

```sql:欲しい結果
+--------+--+
| tbl.x  |
+--------+--+
| 1      |
| 2      |
+--------+--+
```

ただし、作成するSELECT文は以下のクエリの`???`の部分を埋めて、作成してください。

```sql
WITH tbl AS (
    ???
)
SELECT * FROM tbl;
```

### 誤答例
サブクエリの中では、IN句を使用できません。。

```sql
0: jdbc:hive2://localhost:10000> WITH tbl AS (
0: jdbc:hive2://localhost:10000>     SELECT * FROM tbl1 WHERE x IN (SELECT * FROM tbl2)
0: jdbc:hive2://localhost:10000> )
0: jdbc:hive2://localhost:10000> SELECT * FROM tbl;
Error: Error while compiling statement: FAILED: SemanticException Line 2:29 Unsupported SubQuery Expression 'x' in definition of CTE tbl [
SELECT * FROM tbl1 WHERE x IN (SELECT * FROM tbl2)
] used as tbl at Line 2:18: Correlating expression cannot contain unqualified column references. (state=42000,code=40000)
```

### 解答例
解答例ではIN句の代わりにLEFT SEMI JOINという句を使いました。

```sql
WITH tbl AS (
    SELECT * FROM tbl1 LEFT SEMI JOIN tbl2 t on tbl1.x = t.x
)
SELECT * FROM tbl;
```

## 7. OR句
### 問題
次のようなテーブル`tbl1`と`tbl2`が存在したとします。

```sql
CREATE TEMPORARY TABLE tbl1 (x INT, y INT);
INSERT INTO tbl1 VALUES (-1, 0), (0, 1), (1, 2), (3, 4);

CREATE TEMPORARY TABLE tbl2 (x INT);
INSERT INTO tbl2 VALUES (1), (2);

0: jdbc:hive2://localhost:10000> SELECT * FROM tbl1;
+---------+---------+--+
| tbl1.x  | tbl1.y  |
+---------+---------+--+
| -1      | 0       |
| 0       | 1       |
| 1       | 2       |
| 3       | 4       |
+---------+---------+--+
4 rows selected (0.039 seconds)
0: jdbc:hive2://localhost:10000> SELECT * FROM tbl2;
+---------+--+
| tbl2.x  |
+---------+--+
| 1       |
| 2       |
+---------+--+
2 rows selected (0.044 seconds)
```

このとき、`tbl1.x`と`tbl2.x`で値が一致しているレコードと、`tbl1.y`と`tbl2.x`で値が一致しているレコードをSELECTするクエリを作成してください。

```sql:欲しい結果
+----+----+--+
| x  | y  |
+----+----+--+
| 0  | 1  |
| 1  | 2  |
| 1  | 2  |
+----+----+--+
```

### 誤答例
JOIN句の中にOR句を書くとエラーが発生します。

```sql
0: jdbc:hive2://localhost:10000> SELECT * FROM tbl1 LEFT SEMI JOIN tbl2 ON tbl1.x = tbl2.x OR tbl1.y = tbl2.x;
Error: Error while compiling statement: FAILED: SemanticException [Error 10019]: Line 1:42 OR not supported in JOIN currently 'x' (state=42000,code=10019)
```

### 解答例
解答例では、OR句の使用を避けるためにANDとUNION ALLを使用しました。

```sql
SELECT
    unionResult.x AS x,
    unionResult.y AS y
FROM (
    SELECT tbl1.x, tbl1.y FROM tbl1 LEFT SEMI JOIN tbl2 ON tbl1.x = tbl2.x AND tbl1.y = tbl2.x
    UNION ALL
    SELECT tbl1.x, tbl1.y FROM tbl1 JOIN tbl2 ON tbl1.x = tbl2.x WHERE tbl1.y != tbl2.x
    UNION ALL
    SELECT tbl1.x, tbl1.y FROM tbl1 JOIN tbl2 ON tbl1.y = tbl2.x WHERE tbl1.x != tbl2.x
) unionResult;
```

また、JOINの中で`!=`を使用したり、複雑な条件を書くとエラーになるので注意です。

```sql
0: jdbc:hive2://localhost:10000> SELECT tbl1.x, tbl1.y FROM tbl1 LEFT SEMI JOIN tbl2 ON tbl1.x != tbl2.x AND tbl1.y = tbl2.x;
Error: Error while compiling statement: FAILED: SemanticException [Error 10017]: Line 1:55 Both left and right aliases encountered in JOIN 'x' (state=42000,code=10017)
```

## 8. GROUP BY
### 問題
以下の`nums`テーブルを用いて、`x`ごとに`y`の合計値を算出するSELECT文を作成してください。ただし、SELECT文には必ず `GROUP BY`句を用いてください。

```sql:numsテーブル
0: jdbc:hive2://localhost:10000> select * from nums;
+---------+---------+--+
| nums.x  | nums.y  |
+---------+---------+--+
| 1       | 2       |
| NULL    | 2       |
| 1       | 2       |
| NULL    | 2       |
+---------+---------+--+
```

```sql:テーブル定義と初期データ
CREATE TEMPORARY TABLE nums (x INT, y INT);
INSERT INTO nums VALUES
    (1, 2),
    (NULL, 2),
    (1, 2),
    (NULL, 2);
```

作成するSELECT文の実行結果は以下のようになります。

```sql:欲しい結果
+-------+----+--+
|   x   | y  |
+-------+----+--+
| NULL  | 2  |
| NULL  | 2  |
| 1     | 4  |
+-------+----+--+
```

### 誤答例
`GROUP BY`句を使用すると、NULLの値がまとめられてしまうので、注意です。[^1]

[^1]: 私は、NULLの判定する際は`=`ではなくて`ISNULL`関数を使うので、てっきり、GROUP BYを使ってもNULLの値はまとめられないと思っていました。

```sql
0: jdbc:hive2://localhost:10000> SELECT x, SUM(y) FROM nums GROUP BY x;
+-------+------+--+
|   x   | _c1  |
+-------+------+--+
| NULL  | 4    |
| 1     | 4    |
+-------+------+--+
2 rows selected (1.207 seconds)
```

### 解答例
こちらは`UNION ALL`を使った解答例です。`FROM`句の中で、`x IS NOT NULL`を指定したSELECT文と`x IS NULL`を指定したSELECT文を書いています。

```sql
SELECT
    unionResult.x AS x,
    unionResult.y AS y
FROM(
    SELECT x AS x, SUM(y) AS y FROM nums WHERE x IS NOT NULL GROUP BY x
    UNION ALL
    SELECT x, y FROM nums WHERE x IS NULL
) unionResult;
```

## 9. UDTFとUDAF
### 問題
次のテーブルのARRAYの合計値を算出するSELECT文を作成してください。

```sql
CREATE TEMPORARY TABLE tbl AS SELECT ARRAY(1, 2, 3) AS arr;
```

### 誤答例
UDAFの中にUDTFを書くことはできないため、`SUM(EXPLODE)`と書くとエラーが発生します。

```sql
0: jdbc:hive2://localhost:10000> SELECT SUM(EXPLODE(arr)) AS element FROM tbl;
Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)
```

### 解答例
`LATERAL VIEW`と使用するとUDAFとUDTFを合わせて使用することができます。

```sql
SELECT
    SUM(num)
FROM
    (SELECT arr FROM tbl) t
LATERAL VIEW
    EXPLODE(arr) arr AS num;
```

## 10. DECIMALの掛け算
### 問題
こちら最後の問題です。次のような `decimal_multiply`テーブルが存在します。

```sql
CREATE TEMPORARY TABLE decimal_multiply AS
SELECT
    CAST(100 AS DECIMAL(38, 18)) AS x,
    CAST(200 AS DECIMAL(38, 18)) AS y
;
```

このとき、以下のSELECT文の `???`の部分を穴埋めして、 `decimal_multiply`テーブルの `x`と `y`を掛け算してください。

```sql:穴埋め問題
SELECT ??? AS result FROM ???;
```

### 誤答例
安易に `x * y`としてしまうと、`NULL`になってしまいます。 

```sql
0: jdbc:hive2://localhost:10000> SELECT x * y AS result from decimal_multiply;
+---------+--+
| result  |
+---------+--+
| NULL    |
+---------+--+
1 row selected (0.083 seconds)
```

`DECIMAL`型の掛け算が上手く行かないのは、概知のバグと報告されており、`DECIMAL(38,18) * DECIMAL(38,18)`の計算結果は `NULL`になってしまいます。 ただし、Hiveの`2.2.0`のバージョンではFixされているようです。

https://issues.apache.org/jira/browse/HIVE-15331

### 解答例
こちらは一旦、 `FLOAT`などの別の数値型にCASTしています。

(`FLOAT`は扱える桁数が`DECIMAL`と違いますが、現状これよりマシな解決策が見当たりません。。もし、この解決策より、良い解決策があれば、教えてください。)

```sql
0: jdbc:hive2://localhost:10000> SELECT CAST(x AS FLOAT) * CAST(y AS FLOAT) AS result from decimal_multiply;
+----------+--+
|  result  |
+----------+--+
| 20000.0  |
+----------+--+
1 row selected (0.079 seconds)
```

他の解答例としては、以下のようなものが考えられます。

- Decimalの掛け算を行うUDFを作成したり
- Hiveをバグが修正されたバージョンにバージョンアップして、 `SELECT x * y AS result from decimal_multiply;`とする

# 終わりに
閲覧ありがとうございましたぁ！！