GreenPlum教程FG007-GreenPlum DDL与数据类型实战

本文档风哥主要介绍GreenPlum DDL与数据类型，包括DDL概念、数据类型概述、命名规范、数据类型选择原则、表设计原则、Schema设计原则、表DDL操作、索引DDL操作、视图DDL操作、数据类型使用案例、表结构变更案例、批量DDL操作案例等内容，风哥教程参考GreenPlum官方文档DDL Guide、Data Types等内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 GreenPlum DDL概念

DDL（Data Definition Language，数据定义语言）是用于定义和管理数据库对象结构的语言。在GreenPlum中，DDL语句用于创建、修改、删除数据库、表、索引、视图、Schema等对象。更多视频教程www.fgedu.net.cn

常见DDL语句：

CREATE：创建数据库对象
ALTER：修改数据库对象
DROP：删除数据库对象
TRUNCATE：清空表数据
COMMENT：添加注释

1.1.1 DDL语句特点

GreenPlum DDL语句特点：

1. 自动提交
– DDL语句执行后自动提交
– 不能回滚
– 需要谨慎操作

2. 分布式执行
– DDL在Master和所有Segment同时执行
– 保证元数据一致性
– 涉及数据重分布时需要注意

3. 锁机制
– DDL操作通常会获取排他锁
– 会阻塞其他DDL和DML操作
– 生产环境需选择合适时机执行

4. 性能影响
– ALTER TABLE可能需要数据重分布
– DROP TABLE删除数据快但不可恢复
– 大表DDL操作耗时较长

1.2 GreenPlum数据类型概述

GreenPlum支持丰富的数据类型，包括数值类型、字符类型、日期时间类型、布尔类型、二进制类型等。选择合适的数据类型对于性能优化和存储空间管理非常重要。学习交流加群风哥微信: itpux-com

1.2.1 数值类型

GreenPlum数值类型：

1. 整数类型
– SMALLINT：2字节，-32768到32767
– INTEGER：4字节，-2147483648到2147483647
– BIGINT：8字节，-9223372036854775808到9223372036854775807

2. 任意精度数值
– NUMERIC(p,s)：精确数值，p总位数，s小数位数
– DECIMAL(p,s)：同NUMERIC

3. 浮点数类型
– REAL：4字节，单精度浮点数
– DOUBLE PRECISION：8字节，双精度浮点数
– FLOAT(n)：浮点数，n精度

4. 序列类型
– SERIAL：自增整数（1到2147483647）
– BIGSERIAL：自增大整数（1到9223372036854775807）

数值类型示例：
CREATE TABLE fgedu.fgedu_numeric_example (
id SERIAL,
small_col SMALLINT,
int_col INTEGER,
big_col BIGINT,
decimal_col NUMERIC(18,2),
float_col DOUBLE PRECISION
) DISTRIBUTED BY (id);

1.2.2 字符类型

GreenPlum字符类型：

1. 定长字符
– CHAR(n)：固定长度n，不足补空格
– CHARACTER(n)：同CHAR(n)

2. 变长字符
– VARCHAR(n)：可变长度，最大n
– CHARACTER VARYING(n)：同VARCHAR(n)
– TEXT：无限变长字符

3. 字符串类型说明
– VARCHAR(n)：最常用，推荐使用
– TEXT：适合不确定长度的大文本
– CHAR(n)：仅在确定长度时使用

字符类型示例：
CREATE TABLE fgedu.fgedu_char_example (
id SERIAL,
char_col CHAR(10),
varchar_col VARCHAR(100),
text_col TEXT
) DISTRIBUTED BY (id);

1.2.3 日期时间类型

GreenPlum日期时间类型：

1. 日期类型
– DATE：日期（年-月-日）
– 范围：4713 BC到294276 AD

2. 时间类型
– TIME：时间（时:分:秒）
– TIME WITH TIME ZONE：带时区的时间

3. 时间戳类型
– TIMESTAMP：日期和时间
– TIMESTAMP WITH TIME ZONE：带时区的日期和时间

4. 间隔类型
– INTERVAL：时间间隔

日期时间类型示例：
CREATE TABLE fgedu.fgedu_datetime_example (
id SERIAL,
date_col DATE,
time_col TIME,
timestamp_col TIMESTAMP,
timestamptz_col TIMESTAMP WITH TIME ZONE,
interval_col INTERVAL
) DISTRIBUTED BY (id);

1.2.4 其他常用类型

GreenPlum其他常用类型：

1. 布尔类型
– BOOLEAN：真/假
– 有效值：TRUE/FALSE, t/f, yes/no, 1/0

