创建数据表

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [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]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
 

• CREATE:创建。
• [TEMPORARY]：临时表,当前session有效,session退出后,表自动删除。
• [EXTERNAL]：外部表，同内部表（托管表）区别是外部表数据由hdfs管理，删除外部表仅仅会删除元数据，HDFS上的数据文件并不会被删除，而删除内部表会直接删除元数据（metadata）及存储的数据，修改外部表时需要用msck repair table 表名。外部表一般用在加载已存在hdfs上的文件。系统上默认是内部表，由hive来管理meta、file、statistics数据。
• [IF NOT EXISTS]：判断表是否不存在，当表已存在时，不会继续操作。
• TABLE：建表关键字。
• [db_name.]table_name：数据库名.表名，如果不写数据库名为当前use的数据库。
• [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]：表字段名 字段类型 字段约束 字段描述 表约束等。
  dataType 详见hive支持的数据类型章节（大数据之-HIVE入门（五））；
  约束和事务都是hive3.0以上版本新加的特性，有可能在你的hive上不工作，下面为列约束和表约束说明。

约束关键词列表如下：
CHECK 字段值范围约束
DEFAULT 缺省值[LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL] 
PRIMARY KEY 表主键
FOREIGN KEY 表外键
UNIQUE KEY 字段值唯一
NOT NULL 不空

约束修饰词列表如下：
ENABLE 确保所有传入的数据符合约束。
DISABLE 不保证所有传入的数据符合约束。
VALIDATE 检查表中的全部已存在数据符合约束。
NOVALIDATE 不检查表中的全部已存在数据符合约束。
ENFORCED 同ENABLE NOVALIDATE 只检查传入数据，不校验已存在的数据 
NOT ENFORCED 同DISABLE NOVALIDATE 不检查传入数据，不校验已存在的数据 
RELY 遵守约束
NORELY 不遵守约束
注意：
1.ENABLE 是  NOVALIDATE RELY  
2.DISABLE 是 NOVALIDATE NORELY
3.如果没有指定修饰词那么缺省是ENABLE NOVALIDATE RELY
4.PRIMARY KEY FOREIGN KEY UNIQUE KEY 不支持ENABLE

举例：
1.创建表t指定b列不空。CREATE TABLE t(a TINYINT, b SMALLINT NOT NULL ENABLE, c INT);
2.创建表Persons和BusinessUnit指定某此字段有缺省值并且指定主键和外键。CREATE TABLE Persons (   
     ID INT NOT NULL,   
     Name STRING NOT NULL,   
     Age INT,
     Creator STRING DEFAULT CURRENT_USER(),    
     CreateDate DATE DEFAULT CURRENT_DATE(),
     PRIMARY KEY (ID) DISABLE NOVALIDATE);
     
     CREATE TABLE BusinessUnit (
     ID INT NOT NULL,    
     Head INT NOT NULL,
     Creator STRING DEFAULT CURRENT_USER(),    
     CreateDate DATE DEFAULT CURRENT_DATE(),
     PRIMARY KEY (ID) DISABLE NOVALIDATE,
     CONSTRAINT fk FOREIGN KEY (Head) REFERENCES Persons(ID) DISABLE NOVALIDATE
     );

• [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]：分桶
• [SKEWED BY (col_name, col_name, ...)] ON ((col_value, col_value, ...), (col_value, col_value, ...), ...) [STORED AS DIRECTORIES]：数据倾斜列和值特征指定。

建立有两列倾斜值处理的表：
CREATE TABLE tbl_bucket_multiple_sample (col1 INT, col2 STRING, col3 STRING,col4 STRING)
 SKEWED BY (col1, col2) ON ((1,'string1'), (99,'string10'), (9999,'string100')) [STORED AS DIRECTORIES];

[ROW FORMAT row_format]:行格式设置，列间符号、逃逸符号、行结束符设置等。

DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later)
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

• [STORED AS file_format]:文件格式设置。

SEQUENCEFILE   --是Hadoop API提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点。
| TEXTFILE -- (Default, depending on hive.default.fileformat configuration) --每一行都是一条记录，每行都以换行符（\ n）结尾。数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用。
| RCFILE -- (Note: Available in Hive 0.6.0 and later)--是一种行列存储相结合的存储方式
| ORC -- (Note: Available in Hive 0.11.0 and later)--是一种优化的记录柱面格式，在处理大型表时效率较高。
| PARQUET -- (Note: Available in Hive 0.13.0 and later)--是一个面向列存储的二进制文件格式。Parquet对于大型查询非常高效，默认采用snappy压缩，也可以用gzip。
| AVRO -- (Note: Available in Hive 0.14.0 and later)--是开源项目支持的格式，为Hadoop提供数据序列化和数据交换服务。
| JSONFILE -- (Note: Available in Hive 4.0.0 and later)--是json格式文件,
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname--自定义格式。

• [LOCATION hdfs_path]：指定表的存储位置。
• [TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later)：表属性
• [AS select_statement]：从其它查询获取建表。

举例：

1.复制已存在的表或视图格式不复制数据

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];

2.创建一个文本格式的内部表

CREATE TABLE IF NOT EXISTS textfile_table(
id bigint,
pv bigint)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
stored as textfile;

3.创建一个带分区的表

create table table_name (
  id                int,
  name              string
) partitioned by (dt string);

4.创建一个parquet表，建立分区字段并指定压缩算法

CREATE TABLE　parquet_compress_test(
  id bigint,
  name string) partitioned by (dt string)
STORED AS PARQUET TBLPROPERTIES('parquet.compression'='SNAPPY');

5.create table as 建表并从源表获取相关数据

CREATE TABLE tbl_as_test AS select id,name,dt from test where dt='2022-03-01';