Apache Hive 概要 / HiveQL チートシート

# はじめに
この記事では、Hive の概要と HiveQL の記述方法についてまとめています。大規模データを扱うプロジェクトでは、標準 SQL や Presto 、Hive 等のクエリを書き分けて利用することも多々あるかと思いますが、これらの技術はアーキテクチャも異なれば、クエリの記述方法も異なるため、特徴を抑えておく必要があります。これらの違いをすぐ思い出せるように、リファレンスとして利用できるものを記述します。

![apache-hive.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/244338/fe02bd73-2c88-0668-7f94-66623c5bd825.png)

# Hive 概要
Hive は **HiveQL（HQL）** と呼ばれる SQL ライクな DSL で MapReduce を実行するための技術です。そのため、裏側では MapReduce の処理が走り、Presto や BigQuery のように**インタラクティブにデータを処理することには向いていません。**

あくまで、SQL ライクな言語で処理を記述し、バッチ処理を行うものです。SQL ライクなため、標準 SQL には準拠していませんが、MySQL の記述に最も近いです。

# Hive ファイルの実体
**Hive のデータは HDFS 上のファイルとして存在しています。**デフォルトで存在する default データベースにテーブルをまとめるか、自身で作成したデータベースにテーブルをまとめるかで少しディレクトリ構成が変わります。

また、パーティション（HDFS 上のディレクトリ）を日付等のカラムの値で分割することで、問い合わせを行う際の検索範囲を制限することができ、処理を高速に行うことができます。

**これはテーブル名が table1 の場合のディレクトリ構成の例です。**

- default データベースの例：/user/hive/warehouse/table1
- db1 データベースの例：/user/hive/warehouse/db1.db/db1table1
- パーティション分割の例：/user/hive/warehouse/table1/year=2020/month=12/day=27

## Hive メタストア

カラムやその属性などのテーブル定義は、メタストアと呼ばれる RDBMS に保存されます。**Hive のデータは、HDFS 上のファイルとして存在する実データと、RDBMS に存在するメタデータをマッピングして扱われます。**

Hive の実データは HDFS 上にあるため、データの一部を上書き更新することができません。そのため、**HiveQL には UPDATE 文や DELETE 文は存在しません。**