2. 二进制类型
– BYTEA：二进制数据
– 用于存储二进制文件

3. 网络地址类型
– INET：IPv4或IPv6地址
– CIDR：网络地址
– MACADDR：MAC地址

4. JSON类型
– JSON：文本格式JSON
– JSONB：二进制格式JSON（推荐）

5. UUID类型
– UUID：通用唯一标识符

其他类型示例：
CREATE TABLE fgedu.fgedu_other_example (
id SERIAL,
bool_col BOOLEAN,
bytea_col BYTEA,
inet_col INET,
json_col JSONB,
uuid_col UUID
) DISTRIBUTED BY (id);

1.3 GreenPlum命名规范

良好的命名规范可以提高代码可读性和可维护性。GreenPlum对象命名应遵循一定的规范。学习交流加群风哥QQ113257174

1.3.1 基本命名规则

GreenPlum命名基本规则：

1. 字符限制
– 只能使用字母、数字、下划线
– 必须以字母开头
– 长度不超过63个字符

2. 大小写敏感
– GreenPlum默认将标识符转换为小写
– 使用双引号可以保留大小写
– 建议统一使用小写

3. 避免使用保留字
– 不要使用SQL保留字作为对象名
– 如需要使用，必须加双引号

4. 命名风格
– 使用下划线分隔单词
– 示例：customer_order, user_profile
– 避免使用驼峰命名

命名示例：
— 好的命名
CREATE TABLE fgedu.customer_order (…);
CREATE TABLE fgedu.user_profile (…);

— 避免的命名
CREATE TABLE fgedu.CustomerOrder (…);
CREATE TABLE fgedu.”user” (…);

1.3.2 对象命名规范

风哥提示：GreenPlum对象命名规范：

表名：使用名词，描述业务实体，如customer_order
字段名：使用名词，描述字段含义，如customer_id
索引名：idx_表名_字段名，如idx_order_customer_id
视图名：v_表名或v_业务名称，如v_customer_order_summary
序列名：seq_表名_字段名，如seq_order_id
函数名：fn_功能描述，如fn_calculate_order_amount

Part02-生产环境规划与建议

2.1 GreenPlum数据类型选择原则

2.1.1 数据类型选择标准

数据类型选择标准：

1. 满足业务需求
– 能存储所有可能的数据
– 精度满足要求
– 范围足够

2. 占用空间最小
– 在满足需求的前提下
– 选择最小的数据类型
– 节省存储空间

3. 性能最优
– 固定长度类型处理更快
– 数值类型比字符类型快
– 避免不必要的类型转换

4. 便于维护
– 数据类型语义清晰
– 便于理解和使用
– 便于后续扩展

2.1.2 常见场景数据类型选择

常见场景数据类型选择：

1. ID字段
– 数据量<20亿：INTEGER或SERIAL - 数据量>20亿：BIGINT或BIGSERIAL
– 分布式ID：UUID或BIGINT

2. 金额字段
– 必须精确：NUMERIC(p,s)
– 精度根据业务定：通常NUMERIC(18,2)
– 避免使用浮点数

3. 日期时间
– 只需日期：DATE
– 需要时间：TIMESTAMP
– 跨时区：TIMESTAMP WITH TIME ZONE

4. 状态字段
– 少量状态：SMALLINT或VARCHAR(20)
– 枚举值：VARCHAR或使用枚举类型
– 建议使用VARCHAR便于扩展

5. 文本字段
– 短文本（<255）：VARCHAR(n) - 长文本（>255）：TEXT
– JSON数据：JSONB

数据类型选择示例：
CREATE TABLE fgedu.fgedu_good_types (
id BIGSERIAL PRIMARY KEY,
order_no VARCHAR(50) NOT NULL,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
total_amount NUMERIC(18,2) NOT NULL,
status VARCHAR(20) NOT NULL,
remark TEXT,
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) DISTRIBUTED BY (customer_id);

2.2 GreenPlum表设计原则

2.2.1 表设计基本原则

GreenPlum表设计基本原则：

1. 规范化与反规范化
– 适度规范化，避免过度
– 考虑查询性能，适当反规范化
– 根据业务场景平衡

2. 字段设计
– 每个字段有明确含义
– 避免NULL值（设置默认值）
– 选择合适的数据类型
– 添加字段注释

3. 主键与约束
– 逻辑主键用于业务
– 物理主键用于数据完整性
– 合理使用约束（NOT NULL、CHECK等）

4. 索引设计
– 只在必要的字段上建索引
– 考虑查询模式
– 避免过多索引影响写入性能

