HiveQL

数据类型和文件格式

基本数据类型

集合数据类型

文本文件数据编码

对于所谓的逗号分隔值CSV或TSV格式，有一个共同的缺点，用户需要对文本文件中那些不需要作为分隔符处理的逗号或者制表符须格外小心。也因此，Hive默认使用了几个控制字符，这些字符很少出现在字段值中。Hive使用术语field来表示替换默认分隔符的字符。

Hive中默认的记录和字段分隔符

读时模式

当用户向传统数据库写入数据的时候，不管是采用装载外部数据的方式，还是采用将一个查询的输出结果写入的方式，或者使用UPDATE语句，等等，数据库对于存储都具有完全的控制力。传统数据库是写时模式(schema on write)，即数据在写入数据库时对模式进行检查。
Hive对底层存储并没有这样的控制。对于Hive要查询的数据，有很多种方式对其进行创建、修改，甚至损坏。因此Hive不会在数据加载时进行验证，而是查询时进行，也就是读时模式(schema on read)

HiveQL是Hive查询语言。和普通使用的所有SQL方言一样，它不完全遵守任一种ANSI SQL标准的修订版。
Hive不支持行级插入操作、更新操作和删除操作。Hive也不支持事务。Hive增加了在Hadoop背景下的可以提供更高性能的扩展，以及一些个性化的扩展，甚至还增加了一些外部程序。

DDL

Hive中的数据库

Hive中数据库的概念本质仅仅是表的一个目录或者命名空间，然而，对于具有很多组和用户的大集群来说，这是非常有用的，因为这样可以避免表明名冲突。通常会使用数据库来将生产表组织成逻辑组。

使用如下语句可以创建一个数据库:

hive> CREATE DATABASE IF NOT EXISTS financials;
1

随时可以通过如下命令方式查看Hive中所包含的数据库:

hive> SHOW DATABASES;
1

Hive会为每个数据库创建一个目录，数据库中的表会以这个数据库目录的子目录形式存储。有一个例外就是default数据库中的表，因为这个数据库本身没有自己的目录。

用户可以通过如下命令修改默认的位置：

hive> CREATE DATABASE financials
    > LOCATION 'my/preferred/directory'; 
1
2

用户也可以为这个数据库增加一个描述信息，这样通过DESCRIBE DATABASE 数据库名 命令就可以看到该信息:

hive> CREATE DATABASE financials
    > COMMENT 'Holds all financial tables';

hive> DESCRIBE DATABASE financials;
1
2
3
4

此外，用户还可以为数据库增加一些和其相关的键值对属性信息，尽管目前仅有功能就是提供了一种可以通过DESCRIBE DATABASE EXTENDED 数据库名语句显示出这些信息的方式:

hive> CREATE DATABASE financials
    > WITH DBPROPERTIES('creator'='Mark Moneybags','date'='2012-01-02');

hive> DESCRIBE DATABASE financials;
hive> DESCRIBE DATABASE EXTENDED financials;
1
2
3
4
5

在默认情况下，Hive是不允许用户删除一个包含有表的数据库的。用户要么先删除数据库中的表，再删除数据库，要么在删除命令的最后面加上关键字CASCADE：

hive> DROP DATABASE IF EXISTS financials CASCADE;
1

修改数据库

用户可以对某个数据库的DBPROPERTIES设置键值对属性

hive> ALTER DATABASE financials SET DBPROPERTIES('edited-by'='Joe Dba');
1

创建表

CREATE TABLE语句遵从SQL语法惯例，但是Hive的这个语句中具有显著的功能扩展，使其可以具有更广泛的灵活性。
例:

CREATE TABLE IF NOT EXISTS mydb.employees(
    name    STRING COMMENT 'Employee name',
    salary  FLOAT COMMENT 'Employee salary',
    subordinates    ARRAY COMMENT 'Name of subordinates',
    deductions  MAP COMMENT 'Keys are deductions name,values are percentages',
    address STRUCT COMMENT 'Home address')
