本文轉(zhuǎn)載自：http://blog.csdn.net/haojun186/article/details/7977565

1.??HIVE結(jié)構(gòu)

Hive 是建立在 Hadoop 上的數(shù)據(jù)倉庫基礎(chǔ)構(gòu)架。它提供了一系列的工具，可以用來進(jìn)行數(shù)據(jù)提取轉(zhuǎn)化加載（ETL），這是一種可以存儲、查詢和分析存儲在 Hadoop 中的大規(guī)模數(shù)據(jù)的機制。Hive 定義了簡單的類 SQL 查詢語言，稱為 QL，它允許熟悉 SQL 的用戶查詢數(shù)據(jù)。同時，這個語言也允許熟悉 MapReduce 開發(fā)者的開發(fā)自定義的 mapper 和 reducer 來處理內(nèi)建的 mapper 和 reducer 無法完成的復(fù)雜的分析工作。

1.1?HIVE架構(gòu)

?

Hive 的結(jié)構(gòu)可以分為以下幾部分：

·????????用戶接口：包括 CLI, Client, WUI

·????????元數(shù)據(jù)存儲。通常是存儲在關(guān)系數(shù)據(jù)庫如 mysql, derby 中

·????????解釋器、編譯器、優(yōu)化器、執(zhí)行器

·????????Hadoop：用 HDFS 進(jìn)行存儲，利用MapReduce 進(jìn)行計算

1、 用戶接口主要有三個：CLI，Client和 WUI。其中最常用的是 CLI，Cli 啟動的時候，會同時啟動一個 Hive 副本。Client 是 Hive 的客戶端，用戶連接至 Hive Server。在啟動 Client 模式的時候，需要指出 Hive Server 所在節(jié)點，并且在該節(jié)點啟動 Hive Server。 WUI 是通過瀏覽器訪問 Hive。

2、 Hive 將元數(shù)據(jù)存儲在數(shù)據(jù)庫中，如 mysql、derby。Hive 中的元數(shù)據(jù)包括表的名字，表的列和分區(qū)及其屬性，表的屬性（是否為外部表等），表的數(shù)據(jù)所在目錄等。

3、 解釋器、編譯器、優(yōu)化器完成 HQL 查詢語句從詞法分析、語法分析、編譯、優(yōu)化以及查詢計劃的生成。生成的查詢計劃存儲在 HDFS 中，并在隨后有 MapReduce 調(diào)用執(zhí)行。

4、Hive 的數(shù)據(jù)存儲在 HDFS 中，大部分的查詢由 MapReduce 完成（包含 * 的查詢，比如 select * from tbl 不會生成 MapRedcue 任務(wù)）。

1.2?Hive?和Hadoop 關(guān)系

?

Hive構(gòu)建在 Hadoop 之上，

·????????HQL 中對查詢語句的解釋、優(yōu)化、生成查詢計劃是由 Hive 完成的

·????????所有的數(shù)據(jù)都是存儲在 Hadoop 中

·????????查詢計劃被轉(zhuǎn)化為 MapReduce 任務(wù)，在 Hadoop 中執(zhí)行（有些查詢沒有 MR 任務(wù)，如：select * from table）

·????????Hadoop和Hive都是用UTF-8編碼的

?

1.3?Hive?和普通關(guān)系數(shù)據(jù)庫的異同

?	Hive	RDBMS
查詢語言	HQL	SQL
數(shù)據(jù)存儲	HDFS	Raw Device or Local FS
索引	無	有
執(zhí)行	MapReduce	Excutor
執(zhí)行延遲	高	低
處理數(shù)據(jù)規(guī)模	大	小

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ù)庫則可以將數(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ù)據(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ù)，因此訪問延遲較高。由于 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)的（類似 select * from tbl 的查詢不需要 MapReduce）。而數(shù)據(jù)庫通常有自己的執(zhí)行引擎。

7.? 執(zhí)行延遲。之前提到，Hive 在查詢數(shù)據(jù)的時候，由于沒有索引，需要掃描整個表，因此延遲較高。另外一個導(dǎo)致 Hive 執(zhí)行延遲高的因素是 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ù)庫 Oracle 在理論上的擴展能力也只有 100 臺左右。

9.?數(shù)據(jù)規(guī)模。由于 Hive 建立在集群上并可以利用 MapReduce 進(jìn)行并行計算，因此可以支持很大規(guī)模的數(shù)據(jù)；對應(yīng)的，數(shù)據(jù)庫可以支持的數(shù)據(jù)規(guī)模較小。

1.4?HIVE元數(shù)據(jù)庫

Hive 將元數(shù)據(jù)存儲在 RDBMS 中,一般常用的有MYSQL和DERBY。

1.4.1?????DERBY

啟動HIVE的元數(shù)據(jù)庫

進(jìn)入到hive的安裝目錄

Eg:

1、啟動derby數(shù)據(jù)庫

/home/admin/caona/hive/build/dist/

運行startNetworkServer -h 0.0.0.0

?

2、連接Derby數(shù)據(jù)庫進(jìn)行測試

查看/home/admin/caona/hive/build/dist/conf/hive-default.xml。

找到<property>

???<name>javax.jdo.option.ConnectionURL</name>

???<value>jdbc:derby://hadoop1:1527/metastore_db;create=true</value>

???<description>JDBC connect string for a JDBCmetastore</description>

? </property>

進(jìn)入derby安裝目錄

/home/admin/caona/hive/build/dist/db-derby-10.4.1.3-bin/bin

輸入./ij

Connect'jdbc:derby://hadoop1:1527/metastore_db;create=true';

?

3、元數(shù)據(jù)庫數(shù)據(jù)字典

表名	說明	關(guān)聯(lián)鍵
BUCKETING_COLS????????	?	?
????COLUMNS??????? ????	Hive表字段信息(字段注釋，字段名，字段類型，字段序號)	SD_ID
DBS	元數(shù)據(jù)庫信息，存放HDFS路徑信息	DB_ID
PARTITION_KEYS????????	Hive分區(qū)表分區(qū)鍵	PART_ID
SDS???????????????????	所有hive表、表分區(qū)所對應(yīng)的hdfs數(shù)據(jù)目錄和數(shù)據(jù)格式。	SD_ID,SERDE_ID
SD_PARAMS?????????????	序列化反序列化信息，如行分隔符、列分隔符、NULL的表示字符等	SERDE_ID
SEQUENCE_TABLE????????	SEQUENCE_TABLE表保存了hive對象的下一個可用ID，如’org.apache.hadoop.hive.metastore.model.MTable’, 21，則下一個新創(chuàng)建的hive表其TBL_ID就是21，同時SEQUENCE_TABLE表中271786被更新為26(這里每次都是+5?)。同樣，COLUMN，PARTITION等都有相應(yīng)的記錄	?
SERDES????????????????	?	?
SERDE_PARAMS??????????	?	?
SORT_COLS?????????????	?	?
TABLE_PARAMS??????????	表級屬性，如是否外部表，表注釋等	TBL_ID
TBLS??????????????????	所有hive表的基本信息	TBL_ID,SD_ID

?

從上面幾張表的內(nèi)容來看，hive整個創(chuàng)建表的過程已經(jīng)比較清楚了

1. 解析用戶提交hive語句，對其進(jìn)行解析，分解為表、字段、分區(qū)等hive對象
2. 根據(jù)解析到的信息構(gòu)建對應(yīng)的表、字段、分區(qū)等對象，從SEQUENCE_TABLE中獲取構(gòu)建對象的最新ID，與構(gòu)建對象信息(名稱，類型等)一同通過DAO方法寫入到元數(shù)據(jù)表中去，成功后將SEQUENCE_TABLE中對應(yīng)的最新ID+5。

實際上我們常見的RDBMS都是通過這種方法進(jìn)行組織的，典型的如postgresql，其系統(tǒng)表中和hive元數(shù)據(jù)一樣裸露了這些id信息(oid,cid等),而Oracle等商業(yè)化的系統(tǒng)則隱藏了這些具體的ID。通過這些元數(shù)據(jù)我們可以很容易的讀到數(shù)據(jù)諸如創(chuàng)建一個表的數(shù)據(jù)字典信息，比如導(dǎo)出建表語名等。

導(dǎo)出建表語句的shell腳本見 附一 待完成

1.4.2?????Mysql

將存放元數(shù)據(jù)的Derby數(shù)據(jù)庫遷移到Mysql數(shù)據(jù)庫

步驟：

?

1.5?HIVE的數(shù)據(jù)存儲

??? 首先，Hive 沒有專門的數(shù)據(jù)存儲格式，也沒有為數(shù)據(jù)建立索引，用戶可以非常自由的組織 Hive 中的表，只需要在創(chuàng)建表的時候告訴 Hive 數(shù)據(jù)中的列分隔符和行分隔符，Hive 就可以解析數(shù)據(jù)。

其次，Hive 中所有的數(shù)據(jù)都存儲在 HDFS 中，Hive 中包含以下數(shù)據(jù)模型：Table，External Table，Partition，Bucket。

