You’re building a Tableau report and want to integrate all the data sources, but not all of them are natively supported.
Some offer the ability to export data or REST APIs to get data in JSON format, which you can import into Tableau and build reports.
In this article, you’ll learn different ways to connect JSON data sources to Tableau and leverage them in your analysis.
Different approaches to load JSON to Tableau
Here’s an overview of the different methods for importing JSON data into Tableau:
- Manual import – You can directly import a local JSON file into the report. However, this method is not suitable for loading data that updates regularly, such as JSON from URLs or APIs, as it requires manual updates each time the data changes.
- Connector by Coupler.io – Coupler.io is a reporting automation platform that connects over 60 popular data sources to Tableau and other destinations. You can easily import data from a live JSON URL into Tableau and also automate data refreshes. The setup is straightforward, requiring no coding, and can be completed in under ten minutes.
- Custom script – For advanced users, you can build a live data pipeline to import JSON into Tableau directly or via an intermediary. This requires a solid understanding of coding and data architectures. Therefore, it’s not suitable for non-tech-savvy users.
How to connect JSON to Tableau & automate imports using Coupler.io?
It’s easy to import data from any live API or URL with Coupler.io. You can set up the automated data importer in three easy steps:
Step 1: Set up JSON source
To connect your JSON data source, click Proceed in the form below:
You will need to sign up for Coupler.io for free (no credit card is required) and configure the data source.
Enter the URL of your JSON file. You can also enter the API URL and select the HTTP method. (In most cases, it will be GET for static files)
If required, you can specify the headers to include in the request. For example, an authorization token and content type. For publicly available URLs, you can keep these fields blank.
Specify the URL query parameters for the request.
You can also use macros here to interpolate date and time values. For example, you can have a parameter value like {{today}}, which passes the current date.
Optionally, specify the columns and path that you want to export from the JSON file.
If you wish, you can connect other data sources to the same importer and merge the data in the next steps. For example, it is helpful to combine the same type of JSON files from multiple URLs.
Step 2: Preview & transform data
Now, a snapshot of imported data will be displayed. It helps you review the data and perform modifications if required.
Here, you can:
- Hide or rearrange columns
- Modify column name & data type
- Filter or sort data
- Aggregate data to calculate the sum or average
- Create new calculated columns using formulas
In the case of multiple data sources, you can also merge them into one source.
Once the data looks good, proceed to the final step.
Step 3: Load data & schedule imports
In the destination settings, generate an integration link for the importer and copy it.
Now, open the Tableau Desktop application, go to Data Sources, and select Web Data Connector from the list. Enter the following in the URL field:
This will open the Coupler.io web data connector interface, where you can paste your integration link and import the data.
Now, you can use this JSON data to build reports in Tableau.
To update this data automatically, enable the automatic data refresh option in the importer and set up a schedule. For example, you can choose the interval (as often as 15 minutes), days of the week, and time for the refresh.
Save the importer, and it will automatically update data from JSON into the Tableau report regularly.
In many cases, instead of using JSON API, you will also find a direct integration for the data source in Coupler.io. The platform offers connectors for over 60 popular apps and services, including CRM, e-commerce, marketing, PPC, social media, and sales platforms. You can easily connect these platforms and perform automated imports from JSON to Looker Studio, Tableau, Power BI, Google Sheets, Excel, and other destinations.
Manual method to import JSON file
The JSON data source is suitable when you want to build reports locally from files stored on your computer. However, you can not directly use it to import JSON into Tableau from URLs; instead, you’ll have to manually download the files and then import them.
Launch Tableau Desktop and go to the data sources interface. Select JSON file from the list and select the file to import.
Now, select the schema levels up to which you want to import data from the JSON data file. This is helpful when you want to omit nested metrics and dimensions.
If you want to import the entire file, select “all” and click OK.
Now, the JSON dataset will be imported into Tableau. You can use it in your workbook for data visualization and reports.
Tableau offers a Live connection option for JSON files, but it’s only meant for local files. This means that when you publish the report to the Tableau server, the data refresh through live connections will not work.
Custom script to import JSON data from URLs
Building a custom data pipeline is a viable option for technically advanced users who need more control. You can code and deploy a program that imports data from JSON API or URL into a supported format.
There are multiple approaches to this:
- JSON > Hyper File – Convert the JSON data into .hyper files, which can be published to the Tableau server via API.
- JSON > Intermediary > Tableau – Save the data into a database or warehouse, where you can perform operations like merging it with other sources and cleaning. Later, you can use it to import JSON into Tableau via the native connector.
Let’s go through a Python script tutorial to connect JSON to Tableau via MySQL as an intermediary.
First, install the required Python libraries using this command:
pip install mysql-connector-python requests pandas
Next, write a Python script to load JSON data and upload it to MySQL. For example, here’s the script I used to upload my JSON data to a database. If you’re using it, make sure to replace the DB configuration and file path.
import requests
import mysql.connector
import pandas as pd
from mysql.connector import Error
db_config = {
"host": "<DB_HOST>",
"port": <DB_PORT>,
"database": "<DB_NAME>",
"user": "<DB_USER>",
"password": "<DB_PASSWORD>"
}
def fetch_json_data(url):
response = requests.get(url)
response.raise_for_status()
return response.json()
def transform_data(json_data):
return pd.json_normalize(json_data['posts']) # adjust for the structure of your JSON
def insert_data_to_mysql(dataframe):
try:
connection = mysql.connector.connect(**db_config)
if connection.is_connected():
cursor = connection.cursor()
drop_table_query = "DROP TABLE IF EXISTS posts;"
cursor.execute(drop_table_query)
create_table_query = """
CREATE TABLE posts (
id INT PRIMARY KEY,
title VARCHAR(255),
body TEXT,
userId INT
);
"""
cursor.execute(create_table_query)
for _, row in dataframe.iterrows():
insert_query = """
INSERT INTO posts (id, title, body, userId)
VALUES (%s, %s, %s, %s)
"""
cursor.execute(insert_query, (row['id'], row['title'], row['body'], row['userId']))
connection.commit()
except Error as e:
print(f"Error parsing json or inserting data into My SQL: {e}")
finally:
if 'connection' in locals() and connection.is_connected():
cursor.close()
connection.close()
def load_json_to_mysql():
url = "<JSON_API_URL>"
json_data = fetch_json_data(url)
dataframe = transform_data(json_data)
insert_data_to_mysql(dataframe)
if __name__ == "__main__":
load_json_to_mysql()
On each subsequent run, it will replace the MySQL table with fresh data.
In Tableau, use the MySQL connector to load this table and start visualizing your data.
crontab -e
Create a new entry for the script using cron syntax. For example, the following will run the script every day at 2 AM:
0 2 * * * /usr/bin/python3 /path/to/script/load_json_to_mysql.py
Further, you’ll need to configure the MySQL connection in Tableau to perform periodic refreshes to ensure real-time data analytics. This was just a basic example; your actual scenario can be much more complex depending on your integration needs. In addition to development hours, this can also cost you additional server hosting and maintenance charges.
What’s the best way to import JSON into Tableau?
Manually importing data is suitable for building one-off reports that don’t require data refreshes. However, for dynamic data sources like JSON URLs and APIs, you should think of other approaches that support automatic live data updates.
While you can build a custom data pipeline to connect JSON to Tableau, it is a time-consuming process. Further, it can turn into a complex project if you want to perform data transformations and automation.
Coupler.io offers the easiest option to load JSON data from any URL or API endpoint into Tableau, complete with automatic updates. The platform also offers powerful data transformation options to modify the data before importing it. Furthermore, it connects to other popular data destinations like spreadsheets, BI tools, and data warehouses.
Automate data flows with Coupler.io
Get started for free
