# Tableau Cloud Export Integration

Using [Tableau Cloud](http://www.tableausoftware.com/products/online) (formerly Tableau Online) with Treasure Data allows you to interactively explore huge amounts of data and share your data discoveries across your business organizations.

![](/assets/image2020-12-2_17-13-28.9f21e33486793fec12b0c06af624d4103809ab33fbcb2fcde46fdd58885e5763.2e27f6a2.png)

For sample workflows of exporting to Tableau Cloud, view [Treasure Boxes](https://github.com/treasure-data/treasure-boxes/tree/master/td/tableau).

## Prerequisites

- Basic knowledge of Treasure Data.
- A license and its installation of Tableau Cloud.
- The connector requires the Datasource publish permission; the creator or admin user role would be matched.
- Personal Access Token name and secret when you want to authenticate with Personal Access Token.


## Limitations

- The maximum result record is 250,000,000 records.
If it is exceeded, the log displays the message: Extract file records limit exceeded: 250000000.
- The lowest Timestamp value is 1000-01-01 00:00:00.
If it is exceeded, the log displays the message: invalid date value.
- Appending a large dataset to an existing Data Source may result in a timeout.
You will see the below message in the job logs:

```
2019-04-10 19:20:41.460 +0000 [WARN] (0001:transaction): !!! Data Source Publish is timed out. This is a known issue of Tableau when you append a large extract file to existing Data Source.
2019-04-10 19:20:41.460 +0000 [WARN] (0001:transaction): !!! Check Tableau Console for final result of the Publish.
```
- The job succeeds, but you must check the final result on Tableau Console (you can verify by using the **Number of records** in the Data Source).
- The project name of Tableau must be unique. If multiple projects have the same name, the export will fail: **Project Name must be unique, but found n projects with name: '<project_name>'**.
- Token expiry when the user chooses Personal Access Token for authentication method. Personal access tokens will expire if they are not used after 15 consecutive days. If they are used more frequently than every 15 days, an access token will expire after 1 year. After a year, you must create a new token. Expired personal access tokens will not display on the My Account Settings page.
- When you authenticate with a PAT, you can only run one process using that PAT at a time (unlike when you authenticate using a username and password). If you run a second authentication using the same PAT while the first job is still running causes the authentication/authorization for the first job to be dropped and results in errors.
It results in the following message in the job logs:

```
2021-12-26 07:55:47.926 +0000 [WARN] (0001:transaction): Retrying 2/7 after 2 seconds. Message: javax.ws.rs.WebApplicationException: HTTP 401 Unauthorized
....
2021-12-26 07:57:53.523 +0000 [ERROR] (main): org.embulk.util.retryhelper.RetryGiveupException: java.lang.NullPointerException
```


## Static IP Address of Treasure Data Integration

If your security policy requires IP whitelisting, you must add Treasure Data's IP addresses to your allowlist to ensure a successful connection.

Please find the complete list of static IP addresses, organized by region, at the following link:
[https://api-docs.treasuredata.com/en/overview/ip-addresses-integrations-result-workers/](https://api-docs.treasuredata.com/en/overview/ip-addresses-integrations-result-workers/)

## Use the TD Console to Create a Connection

You must create and configure the data connection in Treasure Data before running your query. As part of the data connection, you provide authentication to access the integration.

There are two different processes to create a connection:

- Use your Tableau username and password
- Use a personal access token


### Create a New Authentication with Tableau Username and Password

1. Open TD Console.
2. Navigate to Integrations Hub > Catalog.
3. Search for and select Tableau.
![](/assets/image2021-11-15_10-18-21.f88bafbe3a46ab1771301f2d75e600113dadda99ca8e755256c90d3f58027b01.2e27f6a2.png)
4. Select **Create Authentication**.
![](/assets/image2021-11-15_10-20-51.39aa71003a00903d3950b1f646ea6c8b985ac428181c577a15e01c2aaa2f0dfc.2e27f6a2.png)
5. Type the following credentials to authenticate.
| Parameter | Description |
|  --- | --- |
| Host | Tableau Cloud host where your site exists  (e.g.  [10az.online.tableau.com](http://10az.online.tableau.com)  ) |
| Username | Your Tableau Cloud username |
| Password | Your Tableau Cloud password |
6. Select Continue.
7. Type a name for your connection.
8. Select **Done**.


### Create a New Authentication with Personal Access Token

1. Log in to your Tableau Cloud server and go to **My Account Settings**.
![](/assets/image2021-10-30_13-57-9.ff406ad211fcf30dece13d515988ff964b25c49503692be9939907d5858fdfcf.2e27f6a2.png)
2. Enter a Personal Access Token name from the Settings tab and then select **Create new** token.
![](/assets/image2021-10-30_14-0-40.5b2d1c36be94847069ae1bbf4ebdda449fcd06f35d3f68f857589545252eaf7f.2e27f6a2.png)
3. Copy and store the Personal Access Token name/ Personal Access Token secret.


### Log in to TD Console

1. Open TD Console.
2. Navigate to **Integrations Hub** > **Catalog**.
3. Search for and select **Tableau**.
![](/assets/image2021-11-15_10-18-21.f88bafbe3a46ab1771301f2d75e600113dadda99ca8e755256c90d3f58027b01.2e27f6a2.png)
4. Select **Create Authentication**.
![](/assets/image2021-11-15_10-36-0.3dbcdc3a78dbd6e87f23e273db19c5339dbe27e178e90a26c19ff734a6e86e17.2e27f6a2.png)
5. Type the following credentials to authenticate.
| Parameter | Description |
|  --- | --- |
| Host | Tableau Cloud host where your site exists  (e.g. [10az.online.tableau.com](http://10az.online.tableau.com)) |
| Auth method | Personal Access Token |
| Personal Access Token Name | Your Tableau Cloud Personal Access Token name |
| Personal Access Token Secret | Your Tableau Cloud Personal Access Token secret |
6. Select **Continue**.
7. Type a name for your connection.Select **Done**.


### Define your Query

1. Navigate to **Data Workbench** > **Queries**.
2. Select **New Query**.
3. Run the query to validate the result set.


## One-Time Query CLI Usage

#### JSON-Style Config (New and Recommended)

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

Password Authentication


```bash
td query \
  -d mydb \
  -r '{
    "type": "tableau",
    "host": "company.online.tableau.com",
    "auth_method": "password",
    "username": "my_user",
    "password": "passw0rd",
    "ssl": true,
    "ssl_verify": false,
    "server_version": "online",
    "datasource": "my_ds",
    "site": "MarketingTeam",
    "project": "",
    "mode": "replace",
    "chunk_size_in_mb": 50,
    "timezone": "America/Los_Angeles"
  }' \
  'SELECT * FROM access'
```

Personal Access Token Authentication


```bash
td query \
  -d mydb \
  -r '{
    "type": "tableau",
    "host": "company.online.tableau.com",
    "auth_method": "pat",
    "pat_name": "pat_name",
    "pat_secret": "pat_secret",
    "ssl": true,
    "ssl_verify": false,
    "server_version": "online",
    "datasource": "my_ds",
    "site": "MarketingTeam",
    "project": "",
    "mode": "replace",
    "chunk_size_in_mb": 50,
    "timezone": "America/Los_Angeles"
  }' \
  'SELECT * FROM access'
```

| Parameter | Description |
|  --- | --- |
| auth_method | Authentication method support([password, pat], default: password) |
| username | Your Tableau Cloud Username (required if auth_method is password) |
| password | Your Tableau Cloud Password (required if auth_method is password) |
| pat_name | Your Personal Access Token name ( required if auth_method is pat) |
| pat_secret | Your Personal Access Token secret (required if auth_method is pat) |
| host | Tableau Cloud Host on which your site exists (e.g. `10ay.online.tableau.com`) |
| datasource | Target Tableau DataSource name |
| site | The URL of the site to sign in to  To determine the value to use for the `site` attribute, sign in to Tableau Cloud, and examine the value that appears after `/site/` in the URL. For example, in the following URL, the `site` value is `MarketingTeam`:  `https://online.tableau.com/#/site/MarketingTeam/workbooks`    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 |
| Mode | `Replace-` replace Data Source each time  `Append-` append to existing Data Source. (Default is Append) |
| chunk_size_in_mb | Chunk file size (in MB) to be uploaded each time, default: 100, min: 50, max: 512 |
| read_timeout_millis | The time you wait for the response (max: 7200000). Value in milliseconds |
| timezone | Timezone to convert from Timestamp data type to Tableau DateTime data type, default: UTC |


## URL-Style (Not Recommended and cannot work with Personal Access Token authentication)

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


```bash
$ td query -d mydb -r 'tableau://username:password@host/datasource?mode=replace&site' 'SELECT * FROM access'
```

## Migrating Existing Output Configurations to Tableau Hyper

Tableau has updated its data engine with a technology called Hyper. To take advantage of this, you must update your existing Treasure Data output configurations. New Treasure Data configurations automatically use the latest version.

### Upgrade Your Existing Configurations

1. Go to Queries and select your scheduled query.
2. In Query Editor and click **Export Results** target.
3. Choose your saved Tableau connection.
4. Input the **Site ID** value; it’s **required** for Tableau Cloud.
5. Select 'hyper' for **Data Source Type**.
6. Click **Done** to save your configuration.


You do not need to change the timezone configuration when migrating from the legacy Tableau to the current Tableau. You can leave the default value (UTC). See also the [timezone](/int/tableau-cloud-export-integration) in this article.

### One-Time Query using TD Console

Go to the query editor on the TD Console, and type in the query. The following example query uses the access log example data set and calculates the distribution of the HTTP methods per day.

#### HIVE


```sql
-- HiveQL
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


```sql
-- Presto
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
```

Treasure Data is casting the Datetime column from String type to TIMESTAMP type in Tableau.

Tableau doesn’t support fractional seconds in a timestamp. Remove fractional seconds (for example, by using subtr() function) before casting to the TIMESTAMP type in the query.

![](/assets/image-20191016-210119.d37229df361e6ae9f83b59c526553896524e37b48f95e23d0574bf94e9f555c1.2e27f6a2.png)

### Choose Saved Connection

A dialog `Choose Integration` displays. Select an existing Tableau Cloud connection. If you do not have a Saved Integration already set up, follow the next step on how to create a new connection within the Sources Catalog.

![](/assets/image-20191016-210136.885a90f1eb4a53ebaba682e06f7ee18784c3eb92e57b38ce3a2ea7e6968b2cd5.2e27f6a2.png)

### Additional Configuration

After you create a Tableau connection or select an existing one, you see the following Configuration popup.

![](/assets/image-20191016-210205.3a1266b427218f641970bc24ccb4facf09d44fea47e49e558ba7f4c89ac5497a.2e27f6a2.png)

| **Parameters** | **Description** | **Default values** |
|  --- | --- | --- |
| Datasource Name | The name of the destination Data Source on Tableau Cloud |  |
| Site ID | The URL of the site to sign in to. To determine the value to use for the `site` attribute, sign in to Tableau Cloud, and examine the value that appears after `/site/` in the URL.  For example, in the following URL, the `site` value is `MarketingTeam`:  `https://online.tableau.com/#/site/MarketingTeam/workbooks`  This is **required** for Tableau Cloud. |  |
| Project Name | Go to your Tableau Cloud to get a list of projects. | Default |
| Mode | `replace` to replace Data Source each time, `append` to append to existing Data Source. | `append` |
| Chunk File Size In MB | The extract file is split into chunks before uploading. This option defines the file size of each chunk (min: 100, max: 1024). | 200 |
| HTTP read timeout in milliseconds | The time you wait for the response (max: 7200000). Value in milliseconds. | 7200000 |
| Timezone | Timezone ID to use when converting from Timestamp (timezone-independent) to Tableau DateTime (timezone-dependent). | UTC |


Append Mode ignores new columns if it is exporting data inserted an existing data source because of the specification of Tableau. [https://onlinehelp.tableau.com/current/pro/desktop/en-us/extracting_addfromfile.html](https://onlinehelp.tableau.com/current/pro/desktop/en-us/extracting_addfromfile.html)

1. After completing all the fields, **Submit** the query. The system will execute the query, create the Tableau Data Extract file (`.tde` or `.hyper`), and upload the extract file to Tableau Cloud.
2. Go to your Tableau Cloud, and click **Data Sources** at the top left bar. You can view the list of data sources, including your TDE file.![](/assets/image2021-11-15_11-9-49.ba68b45abc430e9d56cad8857d2934145a21de3d9f1cfd2f2df4e1d7657ed646.2e27f6a2.png)
3. Select New Workbook to create the charts and dashboard from the browser. Drag and drop the dimensions and measures from the left navigation to the top right navigation to create graphs. Select **Save** to store the result.


![](/assets/image2021-11-15_11-26-27.0b1d5c18af53267f7cbb59f47a977dcf7203c92e97941db85bf739b97bf357df.2e27f6a2.png)

## Migration Authentication from Password to Personal Access Token

When you enable MFA on the Tableau Cloud console, you can no longer use a username/password to authenticate. Tableau requires a Personal Access Token authentication. You must migrate all connections that used the Password Authentication method to the Personal Access Token Authentication method.

### Change Connection to Personal Access Token Authentication Method

1. Log in to TD console and go to Authentication.
![](/assets/image2021-10-30_14-56-55.84f5d6c7f59fb7b721ed0e44ba26068c9678899842b97ff2d3a31553f9dca4f3.2e27f6a2.png)
2. Change **Auth method** from Password to Personal Access Token and enter the token name/secret.
![](/assets/image2021-10-30_15-0-47.6b767f8ec9d81920161239ddbd138ec0c41c20f70158e1c3b911ff5fa5bc829d.2e27f6a2.png)
3. You can use this connection to export data to Tableau with your Personal Access Token name/secret.


## Update a Save Query to Personal Access Token Authentication Method

1. Change the connector in the saved query from Password Authentication to Personal Access Token Authentication.
2. Navigate to **Data Workbench** > **Queries**.
3. Select your saved query and select **Clone**. Enter a new name for your query.
![](/assets/image2021-10-30_15-11-58.0a369a89d5a208149688071aed5e6938ff5cc5b1fa06beff44bbaa718e2500d6.2e27f6a2.png)
4. From the query editor, select Export Result and choose the connection that was edited from Step 1; complete the export configuration again.
![](/assets/image2021-10-30_15-15-55.e420aded5b218151fd090504c65e074bd75947682be54528201a3ef75c0b594c.2e27f6a2.png)
5. Go to query on TD console and delete your old query.
![](/assets/image2021-10-30_15-19-22.4af54171e9721f91616db2486cd4919e6a9e4767bd08b3f1f575e5c7b3ae04e5.2e27f6a2.png)


## Options

Result output to Tableau 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 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@host/?ssl=true
```

### ssl_verify Option

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


```
tableau://username:password@host/?ssl=true&ssl_verify=true
```

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

### Timezone

1. To convert from timestamp value, which is timezone independent, for example, 1548979200, to Tableau DateTime, which includes day, hour, minute, etc., the connector needs to know the target timezone.
2. If your query contains a TIMESTAMP column or cast a datetime column to TIMESTAMP, the value is exported to the Tableau server as DateTime. There is a conversion, and you must provide the required target timezone.
3. Treasure Data stores datetime value using UTC timezone. In most cases, leave the timezone config as default (UTC) to preserve the value from Treasure Data unless you want to convert the value to another timezone.


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

### From the CLI


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

### As part of TD Workflow


```
host: "company.online.tableau.com"ssl: truessl_verify: trueusername: "my_user"password: "passw0rd"
datasource: "my_ds"site: "my_company"project: "Default" server_version: "online"
timezone: "America/Los_Angeles"
```

## (Optional) Configure Export Results in Workflow

Within Treasure Workflow, you can specify the use of a data connector to export data.

Learn more at [Using Workflows to Export Data with the TD Toolbelt](https://docs.treasuredata.com/articles/project-product-documentation/about-exporting-data).

### Example Workflow for Password Authentication Method


```yaml
_export:
  td:
    database: tableau_db
 
+tableau_export_task:
  td>: export_tableau.sql
  database: ${td.database}
  result_connection: new_created_tableau_auth
  result_settings:
    datasource: "datasource"
    site: "site"
    project: "project"
    targetType: "hyper"
    chunkSizeInMb: 100
    timezone: "UTC"
```

### Example Workflow for Personal Access Token Authentication Method


```yaml
_export:
  td:
    database: tableau_db
 
+tableau_export_task:
  td>: export_tableau.sql
  database: ${td.database}
  result_connection: new_created_tableau_auth
  result_settings:
    datasource: "datasource"
    site: "site"
    project: "project"
    targetType: "hyper"
    chunkSizeInMb: 100
    timezone: "UTC"
```