1. Hive 中的 Table 和數(shù)據(jù)庫中的 Table 在概念上是類似的，每一個 Table 在 Hive 中都有一個相應(yīng)的目錄存儲數(shù)據(jù)。例如，一個表 xiaojun，它在 HDFS 中的路徑為：/ warehouse /xiaojun，其中，wh 是在 hive-site.xml 中由 ${hive.metastore.warehouse.dir} 指定的數(shù)據(jù)倉庫的目錄，所有的 Table 數(shù)據(jù)（不包括 External Table）都保存在這個目錄中。
2. Partition 對應(yīng)于數(shù)據(jù)庫中的 Partition 列的密集索引，但是 Hive 中 Partition 的組織方式和數(shù)據(jù)庫中的很不相同。在 Hive 中，表中的一個 Partition 對應(yīng)于表下的一個目錄，所有的 Partition 的數(shù)據(jù)都存儲在對應(yīng)的目錄中。例如：xiaojun 表中包含 dt 和 city 兩個 Partition，則對應(yīng)于 dt = 20100801, ctry = US 的 HDFS 子目錄為：/ warehouse /xiaojun/dt=20100801/ctry=US；對應(yīng)于 dt = 20100801, ctry = CA 的 HDFS 子目錄為；/ warehouse /xiaojun/dt=20100801/ctry=CA
3. Buckets 對指定列計算 hash，根據(jù) hash 值切分?jǐn)?shù)據(jù)，目的是為了并行，每一個 Bucket 對應(yīng)一個文件。將 user 列分散至 32 個 bucket，首先對 user 列的值計算 hash，對應(yīng) hash 值為 0 的 HDFS 目錄為：/ warehouse /xiaojun/dt =20100801/ctry=US/part-00000；hash 值為 20 的 HDFS 目錄為：/ warehouse /xiaojun/dt =20100801/ctry=US/part-00020
4. External Table 指向已經(jīng)在 HDFS 中存在的數(shù)據(jù)，可以創(chuàng)建 Partition。它和 Table 在元數(shù)據(jù)的組織上是相同的，而實際數(shù)據(jù)的存儲則有較大的差異。

• Table 的創(chuàng)建過程和數(shù)據(jù)加載過程（這兩個過程可以在同一個語句中完成），在加載數(shù)據(jù)的過程中，實際數(shù)據(jù)會被移動到數(shù)據(jù)倉庫目錄中；之后對數(shù)據(jù)對訪問將會直接在數(shù)據(jù)倉庫目錄中完成。刪除表時，表中的數(shù)據(jù)和元數(shù)據(jù)將會被同時刪除。
• External Table 只有一個過程，加載數(shù)據(jù)和創(chuàng)建表同時完成（CREATE EXTERNAL TABLE ……LOCATION），實際數(shù)據(jù)是存儲在 LOCATION 后面指定的 HDFS 路徑中，并不會移動到數(shù)據(jù)倉庫目錄中。當(dāng)刪除一個 External Table 時，僅刪除

?

1.6?其它HIVE操作

?

1、 啟動HIVE的WEB的界面

sh $HIVE_HOME/bin/hive --service hwi

?

2、查看HDFS上的文件數(shù)據(jù)

hadoopfs -text /user/admin/daiqf/createspu_fp/input/cateinfo |head

?

?

2.??HIVE?基本操作

2.1?createtable

2.1.1??? 總述

l? CREATETABLE 創(chuàng)建一個指定名字的表。如果相同名字的表已經(jīng)存在，則拋出異常；用戶可以用 IF NOT EXIST 選項來忽略這個異常。

l? EXTERNAL關(guān)鍵字可以讓用戶創(chuàng)建一個外部表，在建表的同時指定一個指向?qū)嶋H數(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ù)。

l? LIKE 允許用戶復(fù)制現(xiàn)有的表結(jié)構(gòu)，但是不復(fù)制數(shù)據(jù)。

l? 用戶在建表的時候可以自定義 SerDe 或者使用自帶的 SerDe。如果沒有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，將會使用自帶的 SerDe。在建表的時候，用戶還需要為表指定列，用戶在指定表的列的同時也會指定自定義的SerDe，Hive 通過 SerDe 確定表的具體的列的數(shù)據(jù)。

l? 如果文件數(shù)據(jù)是純文本，可以使用 STORED AS TEXTFILE。如果數(shù)據(jù)需要壓縮，使用 STORED ASSEQUENCE 。

l? 有分區(qū)的表可以在創(chuàng)建的時候使用 PARTITIONED BY 語句。一個表可以擁有一個或者多個分區(qū)，每一個分區(qū)單獨存在一個目錄下。而且，表和分區(qū)都可以對某個列進(jìn)行 CLUSTERED BY 操作，將若干個列放入一個桶（bucket）中。也可以利用SORT BY 對數(shù)據(jù)進(jìn)行排序。這樣可以為特定應(yīng)用提高性能。

l? 表名和列名不區(qū)分大小寫，SerDe 和屬性名區(qū)分大小寫。表和列的注釋是字符串。

2.1.2??? 語法

?

目前在hive中常用的數(shù)據(jù)類型有:

?? BIGINT – 主要用于狀態(tài),類別,數(shù)量的字段, 如status/option/type/quantity

?? DOUBLE – 主要用于金額的字段, 如fee/price/bid

?? STRING – 除上述之外的字段基本都使用String, 尤其是id和日期時間這樣的字段

?

2.1.3?????基本例子

1、如果一個表已經(jīng)存在，可以使用if not exists

2、 create table xiaojun(id int,cont string) row format delimitedfields terminated by '\005' stored as textfile;

terminated by：關(guān)于來源的文本數(shù)據(jù)的字段間隔符

如果要將自定義間隔符的文件讀入一個表，需要通過創(chuàng)建表的語句來指明輸入文件間隔符，然后load data到這個表。

4、Alibaba數(shù)據(jù)庫常用間隔符的讀取

我們的常用間隔符一般是Ascii碼5，Ascii碼7等。在hive中Ascii碼5用’\005’表示， Ascii碼7用’\007’表示，依此類推。

5、裝載數(shù)據(jù)

查看一下：Hadoop fs -ls

LOAD DATA INPATH'/user/admin/xiaojun/a.txt' OVERWRITE INTO TABLE xiaojun;

6、如果使用external建表和普通建表區(qū)別

A、指定一個位置，而不使用默認(rèn)的位置。如：

create? EXTERNAL?table xiaojun(id int,cont string) row format delimited fields terminatedby '\005' stored as textfile location '/user/admin/xiaojun/';

--------------check結(jié)果

ij> selectLOCATION from tbls a,sds b where a.sd_id=b.sd_id and tbl_name='xiaojun';?

-----

LOCATION???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

--------------------------------------------------------------------------------------------------------------------------------

hdfs://hadoop1:7000/user/admin/xiaojun??

?

ij> selectLOCATION from tbls a,sds b where a.sd_id=b.sd_id and tbl_name='c';

----

LOCATION???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

--------------------------------------------------------------------------------------------------------------------------------

hdfs://hadoop1:7000/user/hive/warehouse/c

B、對于使用create table external建表完成后，再drop掉表，表中的數(shù)據(jù)還在文件系統(tǒng)中。

如：

hive>create? EXTERNAL? table xiaojun(id int,cont string) row formatdelimited fields terminated by '\005' stored as textfile;

----

OK

?

hive> LOADDATA INPATH '/user/admin/xiaojun' OVERWRITE INTO TABLE xiaojun;

--------------------------------------------------

Loading data totable xiaojun

OK

?

hive> droptable xiaojun;

----

OK

?

[admin@hadoop1bin]$ ./hadoop fs -ls hdfs://hadoop1:7000/user/hive/warehouse/xiaojun

Found 1 items

使用普通的建表DROP后則找不到

2.1.4??? 創(chuàng)建分區(qū)

?? HIVE的分區(qū)通過在創(chuàng)建表時啟用partitionby實現(xiàn)，用來partition的維度并不是實際數(shù)據(jù)的某一列，具體分區(qū)的標(biāo)志是由插入內(nèi)容時給定的。當(dāng)要查詢某一分區(qū)的內(nèi)容時可以采用where語句，形似where tablename.partition_key >a來實現(xiàn)。

創(chuàng)建含分區(qū)的表。

命令原型：

?

Eg:

建表：

CREATE TABLE c02_clickstat_fatdt1

(yyyymmdd? string,

?id????????????? INT,

?ip?????????????? string,

?country????????? string,

?cookie_id??????? string,

?page_id????????? string? ,?

?clickstat_url_id int,

?query_string???? string,

?refer??????????? string

)PARTITIONED BY(dt STRING)

row format delimited fields terminated by '\005' stored astextfile;

?

裝載數(shù)據(jù)：

LOAD DATA INPATH'/user/admin/SqlldrDat/CnClickstat/20101101/19/clickstat_gp_fatdt0/0' OVERWRITEINTO TABLE c02_clickstat_fatdt1

?PARTITION(dt='20101101');

?

訪問某一個分區(qū)

SELECT count(*)

??? FROMc02_clickstat_fatdt1 a

??? WHERE a.dt >='20101101' AND a.dt < '20101102';

2.1.5??? 其它例子

1、指定LOCATION位置

2、 復(fù)制一個空表

?

?

?

2.2?AlterTable

2.2.1??? AddPartitions

Eg:

ALTER TABLE c02_clickstat_fatdt1 ADD

PARTITION (dt='20101202') location'/user/hive/warehouse/c02_clickstat_fatdt1/part20101202'

PARTITION (dt='20101203') location'/user/hive/warehouse/c02_clickstat_fatdt1/part20101203';

?

2.2.2??? DropPartitions

ALTER TABLE table_name DROP partition_spec, partition_spec,...

?

ALTER TABLE c02_clickstat_fatdt1 DROP PARTITION(dt='20101202');

2.2.3??? RenameTable

ALTER TABLE table_name RENAME TO new_table_name

這個命令可以讓用戶為表更名。數(shù)據(jù)所在的位置和分區(qū)名并不改變。換而言之，老的表名并未“釋放”，對老表的更改會改變新表的數(shù)據(jù)。

2.2.4??? ChangeColumn

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_namecolumn_type [COMMENT col_comment] [FIRST|AFTER column_name]

這個命令可以允許改變列名、數(shù)據(jù)類型、注釋、列位置或者它們的任意組合

Eg:

?

2.2.5??? Add/ReplaceColumns

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type[COMMENT col_comment], ...)

?

ADD是代表新增一字段，字段位置在所有列后面(partition列前);REPLACE則是表示替換表中所有字段。

Eg:

hive> desc xi;

OK

id????? int

cont??? string

dw_ins_date???? string

Time taken: 0.061 seconds

hive> create table xibak like xi;????????????????????????

OK

Time taken: 0.157 seconds

hive> alter table xibak replace columns (ins_datestring);??

OK

Time taken: 0.109 seconds

hive> desc xibak;

OK

ins_date??????? string

2.3?CreateView

CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENTcolumn_comment], ...) ]

[COMMENT view_comment]

[TBLPROPERTIES (property_name = property_value, ...)]

AS SELECT ...

?

2.4?Show

查看表名

?

查看表名，部分匹配

?

查看某表的所有Partition，如果沒有就報錯：

?

查看某表結(jié)構(gòu)：

?

查看分區(qū)內(nèi)容

?

查看有限行內(nèi)容，同Greenplum，用limit關(guān)鍵詞

?

查看表分區(qū)定義

2.5?Load

?? HIVE裝載數(shù)據(jù)沒有做任何轉(zhuǎn)換加載到表中的數(shù)據(jù)只是進(jìn)入相應(yīng)的配置單元表的位置移動數(shù)據(jù)文件。純加載操作復(fù)制/移動操作。

?

3.1 語法

Load 操作只是單純的復(fù)制/移動操作，將數(shù)據(jù)文件移動到 Hive 表對應(yīng)的位置。

• filepath 可以是：
  • 相對路徑，例如：project/data1
  • 絕對路徑，例如： /user/hive/project/data1
  • 包含模式的完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1
• 加載的目標(biāo)可以是一個表或者分區(qū)。如果表包含分區(qū)，必須指定每一個分區(qū)的分區(qū)名。
• filepath 可以引用一個文件（這種情況下，Hive 會將文件移動到表所對應(yīng)的目錄中）或者是一個目錄（在這種情況下，Hive 會將目錄中的所有文件移動至表所對應(yīng)的目錄中）。
• 如果指定了 LOCAL，那么：
  • load 命令會去查找本地文件系統(tǒng)中的 filepath。如果發(fā)現(xiàn)是相對路徑，則路徑會被解釋為相對于當(dāng)前用戶的當(dāng)前路徑。用戶也可以為本地文件指定一個完整的 URI，比如：file:///user/hive/project/data1.
  • load 命令會將 filepath 中的文件復(fù)制到目標(biāo)文件系統(tǒng)中。目標(biāo)文件系統(tǒng)由表的位置屬性決定。被復(fù)制的數(shù)據(jù)文件移動到表的數(shù)據(jù)對應(yīng)的位置。
• 如果沒有指定 LOCAL 關(guān)鍵字，如果 filepath 指向的是一個完整的 URI，hive 會直接使用這個 URI。 否則：
  • 如果沒有指定 schema 或者 authority，Hive 會使用在 hadoop 配置文件中定義的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI。
  • 如果路徑不是絕對的，Hive 相對于 /user/ 進(jìn)行解釋。
  • Hive 會將 filepath 中指定的文件內(nèi)容移動到 table （或者 partition）所指定的路徑中。
• 如果使用了 OVERWRITE 關(guān)鍵字，則目標(biāo)表（或者分區(qū)）中的內(nèi)容（如果有）會被刪除，然后再將 filepath 指向的文件/目錄中的內(nèi)容添加到表/分區(qū)中。
• 如果目標(biāo)表（分區(qū)）已經(jīng)有一個文件，并且文件名和 filepath 中的文件名沖突，那么現(xiàn)有的文件會被新文件所替代。

?

從本地導(dǎo)入數(shù)據(jù)到表格并追加原表

?

從本地導(dǎo)入數(shù)據(jù)到表格并追加記錄

?

從hdfs導(dǎo)入數(shù)據(jù)到表格并覆蓋原表

關(guān)于來源的文本數(shù)據(jù)的字段間隔符

如果要將自定義間隔符的文件讀入一個表，需要通過創(chuàng)建表的語句來指明輸入文件間隔符，然后load data到這個表就ok了。

?

2.6?Insert

2.6.1???Inserting data into HiveTables from queries

?

?

Insert時，from子句既可以放在select子句后，也可以放在insert子句前，下面兩句是等價的

hive沒有直接插入一條數(shù)據(jù)的sql，不過可以通過其他方法實現(xiàn)：
假設(shè)有一張表B至少有一條數(shù)據(jù)，我們想向表A（int，string）中插入一條數(shù)據(jù)，可以用下面的方法實現(xiàn)：
from B
insert table A select??1，‘a(chǎn)bc’ limit 1；

?

我覺得hive好像不能夠插入一個記錄，因為每次你寫insert語句的時候都是要將整個表的值overwrite。我想這個應(yīng)該是與hive的storage layer是有關(guān)系的，因為它的存儲層是HDFS，插入一個數(shù)據(jù)要全表掃描，還不如用整個表的替換來的快些。

?

Hive不支持一條一條的用insert語句進(jìn)行插入操作，也不支持update的操作。數(shù)據(jù)是以load的方式，加載到建立好的表中。數(shù)據(jù)一旦導(dǎo)入，則不可修改。要么drop掉整個表，要么建立新的表，導(dǎo)入新的數(shù)據(jù)。

2.6.2???Writing data intofilesystem from queries

?

導(dǎo)出文件到本地

?

導(dǎo)出文件到HDFS

?

一個源可以同時插入到多個目標(biāo)表或目標(biāo)文件，多目標(biāo)insert可以用一句話來完成

Eg:

from xi?

insert overwrite?table test2 select? '1,2,3' limit1

insert overwrite?table d select? '4,5,6' limit 1;

2.7Cli [王黎3] ?

2.7.1??? HiveCommand line Options

$HIVE_HOME/bin/hive是一個shell工具，它可以用來運行于交互或批處理方式配置單元查詢。

語法：

?

運行一個查詢：

Example of setting hive configurationvariables

將查詢結(jié)果導(dǎo)出到一個文件

運行一個腳本

Example of running an initialization scriptbefore entering interactive mode

?

2.7.2??? Hiveinteractive Shell Command

Command	Description
quit	使用 quit or exit 退出
set <key>=<value>	使用這個方式來設(shè)置特定的配置變量的值。有一點需要注意的是，如果你拼錯了變量名，CLI將不會顯示錯誤。
set	這將打印的配置變量，如果沒有指定變量則由顯示HIVE和用戶變量。如set I 則顯示i的值,set則顯示hive內(nèi)部變量值
set -v	This will give all possible hadoop/hive configuration variables.
add FILE <value> <value>*	Adds a file to the list of resources.
list FILE	list all the resources already added
list FILE <value>*	Check given resources are already added or not.
! <cmd>	execute a shell command from hive shell
dfs <dfs command>	execute dfs command command from hive shell
<query string>	executes hive query and prints results to stdout

Eg:

?

還可以這樣用

hive> set $i='121.61.99.14.128160791368.5';

hive> select count(*) from c02_clickstat_fatdt1 wherecookie_id=$i;

11

2.7.3???Hive Resources

Hive can manage theaddition of resources to a session where those resources need to be made availableat query execution time. Any locally accessible file can be added to thesession. Once a file is added to a session, hive query can refer to this fileby its name (in? map/reduce/transformclauses [王黎5] ?) and this file is available locally at execution time on the entirehadoop cluster. Hive uses Hadoop's Distributed Cache to distribute the addedfiles to all the machines in the cluster at query execution time.

Usage:

• FILE resources are just added to the distributed cache. Typically, this might be something like a transform script to be executed.
• JAR resources are also added to the Java classpath. This is required in order to reference objects they contain such as UDF's.
• ARCHIVE resources are automatically unarchived as part of distributing them.

Example:

It is not neccessary to addfiles to the session if the files used in a transform script are alreadyavailable on all machines in the hadoop cluster using the same path name. Forexample:

• ... MAP a.networkid USING 'wc -l' ...: here wc is an executable available on all machines
• ... MAP a.networkid USING '/home/nfsserv1/hadoopscripts/tt.py' ...: here tt.py may be accessible via a nfs mount point that's configured identically on all the cluster nodes

[王黎6] ?

2.7.4??? 調(diào)用python、shell等語言

如下面這句sql就是借用了weekday_mapper.py對數(shù)據(jù)進(jìn)行了處理

，其中weekday_mapper.py內(nèi)容如下

?

如下面的例子則是使用了shell的cat命令來處理數(shù)據(jù)

[王黎8] ?

?

?

?

?

2.8?DROP

刪除一個內(nèi)部表的同時會同時刪除表的元數(shù)據(jù)和數(shù)據(jù)。刪除一個外部表，只刪除元數(shù)據(jù)而保留數(shù)據(jù)。

?

2.9?其它

2.9.1??? Limit

Limit可以限制查詢的記錄數(shù)。查詢的結(jié)果是隨機選擇的。下面的查詢語句從 t1 表中隨機查詢5條記錄：

SELECT* FROM t1 LIMIT 5

2.9.2??? Top k

下面的查詢語句查詢銷售記錄最大的 5 個銷售代表。

SETmapred.reduce.tasks = 1

? SELECT * FROM sales SORT BY amount DESC LIMIT5

?

2.9.3??? REGEX Column Specification

SELECT 語句可以使用正則表達(dá)式做列選擇，下面的語句查詢除了 ds 和 hr 之外的所有列：

SELECT `(ds|hr)?+.+`FROM sales

[王黎9] ?

3.??Hive Select

語法：

?

3.1?GroupBy

基本語法：

?

高級特性：

l? 聚合可進(jìn)一步分為多個表，甚至發(fā)送到Hadoop的DFS的文件（可以進(jìn)行操作，然后使用HDFS的utilitites）。例如我們可以根據(jù)性別劃分，需要找到獨特的頁面瀏覽量按年齡劃分。如下面的例子：

?

l? hive.map.aggr可以控制怎么進(jìn)行匯總。默認(rèn)為為true，配置單元會做的第一級聚合直接在MAP上的任務(wù)。這通常提供更好的效率，但可能需要更多的內(nèi)存來運行成功。

PS:在要特定的場合使用可能會加效率。不過我試了一下，比直接使用False慢很多。

3.2?Order/Sort By

Order by 語法：

?

Sort By 語法：

Sort順序?qū)⒏鶕?jù)列類型而定。如果數(shù)字類型的列，則排序順序也以數(shù)字順序。如果字符串類型的列，則排序順序?qū)⒆值漤樞颉?

