目錄
一、Hive建表語法
二、內(nèi)部表外部表
三、分區(qū)表
四、分桶表

Hive在建表時可指定內(nèi)部表、外部表、分區(qū)表和分桶表，所以在學(xué)習(xí)這四種表之前，先一起了解Hive建表語法

一、Hive建表語法

Hive建表方式共有三種：直接建表法、查詢建表法、like建表法，下面講解直接建表法

’[]’ 表示可選，’|’ 表示二選一

創(chuàng)建表的語句：
Create [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
//EXTERNAL 關(guān)鍵字可以讓用戶創(chuàng)建一個外部表，如果不指定關(guān)鍵字EXTERNAL則為內(nèi)部表
//如：create table IF NOT EXISTS test_bucket
[(col_name data_type [COMMENT col_comment], ...)] 
//col_nam表字段，data_type字段類型，[COMMENT col_comment]字段注釋 ， ...為其他字段
//如：(word string,num bigint )
[COMMENT table_comment] 
//表注釋，COMMENT關(guān)鍵字，table_comment表解釋具體內(nèi)容
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
//PARTITIONED BY分區(qū)
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC],...)]INTO num_buckets BUCKETS]
//分桶，通過 clustered by(字段名) into bucket_num buckets 分桶，意思是根據(jù)字段名分成bucket_num個桶
//如：clustered by(id) into 4 buckets
[ROW FORMAT row_format] 
//加載數(shù)據(jù)文件到hive表時，數(shù)據(jù)文件字段于字段的分隔符
//如：row format delimited fields terminated by '\t'
[STORED AS file_format] 
[LOCATION hdfs_path]
//指定表存放的HDFS位置，如果不指定存放在默認(rèn)路徑，內(nèi)部表默認(rèn)路徑/user/hive/warehouse

建表語句解釋：

• CREATE TABLE 創(chuàng)建一個指定名字的表。

• EXTERNAL 關(guān)鍵字可以讓用戶創(chuàng)建一個外部表，無EXTERNAL 關(guān)鍵字則為內(nèi)部表。在刪除表的時候，內(nèi)部表的元數(shù)據(jù)和數(shù)據(jù)會被一起刪除，而外部表只刪除元數(shù)據(jù)，不刪除數(shù)據(jù)。

• 有分區(qū)的表可以在創(chuàng)建的時候使用 PARTITIONED BY 語句。一個表可以擁有一個或者多個分區(qū)，每一個分區(qū)單獨存在一個目錄下。

案例：

//01-創(chuàng)建orders_part外部表
create  EXTERNAL table orders_part(
order_id string comment '訂單編號',
user_id string comment '客戶號',
order_number string  comment '訂單數(shù)量',
order_date string comment '下單時間'
)
comment '測試_創(chuàng)建orders_part表'
partitioned by(order_date string)
row format delimited fields terminated by ','

//02-創(chuàng)建分桶表
create table test_bucket (
id int comment 'ID', 
name string comment '名字'
)
comment '測試分桶'
clustered by(id) into 4 buckets
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

二、內(nèi)部表外部表

通過建表語句、location位置、刪除表三方面來學(xué)習(xí)外部表，內(nèi)部表

1、建表語句

1.1、內(nèi)部表
平時創(chuàng)建的普通表為內(nèi)部表

create table `test_internal` (
id string comment 'ID', 
name string comment '名字'
)
comment '測試內(nèi)部表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

1.2、外部表
帶external關(guān)鍵字的為外部表

create external table `test_external` (
id string comment 'ID', 
name string comment '名字'
)
comment '測試外部表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

僅從建表語句上看，內(nèi)部表和外部表的區(qū)別為是否帶有external關(guān)鍵字

2、location

網(wǎng)上很多博客寫的外部表建表語句中都帶有l(wèi)ocation關(guān)鍵字，我這里單獨分開來寫。[我參考的是一個博主的文章]

• 其實不管是內(nèi)部表還是外部表都可以加location關(guān)鍵字指定hive表的存儲路徑，當(dāng)然也可以不加，從這點看是無區(qū)別的。

