自学内容网 自学内容网

Hive数仓操作(十六)

DML(数据操作语言)指的是用于操作数据的 SQL 语言部分,主要包括对数据的插入、更新、删除等操作。Hive 的 DML语句主要包括 INSERTUPDATEDELETE 。以下是一些重要的 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_1stu05_2stu05_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_1stu05_2stu05_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 版本中执行。

原文地址:https://blog.csdn.net/m0_58076578/article/details/142718242

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!