Understand Workbook Impact on Your Database Using Query Tagging
Query Tagging Overview
Query tagging enables administrators to trace the specific Tableau Cloud or Server activity that originates queries to their remote SQL databases.
Common reasons for using query tagging start when a database administrator notices long-running or failed queries. On servers or sites with multiple workbooks and data sources, databases can often be accessed by different datasources in the same workbook, or different workbooks in a site. Prior to query tags, it was hard to understand which Tableau resource was responsible for which query. With query tagging enabled, events in the source database’s logs will be tagged with LUIDs of the workbook, dashboard, or worksheet (view) that caused the query to be generated.
Queries generated by activities of Ask Data, Accelerated Workbooks, extract refresh, and Tableau Prep features are not currently supported.
Important: If you are connecting to data sources that require OAuth or multi-factor authentication (for example, BigQuery), you get an error with query tagging enabled. We do not support queries that require these types of authentication.
How to Use Query Tagging to Find the Tableau Origin of Queries to Your Database
Once an issue with SQL queries to your database is identified, use REST API requests to enable query tagging. 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 which 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 THE DATA CONNECTIONS THAT ACCESS YOUR DATABASE: To understand how the problem query event is tied to in your workbook(s), you first need to understand what connections are making queries to the database. See the following section to see an example of how to use the Tableau REST API to find and enable query tagging for the right connections.
Note: This step is not required for data connections using Hyper, Snowflake, or Teradata as query tagging is enabled by default for those data stores.
On Tableau Server, you can choose to disable query tagging for Snowflake or Teradata by using TSM configuration set options to set the value osResourceLuidsInSnowflakeSessionQueryTagsEnabled
(Link opens in a new window) orResourceLuidsInTeradataSessionQueryBandsEnabled
(Link opens in a new window) tofalse
. -
LOOK FOR THE TABLEAU QUERY ORIGINATOR IN YOUR DATABASE MONITORING: At this point, query tagging is enabled for the connections between your workbook or data source and your database. When you find an instance of your next instance of your recurring problem in your database log, history, or analytics, you will see that the related query has a string appended beginning with
/* "tableau-query-origins": "..." */
. That string includes the LUID of 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: Now that you know the LUID of the Tableau element that is making the problem query, you can find it’s name and properties with a request to the get workbook method.
Once you have completed your investigation, you can update the workbook or data source connections to disable query tagging.
Python Script Example to Enable Query Tagging
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 connection using query data source connections(Link opens in a new window). ch resource 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 making 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 # or higher
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])