非常詳細地Hive的基本操作和一些注意事項

1.概述
　　上一章《那些年使用Hive踩過的坑》介紹了Hive的基本架構及原理，加下來介紹Hive的基本操作和一些注意事項。
2.基本操作
2.1Create Table
2.1.1介紹
 CREATE TABLE 建立一個指定名字的表。如果相同名字的表已經存在，則丟擲異常；使用者可以用 IF NOT EXIST 選項來忽略這個異常。
 EXTERNAL 關鍵字可以讓使用者建立一個外部表，在建表的同時指定一個指向實際資料的路徑（LOCATION），Hive 建立內部表時，會將資料移動到資料倉儲指向的路徑；若建立外部表，僅記錄資料所在的路徑，不對資料的位置做任何改變。在刪除表的時候，內部表的後設資料和數 據會被一起刪除，而外部表只刪除後設資料，不刪除資料。
 LIKE 允許使用者複製現有的表結構，但是不復制資料。
 使用者在建表的時候可以自定義 SerDe 或者使用自帶的 SerDe。如果沒有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，將會使用自帶的 SerDe。在建表的時候，使用者還需要為表指定列，使用者在指定表的列的同時也會指定自定義的 SerDe，Hive 通過 SerDe 確定表的具體的列的資料。
 如果檔案資料是純文字，可以使用 STORED AS TEXTFILE。如果資料需要壓縮，使用 STORED AS SEQUENCE 。
 有分割槽的表可以在建立的時候使用 PARTITIONED BY 語句。一個表可以擁有一個或者多個分割槽，每一個分割槽單獨存在一個目錄下。而且，表和分割槽都可以對某個列進行 CLUSTERED BY 操作，將若干個列放入一個桶（bucket）中。也可以利用SORT BY 對資料進行排序。這樣可以為特定應用提高效能。
 表名和列名不區分大小寫，SerDe 和屬性名區分大小寫。表和列的註釋是字串
　　注：

SerDe是Serialize/Deserilize的簡稱，目的是用於序列化和反序列化
STORED AS TEXTFILE：預設格式，資料不做壓縮，磁碟開銷大，資料解析開銷大。 可結合Gzip、Bzip2使用(系統自動檢查，執行查詢時自動解壓)，但使用這種方式，hive不會對資料進行切分， 從而無法對資料進行並行操作
STORED AS SEQUENCE：Hadoop API提供的一種二進位制檔案支援，其具有使用方便、可分割、可壓縮的特點。

SequenceFile支援三種壓縮選擇：NONE，RECORD，BLOCK。Record壓縮率低，一般建議使用BLOCK壓縮。
2.1.2語法

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]
   | STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]  
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]  [AS select_statement]  CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  LIKE existing_table_name
  [LOCATION hdfs_path]

data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type

primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | STRING

array_type
  : ARRAY < data_type >

map_type
  : MAP < primitive_type, data_type >

struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>

row_format
  : DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

file_format:
  : SEQUENCEFILE
  | TEXTFILE
  | RCFILE     (Note:  only available starting with 0.6.0)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

2.1.3基本示例
1、如果一個表已經存在，可以使用if not exists

2、create table user(id int,cont string) row format delimited fields terminated by '\005' stored as textfile; terminated by：關於來源的文字資料的欄位間隔符

3、如果要將自定義間隔符的檔案讀入一個表，需要通過建立表的語句來指明輸入檔案間隔符，然後load data到這個表。

4、Shops資料庫常用間隔符的讀取 我們的常用間隔符一般是Ascii碼5，Ascii碼7等。在hive中Ascii碼5用’\005’表示， Ascii碼7用’\007’表示，依此類推。

5、裝載資料 檢視一下：Hadoop fs -ls LOAD DATA INPATH '/user/admin/user/a.txt' OVERWRITE INTO TABLE user;

6、如果使用external建表和普通建表區別：前者存放後設資料，刪除後檔案系統中的資料不會刪除，後者會直接刪除檔案系統中的資料

2.1.4建立分割槽

　　HIVE的分割槽通過在建立表時啟用partition by實現，用來partition的維度並不是實際資料的某一列，具體分割槽的標誌是由插入內容時給定的。當要查詢某一分割槽的內容時可以採用where語 句，形似where tablename.partition_key > a來實現。 建立含分割槽的表。 命令原型：

CREATE TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
 COMMENT 'This is the page view table'
 PARTITIONED BY(dt STRING, country STRING)
 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS SEQUENCEFILE;

　　如：建表

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 as textfile;

裝載資料：

LOAD DATA INPATH '/user/admin/SqlldrDat/CnClickstat/20131101/19/clickstat_gp_fatdt0/0' OVERWRITE INTO TABLE c02_clickstat_fatdt1
 PARTITION(dt='20131101');

　訪問某個分割槽：

SELECT count(*)
    FROM c02_clickstat_fatdt1 a
    WHERE a.dt >= '20131101' AND a.dt < '20131102';

