You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
CREATE TABLE dwd_itop_order_form_details
(
id bigint,
ename varchar,
url varchar
) WITH (
'connector' = 'stream-x'
);
insert into dwd_itop_order_form_details
select a.id
,a.ename
,t.url
from ods_s9132_order_form_details a
cross join unnest(url_data) as t (url)
;
三、执行命令
sh bin/chunjun-local.sh -job chunjun-examples/sql/binlog/itsm_dwd_json.sql
sh bin/chunjun-local.sh -job chunjun-examples/sql/binlog/itsm_dwd_json2.sql
四、操作数据,重现问题
1、mysql源表插入数据
insert into test_jons(id,ename,url_data)
select 4,'phee','["http://json.la/", "http://www.baidu.com/", "https://hub.fastgit.org/"]'
Search before asking
What happened
What you expected to happen
cross join unnest正常使用
How to reproduce
一、环境准备
1、mysql表
CREATE TABLE
test_jons
(id
bigint(20) NOT NULL AUTO_INCREMENT,ename
varchar(50) DEFAULT NULL,url_data
json DEFAULT NULL,PRIMARY KEY (
id
)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
二、脚本配置
1、脚本itsm_dwd_json.sql
[root@t-hadoop01 binlog]# cat itsm_dwd_json.sql
CREATE TABLE stg_s9132_order_form_details
(
id bigint,
ename varchar,
url_data varchar
) WITH (
'connector' = 'binlog-x'
,'username' = 'itsm_repl'
,'password' = 'xxxxx'
,'cat' = 'insert,delete,update'
,'url' = 'jdbc:mysql://172.16.44.7:3306/itsm_db?useSSL=false'
,'host' = '172.16.44.7'
,'port' = '3306'
,'table' = 'itsm_db.test_jons'
,'timestamp-format.standard' = 'SQL'
);
CREATE TABLE ods_s9132_order_form_details
(
id bigint,
ename varchar,
url_data varchar,
primary key (id) NOT ENFORCED
) WITH (
'connector' = 'upsert-kafka-x'
,'topic' = 'flinkcdc-mysql'
,'properties.bootstrap.servers' = '172.16.56.254:34715'
,'key.format' = 'json'
,'value.format' = 'json'
,'value.fields-include' = 'ALL'
);
insert into ods_s9132_order_form_details
select id
,ename
,url_data
from stg_s9132_order_form_details a
;
2、脚本itsm_dwd_json2.sql
[root@t-hadoop01 binlog]# cat itsm_dwd_json2.sql
CREATE TABLE ods_s9132_order_form_details
(
id bigint,
ename varchar,
url_data array
) WITH (
'connector' = 'kafka-x'
,'topic' = 'flinkcdc-mysql'
,'properties.bootstrap.servers' = '172.16.56.254:34715'
,'properties.group.id' = 'dwd_itop_order_form_details'
,'scan.startup.mode' = 'latest-offset'
,'format' = 'json'
,'json.timestamp-format.standard' = 'SQL'
,'scan.parallelism' = '1'
,'json.fail-on-missing-field' = 'true'
,'json.ignore-parse-errors' = 'false'
);
CREATE TABLE dwd_itop_order_form_details
(
id bigint,
ename varchar,
url varchar
) WITH (
'connector' = 'stream-x'
);
insert into dwd_itop_order_form_details
select a.id
,a.ename
,t.url
from ods_s9132_order_form_details a
cross join unnest(url_data) as t (url)
;
三、执行命令
sh bin/chunjun-local.sh -job chunjun-examples/sql/binlog/itsm_dwd_json.sql
sh bin/chunjun-local.sh -job chunjun-examples/sql/binlog/itsm_dwd_json2.sql
四、操作数据,重现问题
1、mysql源表插入数据
insert into test_jons(id,ename,url_data)
select 4,'phee','["http://json.la/", "http://www.baidu.com/", "https://hub.fastgit.org/"]'
2、问题报错
Anything else
No response
Version
1.12_release
Are you willing to submit PR?
Code of Conduct
The text was updated successfully, but these errors were encountered: