flinkSQL解析kafka数据源格式

启动生产者：

cd /usr/hdp/3.0.1.0-187/kafka/
./bin/kafka-console-consumer.sh --bootstrap-server kafka175.data:6667 --topic edu-log-streaming-test

第一种数据源类型
json格式：

{
	"debug": false,
	"env": "product",
	"fncode": "course-publish",
	"fngroup": null,
	"logid": "4b97614d-74ef-4d9d-96e7-76a4b7858",
	"query": {
		"courseStatus": "1",
		"type": "发布",
		"courseId": "361813230299077"
	},
	"send_time": "0",
	"tenantid": "35043183495507966",
	"time": "1684829888",
	"user_id": "34548551468328555"
}

flinkSQL:

SET execution.checkpointing.interval = 5s;
drop table kafkaInputTable3;
create table kafkaInputTable3 (
debug varchar,
env varchar,
fncode varchar,
fngroup varchar,
logid varchar,
query varchar,
send_time varchar,
tenantid varchar,
`time` varchar,
user_id varchar
) with (
'connector' = 'kafka',
'topic' = 'edu-log-streaming-test',
'properties.bootstrap.servers'='kafka175.data:6667,kafka176.data:6667,kafka177.data:6667',
'scan.startup.mode' = 'latest-offset',
'properties.group.id' = 'log-center-01',
'format' = 'json'
);
select * from kafkaInputTable3 ;

第二种数据源类型

{
	"data": [{
		"debug": false,
		"env": "product",
		"fncode": "course-publish",
		"fngroup": null,
		"logid": "4b97614d-74ef-4d9d-96e7-76ab764b7858",
		"query": {
			"courseStatus": "1",
			"type": "发布",
			"courseId": "3618132307951299072"
		},
		"send_time": "0",
		"tenantid": "3504348183495507968",
		"time": "1684824159887",
		"user_id": "3454855359146832856"
	}, {
		"debug": false,
		"env": "product",
		"fncode": "course-publish",
		"fngroup": null,
		"logid": "4b97614d-74ef-4d9d-96e7-76ab764b7858",
		"query": {
			"courseStatus": "1",
			"type": "发布",
			"courseId": "3618132307951299077"
		},
		"send_time": "0",
		"tenantid": "3504348183495507966",
		"time": "1684824159888",
		"user_id": "34548553591468328555"
	}]
}

flinkSQL:

drop table kafkaInputTable3;
create table kafkaInputTable3 (
`data` ARRAY<ROW<logid string,debug string,env string,fncode string ,fngroup string,query string,send_time string,tenantid string,`time` string,user_id string>>
) with (
'connector' = 'kafka',
'topic' = 'edu-log-streaming-test',
'properties.bootstrap.servers'='kafka175.data:6667,kafka176.data:6667,kafka177.data:6667',
'scan.startup.mode' = 'latest-offset',
'properties.group.id' = 'log-center-01',
'format' = 'json'
);
SET execution.result-mode=tableau;
SET execution.result-mode=table;
--处理
select logid ,debug ,env ,fncode  ,fngroup ,query ,send_time ,tenantid ,`time` ,user_id 
from     kafkaInputTable3 CROSS JOIN UNNEST(`data`) AS t (logid ,debug ,env ,fncode  ,fngroup ,query ,send_time ,tenantid ,`time` ,user_id);

第三种数据源类型

{
	"data": [{
		"id": "3610897522400",
		"ex_create_time": "2023-04-14 14:53:11",
		"ex_create_user_id": "39476031396096",
		"ex_update_time": "2023-04-14 14:53:11",
		"ex_update_user_id": "360534996096",
		"del": "N",
		"version": "0",
		"weike_answer_id": "3609975905549197824",
		"time": null,
		"video_view_sec": null,
		"finish": "N",
		"user_id": "3605349476031396096",
		"tenant_id": null,
		"business_id": "jxpt",
		"course_id": "35694903848416",
		"knowledge_id": null,
		"cou_res_id": "3498059950080185",
		"cou_res_type": "WEIKE",
		"name": "2-3-2mp4",
		"resource_type": "CUSTODEO",
		"start_time": "2023-04-14 14:53:11",
		"last_submit_time": null,
		"last_view_sec": null,
		"answer_progress": null,
		"answer_type": "ANSWER"
	}],
	"database": "weswer-basic",
	"es": 16814190000,
	"id": 11812806,
	"isDdl": false,
	"mysqlType": {
		"id": "bigint(21)",
		"ex_create_time": "datetime",
		"ex_create_user_id": "varchar(32)",
		"ex_update_time": "datetime",
		"ex_update_user_id": "varchar(32)",
		"del": "varchar(1)",
		"version": "int(11)",
		"weike_answer_id": "bigint(21)",
		"time": "datetime",
		"video_view_sec": "int(11)",
		"finish": "varchar(1)",
		"user_id": "varchar(32)",
		"tenant_id": "varchar(32)",
		"business_id": "varchar(32)",
		"course_id": "varchar(50)",
		"knowledge_id": "varchar(32)",
		"cou_res_id": "varchar(32)",
		"cou_res_type": "varchar(32)",
		"name": "varchar(200)",
		"resource_type": "varchar(100)",
		"start_time": "datetime",
		"last_submit_time": "datetime",
		"last_view_sec": "int(11)",
		"answer_progress": "decimal(11,2)",
		"answer_type": "varchar(30)"
	},
	"old": null,
	"pkNames": ["id"],
	"sql": "",
	"sqlType": {
		"id": -5,
		"ex_create_time": 93,
		"ex_create_user_id": 12,
		"ex_update_time": 93,
		"ex_update_user_id": 12,
		"del": 12,
		"version": 4,
		"weike_answer_id": -5,
		"time": 93,
		"video_view_sec": 4,
		"finish": 12,
		"user_id": 12,
		"tenant_id": 12,
		"business_id": 12,
		"course_id": 12,
		"knowledge_id": 12,
		"cou_res_id": 12,
		"cou_res_type": 12,
		"name": 12,
		"resource_type": 12,
		"start_time": 93,
		"last_submit_time": 93,
		"last_view_sec": 4,
		"answer_progress": 3,
		"answer_type": 12
	},
	"table": "wkans_anresult",
	"ts": 1681455190680,
	"type": "INSERT"
}

flinkSQL:

create table kafkaInputTable3 (
    p_id VARCHAR primary key,
    p_name VARCHAR,
    flag VARCHAR,
    ts TIMESTAMP
    ) with (
    'connector' = 'kafka',
    'topic' = 'jxppic',
    'properties.bootstrap.servers'='kafka175.data:6667,kafka176.data:6667,kafka177.data:6667',
    'scan.startup.mode' = 'latest-offset',
    'properties.group.id' = 'hast1',
    'format' = 'canal-json',
    'canal-json.ignore-parse-errors' = 'false',
    'canal-json.database.include'='weswer-basic',
    'canal-json.table.include'='wkans_anresult'
    );
    
select * from kafkaInputTable3 ;