A data warehouse such as Google BigQuery, or Snowflake, is used to consolidate data from multiple sources in a single location. Data can then be analyzed using a BI tool such as Looker. Looker and many other BI tools discover patterns and help visualize data to make it more understandable and boost the analysis process. These help in gaining a better picture of the metrics, trends, user needs, and performance, and hence play a key role in various data-driven decisions.
This article aims at answering all your queries about connecting BigQuery to Looker. Follow our easy step-by-step guide to master the skill of connecting BigQuery to Looker using various methods.
Table of Contents
Introduction to BigQuery
It is Google Cloud Platform’s enterprise data warehouse for analytics. BigQuery performs exceptionally even while analyzing huge amounts of data & quickly meets your Big Data processing requirements with offerings such as exabyte-scale storage and petabyte-scale SQL queries. It is a serverless Software as a Service (SaaS) application that supports querying using ANSI SQL & houses machine learning capabilities.
Some key features of BigQuery:
- Scalability: BigQuery offers true scalability and consistent performance using its massively parallel computing and secure storage engine.
- Data ingestion formats: BigQuery allows users to load data in various formats such as AVRO, CSV, JSON, etc.
- Built-in AI & ML: IIt supports predictive analysis using its auto ML tables feature, a codeless interface that helps develop models having best-in-class accuracy. BigQuery ML is another feature that supports algorithms such as K means, Logistic Regression, etc.
- Parallel Processing: It uses a cloud-based parallel query processing engine that reads data from thousands of disks at the same time.
Introduction to Looker
Looker is a cloud-based business intelligence tool that lets you analyze data in real time. It helps you understand the customer trends, metrics, and how your product is performing in the market.
Rich visualizations such as charts, graphs, etc, can be created using its interactive dashboard. Using Looker’s data modeling language, you can define performance metrics and get an insight into the relationship between various data sets.
For further information on Looker, you can click here.
Hevo Data, a No-code Data Pipeline helps to transfer data from multiple sources to BigQuery and visualize it in Looker. Hevo is fully-managed and completely automates the process of not only exporting data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.
It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using BI tools such as Looker and many more.
Visit our Website to Explore Hevo
Some stand-out features of Hevo:
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
- 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.
- Completely Managed Platform: Hevo is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to export.
Simplify your data analysis with Hevo today! Sign up here for a 14-day Free Trial Today!
Prerequisites
- Working knowledge of BigQuery.
- Working knowledge of Looker.
- A Looker account.
- A BigQuery project.
Methods to connect BigQuery to Looker
There are multiple ways in which you can connect BigQuery with Looker:
Method 1: Connecting to Google BigQuery standard SQL or legacy SQL
You can establish a connection between BigQuery and Looker using the following steps:
Step 1: Creating a service account
To create a service account, you must have admin permission for Google Cloud. Ensure you have these permissions before proceeding further.
Open the Credentials page using the Google Cloud Platform API Manager. Now select an existing project if you have created one in the past otherwise proceed further.
Click on the create credentials option and select the account type as service.
Provide a name and description for the newly created service account. Once you have filled in the details, click on the create button.
Service account requires selecting either of the two predefined roles as a data editor or a job user. Select the Google BigQuery data editor role as the first option and select the second role as the Google BigQuery job user. Click on continue.
Click on create key option located at the bottom of the page to generate the private key.
Download the key and save it on your system carefully. Select the file type as JSON.
Step 2: Create a temporary data-set to store persistent derived tables
To set-up, up a temporary data set, open the Google Cloud BigQuery console and select the project you have created. Click on Create a new dataset option.
A new dialogue box will now open up, where you need to provide the dataset ID and location.
Click on the create dataset button.
Step 3: Connecting BigQuery to Looker
Open Looker and navigate to the admin section. Under the admin section, go to the Connections page and click on the new connection option.
Fill the following details carefully:
- Dialect: Select Google BigQuery Standard SQL or Google BigQuery Legacy SQL.
- Project Name: Provide the project ID here.
- Dataset: Enter the name of the dataset you want to use. This should be same as the dataset in BigQuery.
- OAuth: This enables every Looker user to authenticate BigQuery and run queries on the linked database.
- Service Account Email: Enter the email id you used to create the service account.
- Service Account JSON/P12 File: Upload the certificate file either in JSON or P12 format.
- Password: If you’re using a JSON certificate, you can leave the password field empty otherwise provide the credentials for the P12 file.
- Temp Dataset: Enter the temporary dataset you created to store the PDT.
- Max Billing Gigabytes: You can use the default value.
- Max Connections: You can use the default value.
- Connection Pool Timeout: You can use the default value.
- SQL Runner Precache: If you don’t want SQL runner to preload information in your tables, you can uncheck this option.
- Database Time Zone: Set the same time zones for both BigQuery and Looker.
Click on the update connection button. This is how you can connect BigQuery to Looker.
Method 2: Using OAuth API to connect BigQuery to Looker
Looker supports using the OAuth API to establish a connection with BigQuery. The OAuth API provides database administrators with the following abilities:
- To monitor which Looker users are running queries against the database.
- Provide role-based access using Google Permissions.
- Make use of OAuth tokens for all processes that access BigQuery.
For more information about OAuth, you can check the official documentation here.
OAuth can be used to connect BigQuery to Looker using the following steps:
Step 1: Setting up OAuth credentials
Open Google Cloud Platform Console. Use the projects drop-down list to select your BigQuery project.
Once you have selected your project, go to the API & Services option present on the left. Click on credentials option from the panel and create a new credential. Select the OAuth client ID option.
A new page called create OAuth client ID will now show up. Create the client ID and provide the necessary details to set up the connection.
Fill in the following details carefully:
- Application Type: Select the application type as Web Application and provide a name for the application.
- Authorized JavaScript Origins: Enter the URL for your Looker instance in this field. You can provide the URL in the following ways:
- If Looker hosts your instance: https://<instancename>.looker.com
- If you have a customer-hosted Looker instance: https://looker.<mycompany>.com
- If your Looker instance uses a port number: https://looker.<mycompany>.com:9999
- Authorized redirect URIs: Enter the URL for your Looker instance as follows: https://.looker.com/external_oauth/redirect or https://looker..com:9999/external_oauth/redirect
Click on Connect. Safely keep your clientID and client secret values.
Step 2: Configuring OAuth consent screen
The OAuth consent screen lets users grant access to their data. Select the OAuth consent screen from the panel on the left and choose the kind of user to whom you’re making this application available.
The consent screen will now show up as follows:
Fill in the following details carefully:
- Application Name: Enter the name of the application to which you’re granting access.
- Support Email: Enter an email on which users can send in their queries.
- Authorized Domains: Enter the domain of the URL for your looker instance.
For further information on the OAuth screen, you can check the official Google documentation here.
Step 3: Establishing a connection
Open Looker and select the BigQuery Configurations page. You can do this by selecting the connections page found under the admin panel.
Check the OAuth box. Provide the clientID and client secret values in the dialogue box.
You can now connect your database to Looker, and follow the procedure mentioned in the official documentation here. Test the connection by following the steps mentioned here.
This is how you can connect BigQuery to Looker using OAuth API.
Conclusion
This article introduces you to the various methods that can be used to connect BigQuery to Looker. It also provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently. These methods, however, can be challenging, especially for a beginner & this is where Hevo saves the day. Hevo Data, a No-code Data Pipeline helps to transfer data from multiple sources to BigQuery and visualize it in Looker without having to write the code repeatedly. Hevo with its strong integration with 150+ sources & BI tools allows you to not only export & load data but also transform & enrich your data to make it analysis-ready in a jiff.
If you are interested in learning about connecting Snowflake with Looker, you can find the guide here, and a guide about connecting Amazon Redshift to Looker can be found here.
Want to take Hevo for a spin? Sign Up for the 14-day free trial! and experience the feature-rich Hevo suite first hand.
Tell us about your preferred way to connect BigQuery to Looker! Share your thoughts with us in the comments section below.
In his role as a freelance writer, Muhammad loves to use his analytical mindset and a problem-solving ability to help businesses solve problems by offering extensively researched content.