LoginSignup
8
0

More than 3 years have passed since last update.

MicroAd (マイクロアド)Advent Calendar 2019Day 8microad
@tobita_yoshikiin株式会社マイクロアド

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

Last updated at Posted at 2019-12-07

はじめに

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

1 ~ 5問目はこちらページに掲載しております。

環境構築

こちらの記事を参考に環境構築すると、Dockerでカンタンにbeelineが利用できるようになります。

参考記事: https://qiita.com/letusfly85/items/b4e5fc67c9566765b794

(記事作成者の方、ありがとうございます！！)

参考記事から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が存在するとします。

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するクエリを作成してください。

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

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

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

誤答例

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

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という句を使いました。

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

7. OR句

問題

次のようなテーブルtbl1とtbl2が存在したとします。

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するクエリを作成してください。

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

誤答例

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

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を使用しました。

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の中で!=を使用したり、複雑な条件を書くとエラーになるので注意です。

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句を用いてください。

numsテーブル
0: jdbc:hive2://localhost:10000> select * from nums;
+---------+---------+--+
| nums.x  | nums.y  |
+---------+---------+--+
| 1       | 2       |
| NULL    | 2       |
| 1       | 2       |
| NULL    | 2       |
+---------+---------+--+
テーブル定義と初期データ
CREATE TEMPORARY TABLE nums (x INT, y INT);
INSERT INTO nums VALUES
    (1, 2),
    (NULL, 2),
    (1, 2),
    (NULL, 2);

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

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

誤答例

GROUP BY句を使用すると、NULLの値がまとめられてしまうので、注意です。1

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文を書いています。

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文を作成してください。

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

誤答例

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

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を合わせて使用することができます。

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

10. DECIMALの掛け算

問題

こちら最後の問題です。次のような decimal_multiplyテーブルが存在します。

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を掛け算してください。

穴埋め問題
SELECT ??? AS result FROM ???;

誤答例

安易に x * yとしてしまうと、NULLになってしまいます。 

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されているようです。

解答例

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

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

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;とする

終わりに

閲覧ありがとうございましたぁ！！

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

8
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
0