Hive，

启动hive

Hive shell

#交互式hive shell
hive

#非交互式hive shell
hive -e 'command'
hive -f script.q

hive启动为一个服务器，来对外提供服务

#启动hive server
hiveserver2
#不挂起、后台运行hive server，丢弃日志
nohup hiveserver2 1>/dev/null 2>/dev/null &

连接hive服务

beeline -u jdbc:hive2://node1:10000 -n root

数据类型

类型	描述	示例
BOOLEAN	true/false	TRUE
TINYINT	1byte	1Y
SMALLINT	2byte	1S
INT	4byte	1
BIGINT	8byte	1L
FLOAT	4byte单精度	1.0
DOUBLE	8byte双精度	1.0
DECIMAL	任意精度	1.0
STRING	无限上限可变长度	‘a’,“a”
VARCHAR	可变长字符串	‘a’,“a”
CHAR	固定长度字符串	‘a’,“a”
BINARY	字节数组	不支持
TIMESTAMP	精确到纳秒时间戳
DATE	日期
--------------------	---------------------------	-------------
ARRAY	一组有序字段	array(1,2)
MAP	一组无序键值对	map(‘a’,1,‘b’,2)
STRUCT	一组命名的字段	struct(‘a’,1,2.0)
UNION	多个数据类型中的任意一个	create_union(1,‘a’,63)

CREATE TABLE example (
	e1 ARRAY<INT>,
    e2 MAP<STRING,INT>,
    e3 STRUCT<a:STRING,b:INT,c:DOUBLE>,
    e4 UNIONTYPE<STRING,INT>
);

操作与函数

-- 显示内置函数列表
SHOW FUNCTIONS;

-- 查询特定函数描述,例如length
DESCRIBE FUNCTION length;

操作或函数如有需要会对数据进行隐式转换，转换规则类似Java

可以自行强制转换，如：CAST(‘1’ AS INT)

本地模式

-- 开启自动尝试使用本地模式
set hive.exec.mode.local.auto=true;

表

托管表和外部表

托管表从HDFS装载数据时把数据转移到仓库目录，删除表时会将表目录及文件删除

外部表不会转移数据，删除表时也不会删除数据本身，而只会删除元数据

-- 内部表，可以不用指定表目录位置，默认为/user/hive/warehouse/
CREATE TABLE managed_table (column string) 
ROW FORMAT DELIMITED
	FIELDS TERMINATED BY ',';
LOAD DATA INPATH '/path../data.txt' INTO TABLE managed_table;

-- 外部表，需要指定外部表目录位置（到表名）
CREATE EXTERNAL TABLE external_table (column string)
ROW FORMAT DELIMITED
	FIELDS TERMINATED BY ','
LOCATION '/path../external_table';
LOAD DATA INPATH '/path../data.txt' INTO TABLE external_table;

分区

-- 创建表时指定分区字段，不能与表字段相同，可以多个
CREATE TABLE table_name (column string) 
PARTITIONED BY (other_column string)
ROW FORMAT DELIMITED
	FIELDS TERMINATED BY ',';

-- 装载数据必须指定分区，分区字段以文件夹名形式存储与表目录下
LOAD DATA INPATH '/path../data.txt'
INTO TABLE table_name
PARTITION (other_column='partition_name');

分桶

CREATE TABLE table_name (column string) 
CLUSTERED BY (column) INTO 3 BUCKETS -- 指定分桶数量
ROW FORMAT DELIMITED
	FIELDS TERMINATED BY ',';


-- 强制Hive为目标表的分桶初始化过程设置一个正确的reduce task个数
set hive.enforce.bucketing=true;
-- 如果没有设置上述属性，则需要手动设置与分桶个数相匹配的reduce task个数
-- 并且在插入的SELECT语句中增加CLUSTER BY语句
set mapred.reduce.tasks=3;		-- 老
set mapreduce.job.reduces=3;	-- 新，两个属性是同一个参数，对应不同版本hive

FROM src
INSERT OVERWRITE TABLE dest
SELECT column WHERE ... [CLUSTER BY column];

HiveQL

标准函数(UDF)

-- concat(str1, str2, ... strN) 
-- 	returns the concatenation of str1, str2, ... strN
-- concat(bin1, bin2, ... binN) 
-- 	returns the concatenation of bytes in binary data  bin1, bin2, ... binN
concat()

-- concat_ws(separator, [string | array(string)]+) 
-- returns the concatenation of the strings separated by the separator.
concat_ws()

-- split(str, regex) - Splits str around occurances that match regex
split()

聚合函数(UDAF)

-- collect_list(x) - Returns a list of objects with duplicates
collect_list()

-- collect_set(x) - Returns a set of objects with duplicate elements eliminated
collect_set()

表生成函数(UDTF)

-- explode(a) - separates the elements of array a into multiple rows
-- or the elements of a map into multiple rows and columns
explode()

其他关键字

-- LATERAL VIEW   横向视图用法，常与explode搭配使用
select column_name from table_name
LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)*;

-- 开窗函数over，以row_number搭配函数为例
-- 有order by子句时，窗口子句默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- 省略order by子句时，窗口子句默认为整个组
-- 第一行是 unbounded preceding，
-- 当前行是 current row，
-- 最后一行是 unbounded following，
-- 窗口子句不能单独出现，必须有order by子句时才能出现
select column,row_number() over(patition by ... [order by ...]
           						[RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW])
from table_name;

常用技巧

列转行

select concat_ws(',', collect_set(column1)),column2
from table_name
group by column2;

行转列

select column1 from table_name
LATERAL VIEW explode(column2) tableAlias AS columnAlias (‘,’ columnAlias)*;