1、在hive中建立以下三個表。
create table student(Sno int,Sname string,Sex string,Sage int,Sdept string)row format delimited fields terminated by ','stored as textfile;
create table course(Cno int,Cname string) row format delimited fields terminated by ',' stored as textfile;
create table sc(Sno int,Cno int,Grade int)row format delimited fields terminated by ',' stored as textfile;2、load資料到三個表中。load data local inpath '/home/student.txt' overwrite into table student;
load data local inpath '/home/sc.txt' overwrite into table sc;
load data local inpath '/home/course.txt' overwrite into table course;95001,李勇,男,20,CS
95002,劉晨,女,19,IS
95003,王敏,女,22,MA
95004,張立,男,19,IS
95005,劉剛,男,18,MA
95006,孫慶,男,23,CS
95007,易思玲,女,19,MA
95008,李娜,女,18,CS
95009,夢圓圓,女,18,MA
95010,孔小濤,男,19,CS
95011,包小柏,男,18,MA
95012,孫花,女,20,CS
95013,馮偉,男,21,CS
95014,王小麗,女,19,CS
95015,王君,男,18,MA
95016,錢國,男,21,MA
95017,王風娟,女,18,IS
95018,王一,女,19,IS
95019,邢小麗,女,19,IS
95020,趙錢,男,21,IS
95021,周二,男,17,MA
95022,鄭明,男,20,MA1,資料庫
2,數學
3,資訊系統
4,作業系統
5,資料結構
6,資料處理95001,1,81
95001,2,85
95001,3,88
95001,4,70
95002,2,90
95002,3,80
95002,4,71
95002,5,60
95003,1,82
95003,3,90
95003,5,100
95004,1,80
95004,2,92
95004,4,91
95004,5,70
95005,1,70
95005,2,92
95005,3,99
95005,6,87
95006,1,72
95006,2,62
95006,3,100
95006,4,59
95006,5,60
95006,6,98
95007,3,68
95007,4,91
95007,5,94
95007,6,78
95008,1,98
95008,3,89
95008,6,91
95009,2,81
95009,4,89
95009,6,100
95010,2,98
95010,5,90
95010,6,80
95011,1,81
95011,2,91
95011,3,81
95011,4,86
95012,1,81
95012,3,78
95012,4,85
95012,6,98
95013,1,98
95013,2,58
95013,4,88
95013,5,93
95014,1,91
95014,2,100
95014,4,98
95015,1,91
95015,3,59
95015,4,100
95015,6,95
95016,1,92
95016,2,99
95016,4,82
95017,4,82
95017,5,100
95017,6,58
95018,1,95
95018,2,100
95018,3,67
95018,4,78
95019,1,77
95019,2,90
95019,3,91
95019,4,67
95019,5,87
95020,1,66
95020,2,99
95020,5,93
95021,2,93
95021,5,91
95021,6,99
95022,3,69
95022,4,93
95022,5,82
95022,6,1003、hive的select
SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ] [LIMIT number]
hive> select Sno,Sname from student;Total MapReduce jobs = 1
hive> select distinct Sname from student inner join sc on student.Sno=Sc.Sno;Total MapReduce jobs = 2
3、hive的group by 和集合函數在一個query裡可以出現多個彙總函式，但是一個query語句裡2個彙總函式只能有一個distinct。
hive.map.aggr控制我們怎樣去彙總，預設值為false，如果設定為ture後，會在map任務裡執行第一層級的彙總，通常這樣會有 高的效率，但是需要更大的記憶體。
hive> select count(distinct Sno)count from student;
Total MapReduce jobs = 1
hive> select avg(distinct Grade) from sc where Cno=1;
select Grade from sc where Cno=1 sort by Grade desc limit 1; Total MapReduce jobs = 2
hive> select Cno,count(1) from sc group by Cno;
Total MapReduce jobs = 1
hive> select Sno from (select Sno,count(Cno)CountCno from sc group by Sno)a where a.CountCno>3;
Total MapReduce jobs = 1hive> select Sno from sc group by Sno having count(Cno)>3;
Total MapReduce jobs = 14、hive的Order By/Sort By/Distribute By/Cluster ByOrder By ，在strict 模式下（hive.mapred.mode=strict),order by 語句必須跟著limit語句，但是在非strict下就不是必須的，這樣做的理由是必須有一個reduce對最終的結果進行排序，如果最後輸出的行數過多，一個reduce需要花費很長的時間。hive> set hive.mapred.mode=strict;
hive> select Sno from student order by Sno;
FAILED: Error in semantic analysis: 1:33 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token 'Sno'Sort By，它通常發生在每一個redcue裡，“order by” 和“sort by"的區別在於，前者能給保證輸出都是有順序的，而後者如果有多個reduce的時候只是保證了輸出的部分有序。set mapred.reduce.tasks=<number>在sort by可以指定，在用sort by的時候，如果沒有指定列，它會隨機的分配到不同的reduce裡去。distribute by 按照指定的欄位對資料進行劃分到不同的輸出reduce中 此方法會根據性別劃分到不同的reduce中 ，然後按年齡排序並輸出到不同的檔案中。hive> set mapred.reduce.tasks=2;hive> insert overwrite local directory '/home/hadoop/out' select * from student distribute by Sex sort by Sage;Cluster By 能保證分配給同一個reduce的相同的列鄰近，除distribute
by除完成的功能外。5、Join
查詢每個學生及其選修課程的情況
- 如果join on的key值不是相同的話，會轉化為2個map/reduc e.g. 如果key相同的話會轉化為一個map/reduce job，由於第一個job的map/reduce的結果會緩衝起來，然後再跟第二個進行join，所以為了減少記憶體，必須把量少的放在第一個join上。
hive> select student.*,sc.*
from student join sc on (student.Sno =sc.Sno);查詢學生的得分情況。hive> select student.Sname,course.Cname,sc.Grade
from student join sc on student.Sno=sc.Sno join course on sc.Cno=course.cno;Total MapReduce jobs =
2
- LEFT，RIGHT 和 FULL
OUTER 關鍵字用於處理 join 中空記錄的情況。
hive> select student.Sname,sc.Cno from student left outer join sc on student.Sno=sc.Sno;如果student的sno值對應的sc在中沒有值，則會輸出student.Sname
null.如果用right out join會保留右邊的值，左邊的為null。
Join 發生在WHERE 子句之前。如果你想限制 join 的輸出，應該在 WHERE 子句中寫過濾條件——或是在join 子句中寫。這裡面一個容易混淆的問題是表分區的情況：
SELECT a.val, b.val FROM a
LEFT OUTER JOIN b ON (a.key=b.key)
WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'
會 join a 表到 b 表（OUTER JOIN），列出 a.val 和 b.val 的記錄。WHERE 從句中可以使用其他列作為過濾條件。但是，如前所述，如果 b 表中找不到對應 a 表的記錄，b 表的所有列都會列出NULL，包括 ds 列。也就是說，join 會過濾 b 表中不能找到匹配 a 表 join
key 的所有記錄。這樣的話，LEFT OUTER 就使得查詢結果與 WHERE 子句無關了。解決的辦法是在 OUTER JOIN 時使用以下文法：
SELECT a.val, b.val FROM a LEFT OUTER JOIN b
ON (a.key=b.key AND
b.ds='2009-07-07' AND
a.ds='2009-07-07')
這一查詢的結果是預先在 join 階段過濾過的，所以不會存在上述問題。這一邏輯也可以應用於 RIGHT 和 FULL 類型的 join 中。
Join 是不能交換位置的。無論是 LEFT 還是 RIGHT join，都是左串連的。
SELECT a.val1, a.val2, b.val, c.val
FROM a
JOIN b ON (a.key = b.key)
LEFT OUTER JOIN c ON (a.key = c.key)
先 join a 表到 b 表，丟棄掉所有join key 中不匹配的記錄，然後用這一中間結果和 c 表做 join。這一表述有一個不太明顯的問題，就是當一個 key 在 a 表和 c 表都存在，但是 b 表中不存在的時候：整個記錄在第一次 join，即 a
JOIN b 的時候都被丟掉了（包括a.val1，a.val2和a.key），然後我們再和 c 表 join 的時候，如果c.key 與 a.key 或 b.key 相等，就會得到這樣的結果：NULL,
NULL, NULL, c.val。
- LEFT SEMI JOIN 是 IN/EXISTS 子查詢的一種更高效的實現。Hive 當前沒有實現 IN/EXISTS 子查詢，所以你可以用 LEFT SEMI JOIN 重寫你的子查詢語句。LEFT SEMI JOIN 的限制是， JOIN 子句中右邊的表只能在 ON 子句中設定過濾條件，在 WHERE 子句、SELECT 子句或其他地方過濾都不行。
SELECT a.key, a.value
FROM a
WHERE a.key in
(SELECT b.key
FROM B);
可以被重寫為：
SELECT a.key, a.val
FROM a LEFT SEMI JOIN b on (a.key = b.key)
- 如果2個join的表非常小的話，join的過程可能只在mapper中就可以完成。
- 如果有兩個表join on的值為buckets列，並且buckets數量相同的話，可以只在mapper階段完成。e.g.
SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key
在mapper階段，可以對b的值按buckets進行取值，a表的一個buckets取b表的一個buckets進行join，這樣做並不是模式的，必須設定。
set hive.optimize.bucketmapjoin = true
查詢選修2號課程且成績在90分以上的所有學生。hive> select student.Sname,sc.Grade from student join sc on student.Sno=sc.Sno where sc.Cno=2 and sc.Grade>90;查詢與“劉晨”在同一個系學習的學生hive> select s1.Sname from student s1 left semi join student s2 on s1.Sdept=s2.Sdept and s2.Sname='劉晨';6、hive的最佳化
- 好的模型設計事半功倍。
- 解決資料扭曲問題。
- 減少job數。
- 設定合理的map reduce的task數，能有效提升效能。(比如，10w+層級的計算，用160個reduce，那是相當的浪費，1個足夠)。
- 瞭解資料分布，自己動手解決資料扭曲問題是個不錯的選擇。set hive.groupby.skewindata=true;這是通用的演算法最佳化，但演算法最佳化有時不能適應特定業務背景，開發人員瞭解業務，瞭解資料，可以通過商務邏輯精確有效解決資料扭曲問題。
- 資料量較大的情況下，慎用count(distinct)，count(distinct)容易產生傾斜問題。
- 對小檔案進行合并，是行至有效提高調度效率的方法，假如所有的作業設定合理的檔案數，對雲梯的整體調度效率也會產生積極的正向影響。
- 最佳化時把握整體，單個作業最優不如整體最優。
