Hightouch で Snowflake のデータを SendGrid に連携してみた

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

chmod 400 rsa_key.p8
chmod 400 rsa_key.pub

cat rsa_key.pub

-- Edit the following variables
set ht_username='HIGHTOUCH_USER';
set ht_default_warehouse='HIGHTOUCH_WH';
set ht_database='HIGHTOUCH_DB';
set ht_default_role='HIGHTOUCH_ROLE';
set ht_comment='Used for Hightouch integrations';

-- Set role for grants
USE ROLE ACCOUNTADMIN;

-- Create a role for Hightouch
CREATE ROLE IF NOT EXISTS identifier($ht_default_role)
COMMENT = $ht_comment;

-- Only if you want to create a new warehouse for Hightouch to use
CREATE WAREHOUSE IF NOT EXISTS identifier($ht_default_warehouse);

-- Create Hightouch's user
CREATE USER IF NOT EXISTS identifier($ht_username)
TYPE=service
rsa_public_key='XXXXX'
DEFAULT_WAREHOUSE=$ht_default_warehouse
DEFAULT_NAMESPACE=$ht_default_namespace
DEFAULT_ROLE=$ht_default_role
COMMENT=$ht_comment;

-- Grant permissions to the role
GRANT ROLE identifier($ht_default_role) TO ROLE SYSADMIN;
GRANT USAGE ON WAREHOUSE identifier($ht_default_warehouse) TO ROLE identifier($ht_default_role);
GRANT ROLE identifier($ht_default_role) TO USER identifier($ht_username);

-- Only if you want to create a new database for Hightouch to use
CREATE DATABASE IF NOT EXISTS identifier($ht_database);

GRANT USAGE ON DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANT USAGE ON ALL SCHEMAS IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANT SELECT ON ALL TABLES IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANT SELECT ON FUTURE TABLES IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANT SELECT ON ALL VIEWS IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);
GRANT SELECT ON FUTURE VIEWS IN DATABASE identifier($ht_database) TO ROLE identifier($ht_default_role);

-- Grant write access for the Lightning Sync Engine (faster syncs) & Sync Logs - Optional
USE identifier($ht_database);
CREATE SCHEMA IF NOT EXISTS hightouch_audit;
CREATE SCHEMA IF NOT EXISTS hightouch_planner;
GRANT OWNERSHIP ON SCHEMA hightouch_audit TO ROLE identifier($ht_default_role);
GRANT OWNERSHIP ON SCHEMA hightouch_planner TO ROLE identifier($ht_default_role);

-- sourceの設定で指定したデータベースにテーブルを作成する
USE SCHEMA hightouch_db.public;

CREATE OR REPLACE TABLE hightouch_db.public.sendgrid_customers (
    "email" VARCHAR(255),
    "first_name" VARCHAR(255),
    "last_name" VARCHAR(255),
    "address_line_1" VARCHAR(255),
    "address_line_2" VARCHAR(255),
    "city" VARCHAR(255),
    "state_province_region" VARCHAR(255),
    "postal_code" VARCHAR(20),
    "country" VARCHAR(255),
    "phone_number_id" VARCHAR(20),
    "external_id" VARCHAR(255),
    "anonymous_id" VARCHAR(255)
);

INSERT INTO hightouch_db.public.sendgrid_customers (
    "email",
    "first_name",
    "last_name",
    "address_line_1",
    "address_line_2",
    "city",
    "state_province_region",
    "postal_code",
    "country",
    "phone_number_id",
    "external_id",
    "anonymous_id"
) VALUES
('<メールアドレス>', 'Test', 'Hightouch', '123 Neverland Lane', 'Suite 42', 'Denver', 'CO', '80202', 'USA', '18778894546', '112', '223');

{
    "type": "contacts",
    "externalIdMapping": {
        "from": "email",
        "to": "email"
    },
    "mappings": [
        {
            "type": "standard",
            "from": "email",
            "to": "_rf2_T"
        },
        {
            "from": "first_name",
            "to": "_rf0_T",
            "type": "standard"
        },
        {
            "type": "standard",
            "from": "last_name",
            "to": "_rf1_T"
        }
    ],
    "customMappings": [
        {
            "type": "standard"
        }
    ]
}

{
    "type": "contacts",
    "externalIdMapping": {
        "from": "email",
        "to": "email"
    },
    "mappings": [
        {
            "type": "standard",
            "from": "email",
+            "to": "email"
-            "to": "_rf2_T"
        },
        {
            "from": "first_name",
+            "to": "first_name",
-            "to": "_rf0_T",
            "type": "standard"
        },
        {
            "type": "standard",
            "from": "last_name",
+            "to": "last_name"
-            "to": "_rf1_T"
        }
    ],
    "customMappings": [
        {
            "type": "standard"
        }
    ]
}