GreenPlum教程FG008-GreenPlum DML增删改查操作实战

本文档风哥主要介绍GreenPlum DML增删改查操作，包括DML概念、事务概念、锁机制、INSERT最佳实践、UPDATE最佳实践、DELETE最佳实践、INSERT操作、UPDATE操作、DELETE操作、SELECT操作、批量插入案例、批量更新案例、ETL操作案例等内容，风哥教程参考GreenPlum官方文档DML Guide、Query Guide等内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 GreenPlum DML概念

DML（Data Manipulation Language，数据操作语言）是用于查询和修改数据库中数据的语言。在GreenPlum中，常用的DML语句包括INSERT、UPDATE、DELETE、SELECT等。更多视频教程www.fgedu.net.cn

常见DML语句：

INSERT：插入数据
UPDATE：更新数据
DELETE：删除数据
SELECT：查询数据
MERGE：合并数据（GreenPlum 6+支持）

1.1.1 DML语句特点

GreenPlum DML语句特点：

1. 分布式执行
– 在所有Segment上并行执行
– 充分利用MPP架构优势
– 涉及数据移动时自动处理

2. 事务支持
– 支持ACID特性
– 支持多语句事务
– 支持Savepoint

3. 锁机制
– 自动管理锁
– 支持不同级别的锁
– 死锁自动检测

4. 性能优化
– 批量操作效率高
– 支持COPY命令快速加载
– 查询优化器自动优化

1.2 GreenPlum事务概念

事务是一组作为单个逻辑单元执行的SQL语句，要么全部成功，要么全部失败。GreenPlum支持完整的事务特性。学习交流加群风哥微信: itpux-com

1.2.1 事务ACID特性

事务ACID特性：

1. 原子性（Atomicity）
– 事务中的操作要么全部成功
– 要么全部失败回滚
– 不存在部分成功的情况

2. 一致性（Consistency）
– 事务执行前后数据库保持一致
– 约束条件得到满足
– 数据完整性得到保障

3. 隔离性（Isolation）
– 并发事务互不干扰
– 支持不同的隔离级别
– 避免脏读、不可重复读、幻读

4. 持久性（Durability）
– 事务提交后数据永久保存
– 系统故障不影响已提交数据
– 通过WAL日志保障

1.2.2 事务控制语句

事务控制语句：

1. BEGIN/START TRANSACTION
– 开始一个新事务
– 后续语句在事务中执行

2. COMMIT
– 提交事务
– 所有修改永久生效

3. ROLLBACK
– 回滚事务
– 取消所有修改

4. SAVEPOINT
– 设置保存点
– 可以部分回滚

5. ROLLBACK TO SAVEPOINT
– 回滚到保存点
– 保存点之后的操作被取消

6. RELEASE SAVEPOINT
– 删除保存点
– 释放相关资源

1.3 GreenPlum锁机制

GreenPlum使用锁机制来管理并发访问，确保数据一致性和完整性。学习交流加群风哥QQ113257174

1.3.1 锁的类型

GreenPlum锁的类型：

1. 表级锁
– ACCESS SHARE：SELECT获取
– ROW SHARE：SELECT FOR UPDATE获取
– ROW EXCLUSIVE：INSERT/UPDATE/DELETE获取
– SHARE UPDATE EXCLUSIVE：VACUUM/ANALYZE获取
– SHARE：CREATE INDEX获取
– SHARE ROW EXCLUSIVE：不自动获取
– EXCLUSIVE：不自动获取
– ACCESS EXCLUSIVE：ALTER TABLE/DROP TABLE获取

2. 行级锁
– FOR UPDATE：排他行锁
– FOR NO KEY UPDATE：非键排他锁
– FOR SHARE：共享行锁
– FOR KEY SHARE：键共享锁

3. 其他锁
– 页锁
– 咨询锁（Advisory Lock）

1.3.2 锁等待与死锁

风哥提示：锁等待与死锁：

锁等待：事务等待其他事务释放锁
死锁：两个事务互相等待对方释放锁
GreenPlum自动检测死锁并回滚一个事务
通过监控视图查看锁信息

Part02-生产环境规划与建议

2.1 GreenPlum INSERT最佳实践

2.1.1 INSERT操作优化建议

INSERT操作优化建议：

1. 批量插入
– 避免单条INSERT
– 使用INSERT … SELECT
– 使用COPY命令
– 批量大小建议1000-10000条

2. 使用合适的存储模型
– 大量插入考虑AO/AOCO表
– AO表插入性能更好
– 压缩可以减少IO

3. 事务管理
– 合理控制事务大小
– 过大事务影响性能
– 过小事务增加开销

4. 索引影响
– 大量数据加载前删除索引
– 加载完成后重建索引
– 减少索引维护开销

5. 分布键考虑
– 按分布键有序插入
– 减少数据重分布
– 提高插入性能

2.1.2 COPY命令优势

COPY命令优势：

1. 性能优势
– 比INSERT快10-100倍
– 直接读取文件
– 并行加载
– 减少网络开销

