第 4 章 Hive DDL

我们都知道 Hive 数据仓库底层是需要依赖于 Hadoop HDFS 进行数据存储的，但 Hive 中所有真实数据都存储在 HDFS 中，元数据（metastore）存储在关系型数据库中，如 Hive 自带的 Derby，MySQL。这样更有利于对数据做分布式计算。

4.1 Hive 数据单元

为了有效地对真实数据进行管理，根据粒度大小，Hive 将真实数据划分为如下数据单元。

4.1.1 数据库（Databases）

Hive 中的数据库类似于 RDBMS（关系数据库管理系统） 中的数据库，在 HDFS 中表现为 hive.metastore.warehouse.dir 目录下的一个文件夹。本质作用就是避免表、视图、分区、列 等命名冲突的命名空间。

4.1.2 表（Tables）

表（Table）由列组成，在表上可以进行过滤、映射、连接和联合 操作。表中的所有数据被存储在一个目录中。表在 HDFS 中表现为所属数据库目录下的子目录，具体又可以分为内部表 和 外部表。

• 内部表：类似于 RDBMS 中的表，由 Hive 统一管理；
• 外部表：指向已经存在 HDFS 中的数据，与内部表数据组织是相同的，但其数据存放位置任意的。外部表的真实数据不会被 Hive 统一管理，也就是说当删除一张内部表时，元数据以及 HDFS 上的真实数据均被删除，而删除外部表则只会删除元数据而不会删除真实数据。

4.1.3 分区（Partitions）

每一个表可以有一个或几个分区键，键值决定了数据是如何存储的。每个表都可以按指定的键分为多个分区。分区的作用是提高查询的效率，其在 HDFS 中表现为表目录下的子目录。

4.1.4 分桶（Buckets）

根据表中某一列的哈希值可以将数据划分为多个分桶（Buckets），在 HDFS 在分桶表现为同一个目录下根据哈希散列之后的多个文件。每一个桶以文件的形式存储在目录中。

数据存储到桶中使系统能有效地评估依据一个数据样本的查询。

我们可以看到，Hive 中的数据单元划分与 RDBMS 的物理模型非常类似，但在 Hive 数据单元划分过程中还必须注意几点：

• Hive 中的表没有主键的概念；
• Hive 表行级操作效率比较低；
• Hive 表虽然不支持批量更新操作，但可以先删除、再添加，达到更新的目的；
• Hive 分区和分桶可以极大提升数据查询效率。

Hive 数据存储单元如下图所示：

按照数据单元的划分结果，Hive 数据在 HDFS 的典型存储结构中表现为以下形式：

• /数据仓库地址/数据库名称/表名称/数据文件（或者分桶数据文件）。
• /数据仓库地址/数据库名称/表名称/分区键/数据文件（或者分桶数据文件）。

如果我们假设数据仓库的地址为：hive.metastore.warehouse.dir=/hive/warehouse ，则可以推导出如下内容：

（1）“/hive/warehouse” 表示 Hive 自带的“default”数据库位置。

（2）“/hive/warehouse/test.db” 表示数据仓库在存在名为test的数据库。

（3）“/hive/warehouse/test.db/users” 表示 test数据库中存在一张表名为users的表。

（4）“/hive/warehouse/test.db/users/00000_······” 表示test数据库下users表中的数据文件。

（5）“/hive/warehouse/test.db/orders/year=2022/part-0000······” 表示test数据库中orders表里的数据按照年份进行分区，这里显示的是分区：year=2022 下的数据文件。

4.2 数据库操作

Hive 中的数据库和常用的关系型数据库中的数据库作用几乎一样，在实际生产环境中，如果表非常多的话，一般会使用数据库把表组织起来，形成逻辑组，这样可以有效防止大规模集群中表命名冲突的问题。

Hive 数据库本质就是数据仓库下的一个目录。Hive 中的数据库相关操作有哪些呢？

4.2.1 创建数据库

由于不可能把所有的数据表都放在default 数据库中，所以需要创建新的数据库。创建数据库的语法如下：