?

4.??Hive Join

語法

Hive 只支持等值連接（equality joins）、外連接（outer joins）和（left/right joins）。Hive 不支持所有非等值的連接，因為非等值連接非常難轉(zhuǎn)化到 map/reduce 任務(wù)。另外，Hive 支持多于 2 個表的連接。

寫 join 查詢時，需要注意幾個關(guān)鍵點：

1、只支持等值join

例如：

? SELECT a.* FROMa JOIN b ON (a.id = b.id)

? SELECT a.* FROM a JOIN b

??? ON (a.id = b.id AND a.department =b.department)

是正確的，然而:

? SELECT a.* FROM a JOIN b ON (a.id? b.id)

是錯誤的。

?

1.?可以 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)化為單個 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。

??

3．join 時，每次map/reduce 任務(wù)的邏輯：

??? reducer 會緩存 join 序列中除了最后一個表的所有表的記錄，再通過最后一個表將結(jié)果序列化到文件系統(tǒng)。這一實現(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 FROMa

??? JOIN b ON (a.key = b.key1)JOIN c ON (c.key = b.key2)

這里用了 2 次 map/reduce 任務(wù)。第一次緩存 a 表，用 b 表 序列化 [王黎11] ?；第二次緩存第一次 map/reduce 任務(wù)的結(jié)果，然后用 c 表序列化。

[王黎12] ?

4．LEFT，RIGHT 和 FULLOUTER 關(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 中找不到等值的 a.key 記錄時也會輸出 a.val, NULL。“FROM a LEFT OUTER JOIN b”這句一定要寫在同一行——意思是 a 表在 b 表的 左邊 ，所以 a 表中的所有記錄都被保留了；“a RIGHT OUTER JOIN b”會保留所有 b 表的記錄。OUTER JOIN 語義應(yīng)該是遵循標(biāo)準(zhǔn) SQL spec的。

Join 發(fā)生在 WHERE 子句 之前 。如果你想限制 join 的輸出，應(yīng)該在 WHERE 子句中寫過濾條件——或是在join 子句中寫。這里面一個容易混淆的問題是表分區(qū)的情況：

? SELECT a.val,b.val FROM a

? LEFT OUTER JOINb ON (a.key=b.key)

? WHEREa.ds='2009-07-07' AND b.ds='2009-07-07'

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

? SELECT a.val,b.val FROM a LEFT OUTER JOIN b

? ON (a.key=b.keyAND

?????b.ds='2009-07-07' AND

?????a.ds='2009-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 JOINc ON (a.key = c.key)

先 join a 表到 b 表，丟棄掉所有 join key 中不匹配的記錄，然后用這一中間結(jié)果和 c 表做 join。這一表述有一個不太明顯的問題，就是當(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.val。

?

5． LEFT SEMI JOIN [王黎13] ? 是 IN/EXISTS 子查詢的一種更高效的實現(xiàn)。Hive 當(dāng)前沒有實現(xiàn) IN/EXISTS 子查詢，所以你可以用 LEFT SEMI JOIN 重寫你的子查詢語句。LEFT SEMI JOIN 的限制是， JOIN 子句中右邊的表只能在 ON 子句中設(shè)置過濾條件，在 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 LEFTSEMI JOIN b on (a.key = b.key)

?

5.??HIVE參數(shù)設(shè)置

??? 開發(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è)定方式：

• 配置文件
• 命令行參數(shù)
• 參數(shù)聲明

配置文件 ：Hive的配置文件包括

• 用戶自定義配置文件：$HIVE_CONF_DIR/hive-site.xml
• 默認(rèn)配置文件：$HIVE_CONF_DIR/hive-default.xml

用戶自定義配置會覆蓋默認(rèn)配置。另外，Hive也會讀入Hadoop的配置，因為Hive是作為Hadoop的客戶端啟動的，Hadoop的配置文件包括

• $HADOOP_CONF_DIR/hive-site.xml
• $HADOOP_CONF_DIR/hive-default.xml

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è)定的作用域也是Session級的。

上述三種設(shè)定方式的優(yōu)先級依次遞增。即參數(shù)聲明覆蓋命令行參數(shù)，命令行參數(shù)覆蓋配置文件設(shè)定。注意某些系統(tǒng)級的參數(shù)，例如log4j相關(guān)的設(shè)定，必須用前兩種方式設(shè)定，因為那些參數(shù)的讀取在Session建立以前已經(jīng)完成了。

另外， SerDe 參數(shù) [王黎14] ?必須寫在DDL（建表）語句中。例如：

create table if not exists t_dummy(

dummy???? string

)

ROW FORMAT SERDE'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'

WITH SERDEPROPERTIES (

'field.delim'='\t',

'escape.delim'='\\',

'serialization.null.format'=' '

) STORED AS TEXTFILE;

類似serialization.null.format這樣的參數(shù)，必須和某個表或分區(qū)關(guān)聯(lián)。在DDL外部聲明將不起作用。

?

6.??HIVE UDF

6.1?基本函數(shù)

6.1.1?????關(guān)系操作符

Operator	Operand types	Description
A = B	All primitive types	TRUE if expression A is equal to expression B otherwise FALSE
A == B	None!	Fails because of invalid syntax. SQL uses =, not ==
A <> B	All primitive types	NULL if A or B is NULL, TRUE if expression A is NOT equal to expression B otherwise FALSE
A < B	All primitive types	NULL if A or B is NULL, TRUE if expression A is less than expression B otherwise FALSE
A <= B	All primitive types	NULL if A or B is NULL, TRUE if expression A is less than or equal to expression B otherwise FALSE
A > B	All primitive types	NULL if A or B is NULL, TRUE if expression A is greater than expression B otherwise FALSE
A >= B	All primitive types	NULL if A or B is NULL, TRUE if expression A is greater than or equal to expression B otherwise FALSE
A IS NULL	all types	TRUE if expression A evaluates to NULL otherwise FALSE
A IS NOT NULL	All types	TRUE if expression A evaluates to NULL otherwise FALSE
A LIKE B	strings	NULL if A or B is NULL, TRUE if string A matches the SQL simple regular expression B, otherwise FALSE. The comparison is done character by character. The _ character in B matches any character in A(similar to . in posix regular expressions) while the % character in B matches an arbitrary number of characters in A(similar to .* in posix regular expressions) e.g. 'foobar' like 'foo' evaluates to FALSE where as 'foobar' like 'foo_ _ _' evaluates to TRUE and so does 'foobar' like 'foo%'
A RLIKE B	strings	NULL if A or B is NULL, TRUE if string A matches the Java regular expression B(See Java regular expressions syntax), otherwise FALSE e.g. 'foobar' rlike 'foo' evaluates to FALSE where as 'foobar' rlike '^f.*r$' evaluates to TRUE
A REGEXP B	strings	Same as RLIKE

6.1.2?????代數(shù)操作符

返回數(shù)字類型，如果任意一個操作符為NULL，則結(jié)果為NULL

Operator	Operand types	Description
A + B	All number types	Gives the result of adding A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands. e.g. since every integer is a float, therefore float is a containing type of integer so the + operator on a float and an int will result in a float.
A - B	All number types	Gives the result of subtracting B from A. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.
A * B	All number types	Gives the result of multiplying A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands. Note that if the multiplication causing overflow, you will have to cast one of the operators to a type higher in the type hierarchy.
A / B	All number types	Gives the result of dividing B from A. The result is a double type.
A % B	All number types	Gives the reminder resulting from dividing A by B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.
A & B	All number types	Gives the result of bitwise AND of A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.
A | B	All number types	Gives the result of bitwise OR of A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.
A ^ B	All number types	Gives the result of bitwise XOR of A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.
~A	All number types	Gives the result of bitwise NOT of A. The type of the result is the same as the type of A.

?

6.1.3?????邏輯操作符

6.1.4?????復(fù)雜類型操作符

Constructor Function	Operands	Description
Map	(key1, value1, key2, value2, ...)	Creates a map with the given key/value pairs
Struct	(val1, val2, val3, ...)	Creates a struct with the given field values. Struct field names will be col1, col2, ...
Array	(val1, val2, ...)	Creates an array with the given elements

?

6.1.5?????內(nèi)建函數(shù)

6.1.6?????數(shù)學(xué)函數(shù)

6.1.7?????集合函數(shù)

6.1.8?????類型轉(zhuǎn)換

?

6.1.9?????日期函數(shù)

返回值類型	名稱	描述
string	from_unixtime(int unixtime)	將時間戳（unix epoch秒數(shù)）轉(zhuǎn)換為日期時間字符串，例如from_unixtime(0)="1970-01-01 00:00:00"
bigint	unix_timestamp()	獲得當(dāng)前時間戳
bigint	unix_timestamp(string date)	獲得date表示的時間戳
bigint	to_date(string timestamp)	返回日期字符串，例如to_date("1970-01-01 00:00:00") = "1970-01-01"
string	year(string date)	返回年，例如year("1970-01-01 00:00:00") = 1970，year("1970-01-01") = 1970
int	month(string date)
int	day(string date) dayofmonth(date)
int	hour(string date)
int	minute(string date)
int	second(string date)
int	weekofyear(string date)
int	datediff(string enddate, string startdate)	返回enddate和startdate的天數(shù)的差，例如datediff('2009-03-01', '2009-02-27') = 2
int	date_add(string startdate, int days)	加days天數(shù)到startdate: date_add('2008-12-31', 1) = '2009-01-01'
int	date_sub(string startdate, int days)	減days天數(shù)到startdate: date_sub('2008-12-31', 1) = '2008-12-30'

?

6.1.10?????????????條件函數(shù)

返回值類型	名稱	描述
-	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	當(dāng)testCondition為真時返回valueTrue，testCondition為假或NULL時返回valueFalseOrNull
-	COALESCE(T v1, T v2, ...)	返回列表中的第一個非空元素，如果列表元素都為空則返回NULL
-	CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END	a = b，返回c；a = d，返回e；否則返回f
-	CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END	a?為真，返回b；c為真，返回d；否則e

?

6.1.11?????????????字符串函數(shù)

The following are built-in String functionsare supported in hive:

返回值類型	名稱?	描述
Int	length(string A)	返回字符串長度
String	reverse(string A)	反轉(zhuǎn)字符串
String	concat(string A, string B...)	合并字符串，例如concat('foo', 'bar')='foobar'。注意這一函數(shù)可以接受任意個數(shù)的參數(shù)
String	substr(string A, int start) substring(string A, int start)	返回子串，例如substr('foobar', 4)='bar'
String	substr(string A, int start, int len) substring(string A, int start, int len)	返回限定長度的子串，例如substr('foobar', 4, 1)='b'
String	upper(string A) ucase(string A)	轉(zhuǎn)換為大寫
String	lower(string A) lcase(string A)	轉(zhuǎn)換為小寫
String	trim(string A)
String	ltrim(string A)
String	rtrim(string A)
String	regexp_replace(string A, string B, string C)	Returns the string resulting from replacing all substrings in B that match the Java regular expression syntax(See Java regular expressions syntax) with C e.g. regexp_replace("foobar", "oo|ar", "") returns 'fb.' Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\\s' is necessary to match whitespace, etc.
String	regexp_extract(string subject, string pattern, int intex)	返回使用正則表達(dá)式提取的子字串。例如，regexp_extract('foothebar', 'foo(.*?)(bar)', 2)='bar'。注意使用特殊字符的規(guī)則：使用'\s'代表的是字符's'；空白字符需要使用'\\s'，以此類推。
String	parse_url(string urlString, string partToExtract)	解析URL字符串，partToExtract的可選項有：HOST, PATH, QUERY, REF, PROTOCOL, FILE, AUTHORITY, USERINFO。
		例如，
		parse_url('http://facebook.com/path/p1.php?query=1', 'HOST')='facebook.com'
		parse_url('http://facebook.com/path/p1.php?query=1', 'PATH')='/path/p1.php'
		parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY')='query=1'，可以指定key來返回特定參數(shù)，key的格式是QUERY:<KEY_NAME>，例如QUERY:k1
		parse_url('http://facebook.com/path/p1.php?query=1&field=2','QUERY','query')='1'可以用來取出外部渲染參數(shù)key對應(yīng)的value值
		parse_url('http://facebook.com/path/p1.php?query=1&field=2','QUERY','field')='2'
		parse_url('http://facebook.com/path/p1.php?query=1#Ref', 'REF')='Ref'
		parse_url('http://facebook.com/path/p1.php?query=1#Ref', 'PROTOCOL')='http'
String	get_json_object(string json_string, string path)	解析json字符串。若源json字符串非法則返回NULL。path參數(shù)支持JSONPath的一個子集，包括以下標(biāo)記：
		$: Root object
		[]: Subscript operator for array
		&: Wildcard for []
		.: Child operator
String	space(int n)	返回一個包含n個空格的字符串
String	repeat(string str, int n)	重復(fù)str字符串n遍
String	ascii(string str)	返回str中第一個字符的ascii碼
String	lpad(string str, int len, string pad)	左端補齊str到長度為len。補齊的字符串由pad指定。
String	rpad(string str, int len, string pad)	右端補齊str到長度為len。補齊的字符串由pad指定。
Array	split(string str, string pat)	返回使用pat作為正則表達(dá)式分割str字符串的列表。例如，split('foobar', 'o')[2] = 'bar'。？不是很明白這個結(jié)果
Int	find_in_set(string str, string strList)	Returns the first occurance of str in strList where strList is a comma-delimited string. Returns null if either argument is null. Returns 0 if the first argument contains any commas. e.g. find_in_set('ab', 'abc,b,ab,c,def') returns 3

?

6.2?UDTF

UDTF即Built-inTable-Generating Functions

使用這些UDTF函數(shù)有一些限制:

1、SELECT里面不能有其它字段

如：SELECTpageid, explode(adid_list) AS myCol...

2、不能嵌套

如：SELECTexplode(explode(adid_list)) AS myCol...不支持

3、不支持GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORTBY

如：SELECTexplode(adid_list) AS myCol ... GROUP BY myCol

?

6.2.1??Explode

將數(shù)組進(jìn)行轉(zhuǎn)置

例如：

1、create table test2(mycol array<int>);

2、insert OVERWRITE table test2 select * from (select array(1,2,3) froma union all select array(7,8,9)? fromd)c;

3、hive> select * from test2;

OK

[1,2,3]

[7,8,9]

3、? hive> SELECT explode(myCol) AS myNewCol FROM test2;

OK

1

2

3

7

8

9

?

?

?

7.??HIVE?的MAP/REDUCE

7.1?JOIN

對于 JOIN 操作：

INSERT OVERWRITE TABLE pv_users SELECT pv.pageid, u.age FROM page_view pv JOIN user u ON (pv.userid = u.userid);

實現(xiàn)過程為：

• Map：
  • 以 JOIN ON 條件中的列作為 Key，如果有多個列，則 Key 是這些列的組合
  • 以 JOIN 之后所關(guān)心的列作為 Value，當(dāng)有多個列時，Value 是這些列的組合。在 Value 中還會包含表的 Tag 信息，用于標(biāo)明此 Value 對應(yīng)于哪個表。
  • 按照 Key 進(jìn)行排序。
• Shuffle：
  • 根據(jù) Key 的值進(jìn)行 Hash，并將 Key/Value 對按照 Hash 值推至不同對 Reduce 中。
• Reduce：
  • Reducer 根據(jù) Key 值進(jìn)行 Join 操作，并且通過 Tag 來識別不同的表中的數(shù)據(jù)。

具體實現(xiàn)過程如圖：

7.2?GROUPBY

SELECT pageid, age, count(1) FROM pv_users GROUP BY pageid, age;

具體實現(xiàn)過程如圖：

7.3?DISTINCT

SELECT age, count(distinct pageid) FROM pv_users GROUP BY age;

實現(xiàn)過程如圖：

?

8.??使用HIVE注意點

8.1?字符集

Hadoop和Hive都是用UTF-8編碼的，所以,?所有中文必須是UTF-8編碼,?才能正常使用

備注：中文數(shù)據(jù)load到表里面,?如果字符集不同，很有可能全是亂碼需要做轉(zhuǎn)碼的,?但是hive本身沒有函數(shù)來做這個

?

8.2?壓縮

hive.exec.compress.output?這個參數(shù),?默認(rèn)是?false，但是很多時候貌似要單獨顯式設(shè)置一遍

否則會對結(jié)果做壓縮的，如果你的這個文件后面還要在hadoop下直接操作,?那么就不能壓縮了

?

8.3?count(distinct)

當(dāng)前的 Hive 不支持在一條查詢語句中有多 Distinct。如果要在 Hive 查詢語句中實現(xiàn)多Distinct，需要使用至少n+1 條查詢語句（n為distinct的數(shù)目），前n 條查詢分 別對 n 個列去重，最后一條查詢語句對 n 個去重之后的列做 Join 操作，得到最終結(jié)果。

?

8.4?JOIN

只支持等值連接

?

8.5?DML操作

只支持INSERT/LOAD操作，無UPDATE和DELTE

8.6?HAVING

不支持HAVING操作。如果需要這個功能要嵌套一個子查詢用where限制

8.7?子查詢

Hive不支持where子句中的子查詢

子查詢，只允許子查詢在from中出現(xiàn)

SELECT station, year, AVG(max_temperature)FROM (SELECT station, year, MAX(temperature) AS max_temperature FROM records2WHERE temperature != 9999 AND (quality = 0 OR quality = 1 OR quality = 4 ORquality = 5 OR quality = 9) GROUP BY station, year) mt GROUP BY station, year;

?

8.8?Join中處理null值的語義區(qū)別

SQL標(biāo)準(zhǔn)中，任何對null的操作（數(shù)值比較，字符串操作等）結(jié)果都為null。Hive對null值處理的邏輯和標(biāo)準(zhǔn)基本一致，除了Join時的特殊邏輯。

這里的特殊邏輯指的是，Hive的Join中，作為Join key的字段比較，null=null是有意義的，且返回值為true。檢查以下查詢：

查詢中，t_weblog表中uid為空的記錄將和t_user表中uid為空的記錄做連接，即l.uid =u.uid=null成立。

如果需要與標(biāo)準(zhǔn)一致的語義，我們需要改寫查詢手動過濾null值的情況：

實踐中，這一語義區(qū)別也是經(jīng)常導(dǎo)致數(shù)據(jù)傾斜的原因之一。

?

8.9?分號字符

分號是SQL語句結(jié)束標(biāo)記，在HiveQL中也是，但是在HiveQL中，對分號的識別沒有那么智慧，例如：

select concat(cookie_id,concat(';',’zoo’))from c02_clickstat_fatdt1 limit 2;

FAILED: Parse Error: line 0:-1 cannotrecognize input '<EOF>' in function specification

可以推斷，Hive解析語句的時候，只要遇到分號就認(rèn)為語句結(jié)束，而無論是否用引號包含起來。

解決的辦法是，使用分號的八進(jìn)制的ASCII碼進(jìn)行轉(zhuǎn)義，那么上述語句應(yīng)寫成：

selectconcat(cookie_id,concat('\073','zoo')) from c02_clickstat_fatdt1 limit 2;

為什么是八進(jìn)制ASCII碼？

我嘗試用十六進(jìn)制的ASCII碼，但Hive會將其視為字符串處理并未轉(zhuǎn)義，好像僅支持八進(jìn)制，原因不詳。這個規(guī)則也適用于其他非SELECT語句，如CREATE TABLE中需要定義分隔符，那么對不可見字符做分隔符就需要用八進(jìn)制的ASCII碼來轉(zhuǎn)義。

?

8.10????????Insert

8.10.1?????????????新增數(shù)據(jù)

根據(jù)語法Insert必須加“OVERWRITE”關(guān)鍵字，也就是說每一次插入都是一次重寫。那如何實現(xiàn)表中新增數(shù)據(jù)呢？

假設(shè)Hive中有表xiaojun1,

hive> DESCRIBE xiaojun1;

OK

id int

value int

hive> SELECT * FROM xiaojun1;

OK

3 4

1 2

2 3

現(xiàn)增加一條記錄:

hive> INSERT OVERWRITE TABLE xiaojun1

SELECT id, value FROM (

SELECT id, value FROM xiaojun1

UNION ALL

SELECT 4 AS id, 5 AS value FROM xiaojun1 limit 1

) u;

結(jié)果是:

hive>SELECT * FROM p1;

OK

3 4

4 5

2 3

1 2

其中的關(guān)鍵在于, 關(guān)鍵字UNION ALL的應(yīng)用, 即將原有數(shù)據(jù)集和新增數(shù)據(jù)集進(jìn)行結(jié)合, 然后重寫表.

?

8.10.2?????????????插入次序

?

INSERT OVERWRITE TABLE在插入數(shù)據(jù)時,是按照后面的SELECT語句中的字段順序插入的. 也就說, 當(dāng)id 和value 的位置互換, 那么value將被寫入id, 同id被寫入value.

8.10.3?????????????初始值

INSERT OVERWRITE TABLE在插入數(shù)據(jù)時, 后面的字段的初始值應(yīng)注意與表定義中的一致性. 例如, 當(dāng)為一個STRING類型字段初始為NULL時:

NULL AS field_name // 這可能會被提示定義類型為STRING, 但這里是void

CAST(NULL AS STRING) AS field_name // 這樣是正確的

又如, 為一個BIGINT類型的字段初始為0時:

CAST(0 AS BIGINT) AS field_name

?

9.??優(yōu)化

9.1?HADOOP計算框架特性

• 數(shù)據(jù)量大不是問題，數(shù)據(jù)傾斜是個問題。
• jobs數(shù)比較多的作業(yè)運行效率相對比較低，比如即使有幾百行的表，如果多次關(guān)聯(lián)多次匯總，產(chǎn)生十幾個jobs，耗時很長。原因是map reduce作業(yè)初始化的時間是比較長的。
• sum,count,max,min等UDAF，不怕數(shù)據(jù)傾斜問題,hadoop在map端的匯總合并優(yōu)化，使數(shù)據(jù)傾斜不成問題。
• count(distinct ),在數(shù)據(jù)量大的情況下，效率較低，如果是多count(distinct )效率更低，因為count(distinct)是按group by 字段分組，按distinct字段排序，一般這種分布方式是很傾斜的，比如男uv,女uv，淘寶一天30億的pv，如果按性別分組，分配2個reduce,每個reduce處理15億數(shù)據(jù)。

9.2?優(yōu)化的常用手段

• 好的模型設(shè)計事半功倍。
• 解決數(shù)據(jù)傾斜問題。
• 減少job數(shù)。
• 設(shè)置合理的map reduce的task數(shù)，能有效提升性能。(比如，10w+級別的計算，用160個reduce，那是相當(dāng)?shù)睦速M，1個足夠)。
• 了解數(shù)據(jù)分布，自己動手解決數(shù)據(jù)傾斜問題是個不錯的選擇。set hive.groupby.skewindata=true;這是通用的算法優(yōu)化，但算法優(yōu)化有時不能適應(yīng)特定業(yè)務(wù)背景，開發(fā)人員了解業(yè)務(wù)，了解數(shù)據(jù)，可以通過業(yè)務(wù)邏輯精確有效的解決數(shù)據(jù)傾斜問題。
• 數(shù)據(jù)量較大的情況下，慎用count(distinct)，count(distinct)容易產(chǎn)生傾斜問題。
• 對小文件進(jìn)行合并，是行至有效的提高調(diào)度效率的方法，假如所有的作業(yè)設(shè)置合理的文件數(shù)，對云梯的整體調(diào)度效率也會產(chǎn)生積極的正向影響。
• 優(yōu)化時把握整體，單個作業(yè)最優(yōu)不如整體最優(yōu)。

???

9.3?全排序

Hive的排序關(guān)鍵字是SORT BY，它有意區(qū)別于傳統(tǒng)數(shù)據(jù)庫的ORDER BY也是為了強調(diào)兩者的區(qū)別–SORT BY只能在單機范圍內(nèi)排序。 [王黎15] ?

9.3.1?????例1

set mapred.reduce.tasks=2;

原值

select cookie_id,page_id,id fromc02_clickstat_fatdt1

where cookie_id IN('1.193.131.218.1288611279693.0','1.193.148.164.1288609861509.2')

1.193.148.164.1288609861509.2??113181412886099008861288609901078194082403????? 684000005

1.193.148.164.1288609861509.2??127001128860563972141288609859828580660473????? 684000015

1.193.148.164.1288609861509.2?? 113181412886099165721288609915890452725326????? 684000018

1.193.131.218.1288611279693.0??01c183da6e4bc50712881288611540109914561053????? 684000114

1.193.131.218.1288611279693.0??01c183da6e4bc22412881288611414343558274174????? 684000118

1.193.131.218.1288611279693.0??01c183da6e4bc50712881288611511781996667988????? 684000121

1.193.131.218.1288611279693.0??01c183da6e4bc22412881288611523640691739999????? 684000126

1.193.131.218.1288611279693.0??01c183da6e4bc50712881288611540109914561053????? 684000128

?

?

hive> select cookie_id,page_id,id fromc02_clickstat_fatdt1 where

cookie_idIN('1.193.131.218.1288611279693.0','1.193.148.164.1288609861509.2')

SORT BY COOKIE_ID,PAGE_ID;

SORT排序后的值

1.193.131.218.1288611279693.0?????????? 684000118?????? 01c183da6e4bc22412881288611414343558274174????? 684000118

1.193.131.218.1288611279693.0?????????? 684000114??????01c183da6e4bc50712881288611540109914561053????? 684000114

1.193.131.218.1288611279693.0?????????? 684000128??????01c183da6e4bc50712881288611540109914561053????? 684000128

1.193.148.164.1288609861509.2?????????? 684000005??????113181412886099008861288609901078194082403????? 684000005

1.193.148.164.1288609861509.2?????????? 684000018??????113181412886099165721288609915890452725326????? 684000018

1.193.131.218.1288611279693.0?????????? 684000126??????01c183da6e4bc22412881288611523640691739999????? 684000126

1.193.131.218.1288611279693.0?????????? 684000121??????01c183da6e4bc50712881288611511781996667988????? 684000121

1.193.148.164.1288609861509.2?????????? 684000015??????127001128860563972141288609859828580660473????? 684000015

?

select cookie_id,page_id,id fromc02_clickstat_fatdt1

where cookie_idIN('1.193.131.218.1288611279693.0','1.193.148.164.1288609861509.2')

ORDER BY PAGE_ID,COOKIE_ID;

1.193.131.218.1288611279693.0?????????? 684000118??????01c183da6e4bc22412881288611414343558274174????? 684000118

1.193.131.218.1288611279693.0?????????? 684000126??????01c183da6e4bc22412881288611523640691739999????? 684000126

1.193.131.218.1288611279693.0?????????? 684000121??????01c183da6e4bc50712881288611511781996667988????? 684000121

1.193.131.218.1288611279693.0?????????? 684000114??????01c183da6e4bc50712881288611540109914561053????? 684000114

1.193.131.218.1288611279693.0?????????? 684000128?????? 01c183da6e4bc50712881288611540109914561053????? 684000128

1.193.148.164.1288609861509.2?????????? 684000005??????113181412886099008861288609901078194082403????? 684000005

1.193.148.164.1288609861509.2?????????? 684000018??????113181412886099165721288609915890452725326???? ?684000018

1.193.148.164.1288609861509.2?????????? 684000015??????127001128860563972141288609859828580660473????? 684000015

可以看到SORT和ORDER排序出來的值不一樣。一開始我指定了2個reduce進(jìn)行數(shù)據(jù)分發(fā)(各自進(jìn)行排序)。結(jié)果不一樣的主要原因是上述查詢沒有reduce key，hive會生成隨機數(shù)作為reduce key。這樣的話輸入記錄也隨機地被分發(fā)到不同reducer機器上去了。為了保證reducer之間沒有重復(fù)的cookie_id記錄，可以使用DISTRIBUTE BY關(guān)鍵字指定分發(fā)key為cookie_id。

select cookie_id,country,id,page_id,id fromc02_clickstat_fatdt1 where cookie_idIN('1.193.131.218.1288611279693.0','1.193.148.164.1288609861509.2')? distribute by cookie_id SORT BY COOKIE_ID,page_id;

1.193.131.218.1288611279693.0?????????? 684000118??????01c183da6e4bc22412881288611414343558274174????? 684000118

1.193.131.218.1288611279693.0?????????? 684000126??????01c183da6e4bc22412881288611523640691739999????? 684000126

1.193.131.218.1288611279693.0?????????? 684000121??????01c183da6e4bc50712881288611511781996667988????? 684000121

1.193.131.218.1288611279693.0?????????? 684000114??????01c183da6e4bc50712881288611540109914561053????? 684000114

1.193.131.218.1288611279693.0??????? ???684000128??????01c183da6e4bc50712881288611540109914561053????? 684000128

1.193.148.164.1288609861509.2?????????? 684000005??????113181412886099008861288609901078194082403????? 684000005

1.193.148.164.1288609861509.2?????????? 684000018?????? 113181412886099165721288609915890452725326????? 684000018

1.193.148.164.1288609861509.2?????????? 684000015??????127001128860563972141288609859828580660473????? 684000015

9.3.2?????例2

CREATE TABLE if not exists t_order(

?

id int, -- 訂單編號

?

sale_id int, -- 銷售ID

?

customer_id int, -- 客戶ID

?

product _id int, -- 產(chǎn)品ID

?

amount int -- 數(shù)量

?

) PARTITIONED BY (ds STRING);

在表中查詢所有銷售記錄，并按照銷售ID和數(shù)量排序：

set mapred.reduce.tasks=2;

?

Select sale_id, amount from t_order

?

Sort by sale_id, amount;

這一查詢可能得到非期望的排序。指定的2個reducer分發(fā)到的數(shù)據(jù)可能是（各自排序）：

Reducer1：

Sale_id | amount

?

0 | 100

?

1 | 30

?

1 | 50

?

2 | 20

Reducer2：

Sale_id | amount

?

0?| 110

?

0 | 120

?

3 | 50

?

4 | 20

使用DISTRIBUTE BY關(guān)鍵字指定分發(fā)key為sale_id。改造后的HQL如下：

set mapred.reduce.tasks=2;

?

Select sale_id, amount from t_order

?

Distribute by sale_id

?

Sort by sale_id, amount;

這樣能夠保證查詢的銷售記錄集合中，銷售ID對應(yīng)的數(shù)量是正確排序的，但是銷售ID不能正確排序，原因是hive使用hadoop默認(rèn)的HashPartitioner分發(fā)數(shù)據(jù)。

這就涉及到一個全排序的問題。解決的辦法無外乎兩種：

1.) 不分發(fā)數(shù)據(jù)，使用單個reducer：

set mapred.reduce.tasks=1;

這一方法的缺陷在于reduce端成為了性能瓶頸，而且在數(shù)據(jù)量大的情況下一般都無法得到結(jié)果。但是實踐中這仍然是最常用的方法，原因是通常排序的查詢是為了得到排名靠前的若干結(jié)果，因此可以用limit子句大大減少數(shù)據(jù)量。使用limit n后，傳輸?shù)絩educe端（單機）的數(shù)據(jù)記錄數(shù)就減少到n* （map個數(shù)）。

