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}' "select primary_key, mytext, myinteger, mydecimal, myphone, myemail, mydate, mylocale, myboolean=1 as myboolean from my_1_million" -T presto

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}' "select primary_key, mytext, myinteger, mydecimal, myphone, myemail, mydate, mylocale, myboolean=1 as myboolean from my_1_million" -T presto

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

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

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

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'

  • No labels