当前位置:   article > 正文

hive 常用sql语句整理_hive查看建表语句

hive查看建表语句

1.查看建表语句

  1. show create table table_name;
  2. -- 或者
  3. desc formatted table_name;

2.刷新表

refresh table_name;


3.表重命名

alter table table_name rename to new_table_name;


4.增加列 可以增加多列,用‘,’隔开

alter table table_name add columns (col_de string comment 'department name');


5.修改字段

  1. alter table table_name change col_old_name col_new_name string comment 'the new name is string' 
  2. 如果表有分区,要加上 cascade;
  3. alter table table_name change col_old_name col_new_name string comment 'the new name is string'  cascade;
  4. 否则数据可能就查不了了


6.调整字段位置 

alter table table_name change col_old_name col_new_name string comment 'the new name is string'  after col_1


7.删除字段 表中有a b c 字段,删除 c

alter table table_name replace columns ( a int, b int);

一般新增一列于调整字段位置一起配合使用


7 查看hive表信息

desc formatted provision_digital.provision_digital_store_rank

8.array<string> 类型的数据查询问题

只取第一条,网上搜到的例子这样查询可以

select id,id[0from table;

实际查询没有作用,不生效,查出来的结果还是一个数组

  1. select id,id[0],size(id)  from table;
  2. -- 或者
  3. select id,id[0from table where size(id) > 0;

可以实现想要的效果。

9.建表语句

  1. CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  2. [(col_name data_type [COMMENT col_comment], ...)]
  3. [COMMENT table_comment]
  4. [PARTITIONED BY(col_name data_type [COMMENT col_comment], ...)]
  5. [CLUSTERED BY (col_name, col_name, ...)
  6. [SORTED BY(col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  7. [ROW FORMAT row_format]
  8. [STORED AS file_format]
  9. [LOCATION hdfs_path]

10.查看hive表hdfs文件

hdfs dfs -ls hdfs://xxx/xxx/xxxx/xxxx/xxxx/xxxx

11.分组排序 row_number() 

  1. select name,count,row_number() over (partition by name order by count) rn from xx.t_xxx_id
  2. -- 分组排序后取第一条
  3. select * from (
  4. select name,count,row_number() over (partition by name order by count) rn from xx.t_xxx_id
  5. ) t where rn = 1;

12.跳序排序 rank()

  1. select name,count, rank() over (order by count desc) as rank from xxx.t_table_xxx;
  2. -- 如果两个排名第2,位于第三排名的序号为4

13.一行转多行

  1. -- array_id 为array<string> 字段类型,或者字符串用逗号拼接
  2. select name,array_id from xxx.t_table_xxx;
  3. -- array<string>
  4. select select name,id from (
  5. select name,array_id from xxx.t_table_xxx
  6. ) t LATERAL VIEW explode(t.array_id) array_id as id
  7. ;
  8. -- 字符串,先用 split将字段分隔为数组,再用上面的方法
  9. select name,split(array_id,',') from xxx.t_table_xxx;

14,字段类型转 cast

cast(count1 as int )

15.多列转多行

  1. 源数据
  2. select id,mobile1,mobile2,mobile3 from xxxx.xxxxxxx
  3. 转换
  4. select a.id,b.idList,b.value from (
  5. select id,mobile1,mobile2,mobile3 from xxxx.xxxxxxx
  6. ) a lateral view explode (map('mobile1',mobile1
  7. ,'mobile2',mobile2
  8. ,'mobile3',mobile3)) b as idList, value

16.多行转多列

  1. -- mobile1 为map中的key kv1[xx] 既取出 key为xx的 value值
  2. -- eg: {1=2, 2=2, 3=3, 4=4} ; kv1[4] 查询结果为 4
  3. -- map里面为字符串时,需要加引号
  4. select brand,
  5. kv1['mobile1'] mobile1,
  6. kv1['mobile2'] mobile2,
  7. kv1['mobile3'] mobile3
  8. from (
  9. select brand, str_to_map(concat_ws(',', collect_set(concat(idList, '-', value))),',','-') kv1
  10. from (
  11. select brand, idList, value
  12. from xxxx.xxxxxxx
  13. ) tmp
  14. group by brand
  15. ) t

17. jsonarray jsonobject

不使用自定义udf函数,用hive collect_set、concat_ws,concat函数

collect_set 和collect_list 两个都可以用,set 会去重

concat 作为字符串拼接函数,如

concat("[",xxxxx,"]") xxxxx

输出的结果就是 [xxxxxx]

concat_ws 指定字符串直接拼接的连接符

concat_ws("-",xx)

输出结果,xx-xx-xx

示例代码:

  1. -- 输出格式 ["xxxx","xxxx"]
  2. select id
  3. , concat("[",concat_ws(",",collect_set(xxxx)),"]") xxxx
  4. from (
  5. select id
  6. , concat("\"",xxxx,"\"") xxxx
  7. from xxxxx.xxxxxxx
  8. ) t group by id
  9. ;
  10. -- 输出格式 ["{xxxx}","{xxxx}"]
  11. select id
  12. , concat("[",concat_ws(",",collect_set(xxxx)),"]") xxxx
  13. from (
  14. select id
  15. , concat("{",xxxx,"}") xxxx
  16. from xxxxx.xxxxxxx
  17. ) t group by id
  18. ;

18 to_json ()

可以将array类型数据直接转成 json串,注意低版本不支持

19 range between /rows between

可以用来统计类似 计算每天前30天的总量

可以将hive表的日期转为时间戳,用时间戳进行range between 比较

如:

  1. -- 表 test_table
  2. -- dt 分区,gc 每天的总数,id 用户 ,dt_1 每天的时间戳
  3. select id
  4. , sum(gc) over(partition by mid order by dt_1 range between 2592000 PRECEDING AND 0 FOLLOWING) as gc_last30days
  5. , dt
  6. from test_table
  7. -- 常用边界
  8. -- unbounded 无边界
  9. -- preceding 往前
  10. -- following 往后
  11. -- unbounded preceding 往前所有行,即初始行
  12. -- n preceding 往前n行
  13. -- unbounded following 往后所有行,即末尾行
  14. -- n following 往后n行
  15. -- current row 当前行

PRECEDING 当前减去

FOLLOWING 当前加上

rows between 类似用法,只是这个统计的是行数。

20。 获取某月的第一天

select trunc(current_date(),'MM');
select trunc('2022-04-30','MM');

返回 2022-04-01

trunc()也可以用来取整 如:

trunc(123.456) 得到123 也可以指定截取位数,默认0 ,即只取整数

还有其他方式获取:

  1. select concat(SUBSTR(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAY(FROM_UNIXTIME(UNIX_TIMESTAMP()))),1,7) ,'-01')
  2. ,concat(substr(add_months(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1),1,7),'-01')

21 获取某月最后一天

  1. select date_sub(trunc(current_date,'MM'),1),
  2. date_format(last_day(add_months(current_date(),-1)),'yyyyMMdd'),
  3. DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAY(FROM_UNIXTIME(UNIX_TIMESTAMP())))
  4. ;

trunc 获取某月第一天 ,再用date_sub 减去1天,即上某个月的最后一天

总结:获取上个月第一天和上个月最后一天,并且格式转成yyyyMMdd

select date_format(trunc(add_months(current_date,-1),'MM'),'yyyyMMdd'),date_format(date_sub(trunc(current_date,'MM'),1),'yyyyMMdd');

22 分组 grouping sets

同一张表,t1表,同一时间范围内,分别统计字段,(a,b),(a,c),(a,c,d) 不同维度情况下的count值。这种情况可以使用 grouping sets 方法,只扫描表一次,提高执行效率

  1. select a
  2. , b
  3. , c
  4. , d
  5. , count(1) as cnt
  6. from t1
  7. group by a, b, c, d grouping sets((a,b) ,(a,c) ,(a,c,d))

数据结果类似下图

 再通过简单的筛选条件就可以得到各个维度的统计值

23 Struct 类型数据

组装map 结果或者 json结构数据。

使用 named_struct()函数,可以得到数据结构为 struct<string,string...> 

  1. select named_struct('id','1','name','xx1');
  2. select array(named_struct('id','1','name','xx1'),named_struct('id','2','name','xx2'));

 如果要将结构类的数组数据拆分开可以使用 explode() 函数

select explode(array(named_struct('id','1','name','xx1'),named_struct('id','2','name','xx2'))) as json;

 

一般explode函数需要用 lateral view

  1. select *
  2. from ( select array(named_struct('id','1','name','xx1'),named_struct('id','2','name','xx2')) as arr
  3. ) t lateral view explode(arr) js as jsons
  4. ;

 

 结构数据读取,

  1. select * ,jsons.id,jsons.name
  2. from ( select array(named_struct('id','1','name','xx1'),named_struct('id','2','name','xx2')) as arr
  3. ) t lateral view explode(arr) js as jsons
  4. ;

24 json_tuple json 数据读取

json_tuple 参数必须全部为string类型,所以使用named_struct 函数得到的json结构数据不可以直接用

  1. select json_tuple('{"id":"1","name":"xx1"}' ,'id','name');

不能解析json_array数据,可以先用explode数据拆分多行,再用json_tuple解析获取字段值 

25  trino 查询hive 

使用trino 连接hive查询可以实现快速查询,但是有很多方便的函数不能使用,在这里整理记录一下可以使用的函数。

查询json 

json_extract_scalar(josn,'$.json_path')

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/神奇cpp/article/detail/855477
推荐阅读
相关标签
  

闽ICP备14008679号