2.) 修改Partitioner，這種方法可以做到全排序。這里可以使用Hadoop自帶的TotalOrderPartitioner（來自于Yahoo!的TeraSort項目），這是一個為了支持跨reducer分發(fā)有序數(shù)據(jù)開發(fā)的Partitioner，它需要一個SequenceFile格式的文件指定分發(fā)的數(shù)據(jù)區(qū)間。如果我們已經(jīng)生成了這一文件（存儲在/tmp/range_key_list，分成100個reducer），可以將上述查詢改寫為

set mapred.reduce.tasks=100;

?

sethive.mapred.partitioner=org.apache.hadoop.mapred.lib.TotalOrderPartitioner;

?

settotal.order.partitioner.path=/tmp/ range_key_list;

?

Select sale_id, amount from t_order

?

Cluster by sale_id

?

Sort by amount;

有很多種方法生成這一區(qū)間文件（例如hadoop自帶的o.a.h.mapreduce.lib.partition.InputSampler工具）。這里介紹用Hive生成的方法，例如有一個按id有序的t_sale表：

CREATE TABLE if not exists t_sale (

?

id int,

?

name string,

?

loc string

?

);

則生成按sale_id分發(fā)的區(qū)間文件的方法是：

create external table range_keys(sale_idint)

?

row format serde

