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—just 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—just 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—just 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.

1
2
3
4

{"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!