BigQuery default connection for Looker (Google Cloud core)

Looker (Google Cloud core) must be connected to a database to enable data exploration. A default BigQuery connection that can use the Application Default Credentials can be created using BigQuery Quickstart Connection.

The BigQuery Quickstart tile on a home page.

Before you begin

Configuring a BigQuery Quickstart Connection requires the following permissions.

Looker (Google Cloud core) permissions

You can view and edit the BigQuery Quickstart Connection page on your Looker (Google Cloud core) instance Home page if you have one of the following permissions:

BigQuery permissions

Looker (Google Cloud core) instances can use Application Default Credentials (ADC) to authenticate when you're setting up a connection to a BigQuery Standard SQL database. When you use ADC, the connection will authenticate to the database by using the credentials of the Looker (Google Cloud core) project's service account.

If the Looker (Google Cloud core) service account doesn't already have IAM roles in the project that contains the BigQuery dataset, to grant new IAM roles to the service agent, you'll need to be able to modify IAM policies in the Google Cloud console using the following steps.

  1. Sign in to the Google Cloud console and navigate to the project where you want to grant new roles.
  2. Copy the principal identifier and add it as a principal in IAM.
  3. Assign both of the following roles to the principal that you added:
    • Service Usage Consumer
    • BigQuery Jobs User

If your Looker (Google Cloud core) instance uses persistent derived tables with a BigQuery dataset, you must also grant the Looker (Google Cloud core) service account the BigQuery Data Editor IAM role.

Configuring a BigQuery Quickstart Connection

The BigQuery Quickstart Connection contains the following sections.

Viewing the connection

The BigQuery Quickstart Connection can be viewed and edited by users with the correct permissions either from the Home page or the Connections page in the Admin panel. On the Connections page the BigQuery Quickstart Connection appears under the name Default BigQuery Connection. On a new instance, the Storage project ID and Billing project ID fields will default to None.

From the Home page, click the Review connection button to manage the connection. You can dismiss the Home page tile by clicking the x or by toggling the BigQuery Quick Start option in the Discover sidebar.

The BigQuery Quickstart Connection contains the following sections.

Billing Project ID

The project ID serves as a unique identifier for the Google Cloud billing project. The billing project is the Google Cloud project that gets billed, but you can still query datasets in a different Google Cloud project if your LookML developers specify fully scoped table names in the sql_table_name parameter of your LookML views, Explores, or joins. This is a required field.

To authenticate to a BigQuery database using OAuth: For BigQuery connections, Looker (Google Cloud core) can automatically use the OAuth application credentials that your Looker (Google Cloud core) admin used when they created the instance. See the Create OAuth authorization credentials for a Looker (Google Cloud core) instance page for more information.

Expand the Status Details section to test the settings for your connection.

Primary Dataset

The Primary Dataset page contains the following settings.

Storage Project ID

If you are using a project other than the BigQuery project where Looker (Google Cloud core) is located, enter the Storage Project ID. This is a required field.

To locate your Storage Project ID, follow these steps:

  1. Open the Google Cloud console.
  2. From the projects list, select All. The names and IDs for all the projects you're a member of are displayed.
  3. Copy the Storage Project ID of the project that contains the data that you want to connect to Looker (Google Cloud core).

Primary Dataset

The primary dataset is where BigQuery will look for tables if their location is not specified in the SQL query text. Note that Looker (Google Cloud core) queries can reference tables in any project or dataset, as long as the queries use fully scoped table names with the format project_id.dataset_name.table_name. The Looker (Google Cloud core) Service Agent will also need the appropriate IAM permissions to access the tables in that location. This is a required field.

To learn more about datasets, see the Connecting Looker to BigQuery documentation page.

Expand the Status Details section to test the settings for your connection.

Optional Settings

