The combination of Google BigQuery and Looker is one of our favorites when it comes to setting up a solid data warehouse and business intelligence foundation for your business. BigQuery’s robust data warehouse capabilities, paired with Looker’s interactive data visualization tools, offer businesses a best-in-class opportunity to glean actionable insights from their data. This guide walks you through the process of connecting your BigQuery database to Looker and outlines essential tips and steps for setting up your first view, model, and explore in Looker so you can hit the ground running and start making actionable decisions with your data at scale.
Connecting BigQuery to Looker
Before getting started, ensure you have these prerequisites:
- Admin privileges in Looker.
- Access to your BigQuery project.
- BigQuery’s API enabled in your Google Cloud Project.
Setting Up the Connection
- In Looker, navigate to the ‘Admin’ section and select ‘Connections’.
- Choose ‘Add Connection’ and select ‘BigQuery’.
Fill in the connection details:
- Name: Assign a name to this connection.
- Connection Scope: We typically select ‘All’ so that any project you create in Looker will be able to access the database connection you are setting up. You may consider selecting a single project if that is necessary for permissioning purposes.
- Dialect: Google BigQuery Standard SQL
After selecting your dialect, several more options will pop up:
- Billing Project ID: This is the ID of your Google Cloud Project that can be seen when you click ‘Select a Project’ in the top left hand corner.
- Dataset: This is the name of the dataset in BigQuery. A dataset may be called a ‘schema’ in other warehousing or SQL database setups. It’s what contains the tables you are trying to access.
- Authentication: We typically use the ‘Service Account’ setting.
- You can follow these instructions to create a service account in Google Cloud. We typically provide the service account with BigQuery Admin access to ensure all necessary roles are covered.
- After the service account has been created, click on the service account you have created, click on ‘Keys’ in the top menu bar, click ‘Add Key’, select ‘JSON’ type and then download the file that is produced.
- Upload this file to the Looker connection settings screen.
Test Your Connection
- There are a few more optional settings you can implement. I won’t cover all, but will cover the most important ones that I’ve used:
- Persistent Derived Tables (PDTs) — This is a good article on the pros and cons of using PDTs in your setup.
Time Zone Settings
This one can be a bit tricky. If your database is set to UTC, but the actual data loaded into the database is in another time zone, you’ll want your query time zone to also be UTC. If your database is UTC and the data itself is also in UTC format, but you’d like to be able to view the data in a given time zone, then it makes sense to set the query time zone to your preferred time zone.
- Database Time Zone: This is typically UTC unless you specified a time zone in your data warehouse setup.
- Query Time Zone: This is the time zone query results are converted to if user time zone settings are disabled (another admin settings feature).
Once happy with all settings, save your connection
Create a Looker Project
- The first thing you’ll want to do after creating your database connection is create your Looker project (sometimes you will have done this before creating the database connection). (Develop -> Projects -> New LookML Project)
- What’s nice about creating a project after you’ve built your database connection is that you can auto-generate a Looker model from your database schema and you can select to build views from all tables or a single table if desired.
- Looker makes this part of the process super straightforward — once you’ve created your project, you’re ready to build a model & explore.
Building Your First View in Looker
Create a New View
- In the LookML (Looker’s modeling language) editor, start by creating a new view file.
- Click the + icon to add a new view.
- Select ‘Create View’
- Define your view using LookML syntax, which maps to your BigQuery table’s schema.
- Add dimensions and measures based on your table columns.
- Test Your View
- Validate the LookML code.
- Preview the data in the Explore section.
When we’re first getting started with creating a new view, there are a couple of items we typically look out for / try to implement to make our data as usable as possible.
- Make sure all dimensions are labeled as dimensions, and that all fields that will have calculations run on them are labeled as measures.
- Leverage dimension groups where possible for your primary date field so that you can slice your data in several different ways.
- We also recommend implementing dynamic date granularity parameters, which come in handy when developing dashboards that would be useful to quickly see across different time horizons and granularities. See here for more detail.
Developing a Model in Looker
- Models in Looker represent a project’s data structure.
- Once you’ve developed views from the data you have connected, you can add views to the model and define how they join with each other for more detailed analysis.
- You can create relationships between views using joins and you’ll define your explores in your model file.
Crafting Your First Explore in Looker
- Explores in Looker are a place where users can query data models that you have developed in LookML.
- Once you have defined an explore in your Looker model, your Looker instance’s users can analyze company data at will. There’s a lot to learn about how to effectively use explores to analyze and visualize your data, but Looker is very intuitive for end users once you get to this step.
Integrating BigQuery with Looker offers a seamless, efficient, and powerful approach to data analytics. After getting your Looker instance connected to BigQuery, you’ll be well on your way to unlocking valuable insights from your data. If at any time you have any questions or need any support on setting up your BigQuery and Looker instances, please reach out to us at nick@southshore.llc and james@southshore.llc. You can also find us at our website at southshore.llc.
About Us
South Shore Analytics (SSA) is an Analytics Consulting Firm, co-founded by James Burke and Nick Lisauskas, both highly skilled professionals with more than a decade of invaluable experience in the field of Analytics. Their shared passion for data-driven insights and business optimization led them to establish SSA, aiming to provide top-notch services to various businesses, irrespective of their size or stage of development.