Understand Workbook Impact on Your Database Using Query Tagging
Query Tagging Overview
Query tagging enables Tableau Server administrators to trace the specific Tableau activity that sends queries to their databases. Query tagging is enabled by default for Hyper, Snowflake, and Teradata data connections, and can be enabled for other activities that generate queries.
A common use case for query tagging begins when a database administrator notices long-running or failed database queries. On Tableau servers with multiple sites, workbooks, and data sources, databases queries can be sourced from many different assets. Without query tagging, it's hard to understand which Tableau asset is responsible for which query. With query tagging enabled, events in the source database's logs are tagged with the following attributes:
- Workbook, worksheet, or dashboard LUID
(Some queries are not associated with an identifiable workbook, worksheet, or dashboard and may not be tagged with these attributes. For instance, queries generated by activities of Ask Data, Accelerated workbooks, and Tableau Prep features are not currently supported.) - Currently authenticated user name
(This tag is emitted only for Tableau Server sites, and is not supported for Tableau Cloud. Also note, some queries will not have an associated user and will not be tagged with a user name. These include queries generated from backgrounder jobs.) - Currently authenticated user LUID
(As with user name, some queries do not have an associated user and will not be tagged with a user LUID.) - Site LUID
Query tagging in Tableau Cloud
- Query tagging is enabled by default and fully supported for Hyper, Snowflake, and Teradata data connections.
- Query tagging for other data connection types isn't currently supported, but is planned for a future release.
Query tagging in Tableau Server 2023.3 and later (API version 3.21 and later)
- Query tagging is enabled by default and fully supported for Hyper, Snowflake, and Teradata data connections.
- You can disable query tagging for Snowflake by using TSM to set the value of
native_api.ResourceLuidsInSnowflakeSessionQueryTagsEnabled
(Link opens in a new window) tofalse
. Set it totrue
to enable it again. - You can disable query tagging for Teradata by using TSM to set the value of
native_api.ResourceLuidsInTeradataSessionQueryBandsEnabled
(Link opens in a new window) tofalse
. Set it totrue
to enable it again. Enable query tagging globally (for all data connections) using the
tsm configuration set
command with thenative_api.UserInfoInGeneratedSQLEnabled
(Link opens in a new window)) key.Note: Although query tagging can be enabled globally for all data connections, it's only fully supported on Hyper, Snowflake, and Teradata data connections. Some types of data connections do not allow query tagging, even when it's globally enabled, and others may not work as expected. When you enable query tagging globally, test the results when you're using data connections that aren't fully supported.
- Metadata and command queries are tagged.
Query tagging in Tableau Server 2021.2 - 2023.1 (API version 3.12 - 3.19)
- Query tagging is enabled by default and fully supported for Hyper, Snowflake, and Teradata data connections.
- You can disable query tagging for Snowflake by using TSM to set the value of
native_api.ResourceLuidsInSnowflakeSessionQueryTagsEnabled
(Link opens in a new window) tofalse
. Set it totrue
to enable it again. - You can disable query tagging for Teradata by using TSM to set the value of
native_api.ResourceLuidsInTeradataSessionQueryBandsEnabled
(Link opens in a new window) tofalse
. Set it totrue
to enable it again. For other connections, enable query tagging on a connection-by-connection basis using REST API version 3.12-3.22 and the Update Data Source Connection(Link opens in a new window) or Update Workbook Connection(Link opens in a new window) methods with the
queryTaggingEnabled
attribute.Note: The
queryTaggingEnabled
attribute of the Update Data Source Connection(Link opens in a new window) and Update Workbook Connection(Link opens in a new window) methods is deprecated starting in Tableau Server 2024.2 (API 3.23).-
The Update Data Source Connection(Link opens in a new window) and Update Workbook Connection(Link opens in a new window) methods do not support modifying connections to data sources that require OAuth or multi-factor authentication
(for example, BigQuery), so it is not possible to enable query tagging for these connections using the REST API. Use the
tsm set native_api.UserInfoInGeneratedSQLEnabled
command in Tableau Server 2023.3 or later to enable query tagging for all data connections instead.
Find the Tableau Origin of Queries to Your Database
Once an issue with SQL queries to your database is identified, enable query tagging to capture metadata for troubleshooting. At a high level the process for investigating an issue might look like:
- IDENTIFY THE ISSUE IN YOUR DATABASE: Your database administrator notices a recurring query event that regularly shows an unexpectedly long run time. They call you, as Tableau administrator, to understand where the query originates and discuss how to make things more efficient.
ENABLE QUERY TAGGING FOR YOUR SERVER: (This step is not required for data connections using Hyper, Snowflake, or Teradata, because query tagging is enabled by default for those data connections.) To understand how the problem query event is tied to your workbook(s), you first need to understand what connections are making queries to the database.
In Tableau Server 2023.3 (API version 3.21 and later): Use TSM to enable query tagging for all content on a server by setting
native_api.UserInfoInGeneratedSQLEnabled
(Link opens in a new window) to true.In Tableau Server 2021.2 - 2023.1 (API version 3.12 - 3.19): Use the Tableau REST API Update Data Source Connection(Link opens in a new window) and Update Workbook Connection(Link opens in a new window) methods to enable query tagging for connections. The Python Script Example section below shows one possible way to do this.
LOOK FOR THE TABLEAU QUERY ORIGINATOR IN YOUR DATABASE MONITORING: Find an instance of the problematic query in your database log, history, or analytics. You will see that the related query has a comment string appended beginning with
/* "tableau-query-origins": "..." */
. That string includes the Tableau asset LUID and other attributes listed above from the originating workbook, dashboard, or view.Note, for Snowflake and Teradata the query originator data is not in the SQL text. For Snowflake connections, the query origins information is in the Session Query Tag(Link opens in a new window). For Teradata connections, the query origins information is in the Session Query Band(Link opens in a new window).
- EXAMINE THE ORIGINATING TABLEAU RESOURCE: Use the Tableau asset LUID to locate and troubleshoot the source of the problematic queries.
Python Script Example (for API 3.12 through 3.21)
Note: The queryTaggingEnabled
attribute of the
Update Data Source Connection(Link opens in a new window) and
Update Workbook Connection(Link opens in a new window)
methods is deprecated after Tableau Server 2023.3 (API 3.21). Starting in Tableau Server 2023.3 (API version 3.21 and later),
enable query tagging globally instead, using the tsm configuration set
command with the
native_api.UserInfoInGeneratedSQLEnabled
(Link opens in a new window)
key.
The Python script in this section illustrates how to use the REST API to find and enable query tagging for the connections that use your database’s server address. To find each of these connections, using your Tableau server address, auth token, and site LUID, the script:
Iterates through each data source in the site using query data sources(Link opens in a new window). You could also choose to iterate through each workbook using query workbooks(Link opens in a new window), instead.
Where workbooks on a site share data sources, choose using data source connections to find which resource originates a query. To include embedded data sources, use workbook connections.
- For each data source, iterates through each of its connections using query data source connections(Link opens in a new window). If you queried workbooks, then use query workbook connections(Link opens in a new window).
- For each connection that uses your database as its serverAddress, enable query tagging for those connections by
setting
queryTaggingEnabled=true
in a request to update data source connection(Link opens in a new window) or update workbook connection(Link opens in a new window).
import requests # Contains methods used to make HTTP requests
import xml.etree.ElementTree as ET # Contains methods used to build and parse XML
import sys
import math
import getpass
VERSION = 3.9 # up to 3.22
xmlns = {'t': 'http://tableau.com/api'}
class ApiCallError(Exception):
pass
class UserDefinedFieldError(Exception):
pass
def _encode_for_display(text):
"""
Returns an ASCII-encoded version of the text.
"""
return text.encode('ascii', errors="backslashreplace").decode('utf-8')
def _check_status(server_response, success_code):
if server_response.status_code != success_code:
parsed_response = ET.fromstring(server_response.text)
error_element = parsed_response.find('t:error', namespaces=xmlns)
summary_element = parsed_response.find('.//t:summary', namespaces=xmlns)
detail_element = parsed_response.find('.//t:detail', namespaces=xmlns)
code = error_element.get('code', 'unknown') if error_element is not None else 'unknown code'
summary = summary_element.text if summary_element is not None else 'unknown summary'
detail = detail_element.text if detail_element is not None else 'unknown detail'
error_message = '{0}: {1} - {2}'.format(code, summary, detail)
raise ApiCallError(error_message)
return
def get_datasource_connections(server, auth_token, site_id, datasource_id):
url = server + "/api/{0}/sites/{1}/datasources/{2}/connections?fields=id,serverAddress".format(VERSION, site_id, datasource_id)
server_response = requests.get(url, headers={'x-tableau-auth': auth_token})
_check_status(server_response, 200)
xml_response = ET.fromstring(_encode_for_display(server_response.text))
return xml_response.findall('.//t:connection', namespaces=xmlns)
def get_datasource_and_connection_ids(server, auth_token, site_id):
connected_server_address = "your_database_.test.tsi.lan"
print("get ids")
url = server + "/api/{0}/sites/{1}/datasources?fields=id".format(VERSION, site_id)
server_response = requests.get(url, headers={'x-tableau-auth': auth_token})
_check_status(server_response, 200)
xml_response = ET.fromstring(_encode_for_display(server_response.text))
id_pairs = []
datasources = xml_response.findall('.//t:datasource', namespaces=xmlns)
for datasource in datasources:
connections = get_datasource_connections(server, auth_token, site_id, datasource.get('id'))
for connection in connections:
if connection.get('serverAddress') == connected_server_address:
id_pair = ( datasource.get('id'), connection.get('id') )
id_pairs.append(id_pair)
print(id_pair)
return id_pairs
def update_datasource_connection(server, auth_token, site_id, datasource_id, connection_id):
xml_request = ET.Element('tsRequest')
connection_element = ET.SubElement(xml_request, 'connection', queryTaggingEnabled=true)
xml_request = ET.tostring(xml_request)
url = server + "/api/{0}/sites/{1}/datasources/{2}/connections/{3}".format(VERSION, site_id, datasource_id, connection_id)
server_response = requests.put(url, data=xml_request, headers={ 'x-tableau-auth': auth_token })
_check_status(server_response, 200)
def main():
server = "http://your_tableau_server"
auth_token = "54RO8RefTAuU5LlfJ0dhyQ|siwMGk8Ad5woJK7Jsor2Z8Mj7CYmGTFO"
site_id = "b27009a5-7b4f-44d1-ba9e-4ad9ae22b97d"
datasource_and_connection_ids = get_datasource_and_connection_ids(server, auth_token, site_id)
for datasource_and_connection_id in datasource_and_connection_ids:
update_datasource_connection(datasource_and_connection_id[0], datasource_and_connection_id[1])