首先

在这篇文章中，总结了Hive的概述和HiveQL的编写方式。在处理大规模数据的项目中，经常需要同时使用标准SQL、Presto和Hive等查询语言，但是由于这些技术的架构和查询语法都不一样，所以需要记住它们各自的特点。为了能够迅速回想起这些区别，将会提供一个可作为参考的描述。

Hive 摘要

Hive 是一种用于执行 MapReduce 的名为 HiveQL（HQL）的 SQL 类型 DSL 技术。因此，在其背后运行着 MapReduce 过程，不适合像 Presto 或 BigQuery 那样以互动方式处理数据。

这是一个使用类似于SQL的语言编写和执行批处理的东西。由于类似于SQL，它并不符合标准的SQL规范，但它与MySQL的语法最为接近。

Hive 文件的实体

Hive的数据以HDFS上的文件形式存在。根据默认设置，可以将表放在默认的数据库中，也可以将表放在自己创建的数据库中，这样会稍微改变目录结构。

同时，您可以通过按照日期等字段的值对分区（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 元数据存储

表格定义，如列和其属性等，会被保存在称为元数据存储（MetaStore）的关系型数据库（RDBMS）中。Hive的数据被处理为存在于HDFS上的实际数据文件和存在于RDBMS中的元数据之间的映射关系。

由于Hive的实际数据存储在HDFS上，所以无法对数据进行部分覆盖更新。因此，在HiveQL中没有UPDATE或DELETE语句。

安装Hive

如果在Docker上使用Apache Hadoop进行入门并完成环境安装（CDH安装），则可以使用以下命令进行安装。如果不是这种情况，请参考本文或其他相关文章。

yum install hive

HiveQL的执行方式

在Hive中，有两种方法可以执行命令：通过Hive命令行和通过Hive Shell执行。

在命令行中执行的方法。

# ファイル名を指定する方法
$ 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数据的目录中，与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之间存在一些行为上的差异，但您仍可以使用以下常见函数进行操作（部分列举）。

在搜索结果中包含列名

如果想在搜索结果中包括列名，则需要进行以下设置。

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

使用排序 BY 而不是按照 BY 进行排序。

可以使用ORDER BY来对结果进行排序，但由于会对整个输出结果进行排序，所以Reducer的数量会受限，可能会影响性能。

另外，如果Hive的选项设置为hive.mapred.mode=nonstrict，ORDER BY排序将在合适的时间被Hive中断处理。

※ 如果 hive.mapred.mode=strict 的话不会被中断，但需要指定 LIMIT。

当使用 SORT BY 功能时，可以让 Reducer 并行执行，并对所有数据进行排序。

SELECT col1 FROM table1 SORT BY col1 DESC;

中国至台湾：加入（但无法在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

只在哪里无法使用/存在

在HiveQL中，不能使用WHERE子句内的IN/EXISTS。要执行类似操作，可以使用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中处理的数据类型。

类型清单

补充的事项

用户自定义函数（UDF）

在 HiveQL 中，您可以使用 Java 实现和使用用户定义的函数。

创建用户定义函数

创建用户自定义函数主要需要做两件事。

1. 创建一个继承org.apache.hadoop.hive.ql.exec.UDF的类

实现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文件。

1. 在Hive Shell中执行ADD jar <jar文件路径>。

在Hive Shell中执行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 的描述方法。希望这篇文章能作为一个简单的参考工具供您使用。

请提供具体的链接

• Hadoop徹底入門 第2版 オープンソース分散処理環境の構築

• Apache Hive LanguageManual Types

• hive(athena)の InputFormat, OutputFormat, SerDe

• HiveQL（HiveのSQLっぽい独自言語）のテーブルに関するメモ。

• HiveQLにおける初心者狩り

Hadoop利用者ならきっと知ってる、Hive/Prestoクエリ関数の挙動の違い