Skip to content

CDIを使ってアカウントデータを同期する

CDIを使ってBrazeアカウントデータを同期する方法を学習する。

前提条件

CDIを使ってアカウントデータを同期する前に、アカウントスキーマを設定する必要がある。

同期の仕組み

  • 各同期では、最終同期時刻よりUPDATED_AT後の行がインポートされる。
  • 統合データは、提供された情報に基づいてアカウントidを作成または更新する。
  • がDELETEDの場合true、アカウントは削除される。
  • 同期処理ではデータポイントをログに記録しないが、同期された全データはアカウントの総使用量に算入される。これは保存データ総量で測定されるため、変更データのみに制限する必要はない。
  • アカウントスキーマにないフィールドは削除される。新しいフィールドを同期する前にスキーマを更新せよ。
  • 同期名をマウスオーバーし、該当するアクションを選択することで、同期の更新、再開、または一時停止ができる。

アカウントデータを同期する

CDIを使って、データウェアハウスやファイルストレージを介してアカウントデータを同期できる。

データソースをデータウェアハウスに統合するには：

  1. Snowflakeにソーステーブルを作成する。例にある名前を使うか、自分でデータベース、スキーマ、テーブル名を選ぶこと。テーブルの代わりにビューやマテリアライズドビューを使うこともできる。 
    1
2
3
4
5
6
7
8
9
10
11
12
13

     CREATE DATABASE BRAZE_CLOUD_PRODUCTION;
 CREATE SCHEMA BRAZE_CLOUD_PRODUCTION.INGESTION;
 CREATE OR REPLACE TABLE BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC (
      UPDATED_AT TIMESTAMP_NTZ(9) NOT NULL DEFAULT SYSDATE(),
      --ID of the account to be created or updated
      ID VARCHAR(16777216) NOT NULL,
      --Name of the account to be created or updated
      NAME VARCHAR(16777216) NOT NULL,
      --Account fields and values that should be added or updated
      PAYLOAD VARCHAR(16777216) NOT NULL,
      --The account associated with this ID should be deleted
      DELETED BOOLEAN
 );
  2. Create a role, warehouse, and user, and grant permissions. If you already have credentials from another sync, you can reuse them—make sure they have access to the accounts table. 
    1
2
3
4
5
6
7
8
9
10
11

     CREATE ROLE BRAZE_INGESTION_ROLE;

 GRANT USAGE ON DATABASE BRAZE_CLOUD_PRODUCTION TO ROLE BRAZE_INGESTION_ROLE;
 GRANT USAGE ON SCHEMA BRAZE_CLOUD_PRODUCTION.INGESTION TO ROLE BRAZE_INGESTION_ROLE;
 GRANT SELECT ON TABLE BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC TO ROLE BRAZE_INGESTION_ROLE;

 CREATE WAREHOUSE BRAZE_INGESTION_WAREHOUSE;
 GRANT USAGE ON WAREHOUSE BRAZE_INGESTION_WAREHOUSE TO ROLE BRAZE_INGESTION_ROLE;

 CREATE USER BRAZE_INGESTION_USER;
 GRANT ROLE BRAZE_INGESTION_ROLE TO USER BRAZE_INGESTION_USER;
  3. If you use network policies, allowlist the Braze IPs so the CDI service can connect. For the list of IPs, see Cloud Data Ingestion.
  4. In the Braze dashboard, go to Data Settings > Cloud Data Ingestion and create a new sync.
  5. Enter connection details (or reuse existing ones), then add the source table.
  6. Select the Accounts sync type, then enter the integration name and schedule.
  7. Choose the sync frequency.
  8. Add the public key from the dashboard to the user you created. This requires a user with SECURITYADMIN access or higher in Snowflake.
  9. Select Test Connection to confirm the setup.
  10. When you’re finished, save the sync.
  1. Create a source table in Redshift. Use the names in the example or choose your own database, schema, and table names. You can also use a view or materialized view instead of a table. 
    1