# Hive インストール
[Docker 上で入門する Apache Hadoop](https://qiita.com/esakik/items/5e2fdac99ba0b8554bf1) で環境構築（CDH のインストール）後であれば、次のコマンドでインストールが可能です。それ以外の場合は[こちらの記事](https://qiita.com/Esfahan/items/a6f2107876e5a712a72c)等を参照してください。

```bash:CentOS
yum install hive
```

# HiveQL 実行方法
Hive コマンドラインで実行する方法と、Hive シェル上で実行する方法が存在します。

## コマンドラインで実行する方法

```bash
# ファイル名を指定する方法
$ hive -f <ファイル名>

# HiveQL を直接記述する方法
$ hive -e '<HiveQL>'
```

## Hive シェル上で実行する方法

```bash
$ hive
hive > <HiveQL>
```

# HiveQL 基本的な文法
HiveQL の基本的な文法とその実装例を記述します。

## データベースの作成

```hql
CREATE DATABASE db1;
```

## データベースの利用

```hql
USE db1;
```

## データベースの一覧表示

```hql
SHOW DATABASES;
```

## テーブルの作成

```hql
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <テーブル名>
  [(<カラム名> <データ型> [COMMENT <カラム説明>], ...)]
  [COMMENT <テーブル説明>] -- コメントを付与
  [PARTITIONED BY (<カラム名> <データ型> [COMMENT <カラム説明>], ...)] -- 指定のカラム名でパーティショニング
  [CLUSTERED BY (<カラム名>, ...) [SORTED BY (<カラム名> [{ASC|DESC}], ...)] INTO <バケット数> BUCKETS] -- テーブルやパーティションに対してバケット数を指定して分割する（データをソートしておくことが可能）
  [ROW FORMAT {DELIMITED|SERDE}
    -- DELIMITED 指定の場合
    [FIELDS TERMINATED BY <カラムの区切り文字>] -- デフォルトはなし
    [COLLECTION ITEMS TERMINATED BY <配列の区切り文字>] -- デフォルトはなし
    [MAP KEYS TERMINATED BY <Mapの区切り文字>] -- デフォルトはなし
    [LINES TERMINATED BY <行の区切り文字>] -- デフォルトはなし
    -- SERDE 指定の場合
    [<SerDe （シリアライズ・デシリアライズクラスの名前）> WITH SERDEPROPERTIES(プロパティ名 = 値, ...)]]
  [STORED AS <ファイルフォーマット>] -- TEXTFILE（デフォルト）, SEQUENCEFILE, RCFILE（列指向）, INPUTFORMAT<クラス名>, OUTPUTFORMAT<クラス名> のいづれか
  [LOCATION <HDFS パス>] -- 指定がない場合は /user/hive/warehouse/<テーブル名>
  [TBLPROPERTIES (property_name=<プロパティ名>, ...)] -- ユーザー独自のメタデータ
  [AS <SELECT 文>] -- SELECT 文の実行結果からテーブルを作成する
```

次のクエリは、カラムの区切り文字が「,（カンマ）」で、行の区切り文字が「\n（改行）」のテキストファイルで保持するテーブルを作成する場合の例です。

```hql
CREATE TABLE table1
  (col1 INT, col2 INT, col3 INT, col4 STRING)
  ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
  STORED AS TEXTFILE;
```

次のクエリは、パーティションとして col1, col2 を持ち、その他に col3, col4 カラムを持つテーブルを作成する場合の例です。

```hql
CREATE TABLE table1
  (col3 INT, col4 INT)
  PARTITIONED BY (col1 INT, col2 INT);
```

## テーブルの一覧表示

```hql
SHOW TABLES;
```

## テーブルの詳細表示

```hql
DESC [FORMATTED] <テーブル名>; -- FORMATTED：より詳細な情報を表示する
```

## テーブルの削除

```hql
DROP TABLE [IF EXISTS] <テーブル名>;
```

## データの挿入

### ファイルシステムからデータを挿入
入力となるデータはローカルファイルシステムや HDFS 上のデータを利用します。そのため、実際には Hive のデータを格納するディレクトリにファイルを移動しているのと同じ動作になります。

```hql
LOAD DATA
  [LOCAL] -- 指定した場合：ローカルファイルシステム上の入力データ、指定しなかった場合：HDFS 上の入力データ
  INPATH '<ファイルパス>'
  [OVERWRITE] -- 指定した場合：TRUNCATE、指定しなかった場合：APPEND
  INTO TABLE <テーブル名>
  [PARTITION (col1=val1, col2=val2, ...)] -- 指定した場合：特定のパーティションにデータを挿入する
```

### Hive テーブルからデータを挿入
Hive テーブルに対するクエリの実行結果を新しく Hive テーブルに挿入します。

```hql
INSERT
  [{OVERWRITE|INTO}] -- OVERWRITE を指定した場合：TRUNCATE、INTO を指定した場合：APPEND
  TABLE <テーブル名>
  [PARTITION (col1=val1, col2=val2, ...) IF NOT EXISTS] -- IF NOT EXISTS を指定すると、データが存在しない場合のみデータを挿入できる
  <SELECT 句> FROM <FROM 句>
```

### 複数テーブルにデータを挿入
クエリの実行結果を複数のテーブルにデータを挿入することができます。

```hql
FROM <ソーステーブル名>
  INSERT INTO TABLE <宛先テーブル名①> SELECT col1, col2 WHERE col1 >= 0
  INSERT INTO TABLE <宛先テーブル名②> SELECT col1, col2 WHERE col1 < 0
```

また、複数のパーティションにデータを挿入することもできます。

```hql
INSERT
  [{OVERWRITE|INTO}]
  TABLE <ソーステーブル名>
  PARTITION (col1=val1, col2=val2, ...)
  <SELECT 句> FROM <FROM 句>
```

## データの出力
Hive テーブルに対するクエリの実行結果をファイルシステムに出力することができます。

```hql
INSERT OVERWRITE
  [LOCAL] -- 指定した場合：ローカルファイルシステムに出力、指定しなかった場合：HDFS に出力
  DIRECTORY '<ディレクトリ名>'
  <SELECT 句> FROM <FROM 句>
```

## データの検索
HiveQL は、標準 SQL とは挙動が異なるものもありますが、次のような一般的な関数などを利用することができます（一部を記載）。

|  ジャンル  |  名前  |  備考  |
| ---- | ---- | ---- |
|  集計関数  |  MAX  |      |
|  集計関数  |  MIN  |      |
|  集計関数  |  COUNT  |      |
|  集計関数  |  SUM  |      |
|  集計関数  |  AVG  |      |
|  配列関数  | COLLECT_LIST  |   ARRAY_AGG とほぼ同じ   |
|  配列関数  |  COLLECT_SET  |   ARRAY_AGG とほぼ同じ（重複排除）   |
|  配列関数  |  ARRAY  |      |
|  文字列関数  |  REPLACE  |      |
|  文字列関数  |  COALESCE  |      |
|  文字列関数  |  CONCAT  |      |
|  文字列関数  |  CONCAT_WS  |   第 1 引数の区切り文字で、<br/>第 2 引数以降の文字を結合する<br/><br/>CONCAT_WS(delimiter, str1, str2, ...)   |
|  文字列関数  |  LENGTH  |      |
|  文字列関数  |  LOWER  |      |
|  文字列関数  |  UPPER  |      |
|  文字列関数  |  SUBSTR  |      |
|  文字列関数  |  REGEXP_REPLACE  |      |
|  文字列関数  |  REGEXP_EXTRACT  |      |
|  文字列関数  |  SPLIT  |      |
|  分析関数  |  OVER(PARTITION BY ~ ORDER BY ~)  |      |
|  番号付け関数 |  RANK  |      |
|  番号付け関数 |  ROW_NUMBER  |      |
|  ナビゲーション関数 |  LEAD  |      |
|  ナビゲーション関数 |  LAG  |      |
|  ナビゲーション関数 |  LAST_VALUE  |      |
|  ナビゲーション関数 |  FIRST_VALUE  |      |
|  数学関数 |  RAND  |      |
|  数学関数 |  ROUND  |      |
|  タイムスタンプ関数 |  UNIX_TIMESTAMP  |      |
|  タイムスタンプ関数 |  FROM_UNIXTIME  |      |
|  日付関数 |  DATEDIFF  |      |
|  日付関数 |  DATE_ADD  |      |
|  日付関数 |  DATE_SUB  |      |
|  日付関数 |  DATE_FORMAT  |      |
|  その他 |  DISTINCT  |      |
|  その他 |  GROUP BY  |      |
|  その他 |  CAST  |      |
|  その他 |  LIKE  |      |
|  その他 |  RLIKE  |   正規表現で検索   |

### 検索結果に列名を含める
検索結果に列名を含めたい場合は、次の設定が必要です。

```bash
$ hive
hive > set hive.cli.print.header=true;
```

### ORDER BY よりも SORT BY を利用する
ORDER BY を利用することはできますが、出力結果全体をソートすることになるため、Ruducer の数がひとつに限られ、パフォーマンスが悪くなる場合があります。

また、**ORDER BY のソートは、オプション設定が hive.mapred.mode=nonstrict の場合、Hive によって適当なタイミングで処理を打ち切られてしまいます。**

※ hive.mapred.mode=strict の場合は打ち切られませんが LIMIT の指定が必要です

一方で、SORT BY を利用すると、Reducer が複数動作し、すべてのデータをソートすることができます。

```hql
SELECT col1 FROM table1 SORT BY col1 DESC;
```

### JOIN は利用できる（ただし ON 句で NOT 条件を使えない）
HiveQL では、内部結合、外部結合、完全外部結合、半結合、クロス結合を利用できます。JOIN のデフォルトは内部結合です。

- **INNER JOIN：**内部結合
- **LEFT (OUTER) JOIN：**外部結合
- **RIGHT (OUTER) JOIN：**外部結合
- **FULL (OUTER) JOIN：**完全外部結合
- **LEFT SEMI JOIN：**半結合（INNER JOIN した結果の左側のテーブルの列のみ出力する）
- **CROSS JOIN：**クロス結合

このように一通り JOIN を利用することができるのですが、注意点として HiveQL の JOIN は ON 句の中に NOT を書くことができません。

```hql
-- !!これは実行できない
SELECT table1.col1 FROM table1 JOIN table2 ON table1.col1 <> table2.col1
```

### WHERE ~ IN/EXISTS が利用できない
WHERE 句内の IN/EXISTS は HiveQL では利用できません。同様のことを行うには、LEFT SEMI JOIN を利用します。

```hql
-- !!これは実行できない
SELECT table1.col1, table1.col2 FROM table1 WHERE table1.col1 IN (SELECT table2.col1 FROM table2);
```

```hql
SELECT table1.col1, table1.col2 FROM table1 LEFT SEMI JOIN table2 ON table1.col1 = table2.col1
```

### UNION は ALL 句のみでサブクエリ内のみで利用できる
HiveQL では、UNION（ALL の省略） や UNION DISTINCT を利用することはできず、UNION ALL のみ利用できます。また、最上位レベルで UNION ALL を利用することができないため、サブクエリ内に記述します。

```hql
SELECT col1, col2 FROM (
  SELECT col1, col2 FROM table1
  UNION ALL
  SELECT col1, col2 FROM table2
) tmp
```

# HiveQL のデータ型
HiveQL で扱うデータ型を一挙にまとめています。

## 型一覧

|  型のカテゴリ  |  型の種類  |  型  |  説明  |
|  ----  | ---- | ---- | ---- |
|  プリミティブ型  |  整数型  |  TINYINT  |  1 バイトの整数<br>（-128 ~ 127）  |
|  プリミティブ型 |  整数型  |  SMALLINT  |  2 バイトの整数<br>（-32,768 ~ 32,767） |
|  プリミティブ型 |  整数型  |  INT  |  4 バイトの整数 |
|  プリミティブ型 |  整数型  |  BIGINT  |  8 バイトの整数  |
|  プリミティブ型 |  浮動小数点型  |  FLOAT  |  4 バイトの浮動小数点数  |
|  プリミティブ型 |  浮動小数点型  |  DOUBLE  |  8 バイトの浮動小数点数  |
|  プリミティブ型 |  浮動小数点型  |  DECIMAL  |  固定小数点  |
|  プリミティブ型 |  浮動小数点型  |  NUMERIC  |  DECIMAL と同じ  |
|  プリミティブ型 |  論理型  |  BOOLEAN  |  論理値  |
|  プリミティブ型 |  文字列型  |  STRING  |  文字列  |
|  プリミティブ型 |  文字列型  |  VARCHAR  |  文字列<br>（1 ~ 65535 文字）  |
|  プリミティブ型 |  文字列型  |  CHAR  |  文字列<br>（最大で 255 文字）  |
|  プリミティブ型 |  バイナリ型  |  BINARY  |  バイナリデータ  |
|  プリミティブ型 | 日付時刻型  |  TIMESTAMP  |  タイムスタンプ<br>（タイムゾーンなし）  |
|  プリミティブ型 | 日付時刻型  |  DATE  |  日付  |
|  複合型  |  配列型  |  ARRAY<データ型>  |  配列  |
|  複合型 |  MAP型  |  MAP<プリミティブ型, データ型>  |  Key, Value 形式  |
|  複合型 |  構造体型  |  STRUCT<カラム名: データ型, ...>  |  複数のデータ型を構造化  |
|  複合型  |  共用体型  |  UNIONTYPE<データ型, データ型, ...>  |  指定されたデータ型の1つだけを保持  |

## 補足事項

|  型  |  補足 |
|  ----  | ---- |
|  TIMESTAMP  |  値フォーマット：'YYYY-MM-DD HH:MM:SS.fffffffff' |
|  DATE  |  値フォーマット：'YYYY-MM-DD' |
|  ARRAY<データ型>  | 挿入時：INSERT ~ SELECT ARRAY('A', 'B', 'C') ~<br/>検索時：SELECT a[0], a[1] ~ |
|  MAP<プリミティブ型, データ型>  | 挿入時：INSERT ~ SELECT MAP('key1', 'value1', 'key2', 'value2') ~<br/>検索時：SELECT m['key1'], m['key2'] ~  |
|  STRUCT<カラム名: データ型, ...>  |  挿入時：INSERT ~ SELECT STRUCT('A', 'B', 'C') ~<br/>検索時：SELECT s.カラム名1, s.カラム名2 ~ |

# ユーザー定義関数（UDF）
HiveQL では、ユーザー定義関数を Java で実装して利用することができます。

## ユーザー定義関数の作成
ユーザー定義関数を作成するために行うことは主に 2 つです。

1. org.apache.hadoop.hive.ql.exec.UDF を継承したクラスを作成する
2. evaluate メソッドを実装する

```Java
package com.example;

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

// org.apache.hadoop.hive.ql.exec.UDF を継承したクラスを作成する
public final class OriginalUDF extends UDF {
    // evaluate メソッドを実装する
    public Boolean evaluate(final Text key, Text ... val) {
        for (Text v: val) {
            if (key.equals(v)) {
                return true;
            }
        }
        return false;
    }
}
```

## ユーザー定義関数の利用
ユーザー定義関数を利用するには、jar ファイルを Hive に登録する必要があります。

1. コンパイルして、クラスファイルの作成と jar ファイルの作成を行う
2. Hive シェル上で `ADD jar <jar ファイルのパス>` を実行する
3. Hive シェル上で `CREATE TEMPORARY FUNCTION <関数名> AS '<クラス名>'` を実行する

これらの手順を行うと、HiveQL で関数名を利用して呼び出すことができます。

```bash
hive > ADD jar ./original_udf.jar;
hive > CREATE TEMPORARY FUNCTION original_udf AS 'com.example.OriginalUDF';
hive > SELECT col1 FROM table1 WHERE original_udf(col1, "A", "B");
```

# まとめ
本記事では、Hive の概要と HiveQL の記述方法についてまとめました。簡単にリファレンスとして利用していただけたら幸いです。

# 参考 URL
- [Hadoop徹底入門 第2版 オープンソース分散処理環境の構築](https://amzn.to/2R4N1P2)
- [Apache Hive LanguageManual Types](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types)
- [hive(athena)の InputFormat, OutputFormat, SerDe](https://blog.n-t.jp/tech/hive-table-input-output-serde)
- [HiveQL（HiveのSQLっぽい独自言語）のテーブルに関するメモ。](https://www.ne.jp/asahi/hishidama/home/tech/apache/hive/table.html)
- [HiveQLにおける初心者狩り](https://qiita.com/woadachi/items/6aff2d01ceddf9b94c2d)
- [Hadoop利用者ならきっと知ってる、Hive/Prestoクエリ関数の挙動の違い](https://qiita.com/y-ken/items/400b7c70c324ac67af02)