COMMENT 'Description of the table'
TBLPROPERTIES('creator'='me','created_at'='2012-01-02 10:00:00',...)
LOCATION '/user/hive/warehouse/mydb.db/employees';
1
2
3
4
5
6
7
8
9

• Hive会自动增加两个表属性:一个是last_modified_by,其保存着最后修改这个表的用户的用户名；另一个是last_modified_time，其保存着最后一次修改的新纪元时间秒

管理表

目前所创建的表都是所谓的管理表，也可称为内部表。因为这种表，Hive会控制着数据的生命周期。默认情况下，会将这些表的数据存储在由配置项hive.metastore.warehouse.dir所定义的目录的子目录下

外部表

下面语句将创建一个外部表，其可以读取所有位于/data/stocks目录下的以逗号分隔的数据：

CREATE EXTERNAL TABLE IF NOT EXISTS stocks(
    exchange    STRING,
    symbol      STRING,
    ymd         STRING,
    price_open  FLOAT,
    price_high  FLOAT,
    price_close FLOAT,
    volume      INT,
    price_adj_close FLOAT)
ROW FORMAT DELIMITED FILEDS TERMINATED BY ','
LOCATION '/data/stocks';
1
2
3
4
5
6
7
8
9
10
11

关键字EXTENAL告诉Hive这个表是外部的，LOCATION子句告诉Hive数据位于哪个路径下

因为表是外部的，所以Hive并非认为其完全拥有这份数据。因此，删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

用户可以通过DESCRIBE EXTENDED 表名来输出看到表是否是管理表或外部表。在详细信息的末尾
管理表为:...tableType:MANAGED_TABLE外部表为:...tableType:EXTERNAL_TABLE

用户可以对一张存在的表进行表结构复制(不会复制数据):

CREATE EXTERNAL TABLE IF NOT EXISTS mydb.employees3
LIKE mydb.employee
LOCATION '/path/to/data';
1
2
3

分区表、管理表

数据分区的一般概念存在已久。其可以有多种形式，但是通常使用分区来水平分散压力，将数据从物理上转移到和使用最频繁的用户更近的地方，以及实现其他目的。

Hive中有分区表的概念，分区表可以将数据以一种符合逻辑的方式进行组织，比如分层存储。
例：

CREATE TABLE employees(
    name STRING,
    salary  FLOAT,
    subordinates    ARRAY,
    deductions  MAP,
    address     STRUCT
)
PARTITIONED BY (country STRING,state STRING);
1
2
3
4
5
6
7
8

分区表改变了Hive对数据存储的组织方式。如果在mydb数据库中创建这个表，那么对于这个表只会有一个employees目录与之对应:

hdfs://master_server/user/hive/warehouse/mydb.db/employees
1

Hive会创建好可以反映分区结构的子目录

.../employees/country=CA/state=AB
.../employees/country=CA/state=BC
...
.../employees/country=US/state=AL
.../employees/country=US/state=AK
1
2
3
4
5

对数据进行分区，最重要的原因就是为了更快的查询。在WHERE子句中增加谓词按照分区值进行过滤时，这些谓词被称为分区过滤器。

如果表中数据以及分区个数都非常大的话，执行一个包含所有分区的查询会触发一个巨大的MapReduce任务，一个高度建议的安全措施就是将Hive设置为strict(严格)模式，这样如果对分区表进行查询而WHERE子句没有加分区过滤的话，将会禁止提交这个任务。
将Hive设置为严格模式：

hive> set hive.mapred.mode=strict;
1

将Hive设置为非严格模式：

hive> set hive.mapred.mode=nonstrict;
1

可以通过SHOW PARTITIONS命令查看表中存在的所有分区：

hive> SHOW PARTITIONS 表名;
1

如果表中存在很多分区，而用户只想查看某个特定分区时，可以以如下命令进行查询：

hive> SHOW PARTITIONS 表名 PARTITION(字段名='值');
1

DESCRIBE EXTENDED 表名也可以显示出分区键

外部分区表

外部表同样可以使用分区。
例:
创建一个外部分区表log_massages并以年月日分区

CREATE EXTERNAL TABLE IF NOT EXISTS log_massages(
    hms     INT,
    severity    STRING,
    server      STRING,
    process_id  INT,
    message     STRING)
