Dinky使用——mysql2clickhouse
要求:通过Dinky把mysql的数据导到clickhouse数据库
对于dinky的安装部署,以及dinky集成clickhouse数据库需要那些jar包可以参考:https://www.cnblogs.com/braveym/p/16455534.html
这里我需要提醒,需要我们提前把mysql和clickhouse的表创建好
一、创建作业
二、在作业里编写flinksql代码
--创建clickhouse的连接信息 CREATE TABLE t_sink( `id` STRING , `name` STRING , `age` INT ) WITH ( 'connector' = 'clickhouse', 'url' = 'jdbc:clickhouse://172.16.119.30:8123/dinky?socket_timeout=300000', 'table-name' = 'test_out', 'username' = 'default', 'password' = 'clickhouse', 'format'='json' ); --mysql的连接 CREATE TABLE t_source ( `id` STRING , `name` STRING , `age` INT ) WITH ( 'connector' = 'jdbc', 'url' = 'jdbc:mysql://172.16.119.26:3306/test?createDatabaseIfNotExist=true&useSSL=false', 'username' = 'root', 'password' = 'root', 'table-name' = 'test_out' ); insert into t_sink select * from t_source ;
三、执行任务
可以打开flink页面查看任务运行情况,这里显示我的任务运行结束了,
如果失败了或者报错了,可以通过flink页面这边查看错误日志信息
先点击任务
点击异常信息按钮查看日志
通过先在mysql表新增一条数据,然后执行作业,查看clickhouse中是否数据同步过来了
mysql中的数据
查看clickhouse中的数据,可以看到新增了一条数据
参考clickhoue的操作命令:
clickhouse-client -u default --password clickhouse ClickHouse client version 22.2.2.1. Connecting to localhost:9000 as user default. Connected to ClickHouse server version 22.2.2 revision 54455. cpb-tjgsdsjptsyhj-xuyue-3 :) show database; Syntax error: failed at position 6 ('database'): show database; Expected one of: CREATE, DATABASES, CLUSTERS, CLUSTER, CHANGED, SETTINGS, TABLES, DICTIONARIES, CREATE, PROCESSLIST, ACCESS, USERS, ROLES, SETTINGS PROFILES, PROFILES, ROW POLICIES, POLICIES, QUOTAS, CURRENT ROLES, ENABLED ROLES, CURRENT QUOTA, QUOTA, GRANTS, PRIVILEGES cpb-tjgsdsjptsyhj-xuyue-3 :) show databases; SHOW DATABASES Query id: 4319b668-50df-43ac-a1e2-519f3a1a989f ┌─name───────────────┐ │ INFORMATION_SCHEMA │ │ default │ │ dinky │ │ gong │ │ information_schema │ │ system │ │ test01 │ └────────────────────┘ 7 rows in set. Elapsed: 0.003 sec. cpb-tjgsdsjptsyhj-xuyue-3 :) use dinky; USE dinky Query id: 603d70f1-5c01-432e-8bb6-3767e42eac0e Ok. 0 rows in set. Elapsed: 0.001 sec. cpb-tjgsdsjptsyhj-xuyue-3 :) show tables; SHOW TABLES Query id: b8ff567f-fb05-488e-963d-8f97b85c6ce0 ┌─name───────────────────┐ │ test_application │ │ test_application_local │ │ test_job │ │ test_job_local │ │ test_local │ │ test_local_local │ │ test_out │ │ test_session │ │ test_session_local │ │ test_standalone │ │ test_standalone_local │ └────────────────────────┘ 11 rows in set. Elapsed: 0.002 sec. cpb-tjgsdsjptsyhj-xuyue-3 :) select * from test_out; SELECT * FROM test_out Query id: dd2bf3b6-d9ad-4dfc-8514-eec8c19129e3 ┌─id─┬─name─┬─age─┐ │ 1 │ li │ 18 │ │ 1 │ li │ 18 │ └────┴──────┴─────┘ ┌─id─┬─name─┬─age─┐ │ 12 │ ww │ 3 │ └────┴──────┴─────┘ ┌─id─┬─name─┬─age─┐ │ 11 │ yy │ 3 │ │ 11 │ yy │ 3 │ └────┴──────┴─────┘ ┌─id─┬─name─┬─age─┐ │ 10 │ zz │ 2 │ └────┴──────┴─────┘ ┌─id─┬─name─┬─age─┐ │ 2 │ li │ 18 │ └────┴──────┴─────┘ 7 rows in set. Elapsed: 0.102 sec. cpb-tjgsdsjptsyhj-xuyue-3 :) select * from test_out; SELECT * FROM test_out Query id: ca85ca87-0b9c-4d95-b02d-0bbd9bf106bc ┌─id─┬─name─┬─age─┐ │ 2 │ li │ 18 │ └────┴──────┴─────┘ ┌─id─┬─name─┬─age─┐ │ 1 │ li │ 18 │ │ 1 │ li │ 18 │ └────┴──────┴─────┘ ┌─id─┬─name─┬─age─┐ │ 12 │ ww │ 3 │ └────┴──────┴─────┘ ┌─id─┬─name─┬─age─┐ │ 11 │ yy │ 3 │ │ 11 │ yy │ 3 │ └────┴──────┴─────┘ ┌─id─┬─name─┬─age─┐ │ 10 │ zz │ 2 │ └────┴──────┴─────┘ ┌─id─┬─name─┬─age─┐ │ 33 │ jack │ 56 │ └────┴──────┴─────┘ 8 rows in set. Elapsed: 0.005 sec.