注意:以下文档只适用于TOP接口,请谨慎使用!

文档中心 > 聚石塔

导入数据

更新时间:2023/06/06 访问次数:8365

一、从 MySQL 导入

1. mysql2pgsql


工具 mysql2pgsql 支持不落地的把 MYSQL 中的表迁移到 HybridDB for PostgreSQL/Greenplum Database/PostgreSQL/PPAS。此工具的原理是,同时连接源端 mysql 数据库和目的端数据库,从 mysql 库中通过查询得到要导出的数据,然后通过 COPY 命令导入到目的端。此工具支持多线程导入(每个工作线程负责导入一部分数据库表)。


2. 参数配置


修改配置文件 my.cfg、配置源和目的库连接信息。

1)源库 mysql 的连接信息如下:

注意:源库 mysql 的连接信息中,用户需要有对所有用户表的读权限。


[src.mysql]
host = "192.168.1.1"
port = "3306"
user = "test"
password = "test"
db = "test"
encodingdir = "share"
encoding = "utf8"


2)目的库 pgsql (包括 Postgresql、PPAS 和 HybridDB for PostgreSQL )的连接信息如下:

注意:目的库 pgsql 的连接信息,用户需要对目标表有写的权限。


[desc.pgsql]
connect_string = "host=192.168.1.1 dbname=test port=5888 user=test password=pgsql"


3. mysql2pgsql 用法

mysql2pgsql 的用法如下所示:


./mysql2pgsql -l <tables_list_file> -d -n -j <number of threads> -s <schema of target able>


参数说明:

1)-l:可选参数,指定一个文本文件,文件中含有需要同步的表;如果不指定此参数,则同步配置文件中指定数据库下的所有表。<tables_list_file>为一个文件名,里面含有需要同步的表集合以及表上查询的条件,其内容格式示例如下:


table1 : select * from table_big where column1 < '2016-08-05'
table2 :
table3
table4: select column1, column2 from tableX where column1 != 10
table5: select * from table_big where column1 >= '2016-08-05'


2)-d:可选参数,表示只生成目的表的建表 DDL 语句,不实际进行数据同步。

3)-n:可选参数,需要与-d 一起使用,指定在 DDL 语句中不包含表分区定义。

4)-j:可选参数,指定使用多少线程进行数据同步;如果不指定此参数,会使用 5 个线程并发。

5)-s:可选参数,指定目标表的 schema,一次命令只能指定一个 schema。如果不指定此参数,则数据会导入到 public 下的表。


典型用法

全库迁移

全库迁移的操作步骤如下所示:

i)通过如下命令,获取目的端对应表的 DDL。


./mysql2pgsql -d


ii)根据这些 DDL,再加入 distribution key 等信息,在目的端创建表。

iii)执行如下命令,同步所有表:


./mysql2pgsql


iv)此命令会把配置文件中所指定数据库中的所有 mysql 表数据迁移到目的端。过程中使用 5 个线程(即缺省线程数为 5),读取和导入所有涉及的表数据。


部分表迁移

i)编辑一个新文件 tab_list.txt,放入如下内容:


t1
t2 : select * from t2 where c1 > 138888


ii)执行如下命令,同步指定的 t1 和 t2 表(注意 t2 表只迁移符合 c1 > 138888 条件的数据):


./mysql2pgsql -l tab_list.txt


4. 下载与说明


1)下载 mysql2pgsql 二进制安装包下载,请单击 这里

2)查看 mysql2pgsql 源码编译说明,请单击 这里


二、从 PostgreSQL 导入


工具 pgsql2pgsql 支持不落地的把 HybridDB for PostgreSQL/Greenplum Database/PostgreSQL/PPAS 中的表迁移到 HybridDB for PostgreSQL/Greenplum Database/PostgreSQL/PPAS。


1. pgsql2pgsql 支持的功能


pgsql2pgsql 支持如下功能:

1)PostgreSQL/PPAS/Greenplum Database/HybridDB for PostgreSQL 全量数据迁移到 PostgreSQL/PPAS/Greenplum Database/HybridDB for PostgreSQL。

2)PostgreSQL/PPAS(版本大于9.4)全量 + 增量迁移到 PostgreSQL/PPAS。


2. 参数配置


修改配置文件 my.cfg、配置源和目的库连接信息。

1)源库 pgsql 连接信息如下所示:

注意:源库 pgsql 的连接信息中,用户最好是对应 DB 的 owner。


[src.pgsql]
connect_string = "host=192.168.1.1 dbname=test port=5888 user=test password=pgsql"


2)本地临时 Database pgsql 连接信息如下所示:


[local.pgsql]
connect_string = "host=192.168.1.1 dbname=test port=5888 user=test2 password=pgsql"


3)目的库 pgsql 连接信息如下所示:

注意:目的库 pgsql 的连接信息,用户需要对目标表有写权限。


[desc.pgsql]
connect_string = "host=192.168.1.1 dbname=test port=5888 user=test3 password=pgsql"


注意:

① 如果要做增量数据同步,连接源库需要有创建 replication slot 的权限。

