singer tap-minio-csv 使用
使用tap-minio-csv 我们可以将s3 中csv 的文件，通过singer 的target 写到不用的系统中，可以兼容
s3 的存储类型,以下是一个集成minio 的测试，将minio 中的csv 数据导入到pg中
环境准备
- docker-compose 文件
version: "3"
services:
s3:
image: minio/minio
command: server /data
ports:
- "9000:9000"
environment:
- "MINIO_ACCESS_KEY=dalongapp"
- "MINIO_SECRET_KEY=dalongapp"
volumes:
- "./data:/data"
target:
image: postgres:9.6.11
ports:
- "5432:5432"
environment:
- "POSTGRES_PASSWORD:dalong"
- 创建bucket 并上传文件
- 文件格式
my_table.csv
id,username,userage2,classinfo
1,"dalong",11,"v1"
2,"rong",29,"v2"
3,"appdemo",30,"v3"
4,"tetst",30,"v4"
my_table2.csv
id2,username2,userage3,classinfo2
7,"dalong",11,"v1"
8,"rong",29,"v2"
9,"appdemo",30,"v3"
10,"tetst",30,"v4"
tap 环境准备
- venv 初始化
mkdir s3-tap
python3 -m venv venv
- 激活虚拟环境
source venv/bin/activate
- 安装tap
pip install tap-minio-csv
- 添加tap 配置
主要是关于文件读取以及s3 连接信息，详细文档参考https://github.com/rongfengliang/tap-minio-csv/blob/master/README.md
{
"start_date": "2017-11-02T00:00:00Z",
"bucket": "demo",
"aws_access_key_id":"dalongapp",
"aws_secret_access_key":"dalongapp",
"endpoint_url":"http://localhost:9000",
"tables": "[{\"search_prefix\":\"exports\",\"search_pattern\":\"my_table.csv\",\"table_name\":\"my_table\",\"key_properties\":\"id\",\"delimiter\":\",\"},{\"search_prefix\":\"exports\",\"search_pattern\":\"my_table2.csv\",\"table_name\":\"my_table2\",\"key_properties\":\"id2\",\"delimiter\":\",\"}]"
}
- 简单说明
我们通过 tables 可以定义文件查找的方法以及csv 的处理规则
target 配置
- venv 初始化
mkdir pg-target
python3 -m venv venv
- 激活虚拟环境
source venv/bin/activate
- 安装tap
pip install target_postgres
- 配置target
{
"host": "localhost",
"port": 5432,
"dbname": "postgres",
"user": "postgres",
"password": "dalong",
"schema": "copy"
}
集成使用
- 模式发现
s3-tap/venv/bin/tap-minio-csv -c s3-tap/tap-config.json -d > catalog.json
效果
WARNING I have direct access to the bucket without assuming the configured role.
INFO Starting discover
INFO Sampling records to determine table schema.
INFO Sampling files (max files: 5)
INFO Checking bucket "demo" for keys matching "my_table.csv"
INFO exports/my_table.csv
INFO 2019-08-22 05:27:52+00:00
INFO Will download key "exports/my_table.csv" as it was last modified 2019-08-22 05:27:52+00:00
INFO Sampling exports/my_table.csv (max records: 1000, sample rate: 5)
INFO Sampled 1 rows from exports/my_table.csv
INFO exports/my_table2.csv
INFO 2019-08-22 09:01:45+00:00
INFO Found 2 files.
INFO Sampling records to determine table schema.
INFO Sampling files (max files: 5)
INFO Checking bucket "demo" for keys matching "my_table2.csv"
INFO exports/my_table.csv
INFO 2019-08-22 05:27:52+00:00
INFO exports/my_table2.csv
INFO 2019-08-22 09:01:45+00:00
INFO Will download key "exports/my_table2.csv" as it was last modified 2019-08-22 09:01:45+00:00
INFO Sampling exports/my_table2.csv (max records: 1000, sample rate: 5)
INFO Sampled 1 rows from exports/my_table2.csv
INFO Found 2 files.
INFO Finished discover
- 启用同步配置
主要是启用selected，在每个stream 的metadata 中
"metadata": [
{
"breadcrumb": [],
"metadata": {
"table-key-properties": [
"id"
],
+ "selected":true
}
}
完整如下：
{
"streams": [
{
"stream": "my_table",
"tap_stream_id": "my_table",
"schema": {
"type": "object",
"properties": {
"id": {
"type": [
"null",
"integer",
"string"
]
},
"username": {
"type": [
"null",
"string"
]
},
"userage2": {
"type": [
"null",
"integer",
"string"
]
},
"classinfo": {
"type": [
"null",
"string"
]
},
"_sdc_source_bucket": {
"type": "string"
},
"_sdc_source_file": {
"type": "string"
},
"_sdc_source_lineno": {
"type": "integer"
},
"_sdc_extra": {
"type": "array",
"items": {
"type": "string"
}
}
}
},
"metadata": [
{
"breadcrumb": [],
"metadata": {
"table-key-properties": [
"id"
],
"selected":true
}
},
{
"breadcrumb": [
"properties",
"id"
],
"metadata": {
"inclusion": "automatic"
}
},
{
"breadcrumb": [
"properties",
"username"
],
"metadata": {
"inclusion": "available"
}
},
{
"breadcrumb": [
"properties",
"userage2"
],
"metadata": {
"inclusion": "available"
}
},
{
"breadcrumb": [
"properties",
"classinfo"
],
"metadata": {
"inclusion": "available"
}
},
{
"breadcrumb": [
"properties",
"_sdc_source_bucket"
],
"metadata": {
"inclusion": "available"
}
},
{
"breadcrumb": [
"properties",
"_sdc_source_file"
],
"metadata": {
"inclusion": "available"
}
},
{
"breadcrumb": [
"properties",
"_sdc_source_lineno"
],
"metadata": {
"inclusion": "available"
}
},
{
"breadcrumb": [
"properties",
"_sdc_extra"
],
"metadata": {
"inclusion": "available"
}
}
]
},
{
"stream": "my_table2",
"tap_stream_id": "my_table2",
"schema": {
"type": "object",
"properties": {
"id2": {
"type": [
"null",
"integer",
"string"
]
},
"username2": {
"type": [
"null",
"string"
]
},
"userage3": {
"type": [
"null",
"integer",
"string"
]
},
"classinfo2": {
"type": [
"null",
"string"
]
},
"_sdc_source_bucket": {
"type": "string"
},
"_sdc_source_file": {
"type": "string"
},
"_sdc_source_lineno": {
"type": "integer"
},
"_sdc_extra": {
"type": "array",
"items": {
"type": "string"
}
}
}
},
"metadata": [
{
"breadcrumb": [],
"metadata": {
"table-key-properties": [
"id2"
],
"selected":true
}
},
{
"breadcrumb": [
"properties",
"id2"
],
"metadata": {
"inclusion": "automatic"
}
},
{
"breadcrumb": [
"properties",
"username2"
],
"metadata": {
"inclusion": "available"
}
},
{
"breadcrumb": [
"properties",
"userage3"
],
"metadata": {
"inclusion": "available"
}
},
{
"breadcrumb": [
"properties",
"classinfo2"
],
"metadata": {
"inclusion": "available"
}
},
{
"breadcrumb": [
"properties",
"_sdc_source_bucket"
],
"metadata": {
"inclusion": "available"
}
},
{
"breadcrumb": [
"properties",
"_sdc_source_file"
],
"metadata": {
"inclusion": "available"
}
},
{
"breadcrumb": [
"properties",
"_sdc_source_lineno"
],
"metadata": {
"inclusion": "available"
}
},
{
"breadcrumb": [
"properties",
"_sdc_extra"
],
"metadata": {
"inclusion": "available"
}
}
]
}
]
}
- 执行同步
s3-tap/venv/bin/tap-minio-csv -c s3-tap/tap-config.json -p catalog.json | pg-target/venv/bin/target-postgres -c pg-target/target.json
效果：
WARNING I have direct access to the bucket without assuming the configured role.
INFO Starting sync.
INFO my_table: Starting sync
INFO Syncing table "my_table".
INFO Getting files modified since 2017-11-02 00:00:00+00:00.
INFO Checking bucket "demo" for keys matching "my_table.csv"
INFO Table 'my_table' does not exist. Creating
INFO exports/my_table.csv
INFO 2019-08-22 05:27:52+00:00
INFO Will download key "exports/my_table.csv" as it was last modified 2019-08-22 05:27:52+00:00
INFO exports/my_table2.csv
INFO 2019-08-22 09:01:45+00:00
INFO Found 2 files.
INFO Syncing file "exports/my_table.csv".
INFO Wrote 4 records for table "my_table".
INFO my_table: Completed sync (4 rows)
INFO my_table2: Starting sync
INFO Syncing table "my_table2".
INFO Getting files modified since 2017-11-02 00:00:00+00:00.
INFO Checking bucket "demo" for keys matching "my_table2.csv"
INFO Table 'my_table2' does not exist. Creating
INFO exports/my_table.csv
INFO 2019-08-22 05:27:52+00:00
INFO exports/my_table2.csv
INFO 2019-08-22 09:01:45+00:00
INFO Will download key "exports/my_table2.csv" as it was last modified 2019-08-22 09:01:45+00:00
INFO Found 2 files.
INFO Syncing file "exports/my_table2.csv".
INFO Wrote 4 records for table "my_table2".
INFO my_table2: Completed sync (4 rows)
INFO Done syncing.
INFO Loading 4 rows into 'my_table'
INFO COPY my_table_temp ("_sdc_extra", "_sdc_source_bucket", "_sdc_source_file", "_sdc_source_lineno", "classinfo", "id", "userage2", "username") FROM STDIN WITH (FORMAT CSV, ESCAPE '\')
INFO UPDATE 0
INFO INSERT 0 4
INFO Loading 4 rows into 'my_table2'
INFO COPY my_table2_temp ("_sdc_extra", "_sdc_source_bucket", "_sdc_source_file", "_sdc_source_lineno", "classinfo2", "id2", "userage3", "username2") FROM STDIN WITH (FORMAT CSV, ESCAPE '\')
INFO UPDATE 0
INFO INSERT 0 4
{"bookmarks": {"my_table": {"modified_since": "2019-08-22T05:27:52+00:00"}, "my_table2": {"modified_since": "2019-08-22T09:01:45+00:00"}}}
pg 内容 
说明
以上是一个简单的实践，详细的使用可以参考https://github.com/rongfengliang/tap-minio-csv/blob/master/README.md
参考资料
https://github.com/rongfengliang/tap-minio-csv/blob/master/README.md
https://github.com/rongfengliang/tap-minio-csv-demo
