How to read JSON data into Tableau – using a free and open source framework (Apache Drill on Windows)

Tableau does not read JSON data natively. By using Apache Drill, you can use Tableau to point at JSON data and start analysing it.

Apache Drill is an open source software framework that supports data intensive distributed applications for interactive analysis of large-scale datasets – Wikipedia.

The Apache drill version I am using does not require a lot of infrastructure such as a Hadoop cluster platform behind it. The version I am using is the Apache Drill for Windows. The installation and connection to JSON data into Tableau is quite simple and is done in my laptop.

Apache Drill is a very powerful open source framework and in this blog post I am using Apache Drill on Windows simply to connect my Tableau into JSON data. Yes, this only works in Windows operating system. Sorry non-Windows OS users, you can go here to install Drill on Linux and Mac OS X.

Below are simple, easy steps to get you connecting Tableau into JSON data and start analysing it.


If your computer does not have JAVA installed, please follow this section. If already installed, skip to the next section of Installing Apache Drill on Windows.

1. Download the latest JDK from Java SE Downloads

2. Install Java

3. Once installed, find the folder in which your Java is installed. Mine is located in C:\Program Files\Java\jdk1.8.0_45

4. Copy the location of your Java installation directory into a clipboard – mine is C:\Program Files\Java\jdk1.8.0_45

5. In the Windows search, I typed in System Variables and this should open the System Properties window

  • Click on the Advanced tab
  • Click on the Environment Variables button
  • Under System variables, click the New button
  • Enter Variable Name = JAVA_HOME
  • Variable Value = the location of your Java, mine is C:\Program Files\Java\jdk1.8.0_45java_home2
  • Click OK to close


Source: Installing Apache Drill on Windows

1. Download the latest version of Apache Drill from Latest version of Apache Drill

2. Move the apache-drill-1.1.0.tar.gz file to a directory where you want to install Drill. I put mine in C:\Program Files\

3. Unzip the TAR.GZ file using a third party tool. The extraction process creates the installation directory named apache-drill-1.1.0 containing the Drill software. For example:

Apache Drill install folder


1. Open Command Prompt.

2. Open the apache-drill-1.1.0 folder. For example, my Apache Drill is located on C:\Program Files\apache-drill-1.1.0

Therefore, in my command prompt, I typed in cd C:\Program Files\apache-drill-1.1.0

Command Prompt Drill

3. Go to the bin directory. For example, I typed cd bin


4. Type the following command on the command line: sqlline.bat -u “jdbc:drill:zk=local”


Source: Installing MapR Drill ODBC Driver

1. Download the MapR Drill ODBC Driver from here

I installed the 64-bit driver as my computer is Windows 64-bit.

2. Double-click the installer from the location within which you downloaded the file.

  • Click Next
  • Select the check box to accept the terms of the License Agreement and click Next
  • Verify or change the install location. Then, click Next – I didn’t change the install location, I leave it to the default one.
  • Click Install
  • When the installation completes, click Finish

3.  Lets verify the installation. In the Start menu, type in ODBC and select the 64-bit ODBC Administrator to open ODBC Administrator.


4. Once open, click on Drivers tab and verify that the MapR Drill ODBC Driver appears in the list of drivers that are installed on your computer.


5. Let’s test the drivers. Whilst still having your ODBC Administrator open, select the System DSN tab.

  • Click the Add buttonsystem-dsn
  • Select the MapR Drill ODBC Drivermapr-data-source
  • In the Data Source Name, enter MapR ODBC Driver for Drill DSNmapr-dsn
  • Click on the Test button on the bottom and you should see a SUCCESS! message as per the below. Click OK several times to close the ODBC Administratorsuccess


1. If your ODBC Administrator is not yet open, go to Start menu and type in ODBC Administrator and click Enter to open. Select System DSN tab and double click on MapR ODBC Driver for Drill DSN.


2. Click on the Drill Explorer button to open Drill Explorer.


3. Expand the plus sign next to dfs_default until you get into a folder for your sample JSON data. My own sample JSON data is located in C:\Data\JSON so I browse from dfs_default\Data\JSON

4. Click on the sample JSON file – mine is my_json.json and I can see the preview of the JSON data.


5. Select the SQL tab (next to Browse tab) and click Preview button. You should see the preview of JSON data. Copy the SQL query into the clipboard.


6. Open Tableau, select connecting via ODBC Driver. Once the ODBC Driver is opened, connect using DSN and select your MapR ODBC Driver for Drill DSN that you created earlier. Click Connect. Click OK.


7. Click on the Schema drop down menu to select a schema, click on the magnifying glasses to show all schema, select dfs.default schema.


8. Double Click on the New Custom SQL option to open a custom SQL window.


9. Paste the SQL Query that you copied from the Drill Explorer in Step 5 above. Click on the Preview Results button.


10. This is where I spent a bit of time noting down which individual column fields I would like to include in my SQL query. Unfortunately, you have to explicitly define the individual fields you would like to bring into Tableau. I noted in my sample JSON data that I would like to bring 8 fields – address, balance, company, eyeColor, favoriteFruit, gender, name, age.

So I close the preview data window, and modify my Tableau Custom SQL Query into the below then I clicked the Preview Results button.

SELECT address, balance, company, eyeColor, favoriteFruit, gender, name, age FROM `dfs`.`default`.`./Data/JSON/my_json.json`


11. Close the Preview Results window and Click OK to close my Edit Custom SQL window. You can now can see the selected fields in the Tableau data explorer.


12. Select Go to Worksheet and start using the fields from my JSON sample data.

13. Create a Tableau Data Extract to create a portable offline copy in Tableau inline columnar format. This is to avoid the ODBC driver limitation. In my Tableau Data Window, I right click into my Custom SQL_Query data source then select Extract Data.


14. Continue with creating an extract and save the extract in a desired location in your computer.

15. Now you can start analysing your JSON data in Tableau. Voila!!



JSON documentation –

Using Apache Drill with Tableau Desktop 9 –

How to read JSON data into Tableau – using a free and open source framework (Apache Drill on Windows)

How to improve your Tableau skills

Baby learning

Every Tableau Jedi started as a novice. They did not become proficient overnight. They gather plenty of learning resources, continuously practicing and challenging themselves to improve their Tableau skills.

When I first encountered Tableau, I was impressed with the amount of learning resources available, such as books and online articles.

Below is a summary of the learning resources that I use on the journey to becoming a Tableau Jedi.


Tableau offers both paid and free training – schedules and types of training can be found here. You need to create a once off free account (even if you are not ‘yet’ a Tableau customer) to access all the training tutorials and materials :).

  • Classroom training – These paid training sessions are fantastic if you like to learn in a structured way. Tableau offers physical and virtual classroom training.
  • On Demand training – As a time poor individual, I prefer the flexibility to learn at my own time and pace. Tableau On Demand training provides online video tutorials that enable me to do just that. Moreover, On Demand training is FREE. The videos range from 5 to 20 minutes. Most of the video tutorials come with Tableau workbooks that you can play along with whilst watching the video. This is fantastic if you like a hands-on training. You can also do this training offline by downloading the videos and workbooks. Based on my personal experience, I do not recommend you spend a week or two watching all the videos in the On Demand training. The best way is to consume what you need, then go back and apply what you’ve learnt. This can be an iterative process. For example, I start with the video detailing how to connect to my data, then go back to my Tableau workbook and implement what I’ve just seen. I may then encounter another challenge which is how to connect to another data source. I’ll then go back to my On Demand training video, watch it and them use these learnings in my own workbook.
  • LIVE Online training – I thank Tableau for providing another FREE training resource on top of the extensive On Demand training videos and tutorials. You can check out the schedules for the LIVE Online training here.The only downside of this LIVE Online training is it’s scheduled at a specific time and date, mostly North America time. If you live in the other part of the world, the time difference can be a challenge.


Tableau webinars is one of my favourite learning resources. It provides much more than just the technical aspects of learning Tableau. Here you’ll have the opportunity to listen to industry leaders on the latest trends as well as customers sharing their experiences. You can also watch previous webinars.

Tableau Community

Tableau has one of the most active user groups and high number of followers. Tableau Community provides you with access to Forums – a fantastic way to have any Tableau question answered quickly or enable you to share your Tableau knowledge by responding to posts by others. If you’re having a problem, there’s a good chance others have encountered the same.

You can also connect to Groups as well as talk about topics of interests in Tableau Community.

To access Tableau Community, create a once off free account. The same account can be used to access Tableau training and tutorials.

Tableau Public

Tableau Public is often called the ‘YouTube’ for Tableau visualisation. Anyone can use Tableau public to build their own visualisation and share it with the world.

You can browse the Tableau Public Gallery or subscribe to Viz of the day by clicking in one of the viz’s and then on the Subscribe link.


It’s truly a source of inspiration.

Often, if I like a particular Viz, I download the workbook from Tableau Public, open it with my Tableau Desktop then reverse engineer the Viz. I get to learn so much from Tableau Public users who tend to be full of creative people, many of whom use Tableau in a way that I would never have thought possible.


Events and Conference

What better way to meet other Tableau enthusiasts than in person. Tableau regularly holds and sponsors events, user groups and conferences. You can find the events and schedules here. By attending these events, not only do you get the chance to put names to faces, but also make new friends that have the same interests and face similar challenges. This is a great way to connect and expand your Tableau skills.

My favorite event is the Tableau Annual Conference (TC). The upcoming TC15 will be held in Las Vegas. It’s the biggest and hottest event of the year. I’d like to compare it with a Rock Concert because it shares the same characteristics –

  • It has rock bands (not just one but many of them)
  • Meet and Greet with the Rockstars – You get to meet and learn from Tableau Jedis and Zen masters as well as fellow Tableau users / customers. You also get to meet Tableau employees ranging from the maker of the software, the founders, the support team, the consultants and they are all freakishly friendly 🙂
  • The key note speakers are inspirational. Past speakers have included Hans Rosling (Data Visionary from Gap Minder Foundation), Michael Lewis (author of Liar’s Poker and Moneyball), Neil deGrasse Tyson (Astrophysicist) and Dr John Medina to name a few
  • There is heaps of hands-on training (you get to learn a lot more than you can possibly learn on your own in one year)
  • Bring your Tableau problems to Tableau Doctors – you can make an appointment with them and get those tricky questions resolved by Tableau employees. You even also get the prescriptions emailed to you after your appointment which contains solutions to your problem or follow ups
  • Get certified – you can get Tableau certification on the spot and earn those well deserved medals.


I follow several blogs about Tableau. Here is my personal list (feel free to share yours as well):

Tableau Whitepapers

The Tableau website also has a lot of interesting whitepapers ranging from best practice visual analysis through to designing an efficient workbook. To access Tableau whitepapers, you can create a once-off free account.


There are lots of books written about Tableau and Data Visualisation.

If you are after a book to learn Tableau, this book is an excellent one – Learning Tableau by Joshua MilliganJoshua is one of the Tableau Zen Masters and I am a big fan of his blog VizPainter.


Here is a list of interesting books to supplement the above resources and further enhance your Tableau and data analysis skills:


Last but not least, you can always ‘Google It’.

Google Tableau

How to improve your Tableau skills