We all have our own Tableau Conference habits. Mine is to participate in the first-day hackathon, build something super cool that nobody understands and in the end win nothing. Tableau 2019 was no exception. Earlier this year, our team won the NYC Tableau Hackathon — and I had almost nothing to do with it. My tasks were limited to keep our squad hydrated, caffeinated and fed, so for TC19 I had no real pressure to go competitive. I was completely free and in a bit of an artsy mood so I decided to build one of my old dream projects:
adding the Haskell programming language as a calculation engine to Tableau, by using External Services API.
If you have no clue what does that means — or even if you do — read on.
Tableau External Services API?
External Services API is the most underrated, underdocumented API in the whole Tableau Ecosystem. It’s so neglected, that it doesn’t even belong to the Tableau’s developer platform. This is fine with me. In fact, it makes it all that much more interesting, I hate popular things, and frankly being a trailblazer these days is greatly appreciated.
I know TabPy, but what could be that “External API”?
If you search the term “Tableau External Services“, you will find some documentation on Tableau’s help site about it: “Tableau supports a set of functions that you can use to pass expressions to external services for integration with R, MATLAB and Python.”, but none of these pages mentions “External API”. The only single document I found is here, buried three subpages down on TabPy’s Github wiki. While the page describes the API interface it forgets to mention what this REST API is actually doing.
So here is the deal: with the External Services API you can:
- Add new supported programming languages to the Tableau Calculation engine (by script_ commands).
- Call web services directly as calculations without any TabPy, RServer or other middleware.
Pretty cool, huh? #2 works well when you start building your own, company-specific calculations or data processing functions in pure AWS Lambdas then invoke it directly from Tableau Desktop or Server. I honestly have no clue why Tableau keeps it a secret, but it’s definitely a good material for another hackathon ;-).
UPDATE: in the meantime, Craig Bloodworth made an example for external webservice api call as calculation, his video is here: https://www.youtube.com/watch?v=h7wbVy5GCXY.
However, as I’m about to add a new language to Tableau, let’s focus on #1.
Why Haskell?
“Being a special snowflake” is my number one mission during these hackathons. As a programming language, Haskell seems to have a similar mission, it is really different — in a good way — from other programming languages. To understand why Haskell is different, let me share a quote from the “How to Learn Haskell” guide:
First of all, you should probably pretend like you’ve never programmed before. If you run into a word like return or class don’t assume that you know what that means; chances are that you don’t. Here’s a general idea of where you stand, depending on what languages you already know well:
C, C++, Java: Haskell will probably blow your mind. The notion of classes is almost completely different, return doesn’t mean what you think it does, the algorithmic patterns are distinct, you don’t get loops, and code doesn’t run in the order it’s typed on the screen. What!? Yeah. It’s pretty awesome.
Trust me, it is *really* awesome, especially when it goes to analytical expressions. Look at this Fibonacci calculation code snippet:
|
1 |
fix (scanl (+) 0 . (1:)) |
Every single character has its meaning. Fix function is typed recursive lambda generator, scanl is an accumulator function, “.” is a function composition operator and “:” a list constructor. After staring the expression for a few seconds things starts to make sense.
Another example for calculating prime numbers:
|
1 2 3 |
primesT = sieve [2..] where sieve (p:xs) = p : sieve [x | x <- xs, rem x p > 0] |
It looks exactly as the mathematical definition of prime numbers. This is one Haskell’s values: its expressions are close to mathematical equations.
Let’s see how can we add these functions directly to Tableau.
The Code
The complete codebase for Haskell integration is not more than twenty lines. In different words, with less than a screen of code, you can add live data and custom calculations based on web services or new programming languages to Tableau by using External Services API.
|
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
data EvalRequest = EvalRequest { _script :: Text, _data :: HashMap Text Value } deriving (Show, Generic, ToJSON) instance FromJSON EvalRequest where parseJSON = genericParseJSON defaultOptions { fieldLabelModifier = Prelude.drop 1 } replaceArgsInScript (EvalRequest s d) = foldrWithKey replaceValuesScript s d where getArrayText = T.toStrict . T.decodeUtf8 . encode replaceValuesScript k v = replace k (getArrayText v) command = replace_args . decode where replace_args Nothing = "Json parse error" replace_args (Just er) = unpack $ replaceArgsInScript er executeCommand p = readProcess "mueval" ["-e", command p] [] infoResponse = "{\"description\": \"\", \"creation_time\": \"0\", \"state_path\": \"\", \"server_version\": \"0.8.7\", \"name\": \"haskell\", \"versions\": {\"v1\": {\"features\": {}}}}" textAsJson t = setHeader "Content-Type" "application/json" >> text t main = scotty 3000 $ do get "/info" $ textAsJson infoResponse post "/evaluate" $ do jsonData <- body cmdRes <- liftIO $ executeCommand jsonData textAsJson $ T.pack cmdRes |
In order to act like an external service you have to implement two REST API endpoints: /info and /evaluate. Info is fairly simple, it just returns with a static JSON file as defined and documented here.
The /evaluate is a bit more tricky, this is the actual endpoint called by the SCRIPT_STR, SCRIPT_INT and other SCRIPT functions. The definition of this endpoint is here, but basically it gets a block of code, set of named parameters with their provided values, then returns with the result. The expected POST request body from Tableau is a JSON dictionary with two elements:
- A key
datawith a value that contains the parameter values passed to the code. These values are key-value pairs, following a specific convention for key names (_arg1,_arg2, etc.). - A key
scriptwith a value that contains the Python code (one or more lines). Any references to the parameter names will be replaced by their values according todata.
The calculation SCRIPT_INT("sort _arg1", SUM([numbers])) will trigger the following /evaluate request:
|
1 2 3 4 |
POST /evaluate HTTP/1.1. {"script":"sort _arg1", "data" :{"_arg1":[1,2,3,4,5,6,8,7,9,10]}} |
All we need to do is to replace _arg1 with actual values, execute script and return. From the above request, the code we should execute is:
|
1 |
sort [1,2,3,4,5,6,8,7,9,10] |
Nice and easy.
Getting up and ready our External Server
You can download the project from https://github.com/tfoldi/tc19-haskell-ext/. All you have to do is install stack in case you don’t have it and call stack run.
Next time you start Tableau Desktop you should set the external service provider in Help, Settings and Performance, Manage External Service Connection.
Set connection to localhost, port 3000
You should be good to go, let’s give a try and define a simple sort calculation:
And if all works well, we should see these results:
Pretty impressive, we just added Haskell support to Tableau with twenty lines code!
Haskell in Tableau
There are more advanced use cases where you can use the combined power of Extensions and Haskell to do interactive data exploration, but that’s a different blog post.
Fibonacci and Prime numbers in Tableau with Haskell
As always, if you have any questions or comments, just drop a line!
And by all means, leave a comment and let me know what you think.
- 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
