一、建立表

在官方的wiki裡，example是這樣的：

Sql程式碼

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_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 FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

[ROW FORMAT DELIMITED]關鍵字，是用來設定建立的表在載入資料的時候，支援的列分隔符；
[STORED AS file_format]關鍵字是用來設定載入資料的資料型別。Hive本身支援的檔案格式只有：Text File，Sequence File。如果檔案資料是純文字，可以使用 [STORED AS TEXTFILE]。如果資料需要壓縮，使用 [STORED AS SEQUENCE] 。通常情況，只要不需要儲存序列化的物件，我們預設採用[STORED AS TEXTFILE]。

那麼我們建立一張普通的hive表，hive sql就如下：

Sql程式碼

CREATE TABLE test_1(id INT, name STRING, city STRING) SORTED BY TEXTFILE ROW FORMAT DELIMITED‘\t’

其中，hive支援的欄位型別，並不多，可以簡單的理解為數字型別和字串型別，詳細列表如下：

Sql程式碼

TINYINT
SMALLINT
INT
BIGINT
BOOLEAN
FLOAT
DOUBLE
STRING

Hive的表，與普通關係型資料庫，如mysql在表上有很大的區別，所有hive的表都是一個檔案，它是基於Hadoop的檔案系統來做的。

hive總體來說可以總結為三種不同型別的表。

1. 普通表

普通表的建立，如上所說，不講了。其中，一個表，就對應一個表名對應的檔案。

2. 外部表

EXTERNAL 關鍵字可以讓使用者建立一個外部表，在建表的同時指定一個指向實際資料的路徑（LOCATION），Hive 建立內部表時，會將資料移動到資料倉儲指向的路徑；若建立外部表，僅記錄資料所在的路徑，不對資料的位置做任何改變。在刪除表的時候，內部表的後設資料和資料會被一起刪除，而外部表只刪除後設資料，不刪除資料。具體sql如下：

Sql程式碼

CREATE EXTERNAL TABLE test_1(id INT, name STRING, city STRING) SORTED BY TEXTFILE ROW FORMAT DELIMITED‘\t’ LOCATION ‘hdfs://../../..’

3. 分割槽表

有分割槽的表可以在建立的時候使用 PARTITIONED BY 語句。一個表可以擁有一個或者多個分割槽，每一個分割槽單獨存在一個目錄下。而且，表和分割槽都可以對某個列進行 CLUSTERED BY 操作，將若干個列放入一個桶（bucket）中。也可以利用SORT BY 對資料進行排序。這樣可以為特定應用提高效能。具體SQL如下：

Sql程式碼

CREATE TABLE test_1(id INT, name STRING, city STRING) PARTITIONED BY (pt STRING) SORTED BY TEXTFILE ROW FORMAT DELIMITED‘\t’

Hive的排序，因為底層實現的關係，比較不同於普通排序，這裡先不講。

桶的概念，主要是為效能考慮，可以理解為對分割槽內列，進行再次劃分，提高效能。在底層，一個桶其實是一個檔案。如果桶劃分過多，會導致檔案數量暴增，一旦達到系統檔案數量的上限，就杯具了。哪種是最優數量，這個哥也不知道。

分割槽表實際是一個資料夾，表名即資料夾名。每個分割槽，實際是表名這個資料夾下面的不同檔案。分割槽可以根據時間、地點等等進行劃分。比如，每天一個分割槽，等於每天存每天的資料；或者每個城市，存放每個城市的資料。每次查詢資料的時候，只要寫下類似 where pt=2010_08_23這樣的條件即可查詢指定時間得資料。

總體而言，普通表，類似mysql的表結構，外部表的意義更多是指資料的路徑對映。分割槽表，是最難以理解，也是最hive最大的優勢。之後會專門針對分割槽表進行講解。

二、載入資料

Hive不支援一條一條的用insert語句進行插入操作，也不支援update的操作。資料是以load的方式，載入到建立好的表中。資料一旦匯入，則不可修改。要麼drop掉整個表，要麼建立新的表，匯入新的資料。

官方指導為：

Sql程式碼

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

Hive在資料load這塊，大方向分為兩種方式，load檔案或者查詢一張表，或者將某張表裡的額查詢結果插入指定表。
如果劃分更細一點個人歸納總結為4種不同的方式的load：

1. Load data到指定的表

直接將file，載入到指定的表，其中，表可以是普通表或者分割槽表。具體sql如下：

Sql程式碼

LOAD DATA LOCAL INPATH '/home/admin/test/test.txt' OVERWRITE INTO TABLE test_1

    關鍵字[OVERWRITE]意思是是覆蓋原表裡的資料，不寫則不會覆蓋。
    關鍵字[LOCAL]是指你載入檔案的來源為本地檔案，不寫則為hdfs的檔案。
    其中
     ‘home/admin/test/test.txt’是相對路徑
     ‘/home/admin/test/test.txt’為據對路徑

2. load到指定表的分割槽

直接將file，載入到指定表的指定分割槽。表本身必須是分割槽表，如果是普通表，匯入會成功，但是資料實際不會被匯入。具體sql如下：

Sql程式碼

LOAD DATA LOCAL INPATH '/home/admin/test/test.txt' OVERWRITE INTO TABLE test_1 PARTITION（pt=’xxxx）

load資料，hive支援資料夾的方式，將資料夾內的所有檔案，都load到指定表中。Hdfs會將檔案系統內的某資料夾路徑內的檔案，分散到不同的實際實體地址中。這樣，在資料量很大的時候，hive支援讀取多個檔案載入，而不需要限定在唯一的檔案中。

3. insert+select

這個是完全不同於檔案操作的資料匯入方式。官方指導為：

Sql程式碼

Standard syntax:
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement
Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...
Hive extension (dynamic partition inserts):
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

這個的用法，和上面兩種直接操作file的方式，截然不同。從sql語句本身理解，就是把查詢到的資料，直接匯入另外一張表。這個暫時不仔細分析，之後查詢章節，再細講。

4. alter 表，對分割槽操作

在對錶結構進行修改的時候，我們可以增加一個新的分割槽，在增加新分割槽的同時，將資料直接load到新的分割槽當中。

Sql程式碼

ALTER TABLE table_name ADD
partition_spec [ LOCATION 'location1' ]
partition_spec [ LOCATION 'location2' ] ...

hive sql語法解讀

相關文章