The following changes to the Google BigQuery connector were introduced with Tableau Desktop 8.1.
BQL Now Supported
Tableau Desktop users can now use BQL, Google’s SQL-like language for BigQuery, instead of writing SQL in the Google BigQuery Connection dialog box. Prior to Tableau 8.1, users were required to write in SQL, which had overlaps with BQL but did not support all BigQuery functionality. For example, with SQL it is not possible to call functions such as FLATTEN, JOIN EACH, or GROUP EACH BY. Thus, it was not possible to deal with nested data and make joins and group bys at scale. For example, JOIN requires that the right-side table contains less than 8 MB of compressed data, whereas JOIN EACH allows join queries for tables of any size.
It is possible that workbooks created in earlier versions of Tableau Desktop that include pass-through functions (RAWSQL) or Custom SQL will need to be modified before they can run in Tableau Desktop 8.1.
New Functions Supported
Tableau Desktop 8.1 supports the following functions for Google BigQuery connections. You can use these functions in the Tableau Desktop function editor to create calculated fields, etc.
REGEXP_MATCH('str', 'reg_exp')
Returns true if str matches the regular expression.
REGEXP_EXTRACT('str', 'reg_exp')
Returns the portion of str that matches the capturing group within the regular expression.
REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')
Returns a string where any substring of orig_str that matches reg_exp is replaced with replace_str.
Example
REGEXP_REPLACE ('Hello', 'lo', 'p') returns Help.HOST('url_str')
Given a URL, returns the host name as a string.
Example
HOST('http://www.google.com:80/index.html') returns www.google.com:80.DOMAIN('url_str')
Given a URL, returns the domain as a string.
Example
HOST('http://www.google.com:80/index.html') returns google.com.TLD('url_str')
Given a URL, returns the top level domain plus any country domain in the URL.
Examples
TLD('http://www.google.com:80/index.html') returns .com.TLD('http://www.google.co.uk:80/index.html') returns .co.uk.GROUP_CONCAT('str')
Concatenates multiple strings into a single comma-delimited string, rather like SUM() for strings. Use this with a grouping statement and a field name for str to concatenate a list of all string values in a group into a single string.
TIMESTAMP_TO_USEC()
Converts a TIMESTAMP data type to a UNIX timestamp in microseconds.
USEC_TO_TIMESTAMP()
Converts a UNIX timestamp to a TIMESTAMP data type in microseconds.