GreenPlum教程FG014-GreenPlum数据加载与外部表实战
本文档风哥主要介绍GreenPlum数据加载与外部表，包括数据加载概念、外部表概念、COPY命令、外部表创建与管理、ETL数据加载案例等内容，风哥教程参考GreenPlum官方文档Administrator Guide、Loading and Unloading Data等内容编写，适合DBA人员在学习和测试中使用。
Part01-基础概念与理论知识
1.1 GreenPlum数据加载概念
数据加载是将外部数据导入GreenPlum数据库的过程。GreenPlum提供了多种高效的数据加载方式。更多视频教程www.fgedu.net.cn
1.1.1 数据加载方式
1. INSERT语句
– 适合小批量数据
– 简单易用
– 性能较低
2. COPY命令
– 适合批量数据加载
– 支持文件导入导出
– 性能较好
3. 外部表
– 支持多种数据源
– 支持并行加载
– 灵活性高
4. gpload工具
– 基于外部表
– 配置文件驱动
– 自动化程度高
5. gpfdist工具
– 高速数据分发
– 支持并行加载
– 性能最佳
1.2 GreenPlum外部表概念
外部表是指向外部数据源的虚拟表，可以直接查询外部数据，也可以将外部数据加载到内部表。学习交流加群风哥微信: itpux-com
1.2.1 外部表类型
1. 可读外部表
– 从外部数据源读取数据
– 支持文件、Web、命令等
– 只读操作
2. 可写外部表
– 将数据写入外部数据源
– 支持文件、Web等
– 用于数据导出
3. 外部表协议
– file：本地文件
– gpfdist：GreenPlum文件分发
– http：HTTP协议
– pxf：Hadoop数据
– s3：S3存储
4. 外部表格式
– TEXT：文本格式
– CSV：CSV格式
– CUSTOM：自定义格式
Part02-生产环境规划与建议
2.1 GreenPlum数据加载最佳实践
- 大批量数据使用COPY或外部表
- 使用gpfdist实现并行加载
- 合理设置数据格式和编码
- 关闭索引和约束提高加载速度
- 加载后重建索引和更新统计信息
Part03-生产环境项目实施方案
3.1 GreenPlum COPY命令实战
3.1.1 COPY导入数据
$ psql -d fgedudb -U fgedu
psql (9.4.26)
Type “help” for help.
fgedudb=>
# 创建目标表
fgedudb=> CREATE TABLE fgedu.fgedu_product_load (
fgedudb(> product_id INT,
fgedudb(> product_name VARCHAR(200),
fgedudb(> category VARCHAR(100),
fgedudb(> price NUMERIC(18,2),
fgedudb(> create_date DATE
fgedudb(> ) DISTRIBUTED BY (product_id);
CREATE TABLE
# 准备数据文件
$ cat > /tmp/fgedu_product.csv << EOF
1,产品A,电子产品,999.99,2024-01-01
2,产品B,家居用品,199.99,2024-01-02
3,产品C,服装,299.99,2024-01-03
4,产品D,食品,99.99,2024-01-04
5,产品E,图书,49.99,2024-01-05
EOF
# 使用COPY导入数据
fgedudb=> COPY fgedu.fgedu_product_load
fgedudb-> FROM ‘/tmp/fgedu_product.csv’
fgedudb-> WITH (FORMAT csv, DELIMITER ‘,’, HEADER false, ENCODING ‘UTF8’);
COPY 5
# 查看导入结果
fgedudb=> SELECT * FROM fgedu.fgedu_product_load;
product_id | product_name | category | price | create_date
————+————–+————+———+————-
1 | 产品A | 电子产品 | 999.99 | 2024-01-01
2 | 产品B | 家居用品 | 199.99 | 2024-01-02
3 | 产品C | 服装 | 299.99 | 2024-01-03
4 | 产品D | 食品 | 99.99 | 2024-01-04
5 | 产品E | 图书 | 49.99 | 2024-01-05
(5 rows)
学习交流加群风哥QQ113257174
3.1.2 COPY导出数据
fgedudb=> COPY fgedu.fgedu_product_load
fgedudb-> TO ‘/tmp/fgedu_product_export.csv’
fgedudb-> WITH (FORMAT csv, DELIMITER ‘,’, HEADER true, ENCODING ‘UTF8’);
COPY 5
# 查看导出文件
$ cat /tmp/fgedu_product_export.csv
product_id,product_name,category,price,create_date
1,产品A,电子产品,999.99,2024-01-01
2,产品B,家居用品,199.99,2024-01-02
3,产品C,服装,299.99,2024-01-03
4,产品D,食品,99.99,2024-01-04
5,产品E,图书,49.99,2024-01-05
更多学习教程公众号风哥教程itpux_com
3.2 GreenPlum外部表实战
3.2.1 创建可读外部表
fgedudb=> CREATE EXTERNAL TABLE fgedu.fgedu_ext_product (
fgedudb(> product_id INT,
fgedudb(> product_name VARCHAR(200),
fgedudb(> category VARCHAR(100),
fgedudb(> price NUMERIC(18,2),
fgedudb(> create_date DATE
fgedudb(> )
fgedudb-> LOCATION (‘file:///tmp/fgedu_product.csv’)
fgedudb-> FORMAT ‘csv’ (DELIMITER ‘,’);
CREATE EXTERNAL TABLE
# 查询外部表
fgedudb=> SELECT * FROM fgedu.fgedu_ext_product;
product_id | product_name | category | price | create_date
————+————–+————+———+————-
1 | 产品A | 电子产品 | 999.99 | 2024-01-01
2 | 产品B | 家居用品 | 199.99 | 2024-01-02
3 | 产品C | 服装 | 299.99 | 2024-01-03
4 | 产品D | 食品 | 99.99 | 2024-01-04
5 | 产品E | 图书 | 49.99 | 2024-01-05
(5 rows)
# 从外部表加载数据到内部表
fgedudb=> INSERT INTO fgedu.fgedu_product_load
fgedudb-> SELECT * FROM fgedu.fgedu_ext_product;
INSERT 0 5
from GreenPlum视频:www.itpux.com
3.2.2 创建可写外部表
fgedudb=> CREATE WRITABLE EXTERNAL TABLE fgedu.fgedu_ext_product_write (
fgedudb(> product_id INT,
fgedudb(> product_name VARCHAR(200),
fgedudb(> category VARCHAR(100),
fgedudb(> price NUMERIC(18,2),
fgedudb(> create_date DATE
fgedudb(> )
fgedudb-> LOCATION (‘file:///tmp/fgedu_product_write.csv’)
fgedudb-> FORMAT ‘csv’ (DELIMITER ‘,’);
CREATE WRITABLE EXTERNAL TABLE
# 向外部表写入数据
fgedudb=> INSERT INTO fgedu.fgedu_ext_product_write
fgedudb-> SELECT * FROM fgedu.fgedu_product_load WHERE price > 100;
INSERT 0 3
# 查看写入的文件
$ cat /tmp/fgedu_product_write.csv
1,产品A,电子产品,999.99,2024-01-01
2,产品B,家居用品,199.99,2024-01-02
3,产品C,服装,299.99,2024-01-03
Part04-生产案例与实战讲解
4.1 GreenPlum ETL数据加载案例
4.1.1 批量数据加载流程
# 1. 创建目标表
fgedudb=> CREATE TABLE fgedu.fgedu_sales_etl (
fgedudb(> sale_id BIGSERIAL,
fgedudb(> sale_date DATE,
fgedudb(> product_id INT,
fgedudb(> quantity INT,
fgedudb(> amount NUMERIC(18,2)
fgedudb(> ) DISTRIBUTED BY (sale_id);
CREATE TABLE
# 2. 准备数据文件
$ cat > /tmp/fgedu_sales.csv << EOF
2024-06-01,1,10,9999.90
2024-06-02,2,20,3999.80
2024-06-03,3,15,4499.85
EOF
# 3. 创建外部表
fgedudb=> CREATE EXTERNAL TABLE fgedu.fgedu_ext_sales (
fgedudb(> sale_date DATE,
fgedudb(> product_id INT,
fgedudb(> quantity INT,
fgedudb(> amount NUMERIC(18,2)
fgedudb(> )
fgedudb-> LOCATION (‘file:///tmp/fgedu_sales.csv’)
fgedudb-> FORMAT ‘csv’ (DELIMITER ‘,’);
CREATE EXTERNAL TABLE
# 4. 加载数据
fgedudb=> INSERT INTO fgedu.fgedu_sales_etl (sale_date, product_id, quantity, amount)
fgedudb-> SELECT sale_date, product_id, quantity, amount
fgedudb-> FROM fgedu.fgedu_ext_sales;
INSERT 0 3
# 5. 验证数据
fgedudb=> SELECT COUNT(*) FROM fgedu.fgedu_sales_etl;
count
——-
3
(1 row)
# 6. 更新统计信息
fgedudb=> ANALYZE fgedu.fgedu_sales_etl;
ANALYZE
Part05-风哥经验总结与分享
5.1 GreenPlum数据加载技巧
1. 性能优化
– 使用gpfdist实现并行加载
– 关闭索引和约束
– 使用批量插入
– 合理设置内存参数
2. 数据格式
– 使用CSV格式
– 设置正确的编码
– 处理特殊字符
– 验证数据质量
3. 错误处理
– 设置错误表
– 记录错误日志
– 处理异常数据
– 验证加载结果
4. 监控与维护
– 监控加载进度
– 检查数据完整性
– 更新统计信息
– 重建索引
5. 最佳实践
– 分批加载大数据
– 使用外部表
– 自动化ETL流程
– 定期清理临时文件
本文档介绍了GreenPlum数据加载与外部表的核心内容，包括COPY命令、外部表创建与管理、ETL数据加载等，希望对大家有所帮助。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
