Analyze data with BI Engine and Looker

Looker is an enterprise platform for business intelligence, data applications, and embedded analytics. Looker helps you explore, share, and visualize your company's data so that you can make better business decisions.

How Looker works

Looker lets data experts at each organization describe their data using a lightweight modeling language called LookML. LookML tells Looker how to query data, so everyone in the organization can create easy-to-read reports and dashboards to explore patterns of data. Looker offers additional features for creating custom data applications and experiences.

Looker's platform works with transactional databases like Oracle and MySQL as well as analytical datastores like BigQuery, Snowflake, Redshift, and more. Looker lets you create consistent data models on top of all your data with speed and accuracy. Looker offers a unified surface to access all of an organization's data.

Looker integration with BigQuery

Looker supports hosting in Google Cloud. Because Looker is platform independent, it connects to data in BigQuery as well as other public clouds.

You don't need Looker to use BigQuery. However, if your BigQuery use case includes business intelligence, data applications, or embedded analytics you might want to review Looker as a provider of these services.

If you already have a Looker instance running, see the instructions for connecting Looker to BigQuery.

Get started with Looker and BigQuery

The BI Engine SQL interface expands BI Engine to integrate with business intelligence (BI) tools such as Looker. For more information, see BigQuery BI Engine SQL interface overview.

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 Google 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 Google Cloud console.

Create your table

To create your table, follow these steps:

  1. In the Google 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 Google 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, configure your BI Engine reservation:

    • In the Project list, verify your Google Cloud project.
    • In the Location list, select a location. The location should match the location of the datasets that you're querying.

    • Adjust the GiB of Capacity slider to the amount of memory capacity that you're reserving. The following example sets the capacity to 2 GiB. The maximum is 250 GiB.

      BI Engine capacity location

  4. Click Next.

  5. In the Preferred Tables section, optionally specify tables for acceleration with BI Engine. To find table names, do the following:

    1. In the Table Id field, type part of the name of the table that you want accelerated by BI Engine—for example, 311.

    2. From the list of suggested names, select your table names.

      Only specified tables are eligible for acceleration. If no preferred tables are specified, all project queries are eligible for acceleration.

  6. Click Next.

  7. In the Confirm and submit section, review the agreement.

  8. If you accept the terms of agreement, 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. Configure an OAuth for a 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 instructions for connecting Looker to your database.

  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 have successfully connected Looker to BigQuery. You can use the System Activity feature in Looker to generate a Looker usage report and analyze the performance of your queries against BigQuery-specific performance metrics. To explore various BigQuery BI Engine query performance metrics, see BigQuery BI Engine metrics.

Clean up

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.

Delete the project

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

To delete the project:

  • Everything in the project is deleted. If you used an existing project for this tutorial, when you delete it, you also delete any other work you've done in the project.
  • Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as an appspot.com URL, delete selected resources inside the project instead of deleting the whole project. If you plan to explore multiple tutorials and quickstarts, reusing projects can help you avoid exceeding project quota limits.

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

    Go to the BI Engine page

  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.

Delete 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 Google 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 Delete reservation? dialog, enter Delete and then click DELETE.

What's next

There are many additional options related to administering Looker, customizing its data model, and exposing data to users. For more information, see the following resources: