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 in Tableau Server 2021.2 - 2023.1 (API version 3.12 - 3.19)

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:

  1. 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.
  2. 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.

  3. 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).

  4. 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:

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])