Calling all data teams and IT professionals. Raise your hand if you’ve ever thought…
- “My business teams have some queries against Redshift, but I don’t want to do them every day.”
- “I don’t want to give my business teams access to Redshift to run their own queries.”
- “I can easily prepare the SQL for them to run elsewhere, but I’m not sure where to store or send them.”
You need a single source of truth for all your data, but, sometimes, this “single source” receives multiple query requests from your less-than-data-savvy business teams.
Many data organizations struggle to truly democratize their data access without running the risk of security breaches or accidental deletions. Moreover, Amazon Redshift queries don’t always run quickly, nor does it have a native scheduled query function.
If you want to better control data governance and automate ad hoc requests from your business team, keep reading. Kloudio allows you to auto-refresh your team’s Sheets documents (instead of giving them access to Redshift) so they don’t bug you time and time again with ad hoc queries.
You or your business team can schedule these reports to run on a daily, weekly, or monthly basis. They can save a pre-written SQL and access their reports via the Kloudio Google Sheets add-on, and refresh the data within the spreadsheet.
Let’s say a user in Finance Operations needs to run a fresh month-end close report. They can open Kloudio, find the report created via SQL (with Kloudio’s Ad Hoc Query feature), and auto-refresh the tab with their report—without having to access Redshift or ping you with requests.
It’s a win-win: you reclaim your time and empower your team, and they trust the accuracy and freshness of your data.
How to Connect Redshift to Google Sheets
Kloudio helps with pre-processing data and eliminates the need for your data engineers to generate commonly-used reports.
But, what if you already know or have a SQL query? Sure, you can go to your Kloudio portal and create a report, but our goal is to help reduce friction and eliminate steps to get you the data you need to start making business decisions.
That’s why we’ve expanded our Google Sheets add-on with the ability to run ad hoc queries directly within Sheets. Let’s take a look at setting it up.
1. Set up your Redshift connection.
In the Kloudio Portal, click Connections > New Connection. Then, scroll down until you see Amazon Redshift and click Setup.
A window will pop up on the right with the information needed to connect your Redshift database. It’s important to note that you need to allow the IP addresses access on the port that we want to use.
If you want, you can share your connection. Select Users, Team, or Email, and if desired, change the User permissions, as well.
2. Open Google Sheets and access the Kloudio Sheets add-on.
Open a Google Sheets document. Click Add-ons > Kloudio > Adhoc Query. (If you don’t have the Kloudio add-on for Google Sheets, click Get add-ons and search for “Kloudio.”)
Once you open the Kloudio add-on you can do one of two things:
- Use the schema browser to select from a Connection, Schema, and Table to build your query, or
- If you happen to know the SQL query, paste it directly in the Editor
Either way, you can click on Save as Report to save these queries as reports to run later or share with teammates. It’s important to note that, by default, this will run in a new tab. However, if you deselect it, it will run in the current tab starting at the selected cell.
The Kloudio Ad Hoc Query functionality simplifies the process of bringing your data into Google Sheets—and not just from a single query. You can run multiple queries against one or multiple data sources.
3. Automate your ad hoc query.
Once you save your report, you can schedule automated refreshes. Under the Schedule add-on window, you can set your report to auto-refresh in the Sheets tab. You can also send yourself an email every time it refreshes so you remember to check your report.
This ensures you always have the most up-to-date data and share your report with others—effectively providing a consistent single source of truth for your team.
Now, you’re ready to format your spreadsheet and begin inspecting data, creating pivot tables, or running any other standard Google Sheets operation.
Over to You
Data teams and IT professionals—no longer are you bound to ad hoc queries at the whim of your business teams, nor do you have to open your Redshift data warehouse at the expense of your data security. Kloudio solves both of these issues and allows you to truly democratize the data access among your organization. Create a free Kloudio account to set up your Amazon Redshift and Google Sheets connection.