Hive常用命令

Hive简介：

Hive：由Facebook开源用于解决海量结构化日志的数据统计工具。
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。

Hive数据类型

基本数据类型

HIVE	MySQL	JAVA	长度	例子
TINYINT	TINYINT	byte	1byte有符号整数	2
SMALINT	SMALINT	short	2byte有符号整数	20
INT	INT	int	4byte有符号整数	20
BIGINT	BIGINT	long	8byte有符号整数	20
BOOLEAN	无	boolean	布尔类型，true或者false	TRUE FALSE
FLOAT	FLOAT	float	单精度浮点数	3.14159
DOUBLE	DOUBLE	double	双精度浮点数	3.14159
STRING	VARCHAR	string	字符系列。可以指定字符集。可以使用单引号或者双引号。	‘now is the time’ “for all good men”
TIMESTAMP	TIMESTAMP		时间类型
BINARY	BINARY		字节数组

集合数据类型

数据类型	描述	语法示例
STRUCT	和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。2	struct() 例如 struct<street:string, city:string>
MAP	MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素	map() 例如map<string, int>
ARRAY	数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。	Array() 例如array

Hive命令：

	数据定义语言，DDL
库操作
查看所有库	show databases
查看库的信息	desc database [extended] 库名	extended：查看库的属性内容
模糊匹配查库	show databases like ‘匹配名’
使用**数据库	use 数据库名
删除数据库	drop database 数据库名 [cascade]	cascade：删除非空库
创建数据库	create database [if not exists] 库名 [comment ‘库的描述信息’] [location ‘库的路径’] [with dbproprerties (‘key’=‘value’, …)];	location：指定库的路径，默认配置(/user/hive/warehouse) with dbproperties：给库添加的属性，K,V都是string
修改库属性	alter database 库名 set dbproperties(‘key’=‘内容’)	只能修改属性,不能改数据库名和数据库所在的目录位置
表格操作
查看所有库	show tables
查表信息	desc [formatted] 表名
创 建 表	create [external] table [if not exists] 表名 [(字段名 字段类型 [comment 字段描述信息], …)] [comment 表描述信息] [partitioned by (字段名 数据类型 [comment 字段描述信息], …)] [clustered by (字段名, 字段名, …) [sorted by (字段名 [ASC|DESC], …)] into 分桶的个数 buckets] [row format row_format] [stored as 文件存储格式] [location ‘hdfs的path’] [tblproperties (‘key’=‘value’, …)] [as select_statement] [like 表名]	EXTERNAL：加该字段是创建外部表，否则为内部表(管理表) 表中需要的字段名、类型信息 指定分区的字段 指定分桶的字段 如果该表是一个分桶表在向表中添加数据时会自动根据该字段进行排序（几乎不用） [row format row_format] #数据中的字段是按照什么分割开 row format delimited fields terminated by ‘,’ #集合数据类型之间的元素是按照什么分割开 collection items terminated by ‘_’ #map类型的数据k,v之间用什么分割开 map keys terminated by ‘:’ #每条数据之间用什么分割开–默认就是一行一条数据。 lines terminated by ‘\n’;
删除表	drop table ***
修改表名	alter table 原表名 rename to 新表名
修改表属性	alter table 表名 set tblproperties(‘key’=‘value’);
改字段名/类型	alter table 表名 change [column] 原字段名 新字段名 字段类型 [comment 字段描述信息] [first/after 字段名]	如果要调整字段的顺序， 必须类型一致（或可以自动类型转换），否则报错。
添加列	alter table 表名称 add columns 字段名 数据类型 [comment 描述]
替换列	alter table 表名称 replace columns (字段名 字段类型 [comment 描述])	①字段的类型是否可以换转 ②替换的字段顺序要按照原字段的顺序依次替换 ③如果替换的字段少于原字段的个数，那么原字段中其它没有被替换的字段会被删除。
清空表	truncate table 表名	只能清空管理表
	数据操作语言，DML
数据导入	load data [local] inpath ‘数据的path’ [overwrite] into table student [partition (partcol1=val1,…)];	local :如果添加该字段从本地导入数据，否则从HDFS上导入数据 overwrite ：如果添加该字段会被原来的内容进行覆盖，否则是追加
添加数据	insert into/overwrite table 表名[(字段名,…)] [partition(分区名=‘值’,…)] values(值…);
	insert into/overwrite table 表名[(字段名,…)] [partition(分区名=‘值’,…)] SQL查询语句
	import table 表名 from ‘HDFS的路径’;	注意： ①表必须不存在否则报错。 ②导入的数据必须是通过Export导出的数据
数据导出	insert overwrite [local] directory ‘路径’ [row format row_format]	row format delimited fields terminated by ‘\t’ 不加数据会连在一块
	hive -e ‘sql语句’ > 本地路径;
	shell：hadoop fs -get HDFS表中文件的路径 本地路径
	hive：dfs -get HDFS表中文件的路径 本地路径
	export table 库名.表名 to ‘HDFS的路径’;
查询
	select null+‘a’ //结果为null
	SELECT [distinct]列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列 LIMIT 起始行，总条数
like	% 代表零个或多个字符(任意个字符)。 _ 代表一个字符。
rlike	后跟Java的正则表达式
between	A [NOT] BETWEEN B AND C //A小于等于C，大于等于B
join	只支持等值连接，不支持非等值连接。
内连接	join…on
左外连接	left join…on
右外连接	right join …on
满外连接	full join…on
多表连接	join…on…join…on
排序
全局排序	order by 字段1,字段二
Reducer内部排序
	set mapreduce.job.reduces=3; //设置reduce个数
	set mapreduce.job.reduces; //查看reduce个数
	sort by 字段名 //每个redeuce中的排序
分区查询	distribute by 字段名
	cluster by 字段名	内部排序和分区排序按照统一字段名且升序排序，简写
分区表
创建分区表	create table 表名(字段名 字段类型) partitioned by(字段名 字段类型,字段名 字段类型)
添加分区	alter table 表名 add partition(分区名=‘分区信息’) partition(分区名=‘分区信息’)
删除分区	alter table 表名 drop partition(分区名=‘分区信息’) partition(分区名=‘分区信息’)
直接在HDFS上创建目录并导入数据不能识别
法一：	修复：	msck repair table 表名
法二：	直接添加分区：	alter table 表名 add partition(分区字段名=‘分区内容’);
法三：	直接load数据到该分区：	load data local inpath ‘数据的路径’ into table 表名 partition(分区字段=‘分区的内容’);
动态分区	开启动态分区：hive.exec.dynamic.partition=true 默认开启
	设置非严格模式：hive.exec.dynamic.partition.mode=nonstrict
	所有MR上可执行的分区数：hive.exec.max.dynamic.partitions=1000
	每个MR上可执行的分区数：hive.exec.max.dynamic.partitions.pernode=100 建议设置365
	MRjob中创建HDFS数：hive.exec.max.created.files=100000
	有空分区生成时，是否抛出异常：hive.error.on.empty.partition=false 默认不抛出
例程：	create table stu2( name string) partitioned by(io int) row format delimited fields terminated by ‘\t’;
insert into table stu2 partition(io) select name,id from stu; //按照id分区，要将id放在末尾
查询分区信息	show partitions 表名;
分桶表
创建分桶表	create table 表名(字段名 字段类型…) clustered by(字段名) //必须同建表中的字段名 into 数字 buckets [row format row_format]
导入数据	load data inpath ‘HDFS路径’ into table 表名;	hive新版本load数据跑mr，因此要改用hdfs路径导数据
函数
行转列	concat(string 字符名,…)
	concat_ws(‘分隔符’,字符串…)
	collect_set(字段)	只接受基本数据类型，去重汇总，产生array类型字段。
	collect_list(字段)	类型是string or array，汇总，产生array类型字段。
列转行	split(str, separator)	将字符串按照后面的分隔符切割，转换成字符array。
	explode(字段名)	将hive一列中复杂的array或者map结构拆分成多行。
示例	select 字段名,… from 表名 lateral view explode(数组) 表名 as 字段名 //必须添加lateral view
NVL	NVL( value，default_value) //如果value为0，赋值为default_value
判断	CASE WHEN THEN ELSE END
窗口函数
数据窗口大小	参数 OVER([partition by 字段名 order by 字段名 rows between 起始位置 and 结束位置])：
当前行	current row
向前n行	n preceding
向后n行	n following
无边界前起	unbounded preceding
无边界后起	unbounded following
往前n行	lag(字段名,n,default_val) //n是往前n行，default_val是默认值，第一条数据之前没有值，就会使用默认值
往后n行	lead(字段名,n,default_val)
当前窗口下第一个值	FIRST_VALUE (字段名,true/false) //true跳过空值
当前窗口下最后一个值	LAST_VALUE (字段名,true/false)
ntile(n)	分成 n组，并加编号
RANK
ran()	排序相同时会重复(考虑并列,会跳号)，总数不会变
dense_rank()	排序相同时会重复(考虑并列,不跳号)，总数会减少
row_number()	会根据顺序计算(不考虑并列,不跳号,行号)
自定义函数
1,建maven工程	org.apache.hive hive-exec 3.1.2
2,创建自定义类	public class MyLength extends GenericUDF
临时函数
	1、打成jar包上传到服务器
	2、add jar /路径/jar包名.jar; //将jar包添加到hive的classpath
	3、create temporary function 函数名 as “jar包引用”; //创建临时函数与开发好的java class关联
	4、在hql中使用自定义的临时函数
	5、drop temporary function 函数名; //删除函数
永久函数
	1、mkdir auxlib //在$HIVE_HOME下面创建auxlib目录
	2、将jar包上传到$HIVE_HOME/auxlib下，重启hive
	3、create function 函数名 as " jar包引用"; //创建永久函数
	4、drop function 函数名; //删除永久函数
压缩
map阶段
中间传输数据压缩	set hive.exec.compress.intermediate=true;
开启mapreduce中map输出压缩功能	set mapreduce.map.output.compress=true;
设置mapreduce中map输出数据的压缩方式	set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
开启本地模式	set hive.exec.mode.local.auto=true;