HUE-hive常用查询语句整理

【自取】最近整理的,有需要可以领取学习:

通过色调导入数据:<details><summary>[En]</summary>Data import through hue:</details>
1,create table demo_id(id string) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
2, load data inpath '/user/demo.csv' into table demo_id

#sql数据筛选
create table table_name as
    select a.id,a.name,a.time
    from a_name a
    where  a.type = 'A' and a.year = '2018' and
    A.month IN('01','02', '03') and a.idstr regxp '^\\$\\_[0-9][0-9]\\0.+'

#sql数据联表,左外连接查询
create table table_name as
    select b.id, b.name, b.month
    from a_name a JOIN b_name b
    on a.id =b.id
    where b.idstr regxp '^\\$\\_(?!01).+'

#sql数据查询 group by
create table table_name as
    select a.id,a.name,min(a.month)
    from a_name a
    where a.str = '你好' and a.m > '05'
    group by a.str,a.id

#sql数据排序 order by,将查询结果按照a字段分组(partition),然后组内按照b字段排序,row_number() 用于标记顺序
create table table_name as
    select * from (select *,row_number() over(partition by t.deviceid order by t.time DESC)
        as RNO
            from table_name_A t) as TEMP
    where TEMP.RNO<7

#sql数据切分字段,并排序
selcet b.id, substr(b.str,3,2),count(b.name) as count
    from table_name b
    group by b.id

#统计人数,按照用户去重
select count(distinct(a.id))
        from table_name a
#根据数字排序
order by cast(a.number as INT)
#正则匹配多条件
where a.str regexp '^\\$\\_01\\_(?!0\\01|0\\_00|0\\_11).+'

Original: https://www.cnblogs.com/smuxiaolei/p/10847391.html
Author: 懵懂的菜鸟
Title: HUE-hive常用查询语句整理

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/6825/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

发表回复

登录后才能评论
免费咨询
免费咨询
扫码关注
扫码关注
联系站长

站长Johngo!

大数据和算法重度研究者!

持续产出大数据、算法、LeetCode干货,以及业界好资源!

2022012703491714

微信来撩,免费咨询:xiaozhu_tec

分享本页
返回顶部