好程序員大數(shù)據(jù)學(xué)習(xí)路線之hive表的查詢

　　好程序員大數(shù)據(jù)學(xué)習(xí)路線之hive表的查詢
　　1.join 查詢
　　1、永遠(yuǎn)是小結(jié)果集驅(qū)動(dòng)大結(jié)果集(小表驅(qū)動(dòng)大表，小表放在左表)。 2、盡量不要使用join，但是join是難以避免的。
　　left join 、 left outer join 、 left semi join(左半開(kāi)連接，只顯示左表信息)
　　hive在0.8版本以后開(kāi)始支持left join
　　left join 和 left outer join 效果差不多
　　hive的join中的on只能跟等值連接 "=",不能跟< >= <= !=
　　join:不加where過(guò)濾，叫笛卡爾積
　　inner join ： 內(nèi)連接
　　outer join ：外鏈接
　　full outer join ： 全外連接，尋找表中所有滿足連接(包括where過(guò)濾)。
　　##創(chuàng)建學(xué)生測(cè)試表
CREATE TABLE student (
id INT,
name string,
sex string,
birth string,
department string,
address string
)
row format delimited fields terminated by ','
;
##從本地加載數(shù)據(jù)
load data local inpath '/root/Desktop/student.txt' into table student;
##創(chuàng)建學(xué)生成績(jī)測(cè)試表
CREATE TABLE score (
stu_id INT,
c_name string,
grade string
)
row format delimited fields terminated by ','
;
##從hdfs加載數(shù)據(jù)
load data inpath '/hive.data/score.txt' into table score;
##創(chuàng)建學(xué)生基本信息測(cè)試表
create table stuinfo(
id int,
height double,
weight double
)
row format delimited fields terminated by ','
;
##從本地加載數(shù)據(jù)
load data local inpath '/root/Desktop/stuinfo.txt' into table stuinfo;
　　1.1 左連接
　　如果左邊有數(shù)據(jù)，右邊沒(méi)有數(shù)據(jù)，則左邊有數(shù)據(jù)的記錄的對(duì)應(yīng)列返回為空。
　　##使用左連接查詢：student表為驅(qū)動(dòng)表，通過(guò)id連接
select
student.name,
score.c_name,
score.grade
from student
left join score
on student.id = score.stu_id
;
　　1.2 左外連接
　　如果左邊有數(shù)據(jù)，右邊沒(méi)有數(shù)據(jù)，則左邊有數(shù)據(jù)的記錄的對(duì)應(yīng)列返回為空。
　　##使用左外連接查詢：student表為驅(qū)動(dòng)表，通過(guò)id連接
select
student.name,
score.c_name,
score.grade
from student
left outer join score
on student.id = score.stu_id
;
　　1.3 左半連接
　　left semi join是left join 的一種優(yōu)化，并且通常用于解決exists in，寫(xiě)left semi join 查詢時(shí)必須遵循一個(gè)限制:右表(sales) 只能在 on子句中出現(xiàn)，且不能在select 表達(dá)式中引用右表。
　　##左半連接
select
student.
from student
left semi join score on
student.id=score.id
;
##查詢結(jié)果等價(jià)于
select from student where student.id not in (select stu_id from score);
　　1.4 右外連接
　　right outer join和right join差不多，不常用
　　如果左邊沒(méi)有數(shù)據(jù)，右邊有數(shù)據(jù)，則右邊有數(shù)據(jù)的記錄對(duì)應(yīng)列返回為空。
　　##使用右外連接查詢：score表為驅(qū)動(dòng)表，通過(guò)id連接
select
student.name,
score.c_name,
score.grade
from student
right outer join score
on student.id = score.stu_id
;
　　1.5 全外連接
　　##全外連接
select
student.name,
score.c_name,
score.grade
from student
full outer join score
on student.id = score.stu_id
;
　　##不使用join，from 后面跟多個(gè)表名使用","分割 、 inner join 、join ：三種效果一樣
select
student.name,
score.c_name,
score.grade
from student，score
where
student.id = score.stu_id
;
　　1.6 內(nèi)連接
　　##使用內(nèi)連接查詢所有有考試成績(jī)的學(xué)生的學(xué)生姓名，學(xué)科名，學(xué)科成績(jī)，及身高
select
student.name,
score.c_name,
score.grade,
stuinfo.height
from student
inner join score
on student.id = score.stu_id
join stuinfo
on student.id = stuinfo.id
;
　　1.7 hive提供一個(gè)小表標(biāo)識(shí)，是hive提供的一種優(yōu)化機(jī)制
　　##小表標(biāo)識(shí)：/+STREAMTABLE(表名)/
select
/+STREAMTABLE(score)/
student.name,
score.c_name,
score.grade,
stuinfo.height
from student
inner join score
on student.id = score.stu_id
join stuinfo
on student.id = stuinfo.id
;
　　1.8 map-side join：
　　如果有一個(gè)連接表小到足以放入內(nèi)存， Hive就可以把較小的表放入每個(gè)mapper的內(nèi)存來(lái)執(zhí)行連接操作。這就叫做map連接。當(dāng)有一大一小表的時(shí)候，適合用map-join。會(huì)將小表文件緩存，放到內(nèi)存中，在map端和內(nèi)存中的數(shù)據(jù)一一進(jìn)行匹配，連接查找關(guān)系。hive-1.2.1 默認(rèn)已經(jīng)開(kāi)啟map-side join：hive.auto.convert.join=true
　　select
student.name,
score.c_name,
score.grade,
stuinfo.height
from student
inner join score
on student.id = score.stu_id
join stuinfo
on student.id = stuinfo.id
;
　　hive 0.7版本以前，需要hive提供的mapjoin()標(biāo)識(shí)。來(lái)標(biāo)識(shí)該join為map-side join。標(biāo)識(shí)已經(jīng)過(guò)時(shí)，但是寫(xiě)上仍然識(shí)別
　　select
/+MAPJOIN(student)/
student.name,
score.c_name,
score.grade,
stuinfo.height
from student
inner join score
on student.id = score.stu_id
join stuinfo
on student.id = stuinfo.id
;
　　hive怎么知道將多大文件緩存,配置文件中配置，下面為默認(rèn)配置
　　<property>
<name>hive.mapjoin.smalltable.filesize</name>
<value>25000000</value>
</property>
　　2.group by：
　　GROUP BY 語(yǔ)句通常會(huì)和聚合函數(shù)一起使用，按照一個(gè)或者多個(gè)列對(duì)結(jié)果進(jìn)行分組，然后對(duì)每個(gè)組執(zhí)行聚合操作。使用group by后，查詢的字段要么出現(xiàn)在聚合函數(shù)中，要么出現(xiàn)在group by 后面。
　　##查詢學(xué)生的考試門數(shù)，及平均成績(jī)
select
count(),
avg(grade)
from student join score
on student.id=score.stu_id
group by student.id;
　　3.where
　　SELECT語(yǔ)句用于選取字段，WHERE語(yǔ)句用于過(guò)濾條件，兩者結(jié)合使用可以查找到符合過(guò)濾條件的記錄。后面不能跟聚合函數(shù)或者聚合函數(shù)的結(jié)果，能跟普通的查詢值或者是方法
　　##查詢學(xué)生的考試平均成績(jī)大于90分
select
count(),
avg(grade) avg_score
from student join score
on student.id=score.stu_id
where student.id<106
group by student.id
having avg_score>90;
　　4.having:
　　對(duì)查詢出來(lái)的結(jié)果進(jìn)行過(guò)濾，通常和group by搭配使用。
　　##查詢學(xué)生的考試平均成績(jī)大于90分的學(xué)生id及平均成績(jī)
select
count(*),
avg(grade) avg_score
from student join score
on student.id=score.stu_id
group by student.id
having avg_score>90;
　　5.排序
　　sort by ：排序，局部排序，只能保證單個(gè)reducer的結(jié)果排序。 order by: 排序，全局排序。保證整個(gè)job的結(jié)果排序。 當(dāng)reducer只有1個(gè)的時(shí)候，sort by 和 order by 效果一樣。建議使用sort by 通常和： desc asc .(默認(rèn)升序)
　　##查詢學(xué)生平均成績(jī)按照降序排序
select
avg(grade) avg_score
from
student join score
on student.id=score.stu_id
group by student.id
order by avg_score desc;

　　設(shè)置reducer個(gè)數(shù)(等于1 或者 2)：
　　set mapreduce.job.reduces=2
　　##使用order by
select
avg(grade) avg_score
from
student join score
on student.id=score.stu_id
group by student.id
order by avg_score desc;
##使用sort by
select
avg(grade) avg_score
from
student join score
on student.id=score.stu_id
group by student.id
sort by avg_score desc;

　　6.distribute by：
　　控制map中如何輸出到reduce。整個(gè)hive語(yǔ)句轉(zhuǎn)換成job默認(rèn)都有該過(guò)程，如果不寫(xiě)，默認(rèn)使用第一列的hash值來(lái)分。當(dāng)只有一個(gè)reducer的時(shí)候不能體現(xiàn)出來(lái)。如果distribute by和sort by 一起出現(xiàn)的時(shí)候注意順序問(wèn)題？？distribute by在前面
　　clusterd by ： 它等價(jià)于distribute by和sort by(升序)。后面跟的字段名需要一樣 clusterd by它既兼有distribute by，還兼有sort by (只能是升序)
　　select
id
from
student
distribute by id
sort by id;

　　select
id
from
student
clusterd by id;

　　7.limit : 限制結(jié)果集的。
　　select
id,name
from student
limit 3;

　　8.union all：
　　將兩個(gè)或者多個(gè)查詢的結(jié)果集合并到一起，不去重每一個(gè)結(jié)果集排序。字段數(shù)必須一致，字段類型盡量相同
　　##將id<108的和id>103的使用union all合并
select
id sid,
name snames
from student
where id<108
union all
select
id sid,
name sname
from student
where id>103;
　　9.union：
　　將兩個(gè)或者多個(gè)查詢結(jié)果集合并到一起，去重,合并后的數(shù)據(jù)排序
　　##將id<108的和id>103的使用union合并
select
id sid,
name sname
from student
where id<108
union
select
id sid,
name sname
from student
where id>103
order by sname;
　　10.子查詢
　　子查詢是內(nèi)嵌在另一個(gè)SQL 語(yǔ)句中的SELECT 語(yǔ)句。Hive 對(duì)子查詢的支持很有限。它只允許子查詢出現(xiàn)在SELECT 語(yǔ)句的FROM 子句中。Hive支持非相關(guān)子查詢，這個(gè)子查詢通過(guò)IN或EXISTS語(yǔ)法在WHERE子句中進(jìn)行查詢。Hive目前暫不支持相關(guān)子查詢，相關(guān)子查詢的執(zhí)行依賴于外部查詢的數(shù)據(jù)。
　　##非相關(guān)子查詢
select
id,name
from
student
where id
in
(select stu_id
from
score);
　　##相關(guān)子查詢的執(zhí)行依賴于外部查詢的數(shù)據(jù)
select sid,uname
from
(select
id sid,
name uname
from student
) s
order by sid
##外層查詢像訪問(wèn)表那樣訪問(wèn)子查詢的結(jié)果，這是為什么必須為子查詢賦予一個(gè)別名(s)的原因。子查詢中的列必須有唯一的名稱，以便外層查詢可以引用這些列。