您现在的位置是:首页 >学无止境 >ClickHouse往MySQL数据回插网站首页学无止境

ClickHouse往MySQL数据回插

万山数据_Spring 2023-06-26 12:00:02
简介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条插入,速度稍微慢点,但是相对稳定

风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。