hive 建立/刪除/截斷 表(翻譯自Hive wiki)

標籤：des   blog   http   使用   io   strong   檔案   for   

這裡羅列常用操作，更多參考 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Create%2FDrop%2FTruncateTable

 

簡單的建立表

create table table_name (  id                int,  dtDontQuery       string,  name              string)

 

 

建立有分區的表

create table table_name (  id                int,  dtDontQuery       string,  name              string)partitioned by (date string)

一個表可以擁有一個或者多個分區，每個分區以檔案夾的形式單獨存在表檔案夾的目錄下。

分區是以欄位的形式在表結構中存在，通過describe table命令可以查看到欄位存在，但是該欄位不存放實際的資料內容，僅僅是分區的表示。

在Hive Select查詢中一般會掃描整個表內容，會消耗很多時間做沒必要的工作。有時候只需要掃描表中關心的一部分資料，因此建表時引入了partition概念。表中的一個 Partition 對應於表下的一個目錄,Partition 就是輔助查詢，縮小查詢範圍，加快資料的檢索速度和對資料按照一定的規格和條件進行管理。

 

典型的預設建立表

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) ROW FORMAT DELIMITED   FIELDS TERMINATED BY ‘\001‘   COLLECTION ITEMS TERMINATED BY ‘\002‘   MAP KEYS TERMINATED BY ‘\003‘ STORED AS TEXTFILE;

 

這裡建立了表page_view,有表的注釋，一個欄位ip的注釋，分區有兩列,分別是dt和country。

[ROW FORMAT DELIMITED]關鍵字，是用來設定建立的表在載入資料的時候，支援的資料行分隔符號。不同列之間用一個‘\001‘分割,集合(例如array,map)的元素之間以‘\002‘隔開,map中key和value用‘\003‘分割。

 

[STORED AS file_format]關鍵字是用來設定載入資料的資料類型,預設是TEXTFILE，如果檔案資料是純文字，就是使用 [STORED AS TEXTFILE]，然後從本地直接拷貝到HDFS上，hive直接可以識別資料。

 

常用的建立表

CREATE TABLE login(     userid BIGINT,     ip STRING,      time BIGINT) PARTITIONED BY(dt STRING) ROW FORMAT DELIMITED   FIELDS TERMINATED BY ‘\t‘ STORED AS TEXTFILE;

 

建立外部表格

如果資料已經存在HDFS的‘/user/hadoop/warehouse/page_view‘上了，如果想建立表，指向這個路徑，就需要建立外部表格:

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 ‘/user/hadoop/warehouse/page_view‘;

建立表，有指定EXTERNAL就是外部表格，沒有指定就是內部表，內部表在drop的時候會從HDFS上刪除資料，而外部表格不會刪除。

外部表格和內部表一樣，都可以有分區，如果指定了分區，那外部表格建了之後，還要修改表添加分區。

外部表格如果有分區，還可以載入資料，覆蓋分區資料，但是外部表格刪除分區，對應分區的資料不會從HDFS上刪除，而內部表會刪除分區資料。

 

指定資料庫建立表

如果不指定資料庫，hive會把表建立在default資料庫下，假設有一個hive的資料庫mydb,要建立表到mydb,如下:

CREATE TABLE mydb.pokes(foo INT,bar STRING);或者是use mydb; --把當前資料庫指向mydbCREATE TABLE pokes(foo INT,bar STRING);

 

複製表結構

CREATE TABLE empty_table_name LIKE table_name;

根據table_name建立一個空表empty_table_name,empty_table_name沒有任何資料。

 

create-table-as-selectt (CTAS)

CTAS建立的表是原子性的，這意味著，該表直到所有的查詢結果完成後，其他使用者才可以看到完整的查詢結果表。

CTAS唯一的限制是目標表，不能是一個有分區的表，也不能是外部表格。

簡單的方式

CREATE TABLE new_key_value_store  AS 
SELECT (key % 1024) new_key, concat(key, value) key_value_pair FROM key_value_store;

複雜的方式

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

 

刪除表

DROP TABLE table_name;DROP TABLE IF EXISTS table_name;

刪除表會移除表的中繼資料和資料，而HDFS上的資料，如果配置了Trash，會移到.Trash/Current目錄下。

刪除外部表格時，表中的資料不會被刪除。

 

截斷表

TRUNCATE TABLE table_name;TRUNCATE TABLE table_name PARTITION (dt=‘20080808‘);

從表或者表分區刪除所有行，不指定分區，將截斷表中的所有分區，也可以一次指定多個分區，截斷多個分區。