使用create table ...as创建表时要注意的问题

谁践踏了优雅 2022-05-29 05:10 404阅读 0赞

###      工作中有时候做hive开发了，需要对一张表进行备份。一般都会使用 create table as  select(简称:CTAS)...简单方便，但是需要注意CTAS建表产生的问题，因为CTAS建表并不一定会保存原表样式。 ###

1.创建一个分区表
    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 '时间分区')
    STORED AS RCFILE
    
    2.往分区表里导入数据
    3.使用create table as....创建表
     create table t_T_DEDUCT_SIGN_D_copy 
     as 
     select * from T_DEDUCT_SIGN_D where statis_date is not null
    4.查看源表和目标表的结构进行对比
    
    查看源表T_DEDUCT_SIGN_D结构：分区表+RCfiLE存储格式
    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')
    
    查看目标表结果：t_T_DEDUCT_SIGN_D_copy  变成了非分区表+TEXTFILE存储格式+多了字段
    hive (fdm_sor)> show create table  t_T_DEDUCT_SIGN_D_copy;
    OK
    CREATE TABLE `t_T_DEDUCT_SIGN_D_copy`(
      `id` bigint, 
      `sign_no` string, 
      `bp_no` string, 
      `statis_date` string)
    ROW FORMAT SERDE 
      'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
    STORED AS INPUTFORMAT 
      'org.apache.hadoop.mapred.TextInputFormat' 
    OUTPUTFORMAT 
      'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    LOCATION
      'hdfs://SuningHadoop2/user/finance/hive/warehouse/fdm_sor.db/t_t_deduct_sign_d_copy'
    TBLPROPERTIES (
      'transient_lastDdlTime'='1546418132')

### **如上演示可以看出，hive中用CTAS创建表时要注意如下事项：** ###

**    1.hive中用CTAS 创建表，所创建的表统一都是非分区表,不管源表是否是分区表。所以对于分区表的创建使用create table ..as一定要注意分区功能的丢失。当然创建表以后可以添加分区，成为分区表。注意如果源表是非分区表则没有这个问题。**

**    2.如果使用create table as  select  \*  创建表时源表是分区表，则新建的表会多字段，具体多的字段个数和名称就是源表分区的个数和名称。当然如果select选择的是指定的列则不会有这种问题。**

**    3.如果源表的存储格式不是TEXTFILE。则使用CTAS创建的表存储格式会变成默认的格式textfile。比如这里源表是RCFILE。而新建的表则是TEXTFILE。****当然可以在使用create table ....as创建表时指定存储格式和解析格式，甚至是列的名称等属性。具体参考博客：****[hive使用create as创建表指定存储格式等属性][hive_create as]**

**   4.使用CTAS方式创建的表不能是外部表。**

**   5.使用CTAS创建的表不能分桶表。**

1.创建一个外部表
    CREATE  external  TABLE T_DEDUCT_SIGN_D_external(
      id bigint COMMENT '主键ID',
      sign_no string COMMENT '签约协议号',
      bp_no string COMMENT '商户号'
      )COMMENT '代扣签约表'
    PARTITIONED BY (
      statis_date string COMMENT '时间分区')
    STORED AS RCFILE
    
    1.使用CTAS带external创建外部表失败：CTAS创建不了外部表
    hive (fdm_sor)> create external table T_T_DEDUCT_SIGN_D_external_COPY 
                  > AS 
                  > SELECT * FROM T_DEDUCT_SIGN_D_external
                  > WHERE STATIS_DATE ='20180101';
    FAILED: SemanticException [Error 10070]: CREATE-TABLE-AS-SELECT cannot create external table
    
    2.不带external使用CTAS创建表，但不是外部表，默认内部表
    create table T_T_DEDUCT_SIGN_D_external_COPY 
    AS
    SELECT * FROM T_DEDUCT_SIGN_D_external
    WHERE STATIS_DATE ='20180101';
    
    hive (fdm_sor)> describe formatted T_T_DEDUCT_SIGN_D_external_COPY;
    OK
    # col_name            	data_type           	comment             
    	 	 
    id                  	bigint              	                    
    sign_no             	string              	                    
    bp_no               	string              	                    
    statis_date         	string              	                    
    	 	 
    # Detailed Table Information	 	 
    Database:           	fdm_sor             	 
    Owner:              	finance             	 
    CreateTime:         	Wed Jan 02 18:55:23 CST 2019	 
    LastAccessTime:     	UNKNOWN             	 
    Protect Mode:       	None                	 
    Retention:          	0                   	 
    Location:           	hdfs://SuningHadoop2/user/finance/hive/warehouse/fdm_sor.db/t_t_deduct_sign_d_external_copy	 
    Table Type:         	MANAGED_TABLE       	 
    Table Parameters:	 	 
    	transient_lastDdlTime	1546426523          
    	 	 
    # Storage Information	 	 
    SerDe Library:      	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	 
    InputFormat:        	org.apache.hadoop.mapred.TextInputFormat	 
    OutputFormat:       	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat	 
    Compressed:         	No                  	 
    Num Buckets:        	-1                  	 
    Bucket Columns:     	[]                  	 
    Sort Columns:       	[]                  	 
    Storage Desc Params:	 	 
    	serialization.format	1                   
    Time taken: 0.079 seconds, Fetched: 29 row(s)

### **但对于oracle和mysql用create table as 创建表要注意如下事项：** ###

**查询官网发现原来使as创建表会有如下问题：**

**Oracle Database automatically defines on columns in the new table any  NOT NULL  constraints that were explicitly created on the corresponding columns of the selected table if the subquery selects the column rather than an expression containing the column. If any rows violate the constraint, then the database does not create the table and returns an error.**

**       1.显示的NOT NULL约束自动会带到新表。**

**NOT NULL  constraints that were implicitly created by Oracle Database on columns of the selected table (for example, for primary keys) are not carried over to the new table.**

> **       2.隐式的NOT NULL约束不会带到新表，如主键。**

**In addition, primary keys, unique keys, foreign keys, check constraints, partitioning criteria, indexes, and column default values are not carried over to the new table.**

> **      3.另外最关键的是，主键，唯一，外键，check约束，分区，索引以及列的默认值不会带到新表。**

**If the selected table is partitioned, then you can choose whether the new table will be partitioned the same way, partitioned differently, or not partitioned. Partitioning is not carried over to the new table. Specify any desired partitioning as part of the  CREATE TABLE  statement before the  AS subquery  clause.**

[hive_create as]: https://blog.csdn.net/qq_26442553/article/details/85621767