hive sql添加表注释 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存储的三种文件格式，具体区别参考另外博客

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）