1. 以常见的方式建表:
CREATE TABLE IF NOT EXISTS `test.test1`(
`user_id` int,
`name` string,
`address` string);
2. 以as方式建表:
会复制表属性和值,分区将会以字段的形式保留
CREATE TABLE IF NOT EXISTS `test.test3` as
SELECT
user_id,
name,
address,
dt
FROM test.test2
WHERE dt='20210608';
test2的分区以字段的形式保留在test3中,并不是以分区的形式存在
CREATE TABLE IF NOT EXISTS `test.test3`(
`user_id` int,
`name` string,
`address` string,
`dt` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://HDFS1668/usr/hive/warehouse/test.db/test3'
TBLPROPERTIES (
'transient_lastDdlTime'='1623144370');
hive> select * from test.test3;
OK
test3.user_id test3.name test3.address test3.dt
1 xiaoming shanghai 20210608
2 xiaolan beijing 20210608
Time taken: 0.097 seconds, Fetched: 2 row(s)
3. 以like方式建表:
CREATE TABLE IF NOT EXISTS `test.test4` LIKE test.test2;
不会复制表的值,只会复制表的结构(包括分区)
CREATE TABLE IF NOT EXISTS `test.test4`(
`user_id` int,
`name` string,
`address` string)
PARTITIONED BY (
`dt` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://HDFS1668/usr/hive/warehouse/test.db/test4'
TBLPROPERTIES (
'transient_lastDdlTime'='1623144765')
hive> select * from test.test4;
OK
Time taken: 0.145 seconds
总结:
as建表方式和like建表方式的不同:
as会复制另一个表的数据,但分区是以字段形式存在
like不会复制另一个表的数据,但分区字段继续以分区字段保留