Trigger Tableau Schedule from Java

 In FOR BI PROS, LEARN, Tableau

java_trigger

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.

 

Talend_anyscale_700

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:

Server Auth

 

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.

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

Server Auth 4

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:

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:

	/**
	 * 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:

/**
 * 
 */
package tableauSrvrWebservice.Auth;

/**
 * @author Horváth Attila
 * @created 2013.08.02
 *
 */

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.StringReader;
import java.math.BigInteger;
import java.security.InvalidKeyException;
import java.security.KeyFactory;
import java.security.NoSuchAlgorithmException;
import java.security.PublicKey;
import java.security.spec.InvalidKeySpecException;
import java.security.spec.RSAPublicKeySpec;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.crypto.BadPaddingException;
import javax.crypto.Cipher;
import javax.crypto.IllegalBlockSizeException;
import javax.crypto.NoSuchPaddingException;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.apache.commons.codec.binary.Hex;
import org.apache.http.Header;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

public class WebServiceClient {
	/**
	 * Fetches The httpresp HttpResponse into a StringBuffer
	 * 
	 * @param httpresp
	 *            HttpResponse
	 * @return StringBuffer with contents of the HttpResponse
	 * @throws IOException
	 */
	public static StringBuffer fetchResponse(HttpResponse httpresp)
			throws IOException {
		BufferedReader bufferedReader = new BufferedReader(
				new InputStreamReader(httpresp.getEntity().getContent()));

		StringBuffer strbuffer = new StringBuffer();
		String currentline = "";
		while ((currentline = bufferedReader.readLine()) != null) {
			strbuffer.append(currentline);
		}
		return strbuffer;
	}

	/**
	 * 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
	 * @throws ClientProtocolException
	 * @throws IOException
	 * @throws ParserConfigurationException
	 * @throws SAXException
	 * @throws NoSuchAlgorithmException
	 * @throws InvalidKeySpecException
	 * @throws NoSuchPaddingException
	 * @throws InvalidKeyException
	 * @throws IllegalBlockSizeException
	 * @throws BadPaddingException
	 */
	public static HttpClient authenticate(String serveraddress, String user,
			String password,
                        String schedule
                        ) throws ClientProtocolException, IOException,
			ParserConfigurationException, SAXException,
			NoSuchAlgorithmException, InvalidKeySpecException,
			NoSuchPaddingException, InvalidKeyException,
			IllegalBlockSizeException, BadPaddingException {
		// Initialize apache HttpClient

		HttpClient client = new DefaultHttpClient();
                
		// Create Http Get request for authentication informations

		String url = serveraddress + "/auth.xml";

		HttpGet request = new HttpGet(url);
		HttpResponse response = client.execute(request);

		StringBuffer result = fetchResponse(response);

		// Parse XML FROM the result
		StringReader reader = new StringReader(result.toString());

		DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
		DocumentBuilder db = dbf.newDocumentBuilder();
		InputSource is = new InputSource();
		is.setCharacterStream(reader);

		Document doc = db.parse(is);

		// Get Required data for creating the authentication request, such as
		// modulus and exponent of the RSA public key and the authencity_token
		String modulusstr = null;
		String exponentstr = null;
		String authencity_token = null;

		NodeList elements = doc.getElementsByTagName("authinfo");
		for (int i = 0; i < elements.getLength(); i++) {
			NodeList moduluses = ((Element) elements.item(i))
					.getElementsByTagName("modulus");
			for (int k = 0; k < moduluses.getLength(); k++) {
				modulusstr = moduluses.item(k).getTextContent();
			}
			NodeList exponents = ((Element) elements.item(i))
					.getElementsByTagName("exponent");
			for (int k = 0; k < exponents.getLength(); k++) {
				exponentstr = exponents.item(k).getTextContent();
			}
			NodeList authencity_tokens = ((Element) elements.item(i))
					.getElementsByTagName("authenticity_token");
			for (int k = 0; k < exponents.getLength(); k++) {
				authencity_token = authencity_tokens.item(k).getTextContent();
			}
		}

		// Parse the modulus and exponent into a BigInteger and create an RSA
		// public key from it
		BigInteger modulus = new BigInteger(modulusstr, 16);
		BigInteger exponent = new BigInteger(exponentstr, 16);

		KeyFactory keyFactory = KeyFactory.getInstance("RSA");
		RSAPublicKeySpec pub = new RSAPublicKeySpec(modulus, exponent);
		PublicKey pubkey = keyFactory.generatePublic(pub);

		Cipher cipher = Cipher.getInstance("RSA");
		cipher.init(Cipher.ENCRYPT_MODE, pubkey);

		// Encrypt the password with the created public key
		byte[] cipherData = cipher.doFinal(password.getBytes());
		String cryptedpass = Hex.encodeHexString(cipherData);

		// Create a post request for the authentication
		HttpPost postrequest = new HttpPost(serveraddress + "/auth/login.xml");

		// Fill in parameters
		List<NameValuePair> nvps = new ArrayList<NameValuePair>();
		nvps.add(new BasicNameValuePair("authenticity_token", authencity_token));
		nvps.add(new BasicNameValuePair("crypted", cryptedpass));
		nvps.add(new BasicNameValuePair("username", user));

		// bind parameters to the request
		postrequest.setEntity(new UrlEncodedFormEntity(nvps));
		HttpResponse postResponse = client.execute(postrequest);

                // 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:

  1. Get the authenticity token from the previous web call
  2. Set up the POST server URL
  3. 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!

 

Contact Us

We're not around right now. But you can send us an email and we'll get back to you, asap.

Not readable? Change text. captcha txt