Hive 之 DDL操作

浅浅的花香味﹌ 2023-09-29 19:07 19阅读 0赞

`DDL` 操作是用于操作对象和对象的属性，这种对象包括数据库本身，以及数据库对象，像：表、视图等等

## 1. 数据库 ##

### 1.1 创建数据库 ###

数据库在 `HDFS` 上的默认存储路径是 `/user/hive/warehouse/*.db`

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
    [COMMENT database_comment]  // 库的注释说明
    [LOCATION hdfs_path]        // 库在hdfs上的路径
    [WITH DBPROPERTIES (property_name=property_value, ...)]; // 库的属性

示例：

create database  if not exists mydb2 
    comment 'this is my db' 
    location 'hdfs://hadoop1:9000/mydb2' 
    with dbproperties('ownner'='rose','tel'='12345','department'='development');

### 1.2 数据库查询 ###

show databases;
    show databases like 'db_hive*';		// 过滤
    use databases;	// 使用数据库
    desc database db_hive;	// 显示数据库信息
    desc database extended db_hive;		// 显示数据库详细信息

### 1.3 修改数据库 ###

`alter table` 命令可以修改数据库的 `DBPROPERTIES` 的键值对，但是数据库的元数据信息不可更改，如：数据库名称、所在目录位置：

// 同名的属性值会覆盖，之前没有的属性会新增
    alter database mydb2 set dbproperties('ownner'='tom','empid'='10001');
    				
    // 查询修改
    desc database extended mydb2;

### 1.4 删除数据库 ###

// 删除空数据库，只能删空库
    drop database if exists db_hive2;
    
    hive> drop database db_hive cascade;

## 2. 数据表 ##

### 2.1 表分类 ###

*  管理表：删除操作时，会删除元数据和数据本身
 *  外部表：删除操作时，只会删除元数据
 *  分区表：在建表时，指定了`PARTITIONED BY`，分区的目的是为了就数据，分散到多个子目录中，在执行查询时，可以只选择查询某些子目录中的数据，加快查询效率
 *  分桶表：建表时指定了 `CLUSTERED BY`，区别于分区表，把数据分散到多个文件中。可以结合 `hive` 提供的抽样查询，只查询指定桶的数据

### 2.2 创建表 ###

创建表的时候，`hive` 会：

*  在 `hdfs` 生成表的路径
 *  向 `MySQL` 的 `metastone` 库中插入两条表的信息（元数据）

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] 	//表中的数据要以哪种文件格式来存储，默认为TEXTFILE（文本文件）
    [LOCATION hdfs_path]		// 指定表在HDFS上的存储位置

> 可选字段说明

*  `EXTERNAL`：创建**外部表**，在建表的同时指定实际数据路径 `location`，`hive` 创建内部表时，会将数据移动到数据仓库所指向的路径；若是外部表，仅记录数据所在路径，不对数据位置做任何改变。删除表时，内部表元数据、数据都会被删除，而外部表只会删除元数据
 *  `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`确定表的具体的列的数据。

`SerDe`是`Serialize/Deserilize`的简称，目的是用于序列化和反序列化。

*  `STORED AS`：指定存储文件类型，如：`sequencefile、rcfile` 等，默认 `textfile`
 *  `LOCATION` ：指定表在`HDFS`上的存储位置
 *  `LIKE` 允许用户复制已存在的表结构，但是不会复制数据

--------------------

示例

// 创建表
    create table if not exists student2(
    id int, name string
    )
    row format delimited fields terminated by '\t'
    stored as textfile
    location '/user/hive/warehouse/student2';
    
    // 根据查询结果创建表（查询的结果会添加到新创建的表中）
    create table if not exists student3 as select id, name from student;
    
    // 根据已经存在的表结构创建表
    create table if not exists student4 like student;
    
    // 查看表类型
    hive (hive_1)> desc formatted student;
    OK
    col_name        data_type       comment
    # col_name              data_type               comment
    
    id                      int
    name                    string
    
    # Detailed Table Information
    Database:               hive_1
    Owner:                  hadoop
    CreateTime:             Sun Nov 21 18:13:32 CST 2021
    LastAccessTime:         UNKNOWN
    Protect Mode:           None
    Retention:              0
    Location:               hdfs://hadoop1:9000/user/hive/warehouse/hive_1.db/student
    Table Type:             MANAGED_TABLE	// 表示为管理表

#### 2.2.1 管理表 ####

管理表也称内部表，默认创建的就是管理表，特点就是 **删除表的同时也会删除元数据**，与之对应的外部表就**只会删除元数据，而不删除数据**

#### 2.2.2 外部表 ####

实际生产中使用的大部分是外部表

