Hive中的分区分桶约束,元数据解析

最新推荐文章于 2024-07-01 22:00:57 发布

原创最新推荐文章于 2024-07-01 22:00:57 发布 · 523 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive #元数据

本文探讨了Hive中的分区分桶概念，通过`desc formatted`命令解析了具有主外键约束的表`user_partition_bucket_pk`和`user_partition_bucket_fk`的元数据信息。

Hive中的分区分桶主外键约束

建表

create table user_partition_bucket_pk(id int comment 'ID',name string comment '姓名',age int comment '年龄',id1 int, id2 int, 
primary key(id1, id2) disable novalidate) comment '测试分桶'
PARTITIONED BY (date_id string)
clustered by (id) sorted by (id) into 4 buckets row format delimited fields terminated by '\t';

create table user_partition_bucket_fk(id int comment 'ID',name string comment '姓名',age int comment '年龄',id1 int, id2 int, 
constraint c1 foreign key(id1, id2) references user_partition_bucket_pk(id1, id2) disable novalidate) comment '测试分桶'
PARTITIONED BY (date_id string)
clustered by (id) sorted by (id) into 4 buckets row format delimited fields terminated by '\t';

desc formatted user_partition_bucket_pk

# col_name	data_type	comment
id	int	ID
name	string	姓名
age	int	年龄
id1	int	
id2	int	
		
# Partition Information		
# col_name	data_type	comment
date_id	string	
		
# Detailed Table Information		
Database:           	default             	
OwnerType:          	USER                	
Owner:              	root                	
CreateTime:         	Mon Dec 09 00:02:36 CST 2019	
LastAccessTime:     	UNKNOWN             	
Retention:          	0                   	
Location:           	hdfs://node01:9000/user/hive/warehouse/user_partition_bucket_pk	
Table Type:         	MANAGED_TABLE       	
Table Parameters:		
	COLUMN_STATS_ACCURATE	{\"BASIC_STATS\":\"true\"}
	SORTBUCKETCOLSPREFIX	TRUE                
	bucketing_version   	2                   
	comment             	测试分桶                
	numFiles            	0                   
	numPartitions       	0                   
	numRows             	0                   
	rawDataSize         	0                   
	totalSize           	0                   
	transient_lastDdlTime	1575820956          
		
# Storage Information		
SerDe Library:      	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	
InputFormat:        	org.apache.hadoop.mapred.TextInputFormat	
OutputFormat:       	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat	
Compressed:         	No                  	
Num Buckets:        	4                   	
Bucket Columns:     	[id]                	
Sort Columns:       	[Order(col:id, order:1)]	
Storage Desc Params:		
	field.delim         	\t                  
	serialization.format	\t                  
		
# Constraints		
		
# Primary Key		
Table:              	default.user_partition_bucket_pk	
Constraint Name:    	pk_252586861_1575820956959_0	
Column Names:       	id1                 	id2

desc formatted user_partition_bucket_fk

# col_name	data_type	comment
id	int	ID
name	string	姓名
age	int	年龄
id1	int	
id2	int	
		
# Partition Information		
# col_name	data_type	comment
date_id	string	
		
# Detailed Table Information		
Database:           	default             	
OwnerType:          	USER                	
Owner:              	root                	
CreateTime:         	Mon Dec 09 00:03:01 CST 2019	
LastAccessTime:     	UNKNOWN             	
Retention:          	0                   	
Location:           	hdfs://node01:9000/user/hive/warehouse/user_partition_bucket_fk	
Table Type:         	MANAGED_TABLE       	
Table Parameters:		
	COLUMN_STATS_ACCURATE	{\"BASIC_STATS\":\"true\"}
	SORTBUCKETCOLSPREFIX	TRUE                
	bucketing_version   	2                   
	comment             	测试分桶                
	numFiles            	0                   
	numPartitions       	0                   
	numRows             	0                   
	rawDataSize         	0                   
	totalSize           	0                   
	transient_lastDdlTime	1575820981          
		
# Storage Information		
SerDe Library:      	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	
InputFormat:        	org.apache.hadoop.mapred.TextInputFormat	
OutputFormat:       	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat	
Compressed:         	No                  	
Num Buckets:        	4                   	
Bucket Columns:     	[id]                	
Sort Columns:       	[Order(col:id, order:1)]	
Storage Desc Params:		
	field.delim         	\t                  
	serialization.format	\t                  
		
# Constraints		
		
# Foreign Keys		
Table:              	default.user_partition_bucket_fk	
Constraint Name:    	c1                  	
Parent Column Name:default.user_partition_bucket_pk.id1	Column Name:id1     	Key Sequence:1      
Parent Column Name:default.user_partition_bucket_pk.id2	Column Name:id2     	Key Sequence:2

在这里插入图片描述
desc formatted user_partition_bucket_fk
desc formatted user_partition_bucket_pk