Hive建表语句浅析

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(column_name data_type [COMMENT column_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (column_name data_type [COMMENT column_comment], ...)]
[CLUSTERED BY (column_name1, column_name2, ...)]
[SORTED BY (column_name [ASC|DESC], ...) INTO num_buckets BUCKETS]
[ROW FORMAT DELIMITED]
  [FIELDS TERMINATED BY fields_format]
  [LINES TERMINATED BY lines_format]
[STORED AS file_format]
[LOCATION hdfs_path]

• CREATE TABLE

建表关键字，如果表已存在，可以用IF NOT EXISTS忽略异常

• EXTERNAL

该关键字指定创建外表，默认不加是创建内表（内表由hive管理生命周期（元数据+数据），外表hive只管理元数据）

• COMMENT

字段和表注释

• PARTITIONED BY

分区表关键字

• CLUSTERED BY

分通表关键字

• SORTED BY ... INTO * BUCKETS

分通表桶内排序

• ROW FORMAT DELIMITED

表存储分隔符，FIELDS TERMINATED BY字段分隔符，LINES TERMINATED BY行分隔符

• STORED AS SEQUENCEFILE/TEXTFILE/RCFILE

表存储方式，TEXTFILE纯文本，SEQUENCEFILE数据压缩

• LOCATION

表数据在hdfs上的存储路径，内表一般不需要自定义，外表需要指定