CREATE DATABASE [IF NOT EXISTS] DATABASE_NAME;

[c-alert type="warning"][IF NOT EXISTS] 是一个可选项，它的作用是：当没有同名数据库存在时才创建后面的数据库，如果有同名数据库存在，则不会执行创建数据库的命令，也不会报数据库已存在的错误。换句话说，该命令并没有对已有的数据库产生任何影响。[/c-alert]

我们来创建一个名为 qxbmi 的数据库：

hive (default)> create database qxbmi;
OK
Time taken: 1.205 seconds

创建完该数据库后，再使用 show databases; 命令查看一下，当出现对应名称的数据库就表示创建成功，具体代码如下所示：

hive (default)> show databases;
OK
database_name
default
qxbmi
Time taken: 0.308 seconds, Fetched: 2 row(s)

如果已经有同名数据库存在，同时又没有加 [IF NOT EXISTS] 关键字，那么系统就会报数据库已经存在的错误提示，如下代码所示：

hive (default)> create database qxbmi;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database qxbmi already exists

如果加上[IF NOT EXISTS] 关键字就没有上面的报错，具体操作如下代码：

hive (default)> create database if not exists qxbmi;
OK
Time taken: 0.015 seconds

我们还可以使用 comment 关键字为数据库做注释。

在创建数据库时为这个数据库做一些注释，也就是对创建的这个数据库做一些描述，只需要加上 comment 关键字就可以了。看下面的代码示例：

hive (default)> create database qxbmi_1
              > comment 'my first database.';
OK
Time taken: 0.113 seconds

数据库创建成功后，我们可以通过命令 desc database qxbmi_1; 查看到注释信息，具体代码如下所示：

hive (default)> desc database qxbmi_1;
OK
db_name comment location        owner_name      owner_type      parameters
qxbmi_1 my first database.      hdfs://master:9000/hive/warehouse/qxbmi_1.db    root    USER
Time taken: 0.044 seconds, Fetched: 1 row(s)

4.2.2 查询数据库

当数据库创建完成后，我们可以通过命令 show databases; 查看数据库中的所有数据库。具体代码如下所示：

hive (default)> show databases;
OK
database_name
default
qxbmi
qxbmi_1
Time taken: 0.021 seconds, Fetched: 3 row(s)

从上面结果可以看出，当前数据库中有 3 个数据库。

如果我们没有明确指定使用哪个数据库的时候，Hive 使用的就是默认的数据库 default，比如创建的表都会放在这个默认的数据库里面。

4.2.3 切换数据库

当前我们所使用的数据库默认是 default 数据库，当我们需要切换到其他数据库时，可以通过命令 use database; 进行切换。具体代码如下所示：

hive (default)> use qxbmi;
OK
Time taken: 0.028 seconds
hive (qxbmi)> 

从上面的结果可以看到，数据库已从 default 切换到 qxbmi 数据库了。

<property>
    <name>hive.cli.print.current.db</name>
    <value>true</value>
    <description>Whether to include the current database in the Hive prompt.</description>
</property>

4.2.4 查看数据库

如果我们想要查看某个数据库的相关信息，可以通过命令 describe database database_name; 进行查看。比如我们现在查看 qxbmi 数据库，具体代码如下所示：

hive (qxbmi)> describe database qxbmi;
OK
db_name comment location        owner_name      owner_type      parameters
qxbmi           hdfs://master:9000/hive/warehouse/qxbmi.db      root    USER
Time taken: 0.023 seconds, Fetched: 1 row(s)

从上面输出的信息中可以看到 location 关键字，它表示的是：当前数据库在 HDFS 上的存储位置。这个默认存储位置在 /user/hive/warehouse 目录下。这个属性项是在 Hive 配置文件 hive-site.xml 中通过属性 hive.metastore.warehouse.dir 进行配置的。如下所示：

<property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/hive/warehouse</value>
    <description>location of default database for the warehouse</description>
</property>

当前我这里配置的是：/hive/warehouse 。

4.2.5 修改数据库

