# Snowflake Export Integration [Learn more about Snowflake Import Integration](/int/snowflake-import-integration). You can write job results directly to Snowflake. For example, you might integrate data from multiple sources into Treasure Data, run queries to organize the data, and then use a job that applies the connector to export the data to Snowflake. ## Prerequisites - Basic knowledge of Treasure Data, including the [TD Toolbelt](https://toolbelt.treasuredata.com/). - A Snowflake account. ## Use the TD Console to create your connection You can use the Treasure Data Console to configure your connection. ### Create a new connection Go to [Treasure Data Connections](https://console.treasuredata.com/app/connections/data-sources) and search and select Snowflake. The following dialog opens. ![](/assets/image2021-10-1_12-4-8.82731cdb927bd12214d58c6b7eac884db7804e2c65401c46fc378fdf795127eb.04f949c4.png) Select Authentication Method: - **Basic**: Provide the required credentials: **User**, **Password**, and **Account** to authenticate Treasure Data to Snowflake. - **User**: Snowflake login username. - **Password**: Snowflake login password. - **Key Pair**: Provide the **Private Key** and its **Passphrase** if it is the encrypted private key - **Private Key**: Your generated private key. See [configuring-key-pair-authentication](https://docs.snowflake.com/en/user-guide/key-pair-auth.md#configuring-key-pair-authentication) - **Passphrase**: The Private Key passphrase, or leave it empty if the Private Key is unencrypted. - **User**: Snowflake login username. - **Account**: Snowflake provided the account name. See [how to find your account name in Snowflake](https://docs.snowflake.net/manuals/user-guide/connecting.md#your-snowflake-account-name). - **OPTIONS**: This option is not supported for this connector. Provide the required credentials: **User**, **Password**, and Snowflake **Account** to authenticate Treasure Data to Snowflake. Then select `Continue` and give your connection a name: ![](/assets/image-20191017-215710.540da5baa379afc236cda04938fc69c6bef48b94eaab7837e514f28927759849.04f949c4.png) Select **Done**. ### Specify Output in Your Snowflake Connector Create or reuse a query that configures the data connection. Sometimes you need to define the column mapping in the query. ### Configure the Connection by Specifying the Parameters Go to the [Treasure Data console](https://console.treasuredata.com/users/sign_in). Go to the [Query Editor](https://console.treasuredata.com/app/queries/editor). Access the query that you plan to use to export data. Sometimes you need to define the column mapping in the query. For Example: ```sql WITH sample_data ( c_id, c_double, c_long, c_string, c_boolean, c_timestamp, c_json ) AS ( VALUES (1, 100.0000, 10, 'T4', TRUE, '2018-01-01', '{ "name":"John"}'), (2, 100.0000, 99, 'P@#4', FALSE, '2018-01-01', '{ "name":"John"}'), (3, 100.1234, 22, 'C!%^&*', FALSE, '2018-01-01', '{ "name":"John"}') ) SELECT c_id, c_double, c_long, c_string, c_boolean, c_timestamp, c_json FROM sample_data; ``` Select **Output Results** located at top of your query editor. The Choose Saved Connection dialog opens. ### Configure the connection by specifying the parameters Type the connection name in the search box to filter and select your connection. After you select your *Snowflake* connection, the *Configuration* or Export Results dialog pane appears: ![](/assets/image-20191017-215727.8fed9c8e289bbade80b3bd2b05be5aa6cef5e79056387fed9f55afd41985156b.04f949c4.png) Specify parameters. The parameters are as follows: - **Role Name (optional):** The default access control role to use for exporting, it should be an existing role that has already been assigned to the user. - **Warehouse (required):** The virtual warehouse to use, it should be an existing warehouse for which the specified role has privileges. - **Database (required):** The database to use, it should be an existing database for which the specified role has privileges. - **Schema (required):** The schema to use for the specified database, it should be an existing schema for which the specified role has privileges. - **Destination table (required):** The table which results will be exported to. If it does not exist, the new one will be created. - **Add missing columns:** If columns from the target table are less than columns in TD results, an exception will be thrown if the option is unselected. If it is selected, new columns which are missing will be added to the target table. - **Load mode (required):** There are 3 mode to fulfill results to Snowflake table, **Append**, **Overwrite**, **Merge**. If **Merge** is selected, Merge Field(s) will be shown. - **Append:** TD results will be appended to the target table - **Overwrite:** The target table will be erased, then TD results will be appended from the beginning of target table - **Merge:** The target table will be merged with the TD results based on conditions from **Merge Fields**. - **Merge Fields (required if in merge mode):** The fields will be used to compare between target table and TD results, if those fields values are equal, a record in target table will be overridden otherwise new record will be inserted into the target table. The format for merge fields is a comma-separated text field. - **Data type mapping:** It will be explained in the following [section](#data-type-mapping). ### Data type mapping Here is the table for 1-1 mapping from the type in TD results to the target table in case target table does not exist or new columns are added: | **TD results** | **Snowflake** | | --- | --- | | string | STRING | | double | FLOAT | | long | BIGINT | | timestamp | TIMESTAMP | | boolean | BOOLEAN | You might want to set the different type as the default. Data Type Mapping is used to explicitly set a specific type (for example, VARCHAR) to a specific column. Data Type Mapping applies a type only to columns in your target table. If you use Data Mapping, the following is true: - If the target table does not exist, then the export job will create a new target table. - If the target table does not have enough columns as compared to TD results, you can specify to add more columns. The syntax for data type mapping parameter is: **col_name_1: VARCHAR; col_name2:** **BIGINT**, You must provide: column name and Snowflake data type. JSON is not fully supported when you export data using a Treasure Data Presto or Hive query. Therefore when a target table does not exist and is created, a JSON data type value is saved as VARCHAR by default in the newly created target table. If you want to save a JSON value as a semi-structured type instead, then you must update the type mapping. Use the Data Mapping syntax to specify a semi-structured data type. For example, in the query: ```sql SELECT c_id, c_double, c_long, c_string, c_boolean, c_timestamp, c_json FROM ( VALUES (1, 100, 10, 'T4', true, '2018-01-01','{ "name":"John"}'), (2, 100, 99, 'P@#4', false, '2018-01-01', '{ "name":"John"}'), (3, 100.1234, 22, 'C!%^&*', false, '2018-01-01','{ "name":"John"}') ) tbl1 (c_id, c_double, c_long, c_string, c_boolean, c_timestamp, c_json) ``` The **c_json** column has JSON content, but the type of that **c_json** column in Snowflake would be VARCHAR by default. If you want a VARIANT type in Snowflake, update the **Data type mapping** field to **c_json: VARIANT** to explicitly set **c_json** to **VARIANT** type. ### (Optional) Schedule Query Export Jobs You can use Scheduled Jobs with Result Export to periodically write the output result to a target destination that you specify. Treasure Data's scheduler feature supports periodic query execution to achieve high availability. When two specifications provide conflicting schedule specifications, the specification requesting to execute more often is followed while the other schedule specification is ignored. For example, if the cron schedule is `'0 0 1 * 1'`, then the 'day of month' specification and 'day of week' are discordant because the former specification requires it to run every first day of each month at midnight (00:00), while the latter specification requires it to run every Monday at midnight (00:00). The latter specification is followed. #### Scheduling your Job Using TD Console 1. Navigate to **Data Workbench > Queries** 2. Create a new query or select an existing query. 3. Next to **Schedule**, select None. ![](/assets/image2021-1-15_17-28-51.f1b242f6ecc7666a0097fdf37edd1682786ec11ef80eff68c66f091bc405c371.0f87d8d4.png) 4. In the drop-down, select one of the following schedule options: ![](/assets/image2021-1-15_17-29-47.45289a1c99256f125f4d887e501e204ed61f02223fde0927af5f425a89ace0c0.0f87d8d4.png) | Drop-down Value | Description | | --- | --- | | Custom cron... | Review [Custom cron... details](#custom-cron-details). | | @daily (midnight) | Run once a day at midnight (00:00 am) in the specified time zone. | | @hourly (:00) | Run every hour at 00 minutes. | | None | No schedule. | #### Custom cron... Details ![](/assets/image2021-1-15_17-30-23.0f94a8aa5f75ea03e3fec0c25b0640cd59ee48d1804a83701e5f2372deae466c.0f87d8d4.png) | **Cron Value** | **Description** | | --- | --- | | `0 * * * *` | Run once an hour. | | `0 0 * * *` | Run once a day at midnight. | | `0 0 1 * *` | Run once a month at midnight on the morning of the first day of the month. | | "" | Create a job that has no scheduled run time. | ``` * * * * * - - - - - | | | | | | | | | +----- day of week (0 - 6) (Sunday=0) | | | +---------- month (1 - 12) | | +--------------- day of month (1 - 31) | +-------------------- hour (0 - 23) +------------------------- min (0 - 59) ``` The following named entries can be used: - Day of Week: sun, mon, tue, wed, thu, fri, sat. - Month: jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec. A single space is required between each field. The values for each field can be composed of: | Field Value | Example | Example Description | | --- | --- | --- | | A single value, within the limits displayed above for each field. | | | | A wildcard `'*'` to indicate no restriction based on the field. | `'0 0 1 * *'` | Configures the schedule to run at midnight (00:00) on the first day of each month. | | A range `'2-5'`, indicating the range of accepted values for the field. | `'0 0 1-10 * *'` | Configures the schedule to run at midnight (00:00) on the first 10 days of each month. | | A list of comma-separated values `'2,3,4,5'`, indicating the list of accepted values for the field. | `0 0 1,11,21 * *'` | Configures the schedule to run at midnight (00:00) every 1st, 11th, and 21st day of each month. | | A periodicity indicator `'*/5'` to express how often based on the field's valid range of values a schedule is allowed to run. | `'30 */2 1 * *'` | Configures the schedule to run on the 1st of every month, every 2 hours starting at 00:30. `'0 0 */5 * *'` configures the schedule to run at midnight (00:00) every 5 days starting on the 5th of each month. | | A comma-separated list of any of the above except the `'*'` wildcard is also supported `'2,*/5,8-10'`. | `'0 0 5,*/10,25 * *'` | Configures the schedule to run at midnight (00:00) every 5th, 10th, 20th, and 25th day of each month. | 1. (Optional) You can delay the start time of a query by enabling the Delay execution. ### Execute the Query Save the query with a name and run, or just run the query. Upon successful completion of the query, the query result is automatically exported to the specified destination. Scheduled jobs that continuously fail due to configuration errors may be disabled on the system side after several notifications. (Optional) You can delay the start time of a query by enabling the Delay execution. ## Activate a Segment in Audience Studio You can also send segment data to the target platform by creating an activation in the Audience Studio. 1. Navigate to **Audience Studio**. 2. Select a parent segment. 3. Open the target segment, right-mouse click, and then select **Create Activation.** 4. In the **Details** panel, enter an Activation name and configure the activation according to the previous section on Configuration Parameters. 5. Customize the activation output in the **Output Mapping** panel. ![](/assets/ouput.b2c7f1d909c4f98ed10f5300df858a4b19f71a3b0834df952f5fb24018a5ea78.8ebdf569.png) - Attribute Columns - Select **Export All Columns** to export all columns without making any changes. - Select **+ Add Columns** to add specific columns for the export. The Output Column Name pre-populates with the same Source column name. You can update the Output Column Name. Continue to select **+ Add Columns**to add new columns for your activation output. - String Builder - **+ Add string** to create strings for export. Select from the following values: - String: Choose any value; use text to create a custom value. - Timestamp: The date and time of the export. - Segment Id: The segment ID number. - Segment Name: The segment name. - Audience Id: The parent segment number. 1. Set a **Schedule**. ![](/assets/snippet-output-connector-on-audience-studio-2024-08-28.a99525173709da1eb537f839019fa7876ffae95045154c8f2941b030022f792c.8ebdf569.png) - Select the values to define your schedule and optionally include email notifications. 1. Select **Create**. If you need to create an activation for a batch journey, review [Creating a Batch Journey Activation](/products/customer-data-platform/journey-orchestration/batch/creating-a-batch-journey-activation). ## Optional: Configure Export Results in Workflow Within Treasure Workflow, you can specify the use of this data connector to output data. ```yaml timezone: UTC _export: td: database: sample_datasets +td-result-into-snowflake: td>: queries/sample.sql result_connection: your_connection_name result_settings: role_name: role warehouse: wh schema: public database: OUTPUT table: TARGET_TABLE mode: insert is_add_missing_columns: false ``` Learn about [using data connectors in the workflow to export data](https://docs.treasuredata.com/smart/project-product-documentation/exporting-data-with-parameters). ## Use the CLI to create your connection ### Install ‘td’ command Install the [TD Toolbelt](https://toolbelt.treasuredata.com/). ### For On-demand Jobs Add the Snowflake result output destination by using the `-r` / `--result` option for the `td query` command: ```bash $ td query -d mydb -w 'SELECT id, name FROM source_table' --type presto -r '{"type":"snowflake", "warehouse":"wh", "user":"owner", "account_name":"owner", "password":"********", "role_name":"role", "schema":"public", "database":"OUTPUT", "table":"TARGET_TABLE", "mode":"insert", "is_add_missing_columns":"false"}' ``` Some parameters, such as **warehouse**, **database,** are self-explanatory and are the same parameters that are used in the in TD Console (described in the "Configure the connection by specifying the parameters" section). However, some parameters are either different in the key or value: - **mode (required):** The value for mode is a raw value of **Load Mode** parameter: - **insert:** TD results will be appended to target table - **truncate_insert:** The target table will be erased, then TD results will be appended from the beginning of target table. - **merge:** The target table will be merged with the TD results based on conditions from - **merge_keys:** Your fields to compare in merge mode, same as **Merge Field(s)**. - **column_options:** The data type mapping. ### For Scheduled Jobs Add the Snowflake result output destination by using the `-r` / `--result` option for the `td sched:create` command: ```bash $ td sched:create every_6_mins "*/6 * * * *" -d mydb -w 'SELECT id, name FROM source_table' --type presto -r '{"type":"snowflake", "warehouse":"wh", "user":"owner", "account_name":"owner", "password":"********", "role_name":"role", "schema":"public", "database":"OUTPUT", "table":"TARGET_TABLE", "mode":"insert", "is_add_missing_columns":"false"}' ``` ### Data type mapping See [Data type Mapping](#data-type-mapping) ### Optional: Use of Scheduled Jobs for Output You can use [Scheduled Jobs](https://docs.treasuredata.com/smart/project-product-documentation/scheduling-jobs-using-td-console) with Result Output, to periodically write the output result to a target destination that you specify. ## Appendix ### Support for SSL Connection to Snowflake server is made via their [official JDBC driver](https://docs.snowflake.net/manuals/user-guide/jdbc-download.md). The JDBC driver forces the usage of SSL as default and mandatory ( i.e. connection with SSL = false will be rejected).