SQL server + Dinky + doris 数据导入demo

1、SQL server + Dinky + doris + flink cdc 

导入plugins的jar包

离线数仓 部分 希望可以 

1、一次性导入整张表(全量历史数据)

2、实时增量导入 通过flink cdc方式

要开启MySQL binglog

修改/etc/my.cnf

systemctl restart mysqld.service  重启mysql

Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation

GRANT REPLICATION CLIENT ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

 2、配置SQL server的 cdc 和开启代理服务

    需要在服务器的控制面板 服务里 开启SQL server的代理服务 已启动

    配置 库表的cdc

ALTER AUTHORIZATION ON DATABASE::[数据库名] TO [sa]

USE database;
GO
EXEC sys.sp_cdc_enable_db

查看是否开启库 

select * from sys.databases where is_cdc_enabled = 1   

执行 开启 表 t1的cdc

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 't1', @role_name = null;

作业 'cdc.natu_capture' 已成功启动。
作业 'cdc.natu_cleanup' 已成功启动。

select name, is_tracked_by_cdc 
from sys.tables where object_id = OBJECT_ID('dbo.t1')

select name,is_tracked_by_cdc from sys.tables where is_tracked_by_cdc = 1

禁用 某个表

-- 禁用
EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', -- sysname
    @source_name = 'test_cdc', -- sysname
    @capture_instance = 'dbo_test_cdc' -- sysname

EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', -- sysname
    @source_name = 'kuoutlist', -- sysname
    @capture_instance = 'dbo_kuoutlist' -- sysname

异常情况：

消息 22960，级别 16，状态 1，过程 sp_cdc_disable_table_internal，行 75 [批起始行 0]
尚未对源表 'dbo.hl' 启用更改数据捕获实例 'dbo_hl'。请使用 sys.sp_cdc_help_change_data_capture 验证捕获实例名称，然后重试操作。

右键删除 CT

完成时间: 2023-09-15T10:47:19.5615815+08:00

EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', -- sysname
    @source_name = 'hl', -- sysname
    @capture_instance = 'dbo_hl' -- sysname

禁用数据库CDC

EXEC sys.sp_cdc_disable_db;
所有表都停止cdc了

1、异常：因为当前数据库中已存在捕获实例名称 'dbo_hl',所以无法创建该捕获实例。

EXEC sp_cdc_help_change_data_capture

EXEC sys.sp_cdc_disable_table @source_schema = 'NULL', -- sysname
    @source_name = 'NULL', -- sysname
    @capture_instance = 'dbo_hl' -- sysname

不起作用

重新建设：

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'hl', @capture_instance = 'dbo_hl_new', @role_name = null;

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'contractthlist', @capture_instance = 'dbo_contractthlist_new', @role_name = null;

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'kuin', @capture_instance = 'dbo_kuin_new', @role_name = null;

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'kuinlist', @capture_instance = 'dbo_kuinlist_new', @role_name = null;

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'caigoulist', @capture_instance = 'dbo_caigoulist_new', @role_name = null;

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'contractthlist', @capture_instance = 'dbo_contractthlist_open', @role_name = null;

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'hl', @capture_instance = 'dbo_hl_open', @role_name = null;

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'kuin', @capture_instance = 'dbo_kuin_open', @role_name = null;

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'kuinlist', @capture_instance = 'dbo_kuinlist_open', @role_name = null;

禁用某个表

EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', -- sysname
    @source_name = 'contract', -- sysname
    @capture_instance = 'dbo_contract' -- sysname

EXEC sp_cdc_help_change_data_capture

select name,is_tracked_by_cdc from sys.tables where is_tracked_by_cdc = 1

EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', -- sysname
    @source_name = 'contractlist', -- sysname
    @capture_instance = 'dbo_contractlist' -- sysname

EXEC sp_cdc_help_change_data_capture

select name,is_tracked_by_cdc from sys.tables where is_tracked_by_cdc = 1

重新开启表的cdc功能

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'contract', @role_name = null;

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'contractlist', @role_name = null;

重启flink cdc任务

报以下错误 

还是要整库重建cdc

EXEC sys.sp_cdc_disable_db;

再启动：

USE big_dajiang;
GO
EXEC sys.sp_cdc_enable_db

select * from sys.databases where is_cdc_enabled = 1