create table ,create table as 与create like三种建表方式的使用详解

浅浅的花香味﹌ 2022-03-30 21:06 363阅读 0赞

Hive的hql是基于sql而来，而sql中关于表的创建有几种方式。同样，hive也支持这些表的创建方式。

** 官网文档关于建表的地址**：[hive常见建表方式官网文档][hive]

## 1.自主创业方式create table  ##

**  使用create table从无到有，按照建表的格式和要求实现建表。实际开发中较常用的方式，主要可以定制化建表。**

具体参考博客：  [https://blog.csdn.net/qq\_26442553/article/details/79747906][https_blog.csdn.net_qq_26442553_article_details_79747906]

[https://blog.csdn.net/qq\_26442553/article/details/78805443][https_blog.csdn.net_qq_26442553_article_details_78805443]

CREATE TABLE DEDUCT_SIGN_D`(
      `id` bigint COMMENT '主键ID',
      `sign_no` string COMMENT '签约协议号',
      `bp_no` string COMMENT '商户号',
      `comments` string COMMENT '备注',
      `etl_create_time` string COMMENT 'ETL创建日期',
      `etl_update_time` string COMMENT 'ETL更新日期')
    COMMENT '代扣签约表'
    PARTITIONED BY (
      `statis_date` string COMMENT '时间分区')

## 2.懒人模式create table ...as select..（CTAS） ##

**   create table ......as 这种方式建表，很常用。主要是在hive项目开发中，会有很多逻辑聚合，以及临时表等情况的时候。**

### **2.1使用CTAS创建表的好处** ###

**   1.**CTAS语句中使用查询的结果创建和填充表。由CTAS创建的表是原子的，这意味着在填充所有查询结果之前，其他用户不会看到该表。因此，其他用户要么看到带有完整查询结果的表，要么根本看不到表。

2.在CTAS中有两个部分，select选择部分可以是HiveQL支持的任何选择语句。CTAS的create创建部分从select选择部分获取结果模式，并可以使用SerDe和存储格式等其他表属性创建指定的目标表。比如指定行列切分格式等属性。

**    但是使用CTAS建表会有表结构的变化，以及使用CTAS建表的缺陷和限制，尤其要注意。具体参考博客：[使用create table ...as创建表时要注意的问题][create table ...as]**

### 2.2使用CTAS创建表的演示 ###

1.直接使用CTAS创建表,不指定创建部分的约束
    create table finance.tmp_dm_rpt_110015_fsa_app_d_23 
    as 
    select  a.stat_date as stat_date    
      ,a.intfc_tp as intfc_tp
      ,a.trmnl_tp_id as trmnl_tp_id
      ,a.app_version  as app_version 
      ,a.clnt_chnl  as clnt_chnl
      ,a.trmnl_clnt as  trmnl_clnt 
    from  finance.tmp_dm_rpt_110015_fsa_app_d_20_04  a
    inner join finance.tmp_dm_rpt_110015_fsa_app_d_22 b
    on b.trmnl_clnt=concat(a.trmnl_tp_id,a.clnt_id);
    
    2.在create创建部分指定表的存储格式等属性，比如指定新表的行列分隔符，存储格式等。
    CREATE TABLE T_DEDUCT_SIGN_D_CTAS
       ROW FORMAT DELIMITED FIELDS TERMINATED BY '&'
       STORED AS ORC
    AS
    SELECT *  FROM T_DEDUCT_SIGN_D WHERE STATIS_DATE IS NOT NULL;
    
    查看使用CTAS建表后的表结构：
    hive (fdm_sor)> SHOW CREATE TABLE T_DEDUCT_SIGN_D_CTAS;
    OK
    CREATE TABLE `T_DEDUCT_SIGN_D_CTAS`(
      `id` bigint, 
      `sign_no` string, 
      `bp_no` string, 
      `statis_date` string)
    ROW FORMAT DELIMITED 
      FIELDS TERMINATED BY '&' 
    STORED AS INPUTFORMAT 
      'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
    OUTPUTFORMAT 
      'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
    LOCATION
      'hdfs://SuningHadoop2/user/finance/hive/warehouse/fdm_sor.db/t_deduct_sign_d_ctas'
    TBLPROPERTIES (
      'transient_lastDdlTime'='1546422100')
    
    查看表中的数据
    hive (fdm_sor)> hive (fdm_sor)> select count(*) from  T_DEDUCT_SIGN_D_CTAS;
    438

### **总结：如上使用CTAS创建的表也可以指定存储格式，字段名等。比如在select的时候给列指定别名，则在新表中就会将源表的列实现了重命名。此外在创建表时给目标表指定特定存储格式，也可以完成数据从源表的存储格式到目标表存储格式的转换。这是hive一个很强大的功能，数据存储格式的转换。后面也可以通过insert实现。** ###

### **3.半自动化模式create table like.....** ###

**  使用这种方式创建的表用官网的话来说就是：**The LIKE form of CREATE TABLE allows you to copy an existing table definition exactly (without copying its data). In contrast to CTAS, the statement below creates a new empty\_key\_value\_store table whose definition exactly matches the existing key\_value\_store in all particulars other than table name. The new table contains no rows.**大致意思就是：CREATE TABLE创建表的形式允许您精确地复制现有表定义(不复制其数据)，其创建的表除了表名和源表不一样外，其余所有的细节都是一样的。但是没有源表的数据。所以这种create  table like的形式非常适合对源表模式的复制。实际开发中其实用的也不是太多。**

create table t__sign_d_like  like T_DEDUCT_SIGN_D
    #查看目标表结构
    hive (fdm_sor)> show create table t__sign_d_like;
    OK
    CREATE TABLE `t__sign_d_like`(
      `id` bigint COMMENT '主键ID', 
      `sign_no` string COMMENT '签约协议号', 
      `bp_no` string COMMENT '商户号')
    PARTITIONED BY ( 
      `statis_date` string COMMENT '时间分区')
    ROW FORMAT SERDE 
      'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe' 
    STORED AS INPUTFORMAT 
      'org.apache.hadoop.hive.ql.io.RCFileInputFormat' 
    OUTPUTFORMAT 
      'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'
    LOCATION
      'hdfs://SuningHadoop2/user/finance/hive/warehouse/fdm_sor.db/t__sign_d_like'
    TBLPROPERTIES (
      'transient_lastDdlTime'='1546420764')
    
    #查看源表结构：
    hive (fdm_sor)> show create table T_DEDUCT_SIGN_D;
    CREATE TABLE `T_DEDUCT_SIGN_D`(
      `id` bigint COMMENT '主键ID', 
      `sign_no` string COMMENT '签约协议号', 
      `bp_no` string COMMENT '商户号')
    COMMENT '代扣签约表'
    PARTITIONED BY ( 
      `statis_date` string COMMENT '时间分区')
    ROW FORMAT SERDE 
      'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe' 
    STORED AS INPUTFORMAT 
      'org.apache.hadoop.hive.ql.io.RCFileInputFormat' 
    OUTPUTFORMAT 
      'org.apache.hadoop.hive.ql.io.RCFileOutputFormat'
    LOCATION
      'hdfs://SuningHadoop2/user/finance/hive/warehouse/fdm_sor.db/t_deduct_sign_d'
    TBLPROPERTIES (
      'transient_lastDdlTime'='1546417837')

[hive]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTableLike
[https_blog.csdn.net_qq_26442553_article_details_79747906]: https://blog.csdn.net/qq_26442553/article/details/79747906
[https_blog.csdn.net_qq_26442553_article_details_78805443]: https://blog.csdn.net/qq_26442553/article/details/78805443
[create table ...as]: https://blog.csdn.net/qq_26442553/article/details/79593504