Hive SQL 常用命令和知识点_hive sql drop table if exist-优快云博客

存储优势
- 在存储方面，ORC+Snappy 相比 Text 压缩率达82.3%，相比 Text+LZO 压缩比可提升20% 左右。同样的数据，选用ORC+Snappy打包后占用的地方最小。
计算优势
- 执行相同的查询操作，使用的map和reduce 的个数，以及map/reduce hdfs的读取量都更少

提示：以下是本篇文章正文内容，下面案例可供参考

一、SELECT 语句的处理顺序

from
on
join
where
group by
having
select
distinct
order by

二、建表

建立orc表

如果是分区表，那么每个分区以文件夹的形式单独存在于表文件夹的目录下

外部表external table，删除表的时候，只删除元数据，而不删除hdfs上的数据文件

内部表，删除的时候，会同时删除元数据和hdfs上的数据文件

如果是在spark session 中建表，那么不能使用row format delimited

以下为在hive中的建表语句

CREATE
	EXTERNAL TABLE IF NOT EXISTS adm.adm_graph_pj_djd_pair_valid
	(
		category_id BIGINT comment '日期',
		province_id BIGINT,
		product_id BIGINT,
		price DOUBLE,
		sell_num BIGINT
	)
	comment ‘3c - 财务历史数据’ PARTITIONED BY
	(
		dt string
	)
	ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' NULL DEFINED AS "" STORED AS ORC tblproperties
	(
		'orc.compress' = 'SNAPPY'
	) ;

--如果表中有特殊形式，比如map:{"money":10,"age":30} 和list:[1,2,3]，那么需要自定义分隔符
create
	table tmp.tmp_zwm_explode_test
	(
		pin string,
		array_test array < int >,
		map_test map < string,
		int >
	)
	ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' collection items terminated by ',' map keys terminated by ':' LINES TERMINATED BY '\n' NULL DEFINED AS "";

通过select 建立orc表（不能建成分区表）

CREATE
	TABLE tmp.tmp_zwm_user_by_phone_90_persona stored AS orc tblproperties
	(
		'orc.compress' = 'SNAPPY'
	) AS
select user_log_acct, level from table

复制表结构(复制大法好啊！！！！！)

以下语句也支持分区表

CREATE TABLE dev_3c_cw_2016  like adm.adm_graph_pj_djd_pair_valid

通过text文本建表（可以自造数据，但是只能建成text表，多为测试使用）

准备text文件，列和列之间用"\t"进行分隔，行和行之间用”\n"进行分隔。
准备元数据注意不要建成orc表

create
table tmp.tmp_test_huiliu_parameter_table
(
`table` string COMMENT '底表名',
`pin` string COMMENT '用户pin',
)
COMMENT '回流自动化融表参数表' PARTITIONED BY
(
dt string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' NULL DEFINED AS "";

接着，show create table tmp.tmp_test_huiliu_parameter_table 得到其hdfs路径，因为是分区表，所以需要 hdfs dfs -mkdir hdfs路径/dt=2020-12-09
接着把之前准备好的含有分区字段的txt文件放到hdfs路径下。 hdfs dfs -put xx.file hdfs路径/dt=2020-12-09/
然后msck repair table tmp.tmp_test_huiliu_parameter_table;
再select * from tmp.tmp_test_huiliu_parameter_table; 看是否有数据

三、删除表和分区

删除表

drop table if exist XXXX （如果是external table, 需要同时手动删除hdfs文件）

删除分区

ALTER TABLE app.app_qk_feature_join_sku DROP PARTITION (dt = '2019-10-23');
alter table dm.dm_call_gateway_bill_time_detail drop partition(dt >="20191101",dt <='20191130')
ALTER TABLE test_table DROP PARTITION (dt='2016-08-08', hour='10');
--如果是external table，需要同时手动删除hdfs文件
--首先 show create table XXXX, 找到表文件所在的路径：hdfs://ns4/user/dd_edw/tmp.db/tmp_phone_purchapower_predict_pin
--之后执行 hdfs dfs -rm hdfs://ns4/user/dd_edw/tmp.db/tmp_phone_purchapower_predict_pin就可以删除相应的数据文件

删除某行数据

hive 表不支持针对某条数据的删除： DELETE FROM dim.dimension_cate_potential_have_set WHERE item_second_cate_cd = '16755' 行不通

解决方案：

创建临时表（不包含要删除的数据） create table xxx as select * from dim.dimension_cate_potential_have_set WHERE item_second_cate_cd <> '16755';
然后再将临时表中的内容insert overwrite到临时表中

四、修改

重命名表名	ALTER TABLE table_name RENAME TO new_table_name;
改变列名/类型/位置/注释	ALTER TABLE test_table CHANGE col_old_name col_new_name column_type COMMENT 'xxxx'
增加/更新列	ALTER TABLE table_name ADD\|REPLACE COLUMNS (col_name data_type [CONMMENT col_comment] 在某列之后增加列 ALTER TABLE dev_3c_cw_2016 ADD COLUMNS (new_col string) AFTER COLUMN op_time
增加删除分区	ALTER TABLE dev_3c_cw_2016 ADD PARTITION (dt=‘2016-06-20’) ALTER TABLE dev_3c_cw_2016 DROP PARTITION (dt=‘2016-06-20’) ALTER TABLE test_table DROP PARTITION (dt='2016-08-08', hour='10'); ALTER TABLE app.app_jypt_qk_output DROP PARTITION (dt <= '2020-06-01');

五、常用函数

函数名	例子
非null查找函数 coalesce	Coalesce(a,b,c………)如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null `select --如果dim_subd_num = = null，返回‘未知’ coalesce(dim_subd_num, '未知'), --如果dim_subd_num = = null，返回0 coalesce(dim_subd_num, 0), --如果dim_subd_num = = null，返回dim_subd_name coalesce(dim_subd_num, dim_subd_name), coalesce(dim_subd_num, dim_subd_name, dim_province_name) from dim.dim_province t`
条件判断函数 case when …. then…. when…. then…. else…. end	`select CASE WHEN amount_avg <= 4000.0 THEN 1 WHEN amount_avg > 4000.0 and amount_avg <= 8000.0 THEN 2 WHEN amount_avg > 8000.0 THEN 3 ELSE 4 END AS level, count( *) from table tmp.tmp_table -------------------------------------------------------------- select sum( case when a.state in('4', '5', '6', '7') then 1 else 0 end) recog1, sum( case when a.state in('4', '5', '6', '7') then 1 else 0 end) recog2 from tmp.tmp_table`
条件函数 if	if(判断条件,true返回值, 其他返回值) `select if(dim_subd_num is null, '空', '非空') from dim.dim_province t`
排序 order by 列名 asc / desc	1、按列 c2 的升序排列 `select * from tablename order by c2 asc;` 2、按列 c2 的降序排列 `select * from tablename order by c2 desc;` 3、复合排序，先按列 c1 升序排列，再按 c2 降序排列 `select * from tablename order by c1 asc, c2 desc;` 注意点：排序的时候，如果是对string类型进行排序，那么'10'会排在’2‘前面。因为’10‘是以1开头的。要想对string类型实现正常排序，需要把stu_idz转为int `Select * from software_version ORDER BY (stu_id+0) desc --转换为整形int 规则排序`
分组后过滤 HAVING	使用having的原因是count,avg,sum等函数不能和where连用 `select dim_subd_name, count(1) cnt from dim.dim_province group by dim_subd_name having count(1) > 1`

函数名

例子

非null查找函数 coalesce

Coalesce(a,b,c………)如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null

select
	--如果dim_subd_num = = null，返回‘未知’
	coalesce(dim_subd_num, '未知'),
	--如果dim_subd_num = = null，返回0
	coalesce(dim_subd_num, 0),
	--如果dim_subd_num = = null，返回dim_subd_name
	coalesce(dim_subd_num, dim_subd_name),
	coalesce(dim_subd_num, dim_subd_name, dim_province_name)
from
	dim.dim_province t

条件判断函数

case when ….

then….

when….

then….

else….

end

select
	CASE
		WHEN amount_avg <= 4000.0
		THEN 1
		WHEN amount_avg > 4000.0
			and amount_avg <= 8000.0
		THEN 2
		WHEN amount_avg > 8000.0
		THEN 3
		ELSE 4
	END AS level,
	count( *)
from
	table tmp.tmp_table
--------------------------------------------------------------
select
	sum(
		case
			when a.state in('4', '5', '6', '7')
			then 1
			else 0
		end) recog1,
	sum(
		case
			when a.state in('4', '5', '6', '7')
			then 1
			else 0
		end) recog2
from
	tmp.tmp_table

条件函数 if

if(判断条件,true返回值, 其他返回值)

select if(dim_subd_num is null, '空', '非空') from dim.dim_province t

排序

order by 列名 asc / desc

1、按列 c2 的升序排列

select * from tablename order by c2 asc;

2、按列 c2 的降序排列

select * from tablename order by c2 desc;

3、复合排序，先按列 c1 升序排列，再按 c2 降序排列

select * from tablename order by c1 asc, c2 desc;

注意点：

排序的时候，如果是对string类型进行排序，那么'10'会排在’2‘前面。因为’10‘是以1开头的。要想对string类型实现正常排序，需要把stu_idz转为int

Select * from software_version ORDER BY (stu_id+0) desc   --转换为整形int  规则排序

分组后过滤

HAVING

使用having的原因是count,avg,sum等函数不能和where连用

select
	dim_subd_name,
	count(1) cnt
from
	dim.dim_province
group by
	dim_subd_name
having
	count(1) > 1

字符串函数

字符串长度函数	length
字符串连接函数	concat(string A, string B…)
字符串截取函数	substr(string A,int a,int b)
带分隔符字符串连接函数	concat_ws(string SEP, string A, string B…) select concat_ws(',',dim_subd_name,dim_subd_name,dim_province_name) from dim.dim_province ;
字符串转大写函数	upper,ucase
字符串转小写函数	lower,lcase
去空格函数	trim
正则表达式替换函数	regexp_replace
select regexp_replace(‘foobar’, ‘oo\|ar’, ”) from dual; 结果	fb
分割字符串函数: split	select split(‘abtcdtef’,‘t’) from dual; 结果["ab","cd","ef"] select split(‘abtcdtef’,‘t’)[0] from dual; 结果 ab
select split(‘abtcdtef’,‘t’)[0] from dual; 结果	ab
类型转换函数: cast	select cast(‘1’ as bigint) from dual;

时间函数

日期时间转日期函数: to_date	select to_date('2011-12-08 10:03:01′) from dual; --返回2011-12-08
日期转年函数: year	select year('2011-12-08 10:03:01') --返回2011
日期比较函数: datediff	select datediff('2012-12-08', '2012-05-09') --’-1‘ 是用第一项减去第二项
日期增加函数: date_add	select date_add('2012-12-08',10) -- 2020-12-18
日期减少函数: date_sub	select date_sub('2012-12-08',10) -- 2020-11-28

row_number 函数使用方法

ROW_NUMBER() 功能	举例
实现自增列	`SELECT *, ROW_NUMBER() OVER() AS Row_Number FROM Employee`
将数据分组，并且组内编号	相同的EmpName 为一个partition，在每个相同的EmpName中根据EmpID排序 `SELECT *, ROW_NUMBER() OVER(Partition by EmpName ORDER BY EmpName) AS Row_Number FROM Employee`
将数据打乱shuffle	这里的row_number() over(order by rand())实现的是把表中的行打乱，然后编号，然后以每行的编号对8的余数作为group_id，可以实现对数据shuffle然后分组 `select a., (row_number() over(order by rand())) %8 group_id from ( select FROM tmp.tmp_dw_potential_cate ) a`

ROW_NUMBER() 功能

举例

实现自增列

SELECT *, ROW_NUMBER() OVER() AS Row_Number FROM Employee

将数据分组，并且组内编号

相同的EmpName 为一个partition，在每个相同的EmpName中根据EmpID排序

SELECT
	*,
	ROW_NUMBER() OVER(Partition by EmpName ORDER BY EmpName) AS Row_Number
FROM
	Employee

将数据打乱shuffle

这里的row_number() over(order by rand())实现的是把表中的行打乱，然后编号，然后以每行的编号对8的余数作为group_id，

可以实现对数据shuffle然后分组

select
	a.*,
	(row_number() over(order by rand())) %8 group_id
from
	(
		select * FROM tmp.tmp_dw_potential_cate
	)
	a

窗口函数 over

name	orderdate	amount
jack	2019-01-02	8000
tony	2019-08-08	6000

sum()

min()

max()

avg()

直接使用over()进行分区计算

求出每个用户当前记录的前三次贷款的金额总和

求出每个用户历史所有贷款累加到下一次贷款的金额总和

SELECT
	*,
	/*当前记录的前三次贷款的金额之和*/
	SUM(amount) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv1,
	/*历史所有贷款 累加到下一次贷款 的金额之和*/
	SUM(amount) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS pv2
FROM
	loan ;

n PRECEDING : 前n行

n FOLLOWING：后n行

CURRENT ROW ：当前行

NTILE()

ROW_NUMBER()

RANK()

DENSE_RANK()，可以为数据集新增加序列号

SELECT
	*,
	--将数据按name切分成10区，并返回属于第几个分区
	NTILE(10) OVER(PARTITION BY name ORDER BY orderdate) AS f1,
	--将数据按照name分区，并按照orderdate排序，返回连续的排序序号: 1，2，3，4，5
	ROW_NUMBER() OVER(PARTITION BY name ORDER BY orderdate) AS f2,
	--返回排序序号，数值相同的一项会标记为相同的序号，而下一个序号会跳过: 1，2，2，4，5
	RANK() OVER(PARTITION BY name ORDER BY orderdate) AS f3,
	--返回排序序号: 1，2，2，3，4
	DENSE_RANK() OVER(PARTITION BY name ORDER BY orderdate) AS f4

LAG(),

LEAD(), FIRST_VALUE(),

LAST_VALUE()

函数返回一系列指定的点

SELECT *,
--取上一笔贷款的日期,缺失默认填NULL
LAG(orderdate, 1) OVER(PARTITION BY name ORDER BY orderdate) AS last_dt,

--取下一笔贷款的日期,缺失指定填'1970-1-1'
LEAD(orderdate, 1,'1970-1-1') OVER(PARTITION BY name ORDER BY orderdate) AS next_dt,

--取最早一笔贷款的日期
FIRST_VALUE(orderdate) OVER(PARTITION BY name ORDER BY orderdate) AS first_dt,

--取新一笔贷款的日期
LAST_VALUE(orderdate) OVER(PARTITION BY name ORDER BY orderdate) AS latest_dt

FROM loan;

over partition by与group by 的区别

-- 原表
-- NAME     DEPT    SALARY
-- A         10     1000
-- B         10     2000
-- C         20     1500
-- D         20     3000
-- E         10     1000   

--用over partition by 我就可以查询到每位员工本来的具体信息和它所在部门的总工资：

select name,dept,salary,sum(salary) over (partition by dept)
 total_salary from salary;  

-- name     dept      salary      tatal_salary
-- A        10        1000        4000
-- B        10        2000        4000
-- E        10        1000        4000
-- C        20        1500        4500
-- D        20        3000        4500

-- 用goup by 就没办法做到这点，只能查询到每个部门的总工资：

select dept,sum(salary) total_salary from salary group by dept

-- dept      total_salary
-- 10        4000
-- 20        4500

六、动态分区

set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;
create table zwm_test
	( a string, b string
	) partition by
	(dt string
	) ;
insert overwrite table zwm_test partition
	(dt
	)
select a, b, dt from table_test 
where dt is not null and dt <> ''

同时，在向动态分区中插入数据的时候，要保证动态分区不为空。否则会产生异常分区'__HIVE_DEFAULT_PARTITION__'

七、行转列和列转行

explode 行转列

pin	install_apk_soft
wry	{"淘宝":0, "拼多多":1}

转成

pin	key	value
wry	淘宝	0
wry	拼多多	1

select
    src.pin,
    mytable.key,
    mytable.value
from
    (
        select
            pin,
            install_apk_soft
        from
            app.app_yhzz_hulk_user_tags
        where
            dt = sysdate( - 2) limit 10
    )
    src lateral view explode(install_apk_soft) mytable as key,
    value

列转行 collect_set(col)/collect_list(col)

collect_set / collect_list(col)函数只接受基本数据类型，它的主要作用是将某字段的值汇总，产生Array类型字段，注意该函数只能接受一列参数！我们一般都会配合group by 函数，直接汇总分组数据！

select month, day, collect_set(cookieid) from test2 group by month, day;
--------------------------------------------------------------------
select
	month,
	day,
	collect_set(cookieid) [0] c0
from
	test2
group by
	month,
	day;
--------------------------------------------------------------------
select
	month,
	day,
	collect_list(concat(cookieid, '|', day)) cl
from
	test2
group by
	month,
	day;

八、日常使用技巧

array[string] 中包含 string 的情况

pin: string	cate_id: string	cate_has_order: array[string]
'zwm'	'655'	['655', '658', '753']

想要筛选出cate_has_order 中不包含 cate_id 的用户

select
	pin
from
	table1
where
	not
	(
		array_contains(cate_has_order, cate_id)
	) ;

string 中包含某个特定的 string

pin: string	brand_code: string	brand_ids: string
'zwm'	'655'	'655, 675, 897'

想要筛选出brand_ids 为'all' 或者brand_ids 中包含brand_code 的用户

select
	pin
from
	table1
where
	(
		--如果业务未明确具体品牌id，就取该三级品类下所有品牌下的商品
		brand_ids = 'all'
		or
		(
			instr(CONCAT_WS(brand_ids, ',', ','), CONCAT_WS(brand_code, ',', ',')) > 0
		)
	) ;

msck repair

MSCK REPAIR TABLE命令主要是用来解决通过hdfs dfs -put或者hdfs api写入hive分区表的数据在hive中无法被查询到的问题。

我们知道hive有个服务叫metastore，这个服务主要是存储一些元数据信息，比如数据库名，表名或者表的分区等等信息。如果不是通过hive的insert等插入语句，很多分区信息在metastore中是没有的，如果插入分区数据量很多的话，你用 ALTER TABLE table_name ADD PARTITION 一个个分区添加十分麻烦。这时候MSCK REPAIR TABLE就派上用场了。只需要运行MSCK REPAIR TABLE命令，hive就会去检测这个表在hdfs上的文件，把没有写入metastore的分区信息写入metastore。