# 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.