2.2.2 表设计注意事项

风哥提示：表设计注意事项：

必须指定分布键（DISTRIBUTED BY）
根据查询模式选择合适的分布键
大表考虑使用分区
考虑使用AO或AOCO存储模型
考虑数据压缩
避免频繁的ALTER TABLE操作

2.3 GreenPlum Schema设计原则

2.3.1 Schema设计原则

GreenPlum Schema设计原则：

1. 按业务模块划分
– 每个业务模块一个Schema
– 便于权限管理
– 便于对象组织

2. 按数据层次划分
– ODS：原始数据层
– DWD：明细数据层
– DWS：汇总数据层
– ADS：应用数据层

3. 按环境划分
– dev：开发环境
– test：测试环境
– prod：生产环境

4. 按功能划分
– etl：ETL相关对象
– report：报表相关对象
– tmp：临时对象

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

3.1 GreenPlum表DDL操作实战

3.1.1 创建表

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

fgedudb=>

# 创建普通表
fgedudb=> CREATE TABLE fgedu.fgedu_customer (
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

# 创建AO表
fgedudb=> CREATE TABLE fgedu.fgedu_order_ao (
fgedudb(> id BIGSERIAL,
fgedudb(> order_no VARCHAR(50) NOT NULL,
fgedudb(> customer_id INTEGER NOT NULL,
fgedudb(> order_date DATE NOT NULL,
fgedudb(> total_amount NUMERIC(18,2) NOT NULL,
fgedudb(> status VARCHAR(20) NOT NULL
fgedudb(> ) WITH (appendonly = true)
fgedudb(> DISTRIBUTED BY (customer_id);
CREATE TABLE

# 创建AOCO表（列存压缩）
fgedudb=> CREATE TABLE fgedu.fgedu_order_aoco (
fgedudb(> id BIGSERIAL,
fgedudb(> order_no VARCHAR(50) NOT NULL,
fgedudb(> customer_id INTEGER NOT NULL,
fgedudb(> order_date DATE NOT NULL,
fgedudb(> total_amount NUMERIC(18,2) NOT NULL,
fgedudb(> status VARCHAR(20) NOT NULL,
fgedudb(> remark TEXT
fgedudb(> ) WITH (
fgedudb(> appendonly = true,
fgedudb(> orientation = column,
fgedudb(> compresstype = zlib,
fgedudb(> compresslevel = 5
fgedudb(> ) DISTRIBUTED BY (customer_id);
CREATE TABLE

# 添加表注释
fgedudb=> COMMENT ON TABLE fgedu.fgedu_customer IS ‘客户信息表’;
COMMENT

# 添加字段注释
fgedudb=> COMMENT ON COLUMN fgedu.fgedu_customer.id IS ‘客户ID’;
COMMENT
fgedudb=> COMMENT ON COLUMN fgedu.fgedu_customer.customer_name IS ‘客户名称’;
COMMENT
fgedudb=> COMMENT ON COLUMN fgedu.fgedu_customer.email IS ‘邮箱’;
COMMENT
fgedudb=> COMMENT ON COLUMN fgedu.fgedu_customer.phone IS ‘电话’;
COMMENT
fgedudb=> COMMENT ON COLUMN fgedu.fgedu_customer.create_time IS ‘创建时间’;
COMMENT

3.1.2 修改表结构

# 添加字段
fgedudb=> ALTER TABLE fgedu.fgedu_customer
fgedudb-> ADD COLUMN address VARCHAR(200);
ALTER TABLE

# 修改字段类型
fgedudb=> ALTER TABLE fgedu.fgedu_customer
fgedudb-> ALTER COLUMN phone TYPE VARCHAR(30);
ALTER TABLE

# 设置字段默认值
fgedudb=> ALTER TABLE fgedu.fgedu_customer
fgedudb-> ALTER COLUMN status SET DEFAULT ‘ACTIVE’;
ALTER TABLE

# 删除字段默认值
fgedudb=> ALTER TABLE fgedu.fgedu_customer
fgedudb-> ALTER COLUMN status DROP DEFAULT;
ALTER TABLE

# 添加NOT NULL约束
fgedudb=> ALTER TABLE fgedu.fgedu_customer
fgedudb-> ALTER COLUMN email SET NOT NULL;
ALTER TABLE

# 删除NOT NULL约束
fgedudb=> ALTER TABLE fgedu.fgedu_customer
fgedudb-> ALTER COLUMN email DROP NOT NULL;
ALTER TABLE

# 重命名字段
fgedudb=> ALTER TABLE fgedu.fgedu_customer
fgedudb-> RENAME COLUMN address TO customer_address;
ALTER TABLE

# 删除字段
fgedudb=> ALTER TABLE fgedu.fgedu_customer
fgedudb-> DROP COLUMN customer_address;
ALTER TABLE

# 重命名表
fgedudb=> ALTER TABLE fgedu.fgedu_customer
fgedudb-> RENAME TO fgedu_customer_old;
ALTER TABLE

fgedudb=> ALTER TABLE fgedu.fgedu_customer_old
fgedudb-> RENAME TO fgedu_customer;
ALTER TABLE

3.1.3 删除表

# 创建测试表
fgedudb=> CREATE TABLE fgedu.fgedu_test_drop (
fgedudb(> id INT,
fgedudb(> name VARCHAR(100)
fgedudb(> ) DISTRIBUTED BY (id);
CREATE TABLE

# 删除表
fgedudb=> DROP TABLE fgedu.fgedu_test_drop;
DROP TABLE

# 删除不存在的表（不报错）
fgedudb=> DROP TABLE IF EXISTS fgedu.fgedu_test_drop;
NOTICE: table “fgedu_test_drop” does not exist, skipping
DROP TABLE

# 创建有依赖关系的表
fgedudb=> CREATE TABLE fgedu.fgedu_parent (
fgedudb(> id INT PRIMARY KEY,
fgedudb(> name VARCHAR(100)
fgedudb(> ) DISTRIBUTED BY (id);
CREATE TABLE

fgedudb=> CREATE TABLE fgedu.fgedu_child (
fgedudb(> id INT,
fgedudb(> parent_id INT REFERENCES fgedu.fgedu_parent(id)
fgedudb(> ) DISTRIBUTED BY (id);
CREATE TABLE

# 尝试删除父表（会失败，因为有依赖）
fgedudb=> DROP TABLE fgedu.fgedu_parent;
ERROR: cannot drop table fgedu_parent because other objects depend on it
DETAIL: constraint fgedu_child_parent_id_fkey on table fgedu_child depends on table fgedu_parent
HINT: Use DROP … CASCADE to drop the dependent objects too.

# 使用CASCADE删除（同时删除依赖对象）
fgedudb=> DROP TABLE fgedu.fgedu_parent CASCADE;
NOTICE: drop cascades to constraint fgedu_child_parent_id_fkey on table fgedu_child
DROP TABLE

# 清空表数据（保留表结构）
fgedudb=> CREATE TABLE fgedu.fgedu_truncate_test (
fgedudb(> id INT,
fgedudb(> name VARCHAR(100)
fgedudb(> ) DISTRIBUTED BY (id);
CREATE TABLE

fgedudb=> INSERT INTO fgedu.fgedu_truncate_test
fgedudb-> SELECT generate_series(1, 1000), ‘test’ || generate_series(1, 1000);
INSERT 0 1000

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

fgedudb=> TRUNCATE TABLE fgedu.fgedu_truncate_test;
TRUNCATE TABLE

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

3.2 GreenPlum索引DDL操作实战

3.2.1 创建索引

# 创建测试表
fgedudb=> CREATE TABLE fgedu.fgedu_order_index (
fgedudb(> id BIGSERIAL,
fgedudb(> order_no VARCHAR(50) NOT NULL,
fgedudb(> customer_id INTEGER NOT NULL,
fgedudb(> order_date DATE NOT NULL,
fgedudb(> status VARCHAR(20) NOT NULL,
fgedudb(> total_amount NUMERIC(18,2) NOT NULL
fgedudb(> ) DISTRIBUTED BY (customer_id);
CREATE TABLE

# 插入测试数据
fgedudb=> INSERT INTO fgedu.fgedu_order_index (
fgedudb(> order_no, customer_id, order_date, status, total_amount
fgedudb(> )
fgedudb-> SELECT
fgedudb-> ‘ORDER’ || lpad(generate_series(1, 100000)::TEXT, 10, ‘0’),
fgedudb-> (random() * 10000)::INT,
fgedudb-> ‘2024-01-01’::DATE + (random() * 365)::INT,
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-> (random() * 10000)::NUMERIC(18,2)
fgedudb-> FROM generate_series(1, 100000);
INSERT 0 100000

# 创建普通B-tree索引
fgedudb=> CREATE INDEX idx_order_customer_id
fgedudb-> ON fgedu.fgedu_order_index (customer_id);
CREATE INDEX

# 创建唯一索引
fgedudb=> CREATE UNIQUE INDEX idx_order_order_no
fgedudb-> ON fgedu.fgedu_order_index (order_no);
CREATE INDEX

# 创建复合索引
fgedudb=> CREATE INDEX idx_order_date_status
fgedudb-> ON fgedu.fgedu_order_index (order_date, status);
CREATE INDEX

# 创建索引并指定表空间
fgedudb=> CREATE INDEX idx_order_amount
fgedudb-> ON fgedu.fgedu_order_index (total_amount)
fgedudb-> TABLESPACE pg_default;
CREATE INDEX

3.2.2 删除索引

# 删除索引
fgedudb=> DROP INDEX fgedu.idx_order_amount;
DROP INDEX

# 删除不存在的索引（不报错）
fgedudb=> DROP INDEX IF EXISTS fgedu.idx_order_amount;
NOTICE: index “idx_order_amount” does not exist, skipping
DROP INDEX

# 重命名索引
fgedudb=> ALTER INDEX fgedu.idx_order_customer_id
fgedudb-> RENAME TO idx_order_cust_id;
ALTER INDEX

3.3 GreenPlum视图DDL操作实战

3.3.1 创建视图

# 创建简单视图
fgedudb=> CREATE VIEW fgedu.v_order_summary AS
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-> FROM fgedu.fgedu_order_index
fgedudb-> GROUP BY customer_id;
CREATE VIEW

# 创建视图并添加注释
fgedudb=> COMMENT ON VIEW fgedu.v_order_summary IS ‘订单汇总视图’;
COMMENT

# 查询视图
fgedudb=> SELECT * FROM fgedu.v_order_summary
fgedudb-> ORDER BY total_amount DESC
fgedudb-> LIMIT 5;
customer_id | order_count | total_amount | avg_amount
————-+————-+————–+——————–
1234 | 12 | 123456.78 | 10288.065000000000
5678 | 10 | 98765.43 | 9876.543000000000
9012 | 8 | 76543.21 | 9567.901250000000
3456 | 9 | 65432.10 | 7270.233333333333
7890 | 7 | 54321.09 | 7760.155714285714
(5 rows)

# 创建物化视图（GreenPlum 6+支持）
fgedudb=> CREATE MATERIALIZED VIEW fgedu.mv_order_summary AS
fgedudb-> SELECT
fgedudb-> customer_id,
fgedudb-> count(*) AS order_count,
fgedudb-> sum(total_amount) AS total_amount
fgedudb-> FROM fgedu.fgedu_order_index
fgedudb-> GROUP BY customer_id;
SELECT 10000

# 刷新物化视图
fgedudb=> REFRESH MATERIALIZED VIEW fgedu.mv_order_summary;
REFRESH MATERIALIZED VIEW

3.3.2 修改和删除视图

# 重命名视图
fgedudb=> ALTER VIEW fgedu.v_order_summary
fgedudb-> RENAME TO v_order_cust_summary;
ALTER VIEW

fgedudb=> ALTER VIEW fgedu.v_order_cust_summary
fgedudb-> RENAME TO v_order_summary;
ALTER VIEW

# 删除视图
fgedudb=> DROP VIEW fgedu.v_order_summary;
DROP VIEW

# 删除不存在的视图（不报错）
fgedudb=> DROP VIEW IF EXISTS fgedu.v_order_summary;
NOTICE: view “v_order_summary” does not exist, skipping
DROP VIEW

# 删除物化视图
fgedudb=> DROP MATERIALIZED VIEW fgedu.mv_order_summary;
DROP MATERIALIZED VIEW

Part04-生产案例与实战讲解

4.1 GreenPlum数据类型使用案例

4.1.1 电商订单表设计

# 电商订单表完整设计
fgedudb=> CREATE TABLE fgedu.fgedu_ecommerce_order (
fgedudb(> — 主键
fgedudb(> id BIGSERIAL PRIMARY KEY,
fgedudb(>
fgedudb(> — 订单基本信息
fgedudb(> order_no VARCHAR(50) NOT NULL,
fgedudb(> order_type VARCHAR(20) NOT NULL,
fgedudb(> order_channel VARCHAR(20) NOT NULL,
fgedudb(>
fgedudb(> — 客户信息
fgedudb(> customer_id BIGINT NOT NULL,
fgedudb(> customer_name VARCHAR(100),
fgedudb(> customer_phone VARCHAR(20),
fgedudb(>
fgedudb(> — 商品信息
fgedudb(> product_id BIGINT NOT NULL,
fgedudb(> product_name VARCHAR(200),
fgedudb(> quantity INT NOT NULL,
fgedudb(> unit_price NUMERIC(18,2) NOT NULL,
fgedudb(>
fgedudb(> — 金额信息
fgedudb(> total_amount NUMERIC(18,2) NOT NULL,
fgedudb(> discount_amount NUMERIC(18,2) DEFAULT 0,
fgedudb(> pay_amount NUMERIC(18,2) NOT NULL,
fgedudb(>
fgedudb(> — 时间信息
fgedudb(> order_time TIMESTAMP NOT NULL,
fgedudb(> pay_time TIMESTAMP,
fgedudb(> ship_time TIMESTAMP,
fgedudb(> complete_time TIMESTAMP,
fgedudb(>
fgedudb(> — 状态信息
fgedudb(> order_status VARCHAR(20) NOT NULL,
fgedudb(> pay_status VARCHAR(20) NOT NULL,
fgedudb(> ship_status VARCHAR(20) NOT NULL,
fgedudb(>
fgedudb(> — 其他信息
fgedudb(> remark TEXT,
fgedudb(> ext_info JSONB,
fgedudb(>
fgedudb(> — 系统字段
fgedudb(> create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
fgedudb(> update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
fgedudb(> is_deleted BOOLEAN NOT NULL DEFAULT FALSE
fgedudb(> ) WITH (
fgedudb(> appendonly = true,
fgedudb(> orientation = column,
fgedudb(> compresstype = zstd,
fgedudb(> compresslevel = 6
fgedudb(> ) DISTRIBUTED BY (customer_id)
fgedudb(> PARTITION BY RANGE (order_time)
fgedudb(> (
fgedudb(> START (timestamp ‘2024-01-01 00:00:00’)
fgedudb(> END (timestamp ‘2025-01-01 00:00:00’)
fgedudb(> EVERY (INTERVAL ‘1 month’)
fgedudb(> );
CREATE TABLE

# 添加注释
fgedudb=> COMMENT ON TABLE fgedu.fgedu_ecommerce_order IS ‘电商订单表’;
fgedudb=> COMMENT ON COLUMN fgedu.fgedu_ecommerce_order.id IS ‘订单ID’;
fgedudb=> COMMENT ON COLUMN fgedu.fgedu_ecommerce_order.order_no IS ‘订单号’;
fgedudb=> COMMENT ON COLUMN fgedu.fgedu_ecommerce_order.customer_id IS ‘客户ID’;
fgedudb=> COMMENT ON COLUMN fgedu.fgedu_ecommerce_order.total_amount IS ‘订单总金额’;
fgedudb=> COMMENT ON COLUMN fgedu.fgedu_ecommerce_order.order_time IS ‘下单时间’;
fgedudb=> COMMENT ON COLUMN fgedu.fgedu_ecommerce_order.order_status IS ‘订单状态’;

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

4.2 GreenPlum表结构变更案例

4.2.1 在线添加字段

# 场景：需要给订单表添加一个字段
# 原表结构
fgedudb=> CREATE TABLE fgedu.fgedu_orders_v1 (
fgedudb(> id BIGSERIAL,
fgedudb(> order_no VARCHAR(50),
fgedudb(> customer_id INT,
fgedudb(> total_amount NUMERIC(18,2)
fgedudb(> ) DISTRIBUTED BY (customer_id);
CREATE TABLE

fgedudb=> INSERT INTO fgedu.fgedu_orders_v1
fgedudb-> SELECT
fgedudb-> generate_series(1, 1000000),
fgedudb-> ‘ORDER’ || lpad(generate_series(1, 1000000)::TEXT, 10, ‘0’),
fgedudb-> (random() * 10000)::INT,
fgedudb-> (random() * 10000)::NUMERIC(18,2);
INSERT 0 1000000

# 方案1：直接添加字段（有默认值会重写表，慢）
fgedudb=> ALTER TABLE fgedu.fgedu_orders_v1
fgedudb-> ADD COLUMN status VARCHAR(20) DEFAULT ‘NEW’;
ALTER TABLE

# 方案2：先添加字段，再更新（推荐）
fgedudb=> CREATE TABLE fgedu.fgedu_orders_v2 (
fgedudb(> id BIGSERIAL,
fgedudb(> order_no VARCHAR(50),
fgedudb(> customer_id INT,
fgedudb(> total_amount NUMERIC(18,2),
fgedudb(> status VARCHAR(20)
fgedudb(> ) DISTRIBUTED BY (customer_id);
CREATE TABLE

fgedudb=> INSERT INTO fgedu.fgedu_orders_v2 (id, order_no, customer_id, total_amount, status)
fgedudb-> SELECT id, order_no, customer_id, total_amount, ‘NEW’
fgedudb-> FROM fgedu.fgedu_orders_v1;
INSERT 0 1000000

fgedudb=> BEGIN;
BEGIN
fgedudb=> ALTER TABLE fgedu.fgedu_orders_v1 RENAME TO fgedu_orders_v1_old;
ALTER TABLE
fgedudb=> ALTER TABLE fgedu.fgedu_orders_v2 RENAME TO fgedu_orders_v1;
ALTER TABLE
fgedudb=> COMMIT;
COMMIT

fgedudb=> DROP TABLE fgedu.fgedu_orders_v1_old;
DROP TABLE

from GreenPlum视频:www.itpux.com

4.3 GreenPlum批量DDL操作案例

4.3.1 批量创建表

# 创建批量建表的脚本
fgedudb=> CREATE OR REPLACE FUNCTION fgedu.fn_create_batch_tables()
fgedudb-> RETURNS VOID AS $$
fgedudb-> DECLARE
fgedudb-> v_table_name TEXT;
fgedudb-> v_sql TEXT;
fgedudb-> BEGIN
fgedudb-> FOR i IN 1..10 LOOP
fgedudb-> v_table_name := ‘fgedu_batch_table_’ || i;
fgedudb-> v_sql := ‘
fgedudb’> CREATE TABLE IF NOT EXISTS fgedu.’ || v_table_name || ‘ (
fgedudb’> id SERIAL,
fgedudb’> name VARCHAR(100),
fgedudb’> create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
fgedudb’> ) DISTRIBUTED BY (id)’;
fgedudb-> EXECUTE v_sql;
fgedudb-> RAISE NOTICE ‘Created table: %’, v_table_name;
fgedudb-> END LOOP;
fgedudb-> END;
fgedudb-> $$ LANGUAGE plpgsql;
CREATE FUNCTION

# 执行批量建表
fgedudb=> SELECT fgedu.fn_create_batch_tables();
NOTICE: Created table: fgedu_batch_table_1
NOTICE: Created table: fgedu_batch_table_2
NOTICE: Created table: fgedu_batch_table_3
NOTICE: Created table: fgedu_batch_table_4
NOTICE: Created table: fgedu_batch_table_5
NOTICE: Created table: fgedu_batch_table_6
NOTICE: Created table: fgedu_batch_table_7
NOTICE: Created table: fgedu_batch_table_8
NOTICE: Created table: fgedu_batch_table_9
NOTICE: Created table: fgedu_batch_table_10
fn_create_batch_tables
————————-

(1 row)

# 批量删除表
fgedudb=> SELECT ‘DROP TABLE IF EXISTS fgedu.’ || tablename || ‘ CASCADE;’
fgedudb-> FROM pg_tables
fgedudb-> WHERE schemaname = ‘fgedu’
fgedudb-> AND tablename LIKE ‘fgedu_batch_table_%’
fgedudb-> ORDER BY tablename;
?column?
——————————–
DROP TABLE IF EXISTS fgedu.fgedu_batch_table_1 CASCADE;
DROP TABLE IF EXISTS fgedu.fgedu_batch_table_2 CASCADE;
DROP TABLE IF EXISTS fgedu.fgedu_batch_table_3 CASCADE;
DROP TABLE IF EXISTS fgedu.fgedu_batch_table_4 CASCADE;
DROP TABLE IF EXISTS fgedu.fgedu_batch_table_5 CASCADE;
DROP TABLE IF EXISTS fgedu.fgedu_batch_table_6 CASCADE;
DROP TABLE IF EXISTS fgedu.fgedu_batch_table_7 CASCADE;
DROP TABLE IF EXISTS fgedu.fgedu_batch_table_8 CASCADE;
DROP TABLE IF EXISTS fgedu.fgedu_batch_table_9 CASCADE;
DROP TABLE IF EXISTS fgedu.fgedu_batch_table_10 CASCADE;
(10 rows)

Part05-风哥经验总结与分享

5.1 GreenPlum DDL最佳实践

5.1.1 表创建最佳实践

表创建最佳实践：

1. 创建前规划
– 明确表的用途和数据量
– 确定分布键
– 确定是否需要分区
– 确定存储模型

2. 字段设计
– 选择合适的数据类型
– 设置NOT NULL约束
– 设置默认值
– 添加字段注释

3. 完整性约束
– 合理使用主键
– 合理使用唯一约束
– 合理使用CHECK约束
– 避免过度约束影响性能

4. 索引设计
– 只在必要的字段上建索引
– 考虑查询模式
– 避免过多索引
– 定期维护索引

5. 创建后操作
– 添加表注释
– 验证表结构
– 测试数据分布
– 授权相应权限

5.1.2 DDL操作最佳实践

风哥提示：DDL操作最佳实践：

在业务低峰期执行DDL操作
大表DDL操作前先备份
先在测试环境验证
使用事务包裹相关的DDL操作
ALTER TABLE添加字段时避免设置默认值
DROP操作前先确认
记录所有DDL变更

5.2 GreenPlum DDL检查清单

GreenPlum DDL检查清单：

[ ] 1. 创建表前检查
[ ] 已确定表的用途
[ ] 已确定数据量预估
[ ] 已确定查询模式
[ ] 已确定分布键
[ ] 已确定是否分区
[ ] 已确定存储模型

[ ] 2. 字段设计检查
[ ] 每个字段都有明确含义
[ ] 数据类型选择合适
[ ] NOT NULL约束设置正确
[ ] 默认值设置合理
[ ] 字段注释已添加

[ ] 3. 索引设计检查
[ ] 索引数量合理
[ ] 索引字段选择正确
[ ] 复合索引顺序合理
[ ] 考虑索引维护成本

[ ] 4. DDL执行前检查
[ ] 已在测试环境验证
[ ] 已选择合适的执行时间
[ ] 已备份相关数据
[ ] 已通知相关人员
[ ] 已准备回滚方案

[ ] 5. DDL执行后检查
[ ] 验证对象创建成功
[ ] 验证数据完整性
[ ] 验证权限设置
[ ] 记录DDL变更
[ ] 监控系统性能

5.3 GreenPlum常见DDL误区

5.3.1 误区1：不指定分布键

误区：创建表时不指定分布键

错误示例：
CREATE TABLE fgedu.fgedu_bad_table (
id INT,
name VARCHAR(100)
);
— 不指定分布键，GreenPlum会默认选择第一个字段

正确做法：
CREATE TABLE fgedu.fgedu_good_table (
id INT,
name VARCHAR(100)
) DISTRIBUTED BY (id);
— 明确指定分布键

原因分析：
– 不指定分布键可能导致数据倾斜
– 表结构变更可能影响分布键
– 明确指定更清晰，便于维护

5.3.2 误区2：频繁使用ALTER TABLE

误区：频繁使用ALTER TABLE修改表结构

错误做法：
ALTER TABLE fgedu.fgedu_table ADD COLUMN col1 INT;
ALTER TABLE fgedu.fgedu_table ADD COLUMN col2 VARCHAR(100);
ALTER TABLE fgedu.fgedu_table ALTER COLUMN col1 TYPE BIGINT;
— 多次ALTER TABLE，每次都可能锁表

正确做法：
— 方案1：一次性完成所有修改
CREATE TABLE fgedu.fgedu_table_new AS
SELECT
t.*,
NULL::INT AS col1,
NULL::VARCHAR(100) AS col2
FROM fgedu.fgedu_table t
DISTRIBUTED BY (id);

BEGIN;
ALTER TABLE fgedu.fgedu_table RENAME TO fgedu_table_old;
ALTER TABLE fgedu.fgedu_table_new RENAME TO fgedu_table;
COMMIT;

— 方案2：提前规划，避免频繁变更

原因分析：
– ALTER TABLE会锁表，影响业务
– 多次ALTER效率低
– 可能导致数据重分布

5.3.3 误区3：DROP前不备份

误区：DROP操作前不备份

错误做法：
DROP TABLE fgedu.fgedu_important_table;
— 直接删除，无法恢复

正确做法：
— 1. 先备份
CREATE TABLE fgedu.fgedu_important_table_backup AS
SELECT * FROM fgedu.fgedu_important_table
DISTRIBUTED BY (id);

— 2. 确认备份成功
SELECT count(*) FROM fgedu.fgedu_important_table_backup;

— 3. 再删除
DROP TABLE fgedu.fgedu_important_table;

— 或者重命名而不是删除
ALTER TABLE fgedu.fgedu_important_table
RENAME TO fgedu_important_table_drop_20240601;

原因分析：
– DROP操作不可回滚
– 误删数据损失严重
– 备份成本低，保险系数高

通过本文档的学习，相信您已经掌握了GreenPlum DDL与数据类型的使用方法和最佳实践。在生产环境中，规范的DDL操作和合理的数据类型选择是数据库稳定运行的基础，需要认真对待。更多视频教程www.fgedu.net.cn，学习交流加群风哥微信: itpux-com，学习交流加群风哥QQ113257174，更多学习教程公众号风哥教程itpux_com，from GreenPlum视频:www.itpux.com。

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