Connect BigQuery to Looker: 2 Easy Methods

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.

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.

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
• Method 2: Using OAuth API to connect BigQuery to 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
• Step 2: Create a temporary data-set to store persistent derived tables
• Step 3: Connecting BigQuery to Looker

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.

OAuth can be used to connect BigQuery to Looker using the following steps:

• Step 1: Setting up Google OAuth credentials
• Step 2: Configuring OAuth consent screen
• Step 3: Establishing a connection

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.

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. Follow the procedure and test your connection.

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.

You can also integrate Looker with Snowflake and Redshift.

FAQ on BigQuery to Looker

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.