One of our customers asked me to help trigger Tableau Server extracts/schedules from their ETL application. The most easier way is to invoke tabcmd and call the runschedule command. Well, I am not huge fan of the command line invocation from tools and applications, it’s fragile and platform dependent (our ETL is running on Linux and Solaris). One option could be the Tableau Server 8.2 REST API, but unfortunately it is more user/workbook management than publishing or triggering – it misses the necessary API call for triggering schedules. For me the cleanest solution is to build an ETL component and invoke Tableau Server directly. In this post I will explain how to design and implement the code which invoke a particular Tableau Server feature (like running schedule), while in Part 2 I will show how you can encapsulate it to an ETL component.
This client uses Talend which relies on java components and plugins, thus I should build the wire frame in java as well. I am using the WebAuth class from “Undocumented Tableau Server Authentication in Java” as starter. This class simply logs in to the tableau server (without the need of trusted authentication, so you can test it on your own PC instead of trusted locations) and let you execute your own code on the authenticated channel.
To build this template code first download the java sources from GitHub and open with one of your favorite java editor (intelliJ, eclipse, or netbeans). This sample project is maven based, so you should build the sources with maven which will download the necessary dependencies from the maven sources:
You can test if everything works properly with the bundled tests. First you should change the server address, username and password in the AppTest.java file, then call the JUnit test.
1 2 3 4 5 6 7 8 9 10 |
------------------------------------------------------- T E S T S ------------------------------------------------------- Running tableauSrvrWebservice.Auth.AppTest Finished! Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 2.255 sec Results : Tests run: 1, Failures: 0, Errors: 0, Skipped: 0 |
This looks cool so far, so lets add the schedule refresh logic. Okay, but how? The easy way is always cheating, lets see how tabcmd does this. You should look into the tabcmd.jar file (copy it to tabcmd.zip, then you can simply unzip it and look into the command folder).
As you see, there is one .rb file per one tabcmd command in the tabcmd.zip\tabcmd\lib\commands folder.
If you open the runschedule.rb, you will see the magic – how tabcmd executes the web request:
1 2 3 4 5 6 7 8 9 10 11 |
def runschedule(schedule) request = Server.create_request("run/schedules", "Post") params = [] params += [ text_to_multipart('name', schedule) ] params += [ text_to_multipart('format', 'xml') ] params += [ text_to_multipart('authenticity_token', Server.authenticity_token) ] request.set_multipart_form_data(params) logger.info "Running schedule '#{schedule}'..." response = Server.execute(request) logger.info Server.display_error(response, false) end |
What we can see here?
- The URL what we should invoke is “/run/schedules” with POST method
- We need two parameters: format which is xml and the name of the schedule
- We should pass the authenticity token (as always).
Hurry up, add this to our java code. First, extend the function with this additional schedule name parameter:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
/** * Creates a HttpClient with authenticated connection to the Tableau Server * * @param serveraddress * - URL address of the Tableau Server * @param user * - Username on Tableau Server * @param password * - Corresponding password to the Username * @param schedule * - Name of the schedule to trigger * @return HttpClient with authenticated connection to the Tableau Server */ public static HttpClient authenticate(String serveraddress, String user, String password, String schedule ) throws ClientProtocolException, IOException, ParserConfigurationException, SAXException, NoSuchAlgorithmException, InvalidKeySpecException, NoSuchPaddingException, InvalidKeyException, IllegalBlockSizeException, BadPaddingException { |
To add the HTTP call which passes the schedule name to runschedule, you should use something like this:
183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 |
// We clear the entity here so we don't have to shutdown the client StringBuffer fetchResponse = fetchResponse(postResponse); Pattern authPattern = Pattern.compile( "authenticity_token>(.*?=)</authenticity_token"); Matcher m = authPattern.matcher(fetchResponse); if (m.find()) { authencity_token = m.group(1); } else { throw new ParserConfigurationException("Auth token not found"); } // Set up URL and parameters postrequest = new HttpPost(serveraddress + "/run/schedules"); nvps.clear(); nvps.add(new BasicNameValuePair("authenticity_token", authencity_token)); nvps.add(new BasicNameValuePair("format", "xml")); nvps.add(new BasicNameValuePair("name", schedule)); // add params postrequest.setEntity(new UrlEncodedFormEntity(nvps)); // fetch postResponse = client.execute(postrequest); fetchResponse = fetchResponse(postResponse); return client; |
What happens here:
- Get the authenticity token from the previous web call
- Set up the POST server URL
- Add the parameters to the request
We can go to the server and check results, the schedule was triggered and the extracts are refreshed. Pretty cool, isn’t it? With few lines of code, without the hassle of the tabcmd porting or executable invocation.
In the next round we will see how can you add this java code into your Talend ETL workflows and call them directly after data warehouse data refresh.
If you like this solution, don’t forget to share it!
- Tableau Extensions Addons Introduction: Synchronized Scrollbars - December 2, 2019
- Tableau External Services API: Adding Haskell Expressions as Calculations - November 20, 2019
- Scaling out Tableau Extracts – Building a distributed, multi-node MPP Hyper Cluster - August 11, 2019