当我们想对数据库进行修改的时候，可以通过命令 alter database database_name; 修改数据库。

【语法】如下：

ALTER (DATABASE|SCHEMA) DATABASE_NAME
SET DBPROPERTIES(PROPERTY_NAME=PROPERTY_VALUE,......)

比如，我们现在将数据库 qxbmi 的创建时间修改为：20220526，具体代码如下：

hive (qxbmi)> alter database qxbmi set dbproperties('createtime'='20220526');
OK
Time taken: 0.241 seconds

修改完后，可以通过 desc database extended qxbmi; 进行查看，输出信息如下：

hive (qxbmi)> desc database extended qxbmi;
OK
db_name comment location        owner_name      owner_type      parameters
qxbmi           hdfs://master:9000/hive/warehouse/qxbmi.db      root    USER    {createtime=20220526}
Time taken: 0.026 seconds, Fetched: 1 row(s)

4.2.6 删除数据库

如果我们想删除不需要的数据库，可以通过命令drop database database_name; 进行删除。但在删除过程中可能会遇到如下几种情况。

1）删除空数据库：

hive (qxbmi)> drop database qxbmi_1;
OK
Time taken: 0.519 seconds

2）如果删除不存在的数据库则会报错，避免报错则需要使用 if exists 关键字判断数据库是否存在：

hive (qxbmi)> drop database qxbmi_1;
FAILED: SemanticException [Error 10072]: Database does not exist: qxbmi_1
hive (qxbmi)> drop database if exists qxbmi_1;
OK
Time taken: 0.017 seconds

3）如果数据库不为空，可以使用 cascade 关键字（表示级联的意思，先自动删除数据库中的表，然后再删除数据库）强制删除。比如我们在数据库 qxbmi 中创建一张表后再删除该数据库操作。

hive (qxbmi)> create table test(id int, name string);
OK
Time taken: 0.43 seconds
hive (qxbmi)> drop database qxbmi;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database qxbmi is not empty. One or more tables exist.)

从上面的报错信息中可以看到：报错信息中提示我们当前数据库中存在表数据，不能删除。如果要强制删除，则使用关键字 cascade 强制删除。

hive (qxbmi)> drop database qxbmi cascade;
OK
Time taken: 1.715 seconds

查看数据库qxbmi是否被删除：

hive (qxbmi)> show databases;
OK
database_name
default
Time taken: 0.01 seconds, Fetched: 1 row(s)

可以看到 qxbmi 数据库已被强制删除。

4.3 表相关操作

我们再对数据表操作之前先要明确对哪种类型的表进行操作。

4.3.1 表分类

Hive 中常见的数据表的类型有：内部表、外部表、分区表、桶表。我们需要对每一种类型的表以及操作进行了解。

1.内部表

内部表又叫管理表（Manager Table）或者托管表，意味着由 Hive 来管理表的数据，Hive 会默认将数据保存到数据仓库目录下。

当删除管理表时，将删除管理表中的真实数据和元数据。

案例实操

1）创建普通表：

hive (qxbmi)> create table if not exists student( id int, name string)row format delimited fields terminated by '\t' stored as textfile location '/hive/warehouse/student';
OK
Time taken: 0.306 seconds

2）根据查询结果创建表（查询的结果会添加到新创建的表中）

hive (qxbmi)> create table if not exists student2 as select id, name from student;

3）根据已经存在的表结构创建表：

hive (qxbmi)> create table if not exists student3 like student;
OK
Time taken: 0.232 seconds

4）查询表的类型：

hive (qxbmi)> desc formatted student2;
OK
Table Type:             MANAGED_TABLE

2.外部表

内部表好理解，但为什么 Hive 中需要外部表呢？如果一份数据需要被多种工具分析时，比如 Pig、Hive等，这就意味着这份数据的所有权并不由 Hive 拥有，这种情况下就可以创建一个外部表（External Table）来指向这份数据。

当我们要删除外部表的时候，由于这份数据并不完全由 Hive 拥有，所以 Hive 只会删除该外部表的元数据信息而不会删除真实数据。

与内部表的区别：

• 实际上内部表和外部表的区别并不单单在于表的数据是否保存在 Hive 默认的数据仓库中；
• 内部表在建表的时候也可以通过指定 location 子句来指定数据的存储路径。
• 一般情况下，当数据需要被多个工具共享的时候，最好通过创建一个外部表来明确数据的所有权。
• 从数据安全性角度考虑，为了不出现由于误删数据表而导致数据丢失，也建议使用外部表。

3.分区表

分区表（Partitions）就是可以将表进行水平切分，将表数据按照某种规则进行存储，然后提高数据查询的效率。

Hive 也是支持对表进行分区的，而且分区表也分为内部分区表和外部分区表。

由于分区表相对于没有分区的表有明显的性能优势，所以在实际生产环境中，分区表的使用是非常普遍的。

1）内部表分区

我们首先对内部表进行分区（也叫分区管理表），如现在我们创建一个表名为 person_partition_manager 的内部分区表，指定 province 和 city 为分区字段。具体操作命令及结果如下所示：

hive (qxbmi)> create table person_partition_manager(
    id int comment 'person_id',
    name string comment 'person_name',
    age int comment 'person_age') 
	partitioned by (province string,city string) 
	row format delimited fields terminated by '\t';		# 创建表
OK
Time taken: 0.517 seconds
hive (qxbmi)> show tables;		# 查询表
OK
tab_name
person_partition_manager
student
student2
student3
Time taken: 0.055 seconds, Fetched: 4 row(s)

hive (qxbmi)> desc extended person_partition_manager;		# 查看表详情
OK
col_name        data_type       comment
id                      int                     person_id
name                    string                  person_name
age                     int                     person_age
province                string
city                    string
# Partition Information
# col_name              data_type               comment
province                string
city                    string
Time taken: 0.303 seconds, Fetched: 13 row(s)

从创建分区表的语句上来看，除了多了 partitioned by 子句外，其他和建立普通表并没有什么区别。

由于分区表会将所有分区字段的（如 province='guizhou' 和 city='guiyang'）数据都存放在对应目录下，所有在查询数据（如查询 province='guizhou' 和 city='guiyang'）的时候，Hive 只会扫描对应分区目录下的数据，减少了扫描的数据量，从而提高查询效率。尤其是对于大规模数据集时，分区表可以显著提高查询效率。

如果没有分区表的话，Hive 就需要全表扫描，所以我们可以把它理解为一种简单的索引。

有一种特殊情况就是，一条查询包含所有的分区的语句，这条查询将会消耗集群巨大的资源和时间。

对于这种特殊情况，我们可以将 Hive 的安全措施设置为“strict”模式，该设置在 Hive 配置文件 hive-site.xml 中进行修改，也可以通过 Hive 命令行方式输入 set hive,mapred.mode=strict; 进行设置。这两种设置的区别在于：属性的生效范围不同。修改配置文件可以对所有 Hive 会话生效，而通过命令行的方式只能对本会话有效。

将 Hive 的安全模式设置为 strict 模式之后，如果对一个分区表的查询没有对分区进行限制的话（即没有指定哪个分区），则该作业将会被禁止提交。

2）外部表分区

下面我们来创建一个外部分区表：person_partition_external，指定 province 和 city 两个字段为分区字段，具体操作命令及结果如下所示：

hive (qxbmi)> create external table person_partition_external(id int comment 'person_id',name string comment 'person_name',age int comment 'person_age' )partitioned by(province string,city string) row format delimited fields terminated by '\t';
OK
Time taken: 0.64 seconds

hive (qxbmi)> show tables;
OK
tab_name
person_partition_external
person_partition_manager
student
student2
student3
Time taken: 0.039 seconds, Fetched: 5 row(s)

和创建普通外部表不同的是，在创建外部分区表时并没有指定表的存储路径，所以在创建完外部分区表之后，通过执行查询语句是查询不到任何数据的。

