[Hive] 04 - HQL：数据定义（DDL）上-【数据库和表】

男娘i 2022-01-17 06:53 459阅读 0赞

**环境**

*  宿主机：Windows 10 64\_bit
 *  虚拟机：VMware pro 12
    
     *  CentOS 7.5 64\_bit（1个master、2个slave）
     *  Hadoop-2.6.5
     *  MariaDB-5.5.60
     *  Hive 1.2.2
 *  SecureCRT 7.3

**目录**

预热

0、关键字、和它们的大小写

1、数据库：创建、使用、修改、删除

\----1.0 创建数据库

\----1.1 使用数据库

\----1.2 修改数据库

2、表的创建、删除、截断，修改表、分区、列

\----2.0 创建表

\----|----2.0.0 托管表、外部表

\----|----2.0.1 对表的存储管理

\----|----|------2.0.1.0 文件格式

\----|----|------2.0.1.1 行格式和SerDe

\----|----2.0.2 分区表

\----|----2.0.3 `create table as select` (CTAS)方式建表

\----|----2.0.4 `create table like`方式创建表

\----|----2.0.5 排序的分桶表

\----|----2.0.6 倾斜表

\----|----2.0.7 临时表

\----|----2.0.8 事务性表

\----|----2.0.9 约束

\----2.1 删除表

\----2.2 截断表

\----2.3 修改：表、分区、列

\----|----2.3.0 `alter table`修改表的名称、属性、注释、SerDe属性、存储属性、倾斜、存储为目录、约束等

\----|----2.3.1 修改分区

\----|----2.3.2 修改表或分区

\----|----2.3.3 修改列

**正文**

**预热**  
认识几个有可能常见的缩写词：基于SQL语言的4大分类

*  DDL（Data Definition Language，数据定义语言），用于创建数据库中的各种对象，如：数据库、表、视图等
 *  DML（Data Manipulation Language，数据操作语言），主要有三种形式：但Hive不支持下方某个操作  
    1、插入：`INSERT`  
    2、更新：`UPDATE`  
    3、删除：`DELETE`
 *  DQL（Data QueryLanguage，数据查询语言），主要是由`SELECT`子句，`FROM`子句，`WHERE`子句组成的查询块：  
    `SELECT <字段名表>`  
    `FROM <表或视图名>`  
    `WHERE <查询条件>`
 *  DCL（Data Control Language，数据控制语言），用于授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等

本文聚焦于HQL DDL。  
![在这里插入图片描述][20190613204550639.png]

<table> 
 <thead> 
  <tr> 
   <th>名词</th> 
   <th>描述</th> 
   <th>对应HDFS目录</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Database</td> 
   <td>Hive可包含多个数据库，默认数据库是<code>default</code>。存储位置通过<code>hive-site.xml</code>的<code>hive.metastore.warehouse.dir</code>进行设置</td> 
   <td><code>/user/hive/warehouse</code></td> 
  </tr> 
  <tr> 
   <td>Table</td> 
   <td>Hive中表分为：内部表、外部表，每个表对应HDFS上一个目录</td> 
   <td><code>/user/hive/warehouse/[database_name.db]/tables</code></td> 
  </tr> 
  <tr> 
   <td>Partition</td> 
   <td>分区，使用某个字段对表进行分区，方便查询、提高效率，每个分区对应HDFS上一个分区目录</td> 
   <td><code>/user/hive/warehouse/[database_name.db]/table/partitions</code></td> 
  </tr> 
  <tr> 
   <td>Bucket</td> 
   <td>桶，分区下还可以进行分桶，对表的数据进行更细致的区分，在HDFS上有对应的分桶目录</td> 
   <td><code>/user/hive/warehouse/[database_name.db]/table/partitions/buckets</code></td> 
  </tr> 
 </tbody> 
</table>

上述对应HDFS目录是由`hive-site.xml`中属性`hive.metastore.warehouse.dir`配置的。

# 0、关键字、和它们的大小写 #

不同的语言，对于如下概念可能会有差异：

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>关键字（Keyword）</td> 
   <td>规定有特殊意义的词。即：在语言中有特定含义，成为语法中一部分的那些字</td> 
  </tr> 
  <tr> 
   <td>保留字（Reserved Keyword）</td> 
   <td>系统留用(可能永远也不会用, 但是你不能用)。也就是说：语言中已经定义过的字，使用者不能再将这些字作为变量名或函数名等使用</td> 
  </tr> 
 </tbody> 
</table>

在一些语言中，一些保留字可能并没有应用于当前的语法中，这就成了保留字与关键字的区别。  
![在这里插入图片描述][2019061312170474.png]

在**HQL DDL**中，不同的Hive版本，拥有的关键字、保留字、非保留字有略微不同，具体可[参考][Link 1]。

如果程序开发者仍想使用保留字作为标识符，需要做以下操作：

*  将保留字使用引号包围，便表示允许将其作为标识符使用；
 *  设置`hive.support.sql11.reserved.keywords=false`。默认为`true`。（`Hive-2.1.0`及更早版本支持）

在使用**关键字、或标识符、甚至表名和列名**的过程中，除了Serde、属性名称、`Hive0.12.0 及更早版本`的索引名外，是不区分大小写的。

在Hive尽量不要Tab键来进行缩进，因为Tab键一般用来关键字补全功能的。所以，用空格缩进（indent）。

# 1、数据库：创建、使用、修改、删除 #

在Hive中，数据库的概念本质上仅仅是**表**的一个目录、或命名空间，它将生产表组织成逻辑组。这个概念对于有很多组、用户的大集群，是很重要的，可以避免表命名冲突。

## 1.0 创建数据库 ##

语法：

