Many of the world’s largest companies now use SQL, often integrating additional BI and Analytics apps, as well as machine learning tools in the mix. This allows companies to get greater visualization and a more comprehensive understanding of their overall business performance, including revenue forecasts, customer churn predictions, and other details.
Metabase allows users to visualize data and share it with the rest of their team. Connecting Redshift DB to Metabase is not very complicated, and it’s a simple process primarily because Redshift is one of the officially supported databases by Metabase. If you want greater clarity over your data, you should consider Redshift Metabase Integration. Read along to gain insights into how you can set up Redshift Metabase Integration.
Table of Contents
Introduction to Redshift and Metabase
Image Source
Amazon Redshift is one of the most popular Data Warehouse offerings in the world right now. The Data Warehouse is a part of Amazon Web Services, arguably the biggest name in the Cloud Computing sector right now. It’s a fully managed Data Warehouse that utilizes the power of SQL to easily analyze both semi-structured and structured data across operational Databases, Lakes, and Data Warehouses.
Image Source
One of the most popular Business Intelligence tools that companies use to get a better understanding of their data is Metabase. Metabase is an open-source Business Intelligence tool that you can connect with Amazon Redshift to easily analyze your data from a myriad of different sources.
It features unparalleled data visualization options, allowing Data Analysts to easily present and visualize data in a variety of different ways. Above all, it doesn’t use SQL at all, so you don’t need professionals with an understanding of a popular programming language.
Hevo Data, a No-code Data Pipeline, helps load data from any data source such as Databases, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports 100+ Data Sources including Metabase and other 40+ Free Sources. It loads the data onto the desired Data Warehouse/destination such as Amazon Redshift and transforms it into an analysis-ready form without having to write a single line of code.
Hevo’s fault-tolerant and scalable architecture ensures that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data.
GET STARTED WITH HEVO FOR FREE
Check out why Hevo is the Best:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled securely and consistently with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Simplify your ETL & Data Analysis with Hevo today!
SIGN UP HERE FOR A 14-DAY FREE TRIAL!
Steps to Work with Redshift Metabase Integration
Now that you have an overview of Redshift and Metabase, let’s discuss the steps to set up the Redshift Metabase Integration. Follow the steps below to work with Redshift Metabase Integration:
Step 1: Connect Redshift to Metabase
When you first log into Metabase, click on the gears icon on the top right, and select the Admin panel. Then, just follow the steps given below:
- Select the tab that says Databases from the top of the navigation bar.
- You will then see a list of all the databases that are connected to Metabase.
- Since you want to add Redshift, click on Add Database.
- Simply follow the connection instructions specified on-screen (and discussed below) to establish a connection.
- Ideally, you are going to require some specific information from the database admin, including the hostname, the port, the database username, password, and you will also have to specify the name of the database.
Step 2: Select a Suitable Database Connection Option
The first step is to select a suitable database connection option. This is the way that Metabase will connect with your Redshift Data Warehouse, so it’s important to select the right option. Metabase gives you four different choices.
Step 3: Perform SSH Tunneling
You can use SSH tunneling to connect to certain databases, starting by connecting a server to your Data Warehouse and Metabase. Then, you use that connection as a bridge in order to establish a connection between Metabase and the Data Warehouse. Ideally, you should consider using SSH tunneling when a direct connection is simply not possible, or when the company policy states that direct connections are not allowed.
In certain organizations, especially those that operate in an enterprise environment, direct connections to Data Warehouses are forbidden. That is one of the reasons why Metabase gives you the option of using SSH tunneling to establish a connection indirectly.
VPNs are generally the preferred choice, though in many cases, you can also use a bastion host, especially with Redshift which bans direct connections due to VPC.
Step 4: Establish a Secure SSL Connection
The default option is to use the SSL connection with your Redshift database. When you set up Metabase, it’ll try to connect to a database using SSL. If that fails, then the app will try to connect without it. If you can establish an SSL connection, Metabase will automatically record this and set it as the default setting.
If you do not want to use SSL, you always have the option of changing the setting whenever you want.
Step 5: Select the Syncing Times and Frequency
Metabase has a built-in scanner that runs after every hour. It scans all lightweight data after one hour and a more intensive scan at least once a day to update all field values. However, if you have a larger database with petabytes of data, Metabase also lets you decide whenever you want to sync and scan.
This way, you will know when the field values scan. If you select this setting, you will see a tab at the top that says “Scheduling”. You can then set a schedule and Metabase will automatically take that into account when running scans.
Step 6: Sync Databases
Metabase maintains its own set of tables and fields and all the information inside to run queries quickly. Metabase runs the sync on an hourly basis and then brings over any changes to the new tables or fields. It’s important to understand that Metabase does not copy data directly from the database.
Instead, it simply manages and maintains its own list of columns and tables. You can decide how frequently you want to sync, ranging from hourly syncs to once a day. You cannot obviously turn syncing off entirely, because that renders Metabase useless.
If you want to set up Metabase to run a sync at a specific time, you just have to click on Databases from the Admin Panel. From there, you can decide when to sync the database schema, as shown below.
Image Source
When Metabase establishes the first connection to your database, it automatically assigns a specific type to the metadata in your columns. The application takes a sample of different tables to identify any encoded strings or JSON files. Of course, you can easily modify this metadata by going into the Data Model tab.
You can also decide when Metabase runs a more comprehensive sampling of the field values, and then create caches of the different values. Obviously, for larger databases, this means quite a bit of slowdown, especially if the samples are run on a daily basis.
Thankfully, Metabase does give you the option of selecting when you want to run the sampling. You can choose whether to run it regularly on a fixed schedule or specify so that it only runs when a new filter widget is added. Or, you can also choose to run it manually, so it’ll never give you a prompt on its own.
Step 7: Run Queries While Filtering and Summarizing
If you don’t tamper with any of the settings, Metabase will automatically run queries every time you use the Filter or the Summarize buttons when you go through a chart or a table. If the users are going through data that is stored in a particularly slow database, you may want to turn this potion off so that the query doesn’t run every single time an option is altered within the Summarize section.
How to Resolve a Time Out Error in Redshift Metabase Integration?
A common error that occurs when you try to connect Metabase to Redshift is the time-out error. This usually occurs when the user is entering the wrong host or the port isn’t input correctly. In some cases, the issue is caused by AWS’ VPC (Virtual Private Clouds), which does not allow for a direct connection.
Or, you may want to check if the firewall permissions have been updated or not. Make sure you whitelist the IP before you try to set up Redshift Metabase Integration.
Conclusion
In this article, you gained a basic understanding of Redshift and Metabase. You also learned the key steps to set up Redshift Metabase Integration. If your company has to pull data from multiple sources, you can easily connect it via Automated Pipelines such as Hevo. Hevo is a no-code data pipeline that allows businesses and organizations to seamlessly manage their connection between different destinations and sources.
Hevo is a fantastic platform for businesses that lets them load data quickly and reliably and then move it to a data warehouse such as Amazon Redshift. It also features integrations for more than 100 platforms, including Metabase. You can easily connect Metabase to your Redshift data warehouse with Hevo and manage the connection seamlessly with minimal hassle.
VISIT OUR WEBSITE TO EXPLORE HEVO
Want to take Hevo for a spin?
SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.
Feel free to share your experience with Redshift Metabase Integration with us in the comments section below!