クラウドデータベースへの変更データのエクスポート
オンプレミスのPostgreSQLデータベースにあるmarket_leadsというテーブルのデータを、クラウドデータベースにあるall_leadsというテーブルに同期する場合を例に説明します。Zoho DataPrepの 増分取得を使用して、新規データと更新済みデータのみをクラウドデータベースに同期します。
クラウドデータベースにデータをエクスポートする際、Zoho DataPrepでは、新しい行をテーブルに追加する方法を選択できます。
- ［追加］：既存の行を削除せずに、新規および更新済みの行をテーブルに追加します。
- ［上書き］：テーブル内の既存の行を、新規および更新済みの行に置き換えます。
Zoho DataPrepでは、［追加］オプションを使用した場合、PostgreSQL内の更新済みデータは更新されず、更新済みの行がエクスポート先のテーブルに追加されます。そのため、データが重複する可能性があります。これを回避するには、次の方法を実行してください。
A. ノーコードでの解決方法
クラウドデータベース内の更新済みデータを更新するには、出力テーブルのデータと受信データを比較し、DataPrepでデータを統合してから、統合したデータで出力テーブルを上書きする必要があります。手順は次のとおりです。
1. 元データのテーブルのインポート
クラウドデータベースのmarket_leadsテーブルからデータを 増分方式でDataPrepにインポートします。これは、all_leads出力テーブルに統合する必要がある、新規および更新済みの見込み客データを含む元データのテーブルです。
2. タイムスタンプ列の追加
タイムスタンプを含む新しい列を追加する数式加工の手順は、次のとおりです。
- market_leadsテーブルのDataPrep Studioページで、［加工］メニューをクリックし、［数式を追加］オプションを選択します。
- ［新しい列の名前］欄に、ZDP_timestampなどの名前を入力します。
- ［current_timestamp()］関数を［数式］欄に挿入します。
- ［適用］をクリックします。出力内容が新しい列に生成されます。詳しくは、数式関数のヘルプをご覧ください。
3. 出力テーブルのインポート
出力テーブルall_leadsをクラウドデータベース（例：PostgreSQL）からDataPrepにインポートします。このデータを元データとの比較対象として使用し、新規または更新済みのデータを特定します。
4. テーブルの追加
次に、両方のテーブルを追加し、ZDP_timestamp列を含めます。
5. データの重複を削除
追加後のデータセットから重複データを特定し、タイムスタンプが古い、またはタイムスタンプがないデータを削除します。重複を削除加工をDataPrep Studioページで使用します。
列単位で重複を削除するには
1. ［加工］メニューをクリックし、［重複を削除］をクリックして、続いて［列単位］を選択します。
2. 重複を識別するため、ID列を選択します。重複を検索する際に、大文字と小文字を区別しない設定や、空白文字を無視する設定も選択できます。
3. ［手動条件］を選択し、ZDP_timestamp列のタイムスタンプが最新の場合に行を保持する条件を入力します。
6. 統合したデータのエクスポート
次に、クリーニング済みのテーブルを、既存のテーブルであるAll_leadsマスターテーブルにエクスポートし、［上書き］を選択します。このマスターテーブルには、すべての見込み客データがクラウドデータベースに保存されています。
7. データ構造の照合の確認
データ構造を照合できるように、クラウドデータベースのエクスポート先テーブルに空のZDP_timestamp列を追加します。
エクスポートの失敗を防ぐため、データ構造の照合が完了していることを確認してください。こちらをクリックすると、データ構造の照合の詳細を確認できます。
このエクスポート先の設定では、パイプラインを実行するたびに、新しく追加されたデータと更新されたデータを含むデータセット全体がエクスポートされ、エクスポート先テーブルが上書きされるため、重複を防止できます。
これにより、マスターテーブルを常に最新の見込み客データに保つことができます。
B. データベースを使用する方法（プロコードソリューション）
この方法では、Zoho DataPrepとクラウドデータベースを組み合わせて使用します。
- DataPrepでは、タイムスタンプ列を追加してデータを準備し、データベースに増分挿入するだけです。
- クラウドデータベースでは、分析関数を含むマテリアライズドビューを使用して、最新のデータを取得します。
これにより、手動で挿入、更新、削除を行わなくても、出力データセットを常にクリーンな状態に保つことができます。
1. DataPrepでのデータの準備
a. 元データテーブルのインポート
market_leadsテーブルを増分方式でクラウドデータベース（例：PostgreSQL）からDataPrepにインポートします。
このテーブルには、新規および更新済みの見込み客データが含まれています。
b. タイムスタンプ列の追加
market_leadsテーブルで、数式関数を使用して、現在のタイムスタンプを格納する新しい列（ZDP_timestamp）を追加します。これにより、新規または更新済みの各データがZoho DataPrepから最後に更新された日時を追跡できます。
タイムスタンプを格納する新しい列を追加するための数式による加工。
- market_leadsテーブルのDataPrepのStudioページで、［加工］メニューをクリックし、［数式を追加］を選択します。
- ［新しい列名］欄に、ZDP_timestampなどの名前を入力します。
- current_timestamp()関数を［数式］欄に挿入します。
- ［適用］をクリックします。出力内容が新しい列に生成されます。数式関数の詳細については、こちらをクリックして確認できます。
c. クラウドデータベースへのエクスポート
テーブルをクラウドデータベース（例：PostgreSQL）のall_leads_rawテーブルにエクスポートします。［追加］オプションを使用します。
この時点で、テーブル（all_leads_raw）には、同じレコード（IDごと）の複数のバージョンが、異なるタイムスタンプで含まれています。
2. クラウドデータベースでのクリーンなビューの作成
ウィンドウ関数を使用して、all_leads_cleanというビューを作成し、IDごとに常に最新のデータが返されるようにします。
- CREATE VIEWall_leads_clean AS
- SELECTid, name, modified_time, ZDP_timestamp
- FROM(
- SELECTid,
- name,
- modified_time,
- ZDP_timestamp,
- ROW_NUMBER() OVER(
- PARTITION BYid
- ORDER BYZDP_timestamp DESC
- ) ASrn
- FROMall_leads_raw
- ) ranked
- WHERErn = 1;
3. クリーンデータのクエリー
all_leads_cleanをクエリーすると、重複が排除された最新のデータセットが常に取得されます。
- SELECT * FROM all_leads_clean;
例。
このテーブルには、重複データ/updates)を含むすべてのデータが、タイムスタンプ付きで格納されています。
|
id |
name |
modified_time |
ZDP_timestamp |
|
1 |
Alan |
2025-06-20 12:22:00 |
2025-08-10 10:15:00 |
|
2 |
Francis |
2024-06-20 12:25:00 |
2025-08-10 10:15:00 |
|
3 |
Dan |
2024-06-20 12:27:00 |
2025-08-10 10:15:00 |
|
1 |
Al |
2024-06-21 01:29:00 |
2025-08-11 09:45:00 |
|
4 |
Gaja |
2024-06-21 01:30:00 |
2025-08-11 09:45:00 |
|
5 |
John |
2024-06-21 01:35:00 |
2025-08-11 09:45:00 |
このビューでは、IDごとに最新のデータのみが返されます。
|
id |
name |
modified_time |
ZDP_timestamp |
|
1 |
Al |
2024-06-21 01:29:00 |
2025-08-11 09:45:00 |
|
2 |
Francis |
2024-06-20 12:25:00 |
2025-08-10 10:15:00 |
|
3 |
Dan |
2024-06-20 12:27:00 |
2025-08-10 10:15:00 |
|
4 |
Gaja |
2024-06-21 01:30:00 |
2025-08-11 09:45:00 |
|
5 |
John |
2024-06-21 01:35:00 |
2025-08-11 09:45:00 |
id = 1の場合、最新のデータ（ZDP_timestampが最も新しいAl）のみが保持されます。