DPI エンジンを使用したリアルタイム ETL - Lindorm - Alibaba Cloud ドキュメントセンター

DPI エンジンは、抽出・変換・書き出し (ETL) SQL を使用して、リアルタイムのテーブルレベルの事前計算を行います。この機能は、リアルタイムのマテリアライズドビューとして機能します。このトピックでは、ETL SQL を使用して、ワイドテーブルデータをリアルタイムで同期および事前計算する方法について説明します。

前提条件

Stream DPI エンジンを有効化していること。
LindormTable をアクティベートしていること。

シナリオ 1: リアルタイムミラーテーブル

説明

ソーステーブルのデータをリアルタイムで同期できます。これは、データ共有、データバックアップ、読み書き分離、異種インデックス作成などのさまざまなビジネスシナリオで役立ちます。

データ準備

MySQL コマンドラインから LindormTable に接続します。

ソーステーブルとミラーテーブルを作成します。

-- ソーステーブルを作成
CREATE TABLE source(p1 INT, c1 DOUBLE, PRIMARY KEY(p1));

-- ミラーテーブルを作成
CREATE TABLE sink(p1 INT, c1 DOUBLE, PRIMARY KEY(p1));

ETL ジョブの送信

MySQL コマンドラインを使用してストリームエンジンに接続します。

ETL を作成します。

CREATE ETL sync_etl AS INSERT INTO sink SELECT * FROM source;

データ検証

MySQL コマンドラインから LindormTable に接続します。

ソーステーブルにデータを挿入します。

INSERT INTO source(p1, c1) VALUES(0, 0.0);
INSERT INTO source(p1, c1) VALUES(1, 1.0);
INSERT INTO source(p1, c1) VALUES(2, 2.0);
INSERT INTO source(p1, c1) VALUES(3, 3.0);
INSERT INTO source(p1, c1) VALUES(4, 4.0);

ミラーテーブルのデータをクエリします。

SELECT * FROM sink;

次の結果が返されます。

+------+------+
| p1   | c1   |
+------+------+
|    0 |  0.0 |
|    1 |  1.0 |
|    2 |  2.0 |
|    3 |  3.0 |
|    4 |  4.0 |
+------+------+

シナリオ 2: リアルタイム事前計算

説明

DPI エンジンは、広範な事前計算機能を提供します。この例では、複数テーブルの事前結合を実行する方法を示します。事前結合は、複数のテーブルのデータを事前にマージします。これにより、クエリごとに JOIN 操作を実行する必要がなくなり、長い応答時間と高い計算オーバーヘッドを防ぐことができます。

データ準備

MySQL コマンドラインから LindormTable に接続します。

2 つのソーステーブル、user_tbl (ユーザー情報) と order_tbl (ユーザー注文情報) を作成します。次に、order_tbl テーブルに idx1 という名前のセカンダリインデックスを作成します。

-- ソーステーブル 1: user_tbl を作成
CREATE TABLE `user_tbl` (
    `user_id` varchar NOT NULL,
    `user_name` varchar,
    `user_addr` varchar,
    PRIMARY KEY (`user_id`)
);

-- ソーステーブル 2: order_tbl を作成
CREATE TABLE `order_tbl` (
    `order_id` varchar NOT NULL,
    `user_id` varchar,
    `product_name` varchar,
    `price` decimal(38, 20),
    PRIMARY KEY (`order_id`)
);

-- ソーステーブル order_tbl のインデックス idx1 を作成
CREATE INDEX idx1 ON `order_tbl`(user_id desc) WITH (COMPRESSION='ZSTD');

統合データを格納するためのシンクテーブル user_order_tbl を作成します。

CREATE TABLE `user_order_tbl` (
    `order_id` varchar NOT NULL,
    `user_id` varchar,
    `product_name` varchar,
    `price` decimal(38, 20),
    `user_name` varchar,
    `user_addr` varchar,
    `user_addr_code` varchar,
    PRIMARY KEY (`order_id`)
) WITH (MUTABILITY = 'MUTABLE_UDT');

