1、目錄課程大綱（HIVE增強）31.Hive基本概念41.1Hive簡介41.1.1什么是Hive41.1.2為什么使用Hive41.1.3Hive的特點41.2Hive架構(gòu)51.2.1架構(gòu)圖51.2.2基本組成51.2.3各組件的基本功能51.3Hive與Hadoop的關(guān)系61.4Hive與傳統(tǒng)數(shù)據(jù)庫對比61.5Hive的數(shù)據(jù)存儲62.Hive基本操作72.1DDL操作72.1.1創(chuàng)建表72.1.2修改表92.1.3顯示命令112.2DML操作112.2.1Load112.2.2Insert132.2.3SELECT152.3 Hive Join182.5 Hive Shell使用進(jìn)階212.

2、5.1 Hive命令行212.5.2. Hive參數(shù)配置方式234. Hive函數(shù)244.1 內(nèi)置運算符244.2 內(nèi)置函數(shù)244.3 Hive自定義函數(shù)和Transform244.3.1 自定義函數(shù)類別244.3.2 UDF開發(fā)實例244.3.3 Transform實現(xiàn)255. Hive執(zhí)行過程實例分析265.1 JOIN265.2 GROUP BY265.3 DISTINCT276. Hive使用注意點（各種小細(xì)節(jié)）276.1 字符集276.2 壓縮286.3 count(distinct)286.4 子查詢286.5 Join中處理null值的語義區(qū)別286.6 分號字符296.7 In

3、sert296.7.1新增數(shù)據(jù)296.7.2 插入次序306.7.3 初始值307. Hive優(yōu)化技巧317.1 HADOOP計算框架特性317.2 優(yōu)化的常用手段概述317.3 全排序327.3.1 例1327.3.2 例2347.4 怎樣寫exist/in子句367.5 怎樣決定reducer個數(shù)367.6 合并MapReduce操作367.7 Bucket 與 Sampling377.8 Partition優(yōu)化387.9 JOIN優(yōu)化397.9.1 JOIN原則397.9.2 Map Join397.10 數(shù)據(jù)傾斜407.10.1 空值數(shù)據(jù)傾斜407.10.2 不同數(shù)據(jù)類型關(guān)聯(lián)產(chǎn)生數(shù)據(jù)傾

4、斜417.10.3 大表Join的數(shù)據(jù)偏斜417.11 合并小文件427.12 Group By 優(yōu)化437.12.1 Map端部分聚合：437.12.2 有數(shù)據(jù)傾斜的時候進(jìn)行負(fù)載均衡438. Hive實戰(zhàn)44Hive 實戰(zhàn)案例1數(shù)據(jù)ETL44需求：44數(shù)據(jù)示例：44實現(xiàn)步驟：45Hive 實戰(zhàn)案例2訪問時長統(tǒng)計47需求：47實現(xiàn)步驟：47Hive實戰(zhàn)案例3級聯(lián)求和48需求：48實現(xiàn)步驟48課程大綱（HIVE增強）Hive增強HIVE基本概念HIVE架構(gòu)及運行機制HQL-DDL基本語法HQL-DML基本語法HIVE的joinHIVE UDF函數(shù)HIVE shell基本操作HIVE 參數(shù)配置HI

5、VE 自定義函數(shù)和TransformHIVE 執(zhí)行HQL的實例分析HIVE最佳實踐注意點HIVE優(yōu)化策略HIVE實戰(zhàn)案例1HIVE實戰(zhàn)案例2HIVE實戰(zhàn)案例3學(xué)習(xí)目標(biāo)：1、熟練掌握hive的使用2、熟練掌握hql的編寫3、理解hive的工作原理4、具備hive應(yīng)用實戰(zhàn)能力1. Hive基本概念1.1 Hive簡介1.1.1 什么是HiveHive是基于Hadoop的一個數(shù)據(jù)倉庫工具，可以將結(jié)構(gòu)化的數(shù)據(jù)文件映射為一張數(shù)據(jù)庫表，并提供類SQL查詢功能。1.1.2 為什么使用Hive 直接使用hadoop所面臨的問題 人員學(xué)習(xí)成本太高 項目周期要求太短 MapReduce實現(xiàn)復(fù)雜查詢邏輯開發(fā)難度太大

6、 為什么要使用Hive 操作接口采用類SQL語法，提供快速開發(fā)的能力。 避免了去寫MapReduce，減少開發(fā)人員的學(xué)習(xí)成本。 擴展功能很方便。1.1.3 Hive的特點 可擴展 Hive可以自由的擴展集群的規(guī)模，一般情況下不需要重啟服務(wù)。 延展性 Hive支持用戶自定義函數(shù)，用戶可以根據(jù)自己的需求來實現(xiàn)自己的函數(shù)。 容錯 良好的容錯性，節(jié)點出現(xiàn)問題SQL仍可完成執(zhí)行。1.2 Hive架構(gòu)1.2.1 架構(gòu)圖Jobtracker是hadoop1.x中的組件，它的功能相當(dāng)于： Resourcemanager+AppMasterTaskTracker 相當(dāng)于： Nodemanager + yarnc

7、hild1.2.2 基本組成 用戶接口：包括 CLI、JDBC/ODBC、WebGUI。 元數(shù)據(jù)存儲：通常是存儲在關(guān)系數(shù)據(jù)庫如 mysql , derby中。 解釋器、編譯器、優(yōu)化器、執(zhí)行器。1.2.3 各組件的基本功能 用戶接口主要由三個：CLI、JDBC/ODBC和WebGUI。其中，CLI為shell命令行；JDBC/ODBC是Hive的JAVA實現(xiàn)，與傳統(tǒng)數(shù)據(jù)庫JDBC類似；WebGUI是通過瀏覽器訪問Hive。 元數(shù)據(jù)存儲：Hive 將元數(shù)據(jù)存儲在數(shù)據(jù)庫中。Hive 中的元數(shù)據(jù)包括表的名字，表的列和分區(qū)及其屬性，表的屬性（是否為外部表等），表的數(shù)據(jù)所在目錄等。 解釋器、編譯器、優(yōu)化器

8、完成 HQL 查詢語句從詞法分析、語法分析、編譯、優(yōu)化以及查詢計劃的生成。生成的查詢計劃存儲在 HDFS 中，并在隨后有 MapReduce 調(diào)用執(zhí)行。1.3 Hive與Hadoop的關(guān)系 Hive利用HDFS存儲數(shù)據(jù)，利用MapReduce查詢數(shù)據(jù)1.4 Hive與傳統(tǒng)數(shù)據(jù)庫對比1. 查詢語言。由于 SQL 被廣泛的應(yīng)用在數(shù)據(jù)倉庫中，因此，專門針對 Hive 的特性設(shè)計了類 SQL 的查詢語言 HQL。熟悉 SQL 開發(fā)的開發(fā)者可以很方便的使用 Hive 進(jìn)行開發(fā)。 2. 數(shù)據(jù)存儲位置。Hive 是建立在 Hadoop 之上的，所有 Hive 的數(shù)據(jù)都是存儲在 HDFS 中的。而數(shù)據(jù)庫則可以

9、將數(shù)據(jù)保存在塊設(shè)備或者本地文件系統(tǒng)中。 3. 數(shù)據(jù)格式。Hive 中沒有定義專門的數(shù)據(jù)格式，數(shù)據(jù)格式可以由用戶指定，用戶定義數(shù)據(jù)格式需要指定三個屬性：列分隔符（通常為空格、”t”、”x001）、行分隔符（”n”）以及讀取文件數(shù)據(jù)的方法（Hive 中默認(rèn)有三個文件格式 TextFile，SequenceFile 以及 RCFile）。由于在加載數(shù)據(jù)的過程中，不需要從用戶數(shù)據(jù)格式到 Hive 定義的數(shù)據(jù)格式的轉(zhuǎn)換，因此，Hive 在加載的過程中不會對數(shù)據(jù)本身進(jìn)行任何修改，而只是將數(shù)據(jù)內(nèi)容復(fù)制或者移動到相應(yīng)的 HDFS 目錄中。而在數(shù)據(jù)庫中，不同的數(shù)據(jù)庫有不同的存儲引擎，定義了自己的數(shù)據(jù)格式。所有數(shù)

10、據(jù)都會按照一定的組織存儲，因此，數(shù)據(jù)庫加載數(shù)據(jù)的過程會比較耗時。 4. 數(shù)據(jù)更新。由于 Hive 是針對數(shù)據(jù)倉庫應(yīng)用設(shè)計的，而數(shù)據(jù)倉庫的內(nèi)容是讀多寫少的。因此，Hive 中不支持對數(shù)據(jù)的改寫和添加，所有的數(shù)據(jù)都是在加載的時候中確定好的。而數(shù)據(jù)庫中的數(shù)據(jù)通常是需要經(jīng)常進(jìn)行修改的，因此可以使用 INSERT INTO . VALUES 添加數(shù)據(jù)，使用 UPDATE . SET 修改數(shù)據(jù)。 5. 索引。之前已經(jīng)說過，Hive 在加載數(shù)據(jù)的過程中不會對數(shù)據(jù)進(jìn)行任何處理，甚至不會對數(shù)據(jù)進(jìn)行掃描，因此也沒有對數(shù)據(jù)中的某些 Key 建立索引。Hive 要訪問數(shù)據(jù)中滿足條件的特定值時，需要暴力掃描整個數(shù)據(jù)，因

11、此訪問延遲較高。由于 MapReduce 的引入， Hive 可以并行訪問數(shù)據(jù)，因此即使沒有索引，對于大數(shù)據(jù)量的訪問，Hive 仍然可以體現(xiàn)出優(yōu)勢。數(shù)據(jù)庫中，通常會針對一個或者幾個列建立索引，因此對于少量的特定條件的數(shù)據(jù)的訪問，數(shù)據(jù)庫可以有很高的效率，較低的延遲。由于數(shù)據(jù)的訪問延遲較高，決定了 Hive 不適合在線數(shù)據(jù)查詢。 6. 執(zhí)行。Hive 中大多數(shù)查詢的執(zhí)行是通過 Hadoop 提供的 MapReduce 來實現(xiàn)的，而數(shù)據(jù)庫通常有自己的執(zhí)行引擎。 7. 執(zhí)行延遲。之前提到，Hive 在查詢數(shù)據(jù)的時候，由于沒有索引，需要掃描整個表，因此延遲較高。另外一個導(dǎo)致 Hive 執(zhí)行延遲高的因素是

12、 MapReduce 框架。由于 MapReduce 本身具有較高的延遲，因此在利用 MapReduce 執(zhí)行 Hive 查詢時，也會有較高的延遲。相對的，數(shù)據(jù)庫的執(zhí)行延遲較低。當(dāng)然，這個低是有條件的，即數(shù)據(jù)規(guī)模較小，當(dāng)數(shù)據(jù)規(guī)模大到超過數(shù)據(jù)庫的處理能力的時候，Hive 的并行計算顯然能體現(xiàn)出優(yōu)勢。 8. 可擴展性。由于 Hive 是建立在 Hadoop 之上的，因此 Hive 的可擴展性是和 Hadoop 的可擴展性是一致的（世界上最大的 Hadoop 集群在 Yahoo!，2009年的規(guī)模在 4000 臺節(jié)點左右）。而數(shù)據(jù)庫由于 ACID 語義的嚴(yán)格限制，擴展行非常有限。目前最先進(jìn)的并行數(shù)據(jù)

13、庫 Oracle 在理論上的擴展能力也只有 100 臺左右。 9. 數(shù)據(jù)規(guī)模。由于 Hive 建立在集群上并可以利用 MapReduce 進(jìn)行并行計算，因此可以支持很大規(guī)模的數(shù)據(jù)；對應(yīng)的，數(shù)據(jù)庫可以支持的數(shù)據(jù)規(guī)模較小?？偨Y(jié)：hive具有sql數(shù)據(jù)庫的外表，但應(yīng)用場景完全不同，hive只適合用來做批量數(shù)據(jù)統(tǒng)計分析1.5 Hive的數(shù)據(jù)存儲1、Hive中所有的數(shù)據(jù)都存儲在 HDFS 中，沒有專門的數(shù)據(jù)存儲格式（可支持Text，SequenceFile，ParquetFile，RCFILE等）2、只需要在創(chuàng)建表的時候告訴 Hive 數(shù)據(jù)中的列分隔符和行分隔符，Hive 就可以解析數(shù)據(jù)。3、Hive

14、中包含以下數(shù)據(jù)模型：DB、Table，External Table，Partition，Bucket。 db：在hdfs中表現(xiàn)為$hive.metastore.warehouse.dir目錄下一個文件夾 table：在hdfs中表現(xiàn)所屬db目錄下一個文件夾 external table：與table類似，不過其數(shù)據(jù)存放位置可以在任意指定路徑 partition：在hdfs中表現(xiàn)為table目錄下的子目錄 bucket：在hdfs中表現(xiàn)為同一個表目錄下根據(jù)hash散列之后的多個文件1.6 HIVE的安裝部署1.6.1 安裝單機版：元數(shù)據(jù)庫mysql版：1.6.2 使用方式Hive交互shellb

