ライブマテリアライズドビューでMySQLとPostgresに参加する方法

多くのマイクロサービスで構成されるプロジェクトで作業している場合、複数のデータベースも含まれる可能性があります。

たとえば、MySQLデータベースPostgreSQLデータベースがあり、どちらも別々のサーバーで実行されているとします。

通常、2つのデータベースのデータを結合するには、データを結合する新しいマイクロサービスを導入する必要があります。しかし、これはシステムの複雑さを増します。

このチュートリアルでは、マテリアライズを使用して、ライブのマテリアライズドビューでMySQLとPostgresを結合します。その後、それを直接クエリし、標準SQLを使用して両方のデータベースからリアルタイムで結果を取得できるようになります。

マテリアライズは、Rustで記述されたソース利用可能なストリーミングデータベースであり、データの変更時にSQLクエリ(マテリアライズドビュー)の結果をメモリに保持します。

チュートリアルには、の使用を開始できるデモプロジェクトが含まれていますdocker-compose

使用するデモプロジェクトでは、模擬Webサイトで注文を監視します。後でカートが長期間放棄されたときに通知を送信するために使用できるイベントを生成します。

デモプロジェクトのアーキテクチャは次のとおりです。

mz-abandoned-cart-demo

前提条件


デモで使用するすべてのサービスはDockerコンテナー内で実行されるため、DockerとDockerComposeではなくラップトップやサーバーに追加のサービスをインストールする必要はありません。

DockerとDockerComposeがまだインストールされていない場合は、ここでその方法に関する公式の指示に従うことができます。

概要

上の図に示すように、次のコンポーネントがあります。

  • 継続的に注文を生成するための模擬サービス。
  • 注文はMySQLデータベースに保存されます。
  • データベースへの書き込みが発生すると、DebeziumはMySQLからRedpandaトピックに変更をストリーミングします。
  • また、ユーザーを取得できるPostgresデータベースもあります。
  • 次に、このRedpandaトピックをPostgresデータベースのユーザーと一緒に直接Materializeに取り込みます。
  • マテリアライズでは、注文とユーザーを結合し、フィルタリングを実行して、放棄されたカート情報を表示するマテリアライズドビューを作成します。
  • 次に、放棄されたカートデータを新しいRedpandaトピックに送信するためのシンクを作成します。
  • 最後に、Metabaseを使用してデータを視覚化します。
  • 後で、その新しいトピックの情報を使用して、ユーザーに通知を送信し、カートが放棄されたことをユーザーに通知することができます。

ここでの補足として、レッサーパンダの代わりにカフカを使用してもまったく問題ありません。すべてのKafkaコンポーネントの代わりに単一のRedpandaインスタンスを実行できるため、Redpandaがテーブルにもたらすシンプルさが気に入っています。

デモを実行する方法

まず、リポジトリのクローンを作成することから始めます。

git clone https://github.com/bobbyiliev/materialize-tutorials.git

その後、ディレクトリにアクセスできます。

cd materialize-tutorials/mz-join-mysql-and-postgresql

まず、Redpandaコンテナを実行することから始めましょう。

docker-compose up -d redpanda

イメージを作成します。

docker-compose build

最後に、すべてのサービスを開始します。

docker-compose up -d

マテリアライズCLIを起動するには、次のコマンドを実行できます。

docker-compose run mzcli

postgres-clientこれは、プリインストールされたDockerコンテナへのショートカットにすぎません。すでにお持ちの場合は、代わりにpsql実行できますpsql -U materialize -h localhost -p 6875 materialize

マテリアライズカフカソースを作成する方法

ordersマテリアライズCLIを使用しているので、データベース内のテーブルmysql.shopをRedpandaソースとして定義しましょう。

CREATE SOURCE orders
FROM KAFKA BROKER 'redpanda:9092' TOPIC 'mysql.shop.orders'
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY 'http://redpanda:8081'
ENVELOPE DEBEZIUM;

orders次のステートメントを実行して、ソースから使用可能な列を確認する場合:

SHOW COLUMNS FROM orders;

MaterializeがRedpandaレジストリからメッセージスキーマデータを取得しているため、各属性に使用する列タイプを認識していることがわかります。

    name      | nullable |   type
--------------+----------+-----------
 id           | f        | bigint
 user_id      | t        | bigint
 order_status | t        | integer
 price        | t        | numeric
 created_at   | f        | text
 updated_at   | t        | timestamp

マテリアライズドビューを作成する方法

次に、最初のマテリアライズドビューを作成して、ordersRedpandaソースからすべてのデータを取得します。

CREATE MATERIALIZED VIEW orders_view AS
SELECT * FROM orders;
CREATE MATERIALIZED VIEW abandoned_orders AS
    SELECT
        user_id,
        order_status,
        SUM(price) as revenue,
        COUNT(id) AS total
    FROM orders_view
    WHERE order_status=0
    GROUP BY 1,2;

これで、を使用SELECT * FROM abandoned_orders;して結果を確認できます。

SELECT * FROM abandoned_orders;

マテリアライズド・ビューの作成の詳細については、マテリアライズドキュメンテーションの「マテリアライズド・ビュー」セクションを確認してください。

Postgresソースを作成する方法

MaterializeでPostgresソースを作成する方法は2つあります。

  • MySQLソースで行ったのと同じようにDebeziumを使用します。
  • Postgres Materialize Sourceを使用します。これにより、MaterializeをPostgresに直接接続できるため、Debeziumを使用する必要がありません。

このデモでは、Postgres Materialize Sourceを使用方法のデモンストレーションとして使用しますが、代わりにDebeziumを自由に使用してください。

Postgres Materialize Sourceを作成するには、次のステートメントを実行します。

CREATE MATERIALIZED SOURCE "mz_source" FROM POSTGRES
CONNECTION 'user=postgres port=5432 host=postgres dbname=postgres password=postgres'
PUBLICATION 'mz_source';

上記のステートメントの簡単な要約:

  • MATERIALIZED:PostgreSQLソースのデータを具体化します。すべてのデータはメモリに保持され、ソースを直接選択できるようになります。
  • mz_source:PostgreSQLソースの名前。
  • CONNECTION:PostgreSQL接続パラメータ。
  • PUBLICATION:マテリアライズにストリーミングされるテーブルを含むPostgreSQLパブリケーション。

PostgreSQLソースを作成したら、PostgreSQLテーブルをクエリできるようにするために、アップストリームパブリケーションの元のテーブルを表すビューを作成する必要があります。

この場合、呼び出されるテーブルは1つしかないusersため、実行する必要があるステートメントは次のとおりです。

CREATE VIEWS FROM SOURCE mz_source (users);

使用可能なビューを確認するには、次のステートメントを実行します。

SHOW FULL VIEWS;

それが完了したら、新しいビューを直接クエリできます。

SELECT * FROM users;

次に、先に進んで、さらにいくつかのビューを作成しましょう。

カフカシンクを作成する方法

シンクを使用すると、Materializeから外部ソースにデータを送信できます。

このデモでは、レッサーパンダを使用します。

RedpandaはKafkaAPIと互換性があり、MaterializeはKafkaソースからのデータを処理するのと同じようにRedpandaからのデータを処理できます。

大量の未払い注文をすべて保持するマテリアライズドビューを作成しましょう。

 CREATE MATERIALIZED VIEW high_value_orders AS
      SELECT
        users.id,
        users.email,
        abandoned_orders.revenue,
        abandoned_orders.total
      FROM users
      JOIN abandoned_orders ON abandoned_orders.user_id = users.id
      GROUP BY 1,2,3,4
      HAVING revenue > 2000;

ご覧のとおり、ここではusers、Postgresソースから直接abandond_ordersデータを取り込んでいるビューと、Redpandaトピックからデータを取り込んでいるビューを実際に結合しています。

上記のマテリアライズドビューのデータを送信するシンクを作成しましょう。

CREATE SINK high_value_orders_sink
    FROM high_value_orders
    INTO KAFKA BROKER 'redpanda:9092' TOPIC 'high-value-orders-sink'
    FORMAT AVRO USING
    CONFLUENT SCHEMA REGISTRY 'http://redpanda:8081';

これで、Redpandaコンテナに接続してrpk topic consumeコマンドを使用すると、トピックからレコードを読み取ることができるようになります。

