[Hadoop]Hive r0.9.0中文文件（一）之資料操作語句

[color=red]文章為原創，轉載請標註文章出處，否則保留對版權的追求權力。[/color]

[size=large][b]一、建立表的語法[/b][/size]

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]
  [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
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

用已知表名建立一個表的時候，如果同名表已經存在則會報一個表已存在的異常，不過你可以使用IF NOT EXISTS來防止這個錯誤。

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

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

如果檔案資料是純文字，可以使用 STORED AS TEXTFILE。如果資料需要壓縮，使用 STORED AS SEQUENCE 。使用INPUTFROMAT 和OUTPUTFORMAT將指定輸入輸出的格式，例如：

'org.apache.hadoop.hive.contrib.fileformat.base64.Base64TextInputFormat'.

代表指定了表的輸入格式必須的是Base64.

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

表名和列名不區分大小寫，SerDe 和屬性名區分大小寫。表和列的註釋是字串。

下面是建立一個表的例子:

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)
 STORED AS SEQUENCEFILE;

建立一個page_view表，按照dt和country進行分割並有序排列。

[size=large][b]二、使用select來建立表（簡稱CTAS）[/b][/size]
表可以按照查詢結果來進行建立，按照CTAS查詢的來的表是自動的，這意味著這個表不會被其他人看見值到查詢結果結束，其他人只能看見查詢的來的完成結果，而不是看到這個表的全部。

CTAS語句分為2個部分，HiveQL支援SELECT部分，建立部分將會使用SELECT查詢的結果，並且可以使用其他表的屬性例如SerDe和儲存格式來建立這個表。CTAS建立表的唯一限制是不能夠建立一個分割槽表，也不能建立一個EXTERNAL的表。

[size=large][b]三、使用 SerDes[/b][/size]
SerDe 是 Serialize/Deserilize 的簡稱，目的是用於序列化和反序列化。序列化的格式包括：
• 分隔符（tab、逗號、CTRL-A）
• Thrift 協議
反序列化（記憶體內）：
• Java Integer/String/ArrayList/HashMap
• Hadoop Writable 類
• 使用者自定義類


CTAS建立如果沒有指定列名那麼查詢得到的列名將會被自動分配為 _col0, _col1, and _col2等這樣的列名。另外，新的目標表將會使用特殊的SerDe進行建立，並且儲存格式與查詢語句相獨立。

CREATE TABLE new_key_value_store
   ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe"
   STORED AS RCFile AS
SELECT (key % 1024) new_key, concat(key, value) key_value_pair
FROM key_value_store
SORT BY new_key, key_value_pair;

把一個表中的資料查詢出來得到另外一個表示Hive一個重要的Feature，在這個查詢過程中，你可以讓Hive處理將原資料格式轉化為另外的一種格式。

[size=large][b]四、按BUCKET（桶）儲存的表[/b][/size]

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;

上面的例子中，page_view 表按照userid進行分桶，共32個桶，資料按照viewTime進行排序。
意義在於，這樣可以對資料叢集按照userid做一個有效的抽樣結果。排序屬性可以讓開發人員更好的利用已知的資料結構進行查詢，當某一個列是lists或者maps型別時，還可以使用MAP KEYS 和 COLLECTION TIEMS關鍵字。

CLUSTERED BY 和 SORTERD BY不影響資料插入方式，隻影響讀取順序。這意味著使用者必須小心使用這2個命令，看看桶型別表是怎麼工作的吧。

[size=large][b]五、外部表[/b][/size]
External Tables

External Table 指向已經在 HDFS 中存在的資料，可以建立 Partition。它和 Table 在後設資料的組織上是相同的，而實際資料的儲存則有較大的差異

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>';

[size=large][b]六、使用Like來建立表[/b][/size]
like僅複製表結構，但是不復制資料

CREATE TABLE empty_key_value_store
LIKE key_value_store;

[size=large][b]七、drop[/b][/size]
刪除一個內部表的同時會同時刪除表的後設資料和資料。刪除一個外部表，只刪除後設資料而保留資料。