Notes on using Python for working with Tableau, Solr and Lucidworks Fusion server data.
Environment
Install Python (Windows)
choco install python3
Install PyCharm
choco install pycharm-community
Install Tableau Server Client (TSC)
pip install tableauserverclient
Notes:
- The Tableau REST API does not allow substring filtering (
hasoperator) on anything other than Query_Jobs. In other words, you cannot return workbooks with the string "foo" in the title. Theinoperator is available on workbooks, datasources and views, but is only meant to work on tags. Otherwise, the equals operator takes an exact string. See reference. - Choice! Return ALL workbooks from server, store results in memory or file, iterate over results in using Python to return items by substring.
The Tableau server-client is currently missing aget-view-by-idmethod for views, and a for loop using theget-workbook-by-idis slow. - Choice! Use python REST instead of the tableau-server-client for tagging.
- Projects don't have a site_id. Need to authenticate to each site individually and find what projects the site contains.
Code Stuff
Code - Tag Tableau Workbooks and Views
Individual Tableau report developers can easily make their products more find-able on a Tableau server by manually adding tags to their views, workbooks and data sources. But, let's say non-techy business users want to scan 20 thousand Tableau data assets and conditionally apply something like a "Confidential" tag on many assets at once without involving IT. This might call for a Python script that works with the Tableau REST API and follows the values specified in a CSV file.
Declare imports
The Tableau Server/Client is used as a fast way to get all Tableau Workbooks and Views. The REST module is used to add tags to the Tableau server by LUID. The XML parsing module is used for POSTing data to Tableau (JSON could be used, but the environment I'm working with here prefers XML) The CSV parser gets input from a CSV file.
# -------------------
# Imports
# -------------------
# Import the Tableau Server-Client module
import tableauserverclient as TSC
# Import the logging modules
import logging, logging.config
# Import the REST module
import requests
# Import the XML parsing module
import untangle
# Import the csv parser
import csv
Log activities
During development, the logger can be set to stream feedback to the IDE. For implementation, the logger can be set to log to a file. The file is useful for validating and documenting the work that's been done. The code includes a statement for filtering out some of the non-root Tableau logging feedback.
# -------------------
# Logging
# -------------------
logger = logging.getLogger()
# log to console (stream) or file
handler = logging.StreamHandler()
# handler = logging.FileHandler('app.log', mode='w')
# settings
formatter = logging.Formatter('%(asctime)s %(name)-2s %(levelname)-2s %(message)s')
handler.setFormatter(formatter)
logger.addHandler(handler)
logger.setLevel(logging.INFO)
# filter out Tableau (non-root) logging
logging.config.dictConfig({
'version': 1,
'disable_existing_loggers': True,
})
Do some prep work
Locate the CSV file in the current directory and get the Tableau Sites and Projects that needs to be processed. This is the precursor to getting the CSV file from a service.
# -------------------
# Data
# -------------------
# Get a list of items to process from CSV file
dataset = []
with open ('tableau_security_tags_list.csv') as csvDataFile:
csvReader = csv.reader(csvDataFile)
count = 0
for row in csvReader:
if len(row[5]) > 0:
if count > 0:
dataset.append([row[0], row[1], row[2], row[3], row[4], row[5]])
count = count + 1
# logging.info('Process the following data:')
# logging.info(dataset)
# Loop through the items list and build list of unique URLs
logging.info('Process the following urls:')
listOfUrls = []
listOfUrlsTest = []
for item in dataset:
u = item[1].strip()
s = item[2].strip()
id = u + '-' + s.replace(' ', '-')
if id not in listOfUrlsTest:
listOfUrlsTest.append(id)
# Add url to list
listOfUrls.append([id, u])
logging.info(u)
# Loop through the items list and build list of unique sites
logging.info("*************************")
logging.info('Process the following sites:')
listOfSites = []
listOfSitesTest = []
for item in dataset:
u = item[1].strip()
s = item[2].strip()
id = u + '-' + s.replace(' ', '-')
if id not in listOfSitesTest:
listOfSitesTest.append(id)
# Add site to list
listOfSites.append([id, u, s])
logging.info(s)
# Loop through the items list and build list of projects
logging.info("*************************")
logging.info('Process the following projects:')
listOfProjects = []
for item in dataset:
u = item[1].strip()
s = item[2].strip()
p = item[3].strip()
t = item[4].strip()
a = item[5].strip()
id = u + '-' + s.replace(' ', '-') + p.replace(' ', '-')
# Add project to list
listOfProjects.append([id, s, p, t, a])
logging.info(p)
Set variables, cache and test Tableau assets
This is the meant and potatoes work of applying the tags. Tableau requires separate logins for each site. There's a bit of trickiness around for the "default" site that does not apply to named sites.
# -------------------
# Process the tags
# -------------------
# Process data for each url separately
for url in listOfUrls:
logging.info("*************************")
# Server admin credentials
tableauSignIn = 'AD Service Account username'
tableauPassword = 'AD Service Account password'
# Set the Tableau server-client url
server = TSC.Server(url[1])
# Set the API version
server.use_server_version()
api_version = server.version
# Set the Tableau Python API sign-in authorization (needs to be server admin)
initialTableauAuth = TSC.TableauAuth(tableauSignIn, tableauPassword)
# Cache a list of site LUIDs
site_cache = {}
with server.auth.sign_in(initialTableauAuth):
for luid in TSC.Pager(server.sites):
site_cache[luid.id] = luid.name
logging.info("site luid is {" + "\n".join("{!r}: {!r},".format(k, v) for k, v in site_cache.items()) + "}")
response = requests.request("POST", url[1] + '/api/' + api_version + '/auth/signout')
logging.info(listOfSites)
# Get a list of sites related to the url
for site in listOfSites:
if site[0] == url[0]:
# Set the site LUID used for the REST calls
siteId = ''
for key, value in site_cache.items():
if value.lower() == site[2].lower():
siteId = key
# Set the site name
siteName = ''
if value.lower() == 'default':
siteName = ""
else:
siteName = value.replace(' ','')
# Set the authorization string = TSC.TableauAuth('USERNAME', 'PASSWORD', site_id='CONTENTURL')
# Note: CONTENTURL is "url_namespace" for the site in the Postgres Sites table
# Note: CONTENTURL has no spaces
# Note: CONTENTURL for the "Default" site is an empty string
# Note: CONTENTURL "Default" site is not always named "Default"
# Note: CONTENTURL does not handle being a variable with empty quotes, so create with quotes below
if (site[2].lower() == 'default') or (site[2].lower() == 'sjhs tableau site'):
tableauAuth = TSC.TableauAuth(tableauSignIn, tableauPassword, site_id="")
else:
tableauAuth = TSC.TableauAuth(tableauSignIn, tableauPassword, site_id=site[2].replace(' ',''))
# Sign in to a specific site
with server.auth.sign_in(tableauAuth):
logging.info("")
logging.info('Signing in to the "' + site[2] + '" site')
# Set the Tableau REST sign-in token
auth_url = url[1] + '/api/' + server.version + '/auth/signin'
auth_payload = '<tsRequest><credentials name="' + tableauSignIn + '" password="' + tableauPassword + '"><site contentUrl="' + siteName + '" /></credentials></tsRequest>'
auth_headers = {'Content-Type': 'application/xml'}
auth_response = requests.request("POST", auth_url, headers=auth_headers, data=auth_payload)
auth_response_obj = untangle.parse(auth_response.text)
auth_token = auth_response_obj.tsResponse.credentials['token']
# Set the XML REST headers with the auth token
xml_headers = {'X-Tableau-Auth': auth_token, 'Content-Type': 'text/plain'}
# Cache assets locally
with server.auth.sign_in(tableauAuth):
logging.info('')
logging.info('Caching projects, workbooks, and views data in "' + site[2] + '" site')
# Cache all projects
# -------------------
project_counter, project_cache = 0, {}
for project in TSC.Pager(server.projects):
project_cache[project_counter] = (project.id, project.name)
project_counter = project_counter + 1
# logging.info("{" + "\n".join("{!r}: {!r},".format(k, v) for k, v in project_cache.items()) + "}")
# Cache all workbooks
# -------------------
workbook_counter, workbook_cache = 0, {}
for workbook in TSC.Pager(server.workbooks):
workbook_cache[workbook_counter] = (workbook.id, workbook.name, workbook.project_id, workbook.tags)
workbook_counter = workbook_counter + 1
# logging.info("{" + "\n".join("{!r}: {!r},".format(k, v) for k, v in workbook_cache.items()) + "}")
# Cache all views
# -------------------
view_counter, view_cache = 0, {}
for view in TSC.Pager(server.views):
view_cache[view_counter] = (view.id, view.name, view.project_id, view.workbook_id, view.tags)
view_counter = view_counter + 1
# logging.info("{" + "\n".join("{!r}: {!r},".format(k, v) for k, v in view_cache.items()) + "}")
for project in listOfProjects:
u = project[0]
s = project[1]
projectName = project[2]
if u == url[0]+projectName.replace(' ', '-'):
logging.info('')
# Set the tag label
newTagLabel = project[3]
# Set the action to take
actionToTake = project[4]
# Build list of workbooks and views needing tags
# ------------------
for project_value in project_cache.values():
cachedProjectId = project_value[0]
cachedProjectName = project_value[1]
# Enable partial match
# if re.search(x, project.name):
# Enable exact match
if cachedProjectName == projectName:
logging.info('Project - ' + projectName)
for workbook_value in workbook_cache.values():
workbookId = workbook_value[0]
workbookName = workbook_value[1]
workbookProjectId = workbook_value[2]
workbookTags = workbook_value[3]
if workbookProjectId == cachedProjectId:
# create the REST URL
workbook_Url = url[1] + '/api/' + api_version + '/sites/' + siteId + '/workbooks/' + workbookId + '/tags'
# add or delete tags
tag_item('Workbook', workbookName, workbook_Url, workbookTags, newTagLabel, actionToTake)
for view_value in view_cache.values():
viewId = view_value[0]
viewName = view_value[1]
viewWorkbookId = view_value[3]
viewTags = view_value[4]
if viewWorkbookId == workbookId:
# create the REST URL
view_Url = url[1] + '/api/' + api_version + '/sites/' + siteId + '/views/' + viewId + '/tags'
# add or delete tags
tag_item('View', viewName, view_Url, viewTags, newTagLabel, actionToTake)
# Log out of tableau server
response = requests.request("POST", url[1] + '/api/' + api_version + '/auth/signout')
# Sign out
logger.info('')
logger.info('Signing out of ' + url[1])
# Job complete
logger.info('')
logger.info('Job is complete')
Apply tags
Actual tagging is done with a function that composes each POST request. The function looks for a setting in the CSV file which defines whether to add, test, or verify the job. This is useful for collecting separate log files which record the previous and post job states of tags on the Tableau server.
# -------------------
# Define the tagging process
# -------------------
def tag_item(type, name, itmUrl, existingTags, newTagLabel, actionToTake):
xml_payload = '<tsRequest><tags><tag label="' + newTagLabel + '"/></tags></tsRequest>'
# Not already tagged
if newTagLabel not in existingTags:
# remove label
if actionToTake == 'add':
logging.info('___ Add "' + newTagLabel + '" tag to ' + type + ' - ' + name + '"')
response = requests.request("PUT", url=itmUrl, headers=xml_headers, data=xml_payload)
logging.info(response.text.encode('utf8'))
# announce that label does not exist (test/verify)
else:
logging.info('___Would have added "' + newTagLabel + '" tag to ' + type + ' - ' + name + '"')
# Already tagged
else:
# remove label
if actionToTake == 'remove':
logging.info('___ Remove ' + newTagLabel + ' from "' + type + ' - ' + name + '"')
response = requests.request("DELETE", url=itmUrl + '/' + newTagLabel, headers=xml_headers)
logging.info(response.text.encode('utf8'))
else:
# announce that label already exists (test/verify)
logging.info('___ ' + type + ' - ' + name + ' is already tagged "' + newTagLabel + '"')
Send Tableau Tags to Solr Index
Declare imports
The Tableau Server/Client is used as a fast way to get all Tableau Workbooks and Views, the REST module is used to add tags to the Tableau server by LUID, and the pysolr module gets the data into the Solr index.
# -------------------
# Imports
# -------------------
# Import the Tableau Server-Client module
import tableauserverclient as TSC
# Import the logging modules
import logging, logging.config
# Import the REST module
import requests
# Make solr request for all workbooks
import pysolr
Log activities
During development, the logger can be set to stream feedback to the IDE. For implementation, the logger can be set to log to a file. The file is useful for validating and documenting the work that's been done. The code includes a statement for filtering out some of the non-root Tableau logging feedback.
# -------------------
# Logging
# -------------------
# Set the logger to stream or file
logger = logging.getLogger()
# log to console (stream) or file
handler = logging.StreamHandler()
# handler = logging.FileHandler('app.log', mode='w')
# settings
formatter = logging.Formatter('%(asctime)s %(name)-2s %(levelname)-2s %(message)s')
handler.setFormatter(formatter)
logger.addHandler(handler)
logger.setLevel(logging.INFO)
# filter out Tableau (non-root) logging
logging.config.dictConfig({
'version': 1,
'disable_existing_loggers': True,
})
Set variables
The job type allows for testing ahead of implementation. The other variables prepare credentials and server addresses.
# -------------------
# Settings
# -------------------
# Job type
# jobType = 'test'
jobType = 'apply'
# Set the Tableau server URL
tableau_url = 'http://whatever-this-is'
# Set the Tableau server-client
server = TSC.Server(tableau_url)
# Set the Tableau API version
server.use_server_version()
api_version = server.version
# Set the Tableau sign-in user (needs to be server admin)
tableauSignIn = 'AD Service Account username'
tableauPassword = 'AD Service Account password'
# Set the Tableau Python API sign-in authorization (needs to be server admin)
initialTableauAuth = TSC.TableauAuth(tableauSignIn, tableauPassword)
# Set the Fusion server Index Profile URL (We're actually using Ludidworks Fusion here)
fusion_url = 'http://fusion-index-profile'
# Set the Fusion API headers
headers = {'content-type': 'application/json', 'Accept-Charset': 'UTF-8', 'Authorization': 'Basic abc123DEFgiaa=='}
# Set the Solr server
host = 'http://solr server address'
querystring = '*:*'
fields = 'id,title'
qt = 'select'
filterquery_views = '_lw_data_source_s:("Tableau-Azure-Views")'
filterquery_workbooks = '_lw_data_source_s:("Tableau-Azure-Workbooks")'
rows = '100000'
Cache data
Get all Tableau workbook and view IDs already in Solr.
# -------------------
# Get all items from Solr Index
# -------------------
# Solr Core
logging.info('Caching Solr Data')
solr = pysolr.Solr(host, search_handler=qt, always_commit=True)
results_workbooks = solr.search(querystring, **{
'fl': fields,
'fq': filterquery_workbooks,
'rows': rows,
# 'hl': 'true',
# 'hl.fragsize': 10,
})
solr = pysolr.Solr(host, search_handler=qt, always_commit=True)
results_views = solr.search(querystring, **{
'fl': fields,
'fq': filterquery_views,
'rows': rows,
# 'hl': 'true',
# 'hl.fragsize': 10,
})
solr_cache_workbooks = {}
for row_workbook in results_workbooks:
solr_cache_workbooks[row_workbook['id']] = (row_workbook['id'])
logging.info('Returned {0} workbooks from Solr'.format(len(results_workbooks)))
logging.info('Cached {0} workbooks from Solr'.format(len(solr_cache_workbooks)))
solr_cache_views = {}
for row_view in results_views:
solr_cache_views[row_view['id']] = (row_view['id'])
logging.info('Cached {0} views from Solr'.format(len(solr_cache_views)))
logging.info('Returned {0} views from Solr'.format(len(results_views)))
logging.info('')
Cache more data
Cache all workbook and view assets from Tableau server, then send only tagged assets to Solr if the asset already exists in the Solr index.
# -------------------
# Identify the sites
# -------------------
# Cache a list of site LUIDs
site_cache = {}
with server.auth.sign_in(initialTableauAuth):
for luid in TSC.Pager(server.sites):
site_cache[luid.id] = luid.name
# logging.info("site luid is {" + "\n".join("{!r}: {!r},".format(k, v) for k, v in site_cache.items()) + "}")
# -------------------
# Process the tags
# -------------------
# Set the site LUID used for the REST calls, e.g. '87cfedbd-70c5-4cf6-9cbf-a63e80cce7a2'
for key, value in site_cache.items():
# Set the authorization string = TSC.TableauAuth('USERNAME', 'PASSWORD', site_id='CONTENTURL')
# Note: CONTENTURL is "url_namespace" for the site in the Postgres Sites table
# Note: CONTENTURL has no spaces
# Note: CONTENTURL for the "Default" site is an empty string
# Note: CONTENTURL "Default" site is not always named "Default"
# Note: CONTENTURL does not handle being a variable with empty quotes, so create with quotes below
if (value.lower() == 'default') or (value.lower() == 'sjhs tableau site'):
tableauAuth = TSC.TableauAuth(tableauSignIn, tableauPassword, site_id="")
else:
tableauAuth = TSC.TableauAuth(tableauSignIn, tableauPassword, site_id=value.replace(' ', ''))
# Sign in to site
with server.auth.sign_in(tableauAuth):
logging.info('Caching Tableau')
# Cache all workbooks
# -------------------
workbook_cache = {}
for workbook in TSC.Pager(server.workbooks):
workbook_cache[workbook.id] = (workbook.name, workbook.tags)
# logging.info("{" + "\n".join("{!r}: {!r},".format(k, v) for k, v in workbook_cache.items()) + "}")
logging.info('Cached {0} workbooks from Tableau'.format(len(workbook_cache)))
# Cache all views
# -------------------
view_cache = {}
for view in TSC.Pager(server.views):
view_cache[view.id] = (view.name, view.tags)
# logging.info("{" + "\n".join("{!r}: {!r},".format(k, v) for k, v in view_cache.items()) + "}")
logging.info('Cached {0} views from Tableau'.format(len(view_cache)))
# Build workbook datasets
# -------------------
logging.info('')
logging.info('Build workbooks dataset')
workbook_set = {x: workbook_cache[x] for x in workbook_cache if x in solr_cache_workbooks}
logging.info('Built dataset with {0} workbooks'.format(len(workbook_set)))
# Build view datasets
# -------------------
logging.info('')
logging.info('Build views dataset')
view_set = {x: view_cache[x] for x in view_cache if x in solr_cache_views}
logging.info('Built dataset with {0} views'.format(len(view_set)))
# POST all workbook tags to Fusion
# -------------------
logging.info('')
logging.info('Sending all workbook tags to Fusion')
for w_key, w_value in workbook_set.items():
id = w_key
name = w_value[0]
tag = ', '.join(w_value[1])
type = 'Workbook'
if len(tag) > 1:
data = '{"id": "' + id + '", "tag":"' + tag + '"}'
runjob(name, tag, type, data)
# POST view tags to Fusion
# -------------------
logging.info('')
logging.info('Sending all view tags to Fusion')
for v_key,v_value in view_set.items():
id = v_key
name = v_value[0]
tag = ', '.join(v_value[1])
type = 'View'
if len(tag) > 1:
data = '{"id": "' + id + '", "tag":"' + tag + '"}'
runjob(name, tag, type, data)
# Sign out
response = requests.request("POST", tableau_url + '/api/' + api_version + '/auth/signout')
logger.info('All done and signed out')
Add the tags to Solr
Use a function to test or apply each POST to a Fusion Index Profile.
# -------------------
# Rinse/Repeat Function
# -------------------
# Run job of each type
def runjob(name, tag, type, data):
if jobType == 'test':
# Run as TEST
logging.info('Would have added tag "' + tag + '" to ' + type + ' "' + name + '"')
else:
# Run for REAL LIKE
response = requests.post(fusion_url, headers=headers, data=data)
logging.info(response.text.encode('utf8'))
References
- Tableau REST API
- Tableau on Github
- Tableau/server-client-python documentation
- Tableau/server-client-python on Github
- Tableau/server-client-python samples
- Publishing to Tableau Server via Python POST Requests using JSON