Hive中创建表的各种方式以及区别详解

一：标准hql建表语法

标准语法:（ hql语言也不区分大小写,下面各个条件选择添加 ）

  CREATE [EXTERNAL] TABLE [ IF NOT EXISTS] table_name

  [(col_name data_type [COMMENT col_comment], ...)]  comment ‘’注释

  [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]声 明表中某个字段是有序的

  [ ROW FORMAT row_format ]    row format delimited fiedls terminated by '\t';(一般必加的语句)

  [STORED AS file_format]

  [LOCATION hdfs_path]   针对的extetnal外部表，要指定存储路径，不指定也没事，默认路径。内部表不用指定，默认路径

各项参数说明：

1.- CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

2-  EXTERNAL关键字可以让用户创建一个外部表，在建表的必须同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

4. -  ROW FORMAT   DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] hive的字段可以是集合等复杂结构  [MAP KEYS TERMINATED BY char] [LINES

3. - LIKE 允许用户复制现有的表结构，但是不复制数据。  TERMINATED BY char]  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]    用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive通过 SerDe 确定表的具体的列的数据 。

5.- STORED AS   SEQUENCEFILE|TEXTFILE|RCFILE如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。这里主要牵涉到hive存储的三种文件格式，具体区别参考另外博客http://blog.csdn.net/qq_26442553/article/details/79313898

6、 CLUSTERED BY对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

    （hash散列）把表（或者分区）组织成桶（Bucket）有两个理由：（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

二：举例创建表的三种方法

     Hive创建表的方式（默认路径/user/hive/warehouse，也可以location指定，主要针对external表）            

1、使用create命令创建一个新表,带分区

    CREATE  TABLE `FDM_SOR.mytest_deptaddr`(  
      `dept_no` int,   
      `addr` string,   
      `tel` string)
    partitioned by(statis_date string ) 
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  

   可以使用describe formatted mytest_deptaddr 查看建表相关的各种配置属性以及默认属性。从下面可以看出，在创建表时有很多属性，比如存储地址，存储格式等属性我们都没有直接配置，而是选择了系统默认的。

 > describe formatted mytest_deptaddr;
OK
col_name                data_type              comment
# col_name              data_type               comment             

dept_no int
addr string
tel string

# Partition Information
# col_name data_type comment

statis_date string

# Detailed Table Information
Database: fdm_sor
Owner: robot
CreateTime: Wed Mar 07 23:49:08 CST 2018
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://hadoop102:9000/user/hive/warehouse/fdm_sor.db/mytest_deptaddr
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1520437748

# 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:
field.delim ,
serialization.format ,

2、把一张表的某些字段抽取出来，创建成一张新表，使用as

    create table mytest_tmp1 

        as   

       select *  from FDM_SOR.mytest_deptaddr where statis_date=‘20180229’;

   注意： 1.as只会复制属性以及属性值到新的表中

          2.使用as创建的表，并不会带原表的分区（分区丢失），包扣一些字段的约束等（可以通过describe formatted查看）

          3.新表中会将原表的分区当做字段出现在新表中。

> describe formatted mytest_tmp1 ;

OK

col_name                data_type              comment

# col_name              data_type               comment             

dept_no int
addr string
tel string
statis_date string

# Detailed Table Information
Database: fdm_sor
Owner: robot
CreateTime: Wed Mar 07 23:57:46 CST 2018
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://hadoop102:9000/user/hive/warehouse/fdm_sor.db/mytest_tmp1
Table Type: MANAGED_TABLE
Table Parameters:
COLUMN_STATS_ACCURATE true
numFiles 1
numRows 0
rawDataSize 0
totalSize 0
transient_lastDdlTime 1520438266

# 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

3、复制表结构，使用like创建表

    create table mytest_tmp like FDM_SOR.mytest_deptaddr;

    注意： 不会复制表中属性值，只会复制表结构（包扣表的分区以及存储格式之类的，区别as）