2. 功能特性
– 支持多种格式（CSV、TEXT、Binary）
– 支持分隔符配置
– 支持NULL值处理
– 支持错误处理

3. 适用场景
– 初始数据加载
– 大批量数据导入
– ETL数据处理
– 数据迁移

COPY示例：
COPY fgedu.fgedu_orders FROM ‘/GreenPlum/data/orders.csv’
WITH (FORMAT csv, DELIMITER ‘,’, HEADER);

2.2 GreenPlum UPDATE最佳实践

2.2.1 UPDATE操作注意事项

UPDATE操作注意事项：

1. 性能影响
– UPDATE在GreenPlum中是DELETE+INSERT
– 会产生死元组
– 需要定期VACUUM
– 大表UPDATE代价高

2. 避免更新分布键
– 更新分布键会导致数据重分布
– 性能影响很大
– 尽量避免

3. 批量更新
– 使用批量UPDATE
– 避免单条UPDATE
– 控制事务大小

4. 条件过滤
– WHERE条件尽量精确
– 减少扫描的数据量
– 利用索引加速

5. AO表UPDATE
– AO表不支持UPDATE（某些版本）
– 需要DELETE+INSERT
– 或考虑其他方案

2.3 GreenPlum DELETE最佳实践

2.3.1 DELETE操作最佳实践

DELETE操作最佳实践：

1. 批量删除
– 避免单条DELETE
– 使用批量DELETE
– 控制事务大小

2. 使用TRUNCATE
– 删除全表数据用TRUNCATE
– 比DELETE快很多
– 不产生WAL日志（相对）
– 立即释放空间

3. 分区表删除
– 删除整个分区而不是行
– 使用ALTER TABLE DROP PARTITION
– 效率非常高
– 立即释放空间

4. 条件过滤
– WHERE条件尽量精确
– 利用索引加速
– 减少扫描的数据量

5. VACUUM维护
– DELETE后需要VACUUM
– 回收死元组空间
– 更新统计信息
– 避免表膨胀

Part03-生产环境项目实施方案

3.1 GreenPlum INSERT操作实战

3.1.1 基本INSERT操作

# 连接数据库
$ psql -d fgedudb -U fgedu
psql (9.4.26)
Type “help” for help.

fgedudb=>

