天天看点
返回

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编程指南》

hive hive分区表
上一篇: Hive学习笔记 3 Hive的数据模型：内部表、分区表、外部表、桶表、视图
下一篇: Hive的分区表入门

继续阅读