-
尽量使用别名,提高代码书写效率和查询效率
-
在hive中,limit的作用只能提取表中的前N条数据,无法取出范围数据,即start参数不起作用(mysql中可以)
-
select子句中,不是聚集函数的列,表达数都要写到group by 子句中,否则会引发
”Exception: Expression not in GROUP BY key“
,并且group by 后面不能使用别名。另外当使用group by时,每个子查询后面要加别名 -
WHERE关键字后不可以使用聚合函数;并且where后面不允许使用别名
-
当同时含有where子句、group by 子句 、having子句、聚集函数和order by子句时,执行顺序如下:
① 先使用where筛选复合条件的数据;
② 使用group by 子句对数据进行分组;
③ 对group by 子句形成的组运行聚集函数计算每一组的值;
④ 用having子句去掉不符合条件的组
⑤ 最后在筛选后的组内进行order by
ps:having 子句中的每一个元素也必须出现在select列表中。有些数据库例外,如oracle;having子句限制的是组,而不是行 -
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判断语句进行聚合筛选 -
查询一张表内某些字段是否有重复数据
select A,B from table1 group by A,B having count(*) > 1
-
将查询后的数据导入到hdfs集群上
hive -f xxx.sql > res.csv
-
作业提交到的队列:
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文件里 -
执行脚本文件Linux脚本文件时,脚本文件开头都会有
#!/bin/sh bash
,其中#!
为特殊字符,后面指定了运行该脚本的shell类型,是bash还是ksh -
hive中的传参使用
① 开发hql脚本,使用类似于:${hiveconf:month}
作为变量的值,等待传递
② 调用hive并传递参数,类似于:
hive --hiveconf month=201912 --hivevar s_score=80 --hivevar c_id=03 -f /kkb/install/hivedatas/hivevariable.hql
-
在bash中,
$( )
与反引号都是用来作命令替换的,执行括号或者反引号中的命令
示例:命令:$ echo today is $(date +%Y-%m-%d)
,显示:today is 2014-07-01 -
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/
- 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}'
-
列转行与行转列
-
hive中union使用注意事项
Hive在1.2.0之前的版本只支持union all,在1.2.0之后的版本才支持union。
Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
Union All:对两个结果集进行并集操作,包括重复行,不进行排序
ps:可以使用hive --version查看自己的hive版本
-
hive中union all使用注意事项
(1)每个select语句返回的列的数量和名字必须一样,同时字段类型必须完全匹配,否则会抛出语法错误
(2)
附送语法知识:
-
关于类型转换
最近写代码发现一个问题,avg可以处理字符数字,于是就想avg函数会自动的将字符数字转换为数字,所以查了一下,果不其然
19.group by后不能使用列的别名
hive中别名的使用都是严格遵循sql执行顺序的:
-
is NULL 和= NULL的区别
前者表示没有对象(函数返回值),后者表示值为NULL(自己的数据默认设置) -
rownumber() over()函数学习
-
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
-
over()关键字窗口函数学习,可以解决占比问题
占比问题 -
distinct 多列解析
-
hive不支持多表查询(sql支持)
--以下操作不可取
select
t1.a,
t2.b
from
table1 t1,
table2 t2
- Hive sql关于json数据的读取
用 Python 语言来编码和解码 JSON 对象 - count、case when写法
特别注意:end
一定要写