2
3
4
5
6
7
8
9
10
11
12
13

     CREATE DATABASE BRAZE_CLOUD_PRODUCTION;
 CREATE SCHEMA BRAZE_CLOUD_PRODUCTION.INGESTION;
 CREATE TABLE BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC (
    updated_at timestamptz default sysdate not null,
    --ID of the account to be created or updated
    id varchar not null,
    --Name of the account to be created or updated
    name varchar not null,
    --Account fields and values that should be added or updated
    payload varchar(max),
    --The account associated with this ID should be deleted
    deleted boolean
 )

  2. Create a user and grant permissions. If you already have credentials from another sync, you can reuse them—make sure they have access to the accounts table.

    1
2
3

     CREATE USER braze_user PASSWORD '{password}';
 GRANT USAGE ON SCHEMA BRAZE_CLOUD_PRODUCTION.INGESTION to braze_user;
 GRANT SELECT ON TABLE ACCOUNTS_SYNC TO braze_user;
  3. If you have a firewall or network policies, allow Braze access to your Redshift instance. For the list of IPs, see Cloud Data Ingestion.
  1. (Optional) Create a new project or dataset for your source table. 
    1

     CREATE SCHEMA BRAZE-CLOUD-PRODUCTION.INGESTION;
  2. Create the source table for your CDI integration: 
    1
2
3
4
5
6
7
8

     CREATE TABLE `BRAZE-CLOUD-PRODUCTION.INGESTION.ACCOUNTS_SYNC`
 (
   updated_at TIMESTAMP DEFAULT current_timestamp,
   id STRING,
   name STRING,
   payload JSON,
   deleted BOOLEAN
 );

    Refer to the following when creating your source table:

  1. Create a user and grant permissions. If you already have credentials from another sync, you can reuse them as long as they have access to the accounts table.

    After granting permissions, generate a JSON key. See Keys create and delete for instructions. You’ll upload it in the Braze dashboard later.

  1. If you use network policies, allow Braze IPs to access your BigQuery instance. For the list of IPs, see Cloud Data Ingestion.
  1. Create a catalog or schema for your source table. 
    1

     CREATE SCHEMA BRAZE-CLOUD-PRODUCTION.INGESTION;
  2. Create the source table for your CDI integration: 
    1
2
3
4
5
6
7
8

     CREATE TABLE `BRAZE-CLOUD-PRODUCTION.INGESTION.ACCOUNTS_SYNC`
 (
   updated_at TIMESTAMP DEFAULT current_timestamp(),
   id STRING,
   name STRING,
   payload STRING, STRUCT, or MAP,
   deleted BOOLEAN
 );

    Refer to the following when creating your source table:

  1. Create a personal access token in Databricks:
    1. Select your username, then select User Settings.
    2. On the Access tokens tab, select Generate new token.
    3. Add a comment to identify the token, such as “Braze CDI”.
    4. Leave Lifetime (days) blank for no expiration, then select Generate.
    5. Copy and save the token securely for use in the Braze dashboard.
  1. If you use network policies, allow Braze IPs to access your Databricks instance. For the list of IPs, see Cloud Data Ingestion.
  1. Create one or more tables for your CDI integration with these fields: 
    1
2
3
4
5
6
7
8
9

     CREATE OR ALTER TABLE [warehouse].[schema].[CDI_table_name] 
 (
   UPDATED_AT DATETIME2(6) NOT NULL,
   PAYLOAD VARCHAR NOT NULL,
   ID VARCHAR NOT NULL,
   NAME VARCHAR NOT NULL,
   DELETED BIT
 )
 GO
  1. Create a service principal and grant permissions. If you already have credentials from another sync, you can reuse them—make sure they have access to the accounts table.
  1. If you use network policies, allow Braze IPs to access your Microsoft Fabric instance. For the list of IPs, see Cloud Data Ingestion.

To sync account data from file storage, create a source file with the following fields.

The following examples show valid JSON and CSV formats for syncing account data from file storage.