rpkただし、現時点では、 AVRO形式であるため、結果をプレビューすることはできません。レッサーパンダは将来これを実装する可能性が最も高いですが、今のところ、実際にトピックをマテリアライズにストリーミングしてフォーマットを確認することができます。

まず、自動的に生成されたトピックの名前を取得します。

SELECT topic FROM mz_kafka_sinks;

出力:

                              topic
-----------------------------------------------------------------
 high-volume-orders-sink-u12-1637586945-13670686352905873426

トピック名の生成方法の詳細については、こちらのドキュメントをご覧ください。

次に、このレッサーパンダのトピックから新しいマテリアライズドソースを作成します。

CREATE MATERIALIZED SOURCE high_volume_orders_test
FROM KAFKA BROKER 'redpanda:9092' TOPIC ' high-volume-orders-sink-u12-1637586945-13670686352905873426'
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY 'http://redpanda:8081';

それに応じてトピック名を変更してください!

最後に、この新しいマテリアライズド・ビューを照会します。

SELECT * FROM high_volume_orders_test LIMIT 2;

トピックにデータが含まれるようになったので、他のサービスを接続してデータを使用し、たとえば電子メールやアラートをトリガーすることができます。

メタベースを接続する方法

デモをローカルで実行している場合、またはサーバーでデモを実行している場合は、メタベースインスタンスにアクセスするためにアクセスしてください。次に、手順に従ってメタベースのセットアップを完了します。http://localhost:3030http://your_server_ip:3030

データのソースとして必ずマテリアライズを選択してください。

準備ができたら、標準のPostgreSQLデータベースの場合と同じようにデータを視覚化できます。

デモを停止する方法

すべてのサービスを停止するには、次のコマンドを実行します。

docker-compose down

結論

ご覧のとおり、これはマテリアライズの使用方法の非常に簡単な例です。マテリアライズを使用して、さまざまなソースからデータを取り込み、それをさまざまな宛先にストリーミングできます。

ソース:https ://www.freecodecamp.org/news/how-to-join-mysql-and-postgres-in-a-live-materialized-view/

 #mysql #postgre

What is GEEK

Buddha Community

ライブマテリアライズドビューでMySQLとPostgresに参加する方法

ライブマテリアライズドビューでMySQLとPostgresに参加する方法

多くのマイクロサービスで構成されるプロジェクトで作業している場合、複数のデータベースも含まれる可能性があります。

たとえば、MySQLデータベースPostgreSQLデータベースがあり、どちらも別々のサーバーで実行されているとします。

通常、2つのデータベースのデータを結合するには、データを結合する新しいマイクロサービスを導入する必要があります。しかし、これはシステムの複雑さを増します。

このチュートリアルでは、マテリアライズを使用して、ライブのマテリアライズドビューでMySQLとPostgresを結合します。その後、それを直接クエリし、標準SQLを使用して両方のデータベースからリアルタイムで結果を取得できるようになります。

マテリアライズは、Rustで記述されたソース利用可能なストリーミングデータベースであり、データの変更時にSQLクエリ(マテリアライズドビュー)の結果をメモリに保持します。

チュートリアルには、の使用を開始できるデモプロジェクトが含まれていますdocker-compose

使用するデモプロジェクトでは、模擬Webサイトで注文を監視します。後でカートが長期間放棄されたときに通知を送信するために使用できるイベントを生成します。

デモプロジェクトのアーキテクチャは次のとおりです。

mz-abandoned-cart-demo

前提条件


デモで使用するすべてのサービスはDockerコンテナー内で実行されるため、DockerとDockerComposeではなくラップトップやサーバーに追加のサービスをインストールする必要はありません。

DockerとDockerComposeがまだインストールされていない場合は、ここでその方法に関する公式の指示に従うことができます。

概要

上の図に示すように、次のコンポーネントがあります。

  • 継続的に注文を生成するための模擬サービス。
  • 注文はMySQLデータベースに保存されます。
  • データベースへの書き込みが発生すると、DebeziumはMySQLからRedpandaトピックに変更をストリーミングします。
  • また、ユーザーを取得できるPostgresデータベースもあります。
  • 次に、このRedpandaトピックをPostgresデータベースのユーザーと一緒に直接Materializeに取り込みます。
  • マテリアライズでは、注文とユーザーを結合し、フィルタリングを実行して、放棄されたカート情報を表示するマテリアライズドビューを作成します。
  • 次に、放棄されたカートデータを新しいRedpandaトピックに送信するためのシンクを作成します。
  • 最後に、Metabaseを使用してデータを視覚化します。
  • 後で、その新しいトピックの情報を使用して、ユーザーに通知を送信し、カートが放棄されたことをユーザーに通知することができます。

