Skip to content
/
MySQL Tables Export Integ...
Last updated

MySQL Tables Export Integration CLI

You can use the CLI to issue queries and output results. The following instructions show you how to format the query output results using the CLI.

TD Query Command Usage

To output the result of a single query to a MySQL server add the --result option to the td query command. After the job is finished, the results are written into your database:

$ td query --result 'mysql://user:password@host/database/table' \
  -w -d testdb \
  "SELECT code, COUNT(1) FROM www_access GROUP BY code"

Scheduled Jobs

To create a scheduled query whose output is systematically written to MySQL add the --result option when creating the schedule through td sched:create command. Every time the job runs, the results are written into mytbl.

$ td result:create mydb mysql://user:password@host/database
$ td sched:create hourly_count_example "0 * * * *" -d testdb "select count(*) from www_access" --result mydb:mytbl

Result Output URL Format

The result output target is represented by URL with the following format:

mysql://username:password@hostname:port/database/table

where:

  • mysql is identified for result output to MySQL;
  • username and password are the credentials to the MySQL server;
  • hostname is the host name of theMySQL server;
  • port is the port number through which theMySQL server is accessible. “:” is optional and assumed to be 3306 by default;
  • database is the name of the destination database;
  • table is the name of a table within the above mentioned database. It may not exist at the moment the query output is execute, in which case a table with the specified name will be created for the user.

Options

Writing Job Results to the MySQL server supports the following options.

Modes to Modify Data (Append/Replace/Truncate/Update)

You can add or delete data in four ways.

mysql://user:password@host/database/table               # append
mysql://user:password@host/database/table?mode=append   # append
mysql://user:password@host/database/table?mode=replace  # replace
mysql://user:password@host/database/table?mode=truncate # truncate
mysql://user:password@host/database/table?mode=update   # update

All four modes are atomic.

In the “host” section, you can specify the port as well. Here is an example:

mysql://superadmin:superstrongpassword@data.science.com:27013/transaction/user_info

APPEND

This is the default mode. The query results are appended to a table. If the table does not exist, a new table will be created.

REPLACE

If the table already exists, the rows of the existing table are replaced with the query results. If the table does not exist, a new table will be created.

We achieve 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 'Preserve schema?' option.

TRUNCATE

The system first truncates the existing table, then inserts the query results. If the table does not exist, a new table will be created.

Unlike REPLACE, TRUNCATE retains the indexes of your table.

UPDATE

This mode inserts new data and update existing data by performing the following steps.

  1. Create a temporary table.
  2. Write to the temporary table.
  3. Update data from temporary table to the destination table.
  4. Insert the rest of the data from temporary table to the destination table.

To specify a unique key, you can set unique parameter, which is comma-separated list of keys. If the values for all keys match all existing record, that record will be replaced. Here is an example:

mysql://superadmin:superstrongpassword@data.science.com:27013/transaction/user_infomode=update&unique=column1%2Ccolumn2  

On CLI, Comma in the paramter must be URL encoded. ("," should be "%2C")

Additional Options

Writing Job Results to the MySQL server supports following additional options that can be specified as optional URL parameters.

ssl option

ssl option determines whether to use SSL or not for connecting to the MySQL server.

ssl=true

Use SSL from Treasure Data to the MySQL server connection. The MySQL server must be configured to accept SSL connection.

mysql://user:password@host/database/table?ssl=true

ssl=false (default)

Do not use SSL from Treasure Data to the MySQL server.

mysql://user:password@host/database/table?ssl=false

Character Encoding

Treasure Data always uses UTF-8 as character encoding. You also need to setup your database to use UTF-8 by default. If you came across any encoding related issues, check the following configurations.

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

INT UNSIGNED type of MySQL

Job Results into MySQL doesn't support INT UNSIGNED type of MySQL. Its value will be converted to an unexpected value.

Previous page
Next page