HiveQL(二):分區表

1 分區表（管理表）

1.1 建立分區表（定義分區字段）

我們重新來看之前的employees表，其address字段包含了city（市）、state（州）等資訊，查詢人員經常會執行一些帶WHERE語句的查詢，這樣可以将結果限制在某個特定的國家或者某個特定的細分（例如‘美國的州’或‘加拿大的省’）。那麼久先按照country（國家）再按照state（州）來對資料進行分區吧：

CREATE TABLE employees(
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING,FLOAT>,
address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
PARTITIONED BY (country STRING, state STRING);
           

分區表改變了Hive對資料存儲的組織方式。如果我們是在mydb資料庫中建立的這個表（沒有分區），那麼對于這個表隻會有一個employees目錄與之對應：

hdfs://master:9000/hive/warehouse/mydb.db/employees

但是，hive現在将會建立好可以反映分區結構的子目錄。例如（在分區後已裝載進資料的前提下）：

…

hdfs://master:9000/hive/warehouse/mydb.db/employees/country=CA/state=AB

hdfs://master:9000/hive/warehouse/mydb.db/employees/country=CA/state=BC

…

hdfs://master:9000/hive/warehouse/mydb.db/employees/country=US/state=AL

hdfs://master:9000/hive/warehouse/mydb.db/employees/country=US/state=AK

…

在州目錄下将會包含有零個檔案或者多個檔案，這些檔案中存放着那些州的雇員資訊

1.2 查詢某個分區資料

如果想要查詢某個國家某個州的所有雇員，可使用如下查詢語句：

SELECT * FROM employees 
WHERE country = 'US' AND state = 'IL';
           

需要注意的是，因為country和state的值已經包含在檔案目錄名稱中了，是以就沒有必要将這些值存放到它們目錄下的檔案中了。也就是說，當向分區表裝載資料時，資料具體屬于哪個分區值是要事先知道的，除非使用動态分區（裝載資料時根據資料中儲存的分區字段值，自動将其劃分到對應分區下）

對資料進行分區，最重要的原因就是為了更快地查詢。在上述将結果範圍限定在IL州的雇員的sql查詢中，僅僅需要掃描一個目錄下的内容即可。即使我們有成千上萬個國家和州目錄，除了想要查詢的一個目标目錄其他的都可以忽略不計。

1.3 嚴格模式

如果表中的資料以及分區個數都非常大的話，執行一個包含所有分區的查詢（比如查詢全球各地的所有員工）可能會觸發一個巨大的MapReduce任務，hive會不得不讀取每個檔案目錄。一個建議的安全措施是将hive設定為“strice（嚴格）”模式，這樣如果對分區表進行查詢而WHERE子句沒有加分區過濾的話，将會禁止送出這個任務。

hive> set hive.mapred.mode=strict;

hive> SELECT e.name, e.salary FROM employees e LIMIT 100;
FAILED: Error in semantic analysis: No partition predicate found for Alias "e" Table "employees"

hive> set hive.mapred.mode=nonstrict;

hive> SELECT e.name, e.salary FROM employees e LIMIT 100;
John Doe 100000.0
...
           

1.4 檢視表中分區資訊

可以通過SHOW PARTITIONS指令檢視表中存在的所有分區：

hive> SHOW PARTITIONS employees;
...
country=CA/state=AB
country=CA/state=BC
...
country=US/state=AL
country=US/state=AK
...
           

如果表中存在很多的分區，比如上述分區是由兩個字段組成的，第一分區是country，第二分區是state，而我們隻想檢視某個分區字段下的分區資訊的話，還可以在上述指令上增加一個指定一個或多個特定分區字段值的PARTITION子句，進行過濾查詢：

hive> SHOW PARTITIONS employees PARTITION(country='US');
country=US/state=AL
country=US/state=AK
...

hive> SHOW PARTITIONS employees PARTITION(country='US', state='AK');
country=US/state=AK
           

另外，DESCRIBE FORMATTED employees指令也會顯示出分區字段

還可以通過DESCRIBE FORMATTED … PARTITION檢視某一分區的詳細資訊：

hive> desc formatted logmsgs partition(year=2012, month=1, day=2);
OK
# col_name            	data_type           	comment             
	 	 
hms                 	int                 	                    
severity            	string              	                    
server              	string              	                    
process_id          	int                 	                    
message             	string              	                    
	 	 
# Partition Information	 	 
# col_name            	data_type           	comment             
	 	 
year                	int                 	                    
month               	int                 	                    
day                 	int                 	                    
	 	 
# Detailed Partition Information	 	 
Partition Value:    	[2012, 1, 2]        	 
Database:           	mydb                	 
Table:              	logmsgs             	 
CreateTime:         	Sat May 04 13:51:20 CST 2019	 
LastAccessTime:     	UNKNOWN             	 
Protect Mode:       	None                	 
Location:           	hdfs://192.168.230.10:9000/data/log_messages/year=2012/month=01/day=02
           

1.5 建立具體分區

在建立了一個分區管理表後，我們可以通過載入資料的方式建立具體分區，如下語句從一個本地目錄/home/boya/california-employees載入資料到表中的時候，将會建立一個US和CA（表示國家和州）分區。使用者需要為每個分區字段指定一個值：

LOAD DATA LOCAL INPATH '/home/boya/california-employees'
INTO TABLE employees
PARTITION(country='US', state='CA');
           

hive将會建立這個分區對應的目錄hdfs://master:9000/hive/warehouse/mydb.db/employees/country=US/state=CA，而且/home/boya/california-employees這個檔案将會被複制到上述分區目錄下。

2 外部分區表

2.1 定義外部分區表

外部表同樣可以使用分區。事實上，這是管理大型生産資料集最常見的情況。這種結合給使用者提供了一個可以和其他工具共享資料的方式，同時也可以優化查詢性能。

按照如下方式定義一個外部分區表：

CREATE EXTERNAL TABLE IF NOT EXISTS log_messages (
hms INT,
severity STRING,
server STRING,
process_id INT,
message STRING)
PARTITIONED BY (year INT, month INT, day INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
           

可以看到，這裡沒有指定LOCATION，則hive會在hdfs路徑下建立該表目錄：/hive/warehouse/mydb.db/log_messages，此時該目錄下還沒有分區子目錄。

2.2 增加分區

但是我們可以通過ALTER TABLE語句增加一個2012年1月2日的分區，并且指定分區目錄的存放路徑：

ALTER TABLE log_messages ADD PARTITION(year = 2012, month = 1, day = 2)
LOCATION '/data/log_messages/year=2012/month=01/day=02';
           

則hive将會依據LOCATION指定的路徑建立分層目錄結構*/data/log_messages/year=2012/month=01/day=02*，此時該目錄下沒有資料檔案。這裡也可看出，hive不關心一個分區對應的分區目錄是否存在或者分區目錄下是否有檔案。如果分區目錄不存在或分區目錄下沒有檔案，則對于這個過濾分區的查詢将沒有傳回結果。

如果此時使用ALTER TABLE語句增加分區，但不指定LOCATION，hive會将建立的分區目錄存放到hive預設的“warehouse”路徑下，（因為CREATE EXTERNAL TABLE時就沒有指定LOCATION,整個表的目錄就預設放到了hive/warehouse/mydb.db目錄下了）：

此時2012年1月3日的分區存放在了hdfs://192.168.230.10:9000/hive/warehouse/mydb.db/log_messages/year=2012/month=1/day=3目錄下

和非分區外部表一樣，hive并不控制這些資料。即使表被删除，資料也不會被删除。

2.3 檢視分區表資訊

當使用DESCRIBE FORMATTED log_messages指令時，在輸出資訊的Location字段顯示的是管理表用到的hive預設目錄即hdfs://192.168.230.10:9000/hive/warehouse/mydb.db/log_messages，不過可通過如下方式檢視分區資料所在的路徑：

hive> DESCRIBE FORMATTED log_messages PARTITION(year=2012,month=1,day=2);
...
Location:hdfs://192.168.230.10:9000/data/log_messages/year=2012/month=01/day=02	
...
           

ALTER TABLE … ADD PARTITION語句并非隻有對外部表才能使用。對于管理表，當有分區資料不是由LOAD和INSERT語句産生時，使用者同樣可以使用這個指令指定分區路徑。使用者需要記住并非所有的表資料都是放在hive的“warehouse”目錄下的，外部表的資料存放路徑可由使用者指定，并且删除表時，這些資料不會連帶被删除。

參考《hive程式設計指南》