This article explains how to write job results to your existing MySQL instance.

For sample workflows of this, view Treasure Boxes.

In this topic:

Prerequisites

  • Basic knowledge of Treasure Data, including the TD Toolbelt.

  • A MySQL instance.

  • Treasure Data must have SELECT, INSERT, DELETE, CREATE, DROP, and ALTER privileges.

Using replace mode may change the schema of the MySQL table. To avoid schema changes to the target table, select the Preserve schema? option.

Configure Results Export to your MySQL Instance

Export from Treasure Data uses queries. You create or reuse a query. In the query, you configure the data connection.

Using a New Query

Go to the TD console > Data Workbench > Queries. Then select New Query in the top right corner to create a new query.

Using an Existing Query

Go to the TD console > Data Workbench > Queries. Select the query that you plan to use to export data.

Setting up the Query Results Export

After your query setup is complete, select Export Results located at the top right of your query editor.


The Choose Integration dialog opens. You have two options when selecting a connection to use to export the results:

Use an Existing Connection

Type the connection name in the search box to filter and select your connection.


Create a New MySQL Connection

Fill in the field values to create a new connection.


Enter the required credentials for your new MySQL connection. Set the following parameters.

  • Host: The host information of the source database, such as an IP address.

  • User: Username to connect to the source database.

  • Password: Password to connect to the source database.

  • Use SSL: Check this box to connect using SSL

    • Require a valid SSL certificate?: Require that a valid SSL certificate is presented on the connection.

Set Transfer Settings

After setting up your connection, set the transfer parameters.


Database name: The name of the database you are transferring data to. (Ex. your_database_name)

Table: The table to which you want to export the data.

Output mode. Different methods to upload the data.

  • Append (default used when no mode option is provided in the URL) Query results are appended to the table. If the table does not exist, a table is created. This mode is atomic.

  • Replace: Replaces the entire content of an existing table with the resulting output of the query. If the table does not exist yet, a new table is created. The replace mode achieves atomicity (so that a consumer of the table always has consistent data) by performing the following three steps in a single transaction:

    1. Create a temporary table.

    2. Write to the temporary table.

    3. Replace the existing table with the temporary table using ALTER TABLE RENAME.

      Using replace mode may change the schema of the MySQL table. To avoid schema changes to the target table, select the Preserve schema? option.

  • Truncate: the system first truncates the existing table, then inserts the query results. If the table does not exist yet, a new table is created. This mode is atomic.

  • Update: a row is inserted unless it can cause a duplicate value in the columns specified in the “unique” parameter: in such case, an update is performed instead. The “unique” parameter is required when using the update mode. This mode is atomic.

Run Query and Check Results

Run the query with the 'Output Results' checkbox checked. If the query completes successfully, you see the results in the MySQL database and table that you specified when entering the transfer details.

Optional Use of Scheduled Jobs for Output

You can use Scheduled Jobs with Result Output, to periodically write the output result to a MySQL instance that you specify.

Optional Configure Export Results in Workflow

Within Treasure Workflow, you can specify the use of this data connector to output data.

timezone: UTC

_export:
  td:
    database: sample_datasets

+td-result-output-mysql:
  td>: queries/sample.sql
  result_connection: your_connections_name
  result_settings:
   database: database_name
   table: table_name
   mode: append



  • No labels