hive語法和常用函數(shù)

Hive就是一個基于Hadoop分布式系統(tǒng)上得數(shù)據(jù)倉庫，最早就是由Facebook公司開發(fā)得,Hive極大

得推進了Hadoopecosystem在數(shù)據(jù)倉庫方面上得發(fā)展。

Facebook得分析人員中很多工程師比較擅長而SQL而不善于開發(fā)MapReduce程序,

為此開發(fā)出Hive,并對比較熟悉SQL得工程師提供了一套新得SQLIike方言---HiveQL?

HiveSQL方言特別與MySQL方言很像，并提供了HiveQL得編程接口。HiveQL語

句最終被Hive解析器引擎解析為MarReduce程序，作為job提交給JobTracker運行。這對

MapReduce框架就是一個很有力得支持。

Hive就是一個數(shù)據(jù)倉庫,它提供了數(shù)據(jù)倉庫得部分功能:數(shù)據(jù)ETL（抽取、轉(zhuǎn)換、

加載）工具,數(shù)據(jù)存儲管理,大數(shù)據(jù)集得查詢與分析能力。

由于Hive就是Hadoop上得數(shù)據(jù)倉庫，因此Hive也具有高延遲、批處理得得特性，

即使處理很小得數(shù)據(jù)也會有比較高得延遲。故此,Hive得性能就與居于傳統(tǒng)數(shù)據(jù)庫得數(shù)據(jù)倉庫得

性能不能比較了。

Hive不提供數(shù)據(jù)排序與查詢得cache功能,不提供索引功能,不提供在線事物,也

不提供實時得查詢功能，更不提供實時得記錄更性得功能，但就是,Hive能很好地處理在不變得超

大數(shù)據(jù)集上得批量得分析處理功能。Hive就是基于hadoop平臺得,故有很好得擴展性（可以自適應

機器與數(shù)據(jù)量得動態(tài)變化）,高延展性（自定義函數(shù)）,良好得容錯性，低約束得數(shù)據(jù)輸入格式。

下面我們來瞧一下Hive得架構(gòu)與執(zhí)行流程以及編譯流程：

,將HiveQL語

‘句編譯成；

1_并優(yōu)化乏廠’

?（編譯器，優(yōu)化器、■期蝴.則力

執(zhí)行器等）

1

1

/IRD=MS]-

Metastore

行藪據(jù)）WMIH

Hive的體系結(jié)構(gòu)和執(zhí)行流程

Parser］將Hi—頭投募

將邏輯計劃轉(zhuǎn)成物理MgpBg薊￡g.Job計劃

?■,?■■■■■■［提交給Hadoop?臺運行MapReduceJob作業(yè):■學

Hive引擎執(zhí)行編譯優(yōu)化HiveQL語句和提交執(zhí)行的過程

用戶提交得HiveQL語句最終被編譯為MapReduce程序作為Job提交給Hadoop執(zhí)

行。

Hive得數(shù)據(jù)類型

Hive得基本數(shù)據(jù)類型

有：TINYINT,SAMLLINT,INT,BIGINT,BOOLEAN,FLOAT,DOUBLE,STRING,TlMESTAMP（VO、8、0+）與

BINARY（VO、8、0+）.

Hive得集合類型有:STRUCT,MAP與ARRAY。

Hive主要有四種數(shù)據(jù)模型（即表）：（內(nèi)部）表、外部表、分區(qū)表與桶表。

表得元數(shù)據(jù)保存?zhèn)鹘y(tǒng)得數(shù)據(jù)庫得表中，當前hive只支持Derby與MySQL數(shù)據(jù)庫。

內(nèi)部表：

Hive中得表與傳統(tǒng)數(shù)據(jù)庫中得表在概念上就是類似得,Hive得每個表都有自己

得存儲目錄，除了外部表外，所有得表數(shù)據(jù)都存放在配置在hivesite、xml文件得${hive、

metastore、warehouse、dir}/table_name目錄下。

Java代碼

1.CREATETABLEIFNOTEXISTSstudents（user_noINT,nameSTRING.sexSTRING,

2.gradeSTRINGMOT，班級'）MONT,學生表，

3.ROWFORMATDELIMITED

4.FIELDSTERMINATEDBY

