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

　

create table

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

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

1.自主创业方式create table 

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

具体参考博客：  https://blog.csdn.net/qq_26442553/article/details/79747906

                        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创建表时要注意的问题

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')

相关阅读

win7 createprocess 如何以当前登陆用户身份运行程序

 开发中有时会遇到这样的问题，当服务程序需要使用某些功能时，由于用户的关系而受到限制，比如访问注册表的HKEY_CURRENT_USER键，使用

CreateProcessAsUser常用参数说明

最近写代码，要把进程启到别的session下，要用到CreateProcessAsUser函数。  查阅了MSDN， 整理了参数说明供日后查阅。先看函数原型BO

Http状态码406(Not Acceptable) 错误问题解决方法

状态码406：HTTP协议状态码的一种，表示无法使用请求的内容特性来响应请求的网页。说白了就是后台的返回结果前台无法解析就报406错误

JDK1.8 --- HashTable和HashMap的区别

HashTable从实现角度和HashMap大致相同。 不同的有以下几点: HashTable不允许key和value为null，HashMap允许key和value为null。

contenteditable换行踩坑心得

做后台编辑器，可伸缩下性的输入框，在H5出了contenteditable之后，基本取代了使用textarea自适应高度的方式。然而，这个属性，除去兼容性