Writing Job Results into Tableau Server

Using Tableau Server with Treasure Data allows users to interactively explore huge amounts of data, and also share the knowledge across multiple people.



Table of Contents

Prerequisites

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

Usage

CLI: One Time Query

Please add the Tableau result output destination with the -r / —result option for the td query command:

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

Console: One Time Query

Please visit the query editor on the web console, and type in the query. The example query below 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
Untitled-3
We're casting Datetime column to TIMESTAMP type from String type, for convenience in Tableau.

Next, please select Tableau Server from Result Export section. Then the dialog like below will pop up. Please fill with your Tableau Server’s username, password, and hostname. Then, please click Save button.



  • Username: Your Tableau Server Username
  • Password: Your Tableau Server Password
  • Host: Your Tableau Server Hostname (If you want to set specified port number, please set HOST:PORT)
Untitled-3
Currently, you MUST setup https (SSL). Please check here about how to set up SSL on your Tableau Server.

Finally, please fill in name of Datasource (TDE filename), Site ID, Project, and Mode. Please visit these URLs to get a list of projects and sites. Let’s use replace mode for now.



  • Datasource: The name of TDE file on Tableau Online
  • Site ID: Please visit the URL https://<TABLEAU_SERVER_URL>/sites to get a list of Site IDs
  • Project: Please visit the URL https://<TABLEAU_SERVER_URL>/t/<YOUR_SITE_NAME>/projects to get a list of Projects
  • Mode: replace to replace TDE each time, append to append to existing TDE
Untitled-3
Append Mode ignores new column if exporting data inserted an exsiting data source because of the specification of the Tableau. https://onlinehelp.tableau.com/current/pro/desktop/en-us/extracting_addfromfile.html

After filling in all the fields, please Submit the query. The system will execute the query, create the TDE (Tableau Data Extract) file, and finally upload TDE to Tableau Server.

Please visit your Tableau Server, and click Data Sources section at the top left bar. This will show the list of data sources, and your TDE file should exist there.



By clicking New Workbook, you can create the charts and dashboard from the browser. Please drag and drop the dimentions & measures from the left nav, to top right nav to create the graphs. Then please click Save to store the result.

Console: Scheduled Query

In the previous section, you have learned how to create the charts by 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. One thing it’s useful is to use TD_SCHEDULED_TIME() UDF, with append mode.

The query below will calculate the # of records within last 24 hours, from the time where 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.


Last modified: Dec 24 2016 00:46:56 UTC

If this article is incorrect or outdated, or omits critical information, please let us know. For all other issues, please see our support channels.