Singer 学习一 使用Singer进行mysql 2 postgres 数据转换
Singer 因为版本的问题，推荐的运行方式是使用virtualenv，对于taps&& target 的运行都是
推荐使用此方式，不然包兼容的问题太费事了
备注： 使用docker-compose 运行数据库服务
环境准备
- docker-compose 文件
数据库准备
version: "3"
services:
gogs-service:
image: gogs/gogs
ports:
- "10022:22"
- "10080:3000"
mysql:
image: mysql:5.7.16
ports:
- 3306:3306
command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
environment:
MYSQL_ROOT_PASSWORD: dalongrong
MYSQL_DATABASE: gogs
MYSQL_USER: gogs
MYSQL_PASSWORD: dalongrong
TZ: Asia/Shanghai
postgres:
image: postgres:9.6.11
ports:
- "5432:5432"
environment:
- "POSTGRES_PASSWORD:dalong"
- 安装virtualenv
如果没有安装的话，必须>=python3.5
pip3 install virtualenv
or:
pip install virtualenv
- 配置virtualenv
mysql tap：
virtualenv mysql
source ./mysql/bin/activate
pip install tap-mysql
postgres target:
virtualenv postgres
source ./postgres/bin/activate
pip install target-postgres
- 初始化数据
启动docker-compose 即可，同时配置gogs 就会有问题
配置tap&& target
主要是数据库参数的配置：
- tap
tap.json:
{
"host": "localhost",
"port": "3306",
"user": "root",
"password": "dalongrong"
}
- target
target.json
{
"host": "localhost",
"port": 5432,
"dbname": "postgres",
"user": "postgres",
"password": "postgres",
"schema": "public"
}
tap 同步信息配置
- 生成tap 的properties
使用discover
./mysql/bin/tap-mysql --config config.json --discover > properties.json
- 添加同步配置
为了测试使用的是full_table && 同步了gogs 的repository 表
"breadcrumb": [],
"metadata": {
+ "selected-by-default": true,
"database-name": "gogs",
"row-count": 1,
"is-view": false,
+ "selected": true,
+ "replication-method": "FULL_TABLE",
执行同步操作
- 命令
./mysql/bin/tap-mysql --config tap.json --properties properties.json | ./postgres/bin/target-postgres --config ta
rget.json
- 效果
INFO Server Parameters: version: 5.7.16, wait_timeout: 2700, innodb_lock_wait_timeout: 2700, max_allowed_packet: 4194304, interactive_timeout: 28800
INFO Server SSL Parameters (blank means SSL is not active): [ssl_version: ], [ssl_cipher: ]
INFO Beginning sync for InnoDB table gogs.repository
INFO Stream repository is using full table replication
INFO Table 'repository' exists
INFO Detected auto-incrementing primary key(s) - will replicate incrementally
INFO Running SELECT `external_wiki_url`,`external_tracker_url`,`num_pulls`,`pulls_ignore_whitespace`,`website`,`size`,`enable_external_wiki`,`updated_unix`,`use_custom_avatar`,`is_private`,`external_tracker_style`,`allow_public_issues`,`num_watches`,`description`,`default_branch`,`allow_public_wiki`,`num_milestones`,`num_closed_milestones`,`enable_external_tracker`,`fork_id`,`owner_id`,`is_fork`,`num_issues`,`is_mirror`,`id`,`num_closed_issues`,`name`,`external_tracker_format`,`enable_issues`,`num_stars`,`pulls_allow_rebase`,`lower_name`,`num_closed_pulls`,`enable_pulls`,`is_bare`,`num_forks`,`created_unix`,`enable_wiki` FROM `gogs`.`repository` WHERE `id` <= 1 ORDER BY `id` ASC
/Users/dalong/mylearning/python-virtualenv/mysql/lib/python3.7/site-packages/pymysql/connections.py:1077: UserWarning: Previous unbuffered result was left incomplete
warnings.warn("Previous unbuffered result was left incomplete")
INFO METRIC: {"type": "counter", "metric": "record_count", "value": 1, "tags": {"database": "gogs", "table": "repository"}}
INFO METRIC: {"type": "timer", "metric": "job_duration", "value": 0.054605960845947266, "tags": {"job_type": "sync_table", "database": "gogs", "table": "repository", "status": "succeeded"}}
INFO Loading 1 rows into 'repository'
INFO COPY repository_temp ("allow_public_issues", "allow_public_wiki", "created_unix", "default_branch", "description", "enable_external_tracker", "enable_external_wiki", "enable_issues", "enable_pulls", "enable_wiki", "external_tracker_format", "external_tracker_style", "external_tracker_url", "external_wiki_url", "fork_id", "id", "is_bare", "is_fork", "is_mirror", "is_private", "lower_name", "name", "num_closed_issues", "num_closed_milestones", "num_closed_pulls", "num_forks", "num_issues", "num_milestones", "num_pulls", "num_stars", "num_watches", "owner_id", "pulls_allow_rebase", "pulls_ignore_whitespace", "size", "updated_unix", "use_custom_avatar", "website") FROM STDIN WITH (FORMAT CSV, ESCAPE '\')
INFO UPDATE 1
INFO INSERT 0 0
{"currently_syncing": null, "bookmarks": {"gogs-repository": {"initial_full_table_complete": true}}}
- 数据库数据
说明
singer 的tap 以及target 还是挺多的，同时官方的文档也还不错。
参考资料
https://github.com/datamill-co/target-postgres
https://github.com/singer-io/tap-mysql
https://github.com/singer-io/getting-started/blob/master/docs/RUNNING_AND_DEVELOPING.md#running-a-singer-tap-with-a-singer-target
https://www.singer.io/