15、in/hiveHive thrift服務(wù)啟動方式，（假如是在hadoop01上）：啟動為前臺：bin/hiveserver2啟動為后臺：nohup bin/hiveserver2 1/var/log/hiveserver.log 2/var/log/hiveserver.err &啟動成功后，可以在別的節(jié)點上用beeline去連接v 方式（1）hive/bin/beeline 回車，進(jìn)入beeline的命令界面輸入命令連接hiveserver2beeline !connect jdbc:hive2/mini1:10000（hadoop01是hiveserver2所啟動的那臺主機名，端口默認(rèn)是1

16、0000）v 方式（2）或者啟動就連接：bin/beeline -u jdbc:hive2:/mini1:10000 -n hadoop接下來就可以做正常sql查詢了Hive命令hadoophdp-node-02 $ hive -e sql2. Hive基本操作2.1 DDL操作2.1.1 創(chuàng)建表建表語法CREATE EXTERNAL TABLE IF NOT EXISTS table_name (col_name data_type COMMENT col_comment, .) COMMENT table_comment PARTITIONED BY (col_name data_type

17、 COMMENT col_comment, .) CLUSTERED BY (col_name, col_name, .) SORTED BY (col_name ASC|DESC, .) INTO num_buckets BUCKETS ROW FORMAT row_format STORED AS file_format LOCATION hdfs_path說明：1、 CREATE TABLE 創(chuàng)建一個指定名字的表。如果相同名字的表已經(jīng)存在，則拋出異常；用戶可以用 IF NOT EXISTS 選項來忽略這個異常。2、 EXTERNAL關(guān)鍵字可以讓用戶創(chuàng)建一個外部表，在建表的同時指定一個指向

18、實際數(shù)據(jù)的路徑（LOCATION），Hive 創(chuàng)建內(nèi)部表時，會將數(shù)據(jù)移動到數(shù)據(jù)倉庫指向的路徑；若創(chuàng)建外部表，僅記錄數(shù)據(jù)所在的路徑，不對數(shù)據(jù)的位置做任何改變。在刪除表的時候，內(nèi)部表的元數(shù)據(jù)和數(shù)據(jù)會被一起刪除，而外部表只刪除元數(shù)據(jù)，不刪除數(shù)據(jù)。3、 LIKE 允許用戶復(fù)制現(xiàn)有的表結(jié)構(gòu)，但是不復(fù)制數(shù)據(jù)。4、 ROW FORMAT DELIMITED FIELDS TERMINATED BY char COLLECTION ITEMS TERMINATED BY char MAP KEYS TERMINATED BY char LINES TERMINATED BY char | SERDE serd

19、e_name WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, .)用戶在建表的時候可以自定義 SerDe 或者使用自帶的 SerDe。如果沒有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，將會使用自帶的 SerDe。在建表的時候，用戶還需要為表指定列，用戶在指定表的列的同時也會指定自定義的 SerDe，Hive通過 SerDe 確定表的具體的列的數(shù)據(jù)。5、 STORED AS SEQUENCEFILE|TEXTFILE|RCFILE如果文件數(shù)據(jù)是純文

20、本，可以使用 STORED AS TEXTFILE。如果數(shù)據(jù)需要壓縮，使用 STORED AS SEQUENCEFILE。6、CLUSTERED BY對于每一個表（table）或者分區(qū)， Hive可以進(jìn)一步組織成桶，也就是說桶是更為細(xì)粒度的數(shù)據(jù)范圍劃分。Hive也是 針對某一列進(jìn)行桶的組織。Hive采用對列值哈希，然后除以桶的個數(shù)求余的方式?jīng)Q定該條記錄存放在哪個桶當(dāng)中。 把表（或者分區(qū)）組織成桶（Bucket）有兩個理由：（1）獲得更高的查詢處理效率。桶為表加上了額外的結(jié)構(gòu)，Hive 在處理有些查詢時能利用這個結(jié)構(gòu)。具體而言，連接兩個在（包含連接列的）相同列上劃分了桶的表，可以使用 Map 端

