# 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**


```bash
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**


```bash
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**


```bash
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**


```bash
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


```bash
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**


```bash
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.


```sql
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'


```sql
SELECT
  primary,
  text,
  CAST(number AS VARCHAR) AS number
FROM table_name
WHERE type = 'SUCCESS';
```