// 创建一个外部表，有 EXTERNAL 关键字就是外部表
    create EXTERNAL table if not exists student2(
    id int, name string
    )
    row format delimited fields terminated by '\t'
    stored as textfile
    location '/user/hive/warehouse/student2';
    
    // 查询表类型
    hive (default)> desc formatted dept;
    Table Type:             EXTERNAL_TABLE

#### 2.2.3 管理表和外部表的相关转换 ####

// 修改内部表 test1 为外部表
    alter table test1 set tblproperties('EXTERNAL'='TRUE');
    
    // 修改外部表 test1 为内部表
    alter table student2 set tblproperties('EXTERNAL'='FALSE');

> 注意：`'EXTERNAL'='TRUE')`和`('EXTERNAL'='FALSE')`为固定写法，区分大小写！

### 2.3 分区表 ###

分区表实际是在 `hdfs` 上创建多个独立的文件夹，`hive` 的分区即是分目录。将一个大的数据集分隔为多个小的数据集，在查询时通过 `where` 子句来指定查询分区，而不是全表查询，其目的是为了提高效率。

语法：

// 比普通建表语句多了一个 partitioned by
    
    // 一级分区表，其中 month 为分区字段，string 为其类型
    create table dept_partition(
    deptno int, dname string, loc string
    )
    partitioned by (month string)
    row format delimited fields terminated by '\t';
    
    // 多级分区表，有多个分区字段 area、province
    create external table if not exists default.deptpart2(
    deptno int,
    dname string,
    loc int
    )
    PARTITIONED BY(area string, province string)
    row format delimited fields terminated by '\t';

> 注意

*  如果表是个分区表，在导入数据时，必须指定向哪个分区目录导入数据，否则会报错
 *  如果表是多级分区表，在导入数据时，数据必须位于最后一级分区的目录

#### 2.3.1 创建分区 ####

*  方法一：`alter table 表名 add partition(分区字段名=分区字段值);`
    
     *  分区创建成功后，会在 `hdfs` 生成分区路径、也会在 `partitions` 表中生成分区的元数据
     *  创建多个分区：`alter table dept_partition add partition(month='201705') partition(month='201704');`
 *  方法二：`load` 命令向分区加载数据，若分区不存在，则会自动创建
 *  方法三：若数据已上传到 `hdfs`，可使用修复分区命令的方式来自动生成分区的元数据：`msck repair table 表名;`

#### 2.3.2 其他操作 ####

// 查看分区表有多少分区
    show partitions 表名
    
    // 查看分区表结构
    desc formatted dept_partition;
    
    // 删除分区
    alter table dept_partition drop partition (month='201704');
    
    // 删除多个分区
    alter table dept_partition drop partition (month='201705'), partition (month='201706');
    
    // 多分区联合查询
    select * from dept_partition where month='201709'
        union
        select * from dept_partition where month='201708'
        union
        select * from dept_partition where month='201707';

#### 2.3.3 练习一 ####

需求：创建一个一级分区表

1、创建分区表：

create table dept_partition(
    deptno int, dname string, loc string
    )
    partitioned by (area string)
    row format delimited fields terminated by '\t';
    
    // 查看表描述信息
    hive (default)> desc dept_partition;
    OK
    col_name        data_type       comment
    deptno                  int
    dname                   string
    loc                     string
    area                    string
    
    # Partition Information
    # col_name              data_type               comment
    
    area                    string
    Time taken: 0.398 seconds, Fetched: 9 row(s)
    
    // 查看分区信息
    hive (default)> show partitions dept_partition;
    OK
    partition
    area=huanan
    Time taken: 0.46 seconds, Fetched: 1 row(s)

2、加载数据：

// 将本地数据加载到分区目录 area='huanan'，
    load data local inpath '/home/hadoop/apps/big_source/files/dept.txt' into table default.dept_partition partition(area='huanan');
    
    // hdfs 上数据存储路径
    /user/hive/warehouse/dept_partition/area=huanan

3、查询：

--------------------

#### 2.3.4 练习二 修复分区信息 ####

1、创建 `hdfs` 目录，并上传数据

// 直接 put，可能会导致修复失败
    [hadoop@hadoop1 files]$ hadoop fs -mkdir -p /user/hive/warehouse/dept_partition/area=zhong
    [hadoop@hadoop1 files]$ hadoop fs -put dept.txt /user/hive/warehouse/dept_partition/area=zhong
    
    // dept.txt
    10	ACCOUNTING	1700
    20	RESEARCH	1800
    30	SALES	1900
    40	OPERATIONS	1700

2、查看分区信息、修复分区：

### 2.4 分桶表 ###

#### 2.4.1 分桶表创建 ####

