Skip to content
/
Tableau Server Export Int...
Last updated

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

Authentication Mode: Username/Password

$ 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

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

ParameterDescription
auth_methodAuthentication method. Support values include: 'password', 'pat'
usernameYour Tableau Server Username (required when auth_method is password)
passwordYour Tableau Server Password (required when auth_method is password)
pat_namePersonal Access Token Name (required when auth_method is pat)
pat_secretPersonal Access Token Secret (required when auth_method is pat)
hostYour 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 for Tableau Server.

chunk_size_in_mbChunk file size (in MB) to be uploaded each time, default: 200, min: 100, max: 1024
read_timeout_millisThe time you wait for the response (max: 7200000). Value in milliseconds
error_when_timeoutTrue or False. If true then running job will failed in case of publish datasource timeout
timezoneTimezone 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:

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.

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.

  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.

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:

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.

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:

$ 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"
Previous page
Next page