② 由于 PostgreSQL 9.4 及以上版本支持逻辑流复制,所以支持作为数据源的增量迁移。打开下列内核参数才能让内核支持逻辑流复制功能。
wal_level = logical
max_wal_senders = 6
max_replication_slots = 6


3. pgsql2pgsql 用法

1)全库迁移

进行全库迁移,请执行如下命令:


./pgsql2pgsql


迁移程序会默认把对应 pgsql 库中所有用户的表数据将迁移到 pgsql。


2)状态信息查询

连接本地临时 Database,可以查看到单次迁移过程中的状态信息。这些信息被放在表 db_sync_status 中,包括全量迁移的开始和结束时间、增量迁移的开始时间和增量同步的数据情况。


4. 下载与说明


1)下载 mysql2pgsql 二进制安装包,请单击 这里

2)查看 mysql2pgsql 源码编译说明,请单击 这里

 

三、使用数据集成同步数据


数据集成(Data Integration) 是阿里云大数据服务提供的数据同步平台。该平台为 20 多种数据源提供不同网络环境下的离线 (全量/增量) 数据进出通道,可跨异构数据存储系统、可弹性扩展、可靠、安全、成本低。查看 支持数据源类型 了解可用的数据源。

本文介绍了使用数据集成向 HybridDB for PostgreSQL 进行 数据导入 数据导出 的实现方法,分别提供在 向导模式(即可视化界面引导)下的操作步骤 和 脚本模式(即模板参数配置) 下的代码示例。

使用该文档,您可以了解数据集成在 HybridDB for PostgreSQL 中的 使用场景,熟悉以下操作:

1)在数据集成和 HybridDB for PostgreSQL端部署 准备工作;

2)在数据集成中 新增 HybridDB for PostgreSQL 数据源;

3)使用数据集成向 HybridDB for PostgreSQL 导入数据。


1. 使用场景

使用数据集成中的同步任务,HybridDB for PostgreSQL 可以:

1)将数据同步到到其他的数据源里,并对数据进行相应处理。

2)将处理好的其他数据源数据同步到 HybridDB for PostgreSQL 中。


2. 准备工作


分别在数据集成和 HybridDB for PostgreSQL 端完成以下准备工作。


数据集成

依次完成以下操作:

1)开通阿里云官网实名认证账号,并且创建好账号的访问秘钥,即 AccessKeys。

2)开通 MaxCompute,系统会自动产生一个默认的 ODPS 的数据源,并使用主账号登录大数据开发套件。

3)创建项目,用户可以在项目中协作完成工作流,共同维护数据和任务等,因此使用大数据开发套件之前需要先创建一个项目。

4)如您想通过子账号创建数据集成任务,可以赋予其相应的权限。

 

3. HybridDB for PostgreSQL


完成以下准备工作:

1)在进行数据导入之前,先通过 PostgreSQL 客户端创建好 HybridDB for PostgreSQL 中待迁入数据的目标数据库和表。

2)若待迁出数据的源数据库为 HybridDB for PostgreSQL,应在 HybridDB for PostgreSQL 管理控制台进行 IP 白名单设置。
如下图所示,登录 HybridDB for PostgreSQL 控制台,选择相应实例,在 数据安全性 页面的 白名单设置 子页下,单击 添加白名单分组。添加以下 IP 地址:10.152.69.0/24,10.153.136.0/24,10.143.32.0/24,120.27.160.26,10.46.67.156,120.27.160.81,10.46.64.81,121.43.110.160,10.117.39.238,121.43.112.137,10.117.28.203,118.178.84.74,10.27.63.41,118.178.56.228,10.27.63.60,118.178.59.233,10.27.63.38,118.178.142.154,10.27.63.15,100.64.0.0/8


image.png


注意:若使用自定义资源组调度 HybridDB for PostgreSQL 数据同步任务,必须把自定义资源组的机器 ip 也加到 HybridDB for PostgreSQL 的白名单中。


4. 新增数据源


使用数据集成向 HybridDB for PostgreSQL 同步数据前,项目管理员应在数据集成中新增 HybridDB for PostgreSQL 数据源,具体步骤如下:

1)以开发者身份登录阿里云数加平台,依次选择 大数据开发套件 > 管理控制台,单击对应项目操作栏中的 进入工作区

2)单击顶部菜单栏中数据集成模块的数据源。

3)单击 新增数据源

4)在新建数据源弹出框中,选择 数据源类型 PostgreSQL

5)选择以 JDBC 形式配置该 PostgreSQL 数据源。


image.png


其中,

① 数据源名称:由英文字母、数字、下划线组成且需以字符或下划线开头,长度不超过 60 个字符 。

② 数据源描述: 对数据源进行简单描述,不超过 80 个字符 。

③ 数据源类型:当前选择的数据源类型 PostgreSQL。

④ 网络类型:当前选择的网络类型。

⑤ JDBCUrl:JDBC 连接信息,格式为:jdbc:PostgreSQL://IP:Port/database。

⑥ 用户名/密码:数据库对应的用户名和密码。

6)完成配置后,单击 测试连通性