ETL ジョブの送信

MySQL コマンドラインを使用してストリームエンジンに接続します。
ETL ジョブを作成し、データ統合ロジックを追加します。
ロジックは次のとおりです。
1. user_id フィールドでソーステーブル order_tbl と user_tbl を結合して、ユーザー情報を注文データに関連付けます。
2. 正規表現 REGEXP_EXTRACT を使用して、user_addr フィールドから住所コードを抽出します。
3. ETL ジョブを作成して、統合データをシンクテーブル user_order_tbl に挿入します。これにより、ユーザー情報と注文データの両方を含む単一のテーブルが作成されます。
```
CREATE ETL join_etl AS
INSERT INTO `lindorm_table`.`default`.`user_order_tbl` (
    order_id,
    user_id,
    product_name,
    price,
    user_name,
    user_addr,
    user_addr_code
)
SELECT
    o.order_id,
    o.user_id,
    o.product_name,
    o.price,
    u.user_name,
    u.user_addr,
    REGEXP_EXTRACT(u.user_addr, '#(.*?)$', 1) AS user_addr_code
FROM
    `lindorm_table`.`default`.`order_tbl` o
    JOIN `lindorm_table`.`default`.`user_tbl` u
    ON o.user_id = u.user_id;
```

データ検証

MySQL コマンドラインから LindormTable に接続します。

ソーステーブルにデータを挿入します。

INSERT INTO user_tbl (user_id, user_name, user_addr) VALUES
('U001', 'Zhang San', 'Chaoyang District, Beijing#100000'),
('U002', 'Li Si', 'Pudong New Area, Shanghai#200000'),
('U003', 'Wang Wu', 'Tianhe District, Guangzhou#510000'),
('U004', 'Zhao Liu', 'Nanshan District, Shenzhen#518000');

INSERT INTO order_tbl (order_id, user_id, product_name, price) VALUES
('O1001', 'U001', 'Laptop', 8999.00),
('O1002', 'U001', 'Wireless Mouse', 159.00),
('O1003', 'U002', 'Smartphone', 6999.00),
('O1004', 'U002', 'Bluetooth Headset', 299.00),
('O1005', 'U003', 'Tablet', 3499.00),
('O1006', 'U004', 'Mechanical Keyboard', 799.00),
('O1007', 'U004', 'Monitor', 1299.00);

シンクテーブルをクエリします。ETL ジョブは、統合ロジックに基づいてデータをフィルター処理し、結果をリアルタイムでシンクテーブルに書き込みます。

SELECT * FROM user_order_tbl;

次の結果が返されます。

+----------+---------+-----------------------+---------------------------+-----------+------------------------------------+----------------+
| order_id | user_id | product_name          | price                     | user_name | user_addr                          | user_addr_code |
+----------+---------+-----------------------+---------------------------+-----------+------------------------------------+----------------+
| O1001    | U001    | Laptop                | 8999.00000000000000000000 | Zhang San | Chaoyang District, Beijing#100000    | 100000         |
| O1002    | U001    | Wireless Mouse        |  159.00000000000000000000 | Zhang San | Chaoyang District, Beijing#100000    | 100000         |
| O1003    | U002    | Smartphone            | 6999.00000000000000000000 | Li Si     | Pudong New Area, Shanghai#200000   | 200000         |
| O1004    | U002    | Bluetooth Headset     |  299.00000000000000000000 | Li Si     | Pudong New Area, Shanghai#200000   | 200000         |
| O1005    | U003    | Tablet                | 3499.00000000000000000000 | Wang Wu   | Tianhe District, Guangzhou#510000  | 510000         |
| O1006    | U004    | Mechanical Keyboard   |  799.00000000000000000000 | Zhao Liu  | Nanshan District, Shenzhen#518000  | 518000         |
| O1007    | U004    | Monitor               | 1299.00000000000000000000 | Zhao Liu  | Nanshan District, Shenzhen#518000  | 518000         |
+----------+---------+-----------------------+---------------------------+-----------+------------------------------------+----------------+