本文共 4247 字，大约阅读时间需要 14 分钟。
PostgreSQL , 阿里云 , RDS PG , HDB PG
批量数据导出在数据分析、数据圈选、广告系统、数据清洗系统、数据规整系统中是一个比较常见的需求，例如：
1、导出任务流。
2、广告系统，圈选人群。
3、数据清洗并导出。
4、数据按某些规则分类，规整并输出到不同的文件。
通常数据库的导出功能相对来说比较单一，例如可以导出query的结果，导出表，导出SCHEMA，导出整个库等。
导出的格式包括CSV，STDOUT等。
但是一个QUERY如果要导出到多个文件（例如前面提到的数据规整需求），目前数据库是没有这样的功能的，需要程序开发。
阿里云RDS PostgreSQL和HDB PostgreSQL提供了这样的导出功能，可以将数据按一定的规则快速的导出到OSS（定义文件的PREFIX，导出的格式等）。
将数据按某些(字段或虚拟列)内容的不同，写入不同的文件。
这些字段或虚拟列，可以输出到文件，也可以不输出到文件（通过参数调整）。
目前不支持写文件头（列的描述），后面可以加这个功能。或者用户可以在文件最前面加一行，比如用sed或其他编辑工具来添加。
HybridDB for PostgreSQL 例子如下：
create table t_source (host text, key int , data text);
insert into t_source values('host1',1,'1'); insert into t_source values('host1',1,'2'); insert into t_source values('host1',2,'3'); insert into t_source values('host2',1,'4'); insert into t_source values('host3',1,'5'); insert into t_source values('host4',1,'6'); insert into t_source values('host5',1,'7'); file_name_generator_col，指定这列的内容，作为输出OSS文件的PREFIX。不同的值，输出到不同的文件中。
output_generator_col，指是否将file_name_generator_col列输出到OSS中。false表示不输出。
create WRITABLE external table cdn_demo_output ( prefix text, -- 这个列作为分布键、也作为输出文件的PREFIX，同时可以通过output_generator_col参数控制是否将这个列输出到OSS文件中。 Data json ) location('@@oss_host@@ dir=cdn_demo_20170824/ id=@@oss_id@@ key= @@oss_key@@ bucket=@@oss_bucket@@ output_generator_col=false file_name_generator_col=prefix') FORMAT 'csv' DISTRIBUTED BY (prefix); create table tbl_output_struct( id int primary key, -- PK prefix text, -- 前缀 struct json, -- 这个前缀对应的，JSON的结构 osspath text -- 这个前缀对应的OSS的路径（bucket）。 );
由于HybridDB for PostgreSQL是分布式数据库，OSS表选择了根据prefix做分布键，所以分布键有几个，就有机会最多往几个SEGMENT去分发，并行写OSS。如果只有一个prefix值，那么就只会分发到一个SEGMENT写OSS。
用户可以根据需要，选择几个PREFIX，当然PREFIX还可以在分词prefix1和prefix2组成的联合prefix。
例如：
国家||'_'||省份 这个相当于两个字段联合的prefix。 taskid||'_'||(random()*9)::int::text 从而每个taskid可以拆成10个prefix2，相当于一个taskid开了10个并行写。 （当没有第二个字段时，使用这种手段效果一样可以多个节点并行）
导出：
begin; -- 记录下此次导出的结构，PREFIX等(若已知)。 insert into tbl_output_struct values ('label1', '{col:type1, col2:type2, ....}', 'bucket_path'); -- OR insert into tbl_output_struct select host||key, '{col:type1, col2:type2, ....}', 'bucket_path' from t_source group by 1; -- 打开按prefix导出的参数开关 set rds_write_oss_file_by_distribution_column=on; -- 导出到OSS insert into cdn_demo_output ( prefix, Data ) select host_and_key, -- prefix驱动键 row_to_json(row(host_and_key,data) -- 将需要导出的内容，封装到JSON中 from ( select row_number() over (partition by host||key order by host||key) as RN, -- 强制数据库按PREFIX的顺序排序，这样才能保证prefix写入到对应的文件 host||key as host_and_key, data from t_source t1 ) t; end; PS，强制分布的原理：
QUERY PLAN -------------------------------------------------------------------------------------------------------------- Gather Motion 48:1 (slice2; segments: 48) (cost=0.04..0.06 rows=1 width=64) -> Subquery Scan t (cost=0.04..0.06 rows=1 width=64) -> Window (cost=0.04..0.05 rows=1 width=68) Partition By: host_and_key Order By: host_and_key -> Sort (cost=0.04..0.04 rows=1 width=68) Sort Key: host_and_key -> Redistribute Motion 48:48 (slice1; segments: 48) (cost=0.00..0.03 rows=1 width=68) Hash Key: host_and_key -> Seq Scan on t_source t1 (cost=0.00..0.01 rows=1 width=68) Settings: enable_bitmapscan=off; enable_seqscan=off; optimizer=off Optimizer status: legacy query optimizer (12 rows)
导出为JSON格式，如果需要转换为CSV，用户可以将数据从OSS读出之后，写程序转换为CSV格式。
格式的内容在元数据表里面。
OSS将来应该也会提供这样的函数编程接口，对OSS里面的数据进行格式转换。
将导出的内容，再提供查询。
由于前面我们设置了output_generator_col=false，所以不输出分布键，那么实际上在OSS文件中，只有一列，所以只读OSS外部表的定义如下
create external table cdn_demo_check ( Data json ) location('@@oss_host@@ dir=cdn_demo_20170824/ id=@@oss_id@@ key= @@oss_key@@ bucket=@@oss_bucket@@') FORMAT 'csv'; select * from cdn_demo_check order by data; RDS PG OSS 外部表文档：
HDB PG OSS 外部表文档：
转载地址：http://mjwao.baihongyu.com/