这个时候就需要用 location 命令单独为外部表指定具体的存储位置，具体操作命令及结果如下所示：

hive (qxbmi)> alter table person_partition_external add partition (province='guizhou',city='guiyang')
            > location '/hive/warehouse/external/partition/person/guizhou/guiyang';
OK
Time taken: 0.336 seconds
hive (qxbmi)> show partitions person_partition_external;
OK
partition
province=guizhou/city=guiyang
Time taken: 0.103 seconds, Fetched: 1 row(s)

外部分区表的目录结构可以完全由自己通过指定 location 建立，和其他外部表一样，即使外部分区表被删除，数据也不会被删除。

不管是内部表还是外部表，一旦表存在分区，那么数据在加载时必须加载到指定分区中。

由于前面我们已经为分区表指定分区的时候指定了 location ，所以加载数据的时候直接就加载到对应的 location 目录下，具体操作命令及执行结果如下所示：

20220501        lihong  	     25      guizhou 		 guiyang
20220502        wangjia 	     26      beijing 	         beijing
20220503        lixiang 	     24      guangzhou        shenzhen
20220504        huanglei       23      chongqin           chongqin
20220505        wanggo  	     22      chognqin           chongqin

hive (qxbmi)> load data local inpath '/opt/apps/hive-1.2.1/data/person.txt' into table person_partition_external
            > partition (province='guizhou',city='guiyang');
Loading data to table qxbmi.person_partition_external partition (province=guizhou, city=guiyang)
Partition qxbmi.person_partition_external{province=guizhou, city=guiyang} stats: [numFiles=1, totalSize=186]
OK
Time taken: 1.793 seconds

我们可以输入命令查看一下数据所在位置，具体操作命令和执行结果如下所示：

hive (qxbmi)> dfs -ls -R /hive/warehouse/external;
drwxr-xr-x   - root supergroup          0 2022-05-28 10:32 /hive/warehouse/external/partition
drwxr-xr-x   - root supergroup          0 2022-05-28 10:32 /hive/warehouse/external/partition/person
drwxr-xr-x   - root supergroup          0 2022-05-28 10:32 /hive/warehouse/external/partition/person/guizhou
drwxr-xr-x   - root supergroup          0 2022-05-28 10:58 /hive/warehouse/external/partition/person/guizhou/guiyang
-rwxr-xr-x   3 root supergroup        186 2022-05-28 10:58 /hive/warehouse/external/partition/person/guizhou/guiyang/person.txt

3）分区表操作

（1）增加分区

我们在 person_partition_external 表上增加分区，具体操作命令及结果如下所示：

hive (qxbmi)> alter table person_partition_external add partition(province='sichuan',city='chengdu')
            > location '/hive/warehouse/external/partition/person/sichuan/chengdu';
OK
Time taken: 0.139 seconds

hive (qxbmi)> show partitions person_partition_external;
OK
partition
province=guizhou/city=guiyang
province=sichuan/city=chengdu
Time taken: 0.096 seconds, Fetched: 2 row(s)

（2）修改分区

我们对 person_partition_external 表的分区进行修改，具体操作命令及结果如下所示：

hive (qxbmi)> alter table person_partition_external partition(province='sichuan',city='chengdu') set location 'hdfs:/hive/warehouse/external/partition/person/guizhou/guiyang';
OK
Time taken: 0.249 seconds

（3）删除分区

删除分区，具体操作命令及结果如下所示：

hive (qxbmi)> show partitions person_partition_external;
OK
partition
province=guizhou/city=guiyang
province=sichuan/city=chengdu
Time taken: 0.095 seconds, Fetched: 2 row(s)
hive (qxbmi)> alter table person_partition_external drop partition(province='sichuan',city='chengdu');
Dropped the partition province=sichuan/city=chengdu
OK
Time taken: 0.795 seconds

hive (qxbmi)> show partitions person_partition_external;
OK
partition
province=guizhou/city=guiyang
Time taken: 0.07 seconds, Fetched: 1 row(s)

4.桶表

（1）开启分桶的功能