• 如果不加location那么不管是內(nèi)部表還是外部表都會在默認(rèn)的hive配置的hdfs路徑下下新建一個和表名相同的文件夾。內(nèi)部表默認(rèn)存放路徑：/user/hive/warehouse

• 如果加了location指定另外的文件夾，那么在查詢時，該文件夾下對應(yīng)的數(shù)據(jù)會加載到hive表里。

如果不理解上面文字描述可以看下面案例

案例：

01、準(zhǔn)備數(shù)據(jù)

將數(shù)據(jù)上傳到HDFS

//data.txt 數(shù)據(jù)
001,張三
002,李四
003,王五

hadoop fs -mkdir -p /tmp/hive_test/internal_location //創(chuàng)建Hdfs目錄
hadoop fs -mkdir -p /tmp/hive_test/external_location
hadoop fs -put data.txt /tmp/hive_test/internal_location  //上傳數(shù)據(jù)到HDFS
hadoop fs -put data.txt /tmp/hive_test/external_location //上傳數(shù)據(jù)到HDFS文件夾

02、建表

內(nèi)部表

create table IF NOT EXISTS test_internal_location (
id string comment 'ID', 
name string comment '名字'
)
comment '測試內(nèi)部表location'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
location '/tmp/hive_test/internal_location';

外部表

create external table   IF NOT EXISTS test_external_location (
id string comment 'ID', 
name string comment '名字'
)
comment '測試外部表location'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
location '/tmp/hive_test/external_location';

說明：hdfs 文件夾及對應(yīng)下的數(shù)據(jù)和建表語句沒有先后順序，建表在前和在后都可以把數(shù)據(jù)加載出來，如果先建表的話，對應(yīng)的文件夾如果不存在，則會自動建立文件夾。

3、刪除表

內(nèi)部表和外部表的區(qū)別主要體現(xiàn)在刪除表，將上面建立的2個表都刪掉。

drop table test_internal_location;
drop table test_external_location;

看一下對應(yīng)的hdfs路徑有啥變化

發(fā)現(xiàn)外部表external_location文件夾存在，而內(nèi)部表的文件夾沒了，這也就是內(nèi)部表和外部表的區(qū)別

內(nèi)部表刪除表時，對應(yīng)的hdfs的路徑下的文件會刪掉；外部表刪除表時，對應(yīng)的HDFS的路徑下的文件則不會刪掉。

4、內(nèi)部表外部表—總結(jié)

(1) 建表：帶有external關(guān)鍵字為外部表，否則為內(nèi)部表
(2) location：內(nèi)部表和外部表建表時都可以自己指定location
(3) 刪除表：外部表不會刪除對應(yīng)的數(shù)據(jù)，只會刪除元數(shù)據(jù)信息，內(nèi)部表則會刪除元數(shù)據(jù)信息和對應(yīng)的數(shù)據(jù)。

參考文章：Hive內(nèi)部表和外部表，看了這個博主寫的兩篇文章，寫得真的很好很好。

三、分區(qū)表

分區(qū)表一般在數(shù)據(jù)量比較大，且有明確的分區(qū)字段時使用，這樣用分區(qū)字段作為查詢條件查詢效率會比較高。Hive分區(qū)分為靜態(tài)分區(qū)和動態(tài)分區(qū)。

1、為什么出現(xiàn)分區(qū)表？

