第 4 章 Hive DDL
- Hive
- 2022-05-30
- 21热度
- 0评论
Hadoop HDFS 进行数据存储的，但 Hive 中所有真实数据都存储在 HDFS 中，元数据（metastore）存储在关系型数据库中，如 Hive 自带的 Derby，MySQL
为了有效地对真实数据进行管理，根据粒度大小，Hive 将真实数据划分为如下数据单元。
hive.metastore.warehouse.dir 目录下的一个文件夹。本质作用就是避免表、视图、分区、列
表（Table）由列组成，在表上可以进行过滤、映射、连接和联合 操作。表中的所有数据被存储在一个目录中。表在 HDFS 中表现为所属数据库目录下的子目录，具体又可以分为内部表 和 外部表。
- ：类似于 RDBMS 中的表，由 Hive 统一管理；
- 外部表
每一个表可以有一个或几个分区键，键值决定了数据是如何存储的。每个表都可以按指定的键分为多个分区。分区的作用是提高查询的效率，其在 HDFS 中表现为表目录下的子目录。
根据表中某一列的哈希值可以将数据划分为多个分桶（Buckets），在 HDFS 在分桶表现为同一个目录下根据哈希散列之后的多个文件。每一个桶以文件的形式存储在目录中。
我们可以看到，Hive 中的数据单元划分与 RDBMS 的物理模型非常类似，但在 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的表。
test数据库下users表中的数据文件。
（5）“/hive/warehouse/test.db/orders/year=2022/part-0000······” 表示test数据库中orders表里的数据按照年份进行分区，这里显示的是分区：year=2022 下的数据文件。
Hive 中的数据库和常用的关系型数据库中的数据库作用几乎一样，在实际生产环境中，如果表非常多的话，一般会使用数据库把表组织起来，形成逻辑组，这样可以有效防止大规模集群中表命名冲突的问题。
由于不可能把所有的数据表都放在default 数据库中，所以需要创建新的数据库。创建数据库的语法如下：
CREATE DATABASE [IF NOT EXISTS] DATABASE_NAME;
[c-alert type="warning"][/c-alert]
qxbmi
hive (default)> create database qxbmi;
OK
Time taken: 1.205 secondsshow 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 secondsdesc 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)show databases;
hive (default)> show databases;
OK
database_name
default
qxbmi
qxbmi_1
Time taken: 0.021 seconds, Fetched: 3 row(s)从上面结果可以看出，当前数据库中有 3 个数据库。
default 数据库，当我们需要切换到其他数据库时，可以通过命令 use database;
hive (default)> use qxbmi;
OK
Time taken: 0.028 seconds
hive (qxbmi)> default 切换到 qxbmi
[warning]hive-site.xml 中就行配置，将 hive.cli.print.current.db 属性值设置为 true[/warning]
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
<description>Whether to include the current database in the Hive prompt.</description>
</property>如果我们想要查看某个数据库的相关信息，可以通过命令 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)[danger]describe 和 desc 可替换，关键字 database 和 schema[/danger]
从上面输出的信息中可以看到 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
[warning]default[/warning]
当我们想对数据库进行修改的时候，可以通过命令 alter database database_name; 修改数据库。
ALTER (DATABASE|SCHEMA) DATABASE_NAME
SET DBPROPERTIES(PROPERTY_NAME=PROPERTY_VALUE,......)qxbmi
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)[danger][/danger]
如果我们想删除不需要的数据库，可以通过命令drop database database_name;
1）删除空数据库：
hive (qxbmi)> drop database qxbmi_1;
OK
Time taken: 0.519 secondsif 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 seconds3）如果数据库不为空，可以使用 cascadeqxbmi 中创建一张表后再删除该数据库操作。
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 secondsqxbmi
hive (qxbmi)> show databases;
OK
database_name
default
Time taken: 0.01 seconds, Fetched: 1 row(s)qxbmi
我们再对数据表操作之前先要明确对哪种类型的表进行操作。
内部表、外部表、分区表、桶表
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 seconds2）根据查询结果创建表（查询的结果会添加到新创建的表中）
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 seconds4）查询表的类型：
hive (qxbmi)> desc formatted student2;
OK
Table Type: MANAGED_TABLE2.外部表
所有权并不由 Hive 拥有，这种情况下就可以创建一个外部表（External Table）来指向这份数据。
[danger]external 指明该表是外部表，location[/danger]
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
[warning]注意：partitioned by 子句中定义的分区字段不能和定义表的字段重复，否则会报错。[/warning]
由于分区表会将所有分区字段的（如 province='guizhou' 和 city='guiyang'）数据都存放在对应目录下，所有在查询数据（如查询 province='guizhou' 和 city='guiyang'）的时候，Hive 只会扫描对应分区目录下的数据，减少了扫描的数据量，从而提高查询效率。尤其是对于大规模数据集时，分区表可以显著提高查询效率。
如果没有分区表的话，Hive 就需要全表扫描，所以我们可以把它理解为一种简单的索引。
有一种特殊情况就是，一条查询包含所有的分区的语句，这条查询将会消耗集群巨大的资源和时间。
对于这种特殊情况，我们可以将 Hive 的安全措施设置为“strict”模式，该设置在 Hive 配置文件 hive-site.xml 中进行修改，也可以通过 Hive 命令行方式输入 set hive,mapred.mode=strict; 进行设置。这两种设置的区别在于：属性的生效范围不同。修改配置文件可以对所有 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 目录下，具体操作命令及执行结果如下所示：
[warning]/opt/apps/hive-1.2.1/data 目录下创建一个 person.txt[/warning]
20220501 lihong 25 guizhou guiyang
20220502 wangjia 26 beijing beijing
20220503 lixiang 24 guangzhou shenzhen
20220504 huanglei 23 chongqin chongqin
20220505 wanggo 22 chognqin chongqinhive (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.txt3）分区表操作
我们在 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）修改分区
我们对 表的分区进行修改，具体操作命令及结果如下所示：
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[danger]location[/danger]
（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)[danger]删除只是删除分区，查看分区信息的时候删除掉的分区没有了，但是文件系统上那个对应的目录还是存在的。dfs -rm -r[/danger]
4.桶表
在开始创建桶表之前，要先通过 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)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创建表的【语法】如下：
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]我们现在对创建表语法中涉及到的关键字段做一下简单的解释：
- 创建一个指定名字的表。如果相同的表名已经存在，则抛出异常；我们可以使用
IF NOT EXISTS关键字忽略这个异常。 - EXTERNAL： 该关键字表示创建一个外部表，在建表的同时可以指定一个指向真实数据的路径（location）;在删除表的时候，内部表的元数据信息和真实数据会被一并删除，而外部表只删除元数据，不删除真实数据。外部表在生产环境中是最常用的一种方式，Hive 一般会以外部表的方式连接 HBase 和 Spark 中的数据。
- COMMENT：给表或字段（列）添加注释。
- PARTITIONED BY： 创建表分区，该关键字在生产环境中是必选的，其目的就是为了降低遍历数据的规模，提高查询效率。
- CLUSTERED 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 确定表的具体的列的数据。
[danger]SerDe 是 Serialize/Deserilize 的简称， Hive 使用 Serde 进行行对象的序列与反序列化。[/danger]
- 指定存储文件类型。常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列 式存储格式文件）。如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
- LOCATION ：指定表在 HDFS 上的存储位置。
- AS：后跟查询语句，根据查询结果创建表。
4.3.3 修改表
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
hive (qxbmi)> alter table bucket_table replace columns(id int,name string);
id、name 两个字段，原有的 age
trancate table table_name;
hive (qxbmi)> truncate table bucket_table;
[c-alert type="warning"]注意：truncate 不能删除外部表！因为外部表里的数据并不是存放在 Hive metastore 中。创建表的时候指定了 external ，外部表在删除分区后，HDFS 中的数据还存在，不会被删除。因此要想删除外部表数据，可以把外部表转成内部表或者删除 HDFS 文件。[/c-alert]
删除表的命令为：drop，使用 drop 命令删除 bucket_table 表的具体操作命令及结果如下所示：
hive (qxbmi)> drop table if exists bucket_table;
4.4 小结