create (database|schema) [if not exists] database_name
    [comment database_comment]
    [location hdfs_path]
    [with dbproperties (property_name=property_value, ...)];

*  `database`、`schema`两者含义相同，用途是一样的，用哪个都行；
 *  `if not exists`创建数据库时，如果有同名数据库已存在，没有这个子句将抛出错误信息；有这个子句，将不会抛出警告信息，这对于在继续执行之前需要根据需要来实时创建数据库的情况很有用；
 *  `comment`为database添加描述信息；
 *  `location`当不写它时，默认是会将数据库存储在由`/conf/hive-site.xml`中属性`hive.metastore.warehouse.dir`指定的目录下（HDFS，`/user/hive/warehouse`）；
 *  `with dbproperties`为数据库添加一些相关的键值对属性信息，如创建时间、作者等信息。【`Hive 0.7`起引入】

hive> create schema foo1_db
        > comment "This is my first db foo1_db."
        > with dbproperties ("author"="cyg","date"="2019-06-13");
    OK
    Time taken: 3.52 seconds
    hive> describe schema foo1_db;
    OK
    foo1_db This is my first db foo1_db.    hdfs://master:9000/user/hive/warehouse/foo1_db.db   root    USER
    Time taken: 0.683 seconds, Fetched: 1 row(s)
    hive> describe schema extended foo1_db --加上extended会显示dbproperties信息
        > ;
    OK
    foo1_db This is my first db foo1_db.    hdfs://master:9000/user/hive/warehouse/foo1_db.db       root    USER    {date=2019-06-13, author=cyg}
    Time taken: 0.072 seconds, Fetched: 1 row(s)
    hive> create schema foo1_db;
    FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database foo1_db already exists
    hive> create schema if not exists foo1_db--不会报错，同时也不会创建一个新的同名的数据库来覆盖旧的;
    OK
    Time taken: 0.054 seconds

查看数据库存储位置：

[root@master src]# hadoop fs -ls /user/hive/warehouse
    drwxr-xr-x   - root supergroup          0 2019-06-13 16:23 /user/hive/warehouse/foo1_db.db

Hive会为每个数据库创建一个目录，目录名以`.db`结尾。数据库中的表将会以这个目录的子目录形式存储。有个例外，`default`数据库中的表，这个数据库本身没有自己的目录，所以它的表是直接放在HDFS`/user/hive/warehouse`下面的。

hive> create schema foo2--通过location修改为非默认的存储位置。需要加上数据库名foo2.db，否则创建不成功。当然这个存储位置还是在HDFS，而不是本地文件系统下。
        > location '/usr/local/src/foo2.db';
    OK
    Time taken: 0.284 seconds

## 1.1 使用数据库 ##

语法：

use database_name;
    use default;

如果用户没有显示地指定哪个数据库，那么将会使用默认的数据库`default`。  
显示当前所在的数据库名称：

hive> set hive.cli.print.current.db=true;
    hive (default)> set hive.cli.print.current.db=false;
    hive>

## 1.2 修改数据库 ##

语法：

alter (database|schema) database_name set dbproperties (property_name=property_value, ...);
    alter (database|schema) database_name set owner [user|role] user_or_role;
    alter (database|schema) database_name set location hafs_path;

*  `alter database ...set location`语句，并不会将数据库的当前目录中的内容移动到新的指定位置。并不改变指定的数据库下的所有表或分区相关联的位置。它只是修改了即将添加到这个数据库中的新表的默认父目录；
 *  `alter database ... set owner [user|role] user_or_role;`修改数据库的用户或权限；
 *   *  数据库中其他元数据是不可用修改的，包括数据库名和数据库所在的目录位置。

## 1.3 删除数据库 ##

语法：

drop (database|schema) [if exists] database_name [restrict|cascade];

*  默认是`restrict`【限制】，若数据库不为空，将删除失败；
 *  删除包含表的数据库，需加上`cascade`【级联】。

# 2、表的创建、删除、截断，修改表、分区、列 #

Hive的`create table`语句在遵循SQL语法惯例基础上，具有显著的功能扩展、更广泛的灵活性。例如：可以定义表的数据文件存储在什么位置、使用哪个[存储格式][Link 2]，等。

## 2.0 创建表 ##

语法：

