MySQL에서 Redshift로 데이터를 복사하는 이유
- WBOY앞으로
- 2023-06-03 10:54:031134검색
MySQL에서 Redshift로 데이터를 복사하는 이유
MySQL을 사용하여 웹 애플리케이션을 구동하는 많은 회사가 데이터 분석을 위해 Redshift를 선택합니다. 이 작업을 수행해야 하는 몇 가지 이유도 있습니다.
애플리케이션 성능을 유지합니다. 이미 언급했듯이 프로덕션 MySQL 데이터베이스에서 분석 쿼리를 실행하면 성능에 심각한 영향을 미칠 수 있습니다. 충돌을 일으킬 수도 있습니다. 분석 쿼리는 리소스 집약적이며 전용 컴퓨팅 성능이 필요합니다.
모든 데이터를 분석하세요. MySQL은 OLTP(온라인 트랜잭션 처리) 데이터베이스이므로 트랜잭션 데이터에 중점을 두고 설계되었으며 그 용도에는 고객 기록 및 금융 데이터가 포함됩니다. 그러나 비거래 유형을 포함하여 전체 데이터 세트에서 통찰력을 얻고 싶습니다. Redshift를 사용하면 모든 데이터를 한 곳에서 캡처하고 분석할 수 있습니다.
더 빠른 분석. Redshift는 MPP(대량 병렬 처리) 데이터 웨어하우스로, 매우 짧은 시간에 많은 양의 데이터를 처리할 수 있습니다. 반면에 MySQL은 대규모 최신 분석 쿼리에 필요한 컴퓨팅 성능을 충족하는 데 어려움을 겪고 있습니다. 심지어 MySQL 복제본 데이터베이스도 Redshift와 동일한 속도를 달성하는 데 어려움을 겪고 있습니다.
확장성. MySQL은 최신 분산 클라우드 인프라가 아닌 단일 노드 인스턴스용으로 설계되었습니다. 따라서 단일 노드 이상으로 확장하는 것은 시간과 리소스 집약적이므로 샤딩이나 마스터노드 설정과 같은 기술을 사용해야 합니다. 이들 모두는 데이터베이스 속도를 더욱 저하시킬 수 있습니다.
MySQL을 Redshift로 복사하는 네 가지 방법
분석 요구 사항을 충족하기 위해 많은 회사에서는 MySQL의 본질적인 약점 때문에 MySQL에서 Redshift로 데이터를 복사합니다. 이를 달성하는 방법에는 4가지가 있습니다.
가져오기
증분 선택 및 복사
binlog 변경 데이터 캡처(CDC) 사용
ETL
1. 가져오기
가장 쉬운 방법 Redshift로 복사하는 것은 전체 MySQL 데이터를 내보내는 것입니다. 그러나 이는 효율성이 가장 낮은 방법이기도 합니다. 세 단계가 있습니다:
Export
Transform
Import
Export
먼저 MySQL의 mysqldump 명령을 사용하여 데이터를 내보냅니다. 일반적인 mysqldump 명령은 다음과 같습니다.
java:
$ mysqldump -h yourmysqlhost -u user mydatabase mytable1 mytable2 --result-file dump.sql
이 명령의 출력은 MySQL SQL 문입니다. Redshift에서는 SQL을 있는 그대로 실행할 수 없습니다. 명령문을 Redshift 가져오기에 적합한 형식으로 변환해야 합니다.
Convert
최적의 업로드 성능을 위해 SQL 문을 TSV(탭으로 구분된 값) 형식으로 변환하세요. Redshift COPY 명령을 사용하여 이 작업을 수행할 수 있습니다.
COPY 명령은 SQL 문을 TSV 형식으로 변환합니다. 그런 다음 파일을 Redshift 테이블의 Amazon S3에 일괄 업로드합니다. 예를 들어 MySQL 덤프의 데이터 행은 다음과 같습니다.
java:
mysql> INSERT INTO `users` (`id`, `firstname`, `lastname`, `age`) VALUES (1923, ‘John’, ‘Smith’, 34),(1925,’Tommy’,’King’);
COPY를 사용하면 다음과 같습니다.
| 1923 | JOHN | SMITH |
|---|---|---|
| 19 2 5 | Tmmy | King |
请注意，值由制表符分隔(\t)。
您可能还必须将数据值转换为与 Redshift 兼容。这是因为 MySQL 和 Redshift 支持不同的列和数据类型。
例如，DATE 值“0000-00-00”在 MySQL 中是有效的，但在 Redshift 中会抛出错误。您必须将该值转换为可接受的 Redshift 格式，例如“0001-01-01”。
进口
将 MySQL 语句转换后，最终步骤是将其从 S3 导入到 Redshift。为此，只需运行 COPY 命令：
java:
COPY users
FROM 's3://my_s3_bucket/unload-folder/users_' credentials
'aws_access_key_id=your_access_key;aws_secret_access_key=your_secret_key';
进出口的弊端
尽管导入和导出是复制到 Redshift 的最简单方法，但它并不适合频繁更新。
大约需要30分钟，就能够通过100 Mbps的网络从MySQL导出18GB的数据。将该数据导入 Redshift 还需要 30 分钟。这假设您在导入或导出期间遇到零连接问题，这将迫使您重新开始该过程。
将 MySQL 复制到 Redshift 的更有效方法是增量 SELECT 和 COPY。
2.增量SELECT和COPY
如果导入和导出对于您的需求来说太慢，增量 SELECT 和 COPY 可能是您的答案。
SELECT 和 COPY 方法仅更新自上次更新以来已更改的记录。与导入和导出整个数据集相比，这花费的时间和带宽要少得多。SELECT 和 COPY 使您能够更频繁地同步 MySQL 和 Redshift。
要使用增量 SELECT 和 COPY，您的 MySQL 表必须满足几个条件：
表必须有一个updated_at列，每次更改行时都会更新其时间戳。
表必须有一个或多个唯一键。
和导入导出一样，这个方法也分三步：
1. 出口
增量 SELECT 仅导出自上次更新以来已更改的行。您在 MySQL 上运行的 SELECT 查询如下所示：
java:
SELECT * FROM users WHERE updated_at >= ‘2016-08-12 20:00:00’;
将结果保存到文件以进行转换。
2. 转型
此转换步骤与导入导出方法相同。将 MySQL 数据转换为 Redshift 的 TSV 格式。
3. 进口
您的MySQL TSV文件现在包括更新过的行和新插入的行。对于目标 Redshift 表，您必须采取其他措施而不能直接运行COPY命令。这将导致更新的行被复制。
为避免重复行，请使用 DELSERT（删除 + 插入）技术：
在 Redshift 上创建一个与目标表具有相同定义的临时表。
运行 COPY 命令将数据上传到临时表。
从目标表中删除临时表中也存在的行。它看起来像这样：
java:
id表的唯一键在哪里。
最后，将行从临时表插入到目标表：
DELETE FROM users USING users_staging s WHERE users.id = s.id;
java:
INSERT INTO users (id, firstname, lastname, updated_at) SELECT id, firstname, lastname, updated_at FROM users_staging s;
SELECT 和 COPY 的缺点
增量 SELECT 和 COPY 比导入和导出更有效，但它有其自身的局限性。
主要问题是从 MySQL 表中删除的行会无限期地保留在 Redshift 中。如果您希望在清理 MySQL 上的旧数据时保留在 Redshift 上的历史数据，那也没有问题。否则，在 Redshift 中删除的行会在数据分析过程中引起严重的头痛。
这种方法的另一个缺点是它不复制表模式更改。如果在MySQL表中添加或删除列，则需要手动更新Redshift表以进行相应更改。
最后，用于从 MySQL 表中提取更新行的查询会影响 MySQL 数据库的性能。
如果这些缺点中的任何一个是破坏者，那么下一个方法适合您。
3. 使用 Binlog 更改数据捕获
更改数据捕获 (CDC) 是一种技术，可捕获对 MySQL 中的数据所做的更改并将其应用于目标 Redshift 表。类似增量选择和复制，它只导入已更改数据，而非整个数据库。
然而，与增量 SELECT 和 COPY 不同，CDC 允许您实现 MySQL 到 Redshift 的真正复制。
为了使用CDC方法对MySQL数据库进行操作，必须启用二进制日志（binlog）。使用Binlog，您可以以流的形式记录数据的更改，从而实现接近实时的复制。
除了能捕获数据的变化（插入、更新和删除），Binlog 还能够记录表结构的变化，例如添加或删除列。它确保 Redshift 中已经删除的行也在 MySQL 中删除。
Binlog 入门
当您将 CDC 与 binlog 结合使用时，您实际上是在编写一个应用程序，该应用程序将流数据从 MySQL 读取、转换和导入到 Redshift。
要执行此操作，您可使用开源库 mysql-replication-listener。通过使用这个 C++ 库，我们可以实时从 MySQL binlog 中读取数据，而且支持流式 API。高级 API 也可用于多种语言：kodama (Ruby) 和python-mysql-replication (Python)。
1. 设置
首先，设置 MySQL 配置参数以启用 binlog。以下是binlog相关参数列表：
java:
log_bin = /file_path/mysql-bin.log
Binlog_format参数的设置影响着Binlog事件以何种方式存储在Binlog文件中的格式。支持 3 种格式：语句、混合和行。
语句格式将查询按原样保存在 binlog 文件中（例如UPDATE SET firstname=’Tom’ WHERE id=293;）。尽管它减小了 binlog 文件的大小，但在用于复制时却存在潜在问题。
要复制到 Redshift，请使用行格式。
行格式将更改的值保存在 binlog 文件中。它增加了 binlog 文件大小，但可确保 MySQL 和 Amazon Redshift 之间的数据一致性。log_bin设置存储binlog文件的路径。binlog file retention period is determined by expire_logs_days.。
指定要复制的表需要在replicate-wild-do-table参数中设置。只有那些指定的表才能进入 binlog 文件。
我们建议将 binlog 文件保留几天。这可确保您有时间解决复制过程中出现的任何问题。
如果您使用 MySQL 复制从服务器作为源，则将 指定log-slave-updates为 TRUE很重要。否则，在复制主服务器上所做的数据更改将不会记录在 binlog 中。
此外，您的 MySQL 帐户需要具有以下权限才能执行复制相关任务：
复制从站
选择
重新加载
复制客户端
锁表
2. 导出和转换
使用 binlog 时，实际上“export”的是 MySQL binlog 文件的实时数据流。binlog 数据的交付方式取决于您使用的 API。
例如，对于 Kodama，binlog 数据以 binlog 事件流的形式交付。
Kodama 允许您为不同的事件类型（插入、更新、删除、更改表、创建表等）注册事件处理程序。您的应用程序将接收二进制日志事件。输出将被生成，以便用于针对 Redshift 导入的数据更改或者表结构更改。
数据更改导入类似于我们其他复制方法的转换步骤。然而，与其他的不同，binlog 允许您处理已删除的事件。您需要专门处理已删除的事件以维护Redshift 上传性能。
3. 进口
最后，是时候导入您的 binlog 数据流了。
问题是 Redshift 没有蒸汽上传功能。使用我们在增量 SELECT 和 COPY 方法中概述的 DELSERT 导入技术。
Binlog 的缺点
虽然Binlog是从MySQL复制到Redshift的理想方法，但它仍存在一些不足之处。构建您的 CDC 应用程序需要认真的开发工作。
除了我们上面描述的数据流之外，您还必须构建：
交易管理。您应该跟踪数据流性能，以避免错误导致您的应用程序停止读取二进制日志数据。事务管理确保您可以从上次中断的地方继续。
数据缓冲和重试。当数据被发送时，Redshift 可能会出现不可用的情况。您的应用程序需要缓冲未发送的数据，直到 Redshift 集群重新联机。如果此步骤操作不当，可能会导致数据丢失或重复数据。
表模式更改支持。表模式更改二进制日志事件（更改/添加/删除表）作为本机 MySQL SQL 语句出现，它不会按原样在 Redshift 上运行。为了使表架构更改得到支持，你需要将 MySQL语句转换成对应的Amazon Redshift语句。
4. 使用 ETL 即服务
借助 ETL 工具，您可以近乎实时地将数据复制到 Redshift。
与 CDC 方法不同，此类工具可以管理整个复制过程并自动将 MySQL 数据类型映射为 Redshift 使用的格式，因此您不必这样做。您可以将多个 MySQL 数据库（以及其他类型的数据库）同时同步到 Redshift。
此外，设置过程简单而简短。
使用 Amazon Redshift 充分利用 MySQL
即使您将 MySQL 用作业务的基础，但它在数据分析方面的限制是公认的。Redshift 为您的 BI 需求提供了一个简单、强大的解决方案。MySQL 和 Redshift 可以将您的业务推向新的高度。
多种方法可用于从 MySQL 复制数据到 Redshift，如您所见。方法从简单到复杂，从非常缓慢到接近实时。您选择的方法取决于几个因素：
复制频率
MySQL 数据集的大小
可用的开发者资源
记住：使用变更数据捕获（CDC）是最快、最真实的复制方法，它利用 MySQL 的 binlog。缺点是需要开发人员数小时来构建和维护应用程序。
위 내용은 MySQL에서 Redshift로 데이터를 복사하는 이유의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!