Spark+ClickHouse实战企业级数据仓库，进军大厂必备
xia栽の地止：
lexuecode.com/6033.html
企业级数据仓库关键技术准备 数据仓库架构的3个核心组件 当创建未来数据仓库的架构时，必须考虑多种因素，例如有多少数据源将连接到数据仓库、每个数据源中的信息量及其性质和复杂性、分析目标、现有技术环境等。然而，说每个架构都是独一无二的是错误的，因为实际上每个架构都具有以下三个组件：
源系统——捕获交易的操作数据库、物联网设备流传感器数据、SaaS应用程序、外部数据源等。
数据暂存区域–临时托管复制数据的区域和一组流程，可帮助加载到数据仓库之前根据业务定义的规则清理和转换数据。有了暂存区，就可以拥有原始数据的历史记录，以防ETL作业失败。通常，一旦ETL作业成功完成，暂存区中的信息就会被删除。但是，仍可以出于遗留原因将其保存一段时间或存档。如果所有数据转换都发生在数据仓库数据库本身中，则可以省略该区域。
数据存储–为特定部门或业务线创建的公司范围信息和数据集市的数据仓库数据库。
除了这些内容之外，企业数据仓库解决方案还包含数据治理和元数据管理组件。扩展数据仓库环境还可以包括OLAP立方体（存储聚合数据以实现交互式查询的多维数据结构）和数据访问层（供最终用户访问和操作所存储信息的工具和应用程序）。然而，这些内容是更大的生态系统（BI架构）的一部分，因此我们不会在这里探讨它们。
企业级数据仓库OLAP数仓架构
数据源：数据来源，互联网公司的数据来源随着公司的规模扩张而呈递增趋势，同时自不同的业务源，比如埋点采集，客户上报，API等。
ODS层：数据仓库源头系统的数据表通常会原封不动地存储一份，这称为ODS层, ODS层也经常会被称为准备区。这一层做的工作是贴源，而这些数据和源系统的数据是同构，一般对这些数据分为全量更新和增量更新，通常在贴源的过程中会做一些简单的清洗。
DW层：数据仓库明细层和数据仓库汇总层是数据仓库的主题内容。将一些数据关联的日期进行拆分，使得其更具体的分类，一般拆分成年、月、日，而ODS层到DW层的ETL脚本会根据业务需求对数据进行清洗、设计，如果没有业务需求，则根据源系统的数据结构和未来的规划去做处理，对这层的数据要求是一致、准确、尽量建立数据的完整性。
DWS层：应用层汇总层，主要是将DWD和DWS的明细数据在hadoop平台进行汇总，然后将产生的结果同步到DWS数据库，提供给各个应用。举个例子，从ODS层中对用户的行为做一个初步汇总，抽象出来一些通用的维度：时间、ip、id，并根据这些维度做一些统计值，比如用户每个时间段在不同登录ip购买的商品数等。这里做一层轻度的汇总会让计算更加的高效，在此基础上如果计算仅7天、30天、90天的行为的话会快很多。
DA应用层：
① 业务产品CRM、ERP等，业务产品所使用的数据，已经存在于数据共享层，直接从数据共享层访问即可；
② 报表FineReport、业务报表，同业务产品，报表所使用的数据，一般也是已经统计汇总好的，存放于数据共享层；
③ 即席查询即席查询的用户有很多，有可能是数据开发人员、网站和产品运营人员、数据分析人员、甚至是部门老大，他们都有即席查询数据的需求；
④ OLAP：目前，很多的OLAP工具不能很好的支持从HDFS上直接获取数据，都是通过将需要的数据同步到关系型数据库中做OLAP，但如果数据量巨大的话，关系型数据库显然不行；
⑤ 其它数据接口：这种接口有通用的，有定制的。比如一个从Redis中获取用户属性的接口是通用的，所有的业务都可以调用这个接口来获取用户属性。
ClickHouse与MySQL、Hbase、Elasticsearch
MySQL与Clickhouse是两个完全不一样的数据库，两者均有着自己的优缺点，两者所适合的业务场景也是不一样的，在实际业务中，我们需要根据数据库自身的特性优点选择合适它的业务场景。传统的MySQL数据库虽然很好的支持了OLTP的业务，但是对于OLAP这类需要对大批量数据进行统计分析的业务场景还是存在较大的一定的性能瓶颈。想要对将线上核心的业务数据做较好的BI统计分析查询，我们就必须要面对如何将线上数据同步至对应的统计分析数据库。Clickhouse从2020年开始，推出了MaterializeMySQL引擎，实现了将Clickhouse作为MySQL的从库，对MySQL数据进行实时同步，实现了OLTP到OLAP的跨越。
一、MaterializeMySQL 1.1 MySQL与CK的简单比较 MySQL与Clickhouse的异同主要如下：
MySQL
Clickhouse
关系型数据库，支持事物
分布式列数据库，不支持事物
行存储模式，适合尽量少的读取需要的行数据
列存储模式，且数据压缩比高，对大批量数据读取有着天然优势
单进程多线程服务，单条业务请求查询无法有效利用到多个CPU资源
多核并行
面向OLTP业务
面向联机分析处理的OLAP业务
1.2 MaterializeMySQL原理 在2020年下半年，Yandex 公司在 ClickHouse社区发布了MaterializeMySQL引擎，该引擎主要是用来支持从MySQL全量及增量实时数据同步。目前支持 MySQL 5.6/5.7/8.0 版本，兼容 Delete/Update 语句，及大部分常用的 DDL 操作。
众所周知，MySQL的自身复制主要依赖于binlog事务日志，MaterializeMySQL引擎也不例外。但是相对于原生MySQL的binlog复制不同的是，由于两者语法上的差异，MaterializeMySQL并不是将event中的SQL语句转换为CK中具体语句进行执行，而是直接讲Binlog Event转换为底层 Block 结构，然后直接写入底层存储引擎，接近于物理复制。
MaterializeMySQL实现流程：
MaterializeMySQL支持数据库级别的复制。 当在Clickhouse中创建库级别复制后，clickhouse通过我们指定的数据库账号通过TCP/IP连接到数据，对数据库执行Flush table with read lock 并获取相关的binlog、表结构元数据信息；元数据复制完毕后释放全局只读锁，并开始通过select * from table_name开始复制表数据信息。 对于后续的增量数据的同步，MaterializeMySQL通过对binlog event的解析来实现的实时同步 （MYSQL_QUERY_EVENT(DDL)、MYSQL_WRITE_ROWS_EVENT(insert)、MYSQL_UPDATE_ROWS_EVENT(update)、MYSQL_DELETE_ROWS_EVENT(delete) 对于DDL操作，MaterializeMySQL默认将MySQL表数据的主键作为CK表的排序键和分区键，但是由于Clickhouse与MySQL的数据定义有区别，DDL语句也会进行相应的转换 对于Update/Delete操作，MaterializeMySQL引入_version的隐藏字段，用来做版本控制，并结合_sign字段标记数据的有效性 MaterializeMySQL创建复制通道时，在全量初始化同步阶段，可通过general_log查看MySQL具体执行操作细节，具体执行日志如下：
2021-03-14T15:40:02.016351+08:00 26 Connect root@172.16.104.11 on ck_test using TCP/IP 2021-03-14T15:40:02.017402+08:00 26 Query SET NAMES utf8 2021-03-14T15:40:02.018822+08:00 26 Query SHOW VARIABLES WHERE (Variable_name = 'log_bin' AND upper(Value) = 'ON') OR (Variable_name = 'binlog_format' AND upper(Value) = 'ROW') OR (Variable_name = 'binlog_row_image' AND upper(Value) = 'FULL') OR (Variable_name = 'default_authentication_plugin' AND upper(Value) = 'MYSQL_NATIVE_PASSWORD') 2021-03-14T15:40:02.032549+08:00 26 Query SELECT version() AS version 2021-03-14T15:40:02.033620+08:00 26 Query FLUSH TABLES 2021-03-14T15:40:02.051444+08:00 26 Query FLUSH TABLES WITH READ LOCK 2021-03-14T15:40:02.052364+08:00 26 Query SHOW MASTER STATUS 2021-03-14T15:40:02.053295+08:00 26 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 2021-03-14T15:40:02.054027+08:00 26 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 2021-03-14T15:40:02.055520+08:00 26 Query SELECT TABLE_NAME AS table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'ck_test' 2021-03-14T15:40:02.057527+08:00 26 Query SHOW CREATE TABLE ck_test.t1 2021-03-14T15:40:02.060780+08:00 26 Query SHOW CREATE TABLE ck_test.t2 2021-03-14T15:40:02.062275+08:00 26 Query UNLOCK TABLES 2021-03-14T15:40:02.075348+08:00 26 Query SELECT * FROM ck_test.t2 2021-03-14T15:40:02.101797+08:00 26 Query SELECT * FROM ck_test.t1 2021-03-14T15:40:02.106329+08:00 26 Query COMMIT 2021-03-14T15:40:02.109026+08:00 27 Connect root@172.16.104.11 on using TCP/IP 2021-03-14T15:40:02.109637+08:00 27 Query SET @master_binlog_checksum = 'CRC32' 2021-03-14T15:40:02.110123+08:00 27 Query SET @master_heartbeat_period = 1000000000 2021-03-14T15:40:02.111290+08:00 27 Binlog Dump GTID Log: '' Pos: 4 GTIDs: '4a2dfc1c-1f50-11eb-a38b-fa057042bc00:1-53, a4ec8037-1a70-11eb-91ff-fa9f1ef63700:1-1741042' 二、MySQL->CK的实时复制实现 1.1 环境准备 1、MySQL
开启binlog日志，且row_format=row 复制使用gtid模式 gtid_mode=ON enforce_gtid_consistency=1 binlog_format=ROW 2、Clickhouse
1）环境参数
-- 该参数默认关闭，若需要使用MaterializeMySQL引擎，必须打开该参数 mdw :) set allow_experimental_database_materialize_mysql=1; 2）创建复制通道
-- 语法 CREATE DATABASE {mysql_ip}:{mysql_dbname}', '{mysql_passoword}');
-- 执行SQL mdw :) CREATE DATABASE ck_test ENGINE = MaterializeMySQL('172.16.104.13:3306', 'ck_test', 'root', '123'); 3）复制信息
对于MySQL的实时复制信息，存储在datadir下的metadata目录下。
-- MySQL的binlog位点信息 root@mysql 15:05: [ck_test]> show master status\G *************************** 1. row *************************** File: mysql-bin.000005 Position: 4048 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 4a2dfc1c-1f50-11eb-a38b-fa057042bc00:1-37, a4ec8037-1a70-11eb-91ff-fa9f1ef63700:1-1741042 1 row in set (0.00 sec)
-- Clickhouse复制位点信息 [root@mdw ck_test]# pwd /data/clickhouse-server/data/metadata/ck_test [root@mdw ck_test]# cat .metadata Version: 2 Binlog File: mysql-bin.000005 //binlog文件 Executed GTID: 4a2dfc1c-1f50-11eb-a38b-fa057042bc00:1-37,a4ec8037-1a70-11eb-91ff-fa9f1ef63700:1-1741042 //GTID信息 Binlog Position: 4048 //binlog位点 Data Version: 9 //数据版本信息，全局递增 1.2 基本功能测试 1、数据写入的同步
对于MySQL的所有数据，在CK中有会有对应的_sign，_version隐藏字段，用于进行版本控制的标记和查询。、
-- MySQL
root@mysql 14:44: [ck_test]> create table t2(id int primary key not null auto_increment,name varchar(2));
Query OK, 0 rows affected (0.03 sec)
root@mysql 14:45: [ck_test]> insert into t2 values(null,'aa'),(null,'bb');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@mysql 14:45: [ck_test]> select * from t2;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
2 rows in set (0.01 sec)
-- Clickhouse
mdw :) select * from ck_test.t2 order by id ;
SELECT *
FROM ck_test.t2
ORDER BY id ASC
┌─id─┬─name─┐
│ 1 │ aa │
└────┴──────┘
┌─id─┬─name─┐
│ 2 │ bb │
└────┴──────┘
2 rows in set. Elapsed: 0.009 sec.
-- Clickhouse隐藏字段查询
mdw :) select *,_sign,_version from ck_test.t2 order by id;
SELECT
*,
_sign,
_version
FROM ck_test.t2
ORDER BY id ASC
┌─id─┬─name─┬─_sign─┬─_version─┐
│ 1 │ aa │ 1 │ 7 │ //一次性写入的_version=7一致，由于是insert操作，_sign=1。
│ 2 │ bb │ 1 │ 7 │
└────┴──────┴───────┴──────────┘
2 rows in set. Elapsed: 0.003 sec. 2、数据更新
对于MySQL的Update操作，当我们直接查询CK表数据时，可以看到表数据已经正常“更新”，但是我们额外去查询_sign,_version的信息时，可以发现更新前数据其实并没有进行物理删除。所以，对于Update操作Clickhouse的做法其实是变更后的记录进行写入，并标记_sign=1,_version=${当前版本}+1，我们执行query查询时，CK根据版本控制帮我们最终返回为正常的结果集信息。
-- MySQL
root@mysql 14:45: [ck_test]> select * from t2;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
2 rows in set (0.01 sec)
root@mysql 14:47: [ck_test]> update t2 set name='aaa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@mysql 14:47: [ck_test]> select * from t2;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | bb |
+----+------+
2 rows in set (0.01 sec)
-- Clickhouse
mdw :) select * from ck_test.t2 order by id ; //可以看到，正常的查询，CK已经帮我们做了处理
SELECT *
FROM ck_test.t2
ORDER BY id ASC
┌─id─┬─name─┐
│ 1 │ aaa │
└────┴──────┘
┌─id─┬─name─┐
│ 2 │ bb │
└────┴──────┘
2 rows in set. Elapsed: 0.014 sec.
mdw :) select *,_sign,_version from ck_test.t2 order by id;
SELECT
*,
_sign,
_version
FROM ck_test.t2
ORDER BY id ASC
┌─id─┬─name─┬─_sign─┬─_version─┐
│ 1 │ aaa │ 1 │ 8 │ //update更新后的数据被写入ck表，_version+1
└────┴──────┴───────┴──────────┘
┌─id─┬─name─┬─_sign─┬─_version─┐
│ 1 │ aa │ 1 │ 7 │
│ 2 │ bb │ 1 │ 7 │
└────┴──────┴───────┴──────────┘
3 rows in set. Elapsed: 0.008 sec.
3、数据删除
对于Delete操作，当我们直接查询CK表数据时，可以看到表数据已经正常“删除”，但是我们额外去查询_sign,_version的信息时，可以发现更新前数据其实并没有进行物理删除，而是新增一行需要删除的行记录数据，并标记_sign=-1、_version=当前版本+1，所以对于ck的delete操作，其实也不会直接对记录进行物理删除，而是依靠sign进行删除标记。当我们执行query查询时，CK根据版本控制帮我们最终返回为正常的结果集信息。
-- MySQL
root@mysql 14:47: [ck_test]> select * from t2;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | bb |
+----+------+
2 rows in set (0.00 sec)
root@mysql 14:48: [ck_test]> delete from t2 where id=2;
Query OK, 1 row affected (0.01 sec)
root@mysql 14:48: [ck_test]> select * from t2;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
+----+------+
1 row in set (0.01 sec)
-- Clickhouse
mdw :) select * from ck_test.t2 order by id ; //query操作正常显示结果集信息
SELECT *
FROM ck_test.t2
ORDER BY id ASC
┌─id─┬─name─┐
│ 1 │ aaa │
└────┴──────┘
1 rows in set. Elapsed: 0.009 sec.
mdw :) select *,_sign,_version from ck_test.t2 order by id;
SELECT
*,
_sign,
_version
FROM ck_test.t2
ORDER BY id ASC
┌─id─┬─name─┬─_sign─┬─_version─┐
│ 1 │ aaa │ 1 │ 8 │
└────┴──────┴───────┴──────────┘
┌─id─┬─name─┬─_sign─┬─_version─┐
│ 1 │ aa │ 1 │ 7 │
│ 2 │ bb │ -1 │ 9 │ //新增删除记录行，并标记_sign=-1表示删除操作
│ 2 │ bb │ 1 │ 7 │
└────┴──────┴───────┴──────────┘
4 rows in set. Elapsed: 0.008 sec.
4、DDL
1）对于ck表数据结构信息，由于MaterializeMySQL暂时不支持show create xx的语法，所以我们可以通过对应的物理文件查看CK创建的表结构信息。
[root@mdw metadata]# cat ck_test
cat: ck_test: 是一个目录
[root@mdw metadata]# cat ck_test.sql
ATTACH DATABASE ck_test
ENGINE = MaterializeMySQL('172.16.104.13:3306', 'ck_test', 'root', '123')
[root@mdw metadata]# cat ck_test/t2.sql
ATTACH TABLE t2
(
`id` Int32,
`name` Nullable(String),
`_sign` Int8 MATERIALIZED 1,
`_version` UInt64 MATERIALIZED 1
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY intDiv(id, 4294967) //分区键、排序键均由MySQL表数据主键继承
ORDER BY tuple(id)
SETTINGS index_granularity = 8192
2）新增字段DDL操作
-- MySQL新增表字段
root@mysql 14:48: [ck_test]> alter table t2 add age int;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@mysql 14:52: [ck_test]> select * from t2;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | aaa | NULL |
+----+------+------+
1 row in set (0.00 sec)
-- Clickhouse表字段信息
[root@mdw metadata]# cat ck_test/t2.sql
ATTACH TABLE t2
(
`id` Int32,
`name` Nullable(String),
`age` Nullable(Int32), //CK表同步MySQL新增字段，对于ck这种列存储来讲，新增字段的操作还是比较简单的
`_sign` Int8 MATERIALIZED 1,
`_version` UInt64 MATERIALIZED 1
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY intDiv(id, 4294967)
ORDER BY tuple(id)
mdw :) select * from ck_test.t2 order by id ;
SELECT *
FROM ck_test.t2
ORDER BY id ASC
┌─id─┬─name─┬──age─┐
│ 1 │ aaa │ ᴺᵁᴸᴸ │
└────┴──────┴──────┘
1 rows in set. Elapsed: 0.010 sec.
mdw :) select *,_sign,_version from ck_test.t2 order by id;
SELECT
*,
_sign,
_version
FROM ck_test.t2
ORDER BY id ASC
┌─id─┬─name─┬──age─┬─_sign─┬─_version─┐
│ 1 │ aa │ ᴺᵁᴸᴸ │ 1 │ 7 │
│ 1 │ aaa │ ᴺᵁᴸᴸ │ 1 │ 8 │
│ 2 │ bb │ ᴺᵁᴸᴸ │ -1 │ 9 │
│ 2 │ bb │ ᴺᵁᴸᴸ │ 1 │ 7 │
└────┴──────┴──────┴───────┴──────────┘
4 rows in set. Elapsed: 0.023 sec.