21、連接 （Map-side join）高效的實現(xiàn)。比如JOIN操作。對于JOIN操作兩個表有一個相同的列，如果對這兩個表都進(jìn)行了桶操作。那么將保存相同列值的桶進(jìn)行JOIN操作就可以，可以大大較少JOIN的數(shù)據(jù)量。（2）使取樣（sampling）更高效。在處理大規(guī)模數(shù)據(jù)集時，在開發(fā)和修改查詢的階段，如果能在數(shù)據(jù)集的一小部分?jǐn)?shù)據(jù)上試運行查詢，會帶來很多方便。具體實例1、 創(chuàng)建內(nèi)部表mytable。2、 創(chuàng)建外部表pageview。3、 創(chuàng)建分區(qū)表invites。create table student_p(Sno int,Sname string,Sex string,Sage int,Sdept

22、string) partitioned by(part string) row format delimited fields terminated by ,stored as textfile;4、 創(chuàng)建帶桶的表student。2.1.2 修改表增加/刪除分區(qū) 語法結(jié)構(gòu)ALTER TABLE table_name ADD IF NOT EXISTS partition_spec LOCATION location1 partition_spec LOCATION location2 .partition_spec: PARTITION (partition_col = partition_c

23、ol_value, partition_col = partiton_col_value, .)ALTER TABLE table_name DROP partition_spec, partition_spec,. 具體實例alter table student_p add partition(part=a) partition(part=b);重命名表 語法結(jié)構(gòu)ALTER TABLE table_name RENAME TO new_table_name 具體實例增加/更新列 語法結(jié)構(gòu)ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name

24、data_type COMMENT col_comment, .) 注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE則是表示替換表中所有字段。ALTER TABLE table_name CHANGE COLUMN col_old_name col_new_name column_type COMMENT col_comment FIRST|AFTER column_name 具體實例2.1.3 顯示命令show tablesshow databasesshow partitionsshow functionsdesc extended t_name;

25、desc formatted table_name;2.2 DML操作2.2.1 Loadl 語法結(jié)構(gòu)LOAD DATA LOCAL INPATH filepath OVERWRITE INTO TABLE tablename PARTITION (partcol1=val1, partcol2=val2 .)說明：1、 Load 操作只是單純的復(fù)制/移動操作，將數(shù)據(jù)文件移動到 Hive 表對應(yīng)的位置。2、 filepath：相對路徑，例如：project/data1 絕對路徑，例如：/user/hive/project/data1 包含模式的完整 URI，列如：hdfs:/namenode:

26、9000/user/hive/project/data13、 LOCAL關(guān)鍵字如果指定了 LOCAL， load 命令會去查找本地文件系統(tǒng)中的 filepath。如果沒有指定 LOCAL 關(guān)鍵字，則根據(jù)inpath中的uri如果指定了 LOCAL，那么： load 命令會去查找本地文件系統(tǒng)中的 filepath。如果發(fā)現(xiàn)是相對路徑，則路徑會被解釋為相對于當(dāng)前用戶的當(dāng)前路徑。 load 命令會將 filepath中的文件復(fù)制到目標(biāo)文件系統(tǒng)中。目標(biāo)文件系統(tǒng)由表的位置屬性決定。被復(fù)制的數(shù)據(jù)文件移動到表的數(shù)據(jù)對應(yīng)的位置。 如果沒有指定 LOCAL 關(guān)鍵字，如果 filepath 指向的是一個完整的 U

27、RI，hive 會直接使用這個 URI。 否則：如果沒有指定 schema 或者 authority，Hive 會使用在 hadoop 配置文件中定義的 schema 和 authority， 指定了 Namenode 的 URI。 如果路徑不是絕對的，Hive 相對于/user/進(jìn)行解釋。 Hive 會將 filepath 中指定的文件內(nèi)容移動到 table （或者 partition）所指定的路徑中。查找文件4、 OVERWRITE 關(guān)鍵字如果使用了 OVERWRITE 關(guān)鍵字，則目標(biāo)表（或者分區(qū)）中的內(nèi)容會被刪除，然后再將 filepath 指向的文件/目錄

28、中的內(nèi)容添加到表/分區(qū)中。 如果目標(biāo)表（分區(qū)）已經(jīng)有一個文件，并且文件名和 filepath 中的文件名沖突，那么現(xiàn)有的文件會被新文件所替代。 l 具體實例1、 加載相對路徑數(shù)據(jù)。2、 加載絕對路徑數(shù)據(jù)。3、 加載包含模式數(shù)據(jù)。4、 OVERWRITE關(guān)鍵字使用。2.2.2 Insertl 將查詢結(jié)果插入Hive表 語法結(jié)構(gòu)INSERT OVERWRITE TABLE tablename1 PARTITION (partcol1=val1, partcol2=val2 .) select_statement1 FROM from_statementMultiple inserts:FROM f

