Hive——DDL（Data Definition Language）数据定义语句用法详解_hive ddl语句

1.数据库操作

1.1创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
1
2
3
4

• IF NOT EXISTS：可选参数，表示如果数据库已经存在，则不会创建，避免出现重复创建的情况。
• COMMENT：可选参数，用于添加数据库的注释。
• LOCATION：可选参数，指定数据库的存储路径

• WITH DBPROPERTIES：可选参数，用于设置数据库的额外属性。

---

示例：

CREATE DATABASE IF NOT EXISTS mydatabase
COMMENT 'This is my database'
LOCATION '/user/hive/warehouse/mydatabase.db'
WITH DBPROPERTIES ('owner'='yushifu', 'created_date'='2024-04-23');
1
2
3
4

• 创建一个名为"mydatabase"的数据库，如果该数据库已存在则不会重新创建。
• 指定了注释为"This is my database"，并将其存储路径设置为"/user/hive/warehouse/mydatabase.db"
• 设置了额外属性"owner"为"yushifu"，“created_date"为"2024-04-23”。

---

1.2 查询数据库

在Hive中，可以使用以下语句来列出所有的数据库，或者使用通配符来匹配数据库名：

SHOW DATABASES [LIKE 'identifier_with_wildcards'];
1

• LIKE 'identifier_with_wildcards'：可选参数，用于指定要匹配的数据库名。

---

示例：

- （1）列出所有数据库：

SHOW DATABASES;
1

- （2）列出以my开头的数据库：

show databases like 'my*';
1

- （3）列出以d开头或者m开头的数据库

show databases like 'd*|m* ';
1

---

1.3 查看数据库信息

DESCRIBE DATABASE [EXTENDED] db_name;
1

在Hive中，DESCRIBE DATABASE EXTENDED命令提供了比普通DESCRIBE DATABASE更详细的数据库信息，包括一些额外的属性和元数据。

使用DESCRIBE DATABASE EXTENDED命令可以获取以下额外的信息：

1. 数据库的所有者（Owner）：显示数据库的所有者信息。
2. 创建时间（Create Time）：显示数据库创建的时间戳。
3. 位置（Location）：显示数据库在Hadoop文件系统中的存储位置。
4. 参数（Parameters）：显示数据库的配置参数，如属性、描述等。

---

示例：

- （1）查看数据库mydatabase的基本信息：

---

- （2）查看数据库mydatabase的详细信息：

---

1.4 修改数据库

• 用户可以使用alter database命令修改数据库某些信息，其中能够修改的信息包括dbproperties、location、owner user。
• 需要注意的是：修改数据库location，不会改变当前已有表的路径信息!! 而只是改变后续创建的新表的默认的父目录。

1. 修改数据库的所有者：

ALTER DATABASE db_name SET OWNER [USER|ROLE] owner_name;
1

2. 修改数据库的位置：

ALTER DATABASE db_name SET LOCATION 'new_location';
1

这将会修改数据库的存储位置。

3. 修改数据库的属性：

ALTER DATABASE db_name SET DBPROPERTIES (property_name=property_value, ...);
1

---

示例：

-- 修改数据库的所有者为 'new_owner'
ALTER DATABASE mydatabase SET OWNER USER new_owner;

-- 修改数据库的存储位置为 '/new_location'
ALTER DATABASE mydatabase SET LOCATION '/new_location';

-- 修改数据库的属性，例如添加或者修改描述
ALTER DATABASE mydatabase SET DBPROPERTIES ('description' = 'New description for my database');

1
2
3
4
5
6
7
8
9

---

1.5 删除数据库

DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];

• RESTRICT：严格模式，若数据库不为空，则会删除失败，默认为该模式。
• CASCADE：级联模式，若数据库不为空，则会将库中的表一并删除。

---

1.6切换数据库

USE database_name

---

2.数据表操作

2.1 创建表

2.1.1 普通建表

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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, ...)];
1
2
3
4
5
6
7
8
9
10
11
12
13

• CREATE: 指定了创建表的操作。

• TEMPORARY: 可选的关键字，用于创建临时表，该表在当前会话结束时将被删除。

• EXTERNAL: 可选的关键字，用于创建外部表，该表的数据存储在外部位置，Hive只管理元数据，不管理数据文件。外部表，与之相对应的是内部表（管理表）。管理表意味着Hive会完全接管该表，包括元数据和HDFS中的数据。而外部表则意味着Hive只接管元数据，而不完全接管HDFS中的数据。

• IF NOT EXISTS: 可选的关键字，如果表已经存在，则不报错，直接忽略该操作。

• db_name: 可选的数据库名称，指定了创建表所属的数据库，如果不指定，默认在当前数据库建表。

• table_name: 要创建的表的名称。

• col_name data_type [COMMENT col_comment]: 表的列定义，包括列名、数据类型，以及可选的列注释。

• COMMENT table_comment: 可选的表注释，描述整个表的含义。

• PARTITIONED BY: 可选的子句，用于指定分区列，表将根据这些列的值进行分区存储。（创建分区表）

• CLUSTERED BY: 可选的子句，用于指定表的分桶列，将表数据分桶存储（创建分桶表）。

• SORTED BY: 可选的子句，用于指定分桶内的数据排序方式。

• ROW FORMAT: 可选的子句，用于指定表中数据的行格式。
  

• STORED AS: 可选的子句，用于指定表数据的存储格式。
  常用的文件格式有，textfile（默认值），sequence file，orc file、parquet file等等。

• LOCATION: 可选的子句，用于指定表数据存储的位置，仅在创建外部表时使用。
  在这里插入图片描述
  

• TBLPROPERTIES: 可选的子句，用于指定表的属性，例如压缩方式、存储格式等。

示例：

