Analyze BigQuery data by using BI Engine and Looker

BigQuery BI Engine lets you perform fast, low-latency analysis services and interactive analytics with reports and dashboards that are backed by BigQuery.

This introductory tutorial is intended for data analysts and business analysts who use the business intelligence (BI) tool Looker to build reports and dashboards.

Objectives

In this tutorial, you complete the following tasks:

  • Create a BI reservation and add capacity using the Google Cloud console.
  • Connect Looker to BigQuery through a service account.
  • Explore your data in Looker.

Costs

BigQuery BI Engine SQL interface pricing is identical to the pricing model described on the BI Engine pricing page.

Before you begin

Before you begin, ensure that you have a project to use, that you have enabled billing for that project, and that you have enabled the BigQuery API.

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project.

  4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  5. Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project.

  6. The BigQuery API is automatically enabled in new projects. To activate the BigQuery API in an existing project, go to

    Enable the BigQuery API.

    Enable the API

Create a BigQuery dataset

The first step is to create a BigQuery dataset to store your BI Engine-managed table. To create your dataset, follow these steps:

  1. In the Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the navigation panel, in the Explorer panel, click your project name.

  3. In the details panel, click View actions, and then click Create dataset.

  4. On the Create dataset page, do the following:

    • For Dataset ID, enter biengine_tutorial.
    • For Data location, choose us (multiple regions in United States), the multi-region location where public datasets are stored.

    • For this tutorial, you can select Enable table expiration, and then specify the number of days before the table expires.

      Create dataset page

  5. Leave all of the other default settings in place and click Create dataset.

Create a table by copying data from a public dataset

This tutorial uses a dataset available through the Google Cloud Public Dataset Program. Public datasets are datasets that BigQuery hosts for you to access and integrate into your applications.

In this section, you create a table by copying data from the San Francisco 311 service requests dataset. You can explore the dataset by using the Cloud console.

Create your table

To create your table, follow these steps:

  1. In the Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the Explorer panel, search for san_francisco_311.

  3. In the Explorer panel, expand san_francisco_311 and click the 311_service_requests table.

  4. In the Explorer toolbar, click Copy.

    Highlight of the copy option.

  5. In the Copy table dialog, in the Destination section, do the following:

    • For Project name, click Browse, and then select your project.
    • For Dataset name, select biengine_tutorial.
    • For Table name, enter 311_service_requests_copy.

      The copy table window with destination options

  6. Click Copy.

  7. Optional: After the copy job is complete, verify the table contents by expanding PROJECT_NAME > biengine_tutorial and clicking 311_service_requests_copy > Preview. Replace PROJECT_NAME with name of your Google Cloud project for this tutorial.

Create your BI Engine reservation

  1. In the Cloud console, under Administration go to the BI Engine page.

    Go to the BI Engine page

  2. Click Create reservation.

  3. On the Create reservation page, for Step 1:

    • Verify your project name.
    • Choose your location. The location should match the location of the datasets you are querying.
    • Adjust the slider to the amount of memory capacity you're reserving. The following example sets the capacity to 2 GB. The current maximum is 100 GB.

      BI Engine capacity location

  4. Click Next.

  5. For Step 2, review your reservation details, and then click Create.

After you confirm your reservation, the details are displayed on the Reservations page.

Confirmed reservation

Connect using Looker

The following instructions show you how to set up Looker with BigQuery.

  1. Log in to Looker as an administrator.
  2. In the Looker documentation about BigQuery, complete the following sections:

    1. Creating a Service Account
    2. Setting up the BigQuery Connection in Looker
  3. Click the Develop tab and select Development Mode.

  4. Generate a LookML model and project for your dataset. For more information, see the Looker instructions for generating a model.

  5. Using the Explore menu, navigate to an explore associate with the new model file name Explore 311_service_requests_copy (or whatever you named your explore).

You can now explore your data.

Clean up

To avoid incurring charges to your Google Cloud account for the resources used on this page, follow these steps.

To avoid incurring charges to your Google Cloud account for the resources used in this quickstart, you can delete the project, delete the BI Engine reservation, or both.

Deleting the project

The easiest way to eliminate billing is to delete the project that you created for the tutorial.

To delete the project:

  1. In the Cloud console, go to the Manage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then click Delete.
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

Deleting the reservation

Alternatively, if you intend to keep the project, then you can avoid additional BI Engine costs by deleting your capacity reservation.

To delete your reservation, follow these steps:

  1. In the Cloud console, under Administration go to the BI Engine page.

    Go to the BI Engine page

  2. In the Reservations section, locate your reservation.

  3. In the Actions column, click the icon to the right of your reservation and choose Delete.

  4. In the Confirm reservation removal dialog, enter REMOVE and then click Proceed.

What's next