29、rom_statement INSERT OVERWRITE TABLE tablename1 PARTITION (partcol1=val1, partcol2=val2 .) select_statement1 INSERT OVERWRITE TABLE tablename2 PARTITION . select_statement2 .Dynamic partition inserts:INSERT OVERWRITE TABLE tablename PARTITION (partcol1=val1, partcol2=val2 .) select_statement FROM fr

30、om_statement 具體實例1、基本模式插入。2、多插入模式。3、自動分區(qū)模式。v 導(dǎo)出表數(shù)據(jù) 語法結(jié)構(gòu)INSERT OVERWRITE LOCAL DIRECTORY directory1 SELECT . FROM .multiple inserts:FROM from_statementINSERT OVERWRITE LOCAL DIRECTORY directory1 select_statement1INSERT OVERWRITE LOCAL DIRECTORY directory2 select_statement2 . 具體實例1、導(dǎo)出文件到本地。說明：數(shù)據(jù)寫入到文件系

31、統(tǒng)時進(jìn)行文本序列化，且每列用A來區(qū)分，n為換行符。用more命令查看時不容易看出分割符，可以使用: sed -e s/x01/|/g filename來查看。2、導(dǎo)出數(shù)據(jù)到HDFS。2.2.3 SELECTl 基本的Select操作 語法結(jié)構(gòu)SELECT ALL | DISTINCT select_expr, select_expr, . FROM table_referenceWHERE where_condition GROUP BY col_list HAVING condition CLUSTER BY col_list | DISTRIBUTE BY col_list SORT BY

32、| ORDER BY col_list LIMIT number注：1、order by 會對輸入做全局排序，因此只有一個reducer，會導(dǎo)致當(dāng)輸入規(guī)模較大時，需要較長的計算時間。2、sort by不是全局排序，其在數(shù)據(jù)進(jìn)入reducer前完成排序。因此，如果用sort by進(jìn)行排序，并且設(shè)置mapred.reduce.tasks1，則sort by只保證每個reducer的輸出有序，不保證全局有序。3、distribute by根據(jù)distribute by指定的內(nèi)容將數(shù)據(jù)分到同一個reducer。4、Cluster by 除了具有Distribute by的功能外，還會對該字段進(jìn)行排序。

33、因此，常常認(rèn)為cluster by = distribute by + sort by 具體實例1、獲取年齡大的3個學(xué)生。2、查詢學(xué)生信息按年齡，降序排序。3、按學(xué)生名稱匯總學(xué)生年齡。2.3 Hive Joinl 語法結(jié)構(gòu)join_table: table_reference JOIN table_factor join_condition | table_reference LEFT|RIGHT|FULL OUTER JOIN table_reference join_condition | table_reference LEFT SEMI JOIN table_reference joi

34、n_conditionHive 支持等值連接（equality joins）、外連接（outer joins）和（left/right joins）。Hive 不支持非等值的連接，因為非等值連接非常難轉(zhuǎn)化到 map/reduce 任務(wù)。另外，Hive 支持多于 2 個表的連接。寫 join 查詢時，需要注意幾個關(guān)鍵點：1. 只支持等值join例如： SELECT a.* FROM a JOIN b ON (a.id = b.id) SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)是正確的，然而:

35、SELECT a.* FROM a JOIN b ON (a.idb.id)是錯誤的。2. 可以 join 多于 2 個表。例如 SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)如果join中多個表的 join key 是同一個，則 join 會被轉(zhuǎn)化為單個 map/reduce 任務(wù)，例如： SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)被轉(zhuǎn)

36、化為單個 map/reduce 任務(wù)，因為 join 中只使用了 b.key1 作為 join key。SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)而這一 join 被轉(zhuǎn)化為 2 個 map/reduce 任務(wù)。因為 b.key1 用于第一次 join 條件，而 b.key2 用于第二次 join。 3join 時，每次 map/reduce 任務(wù)的邏輯： reducer 會緩存 join 序列中除了最后一個表的所有表的記錄，再通過最后一個表將結(jié)果序列化到文件系統(tǒng)。

