强制数据分布与导出prefix - 阿里云pg, hdb pg oss快速数据规整外部表导出实践案例-阿里云开发者社区

开发者社区> 德哥> 正文

强制数据分布与导出prefix - 阿里云pg, hdb pg oss快速数据规整外部表导出实践案例

简介:
+关注继续查看

标签

PostgreSQL , 阿里云 , RDS PG , HDB PG


背景

批量数据导出在数据分析、数据圈选、广告系统、数据清洗系统、数据规整系统中是一个比较常见的需求,例如:

1、导出任务流。

2、广告系统,圈选人群。

3、数据清洗并导出。

4、数据按某些规则分类,规整并输出到不同的文件。

通常数据库的导出功能相对来说比较单一,例如可以导出query的结果,导出表,导出SCHEMA,导出整个库等。

导出的格式包括CSV,STDOUT等。

但是一个QUERY如果要导出到多个文件(例如前面提到的数据规整需求),目前数据库是没有这样的功能的,需要程序开发。

阿里云RDS PostgreSQL和HDB PostgreSQL提供了这样的导出功能,可以将数据按一定的规则快速的导出到OSS(定义文件的PREFIX,导出的格式等)。

数据按某些规则分类,规整并输出到不同的文件

将数据按某些(字段或虚拟列)内容的不同,写入不同的文件。

这些字段或虚拟列,可以输出到文件,也可以不输出到文件(通过参数调整)。

目前不支持写文件头(列的描述),后面可以加这个功能。或者用户可以在文件最前面加一行,比如用sed或其他编辑工具来添加。

HybridDB for PostgreSQL 例子如下:

1、创建源表

create table t_source (host text, key int , data text);   

2、写入一些测试数据

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');   

3、创建目标表(通用型)

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);   

4、创建导出的日志表(元数据表)

create table tbl_output_struct(   
  id int primary key,   -- PK   
  prefix text, -- 前缀   
  struct json, -- 这个前缀对应的,JSON的结构   
  osspath text -- 这个前缀对应的OSS的路径(bucket)。   
);   

5、导出到OSS

由于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)   

6、格式转换

导出为JSON格式,如果需要转换为CSV,用户可以将数据从OSS读出之后,写程序转换为CSV格式。

格式的内容在元数据表里面。

OSS将来应该也会提供这样的函数编程接口,对OSS里面的数据进行格式转换。

7、将结果作为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 外部表文档:https://help.aliyun.com/knowledge_detail/43352.html

HDB PG OSS 外部表文档:https://help.aliyun.com/document_detail/35457.html

《日增量万亿+级 实时分析、数据规整 - 阿里云HybridDB for PostgreSQL最佳实践》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
DLA如何分析Table Store的数据
数据湖(Data Lake)是时下热门的概念,基于数据湖,可以不用做任何ETL、数据搬迁等过程,实现跨各种异构数据源进行大数据关联分析,从而极大的节省成本和提升用户体验。
886 0
如何访问 redis 中的海量数据?避免事故产生
有时候我们需要知道线上的redis的使用情况,尤其需要知道一些前缀的key值,让我们怎么去查看呢?今天分享一个小知识点
3236 0
Thinkphp5.0 PHPExcel 数据表格导出导入
1、先在github里面下载PHPexcel这个类库 或者通过以下链接下载PHPexcel类库。 http://www.php.cn/xiazai/leiku/1491 2、解压之后把它复制到extend里面 控制器代码如下: /** * Created by PhpStorm.
1792 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载