{"id":"s3-qa-0","name":"account0","payload":"{\"attribute_0\": \"GT896\", \"attribute_1\": 74, \"attribute_2\": true, \"retention\": {\"previous_purchases\": 21, \"vip\": false}, \"last_visit\": \"2023-08-08T16:03:26.600803\"}"}
{"id":"s3-qa-1","name":"account1","payload":"{\"attribute_0\": \"GT896\", \"attribute_1\": 74, \"attribute_2\": true, \"retention\": {\"previous_purchases\": 21, \"vip\": false}, \"last_visit\": \"2023-08-08T16:03:26.600803\"}","deleted":true}
{"id":"s3-qa-2","name":"account2","payload":"{\"attribute_0\": \"GT896\", \"attribute_1\": 74, \"attribute_2\": true, \"retention\": {\"previous_purchases\": 21, \"vip\": false}, \"last_visit\": \"2023-08-08T16:03:26.600803\"}","deleted":false}
{"id":"s3-qa-3","name":"account3","payload":"{\"attribute_0\": \"GT896\", \"attribute_1\": 74, \"attribute_2\": true, \"retention\": {\"previous_purchases\": 21, \"vip\": false}, \"last_visit\": \"2023-08-08T16:03:26.600803\"}"}

1
2
3

ID,NAME,PAYLOAD,DELETED
85,"ACCOUNT_1","{""region"": ""APAC"", ""employees"": 850}",TRUE 
1,"ACCOUNT_2","{""region"": ""EMEA"", ""employees"": 10000}",FALSE

1
2
3

ID,NAME,PAYLOAD
85,"ACCOUNT_1","{""region"": ""APAC"", ""employees"": 850}"
1,"ACCOUNT_2","{""region"": ""EMEA"", ""employees"": 10000}"

同期ビューを作成する

データウェアハウスに同期ビューを作成すれば、追加のクエリを書き直す必要なく、データソースが自動的に更新される。

例えば、アカウントデータテーブルがありaccount_details_1、account_idそれに、account_name 、 、そして3つの追加属性があるとすると、次のような同期ビューを作成できる：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE VIEW BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC AS 
SELECT
    CURRENT_TIMESTAMP as UPDATED_AT,
    account_id as id,
    account_name as name,
    TO_JSON(
        OBJECT_CONSTRUCT (
            'attribute_1',
            attribute_1,
            'attribute_2',
            attribute_2,
            'attribute_3',
            attribute_3)
    )as PAYLOAD FROM "account_details_1";

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC AS
SELECT
    CURRENT_TIMESTAMP as UPDATED_AT,
    account_id as id,
    account_name as name,
    JSON_SERIALIZE(
        OBJECT (
            'attribute_1',
            attribute_1,
            'attribute_2',
            attribute_2,
            'attribute_3',
            attribute_3)
    ) as PAYLOAD FROM "account_details_1";

1
2
3
4
5
6
7
8
9
10
11
12

CREATE view IF NOT EXISTS BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC AS (SELECT
    last_updated as UPDATED_AT,
    account_id as ID,
    account_name as NAME,
    TO_JSON(
      STRUCT(
      attribute_1,
      attribute_2,
      attribute_3,
      )
    ) as PAYLOAD 
  FROM `BRAZE_CLOUD_PRODUCTION.INGESTION.account_details_1`);

1
2
3
4
5
6
7
8
9
10
11
12

CREATE view IF NOT EXISTS BRAZE_CLOUD_PRODUCTION.INGESTION.ACCOUNTS_SYNC AS (SELECT
    last_updated as UPDATED_AT,
    account_id as ID,
    account_name as NAME,
    TO_JSON(
      STRUCT(
      attribute_1,
      attribute_2,
      attribute_3,
      )
    ) as PAYLOAD 
  FROM `BRAZE_CLOUD_PRODUCTION.INGESTION.account_details_1`);

1
2
3
4
5
6
7
8

CREATE VIEW [BRAZE_CLOUD_PRODUCTION].[INGESTION].[ACCOUNTS_SYNC]
AS SELECT 
    account_id as ID,
    account_name as NAME,
    CURRENT_TIMESTAMP as UPDATED_AT,
    JSON_OBJECT('attribute_1':attribute_1, 'attribute_2':attribute_2, 'attribute_3':attribute_3, 'attribute_4':attribute_4) as PAYLOAD

FROM [braze].[account_details_1] ;
Github   GitHub でこのページを編集
New Stuff!