7)测试连通性通过后,单击 确定

至此,您已完成 HybridDB for PostgreSQL 数据源的添加。


5. 通过数据集成导入数据


下文提供两种配置同步任务的方法,供您选择:

i)若使用可视化界面引导操作,请参照 向导模式配置同步任务。包含以下步骤:选择来源,选择目标,字段映射,通道控制,预览保存。不同的数据源之间,每一步的界面可能有不同的内容。向导模式可以转换成脚本模式。

ii)若使用模板参数配置操作,请参照 脚本模式配置同步任务。在脚本界面选择相应的模板,模板包含了同步任务的主要参数,配置参数信息以实现同步任务配置。脚本模式不能转化成向导模式。


1)前提条件

已参照 新增数据源 在数据集成中新增 HybridDB for PostgreSQL 数据源。

 

2)向导模式配置同步任务

操作步骤如下:

① 选择以 向导模式 新建同步任务,如下图所示:


image.png


② 选择数据来源,如下图所示:


image.png


其中,

i) 数据源:选择 odps_first(odps),即 MaxCompute。

ii) :选择 hpg

iii)数据预览:默认是收起的,单击可展开。

③ 完成后单击 下一步

④ 选择目标,如下图所示:


image.png


其中,

i)数据源:选择 I_PostGreSql(postgresql)

ii)表:选择 public.person

iii)导入前准备语句:输入执行数据同步任务之前执行的 SQL 语句。

注意:向导模式只允许执行一条 SQL 语句,脚本模式可以支持多条 SQL 语句,例如清除旧数据。

iv)导入后准备语句:输入执行数据同步任务之后执行的 SQL 语句。

注意:向导模式只允许执行一条 SQL 语句,脚本模式可以支持多条 SQL 语句,例如加上某一个时间戳。

v)主键冲突:选择 Insert Into,当主键/唯一性索引冲突,数据集成视为脏数据进行处理。

⑤ 完成后单击 下一步

⑥ 字段映射。对字段映射关系进行配置,左侧 源头表字段 和右侧 目标表字段 为一一对应的关系,如下图所示:


image.png


说明:

a)可以输入常量,输入的值需要使用英文单引号包括,如 ‘abc’、’123’等;

b)可以配合调度参数使用,如 ${bdp.system.bizdate} 等;

c)可以输入你要同步的分区列,如分区列有 pt 等;

d)如果您输入的值无法解析,则类型显示为 ‘未识别’;

e)不支持配置 odps 函数。


完成后单击 下一步

⑦ 通道控制。配置作业速率上限和脏数据检查规则,如下图所示:


image.png


其中,

i)作业速率上限:设置数据同步作业可能达到的最高速率,其最终实际速率受网络环境、数据库配置等的影响。

ii)作业并发数:作业速率上限 = 作业并发数 * 单并发的传输速率


当作业速率上限已选定的情况下,应该如何选择作业并发数?

a)如果你的数据源是线上的业务库,建议您不要将并发数设置过大,以防对线上库造成影响。

b)如果您对数据同步速率特别在意,建议您选择最大作业速率上限和较大的作业并发数。


⑧ 预览保存。完成以上配置后,上下滚动鼠标可查看任务配置,如若无误,单击 保存,如下图所示:


image.png


⑨ 获取结果。同步任务保存后,

i)单击 运行任务 会立刻运行。

ii)单击右边的 提交,会将同步任务提交到调度系统中。

注意:调度系统会按照配置属性在从第二天开始自动定时执行。相关调度的配置请参考 调度配置介绍


⑩ 运行结果如下图所示:


image.png


至此,您已完成在向导模式下创建数据同步任务向 HybridDB for PostgreSQL 导入数据。

 

3)脚本模式配置同步任务

代码样例如下:


{
"configuration": {
"reader": {
"plugin": "odps",
"parameter": {
"partition": "pt=${bdp.system.bizdate}",//分区信息
"datasource": "odps_first",//数据源名,建议数据源都先添加数据源后再配置同步任务,此配置项填写的内容必须要与添加的数据源名称保持一致
"column": [
"id",
"name",
"year",
"birthdate",
"ismarried",
"interest",
"salary"
],
"table": "hpg"//源端表名
}
},
"writer": {
"plugin": "postgresql",
"parameter": {
"postSql": [],//导入后准备语句
"datasource": "l_PostGreSql",//数据源名,建议数据源都先添加数据源后再配置同步任务,此配置项填写的内容必须要与添加的数据源名称保持一致
"column": [
"id",
"name",
"year",
"birthdate",
"ismarried",
"interest",
"salary"
],
"table": "public.person",//目标表名
"preSql": []//导入前准备语句
}
},
"setting": {
"speed": {
"concurrent": 7,//并发数
"mbps": 7//数率最高上限
}
}
},
"type": "job",
"version": "1.0"
}


6. 相关文档


1)使用 OSS 外部表同步数据

2)从 MySQL 导入

3)使用 COPY 命令导入数据

4)从 PostgreSQL 导入


FAQ

关于此文档暂时还没有FAQ
返回
顶部