在开始创建桶表之前，要先通过 set hive.enforce.bucketing=true; 命令开启分桶的功能，具体操作命令及执行结果如下所示：

hive (qxbmi)> set hive.enforce.bucketing;
hive.enforce.bucketing=false
hive (qxbmi)> set hive.enforce.bucketing=true;
hive (qxbmi)> set hive.enforce.bucketing;
hive.enforce.bucketing=true

（2）创建桶表

现在我们创建表名为 bucket_table 的桶表，比如通过 clustered by(id) into 3 buckets 命令将 bucket_table 表按照 id 字段划分为 3 个桶。具体操作命令及执行结果如下所示：

hive (qxbmi)> create table if not exists bucket_table(
            > id int comment 'person_id',
            > name string comment 'person_name',
            > age int comment 'person_age')
            > clustered by(id) into 3 buckets
            > row format delimited
            > fields terminated by '\t';
OK
Time taken: 0.233 seconds
hive (qxbmi)> show tables;
OK
tab_name
bucket_table
person_partition_external
person_partition_manager
student
student2
student3
Time taken: 0.025 seconds, Fetched: 6 row(s)

（3）桶表中插入数据

我们可以通过执行 insert 语句 把 person_partition_external 表中的数据查询出来，然后插入到 bucket_table 桶表中，具体操作命令及结果如下所示：

hive (qxbmi)> insert overwrite table bucket_table select id, name, age from person_partition_external;
Query ID = root_20220528122516_f22b9e05-17f6-4871-806b-a0561f71a520
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 3
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1653704105737_0001, Tracking URL = http://master:8088/proxy/application_1653704105737_0001/
Kill Command = /opt/apps/hadoop-2.7.4/bin/hadoop job  -kill job_1653704105737_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 3
2022-05-28 12:25:31,787 Stage-1 map = 0%,  reduce = 0%
2022-05-28 12:25:40,313 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.29 sec
2022-05-28 12:25:49,983 Stage-1 map = 100%,  reduce = 33%, Cumulative CPU 1.29 sec
2022-05-28 12:26:02,948 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 6.62 sec
MapReduce Total cumulative CPU time: 6 seconds 620 msec
Ended Job = job_1653704105737_0001
Loading data to table qxbmi.bucket_table
Table qxbmi.bucket_table stats: [numFiles=3, numRows=5, totalSize=99, rawDataSize=94]
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 3   Cumulative CPU: 6.62 sec   HDFS Read: 13754 HDFS Write: 321 SUCCESS
Total MapReduce CPU Time Spent: 6 seconds 620 msec
OK
id      name    age
Time taken: 49.35 seconds

hive (qxbmi)> select * from bucket_table;
OK
bucket_table.id bucket_table.name       bucket_table.age
20220504        huanglei        23
20220501        lihong  25
20220505        wanggo  22
20220502        wangjia 26
20220503        lixiang 24
Time taken: 0.154 seconds, Fetched: 5 row(s)

数据将被划分为 3 个文件存放到表路径下，每个文件代表一个桶，查看 bucket_table 桶表目录下的数据文件的具体操作如下所示：

hive (qxbmi)> dfs -ls /hive/warehouse/qxbmi.db/bucket_table;
Found 3 items
-rwxr-xr-x   3 root supergroup         40 2022-05-28 12:26 /hive/warehouse/qxbmi.db/bucket_table/000000_0
-rwxr-xr-x   3 root supergroup         39 2022-05-28 12:25 /hive/warehouse/qxbmi.db/bucket_table/000001_0
-rwxr-xr-x   3 root supergroup         20 2022-05-28 12:26 /hive/warehouse/qxbmi.db/bucket_table/000002_0

（4）查看桶表文件内容

查看每个桶表文件内容的具体操作命令及执行结果如下所示：

