Welcome to the intermediate DataDev Quest Challenge for February 2025! This challenge is designed to help you learn how to use Tableau REST API, Tableau Server Client, Tableau Metadata API and Tableau Developer Sandbox to update connection details within a workbook/datasource.
Challenge Overview
Objectives:
Use Python (REST API or TSC) to update any combination of username, password, server URL and port for a connection or connections within each workbook or datasource that connects to a specific database.
If you need a small database to work with, we recommend the free tier of tembo.io.
Why this challenge?
We’ve all been there — a database upgrade/migration is going to break dozens or even hundreds of connections within the Tableau workbooks and datasources that connect to it. If you have a smaller environment, you might be tempted to just go through each connection individually and make the changes in the UI, but this obviously isn’t scalable to even medium sized installations, let alone enterprise customers!
You can choose how you wish to find the affected workbooks/datasources for this challenge, although we strongly recommend using the Metadata API to identify impacted items, since querying for workbook/datasource connections via the REST API/TSC can be quite repetitive/expensive if you do not know exactly what you’re looking for.
To complete this challenge, your script/module should identify all workbooks/datasources that contain a connection to your impacted database. Once it has them, it should make the relevant updates to all of them at once.
Learning Goals
- Find workbooks/datasources with connections that meet certain criteria (preferably, using the Metadata API)
- Update connection details (server url, port, username, password)
- Ensure changes show on Tableau Cloud/Server
Submission Guidelines
- Source Code: Publish your project publically in the GitHub profile
- Add README: Include setup instructions and describe how to run the program.
- Video of Solution: Include a video of your solution in the README file, which you can publish on YouTube and embed the iframe. Or save the video file in the directory root inside the repository.
- Comments: Ensure your code is well-commented.
- Submission: Submit your challenge in the following **forms**
Additional Resources
- How to get your Tableau Online Sandbox?
- How to get a Tableau PAT (Personal Access Token)?
- Personal Access Token (PAT)
- Enable Personal Access Token (PAT)
- REST API
- Tableau Server Client (Python)
- Metadata API
Getting Started
If you have not done so already, follow Cristian Saavedra-Desmoineaux’s step-by-step guide to setting your Tableau Developer Sandbox
1. Create or choose a workbooks/datasources you want to interact with.
– Make sure it’s not anything mission critical, as we will be breaking it temporarily
2. Make a change to the connection details that will break the connection, then click ‘Test Connection’
– We want this to be ‘broken’ so that the changes made via the REST API/TSC will fix it
– In this example, I changed the port to an incorrect value
– Repeat this for multiple items that connect to the same database
3. Be sure that you have Enabled the Personal Access Token (PAT) and configure your Personal Access Token (PAT). If you need help, follow Cristian’s Medium Post
4. If using tableauserverclient (TSC), make sure it is installed in your Python environment
– If you’re using python ‘requests’, ensure it’s available
5. If needed, review my article on Tableau REST API/TSC authentication.
– NOTE: You will need to use PAT authentication for this challenge
Challenge
Your challenge is to create a script/module that will do the following:
- Find all workbooks and datasources that connect to a specific database
- Once identified, use the relevant methods to update the connection details that we broke above
- After these changes have been made, ensure that further Test Connection clicks are successful
Our suggested solution will be posted here in mid-March 2025
Hints
If you’re new to using the Metadata API, a helpful query might look something like this: 😉
query useMetadataApiToQueryForConnections {
databaseServersConnection(filter: {hostName: "YOUR-HOST-NAME"}) {
nodes {
id
name
hostName
port
downstreamWorkbooks {
id
name
projectName
projectLuid
}
downstreamDatasources {
id
name
projectName
}
}
}
}
The following REST API/TSC endpoints and methods will help you with this quest:
- Filtering & Sorting: REST API / TSC
- Workbook methods: REST API / TSC
- Datasource methods: REST API / TSC
Solution
Note: Be sure to create a virtual environment and pip install tableauserverclient & pip install requests
Step 1: Create a creds.py file
PAT_NAME = 'YOUR-PAT-NAME'
PAT_SECRET = 'YOUR-PAT-SECRET'
SITE_NAME = 'YOUR-SITE-NAME' # As it appears in the URL
DB_HOST = 'YOUR-DB-HOST'
DB_PORT = 'YOUR-DB-PORT'
DB_USERNAME = 'YOUR-DB-USERNAME'
DB_PASSWORD = 'YOUR-DB-PASSWORD'
ORIGINAL_HOST = 'YOUR-ORIGINAL-HOST'
UPDATED_HOST = 'YOUR-UPDATED-HOST'Step 2: graphql_query.py
def get_graphql_query():
qry = """
query getWorkbooksAndDataSourcesConnectingToHost($dbHost:String) {
databaseServersConnection(filter: {hostName: $dbHost}) {
nodes {
id
name
hostName
port
downstreamWorkbooks {
luid
name
projectName
projectLuid
}
downstreamDatasources {
luid
name
projectName
}
}
}
}
"""
return qryStep 3: find_db_connections.py
from graphql_qry import get_graphql_query
import requests
import tableauserverclient as TSC
import creds
SERVER_ROOT_URL = 'https://YOUR-SERVER-ROOT-URL' # e.g. 10ax.online.tableau.com
SITE_NAME = 'YOUR-SITE-NAME' # As it appears in the URL
def get_rest_api_token():
url = f"{SERVER_ROOT_URL}/api/3.24/auth/signin"
body = {
"credentials": {
"personalAccessTokenName": creds.PAT_NAME,
"personalAccessTokenSecret": creds.PAT_SECRET,
"site": {
"contentUrl": SITE_NAME
}
}
}
headers = {"accept": "application/json", "content-type": "application/json"}
response = requests.post(url, json=body, headers=headers)
return response.json()["credentials"]["token"]
def get_db_connections(hostname):
server = TSC.Server(SERVER_ROOT_URL, use_server_version=True)
tab_auth = TSC.PersonalAccessTokenAuth(creds.PAT_NAME, creds.PAT_SECRET, site_id=SITE_NAME)
with server.auth.sign_in(tab_auth):
conn_updates = server.metadata.query(get_graphql_query(),variables={"dbHost": hostname})
return conn_updates["data"]["databaseServersConnection"]["nodes"]
Step 4: update_connections.py
import tableauserverclient as TSC
import creds
from find_db_connections import get_db_connections
SERVER_ROOT_URL = "https://YOUR-SERVER-ROOT-URL" # e.g. 10ax.online.tableau.com
def update_connections(connection_updates, embed=True):
server = TSC.Server(SERVER_ROOT_URL, use_server_version=True)
tab_auth = TSC.PersonalAccessTokenAuth(
creds.PAT_NAME, creds.PAT_SECRET, site_id=creds.SITE_NAME
)
with server.auth.sign_in(tab_auth):
for update in connection_updates:
for wb in update["downstreamWorkbooks"]:
print("\t", wb["name"])
tsc_wb = server.workbooks.get_by_id(wb["luid"])
server.workbooks.populate_connections(tsc_wb)
for conn in tsc_wb.connections:
if conn.server_address == creds.ORIGINAL_HOST:
conn.server_address = creds.UPDATED_HOST
conn.server_port = creds.DB_PORT
conn.username = creds.DB_USERNAME
conn.password = creds.DB_PASSWORD
conn.embed_password = embed
server.workbooks.update_connection(tsc_wb, conn)
print("\t\tCOMPLETED")
for ds in update["downstreamDatasources"]:
print("\t", ds["name"])
tsc_ds = server.datasources.get_by_id(ds["luid"])
server.datasources.populate_connections(tsc_ds)
for conn in tsc_ds.connections:
if conn.server_address == creds.ORIGINAL_HOST:
conn.server_address = creds.UPDATED_HOST
conn.server_port = creds.DB_PORT
conn.username = creds.DB_USERNAME
conn.password = creds.DB_PASSWORD
conn.embed_password = embed
server.datasources.update_connection(tsc_ds, conn)
print("\t\tCOMPLETED")
conn_updates = get_db_connections(creds.ORIGINAL_HOST)
update_connections(conn_updates)
Who Am I?
Oh, hi there! I’m Kyle Massey, Tableau Visionary and DataDev Ambassador. I’m also one of the founding members of DDQ, and I’m so happy you’re here with us! 😃 I’m passionate about all things data viz, software engineering, automation, etc. Always happy to connect: