Hive

By | 2018年11月26日

启动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)*;

发表评论