PARTITIONED BY (year INT,month INT,day INT)
ROW FORMAT DELIMITED FIELEDS TERMINATED BY '\t';
1
2
3
4
5
6
7
8

增加一个2012年1月2日的分区:

ALTER TABLE log_messages ADD PARTITION(year=2012,month=1,day=2)
LOCATION 'hdfs://master_server/data/log_messages/2012/01/02';
1
2

和非分区外部表一样，Hive并不控制这些数据，即使表被删除，数据也不会被删除。

删除表

DROP TABLE IF EXISTS 表名;
1

表重命名

ALTER TABLE 表名 RENAME TO 新表名;
1

增加、修改和删除表分区

以增加时间属性分区为例:

ALTER TABLE 表名 ADD IF NOT EXISTS
PARTITION (year=2022,month=1,day=1) LOCATION '/logs/2022/01/01'
PARTITION (year=2022,month=1,day=2) LOCATION '/logs/2022/01/02'
PARTITION (year=2022,month=1,day=3) LOCATION '/logs/2022/01/03'
1
2
3
4

删除分区：

ALTER TABLE 表名 DROP IF EXISTS PARTITION(year=2022,month=1,day=2);
1

修改列信息

ALTER TABLE 表名
CHANGE COLUMN 字段名 新字段名 数据类型
COMMENT '注释内容'
AFTER 某字段名;
1
2
3
4

如果置顶可将AFTER换为FIRST

增加列

ALTER TABLE 表名 ADD COLUMNS(
    字段名1     数据类型    COMMENT '注释1',
    字段名2     数据类型    COMMENT '注释2',
    ...
);
1
2
3
4
5

如果新增的字段中有某个或多个字段位置需要调整则使用ALTER COULME 表名 CHANGE COLUMN逐一将字段调整到正确的位置

修改表属性

用户可以增加附加的表属性或者修改已经存在的属性，但是无法删除属性：

ALTER TABLE 表名 SET TBLPROPERTIES(
    'notes'='The process id is no longer captured;this column is always NULL'
);
1
2
3

修改存储属性

下例将一个分区的存储格式改成了SEQUENCE FILE

ALTER TABLE 表名
PARTITION(year=2022,month=1,day=1)
SET FILEFORMAT SEQUENCEFILE;
1
2
3

ALTER TABLE ... ARCHIVE PARTITION语句会将分区内的文件打成一个Hadoop(HAR)压缩包文件。但这样仅仅可以降低文件系统中的文件数以减轻NameNode压力，而不会减少任何存储空间。

DML

向管理表中装载数据

既然Hive没有行级别的数据插入、数据更新和删除操作，那么往表中装载数据的途径就是一种数据装载操作。或者通过其他方式仅仅将文件写入到正确的目录下。

LOAD DATA LOCAL INPATH '${env:HOME}/读取路径'
OVERWRITE INTO TABLE 表名
PARTITION(字段名1='值1',字段名2='值2',...);
1
2
3

如果分区目录不存在的话，这个命令会先创建分区目录，然后再将数据拷贝到该目录下。
如果目标表是非分区表，那么省略PARTITION子句。

通常情况下指定的路径应该是一个目录，而不是单独的文件。Hive会将所有文件都拷贝到这个目录中。使得用户将更方便地组织数据到多文件中，同时，在不修改Hive脚本地前提下修改文件命名规则。

如果使用了LOCAL这个关键字，那么这个路径应该为本地文件系统路径，数据会被拷贝到目标位置。如果省略掉LOCAL关键字，那么这个路径应该是分布式文件系统中的路径。

通过查询语句向表中插入数据

INSERT语句允许用户通过查询语句向目标表中插入数据。

INSERT OVERWRITE TABLE 表名
PARTITION(分区字段1='值1',分区字段2='值2')
SELECT * FROM 来源表
WHERE 来源表.对应字段1='值1' AND 来源表.对应字段2='值2';
1
2
3
4

这里如果没有使用OVERWRITE关键字或者使用INTO关键字则不会覆盖掉之前的内容，追加记录

