轻松入门大数据第9章入门到进阶：如何快速掌握Hive DDL&DML 9.1 Hive DDL&DML 9.1.5 DDL之创建表语法

深藏阁楼爱情的钟 2024-03-16 09:39 19阅读 0赞

### 轻松入门大数据 ###

#### 文章目录 ####

*   *  轻松入门大数据
     *   *  第9章 入门到进阶：如何快速掌握Hive DDL&DML
         *   *  9.1 Hive DDL&DML
             *   *  9.1.5 DDL之创建表语法

![在这里插入图片描述][f2505d87240e4f258b997cb24cf748fd.png_pic_center]

#### 第9章 入门到进阶：如何快速掌握Hive DDL&DML ####

##### 9.1 Hive DDL&DML #####

> 1.  数据模型
> 2.  DDL之数据库
> 3.  DDL之表
> 4.  内部表vs外部表
> 5.  加载数据方式
> 6.  分区表
> 7.  Hive QL查询

###### 9.1.5 DDL之创建表语法 ######

【重点】

还是看到官网文档：[https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL][https_cwiki.apache.org_confluence_display_Hive_LanguageManual_DDL]

![在这里插入图片描述][deaf3d21e7754bba964b53e286cf41d3.png_pic_center]

当然对表的操作比起数据库就要复杂多了

![在这里插入图片描述][76548f06b4e44d0b97ceefa992bf9b1d.png_pic_center]

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 [column_constraint_specification] [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)
     
    CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
      LIKE existing_table_or_view_name
      [LOCATION hdfs_path];
     
    data_type
      : primitive_type
      | array_type
      | map_type
      | struct_type
      | union_type  -- (Note: Available in Hive 0.7.0 and later)
     
    primitive_type
      : TINYINT
      | SMALLINT
      | INT
      | BIGINT
      | BOOLEAN
      | FLOAT
      | DOUBLE
      | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
      | STRING
      | BINARY      -- (Note: Available in Hive 0.8.0 and later)
      | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
      | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
      | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
      | DATE        -- (Note: Available in Hive 0.12.0 and later)
      | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
      | CHAR        -- (Note: Available in Hive 0.13.0 and later)
     
    array_type
      : ARRAY < data_type >
     
    map_type
      : MAP < primitive_type, data_type >
     
    struct_type
      : STRUCT < col_name : data_type [COMMENT col_comment], ...>
     
    union_type
       : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)
     
    row_format
      : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
            [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
            [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
      | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
     
    file_format:
      : SEQUENCEFILE
      | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
      | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
      | ORC         -- (Note: Available in Hive 0.11.0 and later)
      | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
      | AVRO        -- (Note: Available in Hive 0.14.0 and later)
      | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
      | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
     
    column_constraint_specification:
      : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
     
    default_value:
      : [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] 
     
    constraint_specification:
      : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
        [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
        [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 
        [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
        [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

> 又臭又长

简单分析一下：

**CREATE TABLE xxx**

**EXTERNAL：外部表**

**col\_name data\_type： 字段名 字段类型 ， 多个字段之间使用逗号隔开**

**COMMENT：对表加上注释**

**PARTITIONED BY：指定分区**

**CLUSTERED BY：4大by再讲解**

**ROW FORMAT**  
**DELIMITED FIELDS TERMINATED BY ','**

**STORED AS：以什么格式进行存储**

*  **textfile**
 *  **orc**
 *  **parquet**

**LOCATION hdfs\_path：指定表的路径**

**like：复制表结构，不复制数据**

[f2505d87240e4f258b997cb24cf748fd.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/4aea0a6b57e14e328df37cf7577b6ed5.png
[https_cwiki.apache.org_confluence_display_Hive_LanguageManual_DDL]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
[deaf3d21e7754bba964b53e286cf41d3.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/1a281711a50747e38cc0e7512c7e85b7.png
[76548f06b4e44d0b97ceefa992bf9b1d.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/091f2ca56a4a4c70addee7290032a77b.png