阅读266 返回首页    go 微信

PostgreSQLReader__Reader插件_使用手册_数据集成-阿里云

1 快速介绍

PostgreSQLReader插件实现了从PostgreSQL读取数据。在底层实现上，PostgreSQLReader通过JDBC连接远程PostgreSQL数据库，并执行相应的sql语句将数据从PostgreSQL库中SELECT出来。

公有云上RDS提供PostgreSQL存储引擎。

2 实现原理

简而言之，PostgreSQLReader通过JDBC连接器连接到远程的PostgreSQL数据库，并根据用户配置的信息生成查询SELECT SQL语句并发送到远程PostgreSQL数据库，并将该SQL执行返回结果使用CDP自定义的数据类型拼装为抽象的数据集，并传递给下游Writer处理。

对于用户配置Table、Column、Where的信息，PostgreSQLReader将其拼接为SQL语句发送到PostgreSQL数据库；对于用户配置querySql信息，PostgreSQL直接将其发送到PostgreSQL数据库。

3 功能说明

3.1 配置样例
  • 使用Instance方式配置一个从PostgreSQL数据库同步到抽取数据作业
{
    "type": "job",
    "traceId": "您可以在这里填写您作业的追踪ID，建议使用业务名+您的作业ID",
    "version": "1.0",
    "configuration": {
        "reader": {
            "plugin": "postgresql",
            "parameter": {
                "instanceName": "datasync001",
                "table": "table",
                "username": "xxx",
                "password": "xxxxxx",
                "column": ["*"],
                "fetchSize": 512,
                "splitPk": "pk",
                "where": "1 = 1"
            }
        },
        "writer": {
            "plugin": "odps",
            "parameter": {
                "accessId": "bazhen.csy",
                "accessKey": "xxxxxxx",
                "project": "project",
                "table": "table",
                "column": ["*"],
                "partition": "pt=2014",
                "truncate": true
            }
        }
    }
}
  • 使用JDBC方式配置一个从PostgreSQL数据库同步抽取数据作业:
{
    "type": "job",
    "traceId": "您可以在这里填写您作业的追踪ID，建议使用业务名+您的作业ID",
    "version": "1.0",
    "configuration": {
        "reader": {
            "plugin": "postgresql",
            "parameter": {
                "jdbcUrl": "jdbc:postgresql://HOST_NAME:PORT/DATABASE_NAME",
                "table": "table",
                "username": "xxx",
                "password": "xxxxxx",
                "column": ["*"],
                "fetchSize": 512,
                "splitPk": "pk",
                "where": "1 = 1"
            }
        },
        "writer": {
            "plugin": "odps",
            "parameter": {
                "accessId": "bazhen.csy",
                "accessKey": "xxxxxxx",
                "project": "project",
                "table": "table",
                "column": ["*"],
                "partition": "pt=2014",
                "truncate": true
            }
        }
    }
}
  • 配置一个自定义SQL的数据库同步任务到ODPS的作业：
{
    "type": "job",
    "traceId": "您可以在这里填写您作业的追踪ID，建议使用业务名+您的作业ID",
    "version": "1.0",
    "configuration": {
        "reader": {
            "plugin": "postgresql",
            "parameter": {
                "jdbcUrl": "jdbc:postgresql://HOST_NAME:PORT/DATABASE_NAME",
                "username": "xxx",
                "password": "xxxxxx",
                "querySql": "SELECT * from dual",
                "fetchSize": 512
            }
        },
        "writer": {
            "plugin": "odps",
            "parameter": {
                "accessId": "bazhen.csy",
                "accessKey": "xxxxxxx",
                "project": "project",
                "table": "table",
                "column": [
                    "*"
                ],
                "partition": "pt=2014",
                "truncate": true
            }
        }
    }
}
3.2 参数说明

  • instanceName

    • 描述: 阿里云RDS实例名称(Instance名称)。用户使用该配置指定RDS的Instance名称，CDP将翻译为底层执行的jdbcUrl连接串连接。 

      instanceName指定的是RDS实例。发给类似Mysql实例，指定了数据源的IP+Port，需要和database配合使用。例如，在RDS WebConsole页面中点击【基本信息】，其右侧详情的名称即是用户拥有RDS的instanceName。

