hive个人使用 持续更新

• 1、连续n天

例如连续12登陆，先日期进行从小到大进行排序，再rank ,然后日期减去rank的序号，有多少个相同的连续值就是连续多少天

• 2、数据只有本月和本月数添加第三列是之前12个月的数总和

(sum(ct2.CREATE_PROJECT_CURRENT_MONTH_CNT) over(ORDER BY ct2.CURRENT_MONTH_ID ASC ROWS BETWEEN 12 preceding AND 1 preceding)
如果原始数据有缺失月份可以先进行缺失月份的补齐默认值补0

• 3、数据只有本月和本月数添加第三列是上个月数第四列上年同月数

使用left join 配合case when 使用 灵活填充

• 4、数据只有本月和本月数添加第三列是本年截止当前数总和

 通过年 来进行分组 

• 5 、列转行 行转列

 concat(固定字符串)
 concat_ws(集合)
 collect_set(列)
 collect_list(列)
 lateral view explode(集合)
 lateral view explode(split(order_value,','))

• 6、数据类型转换

cast（xxx as xxx）

• 7、case when 灵活方式使用

• 8、脱敏

这是使用sql 的方式进行脱敏
regexp_replace(selphone,substr(selphone,4,4),'****')
还可以把脱敏封装为函数，手机号，身份证号，姓名，银行卡号等，需要脱敏时直接调用函数。

-9、自定义函数

## 临时函数使用

进入hive的交互shell中

1. 上传自定义udf的jar
   add jar /path/to/lower.jar
2. 创建临时函数
   create temporary function xxoo_lower as 'test.ql.LowerUDF';
3. 验证
   select xxoo_lower("Hello World!");



## 永久函数使用

1. 把自定义函数的jar上传到hdfs中.
   hdfs dfs -put lower.jar 'hdfs:///path/to/hive_func';
2. 创建永久函数
   create function TimeDiff as 'yang.udf.TimeDiff' using jar 'hdfs:///hive-function/hive-function-1.0-SNAPSHOT.jar';
3. 验证
   select TimeDiff('2019-09-09','2019-09-10','yyyy-MM-dd');
   show functions;

• 10、sql执行顺序

from 
on 
join 
where 
group by 
having 
select 
distinct 
over
order by 
limit

• 根据模型合成宽表

1、采用union all 的方式 ，然后在group by 把数据整合到同一行
 with 
 temp1 as (),
 temp2 as(),
 ···
 select from  temp1 union temp2 ···
 2、直接使用join 进行宽表的合成
 	这种合成的方式效率低下，建议使用第一种方式。

• 12 、数据迁入迁是，使用sqoop

迁入：注意mysql中的 \t \r \n, 去除掉
迁出：
mysql中，中文输入法输入的数据，字段可能包含hive默认的分隔符\u0000-\u0013
迁入之后再导出会出现分隔符的问题，先过滤数据。
regexp_replace(m.changed_way,'([\\u0000-\\u0013]+)','')

-13 hive sql中使用正则表达式

set hive.support.quoted.identifiers=None; 
select a.pin, `(pin)?+.+` from Table

-14 优化排序

order by全局排序，一个reduce实现，不能并行故效率偏低
sort by部分有序，配合distribute by使用
cluster by col1 == distribute by col1 sort by col1，但不能指定排序规则

• 15 join 优化

多表join的key值统一则可以归为一个reduce；
先过滤后join；
小表在前读入内存，大表在后；

-16 十三位时间戳格式转化

from_unixtime(cast(substr(time_stamp,1,10) as bigint), 'yyyy-MM-dd')

• 17 map数据类型与json互转

例1：
select map(clientname,clientid) from dws.dws_awa_t_cal_recomperiods_result limit 10
 
返回：
 
{"陕西天诚软件有限公司":"51025"}
{"天俱时工程科技集团有限公司":"79261"}
{"杭州览众数据科技有限公司":"48848"}
{"上海融道文化传播有限公司":"157776"}
{"奈良（上海）商务咨询有限公司":"129871"}
{"深圳市自然醒智慧家居有限公司":"82233"}
{"成都艾乐橙文化传播有限公司":"110551"}
{"精诚瑞宝计算机系统有限公司":"23430"}
{"广西中科曙光云计算有限公司":"141473"}
{"临沂市拓普网络股份有限公司":"150854"}
 
例2：
select map(clientname,clientid,jobname,first_interview) from dws.dws_awa_t_cal_recomperiods_result limit 10
返回:
{"陕西天诚软件有限公司":"51025","AI开发工程师":null}
{"天俱时工程科技集团有限公司":"79261","销售":"2019-03-15 09:00:00.0"}
{"杭州览众数据科技有限公司":"48848","大客户软件销售经理":"2019-02-26 10:30:00.0"}
{"上海融道文化传播有限公司":"157776","自媒体运营":null}
{"奈良（上海）商务咨询有限公司":"129871","用户运营":null}
{"深圳市自然醒智慧家居有限公司":"82233","首席产品官":null}
{"成都艾乐橙文化传播有限公司":"110551","教学部HRD":"2019-01-08 15:00:00.0"}
{"精诚瑞宝计算机系统有限公司":"23430","信息安全产品经理（上海）":null}
{"广西中科曙光云计算有限公司":"141473","人力资源总监":"2019-04-29 10:00:00.0"}
{"临沂市拓普网络股份有限公司":"150854","人力总监":null}
 
例3：
select map("clientname",clientname,"clientid",clientid) from dws.dws_awa_t_cal_recomperiods_result limit 10
返回：
{"clientname":"陕西天诚软件有限公司","clientid":"51025"}
{"clientname":"天俱时工程科技集团有限公司","clientid":"79261"}
{"clientname":"杭州览众数据科技有限公司","clientid":"48848"}
{"clientname":"上海融道文化传播有限公司","clientid":"157776"}
{"clientname":"奈良（上海）商务咨询有限公司","clientid":"129871"}
{"clientname":"深圳市自然醒智慧家居有限公司","clientid":"82233"}
{"clientname":"成都艾乐橙文化传播有限公司","clientid":"110551"}
{"clientname":"精诚瑞宝计算机系统有限公司","clientid":"23430"}
{"clientname":"广西中科曙光云计算有限公司","clientid":"141473"}
{"clientname":"临沂市拓普网络股份有限公司","clientid":"150854"}
 
例4：
select collect_list(map("clientname",clientname,"clientid",clientid)) from dws.dws_awa_t_cal_recomperiods_result limit 2
 
返回：
[{
	"clientname": "陕西天诚软件有限公司",
	"clientid": "51025"
}, {
	"clientname": "天俱时工程科技集团有限公司",
	"clientid": "79261"
}, {
	"clientname": "杭州览众数据科技有限公司",
	"clientid": "48848"
}
...
]