Hive sql & Spark sql笔记

记录了日常使用时遇到的特殊的查询语句。不断更新～

hive -e "...Hive SQL..." > /tmp/out

sparkhive --disableQuotingForSV=true  --slient=true --showHeader=false --outputformat=tsv -e "...SPARK SQL..." > /tmp/out

--disableQuotingForSV=true 可以去掉输出字段为string的耳朵

select id, from_unixtime(ts , 'yyyy-MM-dd HH:mm:ss') from log where (dt=20180601 or dt=20180602）;

select split(abc,',')[0] as a , count(id) from log where dt=20180601 group by a;

优化前

select id from（select id, a_list, size(a_list) size from log where dt=20180601) t where t.a_list[t.size-1]=0

优化后

select distinct id from log where dt=20180601 and a_list[size(a_list)-1]=0

解释器有自动谓词下推策略进行优化。优化前后仅有不到1s差距。

 select id, sum(col) sum_a from (select id, posexplode(a_list) from log where dt=20180601) group by id having sum_a>1;

巴特西