hive命令晋级

主要针对查询功能进行说明

• 增加输出字段信息

set hive.cli.print.header=true;

• 全量查找

SET mapreduce.input.fileinputformat.input.dir.recursive=true;

• 去重

select picfilepath, specid, collect_set(seriesid) from $TABLENAME where picclass=1 group by picfilepath, specid ; #去重后输出多字段

• 升序降序

select `picfilepath`, count(*) as num  from $TABLENAME where picclass=1 group by picfilepath order by num; #默认去重并按照键值数量num升序
select `picclass`, count(*) as num  from $TABLENAME group by picclass order by picclass; #去重并按照字段排序
select `picfilepath`, count(*) as num  from $TABLENAME where picclass=1 group by picfilepath order by num asc; #使用asc升序关键字
select `picfilepath`, count(*) as num  from $TABLENAME where picclass=1 group by picfilepath order by num desc; #使用desc降序关键字

• 数量查找

select `picfilepath`, count(*) as num  from $TABLENAME where picclass=1 group by picfilepath ; #重复字段的数量
select seriesid,count(picid) as num from $TABLENAME group by seriesid  having num <10000 order by num ; #数量阈值：picid超过10000的值

• 限制数量的查找

select picclass, count(picclass) as num from $TABLENAME group by picclass order by num limit 0, 1; #最少数量的
select picclass, count(picclass) as num from $TABLENAME group by picclass order by num desc limit 0, 1; #最多数量的
select picclass, count(picclass) as num from $TABLENAME group by picclass order by num limit 0, 4; #最少数量的前4个

• 区间查找

select seriesid,count(picid) as num from $TABLENAME where picclass=1  group by seriesid having num between 10000 and 30000  order by num ; #num位于[1w, 3w]的值
select seriesid,count(picid) as num from $TABLENAME where picclass between 1 and 3  group by seriesid  order by num ; #字段picclass位于[1, 3]区间的值

• 范围查询

hive -e 'set hive.cli.print.header=true; \
	select  b.brand_id, collect_set(b.brand_name),collect_set( b.factory_id), collect_set(b.factory_name),collect_set(b.series_id), \
	collect_set(b.series_name), collect_set(a.picfilepath) from  fdm.fdm_product as a left join dim.dim_series as b \
	on a.seriesid==b.series_id where b.series_id in ('4412', '149', '5118', '4960', '784',) and a.picclass ==1 group by b.brand_id, b.factory_id, b.series_id' >bd_series.txt

• 对比mysql查询方式

mysql -uhehe_reader -phehe_reader -h192.18.24.24 --default-character-set=utf8 -e \
  "select a.pic_url, a.desc from bee.yiche_pic as a where a.pic_class=6 and  a.desc in ('正侧（车头向右）') limit 0,5000;" 

• 联表查询

SELECT p.picfilepath ,p.seriesid, q.series_name,p.specid
FROM  $TABLENAMEA as p left join $TABLENAMEB as q
on  p.seriesid = q.series_id
where p.picclass=1 
order by p.seriesid, p.specid, q.series_name; #联表查询seriesid和series_id字段相等的值

select series_id,count(picid) as num 
from(select b.series_id as series_id,a.picid as picid
from (select picid,seriesid from $TABLENAMEA where picclass=1) a join 
(select * from $TABLENAMEB where series_is_public=1) b on a.seriesid=b.series_id) c
group by series_id
order by num;

参考文献：

1. http://www.runoob.com/mysql/mysql-join.html