5.STOREASTEXTFILE;

外部表：

外部表指向已經(jīng)存在在HadoopHDFS上得數(shù)據(jù),除了在刪除外部表時只刪除元數(shù)

據(jù)而不會刪除表數(shù)據(jù)外,其她與內(nèi)部表很像。

Java代碼

1.CREATEEXTERNALTABLEIFNOTEXISTSstudents(user_noINT,nameSTRING,sexSTRING,

2.classSTRINGMOT，班級')MONT'學生表’

3.ROWFORMATDELIMITED

4.FIELDSTERMINATEDBY

5.STOREASSEQUENCEFILE

6.LOCATION'/usr/test/data/studentsvtxt,;

分區(qū)表：

分區(qū)表得每一個分區(qū)都對應數(shù)據(jù)庫中相應分區(qū)列得一個索弓I,但就是其組織方

式與傳統(tǒng)得關(guān)系型數(shù)據(jù)庫不同。在Hive中，分區(qū)表得每一個分區(qū)都對應表下得一個目錄，所有得分

區(qū)得數(shù)據(jù)都存儲在對應得目錄中。

比如說，分區(qū)表partitinTable有包含nation（國家）、ds（日期）與city（城市）3

個分區(qū)，其中nation=china,ds=20130506,city=Shanghai則對應HDFS上得目錄為:

/datawarehouse/partitinTable/nation=china/city=Shangha

i/ds=20130506/。

Java代碼

I.CREATETABLEIFNOTEXISTSstudents(user_noINT,nameSTRING.sexSTRING,

2.classSTRINGMOT，班級’)MONT，學生表，

3.PARTITIONEDBY(dsSTRING.countrySTRING)

4.ROWFORMATDELIMITED

5.FIELDSTERMINATEDBY

6.STOREASSEQUENCEFILE;

分區(qū)中定義得變量名不能與表中得列相同。

桶區(qū)表：

桶表就就是對指定列進行哈希（hash）計算,然后會根據(jù)hash值進行切分數(shù)據(jù)，將

具有不同hash值得數(shù)據(jù)寫到每個桶對應得文件中。

Java代碼

1.CREATETABLEIFNOTEXISTSstudents(user_noINT,nameSTRING,sexSTRING.

classSTRINGMOT，班級'，scoreSMALLINTMOT，總分')MONT，學生表'

3.PARTITIONEDBY(dsSTRING,countrySTRING)

4.CLUSTEREDBY(user_no)SORTEDBY(score)INTO32BUCKETS

5.ROWFORMATDELIMITED

6.FIELDSTERMINATEDBY

7.STOREASSEQUENCEFILE:

內(nèi)部表與外部表得主要區(qū)別：

1)、內(nèi)部表創(chuàng)建要2步:表創(chuàng)建與數(shù)據(jù)加載,這兩個過程可以同步執(zhí)行。在數(shù)據(jù)加

載得過程中，數(shù)據(jù)數(shù)據(jù)會移動到數(shù)據(jù)倉庫得目錄中;外部表得創(chuàng)建只需要一個步驟,表創(chuàng)建數(shù)據(jù)加

載同時完成,表數(shù)據(jù)不會移動。

2)、刪除內(nèi)部表時,會將表數(shù)據(jù)與表得元數(shù)據(jù)一同刪除;而刪除外部表時,緊刪除

表得元數(shù)據(jù)而不會刪除表數(shù)據(jù)。

在上一節(jié)《深入學習《ProgramingHive》：Hive得數(shù)據(jù)模型(表)》中，已經(jīng)學習過表得定義，接下

來接著學習Hive得DDL操作。

復制表結(jié)構(gòu)：

Java代碼

1.CREATETABLEIFNOTEXISTSstudents?LIKEstudents;

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

Java代碼

I.DESCRIBETABLEstudents;

刪除表：

Java代碼

I.DROPTABLEstudents;

創(chuàng)建視圖：

Hive中得視圖(view)得概念與傳統(tǒng)數(shù)據(jù)庫中得表就是相同得,就是只讀得，目

前還不支持不支持物化視圖。

如果在創(chuàng)建試圖后,再將基本表中被視圖引用得列修改,那么修改后得數(shù)據(jù)列

