Hive sql常用语句

1.建库语句：
	CREATE DATABASE [IF NOT EXISTS] database_name
	[COMMENT database_comment]
	[LOCATION hdfs_path]
	[WITH DBPROPERTIES (property_name=property_value, ...)];
		例：
			create DATABASE if NOT EXISTS hive_db2
			comment "my first database"
			location "/hive_db2"
2.库的修改：
	alter database hive_db2 set DBPROPERTIES ("createtime"="2018-12-19");
3.库的删除
	drop database db_hive cascade if exists; -- 删除存在表的数据库
3.建表语句：
	CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
	[(col_name data_type [COMMENT col_comment], ...)] 
	[COMMENT table_comment] 
	[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
	[CLUSTERED BY (col_name, col_name, ...) 
	[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
	[ROW FORMAT row_format] 
	DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
			[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] 
	   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
	[STORED AS file_format] Textfile
	[LOCATION hdfs_path]
	[TBLPROPERTIES (property_name=property_value, ...)]
	[AS select_statement]
		例1：
			create table student2(id int COMMENT "xuehao", name string COMMENT "mingzi")
			COMMENT "xueshengbiao"
			ROW format delimited
			fields terminated by '\t'
			STORED as Textfile
			location '/student2' -- 直接加载该目录下的数据文件到表中
			TBLPROPERTIES ("createtime"="2018-12-18");
		例2：
			create table student(id int, name string)
			row format delimited
			fields terminated by '\t';
			load data local inpath '/opt/module/datas/student.txt' into table student;
		例3：
			create table student4 like student2; -- 仅复制表结构
4.导入数据语句
	4.1 不加local则导入hdfs上文件，但会剪贴原文件，local本地仅粘贴
			load data [local] inpath '/opt/module/datas/student.txt' 
			[overwrite] into table student [partition (partcol1=val1,…)];
	4.2 创建表并导入数据（依据以存在的表）
			create table student6 as select * from student; -- 仅导入数据，不会导入其他细节属性 --被创建表不能是外部表 -- 被创建表不支持分区及分桶
	4.3 覆盖插入
			insert overwrite table student3 select * from student;
	4.4 插入带分区的表
			insert into table stu_par partition(month = '08') select id ,name from stu_par where month = '09';
	4.5 将单表中数据导入多表
			from student
			insert into table student4 select *
			insert into table student5 select *;
	4.6 多分区导入单表
			from stu_par
			insert into table stu_par partition(month = '06')
			select id ,name where month = '08'
			insert into table stu_par partition(month = '07')
			select id,name where month = '10';
5.表的修改操作
	5.1 修改表的属性
			alter table stu_ex set TBLPROPERTIES ('EXTERNAL' = 'TRUE');
	5.2 重命名表名
			alter table student4 rename to student3;
	5.3 修改表的serde属性（序列化和反序列化）
		alter table table_name set serdepropertyes('field.delim'='\t');
6.列的更新操作
	6.1 修改列语法
		ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
	6.2 增加或替换列语法
		ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 
		例1：
			增加列：alter table student2 add COLUMNS (score double);
		例2：
			修改列：alter table student2 CHANGE COLUMN score score int AFTER id;
		例3：
			替换列（全部替换）：alter table student2 replace COLUMNS (id int, name string);
7.带有分区的表
	7.0 查看分区
			show partitions table_name;
	7.1 创建单个分区
			create table stu_par(id int, name string)
			partitioned by (month string)
			ROW format delimited
			FIELDS terminated by '\t';
		-- 错误示例
			create table stu_par2(id int, name string)
			partitioned by (id int)
			ROW format delimited
			FIELDS terminated by '\t';  错！！！！（不能以数据库字段作为分区）
		-- 加载数据到指定分区(分区不存在则自动创建)
			load data local inpath '/opt/module/datas/student.txt' into table stu_par partition(month = '12');
			load data local inpath '/opt/module/datas/student.txt' into table stu_par partition(month = '11');

		-- 合并分区查询结果
			select * from stu_par where month = '11'
			union
			select * from stu_par where month = '12';
	7.2 增加多个分区
		alter table stu_par add partition (month = '08') partition(month='07');
	7.3 删除多个分区
		alter table stu_par drop partition(month='08'),partition(month='09');
	7.4 创建多级分区
		create table stu_par2(id int, name string)
		partitioned  by (month string, day string)
		row format delimited
		FIELDS terminated by '\t';
	7.5 导入数据到多级分区
		load data local inpath '/opt/module/datas/student.txt' into table stu_par2 
		partition (month='12',day='19');
	7.6 向多级分区增加分区
		alter table stu_par2 add partition(month = '12', day = '17');
	7.7 查询多级分区中的数据
		select * from stu_par2 where day = '18';
	7.8 修复分区（也可以使用添加分区的语句）
		msck repair table dept_partition2;
8.创建外部表（删除表不会删除表中数据，仅删除表的元数据）
	create external table stu_ex2(id int, name string)
	ROW format delimited
	FIELDS terminated by '\t'
	location '/student';
	8.1 外部表与内部表的转换
		alter table stu_ex set TBLPROPERTIES ('EXTERNAL' = 'TRUE');
9.数据的导出
	9.1 导出同时格式化（不加local则导出到hdfs）
		insert overwrite local directory '/opt/module/datas/student'
		row format delimited
		fields terminated by '\t'
		select * from student;
	9.2 hadoop命令导出到本地
		dfs -get /user/hive/warehouse/student/month=201709/000000_0 /opt/module/datas/export/student3.txt;
	9.3 shell命令导出
		hive -f/-e 执行语句或者脚本 > file	-- -f跟文件，-e跟执行语句
	9.4 export仅可以导出到hdfs，常用于hdfs集群hive表迁徙
		export table default.student to '/user/hive/warehouse/export/student'; -- 同时会导出表的元数据
10.数据的导入(仅能导入export导出的数据，因为需要获取表的元数据)
	import table table_name from 'export导出数据的路径';
11.清除表中数据
	truncate table student; -- 只能删除管理表，不能删除外部表中数据
12.Like、RLike：RLike可以使用java的正则表达式
13.group by及having的使用 -- hive中对于使用group by后查询字段仅限group by的字段及聚合函数
	select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
14.mapreduce的join	
	14.1 mapreduce中的reducejoin特点：在mapper阶段进行数据关联标记，在reducer阶段进行数据聚合
	14.2 mapreduce中的mapjoin特点：将小表加载到内存中，在mapper阶段根据内存中的数据对大表进行数据处理，没有reduce阶段
15.HQL的join
	15.1 仅支持等值连接不支持非等值连接
		例：不支持select * from A left join B on A.id != B.id;
	15.2 不支持在on条件中使用‘or’
	15.3 每个join都会启动一个mapreduce任务，但hive默认开启mapreduce优化
		关闭mapreduce优化：set hive.auto.convert.join=false;
16.order by
	会进行全局排序，则reduce数量被看作1个，效率低下
17.sort by -- 局部排序
	对于每个mapreduce各分区进行局部排序，分区中的数据随机给定
18.distribute by
	18.1 即mapreduce中自定义分区操作，hql书写规则：先分区后排序  
	18.2 distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。
19.cluster by
	当distribute和sort字段相同时可用cluster进行替代，默认正序，单不支持desc倒序
20.分桶 -- 分桶表的数据需要通过子查询的方式导入
	20.1 开启分桶的设置
		set hive.enforce.bucketing=true;
	20.2 分桶表的创建
		create table stu_buck(id int, name string)
		clustered by(id) 
		into 4 buckets
		row format delimited fields terminated by '\t';
	20.3 分桶的规则
		用分桶字段的hash值与桶的个数求余，来决定数据存放在那个桶，
	20.4 分桶与分区区别
		a. 分桶结果在表的目录下存在多个分桶文件
		b. 分区是将数据存放在表所在目录不同文件路径下
		c. 分区针对是数据存储路径，分桶针对的是数据文件，分桶可以在分区的基础粒度细化
21.分桶的抽样	
	21.1 抽样语法 -- 必须 x<=y
		select * from table_name tablesample(bucket x out of y on bucketKey); -- on bucketKey可不写
	21.2 抽样规则
		a. y用来决定抽样比例，必须为bucket数的倍数或者因子，
			例：bucket数为4时，当y=2时，4/2=2,则抽取两个桶的数据，具体抽取哪个桶由x决定
		b. x用来决定抽取哪个桶中的数据
			例1：当bucket=4, y=4, x=2时，则需要抽取的数据量为bucket/y=1个桶，抽取第x桶的数据
			例2：当bucket=4, y=2, x=2时，则需要抽取的数据量为bucket/y=2个桶，抽取第x桶和第x+y桶的数据
			例3：当bucket=12, y=3, x=2时，抽bucket/y=4个桶，抽取第x桶和第x+2y桶的数据
22.NVL函数
	NVL(column_name, default_cvalue),如果该行字段值为null,则返回default_value的值
23.CONCAT_WS()函数
	使用规则：concat_ws(separator, [string | array(string)]+)
	例：select concat_ws('_', 'www', array('achong','com')) 拼接结果：www_achong_com
24.COLLECT_SET(col)函数
	使用规则：仅接受基本数据类型，将字段去重汇总，并返回array类型
	例（行转列）：表结构
			name	xingzuo     	  blood
			孙悟空	  白羊座	        A
			大海	  射手座	        A
			宋宋	  白羊座	        B
			猪八戒	  白羊座	        A
			凤姐	  射手座	        A
		需求：把星座和血型一样的人归类到一起
			射手座,A            大海|凤姐
			白羊座,A            孙悟空|猪八戒
			白羊座,B            宋宋
		查询语句：
			SELECT CONCAT_WS(',', xingzuo, blood), CONCAT_WS('|', COLLECT_SET(NAME))
			FROM xingzuo
			GROUP BY xingzuo, blood
25.EXPLODE(爆炸函数)及LATERAL_VIEW)(侧写函数)
	25.1 explode:将列中的array或者map结构拆分成多行 -- 一般需结合lateral_view使用
	25.2 lateral_view: LATERAL VIEW udtf(expression) 表别名 AS 列别名
	例（行转列）
		select movie, category_name
		from  movie_info 
		lateral view explode(category) table_tmp as category_name;
26.开窗函数 -- 常结合聚合函数使用，解决即需要聚合前的数据又需要聚合后的数据展示
	26.1 语法：UDAF() over (PARTITION By col1，col2 order by col3 窗口子句（rows between .. and ..）) AS 列别名
			（partition by .. order by）可替换为（distribute by .. sort by ..）
	26.2 over(): 指定分析数据窗口大小
	26.3 窗口子句 -- 先分区在排序然后接rows限定执行窗口
		26.3.01 n PRECEDING：往前n行数据
		26.3.02 n FOLLOWING：往后n行数据
			例：select name, orderdate, cost, sum(cost) over(
																partition by name 
																order by orderdate 
																rows between 1 PRECEDING and 1 FOLLOWING
															) from business;
		26.3.03 CURRENT ROW：当前行
		26.3.04 UNBOUNDED PRECEDING 表示从前面的起点
		26.3.05 UNBOUNDED FOLLOWING表示到后面的终点
			例：select name, orderdate, cost, sum(cost) over(
																partition by name 
																order by orderdate 
																rows between CURRENT ROW and UNBOUNDED FOLLOWING
															) from business;
27.LAG(col,n,default_val)：往前第n行数据
28.LEAD(col,n, default_val)：往后第n行数据
	例：select name, orderdate, cost, lag(orderdate, 1, 'null') over(partition by name order by orderdate)
		from business; -- 即获取前1行的orderDate数据
29.ntile(n):把有序分区中的行分为n组，每组编号从1开始 -- 分组规则详见:ntile的分组规则.sql
	例：select name,orderdate,cost, ntile(5) over(order by orderdate) num from business
30.Rank函数
	rank() 出现相同排序时，总数不变
	dense_rank() 出现相同排序时，总数减少
	row_number() 不会出现相同排序




sql执行顺序
	from... where...group by... having.... select ... order by...
	
hql执行顺序	
	from … where … group by … having … select … order by … 或
	from … on … join … where … group by … having … select … distinct … order by … limit
	存在开窗函数时，起码在order by之前执行

	
例题1：-- 集合类型数据导入
	{
		"name": "songsong",
		"friends": ["bingbing" , "lili"] ,       //列表Array, 
		"children": {                      //键值Map,
			"xiao song": 18 ,
			"xiaoxiao song": 19
		}
		"address": {                      //结构Struct,
			"street": "hui long guan" ,
			"city": "beijing" 
		}
	}

	基于上述数据结构，我们在Hive里创建对应的表，并导入数据。
	1.1 格式化数据为：
		songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
		yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing
	1.2 建表语句：
		create table test(name string, 
		friends array<string>,
		children map<string, int>,
		address struct<street:string, city:string>)
		row format delimited
		fields terminated by ','
		collection items terminated by '_'
		map keys terminated by ':';
	1.3 数据写入语句
		load data local inpath '/opt/module/datas/test.txt' into table test;
	1.4 查询语句
		select friends[0] friend,children['xiao song'] age,address.city from test where name = 'songsong';