?

'org.apache.hadoop.hive.serde2.binarysortable.BinarySortableSerDe'

?

stored as

?

inputformat

?

'org.apache.hadoop.mapred.TextInputFormat'

?

outputformat

?

'org.apache.hadoop.hive.ql.io.HiveNullValueSequenceFileOutputFormat'

?

location '/tmp/range_key_list';

?

?

?

insert overwrite table range_keys

?

select distinct sale_id

?

from source t_salesampletable(BUCKET 100 OUT OF 100 ON rand()) s

?

sort by sale_id;

生成的文件（/tmp/range_key_list目錄下）可以讓TotalOrderPartitioner按sale_id有序地分發(fā)reduce處理的數(shù)據(jù)。區(qū)間文件需要考慮的主要問題是數(shù)據(jù)分發(fā)的均衡性，這有賴于對數(shù)據(jù)深入的理解。

9.4?怎樣做笛卡爾積

當(dāng)Hive設(shè)定為嚴(yán)格模式（hive.mapred.mode=strict）時，不允許在HQL語句中出現(xiàn)笛卡爾積，這實際說明了Hive對笛卡爾積支持較弱。因為找不到Join key，Hive只能使用1個reducer來完成笛卡爾積。

當(dāng)然也可以用上面說的limit的辦法來減少某個表參與join的數(shù)據(jù)量，但對于需要笛卡爾積語義的需求來說，經(jīng)常是一個大表和一個小表的Join操作，結(jié)果仍然很大（以至于無法用單機處理），這時MapJoin才是最好的解決辦法。