將不會體現(xiàn)在視圖中；如果基本表被刪除或以不兼容得方式被修改，則查詢該視圖時會失

敗。

Java代碼

I.CREATEVIEWIFNOTEXISTSview_students(user_no,name,score)

2.AS

3.SELECTuser_no,name,scoreFROMstudents;

刪除視圖：

Java代碼

1.DROPVIEWview_students;

定制表存儲方式：

Java代碼

1.CREATETABLEIFNOTEXISTSemployees(

2.noSTRING.

3.nameSTRING,

4.salaryFLOAT,

5.subordinatesARRAY<STRING>,

6.deductionsMAP<STRING,FLOAT>.

addressSTRUCKstreet:STRING.district:STRING.city:STRING.province:STRING,ZIP:STRING>

8.)

9.ROWFORMATDELIMITED

10.FIELDSTERMINATEDBY'\001'

11.COLLECTINITEMSTERMINATEDBY'\002'

12.MAPKEYSTERMINATED'\003'

13.LINESTERMINATEDBY'\n'

14.STOREDAS

15.INPUTFORMAT'orgxIinkedin^haiwreovAvroContainerInputFormat'

16.OUTPUTFORMAT'orgxIinkedin^haivvreoxAvroContainerOutputFormat,;

修改表結(jié)構(gòu)：

1)、重命名表

Java代碼

I.ALTERTABLEtabIe_nameRENAMETOnew_tabIe_name;

只修改了表名，不會改變表結(jié)構(gòu)與數(shù)據(jù)存放得位置。

2)、修改列名、類型、位置及注釋

Java代碼

I.CREATETABLEIFNOTEXISTStest(aINT,bSTRING,cFLOAT);〃創(chuàng)建測試表

2.〃修改列名

3.TABLEtestCHANGEaa2INT;

4./*將a列得名字改為a2,并將其數(shù)據(jù)類型改為STRING,然后將之放在b列之后;修改

5.*后得表結(jié)構(gòu)為：bINT,a2STRING,cFLOAT

6.*/

7.ALTERTABLEtestCHANGEaa2STRINGAFTERb;

8./*

9.*將b列得名字改為b2,并將其數(shù)據(jù)類型由FLOAT改為DOUBLE,然后將之

10.*放在第一列;修改后得表結(jié)構(gòu)為：

11.*b2DOUBLE,aSTRING,cFLOAT

12.*/

13.ALTERTABLEtestCHANGEbb2DOUBLEFIRST;

列得改變只會修改Hive得元數(shù)據(jù)，而不改變實際得數(shù)據(jù)。

用戶應該確保元數(shù)據(jù)得定義與實際得數(shù)據(jù)結(jié)構(gòu)保持一致。

3）、增加新列

Java代碼

i.ALTERTABLEtestADDCOLOMNS(dINT,eSTRING);

4)、改變表得數(shù)據(jù)存儲格式

Java代碼

1.ALTERTABLEtestSETSEQUENCEFILE;

5)、添加分區(qū)Partition

Java代碼

1.ALTERTABLEstudentsADDPARTITION(ds='20130507',country='china')

2.LOCATION'/usr/test/data/test%txt'：

6)、刪除分區(qū)Partition

Java代碼

1.ALTERTABLEstudentsDROPPARTITION(ds='20130507',country='china');

7)、創(chuàng)建函數(shù)

Java代碼

I.CREATETEMPORARYFIUNCTIONaFuncasclass_name;

8)、刪除函數(shù)

Java代碼

I.DROPTEMPORARYFlUNCTIONaFunc;

9)、顯不表

Java代碼

1.SHOWTABLESstudents;

9)、顯示分區(qū)

Java代碼

1.SHOWPARTITIONSstudents;

本節(jié)繼續(xù)討論HiveQL,Hive查詢語言,如何向Hive表中添加數(shù)據(jù),操縱數(shù)據(jù)與從表中將數(shù)據(jù)提取到

文件系統(tǒng)。

之前已經(jīng)學習過Hive表得創(chuàng)建,本節(jié)主要關(guān)注向表中填充數(shù)據(jù)，使表中有數(shù)據(jù)可

供查詢。

先行創(chuàng)建本節(jié)要使用得表empIoyees:

Java代碼