-- 创建名为 "t1" 的表，如果该表不存在则执行创建操作
CREATE TABLE IF NOT EXISTS default.t1
(
    -- 学生ID，整数类型，用于唯一标识学生
    id INT COMMENT 'student\'s id',
    
    -- 学生姓名，字符串类型，存储学生的姓名信息
    name STRING COMMENT 'student\'s name',
    
    -- 学生地址，字符串类型，存储学生的地址信息
    addr STRING COMMENT 'student\'s addr'
)
-- 表的注释，描述该表存储的是学生信息
COMMENT 'student\'s information'
-- 指定行格式为逗号分隔的文本文件
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
-- 指定表数据的存储格式为文本文件
STORED AS TEXTFILE
-- 指定表的属性，包括作者和创建时间
TBLPROPERTIES ('author'='yushifu', 'create_time'='2024-04-23');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- 如果不存在，创建名为 "t2" 的表
CREATE TABLE IF NOT EXISTS default.t2
(
    -- 学生ID，整数类型，用于唯一标识学生
    id INT COMMENT 'student\'s id',
    
    -- 学生姓名，字符串类型，存储学生的姓名信息
    name STRING COMMENT 'student\'s name',
    
    -- 学生地址，字符串类型，存储学生的地址信息
    addr STRING COMMENT 'student\'s addr'
)
-- 表的注释，描述该表存储的是学生信息
COMMENT 'student\'s information'
-- 指定行格式为逗号分隔的文本文件
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

STORED AS TEXTFILE

-- 指定表数据的存储位置为 '/t2'
LOCATION '/t2'
-- 指定表的属性，包括作者和创建时间
TBLPROPERTIES ('author'='yushifu', 'create_time'='2024-04-23');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

---

2.1.2 Create Table As Select（CTAS）建表

CREATE TABLE new_table_name
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION 'hdfs_path']
[TBLPROPERTIES (property_name = property_value, ...)]
AS
SELECT column1, column2, ...
FROM existing_table_name
[WHERE condition];
1
2
3
4
5
6
7
8
9

• new_table_name: 新表的名称。
• ROW FORMAT row_format: 可选项，用于指定新表的行格式。
• STORED AS file_format: 可选项，用于指定新表的存储文件格式。
• LOCATION 'hdfs_path': 可选项，指定新表的存储位置。
• TBLPROPERTIES (property_name = property_value, ...): 可选项，用于指定表级别的属性。
• AS: 关键字，表示创建表并将其填充为SELECT查询的结果。
• SELECT column1, column2, ...: SELECT子句用于指定要从现有表中选择的列。
• FROM existing_table_name: 指定要从中获取数据的现有表。
• [WHERE condition]: 可选项，用于过滤要选择的行。

---

示例：

---

2.1.3 Create Table Like语法

该语法允许用户复刻一张已经存在的表结构，与上述的CTAS语法不同，该语法创建出来的表中不包含数据。

2.1.4 内部表与外部表

内部表：

• Hive中默认创建的表都是的内部表，有时也被称为管理表。
• 对于内部表，Hive会完全管理表的元数据和数据文件。
  
  desc formatted student;查看详细表信息可知表类型。
  
  

删除内部表之后 HDFS的也会删除，因为对于内部表，Hive会完全管理表的元数据和数据文件。

删除内部表后，HDFS中的文件也会随之删除！

---

外部表：

• 外部表通常可用于处理其他工具上传的数据文件。
• 对于外部表，Hive只负责管理元数据，不负责管理HDFS中的数据文件。
  
  
  

---

2.1.5 SERDE和复杂数据类型

若现有如下格式的JSON文件需要由Hive进行分析处理，请考虑如何设计表？

{
  "name": "yushifu",
  "age": 18,
  "hobbies": ["singing", "jumping", "rapping","basketball"],
  "address": {
    "street": "123 Main Street",
    "city": "弗洛里达",
    "country": "USA"
  }
}

1
2
3
4
5
6
7
8
9
10
11

• 可以考虑使用专门负责JSON文件的JSON Serde。
• 设计表字段时，表的字段与JSON字符串中的一级字段保持一致。
• 对于具有嵌套结构的JSON字符串，考虑使用合适复杂数据类型保存其内容。

create table stu
(
    name     string,
    age		 int,
    hobbies	 map<string,int>,
    address  struct<city:string,street:string,postal_code:int>
)
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
location '/user/hive/warehouse/teacher';

1
2
3
4
5
6
7
8
9
10

---

2.2 查看表

2.2.1 展示表

SHOW TABLES [IN database_name] LIKE ['identifier_with_wildcards'];
1

• like通配表达式说明：*表示任意个任意字符，|表示或的关系

show tables like ‘stu*’;

---

2.1.5 查看表信息

（1）查看基本信息

desc table;
1

(2) 查看详细信息

desc formatted table;
1

---

2.3 修改表

在Apache Hive中，要修改表的属性或结构，你可以使用ALTER TABLE语句。以下是一些常见的用法：

1.修改表名称：

ALTER TABLE old_table_name RENAME TO new_table_name;

2.添加列：
该语句允许用户增加新的列，新增列的位置位于末尾。
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...);

3.修改列名称和类型(更新列)：
该语句允许用户修改指定列的列名、数据类型、注释信息以及在表中的位置。
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

4.替换列：
该语句允许用户用新的列集替换表中原有的全部列。
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

2.4 删除表

DROP TABLE [IF EXISTS] table_name;
1

2.5 清空表

清空表数据内容。

TRUNCATE [TABLE] table_name
1

• truncate只能清空管理表，不能删除外部表中数据。
• 因为Hive只能管理内部表的数据文件，外部表无权限处理。
  

---

整理不易！一键三连呀各位~