Hive入門到剖析（二）

5 Hive參數(shù)

hive.exec.max.created.files

說明：所有hive運(yùn)行的map與reduce任務(wù)可以產(chǎn)生的文件的和

默認(rèn)值:100000

hive.exec.dynamic.partition

說明：是否為自動分區(qū)

默認(rèn)值：false

hive.mapred.reduce.tasks.speculative.execution

說明：是否打開推測執(zhí)行

默認(rèn)值：true

hive.input.format

說明：Hive默認(rèn)的input format

默認(rèn)值： org.apache.hadoop.hive.ql.io.CombineHiveInputFormat

如果有問題可以使用org.apache.hadoop.hive.ql.io.HiveInputFormat

hive.exec.counters.pull.interval

說明：Hive與JobTracker拉取counter信息的時間

默認(rèn)值：1000ms

hive.script.recordreader

說明：使用腳本時默認(rèn)的讀取類

默認(rèn)值： org.apache.hadoop.hive.ql.exec.TextRecordReader

hive.script.recordwriter

說明：使用腳本時默認(rèn)的數(shù)據(jù)寫入類

默認(rèn)值： org.apache.hadoop.hive.ql.exec.TextRecordWriter

hive.mapjoin.check.memory.rows

說明： 內(nèi)存里可以存儲數(shù)據(jù)的行數(shù)

默認(rèn)值： 100000

hive.mapjoin.smalltable.filesize

說明：輸入小表的文件大小的閥值，如果小于該值，就采用普通的join

默認(rèn)值： 25000000

hive.auto.convert.join

說明：是不是依據(jù)輸入文件的大小，將Join轉(zhuǎn)成普通的Map Join

默認(rèn)值： false

hive.mapjoin.followby.gby.localtask.max.memory.usage

說明：map join做group by 操作時，可以使用多大的內(nèi)存來存儲數(shù)據(jù)，如果數(shù)據(jù)太大，則不會保存在內(nèi)存里

默認(rèn)值：0.55

hive.mapjoin.localtask.max.memory.usage

說明：本地任務(wù)可以使用內(nèi)存的百分比

默認(rèn)值： 0.90

hive.heartbeat.interval

說明：在進(jìn)行MapJoin與過濾操作時，發(fā)送心跳的時間

默認(rèn)值1000

hive.merge.size.per.task

說明： 合并后文件的大小

默認(rèn)值： 256000000

hive.mergejob.maponly

說明： 在只有Map任務(wù)的時候 合并輸出結(jié)果

默認(rèn)值： true

hive.merge.mapredfiles

默認(rèn)值： 在作業(yè)結(jié)束的時候是否合并小文件

說明： false

hive.merge.mapfiles

說明：Map-Only Job是否合并小文件

默認(rèn)值：true

hive.hwi.listen.host

說明：Hive UI 默認(rèn)的host

默認(rèn)值：0.0.0.0

hive.hwi.listen.port

說明：Ui監(jiān)聽端口

默認(rèn)值：9999

hive.exec.parallel.thread.number

說明：hive可以并行處理Job的線程數(shù)

默認(rèn)值：8

hive.exec.parallel

說明：是否并行提交任務(wù)

默認(rèn)值：false

hive.exec.compress.output

說明：輸出使用壓縮

默認(rèn)值： false

hive.mapred.mode

說明： MapReduce的操作的限制模式，操作的運(yùn)行在該模式下沒有什么限制

默認(rèn)值： nonstrict

hive.join.cache.size

說明： join操作時，可以存在內(nèi)存里的條數(shù)

默認(rèn)值： 25000

hive.mapjoin.cache.numrows

說明： mapjoin 存在內(nèi)存里的數(shù)據(jù)量

默認(rèn)值：25000

hive.join.emit.interval

說明： 有連接時Hive在輸出前，緩存的時間

默認(rèn)值： 1000

hive.optimize.groupby

說明：在做分組統(tǒng)計時，是否使用bucket table

默認(rèn)值： true

hive.fileformat.check

說明：是否檢測文件輸入格式

默認(rèn)值：true

hive.metastore.client.connect.retry.delay

說明： client 連接失敗時,retry的時間間隔

默認(rèn)值：1秒

hive.metastore.client.socket.timeout

說明:  Client socket 的超時時間

默認(rèn)值：20秒

