如果同步到Redshift/BigQuery，来自MySQL数据库的bin日志复制会保持唯一的约束吗？

Question

我们希望将数据仓库从MySQL数据库移动到Redshift或BigQuery。

虽然针对OLAP操作进行了优化，但这些基于列的数据库的一个缺点是它们不强制执行唯一约束。

因此，表中有重复的订单/产品并不是不可能的。我们工作的行业是零售业，我们使用标准的Kimball事实和维度(星型架构)数据库设计。

提出的一个潜在的解决方案是在MySQL中建立数据库，并使用第三方复制工具将数据同步到Redshift/BigQuery。这样，我们将在原始的MySQL数据库中强制执行键约束，并且我们将只对读查询使用Redshift/BigQuery。

然而，在MySQL中强制执行约束并设置到Redshift/BigQuery的bin日志复制将保持数据与MySQL中的数据相同，从而强制执行唯一约束？

Answer 1

首先，你不能从MySQL复制到RedShift/BigQuery。

请理解，BigQuery是一个分析型数据库。

建议您在Cloud SQL中设置来自MySQL的复制。然后你可以在BigQuery中运行EXTERNAL_QUERY，这意味着你可以查询/连接你的BQ数据库和Cloud SQL MySQL数据库。

从您的当前实例到云

• 实例的

1. 安装副本，请遵循此guide。
2. 了解Cloud SQL federated queries如何让您从BigQuery云SQL实例中进行查询。

通过这种方式可以实时访问关系数据库，如下所示：

您在BigQuery上运行的示例查询：

SELECT * EXTERNAL_QUERY(
'connection_id',
'''SELECT * FROM mysqltable AS c ORDER BY c.customer_id'');

您甚至可以将Bigquery表与SQL表连接起来：

示例：

SELECT c.customer_id, c.name, SUM(t.amount) AS total_revenue,
rq.first_order_date
FROM customers AS c
INNER JOIN transaction_fact AS t ON c.customer_id = t.customer_id
LEFT OUTER JOIN EXTERNAL_QUERY(
  'connection_id',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

Answer 2

您提出的解决方案将允许：

• 在源MySQL数据库上强制实施唯一键约束
• 以复制/捕获该数据库对您的数据仓库

所做的所有更改

也就是说，您最终在数据仓库上看到的是更改了MySQL DB的所有事件(插入、更新、(删除:不被所有SaaS产品支持...))的视图。因此，仓库中的“原始”表对于MySQL的每个唯一键将有多个事件，然后您需要重新处理这些事件，以得到与MySQL中相同的表。

为了进一步说明这一点:这就像您的MySQL表在每个时间点是快照或固定图片/状态，而从binlog复制中获得的是数据库所有连续状态更改的“电影”。如果您想要仓库中的快照，那么您需要“重放”所有的更改，直到您想要快照的点。

这是非常强大的，因为您永远不会丢失数据库上发生的任何更改，并且总是可以找到它。但是，要将数据仓库表转换为与输入数据库相同的“快照”形状，确实需要进行额外的工作。

这通常可以通过添加row_number() over (partition by id order by updated_at desc) as rn的CTE在您的仓库上完成，然后在where rn = 1 and deleted_at is null上过滤该CTE ( id是具有唯一约束的列，如果您的唯一约束是复合的(在多个键上)，您可以列出多个，updated_at是每个更改数据捕获事件的时间戳，deleted_at是删除事件的时间戳(如果给定的键没有发生删除事件，则为null ))。

对于开源和自托管的变更数据捕获，您还可以查看在Kafka Connect (或AWS Kinesis或其他...)上运行的Debezium等内容。如果这是你的客户愿意投资的基础设施...或者只查看您选择的语言的数据库引擎/库中的逻辑复制连接(例如，我为Python上的PostgreSQL使用psycopg2 (带有extras)……)