Hadoop之Hive完整建表语句

柔光的暖阳◎ 2023-07-21 13:28 0阅读 0赞

### Hive完整建表语句 ###

*  完整建表语句

# 完整建表语句 #

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
      [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
      [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]
      [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
         ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
         [STORED AS DIRECTORIES]
      [
       [ROW FORMAT row_format] 
       [STORED AS file_format]
         | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
      ]
      [LOCATION hdfs_path]
      [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
      [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

介绍：  
**\[TEMPORARY\]**：临时表，当sesssion关闭时，表会被删除  
**\[EXTERNAL\]**：外部表,与 \[LOCATION hdfs\_path\]对应，外部表的位置，删除外部表，文件不会从hdfs上删除  
**\[PARTITIONED BY (col\_name data\_type \[COMMENT col\_comment\], …)\]**：根据后面字段进行分区，对应于hdfs上table级下又的子文件夹，注意，不能是hive中已经存在的字段  
**\[CLUSTERED BY (col\_name, col\_name, …) \[SORTED BY (col\_name \[ASC|DESC\], …)\] INTO num\_buckets BUCKETS\]**：分桶，根据后面字段进行分桶，并且排序，对应与mr中的partation，方便数据采样  
**\[SKEWED BY (col\_name, col\_name, …) – (Note: Available in Hive 0.10.0 and later)\] ON ((col\_value, col\_value, …), (col\_value, col\_value, …), …)  
\[STORED AS DIRECTORIES\]**:hive标记这个表是倾斜表， (col\_name, col\_name, …)指倾斜的列，on后跟随的是倾斜的值。  
**\[  
\[ROW FORMAT row\_format\]  
\[STORED AS file\_format\]  
| STORED BY ‘storage.handler.class.name’ \[WITH SERDEPROPERTIES (…)\] – (Note: Available in Hive 0.6.0 and later)  
\]**：ROW FORMAT row\_format 定义数据之间的分割符，\[STORED AS file\_format\]指定文件存储格式，  
**\[TBLPROPERTIES (property\_name=property\_value, …)\]** ：hive中表内置了一部分属性，并且可以根据这个进行表属性的修改  
**\[AS select\_statement\]**：在创建表的时候，导入数据。