hql基本语句 - 勇婕同心
hql基本语句
<开始>
本篇博客是对hive sql 一些常用语法的总结。
注意：如果标志（hive之外引入私有变量），是写在hive外的 ，其余均是hql，需要写在hive中。
#A_alter
#增加列
alter table table_name add columns (col_name bigint);
注意：hive中，增加了列后，重跑数据前，数据要删除，语法如下，否则重跑增加的列还是空的。
alter table table_name drop partition(dayno=${v_day})
#修改表名
alter table name rename to new_name
#删除列（针对非分区表）
alter table name drop column_name
#修改列名
alter table name change column_name new_name new_type
alter table name replace columns (col_spec[, col_spec ...])
#B_变量
given_dayno=”\’20190601\'” （hive之外引入私有变量）
……
dayno=${given_dayno} （hive之内使用私有变量）
#C_case
#CASE ... WHEN ... THEN句式：
#与if条件语句相似，用于处理单个列的查询结果
#最后两位数 case when substring(imei,length(imei)-1,2) between \'00\' and \'09\' then \'00-09\' when substring(imei,length(imei)-1,2) between \'10\' and \'19\' then \'10-19\' when substring(imei,length(imei)-1,2) between \'20\' and \'29\' then \'20-29\' when substring(imei,length(imei)-1,2) between \'30\' and \'39\' then \'30-39\' when substring(imei,length(imei)-1,2) between \'40\' and \'49\' then \'40-49\' when substring(imei,length(imei)-1,2) between \'50\' and \'59\' then \'50-59\' when substring(imei,length(imei)-1,2) between \'60\' and \'69\' then \'60-69\' when substring(imei,length(imei)-1,2) between \'70\' and \'79\' then \'70-79\' when substring(imei,length(imei)-1,2) between \'80\' and \'89\' then \'80-89\' when substring(imei,length(imei)-1,2) between \'90\' and \'99\' then \'90-99\' end as imei
#C_cast
#cast类型转换
cast(dayno as string)
注意：BINARY类型只支持将BINARY类型转换为STRING类型；如果用户知道其值是数值的话，可以通过嵌套cast()的方式对其进行分类转换。
#C_create
#建表
#建分区表：
CREATE TABLE tableName ( col1 string, col2 bigint ) partitioned by (dayno string) row format delimited fields terminated by \'\t\' COLLECTION ITEMS TERMINATED BY \',\' MAP KEYS TERMINATED BY \':\' stored as orcfile;
注意1：分区后面的设置是对map类型的字段进行设置，由，：进行间隔。
注意2：如果分区设了两个，插入数据要这样：
insert overwrite table table_name partiton(dayno=\'20190707\',hour=\'12\') 否则，会报
Line 1:89 Partition not found \'20190707\'。
#C_count
#count(0)也算一条记录，计算uv时：
错：count(if(act_code in (\'20090031\',\'20180031\',\'20090080\'),1,0))
对：count(if(act_code in (\'20090031\',\'20180031\',\'20090080\'),1,null))
sum(if(act_code in (\'20090031\',\'20180031\',\'20090080\'),1,0))
#C_concat
#concat_ws 用|串联，案例，每个imei会登录多个host，想放在一行中显示，将host串联起来
concat_ws(\’|\’,collect_set(host)) as host
#C_参数
#动态分区
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.optimize.sort.dynamic.partition=true;
#并行执行(union all 多的情况)
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
#有个任务，数据量不大（几个字段），但是记录数3kw左右，做join有个stage跑了一个多小时都没跑完，优化后，脚本大概20分钟就跑完了。
–时间长原因：map数太少，运行时该stage被只分配了11个map
–解决方法：适当加大map
set mapreduce.input.fileinputformat.split.maxsize=20000000;
set mapreduce.input.fileinputformat.split.minsize.per.node=10000000;
set mapreduce.input.fileinputformat.split.minsize.per.rack=10000000;
#map过大 跑不动number of mappers: 230691; number of reducers: 1099
set mapred.max.split.size=1000000000;
#报code2错误，内存不够，可以优化代码
set mapreduce.map.memory.mb=4096;
set mapreduce.reduce.memory.mb=6144;
#D_笛卡尔积
#现象：笛卡尔积的出现是在多表进行联合查询的时候会出现的一种情况。如下会出现笛卡尔现象。
select studentname, Subjectname from Student ,subject
#后果：数据量大时，会异常慢，甚至跑不出结果。
#避免：在实际应用过程中通过关联表的最小粒度关联,可以避免产生笛卡尔积.这里的最小粒度可以理解为表中的唯一性约束的字段值，说白了，就是一定要有on。
select a.statis_month,a.area_code from tb_test_01 a inner join tb_test_02 b on a.area_code = b.area_code
#交叉连接
#匹配一个表，如含低俗词
cross join
#G_规范
#表名规范
接口层 |
st开头 + 业务表名 |
1：为了区分业务表名中需要包含对应业务标识:浏览器 browser、锁屏杂志 magazine 短视频 short_video 信息流iflow; 2:具体的命名可以参考字段命名规范 |
明细层 |
f_evt +业务表名 |
记录时间变化的数据，按日期分区表 |
f_agt +业务表名 |
记录某一状态的信息 |
|
f_his +业务表名 |
保存历史记录的信息,按日期分区表 |
|
f_dim |
维表命名 |
|
轻度汇总层 |
a_evt +业务表名 |
记录时间变化的数据，按日期分区表 |
a_agt +业务表名 |
保存历史记录的信息,按日期分区表 |
|
a_his +业务表名 |
保存历史记录的信息,按日期分区表 |
|
数据集市层 |
dm_ +业务表名 |
宽表 |
应用报表层 |
rpt_ + 业务表名 |
#I_insert
#INSERT OVERWRITE/INTO（覆盖/追加）
insert overwrite table t partition (dt=${YYYYMMDD})
#J_join
#内连接：只有进行连接的两个表中都存在与连接标准相匹配的数据才会被保留下来；
INNER JOIN
#交叉连接：笛卡尔积
CROSS JOIN
#左外连接：JOIN操作符左边的表符合WHERE子句的所有记录将会被返回，JOIN操作符右边表中如果没有符合ON后面连接条件的记录时，那么从右边表指定选择的列将会是NULL。
LEFT OUTER JOIN
#右外连接：返回右边表所有符合WHERE语句的记录，左表中匹配不上的字段值用NULL代替。
RIGHT OUTER JOIN
#全外连接：返回所有表中符合WHERE语句条件的所有记录；如果任一表的指定字段没有符合条件的值的话，那就使用NULL值代替。
FULL OUTER JOIN
#L_like
#模糊查询”非”
#非 and not (( upper(model) like \'ABC%\'
or upper(model) like \'GD%\' )
or ( upper(model) like \'FJ%\'
or upper(model) = \'KLMN%\'
or upper(model) = \'CAKH%\' ))
#rlike
#一列中包含另一列，如标题，包含低俗词
a.title rlike b.word
#L_留存
#留存 #次留、7留 #缺点：如果是版本迭代，用版本来分组，就会出现识别不到的问题 #优点：速度快 #CREATE TABLE 宝贝 ( # source string, # active_diff0 string, # active_diff1 string, # active_diff7 bigint #) #PARTITIONED BY (dt string) #row format delimited #fields terminated by \'\t\' #COLLECTION ITEMS TERMINATED BY \',\' #MAP KEYS TERMINATED BY \':\';
insert overwrite table 宝贝 partition(dt=${YYYYMMDD}) select source ,sum(active_diff0) as active_diff0 ,sum(active_diff1) as active_diff1 ,sum(active_diff7) as active_diff7 from ( select source ,sum(if(v_today>0,1,0)) as active_diff0 ,sum(if(v_last_1 >0,1,0)) as active_diff1 ,sum(if(v_last_7 >0,1,0)) as active_diff7 from ( select x1.imei ,\'整体\' as source ,sum(if(x2.dayno=\'${v_day}\',1,0)) as v_today ,sum(if(x2.dayno=\'${v_last_day}\',1,0)) as v_last_1 ,sum(if(x2.dayno=\'${v_last_7_day}\',1,0)) as v_last_7 from ( select distinct imei from 支付宝) as x1 inner join ( select imei ,dayno from 天安门 where dayno in (${v_day},${v_last_day},${v_last_7_day}) and system_id=\'20174\') as x2 on x1.imei=x2.imei inner join ( select imei from 天安门 where dayno=${v_day} and system_id=\'20174\') as x3 on x2.imei=x3.imei group by x1.imei) as y group by source ) as t group by source #统计来源、活跃、次留、七留 #CREATE TABLE 宝宝 ( # dayno string, # source string, # active_diff0 bigint, # active_diff1 bigint, # active_diff7 bigint #) #PARTITIONED BY (dt string) #row format delimited #fields terminated by \'\t\' #COLLECTION ITEMS TERMINATED BY \',\' #MAP KEYS TERMINATED BY \':\'; insert overwrite table 宝宝 partition(dt=${YYYYMMDD}) select dayno ,source ,sum(active_diff0) as active_diff0 ,sum(active_diff1) as active_diff1 ,sum(active_diff7) as active_diff7 from ( select dt ,\'${v_day}\' as dayno ,source ,sum(active_diff0) as active_diff0 ,sum(0) as active_diff1 ,sum(0) as active_diff7 from 宝贝 where dt=${v_day} group by dt,source union all select dt ,\'${v_last_day}\' as dayno ,source ,sum(0) as active_diff0 ,sum(active_diff1) as active_diff1 ,sum(0) as active_diff7 from 宝贝 where dt=${v_day} group by dt,source union all select dt ,\'${v_last_7_day}\' as dayno ,source ,sum(0) as active_diff0 ,sum(0) as active_diff1 ,sum(active_diff7) as active_diff7 from 宝贝 where dt=${v_day} group by dt,source ) as t group by dayno ,source
#L_limit
#限定返回行数
limit 5
#M_mapjoin
Hive的MapJoin，在Join 操作在 Map 阶段完成，如果需要的数据在 Map 的过程中可以访问到则不再需要Reduce。
小表关联一个超大表时，容易发生数据倾斜，可以用MapJoin把小表全部加载到内存在map端进行join，避免reducer处理。
可以使用mapjoin把小表放到内存中处理，语法很简单只需要增加 /*+ MAPJOIN(pt) */ ，把需要分发的表放入到内存中
insert overwrite table table_name partition (dayno,hour) select /*+ MAPJOIN(a) */ b.* from small_table as a join ( select * from big_table where dayno=${YYYYMMDD} ) as b on b.uuid=a.uuid
#注：这里用到了动态分区，hour字段是动态分区
#O_over
#P_partition
#开窗函数
#累计计算SELECT cookieid, createtime, pv, SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 SUM(pv) OVER(PARTITION BY cookieid) AS pv3, --分组内所有行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 ---当前行+往后所有行 FROM lxw1234; cookieid createtime pv pv1 pv2 pv3 pv4 pv5 pv6 ----------------------------------------------------------------------------- cookie1 2015-04-10 1 1 1 26 1 6 26 cookie1 2015-04-11 5 6 6 26 6 13 25 cookie1 2015-04-12 7 13 13 26 13 16 20 cookie1 2015-04-13 3 16 16 26 16 18 13 cookie1 2015-04-14 2 18 18 26 17 21 10 cookie1 2015-04-15 4 22 22 26 16 20 8 pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号 pv2: 同pv1 pv3: 分组内(cookie1)所有的pv累加 pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号， 13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号 pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21 pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，14号=14号+15号+16号=2+4+4=10
#R_row_number
#使用row_number（）函数进行编号，增加一列
select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer
#S_substring
#截取
#从1开始，不是0；
#截取 从字符串的第 4 个字符位置开始取，只取 2 个字符:
select substring(\'example.com\', 4, 2);
#取最后两位
select substring(\'abc\',length(\'abc\')-1,2)
#S_show
#查看建表语句
show create table table_name;
#S_索引
#索引失效
#看看有没有索引，如果有索引，看是否失效；
#失效比如：dim int ，查询可以写成dim=1，或者dim=\'1\',但是dim=\'1\'就会索引失效
#S_筛选
#筛选条件
#表2先筛选大于等于40，再关联
select * from t1 left outer join t2 on t1.id=t2.id and t2.cnt>=40
#最后筛选，如果有空，也会过滤掉
select * from t1 left outer join t2 on t1.id=t2.id where t2.cnt>=40
#T_脱敏
MD5：由于现在单靠MD5加密已经不再安全，现选择对MD5+盐(几个由字母或数字组成的字符串)提高安全性
对于string脱敏：
md5(concat(\’837c1f90ddb8f821523f2e2535785cae\’,imei))
对于map脱敏：
第一步：map to string ，字段设为string类型，直接赋值即可；
第二步：string to map，字段设为map类型，str_to_map：str_to_map(字符串参数, 分隔符1, 分隔符2)，
分隔符1将文本分成K-V对，分隔符2分割每个K-V对。对于分隔符1默认分隔符是 \’,\’，对于分隔符2默认分隔符是 \’=\’。
str_to_map(regexp_replace(event_info,\’app_session\\u0003[0-9]+\’,\’app_session\\u0003***\’),\’\\u0002\’,\’\\u0003\’) as event_info
效果如下：
#T_退出
exit ${v_job_stat} （hive之外引入私有变量）
#########################################################################
#更新作业完成标志，若失败则读取错误信息。
#########################################################################
echo "v_job_stat is ${v_job_stat}"
exit ${v_job_stat}
#判断步骤是否为0,不是0退出
if [ ${v_job_stat} -ne 0 ]; then
exit ${v_job_stat}
fi
#U_union
#UNION ALL
#可以将2个或多个表进行合并。每一个union子查询都必需具有相同的列，而且对应的每个字段的字段类型必须是一致的。
UNION ALL 允许值重复；
UNION 不允许重复值出现（hive不支持union）。
<结束>