# Tableau Server Export Integration CLI

## One Time Query CLI Usage

### JSON-Style Configuration

This method is recommended.

1. Add the Tableau result output destination by using the `-r` / `—result` option for the `td query`command:


Authentication Mode: Username/Password


```bash
$ td query -d mydb -r '{"type":"tableau","host":"my.tableauserver.com", "auth_method":"password", "username":"my_user", "password":"passw0rd", "ssl":true, "ssl_verify":false, "server_version":"10.0", "datasource":"my_ds", "site":"","project":"", "mode":"replace", "chunk_size_in_mb":50, "timezone":"PST"}' 'SELECT * FROM access'
```

Authentication Mode: PAT


```bash
$  td query -d mydb -r '{"type":"tableau","host":"my.tableauserver.com", "auth_method":"pat", "pat_name":"pat_name", "pat_secret":"pat_secret", "ssl":true, "ssl_verify":false, "server_version":"10.0", "datasource":"my_ds", "site":"","project":"", "mode":"replace", "chunk_size_in_mb":50, "timezone":"PST"}' 'SELECT * FROM access'
```

where:

| Parameter | Description |
|  --- | --- |
| auth_method | Authentication method. Support values include: 'password', 'pat' |
| username | Your Tableau Server Username (required when auth_method is password) |
| password | Your Tableau Server Password (required when auth_method is password) |
| pat_name | Personal Access Token Name (required when auth_method is pat) |
| pat_secret | Personal Access Token Secret (required when auth_method is pat) |
| host | Your Tableau Server Hostname |
| datasource
 | The URL of the site to sign in to (optional)