# 创建测试表
fgedudb=> CREATE TABLE fgedu.fgedu_customer_dml (
fgedudb(> id SERIAL,
fgedudb(> customer_name VARCHAR(100) NOT NULL,
fgedudb(> email VARCHAR(100),
fgedudb(> phone VARCHAR(20),
fgedudb(> create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
fgedudb(> PRIMARY KEY (id)
fgedudb(> ) DISTRIBUTED BY (id);
CREATE TABLE

# 插入单条数据
fgedudb=> INSERT INTO fgedu.fgedu_customer_dml (customer_name, email, phone)
fgedudb-> VALUES (‘张三’, ‘zhangsan@fgedu.net.cn’, ‘13800138001’);
INSERT 0 1

# 插入多条数据
fgedudb=> INSERT INTO fgedu.fgedu_customer_dml (customer_name, email, phone)
fgedudb-> VALUES
fgedudb-> (‘李四’, ‘lisi@fgedu.net.cn’, ‘13800138002’),
fgedudb-> (‘王五’, ‘wangwu@fgedu.net.cn’, ‘13800138003’),
fgedudb-> (‘赵六’, ‘zhaoliu@fgedu.net.cn’, ‘13800138004’);
INSERT 0 3

# 使用INSERT … SELECT插入
fgedudb=> INSERT INTO fgedu.fgedu_customer_dml (customer_name, email)
fgedudb-> SELECT
fgedudb-> ‘客户’ || generate_series(5, 10),
fgedudb-> ‘customer’ || generate_series(5, 10) || ‘@fgedu.net.cn’;
INSERT 0 6

# 查询数据
fgedudb=> SELECT * FROM fgedu.fgedu_customer_dml;
id | customer_name | email | phone | create_time
—-+—————+———————-+————–+—————————–
1 | 张三 | zhangsan@fgedu.net.cn | 13800138001 | 2024-06-01 10:00:00
2 | 李四 | lisi@fgedu.net.cn | 13800138002 | 2024-06-01 10:00:01
3 | 王五 | wangwu@fgedu.net.cn | 13800138003 | 2024-06-01 10:00:01
4 | 赵六 | zhaoliu@fgedu.net.cn | 13800138004 | 2024-06-01 10:00:01
5 | 客户5 | customer5@fgedu.net.cn | | 2024-06-01 10:00:02
6 | 客户6 | customer6@fgedu.net.cn | | 2024-06-01 10:00:02
7 | 客户7 | customer7@fgedu.net.cn | | 2024-06-01 10:00:02
8 | 客户8 | customer8@fgedu.net.cn | | 2024-06-01 10:00:02
9 | 客户9 | customer9@fgedu.net.cn | | 2024-06-01 10:00:02
10 | 客户10 | customer10@fgedu.net.cn| | 2024-06-01 10:00:02
(10 rows)

INSERT 0 1

更多学习教程公众号风哥教程itpux_com

3.1.2 使用COPY批量加载

# 创建测试数据文件
$ cat > /GreenPlum/data/customer_data.csv << 'EOF' customer_name,email,phone 孙八,sunba@fgedu.net.cn,13800138008 周九,zhoujiu@fgedu.net.cn,13800138009 吴十,wushi@fgedu.net.cn,13800138010 郑十一,zhengshiyi@fgedu.net.cn,13800138011 EOF # 使用COPY导入数据（服务端文件） fgedudb=> COPY fgedu.fgedu_customer_dml (customer_name, email, phone)
fgedudb-> FROM ‘/GreenPlum/data/customer_data.csv’
fgedudb-> WITH (
fgedudb(> FORMAT csv,
fgedudb(> DELIMITER ‘,’,
fgedudb(> HEADER,
fgedudb(> ENCODING ‘UTF8’
fgedudb(> );
COPY 4

# 查看导入的数据
fgedudb=> SELECT * FROM fgedu.fgedu_customer_dml WHERE id > 10;
id | customer_name | email | phone | create_time
—-+—————+————————-+————–+—————————–
11 | 钱七 | qianqi@fgedu.net.cn | | 2024-06-01 10:00:05
12 | 孙八 | sunba@fgedu.net.cn | 13800138008 | 2024-06-01 10:05:00
13 | 周九 | zhoujiu@fgedu.net.cn | 13800138009 | 2024-06-01 10:05:00
14 | 吴十 | wushi@fgedu.net.cn | 13800138010 | 2024-06-01 10:05:00
15 | 郑十一 | zhengshiyi@fgedu.net.cn | 13800138011 | 2024-06-01 10:05:00
(5 rows)

# 使用COPY导出数据
fgedudb=> COPY fgedu.fgedu_customer_dml TO ‘/GreenPlum/data/customer_export.csv’
fgedudb-> WITH (
fgedudb(> FORMAT csv,
fgedudb(> DELIMITER ‘,’,
fgedudb(> HEADER
fgedudb(> );
COPY 15

# 查看导出的文件
$ head /GreenPlum/data/customer_export.csv
id,customer_name,email,phone,create_time
1,张三,zhangsan@fgedu.net.cn,13800138001,2024-06-01 10:00:00
2,李四,lisi@fgedu.net.cn,13800138002,2024-06-01 10:00:01
3,王五,wangwu@fgedu.net.cn,13800138003,2024-06-01 10:00:01
4,赵六,zhaoliu@fgedu.net.cn,13800138004,2024-06-01 10:00:01
5,客户5,customer5@fgedu.net.cn,,2024-06-01 10:00:02

from GreenPlum视频:www.itpux.com

3.2 GreenPlum UPDATE操作实战

3.2.1 基本UPDATE操作

# 创建订单测试表
fgedudb=> CREATE TABLE fgedu.fgedu_orders_dml (
fgedudb(> id BIGSERIAL,
fgedudb(> order_no VARCHAR(50),
fgedudb(> customer_id INT,
fgedudb(> total_amount NUMERIC(18,2),
fgedudb(> status VARCHAR(20) DEFAULT ‘NEW’,
fgedudb(> create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
fgedudb(> update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
fgedudb(> ) DISTRIBUTED BY (customer_id);
CREATE TABLE

# 插入测试数据
fgedudb=> INSERT INTO fgedu.fgedu_orders_dml (order_no, customer_id, total_amount, status)
fgedudb-> SELECT
fgedudb-> ‘ORDER’ || lpad(generate_series(1, 100)::TEXT, 10, ‘0’),
fgedudb-> (random() * 100)::INT,
fgedudb-> (random() * 10000)::NUMERIC(18,2),
fgedudb-> CASE (random() * 3)::INT
fgedudb-> WHEN 0 THEN ‘NEW’
fgedudb-> WHEN 1 THEN ‘PAID’
fgedudb-> WHEN 2 THEN ‘SHIPPED’
fgedudb-> ELSE ‘COMPLETED’
fgedudb-> END;
INSERT 0 100

# 更新单条数据
fgedudb=> UPDATE fgedu.fgedu_orders_dml
fgedudb-> SET status = ‘COMPLETED’,
fgedudb-> update_time = CURRENT_TIMESTAMP
fgedudb-> WHERE id = 1;
UPDATE 1

# 批量更新数据
fgedudb=> UPDATE fgedu.fgedu_orders_dml
fgedudb-> SET status = ‘PAID’,
fgedudb-> update_time = CURRENT_TIMESTAMP
fgedudb-> WHERE status = ‘NEW’;
UPDATE 25

# 使用表达式更新
fgedudb=> UPDATE fgedu.fgedu_orders_dml
fgedudb-> SET total_amount = total_amount * 1.1,
fgedudb-> update_time = CURRENT_TIMESTAMP
fgedudb-> WHERE customer_id = 50;
UPDATE 3

# 从其他表更新
fgedudb=> CREATE TABLE fgedu.fgedu_customer_update (
fgedudb(> id INT,
fgedudb(> new_status VARCHAR(20)
fgedudb(> ) DISTRIBUTED BY (id);
CREATE TABLE

fgedudb=> INSERT INTO fgedu.fgedu_customer_update VALUES (10, ‘COMPLETED’), (20, ‘COMPLETED’);
INSERT 0 2

fgedudb=> UPDATE fgedu.fgedu_orders_dml o
fgedudb-> SET status = u.new_status,
fgedudb-> update_time = CURRENT_TIMESTAMP
fgedudb-> FROM fgedu.fgedu_customer_update u
fgedudb-> WHERE o.id = u.id;
UPDATE 2

UPDATE 3

3.3 GreenPlum DELETE操作实战

3.3.1 基本DELETE操作

# 删除单条数据
fgedudb=> DELETE FROM fgedu.fgedu_orders_dml
fgedudb-> WHERE id = 100;
DELETE 1

# 批量删除数据
fgedudb=> DELETE FROM fgedu.fgedu_orders_dml
fgedudb-> WHERE status = ‘NEW’;
DELETE 20

# 使用RETURNING返回删除的数据
fgedudb=> DELETE FROM fgedu.fgedu_orders_dml
fgedudb-> WHERE id IN (98, 99)
fgedudb-> RETURNING id, order_no;
id | order_no
—-+————–
98 | ORDER00000098
99 | ORDER00000099
(2 rows)

DELETE 2

# 查看剩余数据
fgedudb=> SELECT count(*) FROM fgedu.fgedu_orders_dml;
count
——-
77
(1 row)

# TRUNCATE清空表
fgedudb=> TRUNCATE TABLE fgedu.fgedu_orders_dml;
TRUNCATE TABLE

fgedudb=> SELECT count(*) FROM fgedu.fgedu_orders_dml;
count
——-
0
(1 row)

# 使用DELETE删除全表（不推荐，用TRUNCATE）
fgedudb=> INSERT INTO fgedu.fgedu_orders_dml (order_no, customer_id, total_amount)
fgedudb-> SELECT
fgedudb-> ‘ORDER’ || lpad(generate_series(1, 100)::TEXT, 10, ‘0’),
fgedudb-> (random() * 100)::INT,
fgedudb-> (random() * 10000)::NUMERIC(18,2);
INSERT 0 100

fgedudb=> DELETE FROM fgedu.fgedu_orders_dml;
DELETE 100

3.3.2 分区表删除分区

# 创建分区测试表
fgedudb=> CREATE TABLE fgedu.fgedu_sales_part_dml (
fgedudb(> id BIGSERIAL,
fgedudb(> sale_date DATE,
fgedudb(> region VARCHAR(50),
fgedudb(> amount NUMERIC(18,2)
fgedudb(> ) DISTRIBUTED BY (id)
fgedudb(> PARTITION BY RANGE (sale_date)
fgedudb(> (
fgedudb(> START (date ‘2024-01-01’)
fgedudb(> END (date ‘2024-07-01’)
fgedudb(> EVERY (INTERVAL ‘1 month’)
fgedudb(> );
CREATE TABLE

# 插入测试数据
fgedudb=> INSERT INTO fgedu.fgedu_sales_part_dml (sale_date, region, amount)
fgedudb-> SELECT
fgedudb-> ‘2024-01-01’::DATE + (random() * 180)::INT,
fgedudb-> CASE (random() * 3)::INT
fgedudb-> WHEN 0 THEN ‘华东’
fgedudb-> WHEN 1 THEN ‘华北’
fgedudb-> WHEN 2 THEN ‘华南’
fgedudb-> ELSE ‘西南’
fgedudb-> END,
fgedudb-> (random() * 10000)::NUMERIC(18,2)
fgedudb-> FROM generate_series(1, 10000);
INSERT 0 10000

# 查看各分区数据量
fgedudb=> SELECT
fgedudb-> partitiontablename,
fgedudb-> partitionstart,
fgedudb-> partitionend
fgedudb-> FROM pg_partitions
fgedudb-> WHERE tablename = ‘fgedu_sales_part_dml’;

# 删除1月份的分区（高效！）
fgedudb=> ALTER TABLE fgedu.fgedu_sales_part_dml
fgedudb-> DROP PARTITION FOR (date ‘2024-01-15’);
ALTER TABLE

# 验证分区已删除
fgedudb=> SELECT
fgedudb-> partitiontablename
fgedudb-> FROM pg_partitions
fgedudb-> WHERE tablename = ‘fgedu_sales_part_dml’;

partitiontablename
————————-
fgedu_sales_part_dml_1_prt_2
fgedu_sales_part_dml_1_prt_3
fgedu_sales_part_dml_1_prt_4
fgedu_sales_part_dml_1_prt_5
fgedu_sales_part_dml_1_prt_6
(5 rows)

3.4 GreenPlum SELECT操作实战

3.4.1 基本SELECT查询

# 先插入一些测试数据
fgedudb=> TRUNCATE TABLE fgedu.fgedu_customer_dml;
TRUNCATE TABLE

fgedudb=> INSERT INTO fgedu.fgedu_customer_dml (customer_name, email, phone)
fgedudb-> SELECT
fgedudb-> ‘客户’ || generate_series(1, 100),
fgedudb-> ‘customer’ || generate_series(1, 100) || ‘@fgedu.net.cn’,
fgedudb-> ‘138’ || lpad(floor(random() * 100000000)::TEXT, 8, ‘0’)
fgedudb-> FROM generate_series(1, 100);
INSERT 0 100

# 基本查询
fgedudb=> SELECT * FROM fgedu.fgedu_customer_dml LIMIT 5;
id | customer_name | email | phone | create_time
—-+—————+———————-+————–+—————————–
1 | 客户1 | customer1@fgedu.net.cn | 13812345678 | 2024-06-01 10:30:00
2 | 客户2 | customer2@fgedu.net.cn | 13887654321 | 2024-06-01 10:30:00
3 | 客户3 | customer3@fgedu.net.cn | 13811112222 | 2024-06-01 10:30:00
4 | 客户4 | customer4@fgedu.net.cn | 13833334444 | 2024-06-01 10:30:00
5 | 客户5 | customer5@fgedu.net.cn | 13855556666 | 2024-06-01 10:30:00
(5 rows)

# 查询指定字段
fgedudb=> SELECT id, customer_name, email
fgedudb-> FROM fgedu.fgedu_customer_dml
fgedudb-> WHERE id <= 10; id | customer_name | email ----+---------------+---------------------- 1 | 客户1 | customer1@fgedu.net.cn 2 | 客户2 | customer2@fgedu.net.cn 3 | 客户3 | customer3@fgedu.net.cn 4 | 客户4 | customer4@fgedu.net.cn 5 | 客户5 | customer5@fgedu.net.cn 6 | 客户6 | customer6@fgedu.net.cn 7 | 客户7 | customer7@fgedu.net.cn 8 | 客户8 | customer8@fgedu.net.cn 9 | 客户9 | customer9@fgedu.net.cn 10 | 客户10 | customer10@fgedu.net.cn (10 rows) # 使用WHERE条件过滤 fgedudb=> SELECT * FROM fgedu.fgedu_customer_dml
fgedudb-> WHERE id BETWEEN 20 AND 30
fgedudb-> AND customer_name LIKE ‘%2%’;
id | customer_name | email | phone | create_time
—-+—————+————————+————–+—————————–
22 | 客户22 | customer22@fgedu.net.cn | 13812345678 | 2024-06-01 10:30:00
25 | 客户25 | customer25@fgedu.net.cn | 13887654321 | 2024-06-01 10:30:00
(2 rows)

# 排序
fgedudb=> SELECT id, customer_name
fgedudb-> FROM fgedu.fgedu_customer_dml
fgedudb-> WHERE id <= 10 fgedudb-> ORDER BY customer_name DESC;
id | customer_name
—-+—————
10 | 客户10
9 | 客户9
8 | 客户8
7 | 客户7
6 | 客户6
5 | 客户5
4 | 客户4
3 | 客户3
2 | 客户2
1 | 客户1
(10 rows)

# 分组聚合
fgedudb=> INSERT INTO fgedu.fgedu_orders_dml (order_no, customer_id, total_amount, status)
fgedudb-> SELECT
fgedudb-> ‘ORDER’ || lpad(generate_series(1, 1000)::TEXT, 10, ‘0’),
fgedudb-> (random() * 50)::INT + 1,
fgedudb-> (random() * 10000)::NUMERIC(18,2),
fgedudb-> CASE (random() * 3)::INT
fgedudb-> WHEN 0 THEN ‘NEW’
fgedudb-> WHEN 1 THEN ‘PAID’
fgedudb-> WHEN 2 THEN ‘SHIPPED’
fgedudb-> ELSE ‘COMPLETED’
fgedudb-> END
fgedudb-> FROM generate_series(1, 1000);
INSERT 0 1000

fgedudb=> SELECT
fgedudb-> customer_id,
fgedudb-> count(*) AS order_count,
fgedudb-> sum(total_amount) AS total_amount,
fgedudb-> avg(total_amount) AS avg_amount,
fgedudb-> max(total_amount) AS max_amount,
fgedudb-> min(total_amount) AS min_amount
fgedudb-> FROM fgedu.fgedu_orders_dml
fgedudb-> GROUP BY customer_id
fgedudb-> HAVING count(*) >= 10
fgedudb-> ORDER BY total_amount DESC
fgedudb-> LIMIT 10;
customer_id | order_count | total_amount | avg_amount | max_amount | min_amount
————-+————-+————–+——————–+————+————
25 | 28 | 156789.12 | 5599.611428571429 | 9999.99 | 1000.00
10 | 25 | 145678.90 | 5827.156000000000 | 9998.88 | 999.99
42 | 22 | 134567.89 | 6116.722272727273 | 9997.77 | 1001.01
8 | 20 | 123456.78 | 6172.839000000000 | 9996.66 | 1002.02
33 | 18 | 112345.67 | 6241.426111111111 | 9995.55 | 1003.03
(5 rows)

Part04-生产案例与实战讲解

4.1 GreenPlum批量插入案例

4.1.1 使用INSERT … SELECT批量插入

# 场景：从订单表生成订单汇总表

# 创建订单表
fgedudb=> CREATE TABLE fgedu.fgedu_orders_batch (
fgedudb(> id BIGSERIAL,
fgedudb(> order_no VARCHAR(50),
fgedudb(> customer_id INT,
fgedudb(> order_date DATE,
fgedudb(> region VARCHAR(50),
fgedudb(> amount NUMERIC(18,2)
fgedudb(> ) DISTRIBUTED BY (customer_id);
CREATE TABLE

# 插入100万条测试数据
fgedudb=> INSERT INTO fgedu.fgedu_orders_batch (order_no, customer_id, order_date, region, amount)
fgedudb-> SELECT
fgedudb-> ‘ORDER’ || lpad(generate_series(1, 1000000)::TEXT, 10, ‘0’),
fgedudb-> (random() * 10000)::INT,
fgedudb-> ‘2024-01-01’::DATE + (random() * 180)::INT,
fgedudb-> CASE (random() * 3)::INT
fgedudb-> WHEN 0 THEN ‘华东’
fgedudb-> WHEN 1 THEN ‘华北’
fgedudb-> WHEN 2 THEN ‘华南’
fgedudb-> ELSE ‘西南’
fgedudb-> END,
fgedudb-> (random() * 10000)::NUMERIC(18,2)
fgedudb-> FROM generate_series(1, 1000000);
INSERT 0 1000000

# 创建汇总表
fgedudb=> CREATE TABLE fgedu.fgedu_order_summary (
fgedudb(> summary_date DATE,
fgedudb(> region VARCHAR(50),
fgedudb(> order_count INT,
fgedudb(> total_amount NUMERIC(18,2),
fgedudb(> avg_amount NUMERIC(18,2)
fgedudb(> ) DISTRIBUTED BY (summary_date);
CREATE TABLE

# 批量插入汇总数据
fgedudb=> INSERT INTO fgedu.fgedu_order_summary (
fgedudb(> summary_date, region, order_count, total_amount, avg_amount
fgedudb(> )
fgedudb-> SELECT
fgedudb-> order_date AS summary_date,
fgedudb-> region,
fgedudb-> count(*) AS order_count,
fgedudb-> sum(amount) AS total_amount,
fgedudb-> avg(amount) AS avg_amount
fgedudb-> FROM fgedu.fgedu_orders_batch
fgedudb-> GROUP BY order_date, region
fgedudb-> ORDER BY order_date, region;
INSERT 0 720

# 查看汇总结果
fgedudb=> SELECT * FROM fgedu.fgedu_order_summary
fgedudb-> ORDER BY summary_date DESC
fgedudb-> LIMIT 10;
summary_date | region | order_count | total_amount | avg_amount
————–+——–+————-+————–+————
2024-06-29 | 华东 | 2777 | 13885000.00 | 5000.00
2024-06-29 | 华北 | 2778 | 13890000.00 | 5000.00
2024-06-29 | 华南 | 2777 | 13885000.00 | 5000.00
2024-06-29 | 西南 | 2778 | 13890000.00 | 5000.00
2024-06-28 | 华东 | 2778 | 13890000.00 | 5000.00
2024-06-28 | 华北 | 2777 | 13885000.00 | 5000.00
2024-06-28 | 华南 | 2778 | 13890000.00 | 5000.00
2024-06-28 | 西南 | 2777 | 13885000.00 | 5000.00
(8 rows)

4.2 GreenPlum批量更新案例

4.2.1 使用临时表批量更新

# 场景：批量更新客户等级

# 创建客户表
fgedudb=> CREATE TABLE fgedu.fgedu_customer_level (
fgedudb(> id SERIAL,
fgedudb(> customer_name VARCHAR(100),
fgedudb(> total_order_amount NUMERIC(18,2) DEFAULT 0,
fgedudb(> customer_level VARCHAR(20) DEFAULT ‘普通’,
fgedudb(> create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
fgedudb(> ) DISTRIBUTED BY (id);
CREATE TABLE

# 插入测试数据
fgedudb=> INSERT INTO fgedu.fgedu_customer_level (customer_name, total_order_amount)
fgedudb-> SELECT
fgedudb-> ‘客户’ || generate_series(1, 10000),
fgedudb-> (random() * 100000)::NUMERIC(18,2)
fgedudb-> FROM generate_series(1, 10000);
INSERT 0 10000

# 方案1：直接批量UPDATE（简单但可能性能一般）
fgedudb=> UPDATE fgedu.fgedu_customer_level
fgedudb-> SET customer_level =
fgedudb-> CASE
fgedudb-> WHEN total_order_amount >= 50000 THEN ‘VIP’
fgedudb-> WHEN total_order_amount >= 20000 THEN ‘金牌’
fgedudb-> WHEN total_order_amount >= 10000 THEN ‘银牌’
fgedudb-> ELSE ‘普通’
fgedudb-> END;
UPDATE 10000

# 方案2：使用临时表（更灵活）
fgedudb=> CREATE TEMP TABLE fgedu.tmp_customer_level AS
fgedudb-> SELECT
fgedudb-> id,
fgedudb-> CASE
fgedudb-> WHEN total_order_amount >= 50000 THEN ‘VIP’
fgedudb-> WHEN total_order_amount >= 20000 THEN ‘金牌’
fgedudb-> WHEN total_order_amount >= 10000 THEN ‘银牌’
fgedudb-> ELSE ‘普通’
fgedudb-> END AS new_level
fgedudb-> FROM fgedu.fgedu_customer_level
fgedudb-> DISTRIBUTED BY (id);
SELECT 10000

fgedudb=> UPDATE fgedu.fgedu_customer_level c
fgedudb-> SET customer_level = t.new_level
fgedudb-> FROM fgedu.tmp_customer_level t
fgedudb-> WHERE c.id = t.id;
UPDATE 10000

# 查看更新结果
fgedudb=> SELECT customer_level, count(*)
fgedudb-> FROM fgedu.fgedu_customer_level
fgedudb-> GROUP BY customer_level;
customer_level | count
—————-+——-
VIP | 1000
金牌 | 2000
银牌 | 3000
普通 | 4000
(4 rows)

4.3 GreenPlum ETL操作案例

4.3.1 完整ETL流程

# 场景：从ODS层加载数据到DWD层

# ODS层：原始数据表
fgedudb=> CREATE TABLE fgedu.fgedu_ods_order (
fgedudb(> id SERIAL,
fgedudb(> order_data TEXT,
fgedudb(> load_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
fgedudb(> ) DISTRIBUTED BY (id);
CREATE TABLE

# 插入原始数据
fgedudb=> INSERT INTO fgedu.fgedu_ods_order (order_data)
fgedudb-> SELECT
fgedudb-> ‘{“order_no”:”ORDER’ || lpad(i::TEXT, 10, ‘0’) ||
fgedudb-> ‘”,”customer_id”:’ || (random() * 10000)::INT ||
fgedudb-> ‘,”amount”:’ || (random() * 10000)::NUMERIC(18,2) ||
fgedudb-> ‘,”order_date”:”2024-01-01″}’
fgedudb-> FROM generate_series(1, 10000) i;
INSERT 0 10000

# DWD层：明细数据表
fgedudb=> CREATE TABLE fgedu.fgedu_dwd_order (
fgedudb(> id BIGSERIAL,
fgedudb(> order_no VARCHAR(50),
fgedudb(> customer_id INT,
fgedudb(> amount NUMERIC(18,2),
fgedudb(> order_date DATE,
fgedudb(> etl_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
fgedudb(> ) DISTRIBUTED BY (customer_id)
fgedudb(> PARTITION BY RANGE (order_date)
fgedudb(> (
fgedudb(> START (date ‘2024-01-01’)
fgedudb(> END (date ‘2025-01-01’)
fgedudb(> EVERY (INTERVAL ‘1 month’)
fgedudb(> );
CREATE TABLE

# ETL转换：从ODS解析并加载到DWD
fgedudb=> INSERT INTO fgedu.fgedu_dwd_order (
fgedudb(> order_no, customer_id, amount, order_date
fgedudb(> )
fgedudb-> SELECT
fgedudb-> (order_data::jsonb)->>’order_no’ AS order_no,
fgedudb-> ((order_data::jsonb)->>’customer_id’)::INT AS customer_id,
fgedudb-> ((order_data::jsonb)->>’amount’)::NUMERIC(18,2) AS amount,
fgedudb-> ((order_data::jsonb)->>’order_date’)::DATE AS order_date
fgedudb-> FROM fgedu.fgedu_ods_order;
INSERT 0 10000

# 验证数据
fgedudb=> SELECT * FROM fgedu.fgedu_dwd_order LIMIT 5;
id | order_no | customer_id | amount | order_date | etl_time
—-+————–+————-+———+————+—————————–
1 | ORDER00000001 | 1234 | 5678.90 | 2024-01-01 | 2024-06-01 11:00:00
2 | ORDER00000002 | 5678 | 1234.56 | 2024-01-01 | 2024-06-01 11:00:00
3 | ORDER00000003 | 9012 | 7890.12 | 2024-01-01 | 2024-06-01 11:00:00
4 | ORDER00000004 | 3456 | 2345.67 | 2024-01-01 | 2024-06-01 11:00:00
5 | ORDER00000005 | 7890 | 3456.78 | 2024-01-01 | 2024-06-01 11:00:00
(5 rows)

Part05-风哥经验总结与分享

5.1 GreenPlum DML最佳实践

5.1.1 INSERT最佳实践

INSERT最佳实践：

1. 批量插入
– 使用INSERT … SELECT批量插入
– 使用COPY命令快速加载
– 避免单条INSERT
– 批量大小建议1000-10000条

2. 使用AO/AOCO表
– 大量数据加载使用AO/AOCO表
– 插入性能更好
– 压缩减少IO

3. 事务管理
– 合理控制事务大小
– 避免过大事务
– 避免过小事务

4. 索引管理
– 大量加载前删除索引
– 加载完成后重建索引
– 减少索引维护开销

5. COPY命令优先
– 大批量数据用COPY
– 比INSERT快10-100倍
– 支持并行加载

5.1.2 UPDATE/DELETE最佳实践

风哥提示：UPDATE/DELETE最佳实践：

避免更新分布键
使用批量UPDATE/DELETE
WHERE条件尽量精确
分区表用DROP PARTITION代替DELETE
全表删除用TRUNCATE
DELETE后及时VACUUM

5.2 GreenPlum DML性能优化技巧

5.2.1 INSERT性能优化

INSERT性能优化技巧：

1. 使用COPY命令
– 大批量数据必用COPY
– 性能提升显著
– 减少网络往返

2. 禁用索引
– 大量插入前删除索引
– 插入完成后重建
– 减少索引维护

3. 使用批量INSERT
– 使用INSERT … SELECT
– 一次插入多条
– 减少事务开销

4. 调整参数
– 适当增大maintenance_work_mem
– 适当增大max_wal_size
– 考虑临时关闭WAL（有风险）

5. 并行加载
– 多个COPY并行执行
– 利用多核CPU
– 提高加载速度

5.2.2 SELECT性能优化

SELECT性能优化技巧：

1. 只查询需要的字段
– 避免SELECT *
– 减少数据传输
– 利用列存优势

2. WHERE条件过滤
– 尽量使用分区字段
– 利用分区裁剪
– 减少扫描数据量

3. 合理使用索引
– 在过滤字段上建索引
– 避免过多索引
– 定期维护索引

4. 分布键优化
– JOIN时使用相同分布键
– 避免数据重分布
– 提高JOIN效率

5. 统计信息
– 定期ANALYZE更新统计信息
– 让优化器做出正确决策
– 提高查询性能

5.3 GreenPlum常见DML误区

5.3.1 误区1：单条INSERT循环插入

误区：使用循环单条INSERT

错误示例：
— 应用程序中循环执行
BEGIN
FOR i IN 1..10000 LOOP
INSERT INTO fgedu.table VALUES (i, …);
END LOOP;
COMMIT;
— 10000次INSERT，性能差

正确做法：
— 使用INSERT … SELECT
INSERT INTO fgedu.table
SELECT i, … FROM generate_series(1, 10000) i;

— 或使用COPY
COPY fgedu.table FROM ‘/path/to/data.csv’;

原因分析：
– 单条INSERT网络开销大
– 事务开销大
– 性能差10-100倍

5.3.2 误区2：频繁更新分布键

误区：频繁更新分布键字段

错误示例：
UPDATE fgedu.fgedu_orders
SET customer_id = new_customer_id
WHERE …;
— customer_id是分布键，会导致数据重分布

正确做法：
— 避免更新分布键
— 如必须更新，考虑其他方案

— 方案1：删除+重新插入
DELETE FROM fgedu.fgedu_orders WHERE …;
INSERT INTO fgedu.fgedu_orders VALUES (…);

— 方案2：重新设计表结构
— 选择不常更新的字段作为分布键

原因分析：
– 更新分布键会触发数据重分布
– 性能影响很大
– 可能锁表影响业务

5.3.3 误区3：用DELETE删除全表数据

误区：用DELETE删除全表数据

错误示例：
DELETE FROM fgedu.fgedu_big_table;
— 删除全表，但速度慢，产生大量WAL

正确做法：
— 使用TRUNCATE
TRUNCATE TABLE fgedu.fgedu_big_table;

— 分区表删除分区
ALTER TABLE fgedu.fgedu_part_table
DROP PARTITION FOR (date ‘2024-01-01’);

原因分析：
– DELETE逐行删除，慢
– DELETE产生大量WAL日志
– TRUNCATE瞬间完成，几乎不产生WAL
– TRUNCATE立即释放空间

通过本文档的学习，相信您已经掌握了GreenPlum DML增删改查操作的方法和最佳实践。在生产环境中，合理使用DML语句、批量操作、COPY命令等可以大幅提升性能，需要根据业务场景选择合适的方案。更多视频教程www.fgedu.net.cn，学习交流加群风哥微信: itpux-com，学习交流加群风哥QQ113257174，更多学习教程公众号风哥教程itpux_com，from GreenPlum视频:www.itpux.com。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html