![RDS instanceName图解](https://img.alibaba.com/kf/HTB1wAz9GpXXXXc9XXXXq6xXFXXXl.jpg)

    • 必选：是

    • 默认值：无

  • jdbcUrl

    • 描述：描述的是到对端数据库的JDBC连接信息，jdbcUrl按照PostgreSQL官方规范，并可以填写连接附件控制信息。
  • 必选：是
  • 默认值：无

  • username

    • 描述：数据源的用户名。
    • 必选：是
    • 默认值：无

  • password

    • 描述：数据源指定用户名的密码。
    • 必选：是
    • 默认值：无

  • table

    • 描述：所选取的需要同步的表名。
    • 必选：是
    • 默认值：无

  • column

    • 描述：所配置的表中需要同步的列名集合，使用JSON的数组描述字段信息。用户使用代表默认使用所有列配置，例如['']。

      支持列裁剪，即列可以挑选部分列进行导出。

      支持列换序，即列可以不按照表schema信息进行导出。

      支持常量配置，用户需要按照JSON格式:

      ["id", "1", "'bazhen.csy'", "null", "to_char(a + 1)", "2.3" , "true"]

      id为普通列名，1为整形数字常量，'bazhen.csy'为字符串常量，null为空指针，to_char(a + 1)为表达式，2.3为浮点数，true为布尔值。

      Column必须显示填写，不允许为空！

    • 必选：是

    • 默认值：无

  • splitPk

    • 描述：PostgreSQLReader进行数据抽取时，如果指定splitPk，表示用户希望使用splitPk代表的字段进行数据分片，CDP因此会启动并发任务进行数据同步，这样可以大大提供数据同步的效能。

      推荐splitPk用户使用表主键，因为表主键通常情况下比较均匀，因此切分出来的分片也不容易出现数据热点。

      目前splitPk仅支持整形、字符串型数据切分，不支持浮点、日期等其他类型。如果用户指定其他非支持类型，PostgreSQLReader将报错！

      splitPk如果不填写，将视作用户不对单表进行切分，PostgreSQLReader使用单通道同步全量数据。

    • 必选：否

    • 默认值：空

  • where

    • 描述：筛选条件，PostgreSQLReader根据指定的column、table、where条件拼接SQL，并根据这个SQL进行数据抽取。例如在做测试时，可以将where条件指定为limit 10；在实际业务场景中，往往会选择当天的数据进行同步，可以将where条件指定为gmt_create > $bizdate ;

      where条件可以有效地进行业务增量同步。where条件不配置或者为空，视作全表同步数据。

    • 必选：否

    • 默认值：无

  • querySql

    • 描述：在有些业务场景下，where这一配置项不足以描述所筛选的条件，用户可以通过该配置型来自定义筛选SQL。当用户配置了这一项之后，CDP系统就会忽略table，column这些配置型，直接使用这个配置项的内容对数据进行筛选，例如需要进行多表join后同步数据，使用select a,b from table_a join table_b on table_a.id = table_b.id

      当用户配置querySql时，PostgreSQLReader直接忽略table、column、where条件的配置。

    • 必选：否

    • 默认值：无

  • fetchSize

    • 描述：该配置项定义了插件和数据库服务器端每次批量数据获取条数，该值决定了CDP和服务器端的网络交互次数，能够较大的提升数据抽取性能。注意，该值过大(>2048)可能造成CDP进程OOM。
    • 必选：否
    • 默认值：512
3.3 类型转换

目前PostgreSQLReader支持大部分PostgreSQL类型，但也存在部分个别类型没有支持的情况，请注意检查你的类型。

下面列出PostgreSQLReader针对PostgreSQL类型转换列表:

CDP 内部类型 PostgreSQL 数据类型
Long bigint, bigserial, integer, smallint, serial
Double double precision, money, numeric, real
String varchar, char, text, bit, inet
Date date, time, timestamp
Boolean bool
Bytes bytea

请注意:

  • 除上述罗列字段类型外，其他类型均不支持; money,inet,bit需用户使用a_inet::varchar类似的语法转换。

4 性能报告

4.1 环境准备
4.1.1 数据特征

建表语句：

create table pref_test( 
 id serial, 
 a_bigint bigint, 
 a_bit bit(10), 
 a_boolean boolean, 
 a_char character(5), 
 a_date date, 
 a_double double precision, 
 a_integer integer, 
 a_money money, 
 a_num numeric(10,2),
 a_real real, 
 a_smallint smallint, 
 a_text text, 
 a_time time, 
 a_timestamp timestamp
)
4.1.2 机器参数

  • 执行CDP的机器参数为:

    1. cpu: 16核 Intel(R) Xeon(R) CPU E5620  @ 2.40GHz
2. mem: MemTotal: 24676836kB    MemFree: 6365080kB
3. net: 百兆双网卡

  • PostgreSQL数据库机器参数为:

    D12 24逻辑核  192G内存 12*480G SSD 阵列
4.2 测试报告
4.2.1 单表测试报告
通道数 是否按照主键切分 CDP速度(Rec/s) CDP流量(MB/s) CDP机器运行负载
1 否 10211 0.63 0.2
1 是 10211 0.63 0.2
4 否 10211 0.63 0.2
4 是 40000 2.48 0.5
8 否 10211 0.63 0.2
8 是 78048 4.84 0.8

说明：

  1. 这里的单表，主键类型为 serial，数据分布均匀。
  2. 对单表如果没有按照主键切分，那么配置通道个数不会提升速度，效果与1个通道一样。

请注意:

  • 除上述罗列字段类型外，其他类型均不支持。

5 约束限制

5.1 主备同步数据恢复问题

主备同步问题指PostgreSQL使用主从灾备，备库从主库不间断通过binlog恢复数据。由于主备数据同步存在一定的时间差，特别在于某些特定情况，例如网络延迟等问题，导致备库同步恢复的数据与主库有较大差别，导致从备库同步的数据不是一份当前时间的完整镜像。

CDP如果同步的是阿里云提供RDS，是直接从主库读取数据，不存在数据恢复问题。但是会引入主库负载问题，请注意流控配置。

5.2 一致性约束

PostgreSQL在数据存储划分中属于RDBMS系统，对外可以提供强一致性数据查询接口。例如当一次同步任务启动运行过程中，当该库存在其他数据写入方写入数据时，PostgreSQLReader完全不会获取到写入更新数据，这是由于数据库本身的快照特性决定的。关于数据库快照特性，请参看MVCC Wikipedia

上述是在PostgreSQLReader单线程模型下数据同步一致性的特性，由于PostgreSQLReader可以根据用户配置信息使用了并发数据抽取，因此不能严格保证数据一致性：当PostgreSQLReader根据splitPk进行数据切分后，会先后启动多个并发任务完成数据同步。由于多个并发任务相互之间不属于同一个读事务，同时多个并发任务存在时间间隔。因此这份数据并不是完整的、一致的数据快照信息。

针对多线程的一致性快照需求，在技术上目前无法实现，只能从工程角度解决，工程化的方式存在取舍，我们提供几个解决思路给用户，用户可以自行选择：

  1. 使用单线程同步，即不再进行数据切片。缺点是速度比较慢，但是能够很好保证一致性。
  2. 关闭其他数据写入方，保证当前数据为静态数据，例如，锁表、关闭备库同步等等。缺点是可能影响在线业务。
5.3 数据库编码问题

PostgreSQL在服务器端只支持两种简体中文编码: EUC_CN和UTF-8，PostgreSQLReader底层使用JDBC进行数据抽取，JDBC天然适配各类编码，并在底层进行了编码转换。因此PostgreSQLReader不需用户指定编码，可以自动获取编码并转码。

对于PostgreSQL底层写入编码和其设定的编码不一致的混乱情况，PostgreSQLReader对此无法识别，对此也无法提供解决方案，对于这类情况，导出有可能为乱码。

5.4 增量数据同步

PostgreSQLReader使用JDBC SELECT语句完成数据抽取工作，因此可以使用SELECT...WHERE...进行增量数据抽取，方式有多种：

  • 数据库在线应用写入数据库时，填充modify字段为更改时间戳，包括新增、更新、删除(逻辑删)。对于这类应用，PostgreSQLReader只需要WHERE条件跟上一同步阶段时间戳即可。
  • 对于新增流水型数据，PostgreSQLReader可以WHERE条件后跟上一阶段最大自增ID即可。

对于业务上无字段区分新增、修改数据情况，PostgreSQLReader也无法进行增量数据同步，只能同步全量数据。

5.5 Sql安全性

PostgreSQLReader提供querySql语句交给用户自己实现SELECT抽取语句，PostgreSQLReader本身对querySql不做任何安全性校验。这块交由CDP用户方自己保证。

FAQ

Q: PostgreSQLReader同步报错，报错信息为XXX

A: 网络或者权限问题，请使用PostgreSQL命令行测试： 

psql -h ${pgHost} -p${pgPort} dbname=${pgDb} user=${pgUser} password=${pgPwd} --command='select * from yourtable'

"

如果上述命令也报错，那可以证实是环境问题，请联系你的DBA。

Q: 我想同步PostgreSQL增量数据，怎么配置?

A: PostgreSQLReader必须业务支持增量字段CDP才能同步增量，例如在淘宝大部分业务表中，通过gmt_modified字段表征这条记录的最新修改时间，那么CDP PostgreSQLReader只需要配置where条件为

    "where": "add_time = '2014-06-01'"

Q: 上述bizdate代表什么意思? 我每天需要同步的gmt_modified值肯定不一样的，如何做到每天使用不同变量值

A: CDP支持自定义变量，请参考CDP Console 中有关自定义变量章节说明。

Q: 我有1亿条数据需要同步，大概需要同步多长时间

A: 和数据库配置、CDP机器负载相关，请参考上述性能章节

Q: 为什么不推荐使用默认列*, 会有问题吗

A: 如果你限定了CDP同步a,b,c三列，那么数据库在添加列，修改列，删除列情况，CDP可以做到要么立即报错，要么兼容老情况。如果直接配置星号，上游的数据库变量会立刻影响下游数据！而且可能会在下游任务运行到一段时间才报错，可能已经引入了大量脏数据。

Q: PostgreSQLReader同步出现乱码，怎么处理

A: 通常情况下是你同步的数据库没有按照规范配置编码，比如数据库配置的编码是UTF8，但是底层物理文件实际存放的编码是GBK，CDP按照UTF-8读取数据就会出现乱码。此时你应该寻求你的DBA修改库的编码格式。

最后更新：2016-11-23 16:03:59

  上一篇：go SQLServerReader__Reader插件_使用手册_数据集成-阿里云
  下一篇：go ODPSReader__Reader插件_使用手册_数据集成-阿里云