Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: reverting to pre february state

You can send job results directly to your Salesforce Marketing Cloud (SFMC) SFTP server.

...

Currently

,

this data connector is in Beta. For more information, contact support@treasuredata.com.

Table of Contents
maxLevel3




Prerequisites

  • Basic knowledge of Treasure Data, including the TD Toolbelt

  • Basic knowledge of Salesforce Marketing Cloud

  • TD account

  • If you intend to SFTP, you can use this Data Connector for SFTP.

  • Open_SSH key format is supported.

  • Open SSH 7.8 Private Key is supported.

  • Marketing Cloud supports AES 256 encryption. Use a 64-character hexadecimal string for the key.
  • Sometimes you need to define the column mapping before writing the query. 

SMC SFTP Account

...

On the SFMC dashboard, in your account, select Administration

Image Modified


From the Account drop-down menu, select FTP Accounts. You are to establish an SFTP account. 

Image Modified


In the FTP Accounts

...

pane select Add FTP User.

Image Modified


Provide an FTP account password.

...

Image Added

...


SMC Key and

...

IV

Get your key and IV (initialization vector (IV) value as described in this article : Field-Level Encryption. A summary of those steps are:

  1. Open Key Management for your account.
  2. To create a symmetric key, provide the required information.
  3. Generate the key value for the pre-shared key field using a cryptographically secure random number generator.
  4. Your internal team can provide information for obtaining the key.
  5. Save your key and return to the Key Management page.
  6. To create an initialization vector (IV), provide the required information. Use a 32-character hexadecimal string for the IV value.
  7. Save your IV.

Use the TD Console to Create your Connection

Create a New Connection

In Treasure Data, you must create and configure the data connection before running your query. As part of the data connection, you provide authentication to access the integration.

...

start-numbering-with1
start-numbering-ath5
Open TD Console.
Navigate to Integrations Hub Catalog.
Search for and select SFTP.

...

You can select:
    • Public/Private Keypair: Open SSH 7.8 Private Key is supported for the Secret key file parameter.
      Passphrase for secret key file value is required. 
    • Password: requires a password instead of a secret key file name.
Define the source table information: Image Removed
Define the data settings: Image Removed
Type a name for your connection.
Select Done.

Define your Query

Excerpt IncludePD:Exporting Your Query Data to Your DestinationPD:Exporting Your Query Data to Your DestinationnopaneltrueAfter

Basic Usage

In TD Console, navigate to Queries and select New Query to access the query editor. You can pick the query language and write your query. Select Output Results.

Image Added

Create or Select an Existing SMC SFTP Connection

Option

Task Steps

Tip

Create a connector

  • Go to Integrations Hub > Catalog

  • Select the Salesforce Marketing Cloud via SFTP icon

  • Complete the required fields on the first pane.

Next, you complete the Configuration fields.

Sometimes you need to define the column mapping before writing the query. 

Select a connector

  • Go to TD Console

  • Navigate to the query editor.

  • Access the query that you plan to use to export data.

  • Select Output Results. The Choose Save Connection dialog opens.

  • Type the connection name in the search box to filter and select the connection that you want.

Next, you complete the Configuration fields.

You can also create a new connection from here by selecting Create New Connection. 

 Creating a New Connection

Specifying a User Name and Password

You need to choose Authentication mode as Password

Image Added

Specifying a Secret Key File

You need to choose Authentication mode as Public/Private key pair.

Image Added


After entering the credential information, you must enter a connection name to filter your connections in Choose Saved Connection dialog.

Image Added


Selecting an Existing Connection

Image Added

Complete the Configuration Fields

From here, enter your target columns to be encrypted. Key and IV are required to encrypt the columns before sending to SMC SFTP server

Image Added

 After you complete the configuration dialog, if you want to edit and update any fields in the configuration dialog, you must re-enter your key and IV to send encrypted data to SFMC.  

If you don't want to encrypt data sending to SMC SFTP, leave encryption columns, key, IV, blank.

Integration Parameters for SFTP Server Export Integration

Some MA tools will try to import data when a file with a specific name exists on the SFTP server. The temp name option is useful for such cases.

Image Removed

...

Treat the user directory as the root directory (ex. '/home/treasure-data/' as '/')

...

Files will be stored at this path

...

Upload with ".tmp" suffix first, then rename to the expected filename

SFTP result output renames the file on the remote SFTP server from “.xxx.tmp” to “.xxx” after all the data is transferred

...

Write the header line with column name as the first line

...

Delimiter character such as, for CSV, "\t" for TSV, "|" or any single-byte character

...

Defaults to empty string for CSV, '\N' for TSV

...

Example Queries

Retrieve Selected Columns from the Values Table 

...

languagesql

...

Execute the Query

You can either save the query with a name and run, or just run the query. Upon successful completion of the query, the query result is automatically imported to the specified Container destination.

After the job finishes, you can check the output file on SMC SFTP server by using the general SFTP command, as shown in the following example:

Image Added


Check the SFMC dashboard to verify a successful import.

Usage from the CLI

You can use CLI for Result Export to SMC SFTP.

Using a Secret Key File

In the following example, note the escape newline with backslash in the key.

Example,

1
2
3
4
5
6
7
8

$ td query --result '{"type":"smc_sftp","host":"xx.xx.xx.xx","port":22,"username":"xxxx","secret_key_file":"{\"content\":\"-----BEGIN RSA PRIVATE KEY-----\nABCDEFJ\nABCDEFJ\n-----END RSA PRIVATE KEY-----\"}","secret_key_passphrase":"xxxxxx", "user_directory_is_root":true,"path_prefix":"/path/to/file.csv","sequence_format":"","rename_file_after_upload":false,"header_line":true,"quote_policy":"MINIMAL","delimiter":",","null_string":"","newline":"CRLF","encryption_column_names":"name, email", "encryption_key": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", "encryption_iv":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"}' -d sample_datasets "SELECT id, name, email FROM (\
 VALUES (1,'a','a@gmail.com’),\
 (2,'b','b@gmail.com’),\
 (3,'c','c@gmail.com’)\
) tbl (id, name, email)" -T presto
 
Job xxxxxxxx is queued.
Use 'td job:show xxxxxxxx' to show the status.


Using a User Name and Password

Refer to the following example:

1
2
3
4
5
6
7
8

$ td query --result '{"type":"smc_sftp","host":"xx.xx.xx.xx","port":22,"auth_method":"Password","username":"xxxx","password":"xxxxx","user_directory_is_root":true,"path_prefix":"/path/to/file.csv","sequence_format":"","rename_file_after_upload":false,"header_line":true,"quote_policy":"MINIMAL","delimiter":",","null_string":"","newline":"CRLF","encryption_column_names":"name, email", "encryption_key": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", "encryption_iv":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"}' -d sample_datasets "SELECT id, name, email FROM (\
 VALUES (1,'a','

...

a@gmail.com’),\

...


 (2,'b','

...

b@gmail.com’),\

...


 (3,'c','

...

c@gmail.com’)\

...


)

...

tbl

...

(id,

...

name,

...

email)

...

Retrieve Everything from the Web Access Table

Code Block
languagesql
select * from www_access

Verifying Results

After successful completion of the query, the query result is automatically imported to the specified Container destination.

After the job finishes, you can check the output file on the Salesforce Marketing Cloud SFTP server by using the general SFTP command, for example:

Image Removed

...

" -T presto
 
Job xxxxxxxx is queued.
Use 'td job:show xxxxxxxx' to show the status.