create [temporary] [external] table [if not exists] [db.name] table
[(col_name data_type [column_constraint_specification] [comment col_comment], ... [constraint specification] )]
[comment table_comment]
[partitioned by (col_name data_type [comment col_comment], ...)] -- 分区字段
[clustered by (col_name data_type [comment col_comment] ) [sorted by (col_name [ase|desc], ...)] into num_buckets buckets]
[row format fow_format]
[stored as file_format]
[location hdfs_path]
[tblproperties (property_name = property_value, ...)];
-- 样例
create external table if not exists tmp.create_table_test (
sku_id string comment "商品编号" ,
cid3 int comment "三级品类id",
price float comment "价格",
sale_qtty int comment "销量"
)
comment "测试使用"
partitioned by (dt string comment "日期")
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
location
'hdfs://ns7/user/mart_scr/tmp.db/create_table_test'
tblproperties (
"author" = 'xueyuan24',
"mart_name" = "mart_rmb"
);
复制表结构,使用like建表
create [temporary] [external] table [if not exists] [db.name]table_name like existing_table_or_view_name
[location hdfs_path]
-- 样例
create external table if not exists tmp.create_table_test_like like tmp.create_table_test;
使用 as select 创建表 CTAS
create table if not exists tmp.create_table_test_select as select * from tmp.create_table_test;
-- 不能创建partition, external, bucket table