mapred.reduce.tasks

默認(rèn)值：-1

說明：每個任務(wù)reduce的默認(rèn)值

 -1 代表自動根據(jù)作業(yè)的情況來設(shè)置reduce的值

hive.exec.reducers.bytes.per.reducer

默認(rèn)值： 1000000000 （1G）

說明：每個reduce的接受的數(shù)據(jù)量

    如果送到reduce的數(shù)據(jù)為10G,那么將生成10個reduce任務(wù)

hive.exec.reducers.max

默認(rèn)值：999

說明： reduce的最大個數(shù)  

hive.exec.reducers.max

默認(rèn)值：999

說明： reduce的最大個數(shù)

hive.metastore.warehouse.dir

默認(rèn)值：/user/hive/warehouse

說明： 默認(rèn)的數(shù)據(jù)庫存放位置

hive.default.fileformat

默認(rèn)值：TextFile

說明： 默認(rèn)的fileformat

hive.map.aggr

默認(rèn)值：true

說明： Map端聚合，相當(dāng)于combiner

hive.exec.max.dynamic.partitions.pernode

默認(rèn)值：100

說明：每個任務(wù)節(jié)點(diǎn)可以產(chǎn)生的最大的分區(qū)數(shù)

hive.exec.max.dynamic.partitions

默認(rèn)值：1000

說明： 默認(rèn)的可以創(chuàng)建的分區(qū)數(shù)

hive.metastore.server.max.threads

默認(rèn)值：100000

說明： metastore默認(rèn)的最大的處理線程數(shù)

hive.metastore.server.min.threads

默認(rèn)值：200

說明： metastore默認(rèn)的最小的處理線程數(shù)

6 Hive高級編程

6.1 產(chǎn)生背景

為了滿足客戶個性化的需求，Hive被設(shè)計成一個很開放的系統(tǒng)，很多內(nèi)容都支持用戶定制，包括：

文件格式：Text File，Sequence File

內(nèi)存中的數(shù)據(jù)格式： Java Integer/String, Hadoop  IntWritable/Text

用戶提供的 map/reduce 腳本：不管什么語言，利用stdin/stdout 傳輸數(shù)據(jù)

1、用戶自定義函數(shù)

雖然Hive提供了很多函數(shù)，但是有些還是難以滿足我們的需求。因此Hive提供了自定義函數(shù)開發(fā)

自定義函數(shù)包括三種UDF、UADF、UDTF

UDF(User-Defined-Function)

UDAF（User- Defined Aggregation Funcation）

UDTF(User-DefinedTable-Generating Functions)  用來解決 輸入一行輸出多行(On-to-many maping) 的需求。

2、HIVE中使用定義的函數(shù)的三種方式

在HIVE會話中add 自定義函數(shù)的jar文件，然后創(chuàng)建function，繼而使用函數(shù)

在進(jìn)入HIVE會話之前先自動執(zhí)行創(chuàng)建function，不用用戶手工創(chuàng)建

把自定義的函數(shù)寫到系統(tǒng)函數(shù)中，使之成為HIVE的一個默認(rèn)函數(shù)，這樣就不需要create temporary function。

6.2 UDF

UDF(User-Defined-Function)：UDF函數(shù)可以直接應(yīng)用于select語句，對查詢結(jié)構(gòu)做格式化處理后，再輸出內(nèi)容。

編寫UDF函數(shù)的時候需要注意一下幾點(diǎn)

A、自定義UDF需要繼承org.apache.hadoop.hive.ql.UDF

B、需要實(shí)現(xiàn)evaluate函數(shù)

C、evaluate函數(shù)支持重載

D、UDF只能實(shí)現(xiàn)一進(jìn)一出的操作，如果需要實(shí)現(xiàn)多進(jìn)一出，則需要實(shí)現(xiàn)UDAF。

UDF用法代碼示例

import org.apache.Hadoop.hive.ql.exec.UDF  
   public  class Helloword  extends UDF{  
      public Stringevaluate(){  
           return"hello world!";  
     }  
 
      public Stringevaluate(String str){  
           return"hello world: " + str;  
     }  
}

開發(fā)步驟

開發(fā)代碼

把程序打包放到目標(biāo)機(jī)器上去

進(jìn)入hive客戶端

添加jar包：hive>add jar/run/jar/udf_test.jar;

