HiveQL

数据类型和文件格式

基本数据类型

数据类型	长度	例子	备注
TINYINT	1byte有符号整数	20
SMALINT	2byte有符号整数	20
INT	4byte有符号整数	20
BIGINT	8byte有符号整数	20
BOOLEAN	布尔类型，true或false	TRUE
FLOAT	单精度浮点数	3.14159	实现的是Java中的float
DOUBLE	双精度浮点数	3.14159
STRING	字符序列。可以指定字符集。可以使用单引号或者双引号	'now is the time',"for all good men"	实现的是Java中的String
TIMESTAMP(v0.0.0+)	整数浮点数或者字符串	1327882394（Unix新纪元秒），1327882394.123456789（Unix新纪元秒并跟随有纳秒数）和'2012-02-03 12:34:56.123456789'(JDBC所兼容的java.sql.Timestamp时间格式)	可以是整数也就是距离Unix新纪元时间(1970年1月1日，午夜12点)的秒数；也可以是浮点数，即距离Unix新纪元时间的秒数精确到纳秒（小数点后保留9位数）；还可以是字符串，即JDBC所约定的时间字符串格式:YYYY-MM-DD hh:mm:ss:fffffffff
BINARY(v0.0.0+)	字节数组	和很多关系型数据库中的VARBINARY数据类型类似，但和BLOB数据类型并不相同。	因为BINARY的列是存储在记录中，而BLOB则不同。BINARY可以在记录中包含任意字节

集合数据类型

数据类型	描述	字面语法示例
STRUCT	和C语言的struct或者“对象”类似，都可以通过“点”符号访问元素内容	struct('John','Doe')
MAP	MAP是一组键值对元组集合，使用数组表示法（例如['key']）可以访问元素。	map('first','JOIN','last','Doe')
ARRAY	数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始	Array('John','Doe')

文本文件数据编码

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

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

分隔符	描述
\n	对于文本文件来说，每行都是一条记录，因此换行符可以分割记录
^A(Ctrl+A)	用于分隔字段(列)。在CREATE TABLE 语句中可以使用八进制编码\001表示
^B	用于分隔ARRARY或者STRUCT中的元素，或用于MAP中键值对之间的分隔。在CREATE TABLE 语句中可以使用八进制编码\002表示
^C	用于MAP中键和值之间的分隔。在CREATE TABLE语句中可以使用八进制编码\003表示

读时模式

当用户向传统数据库写入数据的时候，不管是采用装载外部数据的方式，还是采用将一个查询的输出结果写入的方式，或者使用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;

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

hive> SHOW DATABASES;

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

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

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

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

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

hive> DESCRIBE DATABASE financials;

此外，用户还可以为数据库增加一些和其相关的键值对属性信息，尽管目前仅有功能就是提供了一种可以通过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;

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

hive> DROP DATABASE IF EXISTS financials CASCADE;

修改数据库

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

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

创建表

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

CREATE TABLE IF NOT EXISTS mydb.employees(
    name    STRING COMMENT 'Employee name',
    salary  FLOAT COMMENT 'Employee salary',
    subordinates    ARRAY<STRING> COMMENT 'Name of subordinates',
    deductions  MAP<STRING,FLOAT> COMMENT 'Keys are deductions name,values are percentages',
    address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT> 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';

• 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';

关键字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';

分区表、管理表

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

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

CREATE TABLE employees(
    name STRING,
    salary  FLOAT,
    subordinates    ARRAY<STRING>,
    deductions  MAP<STRING,FLOAT>,
    address     STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>
)
PARTITIONED BY (country STRING,state STRING);

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

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

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

.../employees/country=CA/state=AB
.../employees/country=CA/state=BC
...
.../employees/country=US/state=AL
.../employees/country=US/state=AK

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

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

hive> set hive.mapred.mode=strict;

将Hive设置为非严格模式：

hive> set hive.mapred.mode=nonstrict;

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

hive> SHOW PARTITIONS 表名;

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

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

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';

增加一个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';

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

删除表

DROP TABLE IF EXISTS 表名;

表重命名

ALTER TABLE 表名 RENAME TO 新表名;

增加、修改和删除表分区

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

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'

删除分区：

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

修改列信息

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

如果置顶可将AFTER换为FIRST

增加列

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

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

修改表属性

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

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

修改存储属性

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

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

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

DML

向管理表中装载数据

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

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

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

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

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

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

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

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

这里如果没有使用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'

动态分区插入

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

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

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

动态分区属性

属性名称	缺省值	描述
hive.exec.dynamic.partition	false	设置成true，表示开启动态分区功能
hive.exec.dynamic.partition.mode	strict	设置成nonstrict，表示允许所有分区都是动态的
hive.exec.max.dynamic.partitions.pernode	100	每个mapper或reducer可以创建的最大动态分区个数。如果某个mapper或reducerr尝试创建大于这个值的分区的话则会抛出一个错误
hive.exec.max.dynamic.partitions	+1000	一个动态分区创建语句可以创建的最大动态分区个数。如果超过这个值则会抛出一个错误
hive.exec.max.created.files	100000	全局可以创建的最大文件个数。有一个Hadoop计数器会跟踪记录创建了多少个文件，如果超过这个值会抛出一个错误

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

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

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

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

导出数据

hadoop fs -cp source_path target_path

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

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

DQL

例：

CREATE TABLE IF NOT EXISTS employees(
    name        STRING,
    salary      FLOAT,
    subordinates    ARRAY<STRING>,
    deductions      MAP<STRING,FLOAT>,
    address     STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>
)
PARTITIONED BY (country STRING,state STRING);

下面两个查询是等价的：

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

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

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

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}

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}

