Hive sql编程经验累积

1. 尽量使用别名，提高代码书写效率和查询效率

2. 在hive中，limit的作用只能提取表中的前N条数据，无法取出范围数据，即start参数不起作用(mysql中可以)

3. select子句中，不是聚集函数的列，表达数都要写到group by 子句中，否则会引发”Exception: Expression not in GROUP BY key“，并且group by 后面不能使用别名。另外当使用group by时，每个子查询后面要加别名

4. WHERE关键字后不可以使用聚合函数;并且where后面不允许使用别名

5. 当同时含有where子句、group by 子句 、having子句、聚集函数和order by子句时，执行顺序如下：
   ① 先使用where筛选复合条件的数据；
   ② 使用group by 子句对数据进行分组；
   ③ 对group by 子句形成的组运行聚集函数计算每一组的值；
   ④ 用having子句去掉不符合条件的组
   ⑤ 最后在筛选后的组内进行order by
   ps：having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle；having子句限制的是组，而不是行

6. count的用法
   ① count(1)和count(*)没什么区别
   ② count(1)和count(字段)：count(1)统计所有记录包括null，count(字段)不统计null
   ③ count(distinct column)：对column中非NULL进行去重统计
   ④ count(1)和limit不能同时使用：SELECT count(1) FROM w_task limit 1,500，因为count(1)只返回一条数据
   ⑤ count(distinct if.....),count函数中可以使用if判断语句进行聚合筛选

7. 查询一张表内某些字段是否有重复数据
   select A,B from table1 group by A,B having count(*) > 1

8. 将查询后的数据导入到hdfs集群上
   hive -f xxx.sql > res.csv

9. 作业提交到的队列：mapreduce.job.queuename=root.xxxxxx
   作业优先级：mapreduce.job.priority
   显示字段名但是带表名:set hive.cli.print.header=true;
   显示字段名不带表名：set hive.resultset.use.unique.column.names=false;
   set配置属性只是当次有效，如果想永久配置的话，将上述命令配置到hive/conf下的配置文件中，或者配置到hiverc文件里

10. 执行脚本文件Linux脚本文件时，脚本文件开头都会有#!/bin/sh bash,其中#!为特殊字符，后面指定了运行该脚本的shell类型，是bash还是ksh

11. hive中的传参使用
    ① 开发hql脚本，使用类似于：${hiveconf:month}作为变量的值，等待传递
    ② 调用hive并传递参数，类似于：
    hive --hiveconf month=201912 --hivevar s_score=80 --hivevar c_id=03 -f /kkb/install/hivedatas/hivevariable.hql

12. 在bash中，$( )与反引号都是用来作命令替换的，执行括号或者反引号中的命令
    示例：命令：$ echo today is $(date +%Y-%m-%d)，显示：today is 2014-07-01

13. hive中的自定义函数支持Transform和UDF。UDF是将java代码打包上传，如果你不想写java代码也可以，那就用到了Transform，写一个脚本，通过脚本来处理:

SELECT TRANSFROM(
--需要处理的变量
)
USING   XXXX.py
AS(
--输出
)
from
(
--表
)
--要注意的是，这里的TRANSFORM的内容可以写*，但是AS()里就不能写*，会报错
--python中的输出结果是由AS中的变量数量来决定的

--学习链接：
--http://heloowird.com/2018/01/29/hive_python_udf/
--http://blog-jfttt.herokuapp.com/blog/2014/01/05/hive-stream-with-python/

14. row_number()用法
    先按某字段分组，然后可以选择在组内按某字段排序，row_num()的作用是为每个组编号，如果要去重，就取num=1

select *, row_number() over (partition by request_trace_id) num
from wx_strategy.mds_adv_basicdata_strategy_logs_inc_ymd
where dt = '${hiveconf:DATE}'

15. 列转行与行转列

16. hive中union使用注意事项

Hive在1.2.0之前的版本只支持union all，在1.2.0之后的版本才支持union。
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序
ps:可以使用hive --version查看自己的hive版本

17. hive中union all使用注意事项
    （1）每个select语句返回的列的数量和名字必须一样，同时字段类型必须完全匹配，否则会抛出语法错误
    （2）
    附送语法知识：

18. 关于类型转换
    最近写代码发现一个问题，avg可以处理字符数字，于是就想avg函数会自动的将字符数字转换为数字，所以查了一下，果不其然

19.group by后不能使用列的别名
hive中别名的使用都是严格遵循sql执行顺序的：

20. is NULL 和= NULL的区别
    前者表示没有对象(函数返回值)，后者表示值为NULL(自己的数据默认设置)

21. rownumber() over()函数学习

22. explode和lateral view组合的用法：将一行变成多行

-- 利用split函数将原表变成一个临时表table_tmp,并把原来的列名(准备被分裂的列)重命名为xxx
-- 当然也可以将多个字段进行该操作，此时被分裂的字段互相之间做笛卡尔积
			select
				字段1,
				字段2,
				字段3,
				...
			from
				(
				select* from
				表名
				where dt=20210321
			    ) a
			LATERAL VIEW explode(split(a.recall_ids,',')) table_tmp as xxx

23. over()关键字窗口函数学习，可以解决占比问题
    占比问题

24. distinct 多列解析
    

25. hive不支持多表查询(sql支持)

--以下操作不可取
select
	t1.a,
	t2.b
from
	table1 t1,
	table2 t2

26. Hive sql关于json数据的读取
    
    用 Python 语言来编码和解码 JSON 对象
27. count、case when写法
    特别注意：end一定要写