To determine the value to use for the `site` attribute, sign in to Tableau Server and examine the value that appears after `/site/` in the URL. For example, in the following URL, the `site` value is `MarketingTeam`:
`https://MyServer/#/site/MarketingTeam/projects`
If the `site` attribute is an empty string, you are signed in to the default site. You are always signed in to a specific site, even if you don't specify a site when you sign in.
Read the [REST API reference](https://help.tableau.com/current/api/rest_api/en-us/REST/rest_api_ref.htm#Sign_In?TocPath=API%2520Reference%7C_____89) for Tableau Server.
 |
| chunk_size_in_mb | Chunk file size (in MB) to be uploaded each time, default: 200, min: 100, max: 1024 |
| read_timeout_millis | The time you wait for the response (max: 7200000). Value in milliseconds |
| error_when_timeout | True or False. If true then running job will failed in case of publish datasource timeout |
| timezone | Timezone to convert from Timestamp data type to Tableau DateTime data type, default: `UTC` |


### URL-Style Configuration

This method is not recommended.

Change the Tableau result output destination from JSON format to URL format as shown in the following example:


```
$ td query -d mydb -r 'tableau://username:password@my.tableauserver.com/?mode=replace' 'SELECT * FROM access'
$ td query -d mydb -r 'tableau://username:password@my.tableauserver.com:8443/?mode=replace&ssl=true' 'SELECT * FROM access'
```

## One Time Query TD Console Usage

1. Open TD Console.
2. Navigate to the query editor
3. Type the query.


The following example query uses the access log example data set, and calculates the distribution of the HTTP method per day. For convenience, it casts the Datetime column to TIMESTAMP type from String type in Tableau.

HIVE:


```sql
SELECT
  CAST(TD_TIME_FORMAT(time, "yyyy-MM-dd 00:00:00") AS TIMESTAMP) AS "dates",
  method AS `Method`,
  COUNT(1) AS `Count`
FROM
  www_access
GROUP BY
  TD_TIME_FORMAT(time, "yyyy-MM-dd 00:00:00"),
  method
```

PRESTO:

For convenience, in Presto queries, we recommend using TD_TIME_FORMAT instead of TD_TIME_STRING.


```sql
SELECT
  CAST(TD_TIME_FORMAT(time, 'yyyy-MM-dd 00:00:00') AS TIMESTAMP) AS "dates",
  method AS `Method`,
  COUNT(1) AS `Count`
FROM
  www_access
GROUP BY
  TD_TIME_FORMAT(time, 'yyyy-MM-dd 00:00:00'),
  method
```

1. Open your Tableau Server.
2. Select **Data Sources** at the top left bar.
You can view the list of data sources, including your TDE file.
![](/assets/image-20191016-211351.4667974c17c9a7ad2b16ec3fa12ae3453e5576c4dfab1e36e504503afa74d386.94b6a600.png)
3. Select **New Workbook** to create the charts and dashboard from the browser.
4. Drag and drop the dimensions and measures from the left nav, to the top right nav to create graphs.
5. Select **Save** to store the result.


![](/assets/image-20191016-211405.b7e75000b994c7c8d8934d01ccfa61923fde5012ba00c349524743454b4bc781.94b6a600.png)

## Schedule Query Refreshes using TD Console

You can use by using the scheduled jobs feature to refresh the dataset. By using this feature, you can periodically execute the query on Treasure Data, and update TDE file on Tableau Server. The schedules can be specified from the query editor. It’s useful is to use TD_SCHEDULED_TIME() UDF, with `append` mode.

The following query calculates the number of records within the last 24 hours, from the time when the query gets executed. By continuously running this scheduled query, you can avoid processing the entire data set every day.

HIVE:


```sql
SELECT 
  CAST(
    TD_TIME_FORMAT(time,
      "yyyy-MM-dd 00:00:00") AS TIMESTAMP
  ) AS `Datetime`,
  method AS `Method`,
  COUNT(1) AS `Count`
FROM
  www_access
WHERE
  TD_TIME_RANGE(time,
    TD_TIME_ADD(TD_SCHEDULED_TIME(),
      '-1d'),
    TD_SCHEDULED_TIME())
GROUP BY
  TD_TIME_FORMAT(time,
    "yyyy-MM-dd 00:00:00"),
  method
```

PRESTO:

For convenience, in Presto queries use TD_TIME_FORMAT instead of TD_TIME_STRING.


```sql
SELECT 
  CAST(
    TD_TIME_FORMAT(time,
      'yyyy-MM-dd 00:00:00') AS TIMESTAMP
  ) AS "dates",
  method AS `Method`,
  COUNT(1) AS `Count`
FROM
  www_access
GROUP BY
  TD_TIME_FORMAT(time,
    'yyyy-MM-dd 00:00:00'),
  method
```

## Options

Result output to Tableau Server supports various options. The options can be specified as URL parameters on the CLI or with the REST APIs or the Console where supported. The options are normally compatible with each other and can be combined. Where applicable, the default behavior is indicated.

### ssl Option

The **ssl** option determines whether to use SSL or not for connecting to the Tableau server. When ‘true’, SSL is used. `ssl=true` is the default when this option is not specified.


```
tableau://username:password@my.tableauserver.com/?ssl=true
tableau://username:password@my.tableauserver.com:443/?ssl=true
```

### ssl_verify Option

The **ssl_verify** option determines whether to require certifcate verification for the SSL communication or not. When ‘true’, the certificate verification is required. `ssl_verify=true` is the default when this option is not specified.


```
tableau://username:password@my.tableauserver.com/?ssl=true&ssl_verify=true
```

Disabling certificate verification is particularly useful when the Tableau server’s SSL certificate is **self-signed**.

### Timezone

The timestamp value is timezone independent. For example, `1548979200`. The Tableau DateTime includes day, hour, minute, etc. To convert from timestamp value to Tableau DateTime the connector needs to know the target timezone. If your query contains a `TIMESTAMP` column, or you cast a DateTime column to `TIMESTAMP`, the value is exported to the Tableau server as DateTime. Meaning, there is a conversion and you need to provide a target timezone.

Treasure Data stores the DateTime value using the UTC timezone. In most cases, leave `timezone` configured as default (UTC), to preserve the value from Treasure Data, unless you particularly want to convert the value to another timezone.

An example of configuring a timezone other than the default UTC is:

From the CLI:


```bash
$ td query "..." -r '{ "type": "tableau", ..., "timezone": "America/Los_Angeles" }'
```

As part of TD Workflow:


```yaml
host: "tableau.company.com"
ssl: true
ssl_verify: false
username: "my_user"
password: "passw0rd"
datasource: "my_ds"
site: "MarketingTeam"
project: "Default"
server_version: "2018.1"
timezone: "America/Los_Angeles"
```