37、這一實現(xiàn)有助于在 reduce 端減少內(nèi)存的使用量。實踐中，應(yīng)該把最大的那個表寫在最后（否則會因為緩存浪費大量內(nèi)存）。例如： SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)所有表都使用同一個 join key（使用 1 次 map/reduce 任務(wù)計算）。Reduce 端會緩存 a 表和 b 表的記錄，然后每次取得一個 c 表的記錄就計算一次 join 結(jié)果，類似的還有： SELECT a.val, b.val, c.val FROM a JOIN b ON (a.

38、key = b.key1) JOIN c ON (c.key = b.key2)這里用了 2 次 map/reduce 任務(wù)。第一次緩存 a 表，用 b 表序列化；第二次緩存第一次 map/reduce 任務(wù)的結(jié)果，然后用 c 表序列化。4LEFT，RIGHT 和 FULL OUTER 關(guān)鍵字用于處理 join 中空記錄的情況例如： SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)對應(yīng)所有 a 表中的記錄都有一條記錄輸出。輸出的結(jié)果應(yīng)該是 a.val, b.val，當(dāng) a.key=b.key 時，而當(dāng) b.key 中找不

39、到等值的 a.key 記錄時也會輸出:a.val, NULL所以 a 表中的所有記錄都被保留了；“a RIGHT OUTER JOIN b”會保留所有 b 表的記錄。Join 發(fā)生在 WHERE 子句之前。如果你想限制 join 的輸出，應(yīng)該在 WHERE 子句中寫過濾條件或是在 join 子句中寫。這里面一個容易混淆的問題是表分區(qū)的情況： 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 JOI

40、N），列出 a.val 和 b.val 的記錄。WHERE 從句中可以使用其他列作為過濾條件。但是，如前所述，如果 b 表中找不到對應(yīng) a 表的記錄，b 表的所有列都會列出 NULL，包括 ds 列。也就是說，join 會過濾 b 表中不能找到匹配 a 表 join key 的所有記錄。這樣的話，LEFT OUTER 就使得查詢結(jié)果與 WHERE 子句無關(guān)了。解決的辦法是在 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=2

41、009-07-07)這一查詢的結(jié)果是預(yù)先在 join 階段過濾過的，所以不會存在上述問題。這一邏輯也可以應(yīng)用于 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 中不匹配的記錄，然后用這一中間結(jié)果和 c 表做 join。這一表述有一個不太明

42、顯的問題，就是當(dāng)一個 key 在 a 表和 c 表都存在，但是 b 表中不存在的時候：整個記錄在第一次 join，即 a JOIN b 的時候都被丟掉了（包括a.val1，a.val2和a.key），然后我們再和 c 表 join 的時候，如果 c.key 與 a.key 或 b.key 相等，就會得到這樣的結(jié)果：NULL, NULL, NULL, c.vall 具體實例1、 獲取已經(jīng)分配班級的學(xué)生姓名。2、 獲取尚未分配班級的學(xué)生姓名。3、 LEFT SEMI JOIN是IN/EXISTS的高效實現(xiàn)。3 Hive Shell參數(shù)3.1 Hive命令行l(wèi) 語法結(jié)構(gòu)hive -hiveconf

43、x=y* * | -S說明：1、 -i從文件初始化HQL。2、 -e從命令行執(zhí)行指定的HQL 3、 -f 執(zhí)行HQL腳本 4、 -v 輸出執(zhí)行的HQL語句到控制臺 5、 -p connect to Hive Server on port number 6、 -hiveconf x=y Use this to set hive/hadoop configuration variables.l 具體實例1、運行一個查詢。2、運行一個文件。3、運行參數(shù)文件。3.2 Hive參數(shù)配置方式Hive參數(shù)大全：/confluence/display/Hive/C

44、onfiguration+Properties開發(fā)Hive應(yīng)用時，不可避免地需要設(shè)定Hive的參數(shù)。設(shè)定Hive的參數(shù)可以調(diào)優(yōu)HQL代碼的執(zhí)行效率，或幫助定位問題。然而實踐中經(jīng)常遇到的一個問題是，為什么設(shè)定的參數(shù)沒有起作用？這通常是錯誤的設(shè)定方式導(dǎo)致的。對于一般參數(shù)，有以下三種設(shè)定方式：l 配置文件 l 命令行參數(shù) l 參數(shù)聲明 配置文件：Hive的配置文件包括l 用戶自定義配置文件：$HIVE_CONF_DIR/hive-site.xml l 默認(rèn)配置文件：$HIVE_CONF_DIR/hive-default.xml 用戶自定義配置會覆蓋默認(rèn)配置。另外，Hive也會讀入Hadoop的配置，