查询数组的第一个元素:

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

查询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

查询STRUCT中的一个元素：

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

使用正则表达式来指定列

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

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

使用列值进行计算

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

算术运算符

运算符	类型	描述
A+B	数值	A与B相加
A-B	数值	A减去B
A*B	数值	A与B相乘
A/B	数值	A除以B，如果能整除那么返回商（商是一个整数，表示在不考虑有余数的情况下，除数可以被除数的次数）
A%B	数值	A除以B的余数
A&B	数值	A和B按位取与
A|B	数值	A和B按位取或
A^B	数值	A和B按位取异或
~A	数值	A按位取反

数学函数

返回值类型	样式	描述
BIGINT	round(DOUBLE d)	返回DOUBLE型d的BIGINT类型的近似值
DOUBLE	round(DOUBLE d,INT n)	返回DOUBLE型d的保留n位小数的DOUBLE型的近似值
BIGINT	floor(DOUBLE d)	d是DOUBLE类型的，返回<=d的最大BIGINT型值
BIGINT	ceil(DOUBLE d) ceiling(DOUBLE d)	d是DOUBLE类型的，返回>=d的最小BIGINT型值
DOUBLE	rand() rand(INT seed)	每行返回一个DOUBLE型随机数，整数seed是随机因子
DOUBLE	exp(DOUBLE d)	返回e的d次幂，返回的是个DOUBLE型值
DOUBLE	ln(DOUBLE d)	以自然数为底d的对数，返回DOUBLE型值
DOUBLE	log10(DOUBLE d)	以10为底d的对数，返回DOUBLE型值
DOUBLE	log2(DOUBLE)	以2为底d的对数，返回DOUBLE型值
DOUBLE	log(DOUBLE base,DOUBLE d)	以base为底d的对数，返回DOUBLE型值，其中base和d都是DOUBLE型的
DOUBLE	pow(DOUBLE,DOUBLE p) power(DOUBLE d,DOUBLE p)	计算d的p次幂
DOUBLE	sqrt(DOUBLE d)	计算d的平方根
STRING	bin(BIGINT i)	计算二进制值i的STRING类型值
STRING	hex(BIGINT i)	计算十六进制值i的STRING类型值
STRING	hex(STRING str)	计算十六进制表达式的值str的STRING类型值
STRING	hex(BINARY b)	计算二进制表达的值b的STRING类型值
STRING	unhex(STRING i)	hex(STRING str)的逆方法
STRING	conv(BIGINT num,INT from_base,INT to_base)	将BIGINT类型的num从from_base进制转换为to_base进制并返回STRING类型结果
STRING	conv(STRING num,INT from_base,INT to_base)	将STRING类型的num从from_base进制转换成to_base进制，并返回STRING类型结果
DOUBLE	abs(DOUBLE d)	计算DOUBLE型值d的绝对值
INT	pmod(INT i1,INT i2)	INT值i1对INT值i2取模
DOUBLE	pmod(DOUBLE d1,DOUBLE d2)	DOUBLE值d1对DOUBLE值d2取模
DOUBLE	sin(DOUBLE d)	在弧度度量中返回d的正弦值
DOUBLE	asin(DOUBLE d)	在弧度度量中返回d的反正弦值
DOUBLE	cos(DOUBLE d)	在弧度度量中返回d的余弦值
DOUBLE	acos(DOUBLE d)	在弧度度量中返回d的反余弦值
DOUBLE	tan(DOUBLE d)	在弧度度量中返回正切值
DOUBLE	atan(DOUBLE d)	在弧度度量中返回反正切值
DOUBLE	degrees(DOUBLE d)	将弧度值d转换成角度值
DOUBLE	radians(DOUBLE d)	将角度值转换为弧度值
INT	positive(INT i)	返回INT型i(其等价的有效表达式是\+i)
DOUBLE	positive(DOUBLE d)	返回DOUBLE型d(其等价的有效表达式是\+d)
INT	nagative(INT i)	返回INT型值i的负数(其等价的有效表达式是-i)
DOUBLE	negative(DOUBLE d)	返回DOUBLE型值d的负数(其等价有效表达式是-d)
FLOAT	sign(DOUBLE d)	如果DOUBLE型值d是整数，则返回FLOAT值1.0；如果d是负数的话，则返回-1.0；否则返回0.0
DOUBLE	e()	数学常数e
DOUBLE	pi()	数学常数pi

聚合函数

返回值类型	样式	描述
BIGINT	count(*)	计算总行数，包括含有NULL值得行
BIGINT	count(expr)	计算提供得expr表达式的值非NULL得行数
BIGINT	count(DISTINCT expr[,expr_.])	计算提供的expr表达式的值去重后非NULL的行数
DOUBLE	sum(col)	计算指定行的值的和
DOUBLE	sum(DISTINCT col)	计算去重后值得和
DOUBLE	avg(col)	计算指定行的平均值
DOUBLE	avg(DISTINCT col)	计算去重后的平均值
DOUBLE	min(col)	计算指定行的最小值
DOUBLE	max(col)	计算指定行的最大值
DOUBLE	variance(col),var_pop(col)	返回集合col中的一组数值的方差
DOUBLE	var_samp(col)	返回一组数值的样本方差
DOUBLE	stddev_pop(col)	返回一组数值的标准偏差
DOUBLE	stddev_samp(col)	返回一组数值的标准样本偏差
DOUBLE	covar_pop(col1,col2)	返回一组数值的协方差
DOUBLE	covar_samp(col1,col2)	返回一组数值的样本协方差
DOUBLE	corr(col1,col2)	返回两组数值的相关系数
DOUBLE	percentile(BIGINT int_expr,p)	int_expr在p（范围是：[0,1]）处的对应的百分比
ARRAY<DOUBLE>	percentile(BIGINT int_expr,ARRAY(P1[,P2]...))	int_expr在p(范围是：[0,1])处的对应的百分比
DOUBLE	percentile_approx(DOUBLE col,p[,NB])	col在p（范围是[0,1]）处对应的百分比，NB是用于估计的直方图中仓库数量(默认是10000)
DOUBLE	percentile_approx(DOUBLE col,ARRAY(p1[,p2]...)[,NB])	col在p（范围是[0,1]）处对应的百分比，NB是用于估计的直方图中仓库数量(默认是10000)
ARRAY<STRUCT{'x','y'}>	histogram_numeric(col,NB)	返回NB数量的直方图仓库数组，返回结果中的值x是中心，值y是仓库的高
ARRAY	collect_set(col)	返回集合col元素去重后的数组

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

hive> SET hive.map.aggr=true;

表生成函数

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

返回值类型	样式	描述
N行结果	explode(ARRAY array)	返回0到多行结果，每行都对应输入的array数组中的一个元素
N行结果	explode(MAP map)	返回0到多行结果，每行对应每个map键-值对，其中一个字段是map键，另一个字段对应map的值
数组的类型	explode(ARRAY<TYPE> a)	对于a中的每个元素，explode()会生成一行记录包含这个元素
结果插入表中	inline(ARRAY<STRUCT[,STRUCT]>)	将结构体数组提取出来并插入到表中
TUPLE	json_tuple(STRING jsonStr,p1,p2,...,pn)	本函数可以接受多个标签名称，对输入的JSON字符串进行处理，这个get_json_object这个UDF类似，不过更高效，其通过一次调用就可以获得多个键值
TUPLE	parse_url_tuple(url,partname1,partname2,...,partnameN)其中N>=1	从URL中解析出N个部分信息。其输入参数是：URL，以及多个要抽取的部分的名称。所有输入的参数的类型都是STRING。
N行结果	stack(INT n,col1,...,colM)	把M列转换成N行每行有M/N个字段。其中n必须是个常数

其他内置函数