如果来源表非常大，Hive提供了另一种INSERT语法:

FROM 来源表
INSERT OVERWRITE TABLE 表名
    PARTITION (分区字段1='值1',分区字段2='值2',...)
    SELECT * WHERE 来源表.对应字段1='值1' AND 来源表.对应字段2='值2'
INSERT OVERWRITE TABLE 表名
    PARTITION (分区字段3='值3',分区字段4='值4',...)
    SELECT * WHERE 来源表.对应字段3='值3' AND 来源表.对应字段4='值4'
INSERT OVERWRITE TABLE 表名
    PARTITION (分区字段5='值5',分区字段6='值6',...)
    SELECT * WHERE 来源表.对应字段5='值5' AND 来源表.对应字段6='值6'
1
2
3
4
5
6
7
8
9
10

动态分区插入

INSERT OVERWRITE TABLE 表名
PARTITION(分区字段1,分区字段2)
SELECT ...,来源表.对应字段1,来源表.对应字段2
FROM 来源表;
1
2
3
4

当静态与动态插入混用时，静态分区键必须在动态分区键之前

动态分区默认情况没有开启，开启后默认是以“严格”模式执行。

动态分区属性

单个查询语句中创建表并加载数据

用户同样可以在一个语句中完成创建表并将查询结果载入到这个表的操作：

CREATE TABLE 表名
AS SELECT 字段名1,字段名2,...
FROM 原表名
WHERE 条件;
1
2
3
4

这个功能不能应用于外部表

导出数据

hadoop fs -cp source_path target_path
1

INSERT OVERWRITE LOCAL DIRECTORY '路径'
SELECT 字段名1,字段名2,字段名3,..
FROM 表名
WHERE 条件;
1
2
3
4

FROM 表名
INSERT OVERWRITE DIRECTORY '路径1'
    SELECT * WHERE 条件1
INSERT OVERWRITE DIRECTORY '路径2'
    SELECT * WHERE 条件2
    ...;
1
2
3
4
5
6

DQL

例：

CREATE TABLE IF NOT EXISTS employees(
    name        STRING,
    salary      FLOAT,
    subordinates    ARRAY,
    deductions      MAP,
    address     STRUCT
)
PARTITIONED BY (country STRING,state STRING);
1
2
3
4
5
6
7
8

下面两个查询是等价的：

hive> SELECT    name,   salary  FROM    employees;
hive> SELECT    e.name, e.salary    FROM    employees e;
1
2

用户选择的列为集合数据类型时，Hive会使用JSON语法用于输出。

hive> SELECT name,subordinates FROM employees;
其输出结果格式为：
John    Doe     ["Mary Smith","Todd Jones"]
Marry   Smith   ["Bill King"]
Todd    Jones   []
Bill    King    []
1
2
3
4
5
6

MAP类型也是用JSON表达：

hive> SELECT name,deductions FROM employees;
其输出结果格式为：
John    Doe     {"Federal Taxes":0.2,"State Taxes":0.05,"Insurance":0.1}
Marry   Smith   {"Federal Taxes":0.2,"State Taxes":0.05,"Insurance":0.1}
Todd    Jones   {"Federal Taxes":0.15,"State Taxes":0.03,"Insurance":0.1}
Bill    King    {"Federal Taxes":0.15,"State Taxes":0.03,"Insurance":0.1}
1
2
3
4
5
6

STRUCT也是用JSON表达：

hive> SELECT name,address FROM employees;
其输出结果格式为：
John    Doe     {"street":"1 Michigan Ave.","city":"Chicago","state":"IL","zip":60600}
Marry   Smith   {"street":"100 Ontario St.","city":"Chicago","state":"IL","zip":60601}
Todd    Jones   {"street":"200 Chicago Ave.","city":"Oak Park","state":"IL","zip":60700}
Bill    King    {"street":"300 Obscure Dr.","city":"Obscuria","state":"IL","zip":60100}
1
2
3
4
5
6

查询数组的第一个元素:

hive> SELECT name,subordinates[0] FROM employees;
其输出结果格式为：
John    Doe     Mary Smith
Marry   Smith   Bill King
Todd    Jones   NULL
Bill    King    NULL
1
2
3
4
5
6

查询MAP中的一个元素:

hive> SELECT name,deductions["State Taxes"] FROM employees;
其输出结果格式为：
John    Doe     0.05
Marry   Smith   0.05
Todd    Jones   0.03
Bill    King    0.03
1
2
3
4
5
6

查询STRUCT中的一个元素：

hive> SELECT name,address.city FROM employees;
其输出结果格式为：
John    Doe     Chicago
Marry   Smith   Chicago
Todd    Jones   Oak Park
Bill    King    Obscuria
1
2
3
4
5
6

使用正则表达式来指定列

例：选择symbol列和所有列名以price作为前缀的列：

hive> SELECT symbol,`price.*` FROM stocks;
1

使用列值进行计算

hive> SELECT upper(name),salary,deductions["Federal Taxes"],round(salary*(1-deductions["Federal Taxes"])) FROM employees;
1

算术运算符

数学函数

聚合函数

通常可以通过设置属性hive.map.aggr值为true来提高聚合的性能：

hive> SET hive.map.aggr=true;
1

表生成函数

与聚合函数相反的一类函数就是表生成函数，其可以将单列扩展成多列或者多行。

其他内置函数

LIMIT语句

典型的查询以LIMIT子句用于限制返回的行数

hive> SELECT upper(name),salary,deductions["Federal Taxes"],round(salary*(1-deductions["Federal Taxes"])) FROM employees limit 2;
1

CASE … WHEN … THEN

例：

hive> SELECT name,salary,
    CASE
        WHEN salary<50000.0 THEN 'low'
        WHEN salary>=50000.0 AND salary<70000.0 THEN 'middle'
        WHEN salary>=70000.0 AND salary<100000.0 THEN 'high'
        ELSE 'very high'
    END AS bracket FROM employees;
1
2
3
4
5
6
7

什么情况下Hive可以避免进行MapReduce

一般情况下

set hive.exec.mode.local.auto=true;
1

Hive会尝试使用本地模式执行查询，而不启动MapReduce

WHERE子句中的谓词操作符

LIKE 和 RLIKE

LIKE符合一个标准的SQL操作符，RLIKE子句是Hive中功能的一个拓展，其可以通过Java的正则表达式来指定匹配条件。

GROUP BY

配合聚合函数，按照一个或者多个列进行分组

HAVING

允许用户通过一个简单的语法完成原本需要子查询才能对GROUP BY分组进行条件过滤的任务

JOIN

INNER JOIN

内连接，只有进行连接的两个表中都存在与连接标准相匹配的数据才会被保留下来。

LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN

左关联，右关联，全关联

笛卡尔积JOIN

SELECT * FROM 表1 JOIN 表2;
1

ORDER BY和SORT BY

Hive中ORDER BY 与SQL方言中的定义是一样的，其对查询结果执行全局排序
Hive增加了一个可供选择的方式，SORT BY，其只会在每个reducer中对数据进行排序，也就是执行一个局部排序过程。

含有SORT BY 的 DISTRIBUTE BY

DISTRICBUTE BY控制map的输出在reducer中是如何划分的。MapReduce job中传输的所有数据都是按照键值对的方式进行组织的，因此Hive在将用户的查询语句转换成MapReduce job时，其必须在内部使用这个功能。
默认情况下，MapReduce计算框架会根据map输入的键计算对应的哈希值，然后按照得到的哈希值将键值对均匀分发到多个reducer中去。
DISTRIBUTE BY和GROUP BY在其控制着reducer是如何接受一行一行数据进行处理这方面是类似的，而SOUT BY控制着reducer内的数据是如何进行排序的。
DISTRIBUTE BY语句要写在SORT BY语句之前。

CLUSTER BY

CLUSTER BY相当于DISTRIBUTE BY与SORT BY 两个的组合。
使用DISTRIBUTE BY … SORT BY语句或其简化版的CLUSTER BY语句会剥夺SORT BY的并行性，然而这样可以实现输出文件的数据是全局排序的。