ここでの補足として、レッサーパンダの代わりにカフカを使用してもまったく問題ありません。すべてのKafkaコンポーネントの代わりに単一のRedpandaインスタンスを実行できるため、Redpandaがテーブルにもたらすシンプルさが気に入っています。

デモを実行する方法

まず、リポジトリのクローンを作成することから始めます。

git clone https://github.com/bobbyiliev/materialize-tutorials.git

その後、ディレクトリにアクセスできます。

cd materialize-tutorials/mz-join-mysql-and-postgresql

まず、Redpandaコンテナを実行することから始めましょう。

docker-compose up -d redpanda

イメージを作成します。

docker-compose build

最後に、すべてのサービスを開始します。

docker-compose up -d

マテリアライズCLIを起動するには、次のコマンドを実行できます。

docker-compose run mzcli

postgres-clientこれは、プリインストールされたDockerコンテナへのショートカットにすぎません。すでにお持ちの場合は、代わりにpsql実行できますpsql -U materialize -h localhost -p 6875 materialize

マテリアライズカフカソースを作成する方法

ordersマテリアライズCLIを使用しているので、データベース内のテーブルmysql.shopをRedpandaソースとして定義しましょう。

CREATE SOURCE orders
FROM KAFKA BROKER 'redpanda:9092' TOPIC 'mysql.shop.orders'
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY 'http://redpanda:8081'
ENVELOPE DEBEZIUM;

orders次のステートメントを実行して、ソースから使用可能な列を確認する場合:

SHOW COLUMNS FROM orders;

MaterializeがRedpandaレジストリからメッセージスキーマデータを取得しているため、各属性に使用する列タイプを認識していることがわかります。

    name      | nullable |   type
--------------+----------+-----------
 id           | f        | bigint
 user_id      | t        | bigint
 order_status | t        | integer
 price        | t        | numeric
 created_at   | f        | text
 updated_at   | t        | timestamp

マテリアライズドビューを作成する方法

次に、最初のマテリアライズドビューを作成して、ordersRedpandaソースからすべてのデータを取得します。

CREATE MATERIALIZED VIEW orders_view AS
SELECT * FROM orders;
CREATE MATERIALIZED VIEW abandoned_orders AS
    SELECT
        user_id,
        order_status,
        SUM(price) as revenue,
        COUNT(id) AS total
    FROM orders_view
    WHERE order_status=0
    GROUP BY 1,2;

これで、を使用SELECT * FROM abandoned_orders;して結果を確認できます。

SELECT * FROM abandoned_orders;

マテリアライズド・ビューの作成の詳細については、マテリアライズドキュメンテーションの「マテリアライズド・ビュー」セクションを確認してください。

Postgresソースを作成する方法

MaterializeでPostgresソースを作成する方法は2つあります。

  • MySQLソースで行ったのと同じようにDebeziumを使用します。
  • Postgres Materialize Sourceを使用します。これにより、MaterializeをPostgresに直接接続できるため、Debeziumを使用する必要がありません。

このデモでは、Postgres Materialize Sourceを使用方法のデモンストレーションとして使用しますが、代わりにDebeziumを自由に使用してください。

Postgres Materialize Sourceを作成するには、次のステートメントを実行します。

CREATE MATERIALIZED SOURCE "mz_source" FROM POSTGRES
CONNECTION 'user=postgres port=5432 host=postgres dbname=postgres password=postgres'
PUBLICATION 'mz_source';

上記のステートメントの簡単な要約:

  • MATERIALIZED:PostgreSQLソースのデータを具体化します。すべてのデータはメモリに保持され、ソースを直接選択できるようになります。
  • mz_source:PostgreSQLソースの名前。
  • CONNECTION:PostgreSQL接続パラメータ。
  • PUBLICATION:マテリアライズにストリーミングされるテーブルを含むPostgreSQLパブリケーション。

