Visualizing Data From Files in Metabase Without Coding
In this article, I’ll show you a simpler way of how you can quickly visualize your data from any static MS Excel, JSON, XML files, or GoogleSheets in Metabase.
Metabase is compatible with a variety of databases and data warehouses, so it’s the perfect tool for data professionals who want to get the most out of their data. Obviously, to take full advantage of Metabase’s features, it’s important to have your data stored in a relational database.
Unfortunately, in real life, data may come to us in many different formats other than properly organized databases. We often have to deal with thousands of unstructured, messy files that must first be prepared before they can be analyzed in BI software.
Here’s where TABLUM.IO SaaS comes in. With just a few clicks, you can ingest any file, stored remotely or locally, into TABLUM.IO, which automatically transforms it into a SQL database. Then, you can easily share ingested data with Metabase using standard ClickHouse interface, and create your dashboard with ease. It’s that simple!
I’m going to show you how to streamline your data analytics workflow and get you quickly from raw flat files to gorgeous dashboards created with Metabase.
For those who prefer watching, there is a video below which shows the step-by-step process of how to get the job done.
This image illustrates the process of what we are about to do:
- Importing data from a data source into TABLUM.IO to turn files into a relational database.
- Connecting Metabase to TABLUM.IO account via a ClickHouse connector.
- Visualizing your data in Metabase.
Data Import (Data Ingestion)
To import data from a file into TABLUM.IO, you can use the “copy-paste,” “drag-and-drop,” or “URL Downloader” options. In this example, we’ll utilize the Universal GoogleSheets Connector named “My Universal GoogleSheet Connector”(*) to import data from a Google Sheets table:
* You need to create it first under the “Connectors” tab.
Once the data import is complete, you will have a fully-featured SQL table ready for analysis and further manipulation. Automatically, all the essential data preparation operations such as data transformation, data type recognition, data cleansing, and data normalization will be accomplished “on the fly” (while the data is being imported). You don’t need to set up a separate database, as TABLUM.IO, powered by ClickHouse, provides you with one by default. Additionally, the data schema is automatically generated based on the information in the source file.
Connecting Metabase to TABLUM.IO (ClickHouse)
Now that the data has been ingested, let’s share it via the ClickHouse interface. Go to the “Ingested Data” tab and click the “Enable DB Sharing” button:
The ClickHouse connection parameters:
- Host: go.tablum.io
- Port: 8443
- User: username
- Password: password
- Database: name
Next, head to your Metabase account to connect it to TABLUM.IO. In the Admin Settings, select “Databases” and then “Add a Database”. From the dropdown menu, choose “ClickHouse” to input TABLUM.IO (ClickHouse) credentials. Finally, synchronize the data:
Now, Metabase can access the data stored in TABLUM.IO and visualize it, giving you the power to gain deeper insights into your data:
Let’s dive deeper into the details of our newly created dataset by navigating to the “Browse Data” section in Metabase. Here you can notice that data type recognition was done automatically by TABLUM.IO without any extra input from the user. TABLUM.IO accurately identified all the data types, such as float, integer, text, and date&time. The data schema was generated automatically, making it easy to quickly go from a raw file to structured data ready for analysis:
Let’s head back to our TABLUM.IO account to add one more new dataset. This time, we’ll be working with a MS Excel file saved locally. To ingest the file into TABLUM.IO, click the dropdown menu and select the “Import from Files and Clipboard” option:
Use the drag-and-drop function to import the MS Excel file into TABLUM.IO. Once the file is ingested, TABLUM.IO will provide you with a SQL table in a relational database format that is ready for import into Metabase:
Now we have two datasets in our TABLUM.IO account:
Let’s go back to Metabase to force it to refresh our database content:
The synchronization process is complete, and we can now access the data stored in the TABLUM.IO database through Metabase to create stunning dashboards:
Key Points
TABLUM.IO can drastically boost your productivity as a data engineer or data analyst when it comes to transforming any raw data file into a SQL database.
It eliminates the need to spend time implementing a programming solution to parse, load and cleanse data from CSV, TSV, JSON, or XML files or Google Sheet, making it ready for analysis and visualization in seconds.
Additionally, importing multiple flat files into TABLUM.IO eliminates the need to configure various 3rd-party connectors in Metabase and prepare the data beforehand. TABLUM.IO enhance the capabilities of Metabase regarding unstructured data usage.
Now it's your turn to try ingesting your flat files to TABLUM.IO and visualizing your data with Metabase! See how quickly and easily you can make sense of your data!
Now it's your turn to try ingesting your flat files to TABLUM.IO and visualizing your data with Metabase! See how quickly and easily you can make sense of your data!