創(chuàng)建臨時函數(shù)：hive>CREATE TEMPORARY FUNCTION my_add AS'com.hive.udf.Add ‘

查詢HQL語句：

SELECT my_add (8, 9) FROM scores;

SELECT my_add (scores.math, scores.art) FROM scores;

銷毀臨時函數(shù)：hive> DROP TEMPORARY FUNCTION my_add ;

細(xì)節(jié)

在使用UDF的時候，會自動進(jìn)行類型轉(zhuǎn)換，例如：

SELECT my_add (8,9.1) FROM scores;

結(jié)果是17.1，UDF將類型為Int的參數(shù)轉(zhuǎn)化成double。類型的飲食轉(zhuǎn)換是通過UDFResolver來進(jìn)行控制的

6.3 UDAF

UDAF

Hive查詢數(shù)據(jù)時，有些聚類函數(shù)在HQL沒有自帶，需要用戶自定義實(shí)現(xiàn)

用戶自定義聚合函數(shù): Sum, Average…… n –1

UDAF（User- Defined Aggregation Funcation）

用法

一下兩個包是必須的import org.apache.hadoop.hive.ql.exec.UDAF和org.apache.hadoop.hive.ql.exec.UDAFEvaluator

開發(fā)步驟

函數(shù)類需要繼承UDAF類，內(nèi)部類Evaluator實(shí)UDAFEvaluator接口

Evaluator需要實(shí)現(xiàn) init、iterate、terminatePartial、merge、terminate這幾個函數(shù)

a）init函數(shù)實(shí)現(xiàn)接口UDAFEvaluator的init函數(shù)。

b）iterate接收傳入的參數(shù)，并進(jìn)行內(nèi)部的輪轉(zhuǎn)。其返回類型為boolean。

c）terminatePartial無參數(shù)，其為iterate函數(shù)輪轉(zhuǎn)結(jié)束后，返回輪轉(zhuǎn)數(shù)據(jù)，terminatePartial類似于hadoop的Combiner。

d）merge接收terminatePartial的返回結(jié)果，進(jìn)行數(shù)據(jù)merge操作，其返回類型為boolean。

e）terminate返回最終的聚集函數(shù)結(jié)果。

執(zhí)行步驟

執(zhí)行求平均數(shù)函數(shù)的步驟

a）將java文件編譯成Avg_test.jar。

b）進(jìn)入hive客戶端添加jar包：

hive>add jar /run/jar/Avg_test.jar。

c）創(chuàng)建臨時函數(shù)：

hive>create temporary function avg_test 'hive.udaf.Avg';

d）查詢語句：

hive>select avg_test(scores.math) from scores;

e）銷毀臨時函數(shù)：

hive>drop temporary function avg_test;

UDAF代碼示例

public class MyAvg extends UDAF {
 
public static class AvgEvaluator implements UDAFEvaluator {
}
public void init() {}
public boolean iterate(Double o) {}
public AvgState terminatePartial() {}
public boolean terminatePartial(Double o) { }
public Double terminate() {}
 
}

 6.4 UDTF

UDTF：UDTF(User-Defined Table-GeneratingFunctions)  用來解決 輸入一行輸出多行(On-to-many maping) 的需求。

開發(fā)步驟

必須繼承org.apache.Hadoop.hive.ql.udf.generic.GenericUDTF

實(shí)現(xiàn)initialize, process, close三個方法

UDTF首先會調(diào)用initialize方法，此方法返回UDTF的返回行的信息（返回個數(shù)，類型），初始化完成后，會調(diào)用process方法，對傳入的參數(shù)進(jìn)行處理，可以通過forword()方法把結(jié)果返回.

最后close()方法調(diào)用，對需要清理的方法進(jìn)行清理

使用方法

UDTF有兩種使用方法，一種直接放到select后面，一種和lateral view一起使用

直接select中使用：select explode_map(properties) as(col1,col2) from src;

不可以添加其他字段使用：select a, explode_map(properties) as (col1,col2) from src

不可以嵌套調(diào)用：select explode_map(explode_map(properties)) from src

不可以和group by/cluster by/distribute by/sort by一起使用：select explode_map(properties) as (col1,col2) from src group bycol1, col2

和lateral view一起使用：select src.id,mytable.col1, mytable.col2 from src lateral view explode_map(properties)mytable as col1, col2;

此方法更為方便日常使用。執(zhí)行過程相當(dāng)于單獨(dú)執(zhí)行了兩次抽取，然后union到一個表里。

lateral view

語法:lateralView: LATERAL VIEW udtf(expression) tableAlias AScolumnAlias (',' columnAlias)* fromClause: FROM baseTable (lateralView)* 

LateralView用于UDTF(user-defined table generating functions)中將行轉(zhuǎn)成列，例如explode().

目前Lateral View不支持有上而下的優(yōu)化。如果使用Where子句，查詢可能將不被編譯。

解決方法見：在查詢之前執(zhí)行set hive.optimize.ppd=false;

例子

pageAds。它有兩個列

SELECT pageid, adid FROM pageAds LATERAL VIEWexplode(adid_list) adTable AS adid;

將輸出如下結(jié)果

string pageid int adid

"front_page" 1

…….

“contact_page"3

代碼示例

public class MyUDTF extends GenericUDTF{
public StructObjectInspector initialize(ObjectInspector[] args) {}
public void process(Object[] args) throws HiveException { }
}

7 HiveQL

7.1 ＤＤＬ

1、DDL功能

建表

刪除表

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

創(chuàng)建／刪除視圖

創(chuàng)建數(shù)據(jù)庫

顯示命令

增加分區(qū)、刪除分區(qū)

重命名表

修改列的名字、類型、位置、注釋

增加/更新列

增加表的元數(shù)據(jù)信息

2、建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  [(col_namedata_type [COMMENT col_comment], ...)]
  [COMMENTtable_comment]
  [PARTITIONED BY(col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY(col_name, col_name, ...)
  [SORTED BY(col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [ROW FORMATrow_format]
  [STORED ASfile_format]
  [LOCATIONhdfs_path]

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

EXTERNAL 關(guān)鍵字可以讓用戶創(chuàng)建一個外部表，在建表的同時指定一個指向?qū)嶋H數(shù)據(jù)的路徑（LOCATION）

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

COMMENT可以為表與字段增加描述

ROW FORMAT

DELIMITED[FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYSTERMINATED BY char] [LINES TERMINATED BY char]
   | SERDEserde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, ...)]

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

STORED AS
           SEQUENCEFILE
            |TEXTFILE
            |RCFILE   
            |INPUTFORMAT input_format_classname OUTPUTFORMAT             output_format_classname

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

建立外部表

CREATE EXTERNAL TABLE page_view(viewTime INT, useridBIGINT,
     page_urlSTRING, referrer_url STRING,
     ip STRINGCOMMENT 'IP Address of the User',
     country STRINGCOMMENT 'country of origination')
 COMMENT 'This isthe staging page view table'
 ROW FORMATDELIMITED FIELDS TERMINATED BY '\054'
 STORED AS TEXTFILE
 LOCATION'<hdfs_location>';

建分區(qū)表

CREATE TABLE par_table(viewTime INT, userid BIGINT,
     page_urlSTRING, referrer_url STRING,
     ip STRINGCOMMENT 'IP Address of the User')
 COMMENT 'This isthe page view table'
 PARTITIONEDBY(date STRING, pos STRING)
ROW FORMAT DELIMITED ‘\t’
   FIELDSTERMINATED BY '\n'
STORED AS SEQUENCEFILE;

建Bucket表

CREATE TABLE par_table(viewTime INT, userid BIGINT,
     page_urlSTRING, referrer_url STRING,
     ip STRINGCOMMENT 'IP Address of the User')
 COMMENT 'This isthe page view table'
 PARTITIONEDBY(date STRING, pos STRING)
 CLUSTEREDBY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
 ROW FORMAT DELIMITED‘\t’
   FIELDSTERMINATED BY '\n'
STORED AS SEQUENCEFILE;

復(fù)制一個空表

CREATE TABLE empty_key_value_store
LIKE key_value_store;

刪除表

DROP TABLE table_name

增加、刪除分區(qū)

增加

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec[ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
     partition_spec:
  : PARTITION(partition_col = partition_col_value, partition_col = partiton_col_value, ...)

刪除

ALTER TABLE table_name DROP partition_spec,partition_spec,...

重命名表

ALTER TABLE table_name RENAME TO new_table_name

修改列的名字、類型、位置、注釋

ALTER TABLE table_name CHANGE [COLUMN] col_old_namecol_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

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

增加/更新列

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

ADD是代表新增一字段，字段位置在所有列后面(partition列前)

     REPLACE則是表示替換表中所有字段。

增加表的元數(shù)據(jù)信息

ALTER TABLE table_name SET TBLPROPERTIES table_propertiestable_properties:
        :[property_name = property_value…..]

用戶可以用這個命令向表中增加metadata

改變表文件格式與組織

ALTER TABLE table_name SET FILEFORMAT file_format;
ALTER TABLE table_name CLUSTERED BY(userid) SORTEDBY(viewTime) INTO num_buckets BUCKETS;

這個命令修改了表的物理存儲屬性

創(chuàng)建／刪除視圖

CREATE VIEW [IF NOT EXISTS] view_name [ (column_name[COMMENT column_comment], ...) ][COMMENT view_comment][TBLPROPERTIES(property_name = property_value, ...)] AS SELECT

增加視圖

如果沒有提供表名，視圖列的名字將由定義的SELECT表達(dá)式自動生成

如果修改基本表的屬性，視圖中不會體現(xiàn)，無效查詢將會失敗

視圖是只讀的，不能用LOAD/INSERT/ALTER

DROP VIEW view_name

刪除視圖

創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE name

顯示命令

show tables;
show databases;
show partitions ;
show functions
describe extended table_name dot col_name

7.2 ＤＭＬ

1、DML功能

向數(shù)據(jù)表內(nèi)加載文件

將查詢結(jié)果插入到Hive表中

0.8新特性 insert into

2、向數(shù)據(jù)表內(nèi)加載文件

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTOTABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

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

filepath

相對路徑，例如：project/data1

絕對路徑，例如： /user/hive/project/data1

包含模式的完整 URI，例如：

hdfs://namenode:9000/user/hive/project/data1

3、向數(shù)據(jù)表內(nèi)加載文件

加載的目標(biāo)可以是一個表或者分區(qū)。如果表包含分區(qū)，必須指定每一個分區(qū)的分區(qū)名

filepath 可以引用一個文件（這種情況下，Hive 會將文件移動到表所對應(yīng)的目錄中）或者是一個目錄（在這種情況下，Hive 會將目錄中的所有文件移動至表所對應(yīng)的目錄中）

4、LOCAL關(guān)鍵字

指定了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

    如果 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）所指定的路徑中

5、OVERWRITE

指定了OVERWRITE

目標(biāo)表（或者分區(qū)）中的內(nèi)容（如果有）會被刪除，然后再將 filepath 指向的文件/目錄中的內(nèi)容添加到表/分區(qū)中。

如果目標(biāo)表（分區(qū)）已經(jīng)有一個文件，并且文件名和 filepath 中的文件名沖突，那么現(xiàn)有的文件會被新文件所替代。

6、將查詢結(jié)果插入Hive表

將查詢結(jié)果插入Hive表

將查詢結(jié)果寫入HDFS文件系統(tǒng)

基本模式

INSERT OVERWRITE TABLE tablename1 [PARTITION(partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

多插入模式

 FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION(partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...]select_statement2] ...

自動分區(qū)模式

 INSERT OVERWRITETABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...)select_statement FROM from_statement

7、將查詢結(jié)果寫入HDFS文件系統(tǒng)

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ...FROM ...
        FROMfrom_statement
        INSERTOVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
     [INSERTOVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]

數(shù)據(jù)寫入文件系統(tǒng)時進(jìn)行文本序列化，且每列用^A 來區(qū)分，\n換行

8、INSERT INTO

INSERT INTO  TABLEtablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1FROM from_statement

7.3 HiveQL 查詢操作

1、SQL操作

基本的Select 操作

基于Partition的查詢

Join

2、基本的Select 操作

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[   CLUSTER BYcol_list
  | [DISTRIBUTE BYcol_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]

使用ALL和DISTINCT選項(xiàng)區(qū)分對重復(fù)記錄的處理。默認(rèn)是ALL，表示查詢所有記錄。DISTINCT表示去掉重復(fù)的記錄

Where 條件

類似我們傳統(tǒng)SQL的where 條件

目前支持 AND,OR ,0.9版本支持between

IN, NOT IN

不支持EXIST ,NOT EXIST

ORDER BY與SORT BY的不同

ORDER BY 全局排序，只有一個Reduce任務(wù)

SORT BY 只在本機(jī)做排序

3、Limit

Limit 可以限制查詢的記錄數(shù)

SELECT * FROM t1 LIMIT 5

實(shí)現(xiàn)Top k 查詢

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

SET mapred.reduce.tasks = 1

  SELECT * FROMtest SORT BY amount DESC LIMIT 5

REGEX Column Specification

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

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

基于Partition的查詢

一般 SELECT 查詢會掃描整個表，使用PARTITIONED BY 子句建表，查詢就可以利用分區(qū)剪枝（input pruning）的特性

Hive 當(dāng)前的實(shí)現(xiàn)是，只有分區(qū)斷言出現(xiàn)在離 FROM 子句最近的那個WHERE 子句中，才會啟用分區(qū)剪枝

4、Join

Syntax
join_table:
   table_referenceJOIN table_factor [join_condition]
  | table_reference{LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
  | table_referenceLEFT SEMI JOIN table_reference join_condition
 
table_reference:
    table_factor
  | join_table
 
table_factor:
    tbl_name[alias]
  | table_subqueryalias
  | (table_references )
 
join_condition:
    ONequality_expression ( AND equality_expression )*
 
equality_expression:
    expression =expression

Hive 只支持等值連接（equality joins）、外連接（outer joins）和（left semi joins）。Hive 不支持所有非等值的連接，因?yàn)榉堑戎颠B接非常難轉(zhuǎn)化到 map/reduce 任務(wù)

LEFT，RIGHT和FULL OUTER關(guān)鍵字用于處理join中空記錄的情況

LEFT SEMI JOIN 是 IN/EXISTS 子查詢的一種更高效的實(shí)現(xiàn)

join 時，每次 map/reduce 任務(wù)的邏輯是這樣的：reducer 會緩存 join 序列中除了最后一個表的所有表的記錄，再通過最后一個表將結(jié)果序列化到文件系統(tǒng)

實(shí)踐中，應(yīng)該把最大的那個表寫在最后

5、join 查詢時，需要注意幾個關(guān)鍵點(diǎn)

只支持等值join

SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b
    ON (a.id = b.idAND a.department = b.department)

可以 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ù)

LEFT，RIGHT和FULL OUTER

例子：SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

如果你想限制 join 的輸出，應(yīng)該在 WHERE 子句中寫過濾條件——或是在 join 子句中寫。

容易混淆的問題是表分區(qū)的情況

SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON(c.key=d.key)

  WHEREa.ds='2010-07-07' AND b.ds='2010-07-07‘

如果 d 表中找不到對應(yīng) c 表的記錄，d 表的所有列都會列出 NULL，包括ds 列。也就是說，join 會過濾 d 表中不能找到匹配 c 表 join key 的所有記錄。這樣的話，LEFT OUTER 就使得查詢結(jié)果與 WHERE 子句無關(guān)

解決辦法

SELECT c.val, d.val FROM c LEFT OUTER JOIN d
  ON (c.key=d.keyAND d.ds='2009-07-07' AND c.ds='2009-07-07')

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 LEFT SEMIJOIN b on (a.key = b.key)

UNION ALL

用來合并多個select的查詢結(jié)果，需要保證select中字段須一致

select_statement UNION ALL select_statement UNION ALLselect_statement ...

7.4 從SQL到HiveQL應(yīng)該轉(zhuǎn)變的幾個習(xí)慣

1、Hive不支持等值連接

SQL中對兩表內(nèi)聯(lián)可以寫成：

select * from dual a,dual b where a.key = b.key;

Hive中應(yīng)為

select * from dual a join dual b on a.key = b.key;

2、分號字符

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

select concat(key,concat(';',key)) from dual;

但HiveQL在解析語句時提示：

        FAILED:Parse Error: line 0:-1 mismatched input '<EOF>' expecting ) in functionspecification

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

select concat(key,concat('\073',key)) from dual;

3、IS [NOT]NULL

SQL中null代表空值, 值得警惕的是, 在HiveQL中String類型的字段若是空(empty)字符串, 即長度為0, 那么對它進(jìn)行ISNULL的判斷結(jié)果是False。