指定LOCATION位置：

CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User',
     country STRING COMMENT 'country of origination')
 COMMENT 'This is the staging page view table'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
 STORED AS TEXTFILE
 LOCATION '<hdfs_location>';

複製一個空表：

CREATE TABLE empty_key_value_store
LIKE key_value_store;

2.2Alter Table
2.2.1新增分割槽

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 c02_clickstat_fatdt1 ADD 
PARTITION (dt='20131202') location '/user/hive/warehouse/c02_clickstat_fatdt1/part20131202' 
PARTITION (dt='20131203') location '/user/hive/warehouse/c02_clickstat_fatdt1/part20131203';

2.2.2刪除分割槽

ALTER TABLE table_name DROP partition_spec, partition_spec,...

　　例子：

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

2.2.3重新命名錶

ALTER TABLE table_name RENAME TO new_table_name

　　這個命令可以讓使用者為表更名。資料所在的位置和分割槽名並不改變。換而言之，老的表名並未“釋放”，對老表的更改會改變新表的資料。

2.2.4修改列/屬性

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

　　這個命令可以允許改變列名、資料型別、註釋、列位置或者它們的任意組合。

2.2.5新增/替換列

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

　　ADD是代表新增一欄位，欄位位置在所有列後面(partition列前);REPLACE則是表示替換表中所有欄位。
　　例子：

> 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_date string);   
> OK
> Time taken: 0.109 seconds
> hive> desc xibak;
> OK
> ins_date        string

2.3建立檢視

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

　　注：檢視關鍵字。 檢視是隻讀的，不能用LOAD/INSERT/ALTER

2.4顯示錶
　　檢視錶名：

SHOW TABLES;
　　檢視錶名，部分匹配：
SHOW TABLES 'page.';
SHOW TABLES '.view';

檢視某表的所有Partition，如果沒有就報錯：

SHOW PARTITIONS page_view;

　　檢視某表結構：

DESCRIBE invites;
　　檢視分割槽內容：

SELECT a.foo FROM invites a WHERE a.ds='2012-08-15';
　　檢視有限行內容，同Greenplum，用limit關鍵詞：

SELECT a.foo FROM invites a limit 3;
　　檢視錶分割槽定義：

DESCRIBE EXTENDED page_view PARTITION (ds='2013-08-08');

2.5載入
　　 HIVE裝載資料沒有做任何轉換載入到表中的資料只是進入相應的配置單元表的位置移動資料檔案。純載入操作複製/移動操作。

2.5.1語法

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

　　Load 操作只是單純的複製/移動操作，將資料檔案移動到 Hive 表對應的位置。

　　如：從本地匯入資料到表格並追加原表

LOAD DATA LOCAL INPATH /tmp/pv_2013-06-08_us.txt INTO TABLE c02 PARTITION(date='2013-06-08', country='US')
　　從本地匯入資料到表格並追加記錄：

LOAD DATA LOCAL INPATH './examples/files/kv1.txt' INTO TABLE pokes;
　　從hdfs匯入資料到表格並覆蓋原表：

LOAD DATA INPATH '/user/admin/SqlldrDat/CnClickstat/20131101/18/clickstat_gp_fatdt0/0' INTO table c02_clickstat_fatdt1 OVERWRITE PARTITION (dt='20131201');
　　關於來源的文字資料的欄位間隔符 如果要將自定義間隔符的檔案讀入一個表，需要通過建立表的語句來指明輸入檔案間隔符，然後load data到這個表就ok了。

2.6插入
2.6.1INSERT語法

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

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

hive> FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(*) WHERE a.foo > 0 GROUP BY a.bar;
hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(*) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;

　　需要注意的是，hive沒有直接插入一條資料的sql，不過可以通過其他方法實現： 假設有一張表B至少有一條資料，我們想向表A（int，string）中插入一條資料，可以用下面的方法實現：

from B insert table A select 1，‘abc’ limit 1；
　　我覺得Hive好像不能夠插入一個記錄，因為每次你寫INSERT語句的時候都是要將整個表的值OVERWRITE。我想這個應該是與Hive 的storage layer是有關係的，因為它的儲存層是HDFS，插入一個資料要全表掃描，還不如用整個表的替換來的快些。

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

2.6.1WRITE語法

Standard syntax:
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...

Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

　　匯出檔案到本地：

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;

　　匯出檔案到HDFS：

INSERT OVERWRITE DIRECTORY '/user/admin/SqlldrDat/CnClickstat/20131101/19/clickstat_gp_fatdt0/0' SELECT a.* FROM c02_clickstat_fatdt1 a WHERE dt=’20131201’;

　　一個源可以同時插入到多個目標表或目標檔案，多目標insert可以用一句話來完成：

FROM src
  INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
  INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
  INSERT OVERWRITE TABLE dest3 PARTITION(ds='2013-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300
  INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;

　　例子：
from tbl1
insert overwrite table test2 select '1,2,3' limit 1
insert overwrite table d select '4,5,6' limit 1;
2.8刪除
　　刪除一個內部表的同時會同時刪除表的後設資料和資料。刪除一個外部表，只刪除後設資料而保留資料。

　　語法：

DROP TABLE tbl_name
2.9Limit/Top/REGEX
　　Limit 可以限制查詢的記錄數。查詢的結果是隨機選擇的。下面的查詢語句從 t1 表中隨機查詢5條記錄：

SELECT * FROM t1 LIMIT 5
　　下面的查詢語句查詢銷售記錄最大的 5 個銷售代表。

SET mapred.reduce.tasks = 1
SELECT * FROM sales SORT BY amount DESC LIMIT 5
　　SELECT 語句可以使用正規表示式做列選擇，下面的語句查詢除了 ds 和 hr 之外的所有列：

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

2.10查詢
2.10.1語法

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

2.10.2GROUP BY

groupByClause: GROUP BY groupByExpression (, groupByExpression)*

groupByExpression: expression

groupByQuery: SELECT expression (, expression)* FROM src groupByClause?

2.10.3Order/Sort By
　　Order by 語法：

colOrder: ( ASC | DESC )
orderBy: ORDER BY colName colOrder? (',' colName colOrder?)
query: SELECT expression (',' expression) FROM src orderBy
　　Sort By 語法： Sort順序將根據列型別而定。如果數字型別的列，則排序順序也以數字順序。如果字串型別的列，則排序順序將字典順序。

colOrder: ( ASC | DESC )
sortBy: SORT BY colName colOrder? (',' colName colOrder?)
query: SELECT expression (',' expression) FROM src sortBy
2.11Hive Join
　　語法：

join_table:
    table_reference JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
  | table_reference LEFT SEMI JOIN table_reference join_condition

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [alias]
  | table_subquery alias
  | ( table_references )

join_condition:
    ON equality_expression ( AND equality_expression )*

equality_expression:
    expression = expression

　　Hive 只支援等值連線（equality joins）、外連線（outer joins）和（left/right joins）。Hive 不支援所有非等值的連線，因為非等值連線非常難轉化到 map/reduce 任務。另外，Hive 支援多於 2 個表的連線。

2.11.2注意事項
只支援等值join。
　　例如：

SELECT a. FROM a JOIN b ON (a.id = b.id)
SELECT a. FROM a JOIN b
ON (a.id = b.id AND a.department = b.department)
　　是正確的，然而:

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

　　是錯誤的。

可以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 會被轉化為單個 map/reduce 任務，例如：
SELECT a.val, b.val, c.val FROM a JOIN b
ON (a.key = b.key1) JOIN c
ON (c.key = b.key1)
　　被轉化為單個 map/reduce 任務，因為 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 被轉化為 2 個 map/reduce 任務。因為 b.key1 用於第一次 join 條件，而 b.key2 用於第二次 join。

join 時，每次 map/reduce 任務的邏輯。
　　reducer 會快取 join 序列中除了最後一個表的所有表的記錄，再通過最後一個表將結果序列化到檔案系統。這一實現有助於在 reduce 端減少記憶體的使用量。實踐中，應該把最大的那個表寫在最後（否則會因為快取浪費大量記憶體）。例如：

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 任務計算）。Reduce 端會快取 a 表和 b 表的記錄，然後每次取得一個 c 表的記錄就計算一次 join 結果，類似的還有：

SELECT a.val, b.val, c.val FROM a
JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

　　這裡用了 2 次 map/reduce 任務。第一次快取 a 表，用 b 表序列化；第二次快取第一次 map/reduce 任務的結果，然後用 c 表序列化。

2.12UDF
　　基本函式：

SHOW FUNCTIONS;
DESCRIBE FUNCTION <function_name>;
2.13UDTF
　　UDTF即Built-in Table-Generating Functions 使用這些UDTF函式有一些限制:

　　1、SELECT裡面不能有其它欄位，如：

SELECT pageid, explode(adid_list) AS myCol...
　　2、不能巢狀，如：

SELECT explode(explode(adid_list)) AS myCol... ＃ 不支援

　　3、不支援GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY ，如：

SELECT explode(adid_list) AS myCol ... GROUP BY myCol
2.14EXPLODE　
　下面是一個示例：

　　場景：將資料進行轉置，如：

create table test2(mycol array<int>);
insert OVERWRITE table test2 select * from (select array(1,2,3) from a union all select array(7,8,9)  from d)c;
hive> select * from test2;
OK
[1,2,3]
[7,8,9]
hive> SELECT explode(myCol) AS myNewCol FROM test2;
OK
2
7
9

3.總結
　　Hive的基本操作就分享到這裡，後面會單獨寫一篇優化的部落格與大家分享，若再操作的過程中有什麼疑問，我會盡我所能為您解答，與君共勉！