1.CREATETABLEempIoyees(

2.nameSTRING.

3.salaryFLOAT,

4.subordinatesARRAY<STRING>MENT'下屬

5.deductionsMAP<STRING,FLOAT>MENT'扣費

addressSTRUKstreet:STRING,city:STRING,state:STRING,zip:INT>

7.)

8.PARTITIONEDBY(countrySTRING.stateSTRING);

向管理表(非外部表)中加載數(shù)據(jù)

由于Hive沒有行級得insert,update與deIete操縱，向表中加載數(shù)據(jù)得唯一方法

就就是“批量”加載數(shù)據(jù)。如下示例，想一個分區(qū)表中加載一批數(shù)據(jù)：

Java代碼

1.LOADDATALOCALINPATH'${env:HOME}/caIaforniaempIoyees'

2.OVERWRITEINTOTABLEemployees

3.PARTITION(country="US',state='CA');

在加載數(shù)據(jù)之前首先要要保證已經(jīng)創(chuàng)建有符合條件得分區(qū)。在這個示例中，數(shù)據(jù)

被加載后再Hive中得目錄為：

hdfs://master_server/user/hive/warehouse/mydb、

db/empIoyees/country=US/state=CA

這個示例需要注意幾點:

關(guān)鍵字OVERWRITE:加上該詞說明如果之前已經(jīng)箱蓋分區(qū)中加載過數(shù)據(jù)得話，

則之前得數(shù)據(jù)會首先被“清洗掉”，然后才加載新數(shù)據(jù);如果沒有加關(guān)鍵字OVERWRITE得話,如：

Java代碼

I.LOADDATALOCALINPATH'${env:HOME}/caIaforniaempIoyees'

2.INTOTABLEempIoyees

3.PARTITION(country='US',state='CA');

就不會將已有得數(shù)據(jù)清洗,而就是直接在原有數(shù)據(jù)后邊追加新得數(shù)據(jù)。

關(guān)鍵字LOCAL:表明就是從本地文件系統(tǒng)得文件夾中加載數(shù)據(jù)，如果不加LOCAL

關(guān)鍵字,則表明就是從HDFS系統(tǒng)得文件夾中加載數(shù)據(jù)：

Java代碼

1.LOADDATAINPATH'${env:HOME}/caIaforniaempIoyees'

2.INTOTABLEemployees

3.PARTITION(country='US',state='CA');

另外一點就就是,對于要指定要加載得文件得路徑,指定一個文件夾就是比較符

合常規(guī)得,這要比每次單獨指定文件夾中得一個具體得文件要好,這樣Hive會一次把指定文件夾

下得所有得位安全都拷貝到Hive倉庫中。

Hive在加載數(shù)據(jù)時并不會管要加載得數(shù)據(jù)就是否與表定義模式相匹配,但就是

會驗證文件格式，比如說,在表中第一得保存得數(shù)據(jù)文件為SEQUENCEFILE,那么加載后得文件就必

須為SEQUENCEFILE文件。

通過HiveQL查詢語句添加數(shù)據(jù)

INSERT語句可以讓用戶通過一個HiveQLQuery語句向Hive表中插入數(shù)據(jù)。瞧下

面得一個示例(這里假設(shè)已經(jīng)定義過表staged_empIoyees):

Java代碼

1.INSERTOVERWRITETABLEemployess

2.PARTTITION(country='US',state='OR')

3.SELECT*FROMstaged_empIoyeesse

4.WHEREsety='US'ANDse、st='OR';

OVERWRITE關(guān)鍵字，我們已經(jīng)討論過。但對于本示例來說，如果去掉OVERWRITE

或用INTO替換掉,如：

Java代碼

I.INSERTINTOTABLEemployess

2.PART!ITION(country='US',state='OR')

3.SELECT*FROMstaged_empIoyeesse

4.WHEREsety='US'ANDse、st='OR';

那么Hive就會將數(shù)據(jù)以“追加”得方式插入數(shù)據(jù)到employess表。

這個示例在一個場景中特別有用：數(shù)據(jù)已經(jīng)被保存在另外一個表中，如Hive得外

部表,然后用戶想把某些數(shù)據(jù)做最終得保存到分區(qū)表中；或者從原有得數(shù)據(jù)源表中將數(shù)據(jù)保存成

符合用得要求得不同得數(shù)據(jù)記錄得格式。

然而,如果staged_employees中數(shù)據(jù)特別大,有可能您需要執(zhí)行很多次這樣得插

入查詢導入數(shù)據(jù)得話，比如說美國部分得數(shù)據(jù)就要執(zhí)行65次之多。Hive提供了另外一種INSERT語

法,就是您只掃描一次表就可以將數(shù)據(jù)插入到對應得分區(qū)中：

Java代碼

1.ROMstaged_empIoyeesse

2.INSERTINTOTABLEemployess

3.PARTTITION(country='US',state='OR')

4.SELECT*WHEREsety='US'ANDse、st='OR';

5.INSERTINTOTABLEemployess

6.PARTTITION(country='US',state='CA')

7.SELECT*WHEREsety='US'ANDse、st='CA';

8.INSERTINTOTABLEemployess

9.PARTTITION(country='US',state='IL')

1().SELECT*WHEREsety='US'ANDse、st='IL';

11.、、、

這種INSERT語法也可以一次插入多個表。

動態(tài)分區(qū)添加數(shù)據(jù)

就上例來說,好友一個問題,那就就是如果有特別多得分區(qū)要創(chuàng)建得話，用戶不

得不要寫許多HiveQL語句！幸運得就是,Hive提供一個叫動態(tài)分區(qū)得功能,可以基于用戶得查詢參

數(shù)推斷要創(chuàng)建得分區(qū)。通過與動態(tài)分區(qū)相比較,之前我們討論得分區(qū)插入數(shù)據(jù)被稱為靜態(tài)分區(qū)數(shù)

據(jù)導入。

將上面得靜態(tài)分區(qū)作如下修改，就變成動態(tài)分區(qū)數(shù)據(jù)導入得示例：

Java代碼

1.INSERTOVERWRITETABLEemployees

2.PARTITION(country,state)

3.SELECT、、、，sety,se、st

4.FROMstaged_empIoyeesse

對于本例來說，Hive會通過SELECT語句中最后兩列sety與se、st得值來決定

employees表中得分區(qū)得key,contry與state°Hive在動態(tài)分區(qū)插入操作中特別強調(diào)源表得數(shù)據(jù)列

得值與要插入分區(qū)得key得值得位置關(guān)系，而不就是通過名字匹配，這也就是為什么在

staged_employees表中給國家與州定義不同得名字得原因。

假設(shè)staged_employees有100個國家與州得匹配對兒,那么執(zhí)行玩這個HiveQL語

句后,employees就會有100個國家分區(qū)！

用戶也可以混合使用動態(tài)分區(qū)與靜態(tài)分區(qū)數(shù)據(jù)插入,如下例中，我們就是用了一

個靜態(tài)得country(US)與動態(tài)得州得值：

Java代碼

1.INSERTOVERWRITETABLEemployees

2.PARTITION(country='US',state)

3.SELECT、、、，sety,se、st

4.FROMstaged_employeesse

5.WHEREsety='US';

要注意得就是,必須要把靜態(tài)分區(qū)得key放在動態(tài)分區(qū)得key之前。

默認請情況下,動態(tài)分區(qū)插入得功能就是被禁用得，當被激活后,Hive默認會工

作在“嚴格(strict)”模式下。在“嚴格(strict)”模式下，必須使用靜態(tài)分區(qū)與動態(tài)分區(qū)混合

使用得方式,這主要就是避免一些不好得數(shù)據(jù)查詢設(shè)計。

要使用動態(tài)模式，用戶首先要激活動態(tài)分區(qū)得一些參數(shù)設(shè)置：

Java代碼

hive>sethibe、exec、dynamic、partition=true;

2hive>sethibe、exec、dynamicvmode=nonstrict;

3hive>sethibe、exec、max、dynamicspartitionsxpernode=1000;

4

5hive>INSERTOVERWRITETABLEemployeesPARTITION(country,state)

6>SELECT、、、，sety,se、stFROMstaged_employeesse;

nonstrict可以完全由用戶得查詢參數(shù)來動態(tài)創(chuàng)建所有得分區(qū)。

在一個HiveQL中創(chuàng)建表與加載數(shù)據(jù)

在Hive中，用戶可以完全使用一條語句創(chuàng)建表并同時加載數(shù)據(jù)：

Java代碼

I.CREATETABLEca_employees

2.ASSELECTnamesalary,address

3.FROMemployees

4.WHEREstate='CA';

這種方法特別適用于在一個大表中提取一個子數(shù)據(jù)集得場景，這種功能不適合

外部表,因外外部表就是在定義時直接給其制定一個數(shù)據(jù)文件得路徑。

導出數(shù)據(jù)

之前講得都就是如何將數(shù)據(jù)加載到Hive表中，那么如何將表中數(shù)據(jù)導出表呢？

用戶可以使用INSERT、、、DIRECTORY、、、語句導出數(shù)據(jù)，示例如下：

Java代碼

I.INSERTOVERWRITELOCALDIRECTORY'tmp/ca_empIoyees'

2.SELECTname,salary,address

3.FROMemployees

4.WHEREstate='CA';

其中OVERWRITE與LOCAL得意義同上。

與加載數(shù)據(jù)一樣,用戶同樣可以在一個語句中將數(shù)據(jù)導出到多個文件夾：

Java代碼

I.FROMstaged_empIoyeesse

2.INSERTOVERWRITEDIRECTORY'/tmp/or_employees'

3.SELECT*FROMsety='US'ANDse、st='OR'

4.INSERTOVERWRITEDIRECTORY'/tmp/CA_empIoyees'

5.SELECT*FROMsety='US'ANDse、st='CA'

6.INSERTOVERWRITEDIRECTORY'/tmp/lL.employees'

7.SELECT*FROMsety='US'ANDse、st='IL'

前幾章已經(jīng)學習過Hive表得定義與數(shù)據(jù)操縱,本章我們開始學習HiveQL查詢。

SELECT、、、FROM、、、查詢

SELECT在SQL中就是一個投影操作。讓我們從新來瞧之前定義過得分區(qū)表

employees:

Java代碼

I.CREATETABLEemployees(

2.nameSTRING.

3.salaryFLOAT,

4.subordinatesARRAY<STRING>MENT'下屬’，

5.deductionsMAP<STRING,FLOAT>MENT'扣費

6.addressSTRUKstreet:STRING,city:STRING,state:STRING,zip:INT>

7.)

8.PARTITIONEDBY(countrySTRING.stateSTRING):

SELECT查詢:

Java代碼

1.hive>SELECTname,salaryFROMemployees;

2.JohnDoe100000,、0

3.MarySmith80000x0

4.ToddJones70000x0

5.Bi11King60000,、0

用戶也可以給FROM之后得表,視圖或子查詢起一個別名，如:

Java代碼

1.hive>SELECTe、name,e%salaryFROMemployeese:

上面兩個HiveQL語句就是相同得,給表起別名在JOIN操作中特別有用。

下面我們來瞧如何查詢empIoyees表中得集合類型得數(shù)據(jù)。我們先瞧一下如何查

詢ARRAY類型得數(shù)據(jù),如empIoyees表得下屬“subordinates”

Java代碼

1.hive>SELECTname,subordinatesFROMemployees;

2.JohnDoe["MarySmith","ToddJones"]

3.MarySmith["BillKing'1]

4.ToddJones[]

5.Billking[]

再瞧MAP類型得查詢，如“deductions”:

Java代碼

hive>SELECTname,deductionsFROMempIoyees;

2.JohnDoe{"FederaITaxes":0x2,"StateTaxes":0x05,"Insurance":0x1)

3.MarySmith{"Federa1Taxes":0>2,"StateTaxes":0、05,"Insurance":0%1)

4.ToddJones{"FederalTaxes":0、15,"StateTaxes":0、03,"Insurance":051)

5.Bi11King{"Federa1Taxes":01.15,"StateTaxes":Ox03,"Insurance":0x1}

再瞧STRUCT類型得查詢，如“address”:

Java代碼

I.hive>SELECTname,addressFROMempIoyees;

2.JohnDoe{"Street":"1MichignAvex","city":"Chicago","State"IL","ZIP":60600)

3.MarySmith{"Street":"100OntarioStt","city":"Chicago","State":nIL","ZIP":60601)

H

4.ToddJones{"Street":"200ChicagoAvex","city":"OakPark","State":IL","ZIP":60700)

5.BiIIKing{"Street":"300ObscureDrxM,ncity":"0bscuria","State"IL","ZIP":60100}

接下來我們再瞧如何查瞧集合性屬性字段中得數(shù)據(jù):

Java代碼

1.hive>SELECTname,subordinates[0],deductions["StateTaxes"],address、

cityFROMemployees;

2.JohnDoeMarySmithOx05Chicago

3.MarySmithBi11KingOx05Chicago

4.ToddJonesNULL0\03OakPark

5.Bi11KingNULLOx03Obscuria

使用正則表達式查詢符合條件得列

在Hive查詢中，用戶可以使用正則表達式查詢符合條件得列,下面得實例中就就

是使用正則表達式得使用用例，可以查詢到symbol列與所有以“price”開頭得列：

Java代碼

1.hive>SELECTsymbol,'prices*'FROMstocks;

2.AAPL195、69197、88194、0194、12194、12

3.AAPL192、63196、0190、85195、46195、46

4.AAPL196、73198、37191、57192、05192、05

5.AAPL195、17200、2194、42199、23199、23

6.AAPL195、91196、32193>38195、86195、86

7.、、、

列計算

在HiveQL中，用戶不但可以從表中查詢某些列,還可以通過函數(shù)或數(shù)學表達式來

計算列得值。例如,我們可以在employees表中查詢雇員得姓名，薪水,聯(lián)邦稅百分百及其她列得

值：

Java代碼

1.hive>SELECTupper(name),salary,deductions[nFederaITaxes"],

2.>round(salary*(1deductions["FederaITaxes"]))

3.>FROMemployees;

4.JOHNDOE100000x00、280000

5.MARYSMITH80000x00、264000

6.TODDJONES70000v0Ox1559500

7.BILLKING60000v00、1551000

Hive就是使用JAVA寫得開源軟件，在函數(shù)或數(shù)學表達式來計算列得值時類型轉(zhuǎn)

型與JAVA得轉(zhuǎn)型相同。

聚合函數(shù)

要在HiveQL查詢中使用聚合函數(shù)，必須先將hive、map、aggr■配置參數(shù)設(shè)置為

true,舉例如下：

Java代碼

1.hive>SEThive、map、aggr=true;

2.hibe>SELECTcount(*),avg(saIary)FROMemployees;

但就是將

Java代碼

I.hive、map、aggr

設(shè)置為true會占用更多得內(nèi)存。

LIMIT

一次典型得HiveQL查詢可能會返回所有符合條件得數(shù)據(jù)記錄，但就是LIMIT關(guān)鍵

字可以限制返回得記錄得條數(shù)：

Java代碼

hive>SELECTupper(name),salary,deductions["FederalTaxes"],

2.>round(salary*(1deductions["FederaITaxes"]))

3.>FROMempIoyees

4.>LIMIT2;

5.JOHNDOE100000x00、280000

6.MARYSMITH80000>00、264000

給列奇別名

Java代碼

I.hive>SELECTupper(name),saIary,deductions["FederaITaxes"]AS

2>fed_taxes,round(salary*(1deductions["FederaITaxes"]))AS

3>saIary_minus_fed_taxes

4>FROMempIoyees

5>LIMIT2;

6.JOHNDOE100000x00、280000

7.MARYSMITH80000、00、264000

子查詢

給列起別名特別適合與子查詢中得列，讓我們將上個查詢示例修改為子查詢得

使用用例：

Java代碼

1.hive>FROM(

2>SELECTupper(name).salary,deductions["FederaITaxes"]AS

3>fed_taxes,round(salary*(1deductions["FederaITaxes"]))

4>ASsaIary_minus_fed_taxes

5>FROMemployees

6>)e

>SELECTe、name,exsaIary_minus_fed_taxes

>WHEREe、saIary_minus_fed_taxes>70000;

9.JOHNDOE100000s00、280000

CASE、、、WHEN、、、THEN語句

CASE、、、WHEN、、、THEN向標準得SQL語句中一樣使用在SELECT列中，對某一

個列得返回值做判斷,示例如下：

Java代碼

I.hive>SELECTname,salary,

2.>CASE

3.>WHENsalary<50000、0THEN1low'

4.>WHENsalary>=50000、0ANDsalary<70000x0THEN'middl

5.>WHENsalary>=70000、0ANDsalay<100000、0THEN'high'

6.ELSE'veryhigh'

7.>ENDASbracketFROMemployees;

8.JohnDoe100000x0veryhigh

9.MarySmith80000x0high

10.ToddJones70000x0high

11.BillKing60000、0middle

12.BossMan200000K0veryhigh

13.FredFinance150000%0veryhigh

14.StcyAccountant60000、0middle

WHERE過濾條件

SELECT決定返回哪些數(shù)據(jù)列,而WHERE決定返回那些符合條件得數(shù)據(jù)：

Java代碼

I.hive>SELECTname,salary,deductions["FederalTaxes"],

2>salary*(1deductions["FederaITaxes"])

3>FROMempIoyees

4>WHEREround(salary*(1deductions["FederaITaxes"]))>

5>70000;

6.ohnDoe100000x00、280000、0

該示例有一個問題，那就就是salary*(1deductions["FederaITaxes"])分

別在SELECT部分與WHERE部分都執(zhí)行了，性能上不就是多優(yōu)化。那么，對salary*(1

deductions["FederalTaxes”])使用別名能否消除這種沖突呢？，不幸得就是這就是無效得：

Java代碼

hive>SELECTname,salary,deductions["FederaITaxes"],

2>salary*(1deductions["FederaITaxes"])AS

3>saIary_minus_fed_taxes

4>FROMemployees

5>WHEREround(saIary_minus_fed_taxes)>70000;

6.FAILED:Errorinsemanticanalysis:Line4:13Invalidtablealiasor

coIomnreferencesaIary_minus_fed_taxes':(possiblecoIomnnames

8.are:name,salary,subordinates,deductions,address)

如錯誤信息中所說,用戶不能在WHERE部分中引用列得別名，那么我們就是否可

以使用其她辦法來消除這種沖突呢？答案就是使用子查詢：

Java代碼

i.hive>SELECTe、*FROM

2>(SELECTname,salary,deductions["FederaITaxes"]ASded.

3>salary*(1deductions["FederaITaxes"])AS

4>saIary_minus_fed_taxes

5>FROMempIoyees)e

6>WHEREround(saIary_minus_fed_taxes)>70000;

浮點比較陷阱

在WHERE查詢條件中：在比較不同類型得數(shù)值(如FLOATvsDOUBLE)時，會引發(fā)浮

點比較陷阱。

瞧下面得HiveQL語句，本來只想要查詢FederalTaxes>0、2,但就是返回結(jié)果

如下：

Java代碼

I.hive>SELECTname,saIary,deductions['FederaITaxes']

>FROMempIoyeesWHEREdeductions['FederaITaxes']>0、2;

3.JohnDoe100000x00、2

4.MarySmith80000x00、2

5.BossMan200000v00、3

6.FredFinance150000s0(0、3

我們發(fā)現(xiàn),為什不符合過濾條件(deductions('FederaITaxes')二二0、2)得結(jié)

果也返回了？！這就就是浮點比較陷阱引發(fā)得。

那么如何來避免這個陷阱呢？那就要將0、2強制轉(zhuǎn)型了：

Java代碼

1.hive>SELECTname,salary,deductions['FederalTaxes']

2.>FROMemployees

3.>WHEREdeductions['FederaITaxes']>cast(0v2ASFLOAT);

4.BossMan200000v0Ox3

5.FredFinance150000x00、3

LIKE與RLIKE

LIKE標識模糊查詢:

Java代碼

1.hive>SELECTname,address^streetFROMempIoyeesWHEREaddress、streetLIKE'%Avex;

2.JohnDoe1MichiganAve、

3.ToddHones200ChicagoAve、

4.

5.hive>SELECTname,addressstreetFROMemployeesWHEREaddress

streetLIKE'%Chi%';

6.ToddHones200ChicagoAve、

RLIKE就是使用正則表達式:

Java代碼

1.hive>SELECTname,addressvstreetFROMemployees

2.>WHEREaddress^streetRLIKE\*(Chicago|Ontario)x*,: