Using Tableau Server with Arm Treasure Data allows users to interactively explore huge amounts of data, and also share data information across your organizations.

 For sample workflows of exporting to Tableau Server, view Treasure Boxes.

Prerequisites

• Basic knowledge of Treasure Data
• A license and its installation of Tableau Server

Usage

CLI: One Time Query

JSON-style config (new and recommended)

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

$ td query -d mydb -r '{"type":"tableau","host":"my.tableauserver.com","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'

where:

• username: Your Tableau Server Username
• password: Your Tableau Server Password
• host: Your Tableau Server Hostname
• site: 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. Note that you always sign in to a specific site, even if you don’t specify a site when you sign in
  • Refer here for more details
• datasource: target Tableau DataSource name
• chunk_size_in_mb: Chunk file size (in MB) to be uploaded each time, default: 200, min: 100, max: 1024
• timezone: timezone to convert from Timestamp data type to Tableau DateTime data type, default: UTC

URL-style config (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'

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.

To 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 multi-tenant Tableau Server (leave it empty if you sign in to Default site).
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 timezone in this article.

Console: One Time Query

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 HTTP method per day.

HIVE:

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

# 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

Choose saved connection

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

Create a new authentication

Go to Integrations Hub > Catalog and search and select Tableau. The following opens:

 

• Username: Your Tableau Server Username
• Password: Your Tableau Server Password
• Host: Your Tableau Server Hostname (If you want to set specified port number, set HOST:PORT)

Additional configuration

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

 

Parameters	Description	Default values
Datasource Name	The name of Data Source on Tableau Server
Site ID	If you don’t have specific Site, set EMPTY String for Tableau Server
Project Name	Go to your Tableau Server 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	Extract File is split into chunks before uploading. This option defines file size of each chunk (min: 100, max: 1024)	200
Timezone	Timezone ID to use when converting from Timestamp (timezone-independent) to Tableau DateTime (timezone-dependent)	UTC

 

After completing all the fields, Submit the query. The system executes the query, creates the Tableau Data Extract file (.tde or .hyper), and uploads Extract file to Tableau Server.

Go to your Tableau Server, and click Data Sources at the top left bar. You can view the list of data sources, including your TDE file.

 

Click New Workbook to create the charts and dashboard from the browser. Drag and drop the dimensions and measures from the left nav, to top right nav to create graphs. Then click Save to store the result.

Console: Scheduled Query

In the previous section, you created the charts using one job. The next action is to periodically refresh the dataset by Scheduled Jobs feature. 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 # of records within 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:

# HiveQL
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:

# 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

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

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

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.

project

On Tableau Server, there should be at least one 'Default' project. You can't delete or rename this project. If you omit Project Name option, 'Default' project is used.

On some rare cases, especially non-English Tableau Server, the 'Default' project name is localized and jobs fail if you don't provide a Project Name (because it couldn't find a project with 'Default' as the name).

timezone

To convert from timestamp value, which is timezone independent, for example, 1548979200, to Tableau DateTime, which includes day, hour, minute, etc. 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 Tableau server as DateTime. Meaning, there is a conversion and you need to provide target timezone as necessary.

Treasure Data stores the datetime value using UTC timezone. In most cases, leave timezone config 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 as follows:

From the CLI:

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

As part of TD Workflow:

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"

Limitations

• The maximum result record is 250,000,000 records. If it 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 exceeded, the log displays the message: invalid date value.
• Known issues:
  • .hyper extracts published to Tableau Server 10.5 via REST API will have .tde file extension on Tableau Console. These extracts were still in .hyper file format and functioned the same way as other .hyper extracts. The issue is fixed in 2018.1. More details: Tableau Server 2018.1 Release Notes (search for Issue ID 754677)
  • Data Source belonging to a nested Project cannot be appended by using a REST API. A Resource Not Found error is returned. Replace mode does not have this issue. A fix is planned for Tableau Server 2018.1.4. If you see such error, it's recommended to upgrade to latest version of Tableau Server.