Greenplum数据库开发

Greenplum表创建语句如下：

CREATE TABLE alex.tch_gsm_user_mon
(
bcyc_id varchar(6) not null,
user_id varchar(16) not null,
imei varchar(20)
)
WITH (APPENDONLY=true, ORIENTATION=column,COMPRESSTYPE=zlib, COMPRESSLEVEL=5)
DISTRIBUTED BY (user_id)
PARTITION BY LIST(bcyc_id)
(
PARTITION  p190001  VALUES('190001')  WITH  (appendonly=true,  orientation=column,  compresstype= zlib, compresslevel=5),
PARTITION  p201303  VALUES('201303')  WITH  (appendonly=true,  orientation=column,  compresstype= zlib, compresslevel=5)
);
COMMENT ON TABLE alex.tc_usr_td_info_mon IS 'TD 用户信息表月表';
COMMENT ON COLUMN alex.tc_usr_td_info_mon.bcyc_id IS '月份';
COMMENT ON COLUMN alex.tc_usr_td_info_mon.user_id IS '用户标识';
COMMENT ON COLUMN alex.tc_usr_td_info_mon.td_tac_flag IS 'TD 终端标志';

其中的设计要点为：

【存储模式】（列模式适合宽表设计(多字段表),在查询时只查询部分字段的情况，效率高于行模式）

1.数据表使用列存储 AO 表，使用 zlib 算法 5 级压缩：

WITH(APPENDONLY=true,ORIENTATION=column,COMPRESSTYPE=zlib,COMPRESSLEVEL=5)

2. 参数表、维表使用行存储非 AO 表，非压缩，其存储模式设置为：

WITH (APPENDONLY=false)

【分布键】

1.所有数据表均应显式设置分布键，不允许不定义分布键。

2.适合作为分布键的字段的数据特征为：数据分 布离散、重复数据越少越好、不应出现大量的空值或空字符串。

3.常用适合作为分布键的字段包括：用户标识、客户标识、账户标识、电话号码、IMSI 号、IMEI 号等。但并非直接使用上述字段就一定没有问题，还是要注意该字段的实际数据总是否存在大量空值空字符串或重复值。

4.应充分理解分布键的含义和用途，不能与主键的使用方式相混淆。

5.对于多维数据表，其分布键设置应 直接定义为随机分布方式。

【数据类型】

1.对于字符串类型字段，不能使用不带长度定义的 varchar 类型，应按照其实际长度进行定义。

【注释】

1.所有表及字段均应使用 comment 语句添加注释，标明其中文名称或含义。

【分区设置】

1. 经分系统中常见的以年、月、周、日、小时等单个列表值作为分区字段的，应使用列表分      区模式，即：PATRITION BY LIST(XXX)

2. 分区名命名规则为：小写 p+年/月/周/日编码。

 例如：p201303

其中分区名中的年/月/周/日编码应与 VALUES 项中的年/月/周/日编码一致。

3. 新建分区表时,无论是否需要立即创建具体的 分区列表，均应额外创建一个统一的默认分区。 对于日分区表，默认分区为： PARTITION p19000101 VALUES('19000101') 对于月/周分区表，默认分区为： PARTITION p190001 VALUES('190001') 其他周期分区表命名以此类推。 此分区的 WITH 存储选项必须与主表的存储选项一致。 所有分区的 WITH 存储选项必须与主表的存储选项一致。

4.在性能可以满足的情况下，尽量不使用数据分区；

5.因会造成表对象数量过多，增加执行计划生成的复杂性，禁止使用二级分区；

6.数据量在亿级别以下，建议不要使用分区；

7.表的数据在单个实例的数据量在100万级别以下，不需要分区；

8.分区字段不可以UPDATE，需要用delete + insert或者truncate + insert替代实现。

外部表

   GP数据库提供了一种名为外部表的特殊表，用于在库内定义与外部文件的映射关系，从而允许用户像访问标准数据库表一样访问外部文件。外部表的主要用途是实现数据文件入库及表数据导出成文件。

1：创建只读外部表，定义数据文件的路径、格式等相关信息；

2：执行 select … insert 语句，将外部表数据实际插入对应同构数据表中，完成入库

典型的导入用只读外部表设计代码示例如下：

CREATE EXTERNAL TABLE alex.ext_fact_td_app_d
(
like alex.fact_td_kpi_d
)
LOCATION
(
'gpfdist://smdw:8888/localdir/gplocaltemp/d_*_00005_01_01_*.dat'
)
FORMAT 'text' (delimiter '$' escape 'OFF')
ENCODING 'GB18030'
LOG ERRORS INTO err_log
SEGMENT REJECT LIMIT 1 PERCENT;

其中的设计要点为：

1.导入用只读外部表的模式名应与对应数据表一致，表名为对应数据表表名增加’ext_’前缀。

2.外部表字段定义应使用like语句，以保证与对应数据表字段结构一致。

3.文件路径定义语句中gpfdist://smdw:8888/对应映射到GP平台ETL接口机的 指定目录。例如：gpfdist://smdw:8888/localdir/gplocaltemp/test.dat 对应的实际文件路径为：/gplocaltemp/test.dat

4.待入库文件均应存放到 GP 平台 ETL 接口机的指定文件系统中，或在其中创建可访问的硬链接/符号链接。

5.文件路径定义中可以使用 ’*’通配符进行多个文件匹配。

6.文件格式定义应与实际数据文件格式一致

7.对于分隔符格式的数据文件，如不带字符串定界符，应定义为 text 文件格式，

 例如： FORMAT 'text' (delimiter '$' escape 'OFF') 其中，固定不支持字符串定界符，字段分隔符可根据实际情况配置。

对于分隔符格式的数据文件，如带字符串定界符，应定义为csv文件格式

8. 对于数据文件中文编码为 GBK 编码的，其文件编码应定义为： ENCODING 'GB18030'