MapJoin，顧名思義，會在Map端完成Join操作。這需要將Join操作的一個或多個表完全讀入內(nèi)存。

MapJoin的用法是在查詢/子查詢的SELECT關(guān)鍵字后面添加/*+ MAPJOIN(tablelist) */提示優(yōu)化器轉(zhuǎn)化為MapJoin（目前Hive的優(yōu)化器不能自動優(yōu)化MapJoin）。其中tablelist可以是一個表，或以逗號連接的表的列表。tablelist中的表將會讀入內(nèi)存，應(yīng)該將小表寫在這里。

PS：有用戶說MapJoin在子查詢中可能出現(xiàn)未知BUG。在大表和小表做笛卡爾積時，規(guī)避笛卡爾積的方法是，給Join添加一個Join key，原理很簡單：將小表擴充一列join key，并將小表的條目復(fù)制數(shù)倍，join key各不相同；將大表擴充一列join key為隨機數(shù)。

9.5?怎樣寫exist/in子句

Hive不支持where子句中的子查詢，SQL常用的exist in子句需要改寫。這一改寫相對簡單。考慮以下SQL查詢語句：

SELECT a.key, a.value

?

FROM a

?

WHERE a.key in

?

(SELECT b.key

?

FROM B);

可以改寫為

SELECT a.key, a.value

?

FROM a LEFT OUTER JOIN b ON (a.key =b.key)

?

WHERE b.key <> NULL;

一個更高效的實現(xiàn)是利用left semi join改寫為：

SELECT a.key, a.val

?

FROM a LEFT SEMI JOIN b on (a.key =b.key);

left semi join是0.5.0以上版本的特性。

9.6?怎樣決定reducer個數(shù)

Hadoop MapReduce程序中，reducer個數(shù)的設(shè)定極大影響執(zhí)行效率，這使得Hive怎樣決定reducer個數(shù)成為一個關(guān)鍵問題。遺憾的是Hive的估計機制很弱，不指定reducer個數(shù)的情況下，Hive會猜測確定一個reducer個數(shù)，基于以下兩個設(shè)定：

1. hive.exec.reducers.bytes.per.reducer（默認(rèn)為1000^3）

2. hive.exec.reducers.max（默認(rèn)為999）

計算reducer數(shù)的公式很簡單：

N=min(參數(shù)2，總輸入數(shù)據(jù)量/參數(shù)1)

通常情況下，有必要手動指定reducer個數(shù)。考慮到map階段的輸出數(shù)據(jù)量通常會比輸入有大幅減少，因此即使不設(shè)定reducer個數(shù)，重設(shè)參數(shù)2還是必要的。依據(jù)Hadoop的經(jīng)驗，可以將參數(shù)2設(shè)定為0.95*(集群中TaskTracker個數(shù))。

9.7?合并MapReduce操作

Multi-group by

Multi-group by是Hive的一個非常好的特性，它使得Hive中利用中間結(jié)果變得非常方便。例如，

FROM (SELECT a.status, b.school,b.gender

?

FROM status_updates a JOIN profilesb

?

ON (a.userid = b.userid and

?

a.ds='2009-03-20' )

?

) subq1

?

INSERT OVERWRITE TABLEgender_summary

?

PARTITION(ds='2009-03-20')

?

SELECT subq1.gender, COUNT(1) GROUPBY subq1.gender

?

INSERT OVERWRITE TABLEschool_summary

?

PARTITION(ds='2009-03-20')

?

SELECT subq1.school, COUNT(1) GROUPBY subq1.school

上述查詢語句使用了Multi-group by特性連續(xù)group by了2次數(shù)據(jù)，使用不同的groupby key。這一特性可以減少一次MapReduce操作。

Multi-distinct

Multi-distinct是淘寶開發(fā)的另一個multi-xxx特性，使用Multi-distinct可以在同一查詢/子查詢中使用多個distinct，這同樣減少了多次MapReduce操作

?

9.8? Bucket? 與sampling

Bucket是指將數(shù)據(jù)以指定列的值為key進(jìn)行hash，hash到指定數(shù)目的桶中。這樣就可以支持高效采樣了。

如下例就是以userid這一列為bucket的依據(jù)，共設(shè)置32個buckets

Sampling可以在全體數(shù)據(jù)上進(jìn)行采樣，這樣效率自然就低，它還是要去訪問所有數(shù)據(jù)。而如果一個表已經(jīng)對某一列制作了bucket，就可以采樣所有桶中指定序號的某個桶，這就減少了訪問量。

如下例所示就是采樣了page_view中32個桶中的第三個桶。

?

9.9?Partition

Partition就是分區(qū)。分區(qū)通過在創(chuàng)建表時啟用partition by實現(xiàn)，用來partition的維度并不是實際數(shù)據(jù)的某一列，具體分區(qū)的標(biāo)志是由插入內(nèi)容時給定的。當(dāng)要查詢某一分區(qū)的內(nèi)容時可以采用where語句，形似where tablename.partition_key >a來實現(xiàn)。

創(chuàng)建含分區(qū)的表

載入內(nèi)容，并指定分區(qū)標(biāo)志

查詢指定標(biāo)志的分區(qū)內(nèi)容

?

9.10????????JOIN

9.10.1?????????????JOIN原則

在使用寫有 Join 操作的查詢語句時有一條原則：應(yīng)該將條目少的表/子查詢放在 Join 操作符的左邊。原因是在 Join 操作的 Reduce 階段，位于 Join 操作符左邊的表的內(nèi)容會被加載進(jìn)內(nèi)存，將條目少的表放在左邊，可以有效減少發(fā)生OOM 錯誤的幾率。對于一條語句中有多個 Join 的情況，如果 Join 的條件相同，比如查詢：