返回值类型	样式	描述
STRING	ascii(STRING s)	返回字符串s中首个ASCII字符的整数值
STRING	base64(BINARY bin)	将二进制值bin转换成基于64位的字符串
BINARY	binary(STRING s) binary(BINARY b)	将输入的值转换成二进制
返回类型就是type定义的类型	cast(<expr> as < type >)	将expr转换成type类型，如果转换过程失败，则返回NULL
STRING	concat(BINARY s1,BINARY s2,...)	将二进制字节码按次序拼接成一个字符串
STRING	concat(STRING s1,STRING s2,...)	将字符串s1,s2等拼接成一个字符串
STRING	concat_ws(STRING separator,STRING s1,STRING s2,...)	和concat类似，不过使用指定的分隔符进行拼接
STRING	concat_ws(BINARY separator,BINARY s1,STRING s2,...)	和concat类似，不过使用指定的分隔符进行拼接
ARRAY<STRUCT<STRING,DOUBLE>>	context_ngrams(array<array<string>>,array<string>,int K,int pf)	和ngrams类似，但是从每个外层数组的第二个单词数组来查找前K个字尾
STRING	decode(BINARY bin,STRING charset)	使用指定的字符集charset将二进制bin解码成字符串
BINARY	encode(STRING src,STRING charset)	使用指定的字符集charset将字符串src编码成二进制值
INT	find_in_set(STRING s,STRING commaSeparatedString)	返回以逗号分隔的字符串中s出现的位置，如果没有找到则返回NULL
STRING	format_number(NUMBER x,INT d)	将数值x转换成'#,###,###.##'格式字符串，并保留d位小数。如果d为0，那么输出值就没有小数点后面的值
STRING	get_json_object(STRING json_string,STRING path)	从给定路径上的JSON字符串中抽取JSON对象，并返回这个对象的JSON字符串形式
BOOLEAN	in	例如，test in (val1,val2,...)其表示如果test值等于后面列表中任一值的话，则返回true
BOOLEAN	in_file(STRING s,STRING filename)	如果文件名为filename的文件中有一行数据和字符串s完全匹配的话，则返回true
INT	instr(STRING str,STRING substr)	查找字符串str中子字符串substr第一次出现的位置
INT	length(STRING s)	计算字符串s的长度
INT	locate(STRING substr,STRING str[,INT pos])	查找在字符串str中的pos位置后字符串substr第一次出现的位置
STRING	lower(STRING s)	将字符串中所有字母转换成小写字母
STRING	lcase(STRING s)	和lower()一样
STRING	lpad(STRING s,INT len,STRING pad)	从左边开始对字符串s使用字符串pad进行填充，最终达到len长度为止。如果字符串s本身长度比len大的话，那么多余的部分会被去除掉
STRING	ltrim(STRING s)	将字符串s前面出现的空格全部去除掉
ARRAY<STRUCT<STRING,DOUBLE>>	ngrams(ARRAY<string>,INT N,INT K,INT pf)	估算文件中前K个字尾。pf是精度系数
STRING	parse_url(STRING url,STRING partname[,STRING key])	从URL中抽取指定部分的内容
STRING	printf(STRING format,Obj...args)	按照printf风格格式化输出输入字符串
STRING	regexp_extract(STRING subject,STRING regex_pattern,STRING index)	抽取字符串subject中符合正则表达式regex_pattern的第index个部分的子字符串
STRING	regexp_replace(STRING s,STRING regex,STRING replacement)	按照Java正则表达式regex将字符串s中符合条件的部分替换成replacement所指定的字符串a
STRING	repeat(STRING s,INT n)	重复输出n次字符串s
STRING	reverse(STRING s)	反转字符串
STRING	rpad(STRING s,INT len,STRING pad)	从右边开始对字符串s使用字符串pad进行填充，最终达到len长度为止
STRING	rtrim(STRING s)	将字符串s后面出现的空格全部去除掉
ARRAY<ARRAY<STRING>>	sentences(STRING s,STRING lang,STRING locale)	将输入字符串s转换成句子数组，每个句子又由一个单词数组构成参数lang和locale是可选的，如果没有使用的，则使用默认的本地化信息
INT	size(MAP<K,V>)	返回MAP中元素的个数
INT	size(ARRAY<T>)	返回数组ARRAY的元素个数
STRING	space(INT n)	返回n个空格
ARRAY<STRING>	split(STRING s,STRING pattern)	按照正则表达式pattern分割字符串s并将分割后的部分以字符串数组的方式返回
MAP<STRING,STRING>	str_to_map(STRING s,STRING delim1,STRING delim2)	将字符串s按照指定分隔符转换成Map，第一个参数是输入的字符串，第二个参数是键值对之间的分隔符，第三个分隔符是键和值之间的分隔符
STRING	substr(STRING s,STRING start_index) substring(STRING s,STRING start_index))	对于字符串s，从start位置开始截取length长度的字符串，作为子字符串
STRING	substr(BINARY s,STRING start_index) substring(BINARY s,STRING start_index))	对于二进制字节值s，从start位置开始截取length长度的字符串，作为子字符串
STRING	translate(STRING input,STRING from,STRING to)
STRING	trim(STRING A)	将字符串s前后出现的空格全部去除掉
BINARY	unbase64(STRING str)	将基于64位的字符串str转换成二进制值
STRING	upper(STRING A) ucase(STRING A)	将字符串中所有字母转换成大写字母
STRING	from_unixtime(BIGIN T unixtime[,STRING format])	将时间戳秒数转换为UTC时间，并用字符串表示，可以通过format规定的时间格式，指定输出的时间格式
BIGINT	unix_timestamp()	获取当前本地时区下的当前时间戳
BIGINT	unix_timestamp(STRING date)	输入的时间字符串格式必须是yyyy-MM-dd HH:mm:ss，如果不符合规则返回0，如果符合则将此时间字符串转换成Unix时间戳
BIGINT	unix_timestamp(STRING date,STRING pattern)	将指定时间字符串格式字符串转换成Unix时间戳，如果格式不对则返回0
STRING	to_date(STRING timestamp)	返回时间字符串的日期部分
INT	year(STRING date)	返回时间字符串中的年份并使用INT类型表示
INT	month(STRING date)	返回时间字符串中的月份并使用INT类型表示
INT	day(STRING date) dayofmonth(STRING date)	返回时间字符串中的天并使用INT类型表示
INT	hour(STRING date)	返回时间戳字符串中的小时并使用INT类表示
INT	minute(STRING date)	返回时间字符串中的分钟数
INT	second(STRING date)	返回时间字符串中的秒数
INT	weekofyear(STRING date)	返回时间字符串位于一年中的第几周
INT	datediff(STRING enddate,STRING startdate)	计算开始时间startdate到结束时间enddate相差的天数
STRING	date_add(STRING startdate,INT days)	位开始时间startdate增加days天
STRING	date_sub(STRING startdate,INT days)	从开始时间startdate中减去days天
TIMESTAMP	from_utc_timestamp(TIMESTAMP timestamp,STRING timezone)	如果给定的时间戳并非UTC则将其转化成指定的时区下的时间戳
TIMESTAMP	to_utc_timestamp(TIMESTAMP timestamp,STRING timezone)	如果给定的时间戳是指定的时区下的时间戳，则将其转化成UTC下的时间戳

