1.删除已使用的物化数据库
DROP DATABASE ods_fyt_baseinfo;
DROP DATABASE ods_fyt_exam_manager;
DROP DATABASE ods_fyt_exam_result ;
DROP DATABASE ods_kp_practice_manager;
DROP DATABASE ods_kp_practice_result;
DROP DATABASE ods_kp_tk_base;
DROP DATABASE ods_kp_scheduling_newera;
DROP DATABASE ods_kp_promotion;
DROP DATABASE ods_ks_report;
DROP DATABASE ods_kp_paper;
DROP DATABASE ods_kp_guidelearn;
2.创建物化数据库
开发环境(下面的sql一次性执行完)：
set allow_experimental_database_materialized_mysql=1;
CREATE DATABASE IF NOT EXISTS ods_fyt_exam_manager ENGINE = MaterializedMySQL('devmysql.local.yjzhixue.com', 'fyt_exam_manager', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_fyt_exam_result ENGINE = MaterializedMySQL('devmysql.local.yjzhixue.com', 'fyt_exam_result', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_practice_manager ENGINE = MaterializedMySQL('devmysql.local.yjzhixue.com', 'kp_practice_manager', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_practice_result ENGINE = MaterializedMySQL('devmysql.local.yjzhixue.com', 'kp_practice_result', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_fyt_baseinfo ENGINE = MaterializedMySQL('devmysql.local.yjzhixue.com', 'fyt_baseinfo', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_guidelearn ENGINE = MaterializedMySQL('devmysql.local.yjzhixue.com', 'kp_guidelearn', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_paper ENGINE = MaterializedMySQL('devmysql.local.yjzhixue.com', 'kp_paper', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_tk_base ENGINE = MaterializedMySQL('devmysql.local.yjzhixue.com', 'kp_tk_base', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_promotion ENGINE = MaterializedMySQL('devmysql.local.yjzhixue.com', 'kp_promotion', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_scheduling_newera ENGINE = MaterializedMySQL('devmysql.local.yjzhixue.com', 'kp_scheduling_newera', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_ks_report ;
CREATE TABLE ods_ks_report.school_data_board
(
schoolId String,
examId String,
subjectCode String,
grade Int8,
ctbCount Int32,
ctbPrintCount Int32,
fczyCount Int32,
reportCount Int32,
roleReportCount Int32,
createTime Datetime
) ENGINE = MongoDB('devmongo.local.yjzhixue.com:27017', 'ks_report', 'school_data_board', 'ks_report', '');
测试环境(下面的sql一次性执行完)：
set allow_experimental_database_materialized_mysql=1;
CREATE DATABASE IF NOT EXISTS ods_fyt_exam_manager ENGINE = MaterializedMySQL('testmysql.local.yjzhixue.com', 'fyt_exam_manager', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_fyt_exam_result ENGINE = MaterializedMySQL('testmysql.local.yjzhixue.com', 'fyt_exam_result', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_practice_manager ENGINE = MaterializedMySQL('testmysql.local.yjzhixue.com', 'kp_practice_manager', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_practice_result ENGINE = MaterializedMySQL('testmysql.local.yjzhixue.com', 'kp_practice_result', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_fyt_baseinfo ENGINE = MaterializedMySQL('testmysql.local.yjzhixue.com', 'fyt_baseinfo', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_guidelearn ENGINE = MaterializedMySQL('testmysql.local.yjzhixue.com', 'kp_guidelearn', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_paper ENGINE = MaterializedMySQL('testmysql.local.yjzhixue.com', 'kp_paper', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_tk_base ENGINE = MaterializedMySQL('testmysql.local.yjzhixue.com', 'kp_tk_base', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_promotion ENGINE = MaterializedMySQL('testmysql.local.yjzhixue.com', 'kp_promotion', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_kp_scheduling_newera ENGINE = MaterializedMySQL('testmysql.local.yjzhixue.com', 'kp_scheduling_newera', 'root', 'kpkj@2021');
CREATE DATABASE IF NOT EXISTS ods_ks_report ;
CREATE TABLE ods_ks_report.school_data_board
(
schoolId String,
examId String,
subjectCode String,
grade Int8,
ctbCount Int32,
ctbPrintCount Int32,
fczyCount Int32,
reportCount Int32,
roleReportCount Int32,
createTime Datetime
) ENGINE = MongoDB('testmongo.local.yjzhixue.com:27017', 'ks_report', 'school_data_board', 'ks_report', '');
3.注意事项
（1）物化过后的数据库，会同步mysql的数据变化，一旦有些表，修改主键等，就会导致同步中断，目前clickhouse在这块也未做改变，只能人工将物化数据库删掉，重新创建物化数据库从mysql同步
再执行。建议使用dbeaver客户端，可以直连clickhouse，建立物化前，需要对会话执行命令：set allow_experimental_database_materialized_mysql=1。 才能使会话有创建物化数据库的权限。建立物化数据库后，等待同步完毕（同步量大，等待时间会越久）
（2）big-data项目已经实现同步中断的检查器，具体实现就是每隔30分钟，拉取所有物化数据库schema，得到所有数据表，发送ping命令：select 1检查表响应。如果非正常响应情况下，数据库会回调错误码和错误信息，程序收到此类信息，封装成json报文，打印error，可在告警群查看。
（3）big-data项目封装了运营平台统计、部分大屏统计。支持配置实时统计，也支持傍晚2-3点离线统计记录（默认，时差范围为1天，如果测试想看到最新统计，后台预留实时更新接口：ETLController.operationETL和ETLController.bigScreenETL，实时统计请注意调用频率，数据量大时会消耗系统资源，cpu和内存会提高）
注意：本文归作者所有，未经作者允许，不得转载
