このトピックでは、DataWorks Data Integration を使用して MySQL パーティションテーブルにデータを書き込むためのベストプラクティスについて説明します。これらのプラクティスは、大規模な MySQL データセットの書き込みおよびクエリパフォーマンスの最適化に役立ちます。
前提条件
DataWorks で MySQL データベースをデータソースとして追加しておく必要があります。詳細については、「MySQL データソース」をご参照ください。
事前準備
-
DataWorks Data Integration がデータを読み取るためのソーステーブルを作成します。
CREATE TABLE `user_tb1` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '名前', `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ); INSERT INTO user_tb1 (name, update_time) VALUES ('u1', '2023-12-28 09:52:05'); INSERT INTO user_tb1 (name, update_time) VALUES ('u2', '2023-12-25 09:52:05'); INSERT INTO user_tb1 (name, update_time) VALUES ('u3', '2023-12-26 09:52:05'); INSERT INTO user_tb1 (name, update_time) VALUES ('u3', '2023-12-27 09:52:05'); INSERT INTO user_tb1 (name, update_time) VALUES ('u4', '2023-12-28 09:52:05'); INSERT INTO user_tb1 (name, update_time) VALUES ('u5', '2023-12-28 09:52:05'); INSERT INTO user_tb1 (name, update_time) VALUES ('u6', '2023-12-28 09:52:05'); INSERT INTO user_tb1 (name, update_time) VALUES ('u7', '2023-12-28 09:52:05'); -
出力データ用の宛先テーブルを作成します。
このトピックでは、レンジパーティションテーブルと KEY パーティションテーブルを例として使用します。次のテーブルを作成します。
-
レンジパーティションテーブル
CREATE TABLE `pt_write_test_tb1` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(100) COMMENT '名前', `update_time` datetime COMMENT '更新時間', PRIMARY KEY (`id`,`update_time`), KEY `idx_update_time` (`update_time`) ) PARTITION BY RANGE COLUMNS(update_time) ( PARTITION pt20231216 VALUES LESS THAN ('20231216'), PARTITION pt20231217 VALUES LESS THAN ('20231217'), PARTITION pt20231218 VALUES LESS THAN ('20231218'), PARTITION pt20231219 VALUES LESS THAN ('20231219'), PARTITION pt20231220 VALUES LESS THAN ('20231220'), PARTITION pt20231221 VALUES LESS THAN ('20231221'), PARTITION pt20231222 VALUES LESS THAN ('20231222'), PARTITION pt20231223 VALUES LESS THAN ('20231223'), PARTITION pt20231224 VALUES LESS THAN ('20231224'), PARTITION pt20231225 VALUES LESS THAN ('20231225'), PARTITION pt20231226 VALUES LESS THAN ('20231226'), PARTITION pt20231227 VALUES LESS THAN ('20231227'), PARTITION pt20231228 VALUES LESS THAN ('20231228'), PARTITION pt20231229 VALUES LESS THAN ('20231229') ); -
KEY パーティションテーブル
CREATE TABLE `pt_write_test_tb2` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(100) COMMENT '名前', `update_time` datetime COMMENT '更新時間', PRIMARY KEY (`id`) ) PARTITION BY KEY(`id`) PARTITIONS 16;
-
パーティションテーブルへの書き込み
レンジパーティションテーブルへの書き込み
この例では、update_time フィールドをパーティションキーとして使用して、レンジパーティションテーブルにデータを書き込む方法を示します。
-
バッチ同期タスクを設定します。
キーポイント:
-
Data Source では、Table を
user_tb1に設定します。 -
Data Destination では、Table を
pt_write_test_tb1に設定します。
[ソース] では、[分割キー] を
idに設定します。[宛先] では、[競合時のポリシー] として [insert into (プライマリキー/制約競合でダーティデータを報告)] を選択します。説明Data Destination の Pre-Import Statement フィールドを使用して、同期タスクがデータを書き込む前に、指定した日付を上限とするパーティションを自動的に作成し、それより前の日付のデータを書き込めるようにすることもできます。
-
スケジューリングパラメーター (例:
pt_date=$[yyyymmdd]) を定義します。 -
Pre-Import Statement フィールドで、次のSQLステートメントを設定します。
ALTER TABLE `pt_write_test_tb1` ADD PARTITION (PARTITION pt${pt_date} VALUES LESS THAN ('${pt_date}'));
-
-
宛先テーブルのデータをクエリします。
-
テーブル内のすべてのデータをクエリします。
SELECT * FROM pt_write_test_tb1;結果の例:
+----+------+---------------------+ | id | name | update_time | +----+------+---------------------+ | 2 | u2 | 2023-12-25 09:52:05 | | 3 | u3 | 2023-12-26 09:52:05 | | 4 | u3 | 2023-12-27 09:52:05 | | 1 | u1 | 2023-12-28 09:52:05 | | 5 | u4 | 2023-12-28 09:52:05 | | 6 | u5 | 2023-12-28 09:52:05 | | 7 | u6 | 2023-12-28 09:52:05 | | 8 | u7 | 2023-12-28 09:52:05 | +----+------+---------------------+ 8 rows in set (0.01 sec) -
単一のパーティションをクエリします。
SELECT * FROM pt_write_test_tb1 partition (pt20231229);結果の例:
+----+------+---------------------+ | id | name | update_time | +----+------+---------------------+ | 1 | u1 | 2023-12-28 09:52:05 | | 5 | u4 | 2023-12-28 09:52:05 | | 6 | u5 | 2023-12-28 09:52:05 | | 7 | u6 | 2023-12-28 09:52:05 | | 8 | u7 | 2023-12-28 09:52:05 | +----+------+---------------------+ 5 rows in set (0.00 sec)
-
KEY パーティションテーブルへの書き込み
この例では、プライマリキーの id をパーティションキーとして使用して、KEY パーティションテーブルにデータを書き込む方法を示します。
-
バッチ同期タスクを設定します。
キーポイント:
-
Data Source では、Table を
user_tb1に設定します。 -
Data Destination では、Table を
pt_write_test_tb2に設定します。
[ソース] では、[分割キー] を
idに設定します。[宛先] では、[競合時のポリシー] として [insert into (プライマリキー/制約競合でダーティデータを報告)] を選択します。 -
-
宛先テーブルのデータをクエリします。
-
テーブル内のすべてのデータをクエリします。
select * from pt_write_test_tb2 order by id;結果の例:
+----+------+---------------------+ | id | name | update_time | +----+------+---------------------+ | 1 | u1 | 2023-12-28 09:52:05 | | 2 | u2 | 2023-12-25 09:52:05 | | 3 | u3 | 2023-12-26 09:52:05 | | 4 | u3 | 2023-12-27 09:52:05 | | 5 | u4 | 2023-12-28 09:52:05 | | 6 | u5 | 2023-12-28 09:52:05 | | 7 | u6 | 2023-12-28 09:52:05 | | 8 | u7 | 2023-12-28 09:52:05 | +----+------+---------------------+ 8 rows in set (0.00 sec) -
各パーティションのデータを表示します。
SELECT TABLE_NAME , PARTITION_NAME , TABLE_ROWS, UPDATE_TIME FROM information_schema.partitions WHERE TABLE_NAME = 'pt_write_test_tb2';結果の例:
+-------------------+----------------+------------+---------------------+ | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | UPDATE_TIME | +-------------------+----------------+------------+---------------------+ | pt_write_test_tb2 | p0 | 0 | NULL | | pt_write_test_tb2 | p1 | 0 | NULL | | pt_write_test_tb2 | p10 | 0 | NULL | | pt_write_test_tb2 | p11 | 1 | 2024-02-29 14:49:14 | | pt_write_test_tb2 | p12 | 0 | NULL | | pt_write_test_tb2 | p13 | 0 | NULL | | pt_write_test_tb2 | p14 | 1 | 2024-02-29 14:49:15 | | pt_write_test_tb2 | p15 | 1 | 2024-02-29 14:49:16 | | pt_write_test_tb2 | p2 | 1 | 2024-02-29 14:49:17 | | pt_write_test_tb2 | p3 | 0 | NULL | | pt_write_test_tb2 | p4 | 1 | 2024-02-29 14:49:14 | | pt_write_test_tb2 | p5 | 1 | 2024-02-29 14:49:15 | | pt_write_test_tb2 | p6 | 0 | NULL | | pt_write_test_tb2 | p7 | 0 | NULL | | pt_write_test_tb2 | p8 | 1 | 2024-02-29 14:49:16 | | pt_write_test_tb2 | p9 | 1 | 2024-02-29 14:49:17 | +-------------------+----------------+------------+---------------------+ 16 rows in set (0.01 sec)
-