LIMIT语句

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

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

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;

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

一般情况下

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

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

WHERE子句中的谓词操作符

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE否则返回FALSE
A<=>B	基本数据类型	如果A和B都为NULL则返回TRUE，其他的和等号操作符一致
A<>B,A!=B	基本数据类型	A或者B为NULL则返回NULL，如果A不等于B则返回TRUE,否则返回FALES
A<B	基本数据类型	A或者B为NULL则返回NULL，如果A小于B则返回TRUE，否则返回FALSE
A<=B	基本数据类型	A或者B为NULL则返回NULL，如果A小于等于B则返回TRUE，否则返回FALSE
A>B	基本数据类型	A或者B为NULL则返回NULL，如果A大于B则返回TRUE，否则返回FALSE
A>=B	基本数据类型	A或者B为NULL则返回NULL，如果A大于等于B则返回TRUE，否则返回FALSE
A[NOT]BETWEEN B AND C	基本数据类型	如果A,B或者C任意为NULL则结果为NULL，如果A的值大于或等于B且小于或等于C则返回TRUE否则返回FALSE
A IS NULL	所有数据类型	如果A等于NULL则返回TRUE否则返回FALSE
A IS NOT NULL	所有数据类型	如果A不等于NULL则返回TRUE否则返回FALSE
A[NOT]LIKE B	STRING类型	B是一个SQL下的简单正则表达式，如果A与其匹配则返回TRUE，否则返回FALSE
A RLIKE B,A REGEXP B	STRING类型	B是一个正则表达式，如果A与其匹配则返回TRUE否则返回FALSE

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;

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的并行性，然而这样可以实现输出文件的数据是全局排序的。