【Hive】- Note Of Hive Sql

【Hive】- Note Of Hive Sql

1. 基础语法

查看分区​​​show partitions tabel​​查看表结构​​​describe table​​字符串转日期​​​cast(string_field as date)​​字符串转时间​​​cast(string_field as timestamp)​​

2. 求累计频数

SELECT ioverduedays, sum(overdue_num) AS overdue_num, SUM(SUM(overdue_num)) OVER ( ORDER BY ioverduedays ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amountFROM (SELECT ioverduedays, count(*) AS overdue_numFROM parquet_myd.aa_ld_channel_feature_tmpWHERE overdue_status = 'yes_overdue' AND irepaystatus = 30GROUP BY ioverduedaysORDER BY ioverduedays ASC) AS tgroup by

3. 分组排序

SELECTrow_number()over(Partition BY user_id ORDER BY gmt_create) as rownumFROM Tabel_a

4. hive调优

set hive.cli.print.header=true;set hive.exec.parallel=true; set mapred.reduce.tasks=8;set hive.exec.parallel.thread.number=16;

5. hive解析json

json: { "status": { "person": { "name": false } }}## 取出key为name对应的valueselect get_json_object(content,'$.status.person.name') from test limit 1;## 对json的要求 标准json形式，且字符串用""引起来

6.正则抽取字符串

## para1:待解析字符串，para2:正则表达式,para3:满足匹配的索引，一般默认为1## 索引为1SELECT regexp_extract('"学位":"博士","入学时间":"2018-05-20"', '"学位":"(.*)","入学(.*)"', 1) ## 索引为2SELECT regexp_extract('"学位":"博士","入学时间":"2018-05-20"', '"学位":"(.*)","入学(.*)"', 2) # 时间":"2018-05-20

7. null转其他

## 如果为空值，则置0SELECT NVL(field,0)

8. 时间和日期处理函数

1) 求某时间处于当年的第几月

##　衍生特征的时候总归会用到吧SELECT month('2003-03-15 01:22:33')

2) 求某时间处于当年的第几周

SELECT weekofyear('2003-03-15 01:22:33')

3) 求某个时间处于星期几

## 逻辑要注意下:周日->周六：1->7SELECT pmod(datediff('2018-06-03','1900-01-07'),7) +1

4) 求某时间处于当月的第几天

SELECT day('2003-03-15 01:22:33')

5) 求某时间距离当月月底日期

SELECT datediff(last_day('2018-06-01'),'2018-06-01')

6) 求某时间所在小时时刻

SELECT hour('2018-06-01 12:00:00')

9. hive 同步到impala

## 在impala中执行invalidate metadata parquet_myd.tmp_education_user_0416

10. 指定分隔符拆分字符串

## hive## 返回结果:["10","11","12"]SELECT split("10,11,12",",")## 返回结果:10SELECT split("10,11,12",",")[0]

## impala中## 返回10SELECT split_part("10.11.12",".",1)

11.

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。