假設(shè)有海量的數(shù)據(jù)保存在hdfs的某一個hive表名對應(yīng)的目錄下，使用hive進(jìn)行操作的時候，往往會搜索這個目錄下的所有的文件，這時會非常耗時。(hive表對應(yīng)hdfs目錄下的文件數(shù)據(jù)都會被加載到hive表中）

如果我們知道這些數(shù)據(jù)的某些特征，然后使用Hive進(jìn)行操作的時候，就可以在where子句種對這些特征進(jìn)行過濾，那么對數(shù)據(jù)的操作就會在符合條件的子目錄下進(jìn)行，其他不符合條件的目錄下的內(nèi)容就不會被讀取。

在數(shù)據(jù)量非常大的時候，這樣節(jié)省大量的時間，這種把表種的數(shù)據(jù)分散到子目錄下的方式就是分區(qū)表。

下面我們還是通過實戰(zhàn)來學(xué)習(xí)分區(qū)表

案例

2、創(chuàng)建分區(qū)表

靜態(tài)分區(qū)和動態(tài)分區(qū)的建表語句是一樣的

create table test_partition (
name string comment '名字',
age int comment '年齡'
)
comment '測試分區(qū)'
partitioned by (provice string comment '省',city string comment '城市')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

用partitioned by指定創(chuàng)建的分區(qū)，多個分區(qū)意味著多級目錄。

創(chuàng)建之后hdfs上的目錄結(jié)構(gòu)并不會立即發(fā)生變化，因為此時表中還沒有數(shù)據(jù)，往表中插入數(shù)據(jù)之后，會發(fā)現(xiàn)在表名對應(yīng)的目錄下，會多出兩級目錄。

3、插入語句

靜態(tài)分區(qū)和動態(tài)分區(qū)的插入數(shù)據(jù)的語法是不一樣的，下面講解靜態(tài)分區(qū)。
想學(xué)習(xí)動態(tài)分區(qū)可看這篇文章hive使用動態(tài)分區(qū)插入數(shù)據(jù)詳解

3.1 靜態(tài)分區(qū)

靜態(tài)分區(qū)是在語句中指定分區(qū)字段為某個固定值

3.1.1 靜態(tài)分區(qū)加載數(shù)據(jù)-INSERT INTO語法

insert into table test_partition partition(provice = "hebei",city = "baoding") values ('張三',20);
insert into table test_partition partition(provice = "hebei",city = "baoding") values ('張三',20);
insert into table test_partition partition(provice = "hebei",city = "baoding") values ('李四',20);

insert into table test_partition partition(provice = "hebei",city = "handan") values ('張三',20);
insert into table test_partition partition(provice = "hebei",city = "handan") values ('張三',20);
insert into table test_partition partition(provice = "hebei",city = "handan") values ('李四',20);

3.1.2 靜態(tài)分區(qū)加載數(shù)據(jù)-LOAD DATA方法

data4.txt

張三,23
李四,23
王五,23

加載本地文件到hive表中

load data local inpath '/home/study/data4.txt' into table test_partition partition (provice = "hebei",city = "handan");
load data local inpath '/home/study/data4.txt' into table test_partition partition (provice = "hebei",city = "handan");
load data local inpath '/home/study/data4.txt' into table test_partition partition (provice = "sichuan",city = "chengdu");

3.1.3 查看數(shù)據(jù)

hive的倉庫路徑: /user/hive/warehouse
數(shù)據(jù)庫名稱: myhive.db myhive
表名: test_partition
分區(qū)字段名:provice,city

總結(jié)：

• (1) 分區(qū)表的意思，其實想明白了就很簡單。就是在系統(tǒng)上建立文件夾，把分類數(shù)據(jù)放在不同文件夾下面，加快查詢速度。

• (2) 關(guān)鍵點1：partitioned by (provice String,city string); 創(chuàng)建表格時，指明了這是一個分區(qū)表。將建立雙層目錄，第一次目錄的名字和第二層目錄名字規(guī)則
  PARTITIONED BY子句中定義列，是表中正式的列，成為分區(qū)列。但是數(shù)據(jù)文件中并沒有這些值，僅代表目錄。

• (3) 關(guān)鍵點2：partition (provice = "hebei",city = "handan"); 上傳數(shù)據(jù)時，把數(shù)據(jù)分別上傳到不同分區(qū)中。也就是分別放在不同的子目錄下。

參考文章1:Hive分區(qū)表學(xué)習(xí)總結(jié)
參考文章2:hive使用動態(tài)分區(qū)插入數(shù)據(jù)詳解
參考文章3:HIVE-分區(qū)表詳解以及實例

四、分桶表

其實分區(qū)和分桶這兩個概念對于初學(xué)者來說是比較難理解的,要想更好的學(xué)習(xí)分桶表,看文章的同時還需要自己操作才行。下面我們通過實操來學(xué)習(xí)分桶表[可能剛看著代碼有點迷惑,不過做到后面就理解了]

也可以像我這樣學(xué)習(xí):看文章+實操+寫文章總結(jié),很多知識點我按這樣學(xué)習(xí)后基本上不會忘記。

1、分桶簡介？

分桶規(guī)則：桶是通過對指定列進(jìn)行哈希計算來實現(xiàn)的，對分桶字段值進(jìn)行哈希，哈希值除以桶的個數(shù)求余，余數(shù)決定了該條記錄在哪個桶中，也就是余數(shù)相同的在一個桶中。
優(yōu)點：1、提高join查詢效率 2、提高抽樣效率

2、分桶操作

2.1、創(chuàng)建桶表：

通過 clustered by(字段名) into bucket_num buckets 分桶，意思是根據(jù)字段名分成bucket_num個桶

create table test_bucket (
id int comment 'ID', 
name string comment '名字'
)
comment '測試分桶'
clustered by(id) into 4 buckets
-- 注意分桶字段只能是建表中已有的字段，
-- 而分區(qū)表的字段必須是建表中沒有的字段
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

2.2、設(shè)置Hive的分桶開關(guān)

打開Hive客戶端，并在會話終端開啟Hive分桶的開關(guān)：set hive.enforce.bucketing=true;(此設(shè)置為臨時設(shè)置，一旦退出會話終端，再打開就會恢復(fù)默認(rèn)設(shè)置false)

set hive.enforce.bucketing=true;  ///強(qiáng)制分桶

此開關(guān)打開之后，會自動根據(jù)bucket(桶)個數(shù)自動分配Reduce task的個數(shù)，Reduce個數(shù)與bucket個數(shù)一致。(此外，Reduce的個數(shù)還可以通過mapred.reduce.tasks進(jìn)行設(shè)置，但是這方法不推薦在Hive分桶中使用)

2.2.3、load data方法插入數(shù)據(jù)到分桶表

buckt_data.txt

1,name1
2,name2
3,name3
4,name4
5,name5
6,name6
7,name7
8,name8
9,name9

load data 插入數(shù)據(jù)到分桶表

load data local inpath '/home/study/buckt_data.txt' into table test_bucket ;

2.2.3、借用中間表insert into方法插入數(shù)據(jù)到分桶表

創(chuàng)建中間表

create table test (
id int comment 'ID', 
name string comment '名字'
)
comment '測試分桶中間表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

先將數(shù)據(jù)load到中間表

load data local inpath '/home/study/buckt_data.txt' into table test;

通過下面insert into的語句，將中間表的數(shù)據(jù)插入到分桶表中，這樣會產(chǎn)生四個文件。

insert into test_bucket select * from test;

借的別人的圖片,不過這個博主寫的文章真的好

從上面圖片可以看見:
(1) 桶是以文件的形式存在的，而不是像分區(qū)那樣以文件夾的形式存在。
(2) 除以4余數(shù)相同的確實在一個文件里，也就是在一個桶中

總結(jié)：

(1) 其實桶的概念就是MapReduce的分區(qū)的概念，兩者完全相同。物理上每個桶就是目錄里的一個文件，一個作業(yè)產(chǎn)生的桶（輸出文件）數(shù)量和reduce任務(wù)個數(shù)相同。
(2) 桶是按照數(shù)據(jù)內(nèi)容的某個值進(jìn)行分桶，把一個大文件散列稱為一個個小文件。
(3) 這些小文件可以單獨排序。如果另外一個表也按照同樣的規(guī)則分成了一個個小文件。兩個表join的時候，就不必要掃描整個表，只需要匹配相同分桶的數(shù)據(jù)即可。效率當(dāng)然大大提升。
同樣，對數(shù)據(jù)抽樣的時候，也不需要掃描整個文件。只需要對每個分區(qū)按照相同規(guī)則抽取一部分?jǐn)?shù)據(jù)即可。

參考文章1:Hive分桶表學(xué)習(xí)總結(jié)
參考文章2:HIVE-分桶表的詳解和創(chuàng)建實例
參考文章3:Hive分桶表的使用場景以及優(yōu)缺點分析,想了解概念看該篇文章

關(guān)于HIVE中內(nèi)部表、外部表、分區(qū)表和分桶表就總結(jié)完了,后期還得繼續(xù)努力學(xué)習(xí)呀.問題還是得一個一個的解決.加油啦~~~
做仰臥起坐去了,周末愉快啦.
這個冬天真冷