INSERT OVERWRITE TABLE pv_users ?SELECT pv.pageid, u.age FROM page_view p ?JOIN user u ON (pv.userid = u.userid) ?JOIN newuser x ON (u.userid = x.userid);?

• 如果 Join 的 key 相同，不管有多少個表，都會則會合并為一個 Map-Reduce
• 一個 Map-Reduce 任務(wù)，而不是 ‘n’ 個
• 在做 OUTER JOIN 的時候也是一樣

如果 Join 的條件不相同，比如：

INSERT OVERWRITE TABLE pv_users ?? SELECT pv.pageid, u.age FROM page_view p ?? JOIN user u ON (pv.userid = u.userid) ?? JOIN newuser x on (u.age = x.age);??

Map-Reduce 的任務(wù)數(shù)目和Join 操作的數(shù)目是對應(yīng)的，上述查詢和以下查詢是等價的：

INSERT OVERWRITE TABLE tmptable ?? SELECT * FROM page_view p JOIN user u ?? ON (pv.userid = u.userid); ?INSERT OVERWRITE TABLE pv_users ?? SELECT x.pageid, x.age FROM tmptable x ?? JOIN newuser y ON (x.age = y.age);???

9.10.2?????????????Map Join

Join 操作在 Map 階段完成，不再需要Reduce，前提條件是需要的數(shù)據(jù)在 Map 的過程中可以訪問到。比如查詢：

INSERT OVERWRITE TABLE pv_users ?? SELECT /*+ MAPJOIN(pv) */ pv.pageid, u.age ?? FROM page_view pv ???? JOIN user u ON (pv.userid = u.userid);???

可以在 Map 階段完成 Join，如圖所示：

相關(guān)的參數(shù)為：

• hive.join.emit.interval = 1000 ?How many rows in the right-most join operand Hive should buffer before emitting the join result.
• hive.mapjoin.size.key = 10000
• hive.mapjoin.cache.numrows = 10000

?

9.11????????數(shù)據(jù)傾斜

9.11.1?????????????空值數(shù)據(jù)傾斜

場景： 如日志中，常會有信息丟失的問題，比如全網(wǎng)日志中的user_id，如果取其中的user_id和bmw_users關(guān)聯(lián)，會碰到數(shù)據(jù)傾斜的問題。

解決方法1： ?user_id為空的不參與關(guān)聯(lián)

Select * From log a

Join bmw_users b

On a.user_id is not null

And a.user_id = b.user_id

Union all

Select * from log a

where a.user_id is null;

解決方法2 ： 賦與空值分新的key值

Select * ?

from log a?

left outer join bmw_users b?

on case when a.user_id is null thenconcat(‘dp_hive’,rand() ) else a.user_id end = b.user_id;?

結(jié)論： 方法2比方法效率更好，不但io少了，而且作業(yè)數(shù)也少了。方法1 log讀取兩次，jobs是2。方法2 job數(shù)是1 。這個優(yōu)化適合無效id(比如-99,’’,null等)產(chǎn)生的傾斜問題。把空值的key變成一個字符串加上隨機數(shù)，就能把傾斜的數(shù)據(jù)分到不同的reduce上 ,解決數(shù)據(jù)傾斜問題。附上hadoop通用關(guān)聯(lián)的實現(xiàn)方法（關(guān)聯(lián)通過二次排序?qū)崿F(xiàn)的，關(guān)聯(lián)的列為parition key,關(guān)聯(lián)的列c1和表的tag組成排序的group key,根據(jù)parition key分配reduce。同一reduce內(nèi)根據(jù)group key排序）

?

9.11.2?????????????不同數(shù)據(jù)類型關(guān)聯(lián)產(chǎn)生數(shù)據(jù)傾斜

場景： 一張表s8的日志，每個商品一條記錄，要和商品表關(guān)聯(lián)。但關(guān)聯(lián)卻碰到傾斜的問題。s8的日志中有字符串商品id,也有數(shù)字的商品id,類型是string的，但商品中的數(shù)字id是bigint的。猜測問題的原因是把s8的商品id轉(zhuǎn)成數(shù)字id做hash來分配reduce，所以字符串id的s8日志，都到一個reduce上了，解決的方法驗證了這個猜測。

解決方法： 把數(shù)字類型轉(zhuǎn)換成字符串類型

Select * from s8_log a

Left outer join r_auction_auctions b

On a.auction_id = cast(b.auction_id asstring);

9.11.3?????????????大表Join的數(shù)據(jù)偏斜

MapReduce編程模型下開發(fā)代碼需要考慮數(shù)據(jù)偏斜的問題，Hive代碼也是一樣。數(shù)據(jù)偏斜的原因包括以下兩點：

1. Map輸出key數(shù)量極少，導(dǎo)致reduce端退化為單機作業(yè)。

2. Map輸出key分布不均，少量key對應(yīng)大量value，導(dǎo)致reduce端單機瓶頸。

Hive中我們使用MapJoin解決數(shù)據(jù)偏斜的問題，即將其中的某個表（全量）分發(fā)到所有Map端進(jìn)行Join，從而避免了reduce。這要求分發(fā)的表可以被全量載入內(nèi)存。

極限情況下，Join兩邊的表都是大表，就無法使用MapJoin。

這種問題最為棘手，目前已知的解決思路有兩種：

?

1. 如果是上述情況1，考慮先對Join中的一個表去重，以此結(jié)果過濾無用信息。這樣一般會將其中一個大表轉(zhuǎn)化為小表，再使用MapJoin?。

一個實例是廣告投放效果分析，例如將廣告投放者信息表i中的信息填充到廣告曝光日志表w中，使用投放者id關(guān)聯(lián)。因為實際廣告投放者數(shù)量很少（但是投放者信息表i很大），因此可以考慮先在w表中去重查詢所有實際廣告投放者id列表，以此Join過濾表i，這一結(jié)果必然是一個小表，就可以使用MapJoin。

?

2. 如果是上述情況2，考慮切分Join中的一個表為多片，以便將切片全部載入內(nèi)存，然后采用多次MapJoin得到結(jié)果。

一個實例是商品瀏覽日志分析，例如將商品信息表i中的信息填充到商品瀏覽日志表w中，使用商品id關(guān)聯(lián)。但是某些熱賣商品瀏覽量很大，造成數(shù)據(jù)偏斜。例如，以下語句實現(xiàn)了一個inner join邏輯，將商品信息表拆分成2個表：

以下語句實現(xiàn)了left outer join邏輯：

上述語句使用Hive的sample table特性對表做切分。

?

9.12????????合并小文件

文件數(shù)目過多，會給 HDFS 帶來壓力，并且會影響處理效率，可以通過合并 Map 和 Reduce 的結(jié)果文件來消除這樣的影響：

hive.merge.mapfiles = true 是否和并 Map 輸出文件，默認(rèn)為 True

hive.merge.mapredfiles = false 是否合并 Reduce 輸出文件，默認(rèn)為 False

hive.merge.size.per.task = 256*1000*1000 合并文件的大小

?

9.13????????GroupBy

·????????Map 端部分聚合：

???? 并不是所有的聚合操作都需要在 Reduce 端完成，很多聚合操作都可以先在Map 端進(jìn)行部分聚合，最后在 Reduce 端得出最終結(jié)果。

????? 基于 Hash

????? 參數(shù)包括：

§? hive.map.aggr = true 是否在 Map 端進(jìn)行聚合，默認(rèn)為 True

§? hive.groupby.mapaggr.checkinterval= 100000 在 Map 端進(jìn)行聚合操作的條目數(shù)目

·????????有數(shù)據(jù)傾斜的時候進(jìn)行負(fù)載均衡

????? hive.groupby.skewindata= false

???? 當(dāng)選項設(shè)定為 true，生成的查詢計劃會有兩個 MR Job。第一個 MR Job 中，Map 的輸出結(jié)果集合會隨機分布到 Reduce 中，每個 Reduce 做部分聚合操作，并輸出結(jié)果，這樣處理的結(jié)果是相同的 Group By Key 有可能被分發(fā)到不同的Reduce 中，從而達(dá)到負(fù)載均衡的目的；第二個 MR Job再根據(jù)預(yù)處理的數(shù)據(jù)結(jié)果按照 Group By Key 分布到 Reduce 中（這個過程可以保證相同的Group By Key 被分布到同一個 Reduce 中），最后完成最終的聚合操作。

?

10.????????HIVE FAQ

1、 [admin@hadoop1 ~]$ hive

Cannot find hadoop installation: $HADOOP_HOME must be set orhadoop must be in the path

原因：HADOOP路徑?jīng)]有在環(huán)境變量中定義

解決方法：admin@hadoop1~]$ export HADOOP_HOME=$HOME/hadoop-0.19.2

?

2、FAILED: Execution Error, return code 1 fromorg.apache.hadoop.hive.ql.exec.DDLTask

解決方案：Hive的元數(shù)據(jù)庫derby服務(wù)沒有啟動

進(jìn)入到hive的安裝目錄

/home/admin/caona/hive/build/dist/db-derby-10.4.1.3-bin/bin

運行startNetworkServer -h 0.0.0.0

?

3、[admin@hadoop1 conf]$ hive

Unable to createlog directory ${build.dir}/tmp

原因：存放日志文件的目錄被人刪除了。

解決方法：進(jìn)行到${build.dir}下面，創(chuàng)建一個tmp目錄。

如：[admin@hadoop1build]$ pwd

/home/admin/caona/hive/build

[admin@hadoop1build]$ mkdir tmp

?

11.????????常用參考資料路徑

Hive地址

http://wiki.apache.org/hadoop/Hive

http://hive.apache.org/

Velocity地址

http://velocity.apache.org/engine/releases/velocity-1.7/user-guide.html

Hadoop地址

http://hadoop.apache.org/

http://www.cloudera.com/

Hadoop中文文檔地址

http://hadoop.apache.org/common/docs/r0.18.2/cn/commands_manual.html

Hive學(xué)習(xí)筆記