Hive数仓操作（十六）

DML（数据操作语言）指的是用于操作数据的 SQL 语言部分，主要包括对数据的插入、更新、删除等操作。Hive 的 DML语句主要包括 INSERT、UPDATE 和 DELETE 。以下是一些重要的 Hive DML 语句及其解析。

Hive的DML语句

一、 插入操作INSERT

一般不会单条或几行插入，使用多表复制即可

1. 新增简单数据类型

单条插入:

INSERT INTO c1 VALUES (1007, "toly", "男", 19);

• 说明：将一条记录插入到表 c1 中。数据类型包括整数、字符串和小数等。

多条插入:

INSERT INTO c1 VALUES 
(1007, 'toly', '男', 19),
(1008, 'toly', '男', 19),
(1009, 'toly', '男', 19);

• 说明：一次性插入多条记录到表 c1。

2. Array 数组类型

使用 ARRAY:

INSERT INTO t12 SELECT 1001, ARRAY('eat', 'sleep');

• 说明：将一条记录插入到表 t12，第二列为一个包含两个字符串元素的数组。

3. Map 映射类型

使用 STR_TO_MAP:

INSERT INTO t14 SELECT 1001, STR_TO_MAP('chinese:99,math:88,english:77');

• 说明：将一条记录插入到表 t14，第二列为一个映射，键为科目，值为分数。

4. 多表复制

在复制数据之前，我们需要先创建目标表 stu05_1、stu05_2 和 stu05_3。可以使用 LIKE 语句创建表，保持相同的结构。

CREATE TABLE stu05_1 LIKE stu05;
CREATE TABLE stu05_2 LIKE stu05;
CREATE TABLE stu05_3 LIKE stu05;

复制:

FROM stu05
INSERT OVERWRITE TABLE stu05_1 SELECT * WHERE condition1;
INSERT OVERWRITE TABLE stu05_2 SELECT * WHERE condition2;
INSERT OVERWRITE TABLE stu05_3 SELECT * WHERE condition3;

• 说明：从源表 stu05 中读取数据，根据不同的条件分别写入到三个目标表 stu05_1、stu05_2 和 stu05_3。这样可以节省读取表格数据的时间。

5. INSERT INTO 方式

语法:

INSERT INTO TABLE table_name [PARTITION (partition_col = 'value' ...)]
SELECT ... FROM source_table WHERE ...;

示例:

INSERT INTO TABLE sales_data PARTITION (year = 2023)
SELECT * FROM staging_sales WHERE sale_date >= '2023-01-01';

解析:

• 该语句将从 staging_sales 表中选择数据，并插入到 sales_data 表中，分区为 year=2023。
• 如果 sales_data 表已经存在相应的年份分区，则数据会被追加到该分区。

6. INSERT OVERWRITE 方式

语法:

INSERT OVERWRITE TABLE table_name [PARTITION (partition_col = 'value' ...)]
SELECT ... FROM source_table WHERE ...;

示例:

INSERT OVERWRITE TABLE sales_data PARTITION (year = 2023)
SELECT * FROM staging_sales WHERE sale_date >= '2023-01-01';

解析:

• 该语句会覆盖 sales_data 表中 year=2023 分区的数据，插入新数据。
• 如果分区已经存在，原有数据会被删除，新的数据将替代它。

二、更新操作UPDATE 和删除操作 DELETE

数仓中的数据存在即有意义，一般不会进行更新和删除操作，虽然公司不用但一般都有这些功能，了解即可，反正我没用过

更新和删除操作的条件

1. 表必须为分桶表：

   • 表格需要使用分桶（Bucketing），这可以提高数据的管理和查询效率。

2. 数据存储格式：

   • 表格的数据必须存储为 ORC 格式，而不能是文本格式。ORC 格式支持 ACID 操作的事务特性。

3. 事务支持：

   • 表格必须启用事务支持，确保可以进行 ACID 操作。通常在创建表时设置属性 transactional='true'。

配置 Hive 为 ACID 模式

1. 启用并发支持：

   SET hive.support.concurrency=true;
   

2. 设置事务管理器：

   SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
   

3. 启用 ACID 语法支持：

   SET hive.exec.dynamic.partition.mode=nonstrict;
   

示例

以下是创建满足这些条件的表的示例：

CREATE TABLE your_table_name (
    column1 INT,
    column2 STRING,
    column3 STRING
)
CLUSTERED BY (column1) INTO N BUCKETS  -- 分桶
STORED AS ORC                           -- 使用 ORC 存储格式
TBLPROPERTIES ('transactional'='true'); -- 启用事务

1. UPDATE

语法:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

示例:

UPDATE sales_data SET sale_amount = sale_amount * 1.1 WHERE sale_date < '2022-01-01';

解析:

• 该语句对 sales_data 表中所有在 2022-01-01 之前的销售记录的 sale_amount 进行更新，增加 10% 的销售额。
• 注意，Hive 的更新操作在早期版本中并不支持，通常需要在支持 ACID 的 Hive 版本中使用。

2. DELETE

语法:

DELETE FROM table_name WHERE condition;

示例:

DELETE FROM sales_data WHERE sale_date < '2020-01-01';

解析:

• 该语句删除 sales_data 表中所有在 2020-01-01 之前的记录。
• 这个操作同样需要在支持 ACID 的 Hive 版本中执行。