create [temporary] [external] table [if not exists] [db_name.]table_name --temporay是Hive-0.14.0起引入的
    [(col_name data_type [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, ...)]  --Hive-0.10.0起引入的
    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 serdepropertied(...)]
    ] 
    [location hdfs_path]
    [tblproperties (property_name=property_value, ...)]
    [as select_statement]; --这一行不支持外部表
    
    create [temporary] [external] table [if not exists]
    [db_name.]table_name
    like existing_table_or_view_name
    [location hdfs_path];
    
    
    data_type
      : primitive_type
      | array_type
      | map_type
      | struct_type
      | union_type   --Hive-0.7.0起引入的
     
    primitive_type
      : TINYINT
      | SMALLINT
      | INT
      | BIGINT
      | BOOLEAN
      | FLOAT
      | DOUBLE
      | DOUBLE PRECISION  --Hive-2.2.0起引入的
      | STRING
      | BINARY       --Hive-0.8.0起引入的
      | TIMESTAMP   --Hive-0.8.0起引入的
      | DECIMAL     --Hive-0.11.0起引入的
      | DECIMAL(precision, scale)  --Hive-0.13.0起引入的
      | DATE        --Hive-0.12.0起引入的
      | VARCHAR     --Hive-0.12.0起引入的
      | CHAR        --Hive-0.13.0起引入的
     
    array_type
      : ARRAY < data_type >
     
    map_type
      : MAP < primitive_type, data_type >
     
    struct_type
      : STRUCT < col_name : data_type [COMMENT col_comment], ...>
     
    union_type
       : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)
     
    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, ...)]
     
    file_format:
      : SEQUENCEFILE
      | TEXTFILE    -- 默认的，依赖hive.default.fileformat配置
      | RCFILE      --Hive-0.6.0起引入的
      | ORC         --Hive-0.11.0起引入的
      | PARQUET     --Hive-0.13.0起引入的
      | AVRO       --Hive-0.14.0起引入的
      | JSONFILE    --Hive-4.0.0起引入的
      | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
     
    constraint_specification: --约束和规范
      : [, primary key (col_name, ...) disable novalidate]
        [, constraint constraint_name foreign key (col_name, ...) references table_name(col_name, ...) disable novalidate

`create table`根据给定的名称创建表。若已存在一个具有相同名称的表、或视图，则会报错（使用`if not exists`跳过错误）。

*  表名（`table_name`）、列名（`col_name`）不区分大小写。但SERDE、属性名称区分大小写。
 *  表、列的注释（`comment`），使用单引号包围的字符串。
 *  创建表时，有 or 无`external`关键字：
    
     *  1 没有`external`，则认为创建的是托管表（`managed table`。由Hive管理其数据，这也是托管之意由来），这是**默认方式**。
     *  2 加上`external`关键字则表明是创建外部表。
     *  3 `describe extended table_name`可查看它是托管表、还是外部表。
     *  4 托管表与外部表的区别参考下方。
 *  `tblproperties`允许使用键值对 定义表的元数据。一些预定义的表的属性，如 `last_modified_user`、`last_modified_time`，是由Hive自动添加和管理的。其他预定义的表属性可[参考][Link 3]。
 *  `temporary`创建临时表。

### 2.0.0 托管表、外部表 ###

托管表，也称【内部表】。

<table> 
 <thead> 
  <tr> 
   <th>对比项</th> 
   <th>托管表（managed table）</th> 
   <th>外部表（external table）</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>语法</td> 
   <td><code>create table tbl_name</code>，这是缺省情况下的语法</td> 
   <td><code>create external table tbl_name</code>，加上<code>external</code>关键字。</td> 
  </tr> 
  <tr> 
   <td>存储位置</td> 
   <td>缺省情况下，表（元数据、数据，由Hive进程内部管理）存储在<code>hive.metastore.warehouse.dir</code>属性指定HDFS目录下，文件路径形如<code>/user/hive/warehouse/db_name.db/tbl_name/</code>。可通过在创建表时<code>location</code>选项修改存储位置</td> 
   <td>元数据存放在HDFS目录下，数据（源）在外部，如Azure或远程HDFS。可由Hive之外的进程访问或处理</td> 
  </tr> 
  <tr> 
   <td>加载（<code>load</code>）数据时的表现？</td> 
   <td>Hive会把数据移动到仓库目录（<code>/user/hive/warehouse/db_name.db/managed_tbl_name/</code>）。Hive不会去检查表目录中的文件是否符合表所声明的schema。若有数据、schema不匹配，只能在查询时发现</td> 
   <td>不会把数据移动到自己的仓库目录，因为有<code>external</code>关键字，Hive就知道数据并不是由自己管理。甚至不会检查外部文件位置是否存在，这是很有用的特性，允许数据推迟到创建表之后进行</td> 
  </tr> 
  <tr> 
   <td>删除表（<code>drop</code>）时的影响、表现？</td> 
   <td>托管表/分区被删除时，与该表/分区相关联的元数据、数据，都将被删掉。<code>drop</code>时指定<code>purge</code>选项，数据会被移动到垃圾文件夹，并保留事先定义的一段时间</td> 
   <td>删除该表，仅仅是删除表的元数据。因为外部表的文件（数据）存在外部，它不受影响。<code>/user/hive/warehouse/db_name.db/tbl_name/</code>下也还有数据（不由Hive管理）。</td> 
  </tr> 
  <tr> 
   <td>备注</td> 
   <td>如果所有处理都需要由Hive完成，应该创建托管表。</td> 
   <td>当外部表的结构或分区被改变时，可用<code>msck repair table tbl_name</code>语句刷新元数据信息。外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。</td> 
  </tr> 
 </tbody> 
</table>

实例：创建一个外部表`page_view`

create external table page_view(viewTime int, userid bigint,
      page_url string, referrer_url string,
      ip string comment 'IP Address of user',
      country string comment 'country of origination')
    comment 'This is the staging page view table'
    row format delimited fields terminated by '\054'
    stored as textfile
    location '<hdfs_location>';

外部表可以存储于其指定的任何HDFS位置，无需存储在`hive.metastore.warehouse.dir`属性指定的目录下。但是，必须确保数据按照`create`语句中指定的分隔符（`'\54'`，应该是代表6）进行分割。

特别注意：使用分隔符时，尽量使用它的[ascii码值][ascii]，避免报错。

### 2.0.1 对表的存储管理 ###

Hive 从两个维度对表的存储进行管理：

*  文件格式（file format）
 *  行格式（row format）和SerDe

#### 2.0.1.0 文件格式 ####

Hive支持内置（bulit-in）、自定义的文件格式。更多关于压缩的表存储格式可[参考][Link 4]

内置的存储格式有：详细说明[参考][Link 5]

*  `stored as textfile`，保存为纯文本文件，这是默认的文件格式，也可由`hive.default.fileformat`进行其他配置。
 *  其他有：`sequencefile`、`orc`、`parquet`、`avro`、`rcfile`、`jsonfile`（`Hive-4.0.0`起）、等等。

在`create table`语句中加上`stored as file_format`子句完成表存储格式的声明。实例：

hive (foo1_db)> create table raw_sequence (line string)
                  > stored as sequencefile;

#### 2.0.1.1 行格式和SerDe ####

> SerDe：序列化、反序列化

创建表时，可以使用自定义SerDe 或本地SerDe。如果没有指定`row format` 或指定了`row format delimited`时，那么用的是本地SerDe。

使用`serde`子句创建自定义SerDe的表。关于SerDe的更多资料可参考：

*  [Hive SerDe][]
 *  [SerDe][]
 *  [HCatalog Storage Formats][]

指定了`row format`子句的实例：

row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
    with serdeproperties
    (
    "input.regex" = "<regex>"
    )
    stored as textfile

### 2.0.2 分区表 ###

**使用`partitioned by`子句可创建分区表**。一个表可以有一个、或多个分区列，分区列的每个不同值被创建为一个单独的数据目录。

还可结合如下子句提高某些类型查询的性能：

*  `clustered by`，以列对表或分区表进行存储
 *  `sorted by`，以列对数据进行排序

实例：例中`page_view`表中暂无数据，所以在仓库目录中没有对应的分区表目录。表中具有`viewTime`、`userid`等列，并对表进行分区，并将【表数据】存储在序列文件中

create table page_view(viewTime int, userid bigint,
      page_url string, referrer_url string, 
      ip string comment 'IP Address of the User')
    comment 'This is the page view table'
    partitioned by(dt string, country string)
    row format delimited fields terminated by '\001'
    stored as sequencefile;

### 2.0.3 `create table as select` (CTAS)方式建表 ###

> CTAS，create table as select的简写。

表 也可以通过一个CTAS语句中的查询结果来创建和填充。CTAS创建的表，是原子的。这意味着 在填充结果之前，其他用户不会看到该表。因此，其他用户要么会看到具有完整查询结果的表，要么根本不会看到该表。

CTAS语句有两部分：

*  `create`部分，取得从`select`部分产生的模式（schema），并使用表的其他属性（SerDe、存储格式等）创建目标表
 *  `select`部分，可以是HQL支持的任意`select`语句

`Hive-3.2.0`起，CTAS语句可以为目标表定义一个分区规范。

CTAS有两个限制：

*  目标表不能是一个外部表；
 *  目标表不能是一个桶表。

实例：

create table new_key_value_store
      row format serde "org.apache.hadoop.serde2.columnar.ColumnarSerDe'
      stored as rcfile
    as
    select (key % 1024) new_key, concat(key, value) key_value_pair
    from key_value_store
    sort by new_key, key_value_pair;

上方CTAS语句使用从`select`语句的查询结果派生的schema（new\_key Double，key\_value\_pair String）来创建目标表`new_key_value_store`。

若`select`语句没有指定列别名，列名将自动分配为`_col0、_col1、_col2`等。  
另外，新的目标表是用指定的Serde和存储格式创建的，而独立于`select`语句中的源表。

能够从一个表选择数据到另一个表中是Hive最强大的特性之一。在执行查询时，Hive处理从源格式到目标格式的数据转换。

### 2.0.4 `create table like`方式创建表 ###

`create table like`语句 可以准确地复制一个现有表的表定义（但不会复制它的数据）。  
实例：与CTAS语句创建表不同，下方语句创建了一个名称为`empty_key_value_store`的新表，除表名之外，所有的细节上都与现表`key_value_store`的定义完全相同。新表不包含任何行（数据）。

create table empty_key_value_store
    like key_value_store [tblproperties (property_name=property_value, ...)];

### 2.0.5 排序的分桶表 ###

实例：

create table page_view(viewTime int, userid bigint,
      page_url string, referrer_url string,
      ip string comment 'IP Address of the User.'
    comment 'This is the page view table'
    partitioned by (dt string, country string)
    clustered by (userid) sorted by (viewTime) into 32 buckets
    row format delimited
      fields terminated by '\001'
      collection items terminated by '\002'
      map keys terminated by '\003'
    stored as sequencefile;

*  `page_view`表按`userid`进行分桶（`clustered by`），并且每个桶中，数据按照`viewTime`升序排序。这样组织 方便用户对集群列做有效的抽样（如`userid`）。
 *  排序属性（`viewTime`）允许内部操作符在评估查询时利用已知的数据结构，以提高效率。
 *  `map keys`、`collection by`关键字可以使用任何列的列表或映射。
 *  `clustered by`、`sorted by`不会影响数据插入表的方式。只会影响数据的读取方式。所以，必须小心正确地插入数据，方法是指定reducers的数量、存储桶的数量等等，并在查询中使用`clustered by`、`sorted by`命令，以便在可能的情况下，跳过或包含整个文件。

### 2.0.6 倾斜表 ###

通常用在一个 或多个列具有倾斜值的表中以提高性能。通过指定经常出现严重倾斜的那个值，Hive将自动把这些文件拆分为单独的文件（或桶所在目录下），并在查询时也考虑到倾斜问题。

可以在创建表过程中，在每个表级别上指定这一点。

实例1：一个列有倾斜值

create table list_bucket_single (key string, value string)
    skewed by (key) on (1,5,6)
    [stored as directories];

*  展示了一个有3个倾斜值的列；
 *  `stored as directories`子句指定了以桶方式进行存储。

实例2：两个列有倾斜值

create table list_bucket_multiple (col1 string, col2 int, col3 string)
    skewed by (col1, col2) on (('s1', 1), ('s3', 3), ('s13', 13), ('s78', 78))
    [stored as directories];

### 2.0.7 临时表 ###

临时表只对当前会话可见。数据将被存储在用户的临时目录（`Hive-1.1.0`起，设置`hive.exec.temporary.table.storage`，临时表的存储策略可设置为memory、ssd或default（参考[HDFS Storage Types and Storage Policies][]）），并在会话结束时删除。

假如创建的一个临时表，它的名称 跟一个已经存在的数据库中的永久表的名称相同，那么在当前会话中，这个临时表的任何引用都将解析到临时表，而不是永久表。这样的话，在不删除临时表、或将其重命名为不冲突的名称 的情况下，将无法访问当前会话中的原始表。

临时表有以下限制：

*  不支持分区列
 *  不支持创建索引

实例：

create temporary table list_bucket_multiple (col1 string, col2 int, col3 string);

### 2.0.8 事务性表 ###

> ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）的数据库，必须要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。

`Hive-4.0`才支持。支持与ACID语义操作的表。参考[Hive Transactions][]

实例：

create transactional table transactional_table_test(key string, value string)
    partitioned by (ds string)
    stored as orc;

### 2.0.9 约束 ###

`Hive-2.1.0`起引入。Hive支持对未经验证的主键和外键约束的支持。一些SQL工具 在存在约束时，会生成更高效的查询。由于这些约束没有被验证，上游系统需要在加载到Hive之前确保数据的完整性。

实例：

create table pk(id1 integer, id2 integer,
      primary key (id1, id2) disable novalidate);
     
    create table fk(id1 integer, id2 integer,
      constraint c1 foreign key (id1, id2) reference pk (id1, id2) disable novalidate);

## 2.1 删除表 ##

语法：

drop table [if exists] tbl_name [purge];

*  `drop table`语句会删除【托管表】的元数据、数据。如果配置了回收站（且未指定`purge`），数据实际上会移动到`.Trash/Current`目录。元数据将全丢失了。
 *  删除外部表时，将不会从文件系统中删除表的数据。
 *  删除视图引用的表时，不会发出任何警告信息（视图将无效，必须由用户删除 或重新创建）。
 *  `purge`选项于`Hive-0.14.0`以引入。若指定了它，表中的数据不会转到`.Trash/Current`目录，因此，误操作`drop`下，将无法进行数据恢复。
 *  删除分区可参考[Drop Partitions][]

## 2.2 截断表 ##

语法：

truncate table tbl_name [parition partition_spec];
    
    partition_spec:
      : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

删除表或分区（一个或多个分区）的所有行。当只需删除数据并保留表结构时，可使用`truncate`语句。

若想对某个现有的文件做尾部的截取（比如为了保留头部关键信息），但同时又不想重新写一个新的文件出来，这个时候我们其实可以采用系统提供的`truncate`命令。

如果启用了文件系统的垃圾回收，数据将被回收，否则它们会被直接删除（`Hive2.2.0`起）。目前，目标表应该是本地的或托管表，否则将引发异常。对于分区表，用户可以指定`partition_spec`一次截断多个分区，或者省略`partition_spec`截断表中的所有分区。

`HIVE 2.3.0`起，如果表属性`auto.purge`设置为`true`， 则当对表发出`truncate table`命令时，该表的数据不移动到回收站，并且如果误操作发出的`truncate table`命令也无法恢复数据。这仅适用于托管表（见托管表）。如果托管表的`auto.purge`属性未设置或设置为`false`，则可以关闭此行为模式。

## 2.3 修改：表、分区、列 ##

`alter table`语句，用于修改现有表的结构：添加列 和分区、修改SerDe、添加表和SerDe属性、重命名表本身、修改表中特定分区的属性。

### 2.3.0 `alter table`修改表的名称、属性、注释、SerDe属性、存储属性、倾斜、存储为目录、约束等 ###

#### 2.3.0.0 重命名 ####

语法：

alter table tbl_name rename to new_tbl_name;

可将表的名称修改为不同的名称。

*  `Hive-0.6`以下版本，只是重命名了metastore中的表，而没有移动HDFS位置；
 *  `Hive-0.6`起，对托管表重命名会移动其HDFS位置；
 *  `Hive-2.2.0`起，只有当没有用`location`子句创建的托管表、并且在它的数据库目录下时，重命名表才会移动其HDFS位置。

#### 2.3.0.1 修改表的属性 ####

语法：

alter table tbl_name set tblproperties tbl_properties;
    
    tbl_properties:
      : (property_name=property_value, property_name=property_value, ...)

*  可以用这个语句向表中添加自己的元数据；
 *  现在，`last_modified_user`、`last_modified_time`属性是由Hive自动添加和管理的；
 *  使用`describe extended table`可查看这些信息。

#### 2.3.0.2 修改表的注释 ####

语法：

alter table tbl_name set tblproperties ('comment'=new_comment);

#### 2.3.0.4 添加SerDe属性 ####

语法：

alter table tbl_name
    [partition partition_spec]
    set serde serde_class_name
    [with serdeproperties serde_properties];
    
    serde_properties:
      : (property_name = property_value, property_name = property_value, ...)

*  可改变一个表的SerDe，或添加用户定义的元数据到表的SerDe对象；
 *  当Hive初始化该表 以序列化和反序列化数据时，SerDe属性将传递给该表的SerDe。因此，用户可以在此存储自定义SerDe所需的任何信息；
 *  `properties_name`和`properties_value`都必要加引号。

实例：

alter table tbl_name set serdeproperties ('field.delim' = ',');

#### 2.3.0.5 修改表的存储属性 ####

语法：

alter table tbl_name clustered by (col_name, col_name, ...)
    [serde by (col_name, ...)]
    into num_buckets buckets;

*  更改表的物理存储性能；
 *  只会修改Hive的元数据，不会重新组织或格式化现有的数据；
 *  用户应该确保实际数据布局符合元数据定义。

#### 2.3.0.6 修改表的倾斜或存储为目录 ####

**倾斜**

alter talbe tbl_name skewed by (col_name1, col_name2, ...)
    on  ([(col_name1_value, col_name2_value, ...) [, (col_name1_value, col_name2_value), ...])
    [stored as directories];

`stored as directories`选项用于确定是否倾斜表使用列表桶功能，该功能为倾斜值创建子目录。

**不倾斜**

alter table tbl_name not skewed;

`not skewed`选项使表不倾斜，并关闭列表桶存储功能(因为列表桶存储表总是倾斜的)。这会影响`alter`语句之后创建的分区，但对`alter`语句之前创建的分区没有影响。

**不存储为目录**

alter table tbl_name not stored as directories;

这将关闭列表桶存储功能，尽管表仍然是倾斜的。

**设置倾斜表存储位置**

alter table tbl_name set skewed location (col_name1="location1" [, col_name2="location2", ...] );

这将更改列表桶存储位置的映射。

#### 2.3.0.7 修改表的约束 ####

`Hive-2.1.0`起引入。表约束可通过`alter table`语句进行添加、删除。

alter table tbl_name add constraint constraint_name primary key (column, ...) disable novalidate;
    
    alter table tbl_name add constraint constraint_name foreign key (column, ...) references tbl_name ((column, ...)) disable novalidate rely;
    
    alter table tbl_name drop constraint constraint_name;

### 2.3.1 修改分区 ###

分区可以通过在`alter table`语句中使用`partition`子句来进行修改：  
**添加、重命名、交换（移动）、删除、存档（或调档）**

`Hive1.2`起，如果属性`hive.typecheck.on.insert`设置为`true`(默认)，分区规范中指定的分区值将被检查、转换和标准化，以符合其列类型。这些值可以是数字字符串。

#### 2.3.1.0 添加分区 ####

alter table tbl_name add [if not exists]
    partion partition_spec
    [location 'location'] [,partition partition_spec [location 'locaiton'], ...];
     
    partition_spec:
      : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

*  只有分区值（`location`）是字符串时，才需要其被引号包围；
 *  `location`必须是一个存放内部数据的目录；
 *  `add partition`更改表的元数据，但不会加载数据；
 *  如果分区的位置不存在数据，查询将不会返回任何结果；
 *  如果表中的已经存在`partition_spec`的分区，则会引发错误。可以使用`if not exists`跳过错误。

#### 2.3.1.1 动态分区 ####

使用Hive `insert`语句（或一个Pig `store`语句）可以动态地向一个表添加分区。  
更多[参考][Link 6]。

#### 2.3.1.2 重命名分区 ####

语法：

alter table tbl_name partition partition_spec
    rename to partition partition partition_spec;

#### 2.3.1.3 表之间移动分区 ####

`Hive-0.12`引入。在`Hive-1.2.2、1.3.0`和`2.0.0`中支持多个分区。将分区中的数据从一个表移动到另一个具有相同架构且还没有该分区的表。  
语法：

-- 从tbl_name1移动1个分区到tbl_name2
    alter table tbl_name2 exchange partition (partition_spec) with table tbl_name1;
    
    -- 移动多个分区
    alter table tbl_name2 exchange partition (partition_spec1, partition_spec2, ...) with table tbl_name1;

#### 2.3.1.4 恢复分区（msck repair table） ####

> msck，metastore check简写

Hive在它的metastore存储了每个表的分区列表。然而，如果新的分区被直接加入到HDFS（比如：通过使用`hadoop fs -put`命令），或从HDFS移除，metastore（和Hive）将不知道这些变化，除非用户在分区表上每次新添或删除分区时分别运行`alter table tbl_name add/drop partition`语句。

不过，用户可以运行一个有`repair`选项的检查metastore的命令：

msck [repair] tbl_name [add/drop/sync partitions];

*  它将更新Hive Metastore中关于分区的元数据，用于那些尚未存在此类元数据的分区；
 *  `msck`命令的默认选项是`add partitions`。使用此选项，它将向Metastore添加任何存在于HDFS上但不在Metastore中的分区；
 *  `drop partitions`选项将从Metastore中删除分区信息，对应那些已经从HDFS中删除的分区；
 *  `sync partitions` 等价于调用`add partitions`和`drop partitions`；
 *  当有大量未跟踪分区时，有一项规定可以按批处理方式运行`msck repair table`，以避免`OOME`(内存不足错误)。通过为属性`hive.msck.repair.batch.size`提供配置的批处理大小，它可以在内部批处理中运行。该属性的默认值为`0`，这意味着它将一次执行所有分区；
 *  不带`repair`选项的`msck`命令可用于查找metadata错配的详细信息；
 *  `Hive-1.3`起，如果分区值中有HDFS目录不允许的字符，`msck`将抛出异常。在客户端上使用设置`hive.msck.path.validation`来改变此行为；`skip`将简单地跳过目录。`ignore`将尝试创建分区(旧的行为)。这可能成功，也可能不起作用。

#### 2.3.1.5 发现和同步分区 ####

将在`Hive-4.0.0`引入。自动地发现、同步在Hive Metastore中分区的metadata。

#### 2.3.1.6 分区保留 ####

将在`Hive-4.0.0`引入。通过表属性`partition.retention.period`为分区表指定一个保留时间。

#### 2.3.1.7 删除分区 ####

语法：

alter table tbl_name drop [if exists] partition partition_spec [,partition partition_spec, ...]
    [ignore protection] -- 不适合Hive-2.0.0以上版本
    [purge];--Hive-1.2.0起

此语句将删除表的分区。删除用于该分区中的数据和元数据。如果设置了回收策略，数据实际移动到`.Trash /Current`目录，除非另外指定`purge`。但元数据完全丢失。

#### 2.3.1.8 分区存档（解档）：（Un）archive ####

alter table tbl_name archive partition partition_spec;
    alter table tbl_name unarchive partition partition_spec;

归档是一个移动一个分区的文件到一个Hadoop Archive（HAR）的功能。请注意，只是文件的数量减少；HAR不提供任何压缩。

### 2.3.2 修改表或分区 ###

#### 2.3.2.0 修改表或分区的文件格式 ####

语法：

alter table tbl_name [partition partition_spec] set fileformat file_format;

此语句只更改表的元数据。任何现有数据的转换必须在Hive外完成。

#### 2.3.2.1 修改表或分区的位置 ####

语法：

alter table tbl_name [partition partition_spec] set location "new location";

#### 2.3.2.2 修改表或分区的touch操作 ####

语法：

alter table tbl_name touch [partition partition_spec];

`touch`关键字可以读取表或分区的元数据，并将其返回。

这个操作具有使前后执行挂钩触发的效果。比如：有一个钩子（hook）记录了所有已修改的表或分区，以及一个直接更改HDFS上文件的外部脚本。因为脚本修改了Hive之外的文件，所以这个修改并不会被钩子（hook）记录下来。外部脚本可以调用`touch`来触发钩子（hook），并将所述表或分区标记为修改后的表或分区。

另外，可以将可靠的最后修改时间合并起来，以后可能会很有用。`touch`也将更新那个时间。

注意：如果`touch`的表或分区不存在，它也不会去创建表或分区。

#### 2.3.2.3 修改表或分区的保护（被删除、访问） ####

`Hive-0.8.0`起，在`cascade`子句加入了`no_drop`。但这个功能在`Hive-2.0.0`被丢弃，并有Hive几个可用的安全选项之一替代。

alter table tbl_name [partition partition_spec] enable|disable no drop [cascade];
    
    alter table tbl_name [partition parttion_spec] enable|disable offline;

*  数据保护可用在任一表或分区级别设置；
 *  启用`no drop`防止表被删除；
 *  启用`offline`防止查询表或分区中的数据，但元数据仍然可被访问；
 *  如果在一个表中的任何分区都启用`no drop`，该表将不能被删除；当然，若是一个表启用了`no drop`，表中分区是可被删除的，但加了`no drop cascade`分区是不能被删除的，除非删除分区的命令指定了`ignore protection`。

#### 2.3.2.4 修改表或分区的compact操作 ####

*  `Hive-0.13.0`起，当事务正在被使用，`alter table`语句可以compact表或分区；
 *  `Hive-1.3.0`和`2.1.0`版本，当事务正在被使用，`alter table ...compact`语句可以加上`tblproperties`子句，或改变compaction MapReduce job属性，或重写任何其他Hive表的属性

语法：

alter table tbl_name [partition (partition_key = 'partition_value' [, ...])]
    compact 'compaction_type'[and wait]
    [with overwrite tblproperties ("property"="value" [, ...])];

*  一般情况下，在使用Hive事务时，不需要请求compact操作，因为系统将检测到它们的需要并自动启动compact操作。
 *  如果关闭了表的compact功能、或在系统不选择的情况下compact表，那么可以使用`alter table`启动compact操作。  
    默认情况下，上述语句将compact请求排队，然后返回。
 *  若要查看compact进度，可使用`show compactions`。
 *  `Hive-2.2.0`起，`and wait`可让操作等待compact完成。

#### 2.3.2.4 修改表或分区的的串联（Concatenate） ####

*  `Hive-0.8.0`中，增加了使用串联`concatenate`命令块级快速合并小型RCFile的支持；
 *  `Hive-0.14.0`中，添加了使用串联命令条带（stripe）级别合并ORC小文件的支持。

语法：

alter table tbl_name [partition (partition_key = 'partition_value' [, ...])] concatenate;

如果表或分区包含许多小的RCFile或ORC文件，那么上述语句可将它们合并成更大的文件。  
对于RCFile，合并发生在块级；对于ORC文件，合并发生在条带（stripe）级，从而避免了数据解压缩、解码的开销。

#### 2.3.2.5 更新表或分区的列 ####

在`Hive-3.0.0`中引入，让用户可以同步SerDe存储模式信息到metastore。  
语法：

alter table tbl_name [partition (partition_key = 'partition_value' [, ...])] update columns;

具有自描述表模式的有SerDes的表在现实中可能有不同的模式，而存储在Hive Metastore中的表也可能有不同的模式。例如，当用户使用模式url或模式文字创建Avro存储表时，模式将被插入到HMS中，然后无论服务器中的url或文字如何更改，模式都不会在HMS中被更改。这可能导致问题，特别是在与其他Apache组件集成时。

更新列功能为用户提供了让在SerDe所做的任何模式更改能同步到HMS的方式。它适用于表和分区一级，而且显然只适用于其模式未被HMS跟踪的表（`metastore.serdes.using.metastore.for.schema`）。在这些后来SerDe类型使用命令将导致错误。

### 2.3.3 修改列 ###

#### 2.3.3.0 列名的规则 ####

*  列名不区分大小写；
 *  `Hive-0.12.0`及之前的版本中，列名只能包含字母、数字、下划线字符。通过设置`hive.support.quoted.identifiers`为`none`，此时，反引号列名将被解释为正则表达式；
 *  `Hive-0.13.0`起，默认情况下，列名可以在反引号里指定，也可以含有任何Unicode字符，但是，点符号（`.`）、冒号（`:`）在查询时会报错。在反引号内指定的任何列名都按字面处理。在反引号字符串中，用双反引号（\`\`）来表示一个反引号字符；
 *  反引号包围符允许使用保留关键字用于列名、表名。

#### 2.3.3.1 修改列名称、类型、位置、注释 ####

语法：

alter table tbl_name [partition partition_spec] change [column]
    col_old_name col_new_name column_type
    [comment col_comment]
    [first|after column_name]
    [cascade|restrict];

*  这个语句允许更改列的名称、数据类型、注释、位置、或它们的组合；
 *  `Hive-0.14.0`起，引入`partition`子句；
 *  `Hive-1.1.0`引入`cascade|restrict`子句。`alter table tbl_name change column`结合`cascade`子句更改表元数据的列、并对所有分区元数据进行相同的更改，这个组合需慎用，因为它将覆盖表或分区相关列的元数据，而不管表或分区的保护模式如何。其中，`restrict`是默认的，限制仅表中列的元数据发生变化；
 *  更改列的语句只修改配置单元的元数据，并不会修改数据。用户应确保表或分区的实际数据与布局的元数据定义一致。

实例：

create table test_change (a int, b int, c int);
    
    第一次更改：将列名a 改为a1
    alter table test_change change a a1 int;
    
    第二次更改：将列名a1改为a2，其数据类型改为string，并把a2列放在b列后面
    alter table test_change change a1 a2 string after b;
    这样新表的结构是：b int, a2 string, c int
    
    第三次更改：将列名c改为c1，并将其放在第一列
    alter table test_change change c c1 int first;
    此时新表的结构是：c1 int, b int a2 string
    
    第四次更改：给列a1添加注释
    alter table test_change change a1 a1 int comment 'this is column a1'

#### 2.3.3.2 添加和替换列 ####

语法：

alter table tbl_name
    [partition partition_spec] --Hive 0.14.0起才支持
    add|replace columns  (col_name data_type [comment col_comment], ...)
    [cascade|retrict] --Hive 1.1.0起才支持

*  `add columns`可将新列添加到现有列之后、分区列之前。这个特性支持Avro的表（Hive 0.14.0起）；
 *  `replace columns`删除所有现有的列，并增加新的一组列。这只能在表是本地SERDE（`DynamicSerDe，MetadataTypedColumnsetSerDe，LazySimpleSerDe和ColumnarSerDe`）的情况下完成。`replace columns`还可以用来删除列。例：`alter table test_change replace columns (a int, b int);`，将会在`test_change`的模式中删除列`c`；
 *  `cascade|restrict`子句在`Hive1.1.0`可用。`alter table change columns`与`cascade`结合更改表元数据的列，并对所有分区元数据进行相同的更改。`restrict`是默认的，限制仅表中列的元数据发生变化；
 *  `alter table add`或`replace columns cascade`将覆盖表分区的列元数据，无视表或分区的的保护模式。得谨慎使用；
 *  列更改语句只修改配置单元的元数据，并且不会修改数据。用户应确保表或分区的实际数据与布局的元数据定义一致。

#### 2.3.3.3 部分分区规范 ####

`Hive 0.14`起，用户可以为上述`alter`列语句提供部分分区规范，类似于动态分区。因此，不必为需要更改的每个分区发出`alter`列语句：比如下面这样

alter table foo partition (ds='2008-04-08', hr=11)
    change columns dec_column_name dec_column_name decimal(38,18);
    
    alter table foo partition (ds='2008-04-08', hr=12)
    change columns dec_column_name dec_column_name decimal(38,18);
    ...

可以使用带有部分分区规范的单个`alter`语句一次更改许多现有分区：

--在使用alter partition语句时设置hive.exec.dynamic.partition=true以支持动态分区。
    set hive.exec.dynamic.partition=true;
    
    -- 将更改表中所有现有 ds='2008-04-08'的分区。要确保清楚正在做什么
    alter table foo partition ('2008-04-08', hr)
    change column dec_column_name dec_column_name decimal(38,18);
    
    alter table foo partition (ds, hr)
    change column dec_column_name dec_column_name decimal(38,18);

类似动态分区，`hive.exec.dynamic.partition`必须设置为true，以便在`alter partition`期间启用部分分区规范。也支持这些操作：更改列、添加列、替换列、文件格式、SerDe属性。

[\[Hive\] 05 - HQL：数据定义（DDL）下-【视图、索引、宏、函数等】][Hive_ 05 - HQL_DDL_-]

参考：  
[Hive官方手册][Hive]

[20190613204550639.png]: /images/20220117/6bdb9e1a2fa143e8becf6ba11cbbcd94.png
[2019061312170474.png]: /images/20220117/f6c698b7fa554f949794f54f27796643.png
[Link 1]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Keywords,Non-reservedKeywordsandReservedKeywords
[Link 2]: https://blog.csdn.net/weixin_38256474/article/details/91565083#2
[Link 3]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable
[ascii]: https://blog.csdn.net/lxpbs8851/article/details/11525501
[Link 4]: https://cwiki.apache.org/confluence/display/Hive/CompressedStorage
[Link 5]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-StorageFormatsStorageFormatsRowFormat,StorageFormat,andSerDe
[Hive SerDe]: https://cwiki.apache.org/confluence/display/Hive/DeveloperGuide#DeveloperGuide-HiveSerDe
[SerDe]: https://cwiki.apache.org/confluence/display/Hive/SerDe
[HCatalog Storage Formats]: https://cwiki.apache.org/confluence/display/Hive/HCatalog+StorageFormats
[HDFS Storage Types and Storage Policies]: http://hadoop.apache.org/docs/r2.7.5/hadoop-project-dist/hadoop-hdfs/ArchivalStorage.html
[Hive Transactions]: https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions
[Drop Partitions]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-DropPartitions
[Link 6]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-DynamicPartitions
[Hive_ 05 - HQL_DDL_-]: https://blog.csdn.net/weixin_38256474/article/details/92017795
[Hive]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual