mysql数据迁移
基于库同步
CREATE DATABASE saic_iov_user ON CLUSTER default
ENGINE = MySQL('10.188.49.10:3306', 'saic_iov_user','iov_user', 'ENC(Mzg2MTE4ODgAYO6BQozrsqT8Gc+MwKN3)');
CREATE DATABASE saic_iov_merge ON CLUSTER default
ENGINE = MySQL('10.188.49.10:3306', 'saic_iov_merge','iov_merge_rw', 'ENC(ODYzMDQxMDmV516U8QSxtUcrPsdFRcMf)');
1、创建mysql的同步表
CREATE TABLE iov_sit.sync_Team ON CLUSTER default
(
`id` Int64 comment '主键ID',
`mis_id` String default '' comment 'mis公司id',
`team_name` String comment '车队名称',
`mis_team_name` String default '' comment 'mis公司名称',
`formal_id` Int64 comment '司管车队标记',
`area_id` Int64 default 0 comment '所属区域ID',
`create_by` String comment '创建者',
`create_time` DateTime comment '创建时间',
`update_by` String comment '更新者',
`update_time` DateTime comment '更新时间',
`is_deleted` Int8 comment '是否删除',
`team_category` Int8 default 0 comment '公司类别：1正式、2测试、3空装、4维修',
`is_manage_vehicle` Int8 default 0 comment '是否车管',
`is_manage_driver` Int8 default 0 comment '是否司管',
`vehicle_sync_default_team` Int8 comment '默认同步车队 0否 1是',
`aj_id` Int64 default 0 comment '安吉车司管同步的车队id'
)ENGINE = MySQL('10.188.136.118:3306', 'saic_iov_user', 't_team', 'saic_test', 'Sit_saic_test181030');
注意这里没有创建 ts Int64 comment ‘处理时间’ 这一列
2、创建clickhouse的基础表
CREATE TABLE iov_sit.t_team ON CLUSTER cluster_2s_2r
(
`id` Int64 comment '主键ID',
`mis_id` Nullable(String) default '' comment 'mis公司id',
`team_name` Nullable(String) comment '车队名称',
`mis_team_name` Nullable(String) default '' comment 'mis公司名称',
`formal_id` Nullable(Int64) comment '司管车队标记',
`area_id` Nullable(Int64) default 0 comment '所属区域ID',
`create_by` Nullable(String) comment '创建者',
`create_time` Nullable(DateTime) comment '创建时间',
`update_by` Nullable(String) comment '更新者',
`update_time` Nullable(DateTime) comment '更新时间',
`is_deleted` Nullable(Int8) comment '是否删除',
`team_category` Nullable(Int8) default 0 comment '公司类别：1正式、2测试、3空装、4维修',
`is_manage_vehicle` Nullable(Int8) default 0 comment '是否车管',
`is_manage_driver` Nullable(Int8) default 0 comment '是否司管',
`vehicle_sync_default_team` Nullable(Int8) comment '默认同步车队 0否 1是',
`aj_id` Nullable(Int64) default 0 comment '安吉车司管同步的车队id',
`ts` Int64 comment '处理时间'
)ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}', ts)
ORDER BY (id)
primary key(id)
3、创建clickhouse队列表
CREATE TABLE iov_sit.q_team ON CLUSTER cluster_2s_2r (
message String
) ENGINE = Kafka('10.188.139.21:9092', 'saic_iov_user_t_team_sit', 'saic_iov_user_t_team_sit_group2', 'JSONAsString');
4、创建clickhouse分布式表
CREATE TABLE iov_sit.dt_team ON CLUSTER cluster_2s_2r as iov_sit.t_team ENGINE = Distributed(cluster_2s_2r, iov_sit, t_team, id);
5、同步插入sql
INSERT INTO iov_sit.dt_team
(id, mis_id, team_name, mis_team_name, formal_id, area_id, create_by, create_time, update_by, update_time, is_deleted, team_category, is_manage_vehicle, is_manage_driver, vehicle_sync_default_team, aj_id)
select id, mis_id, team_name, mis_team_name, formal_id, area_id, create_by, create_time, update_by, update_time, is_deleted, team_category, is_manage_vehicle, is_manage_driver, vehicle_sync_default_team, aj_id
from saic_iov_user.t_team
6、创建clickhouse物化视图
CREATE MATERIALIZED VIEW iov_sit.mv_team ON CLUSTER cluster_2s_2r TO iov_sit.dt_team AS
SELECT visitParamExtractInt(JSONExtractRaw(message, 'data'), 'id') as id,
visitParamExtractString(JSONExtractRaw(message, 'data'), 'mis_id') as mis_id,
visitParamExtractString(JSONExtractRaw(message, 'data'), 'team_name') as team_name,
visitParamExtractString(JSONExtractRaw(message, 'data'), 'mis_team_name') as mis_team_name,
toInt64(visitParamExtractInt(JSONExtractRaw(message, 'data'), 'formal_id')) as formal_id,
toInt64(visitParamExtractInt(JSONExtractRaw(message, 'data'), 'area_id')) as area_id,
visitParamExtractString(JSONExtractRaw(message, 'data'), 'create_by') as create_by,
toDateTimeOrZero(visitParamExtractString(JSONExtractRaw(message, 'data'), 'create_time')) as create_time,
visitParamExtractString(JSONExtractRaw(message, 'data'), 'update_by') as update_by,
toDateTimeOrZero(visitParamExtractString(JSONExtractRaw(message, 'data'), 'update_time')) as update_time,
toInt8(visitParamExtractInt(JSONExtractRaw(message, 'data'), 'is_deleted')) as is_deleted,
toInt8(visitParamExtractInt(JSONExtractRaw(message, 'data'), 'team_category')) as team_category,
toInt8(visitParamExtractInt(JSONExtractRaw(message, 'data'), 'is_manage_vehicle')) as is_manage_vehicle,
toInt8(visitParamExtractInt(JSONExtractRaw(message, 'data'), 'is_manage_driver')) as is_manage_driver,
toInt8(visitParamExtractInt(JSONExtractRaw(message, 'data'), 'vehicle_sync_default_team')) as vehicle_sync_default_team,
toInt64(visitParamExtractInt(JSONExtractRaw(message, 'data'), 'aj_id')) as aj_id,
visitParamExtractInt(JSONExtractRaw(message, 'data'), 'ts') as ts from iov_sit.q_team;