The Optional Settings section contains the following options:

  • Maximum Connections per Node: The maximum number of connections to the database that are allowed at one time. Note: This setting is per each node in the Looker (Google Cloud core) deployment. The value must be between 5 and 100 and can be left at the default value initially. Read more about this setting in the Max connections per node section of the Connecting Looker to your database documentation page.

  • Connection Pool Timeout: The number of seconds that a query will wait before timing out when the connection pool is full. Can be left at the default value initially. Read more about this setting in the Connection Pool Timeout section of the Connecting Looker to your database documentation page.

  • Additional JDBC Parameters: Add any additional JDBC parameters, such as BigQuery labels (see the Job labels and context comments for BigQuery connections section on this page for more information).

  • Maintenance Schedule: Cron expression that indicates the maximum frequency of datagroup trigger checks and PDT maintenance. Read more about this setting in the Datagroup and PDT Maintenance Schedule documentation.

  • SSL: Choose whether you want to use SSL encryption to protect data as it passes between Looker (Google Cloud core) and your database. SSL is only one option that can be used to protect your data; other secure options are described on the Enabling secure database access documentation page.

  • Verify SSL: Choose whether you want to require verification of the SSL certificate used by the connection. Read more about this setting in the Verify SSL section of the Connecting Looker to your database documentation page.

  • Precache tables and columns: In SQL Runner, all table information is pre-loaded as soon as you select a connection and schema. This enables SQL Runner to quickly display table columns as soon as you click a table name. However, for connections and schema with many tables or with very large tables, you may not want SQL Runner to pre-load all the information.

  • Fetch and cache schema: For some SQL-writing features such as aggregate awareness, Looker (Google Cloud core) uses your database's information schema to optimize SQL writing. Read more about this setting in the Fetch Information Schema For SQL Writing section of the Connecting Looker to your database documentation page.

  • Enable PDTs: Turn on the Enable PDTs toggle to enable persistent derived tables. When PDTs are enabled, the Optional Settings window reveals additional PDT fields and the PDT Overrides section.

  • Temp database: Enter the dataset in BigQuery where Looker (Google Cloud core) will create persistent derived tables. You should configure this dataset ahead of time, with the appropriate write permissions. This field is required to use PDTs.

  • Max Number of PDT Builder Connections: The Max number of PDT builder connections setting defaults to 1 but may be set as high as 10. However, the value cannot be higher than the value set in the Max connections per node. Read more about this setting in the Max Number of PDT Builder Connections section of the Connecting Looker to your database documentation page. Set this value carefully. If the value is too high, you may overwhelm your database. If the value is low, then long-running PDTs or aggregate tables can delay the creation of other persistent tables or slow down other queries on the connection.

  • Retry failed PDT builds: The Retry failed PDT builds toggle configures how the Looker (Google Cloud core) regenerator attempts to rebuild trigger-persisted tables that failed in the previous regenerator cycle. Read more about this setting in the Retry failed PDT builds section of the Connecting Looker to your database documentation page.

  • PDT API Control: The PDT API Control toggle determines whether the start_pdt_build, check_pdt_build, and stop_pdt_build API calls can be used for this connection. When the PDT API Control toggle is disabled, these API calls will fail when they reference PDTs on this connection.

  • PDT Overrides: If your database supports persistent derived tables, and you have turned on the Enable PDTs toggle in the connection settings, Looker (Google Cloud core) displays the PDT Overrides section. In the PDT Overrides section, you can enter separate JDBC parameters (host, port, database, username, password, schema, additional parameters, and after connect statements) that are specific to PDT processes. Read more about this setting in the PDT Overrides section of the Connecting Looker to your database documentation page.

  • Database Time Zone: The time zone in which your database stores time-based information. Looker (Google Cloud core) needs to know this so that it can convert time values for users, making it easier to understand and use time-based data. See the Using time zone settings documentation page for more information.

  • Query Time Zone: The Query Time Zone option is visible only if you have disabled User Specific Time Zones. See the Using time zone settings documentation page for more information.

Expand the Status Details section to test the settings for your connection.

Review

Review and modify the connection details that you entered in the previous sections in the Review section.

Expand the Status Details section to test the settings for your connection. Click the edit icon next to each section to be taken back to that section to change your settings.

Saving and testing the connection

Click the Save button to save any changes made to the BigQuery Quickstart Connection.

You can test your connection settings from a couple of places in the Looker (Google Cloud core) UI:

  • Expand the Status Details section at the bottom of any of the QuickStart Connection pages, and click Test Connection.
  • From the Home page, expand the Status Details section at the bottom of the QuickStart Connection tile, and click Test Connection.
  • On the Connections Admin page, select the Test button next to the connection's listing, as described on the Connections documentation page.

Once you've entered the connection settings, click Test to verify that the information is correct and the database is able to connect.

If your connection does not pass one or more tests, here are some troubleshooting options:

What's next