Hive建表语法及解释

建表语法：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

各关键字解释：

COMMENT col_comment：字段说明。

COMMENT table_comment：表说明。

PARTITIONED BY：创建分区表。

CLUSTERED BY：创建分桶表。

SORTED BY：指定某个桶中按照某个字段排序。

ROW FORMAT：同下面命令使用指定分隔符或者序列化反序列化方式。
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]

STORED AS：指定文件类型。

LOCATION：表数据在hdfs上的存储路径。

TBLPROPERTIES：表属性信息。

AS：根据后面的查询结果创建表。