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