在建表的时指定 `CLUSTERED BY`，那么这个表就是分桶表。区别于分区表，分桶表将数据分散到多个文件，可以结合 `hive` 提供的抽样查询，只查询指定桶的数据，另外还可以提高 `join` 查询效率

分桶原理跟 `MR` 中的 `HashPartitioner` 的原理一模一样：

*  `MR` ：按照 `key` 的 `hash` 值去模除以`reductTask`的个数
 *  `hive`：按照分桶字段的 `hash` 值去模除以分桶个数，如：`1001、1005、1009` 的 `hash` 值是它本身，三个值对分桶个数取模都是 1，`1001/1005/1009 % 4 = 1`，那么这三条数据会被分在同一个文件中

// col_name 分桶字段
    [CLUSTERED BY (col_name, col_name, ...) 
    		分桶的字段，是从表的普通字段中来取
    [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]

--------------------

**示例**

1、创建分桶表：

// 创建 4 个桶
    create table stu_buck(id int, name string)
    clustered by(id) 
    SORTED BY (id desc)
    into 4 buckets
    row format delimited fields terminated by '\t';

2、设置强制分桶、排序（必须）：

set hive.enforce.bucketing=true;	// 需要打开强制分桶开关
    set hive.enforce.sorting=true;		// 需要打开强制排序开关

3、加载数据：

// 使用 load 方式加载数据，发现并没有分桶，这是因为向分桶表导入数据时，必须运行MR程序，才能实现分桶操作，需要使用 insert 方式插入数据
    load data local inpath '/home/hadoop/apps/big_source/files/student.txt' into table stu_buck;

创建临时表，通过查询临时表的方式向分桶表插入数据：

// 1.  从 hdfs 或本地磁盘中 load 数据，导入中间表
    create table stu_buck_tmp(id int, name string)
    row format delimited fields terminated by '\t';
    
    load data local inpath '/home/hadoop/apps/big_source/files/student.txt' into table stu_buck_tmp;
    
    // 通过从中间表查询的方式的完成数据导入
    insert into table stu_buck select id, name from stu_buck_tmp;

> 总结

*  向分桶表插入数据会运行 `mr` 程序，需要使用 `insert` 来插入数据
 *  创建分桶表时，需要打开强制分桶的开关
 *  需要确保 `reduce` 的数量与表中的 `bucket` 数量一致，有两种方式
    
     *  让 `hive` 强制分桶，自动按照分桶表的`bucket` 进行分桶（推荐）
     *  手动指定 `reduce` 数量，并在 `SELECT` 后增加`CLUSTER BY` 语句
    
        set mapreduce.job.reduces = num;
        set mapreduce.reduce.tasks = num;

#### 2.4.2 抽样查询 ####

对于一个大的数据集来说，有时并不需要查询全部，而是抽样部分有代表性的结果即可

格式：

select * from 分桶表 tablesample(bucket x out of y on 分桶表分桶字段);

参数详解：

*  `y`：必须是 `table` 总 `bucket`（即桶）数目的倍数或者因子，`hive` 会根据 `y` 的大小来决定抽样的比例。如：`bucket=4`，当`y=2` 时，表示抽取 `(4/2)` 个 `bucket` 的数据，当`y=8`时，抽取`(4/8=)1/2`个`bucket`的数据
 *  `x`：表示从第几个 `bucket` 开始抽取，如：`bucket=4`，`tablesample(bucket 1 out of 2)`，表示总共抽取`（4/2=）2`个 `bucket` 的数据，抽取第`1(x)`个和第`3(x+y)`个 `bucket` 的数据。

翻译过来就是：从第 x 桶开始抽样，每间隔 y 桶抽一桶，知道抽满 `bucket/y` 桶

// 从第1桶(0号桶)开始抽，抽第x+y*(n-1)，一共抽2桶：0、2 号桶
    0: jdbc:hive2://hadoop1:10000> select * from stu_buck tablesample(bucket 1 out of 2 on id);
    +--------------+----------------+--+
    | stu_buck.id  | stu_buck.name  |
    +--------------+----------------+--+
    | 1016         | ss16           |
    | 1012         | ss12           |
    | 1008         | ss8            |
    | 1004         | ss4            |
    | 1014         | ss14           |
    | 1010         | ss10           |
    | 1006         | ss6            |
    | 1002         | ss2            |
    +--------------+----------------+--+
    
    // 从第 1 桶开始抽，抽 4 桶：0、1、2、3 桶
    0: jdbc:hive2://hadoop1:10000> select * from stu_buck tablesample(bucket 1 out of 1 on id);
    +--------------+----------------+--+
    | stu_buck.id  | stu_buck.name  |
    +--------------+----------------+--+
    | 1016         | ss16           |
    | 1012         | ss12           |
    | 1008         | ss8            |
    | 1004         | ss4            |
    | 1013         | ss13           |
    | 1009         | ss9            |
    | 1005         | ss5            |
    | 1001         | ss1            |
    | 1014         | ss14           |
    | 1010         | ss10           |
    | 1006         | ss6            |
    | 1002         | ss2            |
    | 1015         | ss15           |
    | 1011         | ss11           |
    | 1007         | ss7            |
    | 1003         | ss3            |
    +--------------+----------------+--+
    
    // 从第 2 桶开始抽，抽 1 桶，即第 2 桶
    0: jdbc:hive2://hadoop1:10000> select * from stu_buck tablesample(bucket 2 out of 4 on id);
    +--------------+----------------+--+
    | stu_buck.id  | stu_buck.name  |
    +--------------+----------------+--+
    | 1013         | ss13           |
    | 1009         | ss9            |
    | 1005         | ss5            |
    | 1001         | ss1            |
    +--------------+----------------+--+
    
    // 从第 2 桶开始抽，抽 0.5 桶
    0: jdbc:hive2://hadoop1:10000> select * from stu_buck tablesample(bucket 2 out of 8 on id);
    +--------------+----------------+--+
    | stu_buck.id  | stu_buck.name  |
    +--------------+----------------+--+
    | 1009         | ss9            |
    | 1001         | ss1            |
    +--------------+----------------+--+

以下为 4 个桶数据分布：

// 第 1 桶
    [hadoop@hadoop1 apps]$ hadoop fs -cat /user/hive/warehouse/stu_buck/000000_0
    1016	ss16
    1012	ss12
    1008	ss8
    1004	ss4
    
    // 第 2 桶
    [hadoop@hadoop1 apps]$ hadoop fs -cat /user/hive/warehouse/stu_buck/000001_0
    1013	ss13
    1009	ss9
    1005	ss5
    1001	ss1
    
    // 第 3 桶
    [hadoop@hadoop1 apps]$ hadoop fs -cat /user/hive/warehouse/stu_buck/000002_0
    1014	ss14
    1010	ss10
    1006	ss6
    1002	ss2
    
    // 第 4 桶
    [hadoop@hadoop1 apps]$ hadoop fs -cat /user/hive/warehouse/stu_buck/000003_0
    1015	ss15
    1011	ss11
    1007	ss7
    1003	ss3

> 注意：x 的值必须小于等于 y 的值

### 2.5 修改表 & 删除表 ###

重命名表：

ALTER TABLE table_name RENAME TO new_table_name

增加/修改/替换列信息:

// 更新列
    ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
    
    // 替换列
    ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 
    
    // 添加一列
    alter table dept_partition add columns(deptdesc string);
    
    // 更新列
    alter table dept_partition change column deptdesc desc int;
    
    // 替换列
    alter table dept_partition replace columns(deptno string, dname string, loc string);
    
    // 改表的属性
    alter table 表名 set tblproperties(属性名=属性值)

删除表：

drop table tableName;
    truncate table tableName   // 清空管理表，只清空数据

## 3. 错误集锦 ##

1、启动 `hive` 报错 `org.apache.tez.dag.api.SessionNotRunning: TezSession has already shutdown. Application`

*  背景：使用 `insert` 往分桶表插入数据时报以上错误
 *  原因：`hadoop` 内存不足
 *  解决：调整内存，修改 `yarn-site.xml`，重启 `ResourceManager` 和 `NodeManager`

<property>
        <name>yarn.nodemanager.vmem-pmem-ratio</name>
        <value>3.0</value>
    </property>

参考：

*  [启动hive报错 org.apache.tez.dag.api.SessionNotRunning: TezSession has already shutdown. Application][hive_ org.apache.tez.dag.api.SessionNotRunning_ TezSession has already shutdown. Application]
 *  [org.apache.tez.dag.api.SessionNotRunning: TezSession has already shutdown. Application application\_1][org.apache.tez.dag.api.SessionNotRunning_ TezSession has already shutdown. Application application_1]

2、更换 `tez` 引擎后，`insert` 数据时报错：`org.apache.hadoop.yarn.exceptions.YarnException: Unauthorized request to start container.`

*  原因：`namenode、datanode` 时间同步问题
 *  解决：`datanode` 与 `namenode` 进行时间同步，每个节点都需要执行

cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
    ntpdate pool.ntp.org

[hive_ org.apache.tez.dag.api.SessionNotRunning_ TezSession has already shutdown. Application]: https://blog.csdn.net/qq_41489540/article/details/109308148
[org.apache.tez.dag.api.SessionNotRunning_ TezSession has already shutdown. Application application_1]: https://blog.csdn.net/w13716207404/article/details/103115921