Hive SQL 日常工作使用总结
like
- like用于指定特定的字符串，或结合正则做模糊匹配
select uid from dw.today where tunittype like '%wew.%'- rlike是hive中对like的扩展，将原先多个like才能完成的任务，使用一个rlike就可以搞定。
select uid from dw.today where tunittype rlike '.*(you|me).*'点号(.)：表示和任意字符串匹配，星号(*)：表示重复“左边的字符串”，（x|y）表示和x或者y匹配
- 对like取反 一般，like的语法形式是： A LIKE B，取反的语法形式：NOT A LIKE B
select uid from dw.today where not tunittype like '%wew.%'时间区间
工作中，需要查询某个区间的用户量，这个时候就需要对时间做处理，以便快速搞定
- 30分钟作为一个区间段
SELECT DISTINCT FROM_UNIXTIME(60*30*CAST(UNIX_TIMESTAMP("2017-11-11 13:23:23")/(60*30) AS BIGINT), 'yyyy-MM-dd HH:mm:ss')
FROM test_table这里便将时间转化为13：00：00，记录的是13：00：00至13：30：00这段时间的数据量
- 10分钟作为一个区间段
SELECT DISTINCT FROM_UNIXTIME(60*10*CAST(UNIX_TIMESTAMP("2017-11-11 13:23:23")/(60*10) AS BIGINT), 'yyyy-MM-dd HH:mm:ss')
FROM test_table总结：一个小时60分钟，一分钟60秒，按照时间单位的秒来转化为相应的区间
排序
- oder by
- 得到rank
语法形式：
row_number() over (partition by 字段 a order by 计算项 b desc ) rankrank 排序的名称；partition by：类似 hive 的建表，分区的意思；order by ：排序，默认是升序，加 desc 降序；这里按字段 a 分区，对计算项 b 进行降序排序
例子： https://blog.csdn.net/jobschen/article/details/70821064
当前时间
SELECT from_unixtime(unix_timestamp())类型转换
cast() 函数将字符串转换为整数、双精度浮点数或执行反向转换
可参考这个博客：https://blog.csdn.net/xiaoshunzi111/article/details/54343291/
select cast(a as double) from tablecase .. when .. then句式
没错，在机器学习中给数据打标签过程最常用到的sql语句，主要用于处理单个列的查询结果
create table if not exists dw.huodong_uid_label as
select uid,
CASE
WHEN action=0 THEN 0
ELSE 1
END AS label from zhangxiang.huodong_action_0_2多列合并
select sum（A+B+C） as 总和 from table group by uid这里要求A，B，C三列都是数值型
条件聚合
select uid,
sum(if(hour in (6,7,8,9,10), cast(num_rate_tgi as DOUBLE) ,0) )
from
table
where pt_dt = '2018-07-18' and group by uid计算6点到11点前的累计tgi和
采样
尝试分桶采样，显示不支持分区表。
方案一：
select * from data.next where pt_dt='2018-06-04' and label = 0 order by rand() limit 88000网上查询说此方案效率低，其原因是每次要扫全表一次。
方案二：
select *， row_number() over(order by rand()) as rn from data.next where pt_dt='2018-06-04' and label = 0 - 应用 AB测试分组，使用方案二，让rn处于某个范围就得到相应的样本量，这样就完成了随机分组。
计算百分数
有两个函数：
- percentile(col, p) 要求输入的字段必须是int类型的，用法如下：
percentile(col,array(0.01,0.05,0.1))- percentile_approx(col, p,B) 这里的参数B控制内存消耗的近似精度，B越大，结果的准确度越高。默认为10,000。当col字段中的distinct值的个数小于B时，结果为准确的百分位数。
percentile_approx(col,array(0.05,0.5,0.95),9999)注意：这里要求p∈(0,1)p∈(0,1)p \in (0,1)
正则化匹配
- regexp 语法结构
A REGEXP B 等同于RLIKE
select count(*) from olap_b_dw_hotelorder_f where create_date_wid not regexp '\\d{8}'等同于
select count(*) from olap_b_dw_hotelorder_f where create_date_wid not rlike '\\d{8}'- regexp_extract
语法结构：
regexp_extract(string subject, string pattern, int index) 例子：从[189][0]10001614-30以上-3中取出10001614-30来
- 三种方案 方案一
select regexp_extract('[189][0]10001614-30以上-3','\\[0](.*?)(-)',1);方案二
select regexp_extract('[189][0]10001614-30以上-3','\\[[0-9]+]\\[[0-9]+]([0-9]*)-',1);方案三
select regexp_extract('[189][0]10001614-30以上-3','(\\[.*\\])([0-9]{0,})(.*)',2);DataFrame插入hive表
- spark中运行
工作中，经常将sql和hive结合，然后对数据分析，有时也需要对分析的结果插入hive中，以便稳定的保存。
import hiveContext.implicits._
data.toDF().registerTempTable("table1")
hiveContext.sql("insert into table2 partition(date='2018-07-24') select name,col1,col2 from table1")- hive中写sql
先将数据保存为文件，如csv格式。此方案对数据量太大的情况不合适，在将数据保持为csv等格式的时候容易导致服务崩溃。
hive -e "insert overwrite directory '/user/local/data_export.csv' row format delimited fields terminated by '\t' select * from locl.data limit 20;"批任务
可以在xshell中的hive端执行，或者在shell中跑
- hive 端执行 格式：将日志输出到log.txt中
hive -e "sql代码" >> log.txt格式：在hive端执行sql文件
hive -f data.hql >> log.txt- shell
#!/bin/bash
source /exportfs/home/test/.bash_profile
echo "
sql代码 ;
">data.hql
hive -f data.hql 2>log.txt
# 这里可以放定时的代码数据类型
https://blog.csdn.net/skywalker_only/article/details/27547515
时间函数
返回周几等 https://blog.csdn.net/bitcarmanlee/article/details/51670879
http://hadooptutorial.info/hive-date-functions/
未完待续。。。。。。