Apache Hive 简介/ HiveQL 快速参考手册

首先

在这篇文章中，总结了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 ''

在Hive中执行的方法

$ hive

hive >

基本的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 ] -- デフォルトはなし

[LINES TERMINATED BY <行の区切り文字>] -- デフォルトはなし

-- SERDE 指定の場合

[ WITH SERDEPROPERTIES(プロパティ名 = 値, ...)]]

[STORED AS <ファイルフォーマット>] -- TEXTFILE（デフォルト）, SEQUENCEFILE, RCFILE（列指向）, INPUTFORMAT<クラス名>, OUTPUTFORMAT<クラス名> のいづれか

[LOCATION ] -- 指定がない場合は /user/hive/warehouse/<テーブル名>

[TBLPROPERTIES (property_name=<プロパティ名>, ...)] -- ユーザー独自のメタデータ

[AS ] -- 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 を指定すると、データが存在しない場合のみデータを挿入できる

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, ...)

FROM

输出数据

您可以将对Hive表的查询结果输出到文件系统中。

INSERT OVERWRITE

[LOCAL] -- 指定した場合：ローカルファイルシステムに出力、指定しなかった場合：HDFS に出力

DIRECTORY '<ディレクトリ名>'

FROM

搜索数据

尽管HiveQL与标准SQL之间存在一些行为上的差异，但您仍可以使用以下常见函数进行操作（部分列举）。

ジャンル名前備考集計関数MAX

集計関数MIN

集計関数COUNT

集計関数SUM

集計関数AVG

配列関数COLLECT_LISTARRAY_AGG とほぼ同じ配列関数COLLECT_SETARRAY_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;

使用排序 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中处理的数据类型。

类型清单

型のカテゴリ型の種類型説明プリミティブ型整数型TINYINT1 バイトの整数

（-128 ~ 127）プリミティブ型整数型SMALLINT2 バイトの整数

（-32,768 ~ 32,767）プリミティブ型整数型INT4 バイトの整数プリミティブ型整数型BIGINT8 バイトの整数プリミティブ型浮動小数点型FLOAT4 バイトの浮動小数点数プリミティブ型浮動小数点型DOUBLE8 バイトの浮動小数点数プリミティブ型浮動小数点型DECIMAL固定小数点プリミティブ型浮動小数点型NUMERICDECIMAL と同じプリミティブ型論理型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 实现和使用用户定义的函数。

创建用户定义函数

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

创建一个继承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 中。

编译并生成类文件和jar文件。

在Hive Shell中执行ADD 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クエリ関数の挙動の違い