DTStack / DTStack/chunjun

[Bug] [sqlservercdcreader] mssqlcdc_kafka.json写入kafka的格式混乱

Open
#1,441 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
Java
Stars
4.1k
Forks
1.7k
PR merge metrics
No merged PRs in 30d

Description

### Search before asking

- [X] I had searched in the [issues](https://github.com/DTStack/chunjun/issues) and found no similar issues.

### What happened

源端插入的数据:
insert into dbo.mkt_city(id,name,del_flag,create_user,create_date,update_user,update_date)
values('38','雅安38',0,'admin','2022-12-07 08:17:00','admin','2022-12-07 08:17:00');
写入kafka的格式是混乱的:
![image](https://user-images.githubusercontent.com/118407740/206347092-ef4c44a8-0a22-4ca4-8216-6a6db116e7b0.png)

### What you expected to happen

local运行模式,json执行脚本,写入kafka的格式是混乱的,需要的格式期待是 字段:
1、值的 json格式,如:
-- {"id":100,"name":"lb james阿道夫","money":293.899778,"dateone":"2020-07-30 10:08:22","age":"33","datethree":"2020-07-30 10:08:22.123","datesix":"2020-07-30 10:08:22.123456","datenigth":"2020-07-30 10:08:22.123456789","dtdate":"2020-07-30","dttime":"10:08:22"}

2、或是debezium json格式
-- {"before":null,"after":{"id":1,"col_bit":true,"col_tinyint":1,"col_smallint":1,"col_mediumint":1,"col_int":1,"col_integer":1,"col_bigint":1,"col_real":1.0,"col_float":1.0,"col_decimal":1,"col_numric":1,"col_double":1.0,"col_char":"a","col_varchar":"a"},"op":"c"}

### How to reproduce

一、环境准备
1、sqlserver版本信息
Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64)
Jun 12 2020 20:39:00
Copyright (C) 2017 Microsoft Corporation
Enterprise Edition (64-bit) on Linux (CentOS Linux 7 (Core))
2、已开启cdc配置
3、源端表结构信息
![image](https://user-images.githubusercontent.com/118407740/206347629-22781657-4015-4183-a237-4b46179e96f5.png)

二、配置脚本
[root@t-hadoop01 sql-script]# cat mssqlcdc_kafka.json
{
"job" : {
"content" : [ {
"reader" : {
"parameter" : {
"databaseName":"GISS_TEST",
"tableList": ["dbo.MKT_CITY"],
"column" : [ {
"name" : "id",
"type" : "varchar"
}, {
"name" : "name",
"type" : "varchar"
}, {
"name" : "del_flag",
"type" : "int"
}, {
"name" : "create_user",
"type" : "varchar"
}, {
"name" : "create_date",
"type" : "datetime"
}, {
"name" : "update_user",
"type" : "varchar"
}, {
"name" : "update_date",
"type" : "datetime"
} ],
"username" : "sa",
"password" : "passwd",
"cat" : "insert,delete,update",
"url" : "jdbc:sqlserver://ip:1433;databaseName=GISS_TEST",
"pollInterval" : 1000
},
"name" : "sqlservercdcreader"
},
"writer": {
"name": "kafkasink",
"parameter": {
"tableFields": [
"id",
"name",
"del_flag",
"create_user",
"create_date",
"update_user",
"update_date"
],
"topic": "test",
'format' = 'json',
"producerSettings": {
"auto.commit.enable": "false",
"bootstrap.servers": "ip1:9092,ip2:9092,ip3:9092"
}
}
}
} ],
"setting" : {
"speed" : {
"bytes" : 0,
"channel" : 1
}
}
}
}

三、执行命令
[root@t-hadoop01 chunjun-dist112]# sh bin/chunjun-local.sh -job sql-script/mssqlcdc_kafka.json

四、执行结果
1、源端插入数据
insert into dbo.mkt_city(id,name,del_flag,create_user,create_date,update_user,update_date)
values('38','雅安38',0,'admin','2022-12-07 08:17:00','admin','2022-12-07 08:17:00');
2、写入kafka的格式是混乱的:
![image](https://user-images.githubusercontent.com/118407740/206347092-ef4c44a8-0a22-4ca4-8216-6a6db116e7b0.png)

### Anything else

_No response_

### Version

1.12_release

### Are you willing to submit PR?

- [ ] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.