PostgreSQLソースを作成したら、PostgreSQLテーブルをクエリできるようにするために、アップストリームパブリケーションの元のテーブルを表すビューを作成する必要があります。

この場合、呼び出されるテーブルは1つしかないusersため、実行する必要があるステートメントは次のとおりです。

CREATE VIEWS FROM SOURCE mz_source (users);

使用可能なビューを確認するには、次のステートメントを実行します。

SHOW FULL VIEWS;

それが完了したら、新しいビューを直接クエリできます。

SELECT * FROM users;

次に、先に進んで、さらにいくつかのビューを作成しましょう。

カフカシンクを作成する方法

シンクを使用すると、Materializeから外部ソースにデータを送信できます。

このデモでは、レッサーパンダを使用します。

RedpandaはKafkaAPIと互換性があり、MaterializeはKafkaソースからのデータを処理するのと同じようにRedpandaからのデータを処理できます。

大量の未払い注文をすべて保持するマテリアライズドビューを作成しましょう。

 CREATE MATERIALIZED VIEW high_value_orders AS
      SELECT
        users.id,
        users.email,
        abandoned_orders.revenue,
        abandoned_orders.total
      FROM users
      JOIN abandoned_orders ON abandoned_orders.user_id = users.id
      GROUP BY 1,2,3,4
      HAVING revenue > 2000;

ご覧のとおり、ここではusers、Postgresソースから直接abandond_ordersデータを取り込んでいるビューと、Redpandaトピックからデータを取り込んでいるビューを実際に結合しています。

上記のマテリアライズドビューのデータを送信するシンクを作成しましょう。

CREATE SINK high_value_orders_sink
    FROM high_value_orders
    INTO KAFKA BROKER 'redpanda:9092' TOPIC 'high-value-orders-sink'
    FORMAT AVRO USING
    CONFLUENT SCHEMA REGISTRY 'http://redpanda:8081';

これで、Redpandaコンテナに接続してrpk topic consumeコマンドを使用すると、トピックからレコードを読み取ることができるようになります。

rpkただし、現時点では、 AVRO形式であるため、結果をプレビューすることはできません。レッサーパンダは将来これを実装する可能性が最も高いですが、今のところ、実際にトピックをマテリアライズにストリーミングしてフォーマットを確認することができます。

まず、自動的に生成されたトピックの名前を取得します。

SELECT topic FROM mz_kafka_sinks;

出力:

                              topic
-----------------------------------------------------------------
 high-volume-orders-sink-u12-1637586945-13670686352905873426

トピック名の生成方法の詳細については、こちらのドキュメントをご覧ください。

次に、このレッサーパンダのトピックから新しいマテリアライズドソースを作成します。

CREATE MATERIALIZED SOURCE high_volume_orders_test
FROM KAFKA BROKER 'redpanda:9092' TOPIC ' high-volume-orders-sink-u12-1637586945-13670686352905873426'
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY 'http://redpanda:8081';

それに応じてトピック名を変更してください!

最後に、この新しいマテリアライズド・ビューを照会します。

SELECT * FROM high_volume_orders_test LIMIT 2;

トピックにデータが含まれるようになったので、他のサービスを接続してデータを使用し、たとえば電子メールやアラートをトリガーすることができます。

メタベースを接続する方法

デモをローカルで実行している場合、またはサーバーでデモを実行している場合は、メタベースインスタンスにアクセスするためにアクセスしてください。次に、手順に従ってメタベースのセットアップを完了します。http://localhost:3030http://your_server_ip:3030

データのソースとして必ずマテリアライズを選択してください。

準備ができたら、標準のPostgreSQLデータベースの場合と同じようにデータを視覚化できます。

デモを停止する方法

すべてのサービスを停止するには、次のコマンドを実行します。

docker-compose down

結論

ご覧のとおり、これはマテリアライズの使用方法の非常に簡単な例です。マテリアライズを使用して、さまざまなソースからデータを取り込み、それをさまざまな宛先にストリーミングできます。

ソース:https ://www.freecodecamp.org/news/how-to-join-mysql-and-postgres-in-a-live-materialized-view/

 #mysql #postgre