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

はじめに

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

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 で環境構築（CDH のインストール）後であれば、次のコマンドでインストールが可能です。それ以外の場合はこちらの記事等を参照してください。

CentOS

yum install hive

HiveQL 実行方法

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

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

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

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

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

$ hive
hive > <HiveQL>

HiveQL 基本的な文法

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

データベースの作成

CREATE DATABASE db1;

データベースの利用

USE db1;

データベースの一覧表示

SHOW DATABASES;

テーブルの作成

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（改行）」のテキストファイルで保持するテーブルを作成する場合の例です。

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 カラムを持つテーブルを作成する場合の例です。

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

テーブルの一覧表示

SHOW TABLES;

テーブルの詳細表示

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

テーブルの削除

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

データの挿入

ファイルシステムからデータを挿入

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

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

Hive テーブルからデータを挿入

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

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

複数テーブルにデータを挿入

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

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

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

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

データの出力

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

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 引数の区切り文字で、 第 2 引数以降の文字を結合する 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	正規表現で検索

検索結果に列名を含める

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

$ 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 が複数動作し、すべてのデータをソートすることができます。

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 を書くことができません。

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

WHERE ~ IN/EXISTS が利用できない

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

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

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 を利用することができないため、サブクエリ内に記述します。

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

HiveQL のデータ型

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

型一覧

型のカテゴリ	型の種類	型	説明
プリミティブ型	整数型	TINYINT	1 バイトの整数 （-128 ~ 127）
プリミティブ型	整数型	SMALLINT	2 バイトの整数 （-32,768 ~ 32,767）
プリミティブ型	整数型	INT	4 バイトの整数
プリミティブ型	整数型	BIGINT	8 バイトの整数
プリミティブ型	浮動小数点型	FLOAT	4 バイトの浮動小数点数
プリミティブ型	浮動小数点型	DOUBLE	8 バイトの浮動小数点数
プリミティブ型	浮動小数点型	DECIMAL	固定小数点
プリミティブ型	浮動小数点型	NUMERIC	DECIMAL と同じ
プリミティブ型	論理型	BOOLEAN	論理値
プリミティブ型	文字列型	STRING	文字列
プリミティブ型	文字列型	VARCHAR	文字列 （1 ~ 65535 文字）
プリミティブ型	文字列型	CHAR	文字列 （最大で 255 文字）
プリミティブ型	バイナリ型	BINARY	バイナリデータ
プリミティブ型	日付時刻型	TIMESTAMP	タイムスタンプ （タイムゾーンなし）
プリミティブ型	日付時刻型	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') ~ 検索時：SELECT a[0], a[1] ~
MAP<プリミティブ型, データ型>	挿入時：INSERT ~ SELECT MAP('key1', 'value1', 'key2', 'value2') ~ 検索時：SELECT m['key1'], m['key2'] ~
STRUCT<カラム名: データ型, ...>	挿入時：INSERT ~ SELECT STRUCT('A', 'B', 'C') ~ 検索時：SELECT s.カラム名1, s.カラム名2 ~

ユーザー定義関数（UDF）

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

ユーザー定義関数の作成

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

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

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 で関数名を利用して呼び出すことができます。

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版 オープンソース分散処理環境の構築
• Apache Hive LanguageManual Types
• hive(athena)の InputFormat, OutputFormat, SerDe
• HiveQL（HiveのSQLっぽい独自言語）のテーブルに関するメモ。
• HiveQLにおける初心者狩り
• Hadoop利用者ならきっと知ってる、Hive/Prestoクエリ関数の挙動の違い