45、因為Hive是作為Hadoop的客戶端啟動的，Hive的配置會覆蓋Hadoop的配置。配置文件的設(shè)定對本機啟動的所有Hive進(jìn)程都有效。命令行參數(shù)：啟動Hive（客戶端或Server方式）時，可以在命令行添加-hiveconf param=value來設(shè)定參數(shù)，例如：bin/hive -hiveconf hive.root.logger=INFO,console這一設(shè)定對本次啟動的Session（對于Server方式啟動，則是所有請求的Sessions）有效。參數(shù)聲明：可以在HQL中使用SET關(guān)鍵字設(shè)定參數(shù)，例如：set mapred.reduce.tasks=100;這一設(shè)定的作用域也是se

46、ssion級的。上述三種設(shè)定方式的優(yōu)先級依次遞增。即參數(shù)聲明覆蓋命令行參數(shù)，命令行參數(shù)覆蓋配置文件設(shè)定。注意某些系統(tǒng)級的參數(shù)，例如log4j相關(guān)的設(shè)定，必須用前兩種方式設(shè)定，因為那些參數(shù)的讀取在Session建立以前已經(jīng)完成了。4. Hive函數(shù)4.1 內(nèi)置運算符內(nèi)容較多，見Hive官方文檔4.2 內(nèi)置函數(shù)內(nèi)容較多，見Hive官方文檔4.3 Hive自定義函數(shù)和Transform當(dāng)Hive提供的內(nèi)置函數(shù)無法滿足你的業(yè)務(wù)處理需要時，此時就可以考慮使用用戶自定義函數(shù)（UDF：user-defined function）。4.3.1 自定義函數(shù)類別UDF 作用于單個數(shù)據(jù)行，產(chǎn)生一個數(shù)據(jù)行作為輸出。（

47、數(shù)學(xué)函數(shù)，字符串函數(shù)）UDAF（用戶定義聚集函數(shù)）：接收多個輸入數(shù)據(jù)行，并產(chǎn)生一個輸出數(shù)據(jù)行。（count，max）4.3.2 UDF開發(fā)實例1、先開發(fā)一個java類，繼承UDF，并重載evaluate方法package cn.itcast.bigdata.udfimport org.apache.hadoop.hive.ql.exec.UDF;import org.apache.hadoop.io.Text;public final class Lower extends UDFpublic Text evaluate(final Text s)if(s=null)return null;r

48、eturn new Text(s.toString().toLowerCase();2、打成jar包上傳到服務(wù)器3、將jar包添加到hive的classpathhiveadd JAR /home/hadoop/udf.jar;4、 創(chuàng)建臨時函數(shù)與開發(fā)好的java class關(guān)聯(lián)Hivecreate temporary function toprovince as cn.itcast.bigdata.udf.ToProvince;5、 即可在hql中使用自定義的函數(shù)stripSelect strip(name),age from t_test;4.3.3 Transform實現(xiàn)Hive的 TRA

49、NSFORM 關(guān)鍵字提供了在SQL中調(diào)用自寫腳本的功能適合實現(xiàn)Hive中沒有的功能又不想寫UDF的情況使用示例1：下面這句sql就是借用了weekday_mapper.py對數(shù)據(jù)進(jìn)行了處理.CREATE TABLE u_data_new ( movieid INT, rating INT, weekday INT, userid INT)ROW FORMAT DELIMITEDFIELDS TERMINATED BY t;add FILE weekday_mapper.py;INSERT OVERWRITE TABLE u_data_newSELECT TRANSFORM (movieid,

50、rating, unixtime,userid) USING python weekday_mapper.py AS (movieid, rating, weekday,userid)FROM u_data;其中weekday_mapper.py內(nèi)容如下#!/bin/pythonimport sysimport datetimefor line in sys.stdin: line = line.strip() movieid, rating, unixtime,userid = line.split(t) weekday = datetime.datetime.fromtimestamp(f

51、loat(unixtime).isoweekday() print t.join(movieid, rating, str(weekday),userid)使用示例2：下面的例子則是使用了shell的cat命令來處理數(shù)據(jù)FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING /bin/cat WHERE a.ds 2008-08-09;5. Hive實戰(zhàn)Hive 實戰(zhàn)案例1數(shù)據(jù)ETL需求： 對web點擊流日志基礎(chǔ)數(shù)據(jù)表進(jìn)行etl（按照倉庫模型設(shè)計） 按各時間維

52、度統(tǒng)計來源域名top10已有數(shù)據(jù)表 “t_orgin_weblog” ：+-+-+-+-+| col_name | data_type | comment |+-+-+-+-+| valid | string | | remote_addr | string | | remote_user | string | | time_local | string | | request | string | | status | string | | body_bytes_sent | string | | http_referer | string | | http_user_agent | string | |+-+-+-+-