Hive
Hive
肯德基套餐
这个作者很懒,什么都没留下…
展开
-
hive合并多个数组
hive> select split(concat_ws(',',array("john", "james"), array('peter'), array("sam","peter")), ",") as a;OK["john","james","peter","sam","peter"]https://stackoverflow.com/questions/53958869/hiveql-merge-array-columns原创 2021-02-22 23:49:51 · 10471 阅读 · 0 评论 -
Hive grouping sets、cube 、rollup
背景假设有如下数据with query1 as ( select stack (5, 'A', 'x', '1', 1, 'B', 'x', '1', 2, 'C', 'x', '0', 3, 'D', 'y', '0', 4, 'E', 'y', '0', 5 ) as (a,b,c,d))假设a、b、c、d是数据中的四种属性现在想通过group by 操作统计出这四种属性不同组合的结原创 2020-12-06 16:27:21 · 955 阅读 · 0 评论 -
Hive in、exists 和 left semi join
with query1 as ( select stack(4, 'A', 1, 'B', 2, 'C', 3, 'D', 4) as (k,v)),query2 as ( select stack(4, 'A', 5, 'B', 6, 'E', 7, 'F', 8) as (k,v))数据:A:B:从保留A中k在query2中出现的数据1.常规写法select t1.k as k, t2.v as vfrom ( select k,原创 2020-12-05 12:54:16 · 1901 阅读 · 0 评论 -
Hive case when 两种写法
case when uid = 1 then 1 when uid = 2 then 2 else 3end as new_uidcase uid when 1 then 1 when 2 then 2 else 3end as new_uidbtw: case when不同条件之间是互斥的,下层的when条件肯定不满足上层所有when的条件原创 2020-12-02 23:14:12 · 2710 阅读 · 0 评论 -
hive生成连续日期或数字
with subquery as ( select split(space(datediff('2020-11-30','2020-11-01')), ' ') as x) select date_add('2020-11-01', idx) as new_datefrom subquery t lateral view posexplode(x) pe as idx, ele原创 2020-11-19 20:25:09 · 4103 阅读 · 1 评论 -
Hive使用with...as建立子查询
基本格式with sub_query_name1 as ( select xx from xxx), -- 注意这里用逗号分割sub_query_name2 as ( --注意这里没有as,上面一个as跟多个sub_query select xx from xx)select --正式查询语句,与上面的with as直接没有符号分割 xxfrom xx注意事项sub_query直接用逗号分割,一个sub_query用圆括号扩起来with 只能使用一次,后面原创 2020-08-30 17:00:51 · 2081 阅读 · 0 评论 -
Hive用直接传入的数据生成表
需求想通过fixed的数据直接生成一个hive 表,不是select from其他表的那种。类似pandas 生成dataframedata = pd.DataFrame([[1,2,3], [4,5,6]], columns=['a','b','c'])在Hive中可以通过stack来实现,stack APIhttps://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-stack(v原创 2020-08-30 12:06:31 · 360 阅读 · 0 评论 -
Hive条件count
记住一件事count时, null不coutsum 时,null不统计,相当于0条件count1.用ifcount(if(substr(uid,0,1)='4',uid,null))if(条件,条件成立值,条件不成立值)2.用case whencount(case when substr(uid,0,1)='4' then uid else null end)count(case when substr(uid,0,1)='4' then uid end )case when可以原创 2020-08-26 01:06:53 · 5292 阅读 · 5 评论