hive中的表、外部表、分割槽和桶的理解

Thinkgamer_gyt發表於2015-08-02
Hive

一、概念介紹

        Hive 沒有專門的資料儲存格式，也沒有為資料建立索引，使用者可以非常自由的組織 Hive 中的表，只需要在建立表的時候告訴 Hive 資料中的列分隔符和行分隔符，Hive 就可以解析資料

        Hive 中的 Table 和資料庫中的 Table 在概念上是類似的，每一個 Table 在 Hive 中都有一個相應的目錄儲存資料。例如，一個表 pvs，它在 HDFS 中的路徑為：/wh/pvs，其中，wh 是在 hive-site.xml 中由 ${hive.metastore.warehouse.dir} 指定的資料倉儲的目錄，所有的 Table 資料（不包括 External Table）都儲存在這個目錄中。
       
        Partition 對應於資料庫中的 Partition 列的密集索引，但是 Hive 中 Partition 的組織方式和資料庫中的很不相同。在 Hive 中，表中的一個 Partition 對應於表下的一個目錄，所有的 Partition 的資料都儲存在對應的目錄中。例如：pvs 表中包含 ds 和 city 兩個 Partition，則對應於 ds = 20090801, ctry = US 的 HDFS 子目錄為：/wh/pvs/ds=20090801/ctry=US；對應於 ds = 20090801, ctry = CA 的 HDFS 子目錄為；/wh/pvs/ds=20090801/ctry=CA
       
        Buckets 對指定列計算 hash，根據 hash 值切分資料，目的是為了並行，每一個 Bucket 對應一個檔案。將 user 列分散至 32 個 bucket，首先對 user 列的值計算 hash，對應 hash 值為 0 的 HDFS 目錄為：/wh/pvs/ds=20090801/ctry=US/part-00000；hash 值為 20 的 HDFS 目錄為：/wh/pvs/ds=20090801/ctry=US/part-00020

        External Table 指向已經在 HDFS 中存在的資料，可以建立 Partition。它和 Table 在後設資料的組織上是相同的，而實際資料的儲存則有較大的差異。Table 的建立過程和資料載入過程（這兩個過程可以在同一個語句中完成），在載入資料的過程中，實際資料會被移動到資料倉儲目錄中；之後對資料對訪問將會直接在資料倉儲目錄中完成。刪除表時，表中的資料和後設資料將會被同時刪除。

       External Table 只有一個過程，載入資料和建立表同時完成（CREATE EXTERNAL TABLE ……LOCATION），實際資料是儲存在 LOCATION 後面指定的 HDFS 路徑中，並不會移動到資料倉儲目錄中。當刪除一個 External Table 時，僅刪除後設資料，表中的資料不會真正被刪除。
二、基本操作命令


簡單的建立表

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

 分割槽表

建立有分割槽的表

create table if not exists test_par(
id int,
name string,
age int)
comment "this is partition table"
partitioned by (year string,month string,day string)
row format delimited fields terminated by "\t"
lines terminated by "\n"
stored as textfile;

一個表可以擁有一個或者多個分割槽，每個分割槽以資料夾的形式單獨存在表資料夾的目錄下。

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

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

新增分割槽

alter table test_par add if not exists partition(year="2016",month="9",day="16");

檢視分割槽

show partitions test_par;

刪除分割槽

alter table test_par drop if not exists partition(year="2016",month="9",day="16");


典型的預設建立表

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; --把當前資料庫指向mydb
CREATE TABLE pokes(foo INT,bar STRING);

 

複製表結構

CREATE TABLE empty_table_name LIKE table_name;

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


hive 桶 

桶是更細粒度的劃分，針對hive表中的某一列進行分桶，hive採用對列值雜湊，然後除以桶的個數求餘的方式巨鼎記錄存放在哪個桶當中，
好處是更高的查詢效率，使取樣（sampling）更高效

在插入資料之前開啟桶功能，也可以在配置檔案中修改
set hive.enforce.bucketing = true;

建立分桶

create table if not exists test_tong(
id int,
name string
)
comment "this is fentong table"
clustered by(id) sorted by(name) into 4 buckets
row format delimited fields terminated by "\t"
lines terminated by "\n"
stored as textfile;

往桶中插入內容，從別的表中查詢插入

insert overwrite table test_tong select id,name from other_table;
(這裡的id，name不一定同名，只要保證對應的資料型別一致就ok)


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 AS
SELECT (key % 1024) new_key, concat(key, value) key_value_pair
FROM key_value_store
SORT 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');

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

load data相關知識：http://blog.csdn.net/wacthamu/article/details/40744217

分割槽相關知識：http://www.2cto.com/kf/201210/160777.html

相關文章