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
数仓中的数据存在即有意义，一般不会进行更新和删除操作，虽然公司不用但一般都有这些功能，了解即可，反正我没用过
更新和删除操作的条件
-
表必须为分桶表：
- 表格需要使用分桶（Bucketing），这可以提高数据的管理和查询效率。
-
数据存储格式：
- 表格的数据必须存储为 ORC 格式，而不能是文本格式。ORC 格式支持 ACID 操作的事务特性。
-
事务支持：
- 表格必须启用事务支持，确保可以进行 ACID 操作。通常在创建表时设置属性
transactional='true'。
- 表格必须启用事务支持，确保可以进行 ACID 操作。通常在创建表时设置属性
配置 Hive 为 ACID 模式
-
启用并发支持：
SET hive.support.concurrency=true; -
设置事务管理器：
SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; -
启用 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 版本中执行。
