Skip to content
/
Salesforce Marketing Clou...
Last updated

Salesforce Marketing Cloud V2 Export Integration Using CLI

You can use Treasure Data CLI to run the query and write output result to Salesforce Marketing Cloud.

Run a Query on Treasure Data CLI

Example using Asynchronous APIs with Legacy package

td query \
  -d database_name \
  -r '{
    "type": "salesforce_marketing_cloud",
    "client_id": "Th1s1s4n3x4mpl3Cl13nt1ds",
    "client_secret": "Th1s1s4F4k3dCl13ntS3cr3t",
    "auth_type": "v2",
    "de_name": "data_extension_name",
    "async": true,
    "records_per_batch": 32000
  }' \
  "SELECT primary_key, mytext, myinteger, mydecimal, myphone, myemail, mydate, mylocale, myboolean = 1 AS myboolean FROM my_1_million" \
  -T presto
  • database_name: Treasure Data database that stores the query source tables.
  • client_id, client_secret: Salesforce Marketing Cloud API credentials.
  • de_name: Target Data Extension name in Salesforce Marketing Cloud.
  • async: Set to true to use asynchronous API export.
  • records_per_batch: Maximum number of rows per batch request.
  • -T presto: Runs the query with the Presto engine (switch to Hive or others if needed).
  • Final SQL statement: Adjust the SELECT clause to match the columns you want to export.

Example using Asynchronous APIs with Enhanced package

td query \
  -d database_name \
  -r '{
    "type": "salesforce_marketing_cloud_v2",
    "client_id": "Th1s1s4n3x4mpl3Cl13nt1ds",
    "client_secret": "Th1s1s4F4k3dCl13ntS3cr3t",
    "auth_type": "v2",
    "auth_uri": "https://th1s1sf4k3d1nst4nc3h0st.auth.marketingcloudapis.com/",
    "de_name": "data_extension_name",
    "async": true,
    "records_per_batch": 32000
  }' \
  "SELECT primary_key, mytext, myinteger, mydecimal, myphone, myemail, mydate, mylocale, myboolean = 1 AS myboolean FROM my_1_million" \
  -T presto
  • type: Use salesforce_marketing_cloud_v2 for the enhanced package.
  • auth_uri: Base authentication endpoint for your SFMC instance (find it in your package settings).
  • Remaining parameters mirror the legacy example and should match your target Data Extension and query output.

Example using Synchronous APIs with Legacy package

td query \
  -d database_name \
  -r '{
    "type": "salesforce_marketing_cloud",
    "client_id": "Th1s1s4n3x4mpl3Cl13nt1ds",
    "client_secret": "Th1s1s4F4k3dCl13ntS3cr3t",
    "auth_type": "v1",
    "de_name": "data_extension_name",
    "async": false,
    "continue_on_failure": true
  }' \
  "SELECT primary_key, mytext, myinteger, mydecimal, myphone, myemail, mydate, mylocale, myboolean = 1 AS myboolean FROM my_1_million" \
  -T presto
  • auth_type: Set to v1 to use the legacy SOAP authentication flow.
  • async: Set to false to switch to the synchronous API.
  • continue_on_failure: Allows the job to skip failing rows and continue processing.

Example using Synchronous APIs with Enhanced package

td query \
  -d database_name \
  -r '{
    "type": "salesforce_marketing_cloud_v2",
    "client_id": "Th1s1s4n3x4mpl3Cl13nt1ds",
    "client_secret": "Th1s1s4F4k3dCl13ntS3cr3t",
    "auth_type": "v2",
    "auth_uri": "https://th1s1sf4k3d1nst4nc3h0st.auth.marketingcloudapis.com/",
    "de_name": "data_extension_name",
    "async": false,
    "continue_on_failure": true
  }' \
  "SELECT primary_key, mytext, myinteger, mydecimal, myphone, myemail, mydate, mylocale, myboolean = 1 AS myboolean FROM my_1_million" \
  -T presto
  • auth_type: Keep v2 to authenticate with the enhanced REST API.
  • async: Set to false to run the export synchronously.
  • continue_on_failure: Lets the export proceed even when individual rows fail.

Example using Synchronous APIs with Enhanced package and create new Sendable data extension if not existed

td query \
  -d database_name \
  -r '{
    "type": "salesforce_marketing_cloud_v2",
    "client_id": "Th1s1s4n3x4mpl3Cl13nt1ds",
    "client_secret": "Th1s1s4F4k3dCl13ntS3cr3t",
    "auth_type": "v2",
    "auth_uri": "https://th1s1sf4k3d1nst4nc3h0st.auth.marketingcloudapis.com/",
    "de_name": "data_extension_name",
    "create_new_de": true,
    "primary_column": "primary_key",
    "is_sendable": true,
    "sendable_column": "mytext",
    "sendable_rule": "Subscriber Key",
    "async": false,
    "continue_on_failure": true
  }' \
  "SELECT primary_key, mytext, myinteger, mydecimal, myphone, myemail, mydate, mylocale, myboolean = 1 AS myboolean FROM my_1_million" \
  -T presto
  • create_new_de: Creates the Data Extension when it does not already exist.
  • primary_column: Sets the primary key field for the new Data Extension.
  • is_sendable: Creates the Data Extension as sendable.
  • sendable_column: Column used as the sendable attribute.
  • sendable_rule: Subscriber attribute that pairs with the sendable column.

Example using Export data extension to a specific folder

td query \
-d database_name \
-r '{
      "type": "salesforce_marketing_cloud_v2",
      "client_id": "Th1s1s4n3x4mpl3Cl13nt1ds",
      "client_secret": "Th1s1s4F4k3dCl13ntS3cr3t",
      "auth_type": "v2",
      "auth_uri": "https://th1s1sf4k3d1nst4nc3h0st.auth.marketingcloudapis.com/",
      "folder_path": "/Data Extensions/FolderPath2/FolderPath3/FolderPath3",
      "create_folder_if_not_exists": true,
      "de_name": "data_extension_name",
      "create_new_de": true,
      "primary_column": "primary_key",
      "is_sendable": true,
      "sendable_column": "mytext",
      "sendable_rule": "Subscriber Key",
      "async": false,
      "continue_on_failure": true
  }' \
"SELECT primary_key, mytext, myinteger, mydecimal, myphone, myemail, mydate, mylocale, myboolean = 1 AS myboolean FROM my_1_million" \
-T presto
  • folder_path: Destination folder path inside Salesforce Marketing Cloud.
  • create_folder_if_not_exists: Creates the folder hierarchy on demand if missing.

Map Between Query Result and Data Extension Column Name

You can use the as keyword in the query to map between query result and Data Extension column.

Example 1:

Given the following Data Extension that contains subscriber information:

name: de_1 Columns: primary_key, cust_email, cust_first_name, cust_last_name, cust_phone_number

Given the following table that contains user account information:

name: account_table

Columns: id, login_id, first_name, last_name, contact_number

You can use the following query with an as keyword to map between the table and Data Extension.

SELECT
  id AS primary_key,
  login_id AS cust_email,
  first_name AS cust_first_name,
  last_name AS cust_last_name,
  contact_number AS cust_phone_number
FROM account_table;

Example 2:

If you want to upload data that has phone numbers (10 digit number), you can also use this query below to cast phone number as STRING in order to avoid error by phone numbers that start with '0'

SELECT
  primary,
  text,
  CAST(number AS VARCHAR) AS number
FROM table_name
WHERE type = 'SUCCESS';
Previous page
Next page