您现在的位置是:首页 >学无止境 >ClickHouse往MySQL数据回插网站首页学无止境
ClickHouse往MySQL数据回插
简介ClickHouse往MySQL数据回插
使用clickhouse从mysql拉取数据构建数仓的同时,有的时候也需要从clickhouse往mysql里回插数据来满足需求,毕竟mysql在生产环境下用的还是比较多的,clickhouse做完数据分析处理,再将数据导入mysql数据库,来向需要使用接口获取mysql数据提供服务
一.MySQL引擎
- 使用mysql引擎进行数据回插
CREATE TABLE out.BID_TEST (`ID` String, `PROJECT_NUMBERS` Nullable(String), `PROJECT_NAME` Nullable(String), `BID_TEST_TITLE` Nullable(String), `BUY_NAMES` Nullable(String), `AGENT_NAMES` Nullable(String), `CAGENT_CONTACT` Nullable(String)) ENGINE =MySQL('192.168.10.100:3306','out','BID_TEST ','test','123456');
- 创建的mysql引擎表需要与mysql表结构一致
- mysql引擎的表需要分别提供mysql连接url,数据库,表,用户名,密码
insert into out.BID_TEST select * from default.BID_TEST_DATA
#从clickhouse库往mysql里插数
但是存在问题,有的时候插数时会报错
如Lost connection to MySQL server “MySQL server has gone away”
优化mysql参数
sort_buffer_size = 512k
join_buffer_size = 512k
thread_cache_size = 64
query_cache_size = 32M
wait_timeout=288000
interactive_timeout=288000
max_allowed_packet=128M
- mysql引擎还是不推荐使用,生产环境下,我使用mysql引擎频繁超时报错,或者丢数
二.JDBC引擎
- jdbc引擎是我比较推荐的引擎,目前也是我一直使用的引擎
- jdbc引擎更加稳定,适合跑回插的定时任务时使用
CREATE TABLE out.BID_TEST (`ID` Nullable(String), `PROJECT_NUMBERS` Nullable(String), `PROJECT_NAME` Nullable(String), `BID_TEST_TITLE` Nullable(String), `BUY_NAMES` Nullable(String), `AGENT_NAMES` Nullable(String), `CAGENT_CONTACT` Nullable(String))ENGINE=JDBC('mysql7','out','BID_TEST')
insert into out.BID_TEST select * from default.BID_TEST_DATA
- 使用jdbc引擎建议设置数据字段都是可为空
有的时候文本字段过长,clickhouse这边是String 而mysql的字段类型是varchar,一旦varchar范围不够,就会导致数据插入失败,可能只有部分满足条件的数据能够插进去,但是全量数据会插入异常,将varcahr改成text 扩大字段范围,同时mysql表字段尽量设置默认值都是可为空
- 使用jdbc时出现时间字段插入异常
Incorrect datetime value: ‘1970-01-01 08:00:00
timestamp的时间范围表示为’1970-01-01 08:00:01’ and ‘2038-01-19 11:14:07’
所以clickhouse的1970-01-01 08:00:00写不进去
- 我试过使用更改sql_mode='' 或者把sql_mode改为ANSI宽松,但也只是插进去了部分时间数据,不确定是不是时间字段引起的,后来索性将mysql表字段的timestamp类型改为datetime 扩充范围问题得以解决
- 使用jdbc引擎插数我设置的时每100条插入,速度稍微慢点,但是相对稳定
风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。