Do you want to connect Redshift to Looker? If yes, then you have landed on the right blog. In this post, you will learn about how to connect Redshift to Looker in 8 easy steps.
Redshift is a very popular cloud-based data warehouse service that is being used by startups, medium businesses as well as enterprises. Looker is a business intelligence tool that helps organizations to develop custom dashboards and charts. Looker spares enterprises from the efforts to create a custom reporting dashboard for their business analysts. Looker can connect to various databases, fetch data, and enable the decision-makers to unearth valuable business insights. Looker provides options for defining and scheduling reports based on the periodic execution of defined logic. Looker accomplishes this through a custom domain-specific language called LookML. While being used as a completely managed service, Looker can create a secure encrypted channel with an organization’s on-premise databases if the need arises.
For further information on Looker, you can click here.
Let’s see what you will cover here:
Prerequisites
- A looker account with admin privileges.
- An AWS Redshift account with IAM permissions to create tables.
- Basic understanding of data warehouses and BI tools.
Hevo is a No-code Data Pipeline. It provides an easy to use interface that can work with a variety of integrations and data warehouse solutions. Hevo supports complex transformations on the fly. Hevo’s simple user interface and easy configuration ensure the lowest time for your ETL. You can use Hevo Data Pipelines to get data into Redshift and simplify the process of data analysis in your desired BI tool.
Get Started with Hevo for Free
Let’s check some amazing features of Hevo:
- Simple: Hevo offers a simple and intuitive user interface. The learning curve of Hevo is minimal.
- Secure: Hevo makes sure that your data is safe and secure by offering two-factor authentication and end-to-end encryption.
- Fault-Tolerant: Hevo finds anomalies in your incoming data and informs you instantly. If there is any affected row, it is kept aside.
- Live Support: Hevo team offers support 24×7 through emails, chats, and calls.
- Zero Maintenance: Set-up once, and Hevo manages all the future changes automatically.
Sign up here for a 14-Day Free Trial!
Steps To Connect Redshift To Looker
Configuring Looker to use the data from Redshift involves two tasks:
- Creating and setting up users and permissions in Redshift for Looker access.
- Adding the connection details in Looker Developer Admin.
You will begin this process by setting up the users and permissions in Redshift.
Step 1: Log in to Redshift shell and create a ‘looker user’.
CREATE USER looker_user WITH PASSWORD 'looker_password';
Step 2: Grant the appropriate privileges for looker user.
GRANT USAGE ON SCHEMA public TO looker;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO looker;
For the uninitiated, ‘public’ is the name of the default schema in Redshift. In the above command, you have granted access to all the tables for this schema to looker users.
Step 3: Give access to ‘information_schema’ to the looker user. This schema is used by Redshift internally to keep track of tables and columns. Looker will need this to list the tables and their columns for the developer while creating reports and dashboards.
GRANT SELECT ON TABLE information_schema.tables TO looker;
GRANT SELECT ON TABLE information_schema.columns TO looker;
Step 4: Set the search path for the looker user. It is required for the looker user to get metadata about the database.
ALTER USER looker SET search_path TO '$user',public;
Add all the schema names that you will be using from the looker dashboard here.
Step 5: The next step is to log in to the Looker dashboard as a user with admin privileges and add Redshift connection details. Go to ‘Admin’, Click ‘Connections’ as shown in the image below.
Image Source: Ascend Developer Hub
Step 6: In the ‘Connections’ tab, click ‘New Connection’.
Step 7: The next step is to add connection details. Choose Redshift as the dialect. Add the Redshift host and port details and then the details of the looker user that we created earlier.
Image Source: Dremio
In case you want Looker to create tables and store the reports, click ‘Persistent Derived Tables’. For now, keep max connections at 75 and connection pool at 120. Click ‘Add Connection’ at the bottom.
Step 8: You can now go into ‘SQL Runner’ and select the connection name that you created in the left-hand side ‘Connection’ tab.
Image Source: Looker Documentation
That concludes the steps to connect Redshift to Looker. You can now type some queries in SQL Runner and verify the working of the connection.
Drawbacks
Connecting Redshift to Looker with the above method has the following drawbacks:
- As evident, this method involves a lot of configurations and permission setup, which can be tedious for a developer to execute.
- Using Looker effectively will require data to be transformed into Redshift. It can be done using Persistent Derived tables, but building such tables is an effort that needs a lot of thought and planning.
- It needs the Looker developers to be comfortable with Redshift’s query syntax and related terms. Since Looker is mostly used by business users who are not tech-savvy, it will require separate effort from a developer with Redshift expertise.
Conclusion
In this blog, you have learned how to connect Amazon Redshift to Looker in 8 easy steps. Even though we have numerous BI tools available in the market, Looker is one of the best tools. But if you have to consolidate data from multiple sources before you opt for analysis, then you can use Hevo.
Visit our Website to Explore Hevo
Hevo, a No-code Data Pipeline can be used to get data into Redshift for simplifying the process of data analysis in Looker. Hevo supports pre-built data integrations from 100+ data sources at a reasonable price.
If you are interested in learning about connecting Google BigQuery with Looker, you can find the guide here, and a guide about connecting Snowflake to Looker can be found here.
Give Hevo a try by and Sign Up for a 14-day free trial today.
Let’s know about your experience of connecting Redshift to Looker in the comment section below.