hive (qxbmi)> dfs -cat /hive/warehouse/qxbmi.db/bucket_table/000000_0;
20220504        huanglei        23
20220501        lihong  25
hive (qxbmi)> dfs -cat /hive/warehouse/qxbmi.db/bucket_table/000001_0;
20220505        wanggo  22
20220502        wangjia 26
hive (qxbmi)> dfs -cat /hive/warehouse/qxbmi.db/bucket_table/000002_0;
20220503        lixiang 24

4.3.2 创建表

创建表的【语法】如下：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_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]
[STORED AS file_format] [LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] 
[AS select_statement]

我们现在对创建表语法中涉及到的关键字段做一下简单的解释：

• CREATE TABLE： 创建一个指定名字的表。如果相同的表名已经存在，则抛出异常；我们可以使用 IF NOT EXISTS 关键字忽略这个异常。
• EXTERNAL： 该关键字表示创建一个外部表，在建表的同时可以指定一个指向真实数据的路径（location）;在删除表的时候，内部表的元数据信息和真实数据会被一并删除，而外部表只删除元数据，不删除真实数据。外部表在生产环境中是最常用的一种方式，Hive 一般会以外部表的方式连接 HBase 和 Spark 中的数据。
• COMMENT：给表或字段（列）添加注释。
• PARTITIONED BY： 创建表分区，该关键字在生产环境中是必选的，其目的就是为了降低遍历数据的规模，提高查询效率。
• CLUSTERED BY：创建分桶表。
• SORTED BY：该关键字不常用，对桶中的一个或多个列另外排序。

ROW FORMAT DELIMITED

[FIELDS TERMINATED BY char]

[COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char]

[LINES 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 确定表的具体的列的数据。

• STORED AS： 指定存储文件类型。常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列 式存储格式文件）。如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
• LOCATION ：指定表在 HDFS 上的存储位置。
• AS：后跟查询语句，根据查询结果创建表。
• LIKE：允许用户复制现有的表结构，但是不复制数据。

4.3.3 修改表

在 Hive 中，我们可以使用 ALTER TABLE 子句来修改表的属性，实际上也就是修改表的元数据，而不会修改表中真实的数据。

（1）重命名（rename to）表

【语法】：

ALTER TABLE table_name RENAME TO new_table_name

（2）更新（change）列信息

更新列操作包含有：修改列信息、字段类型或类型。

【语法】：

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name 
column_type [COMMENT col_comment] [FIRST|AFTER column_name]

（3）增加（add）和替换（replace）列信息

我们可以通过 add columns 命令给表增加一个或多个列。

【语法】：

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT 
col_comment], ...) 

[c-alert type="success"]

注意：add 是代表新增一个字段，字段位置在所有列后面，但在 partition 列之前。

replace 则是代表替换表中所有字段。

[/c-alert]

（4）删除（replace）列信息

replace 这个关键字主要功能是替换，所以如果想只删除某几列，可以使用 replace 命令，只是里面的字段保留想要的那些字段，比如：

hive (qxbmi)> alter table bucket_table replace columns(id int,name string);

这条语句将保留 id、name 两个字段，原有的 age 字段将被删除。

4.3.4 清空表

如果只想保留表结构而不保留原有数据，可以通过命令 trancate table table_name; 清空表数据。如：

hive (qxbmi)> truncate table bucket_table;

[c-alert type="warning"]注意：truncate 不能删除外部表！因为外部表里的数据并不是存放在 Hive metastore 中。创建表的时候指定了 external ，外部表在删除分区后，HDFS 中的数据还存在，不会被删除。因此要想删除外部表数据，可以把外部表转成内部表或者删除 HDFS 文件。[/c-alert]

4.3.5 删除表

删除表的命令为：drop，使用 drop 命令删除 bucket_table 表的具体操作命令及结果如下所示：

hive (qxbmi)> drop table if exists bucket_table;

4.4 小结

本章节我们主要学习了 Hive 数据存储单元（数据库、表、分区、分桶），有关数据库的相关操作，如创建、查询、切换、查看、修改和删除等操作，也学习了有关表相关的操作。重点要掌握内部表和外部表在操作上的一些区别。熟练掌握有关表操作（创建、修改、清空、删除等）的相关语句。