User Guide

BigQuery

Steps to prepare and connect to BigQuery

Suggest Edits

Lightup account setup

Lightup needs a service account with a JSON key and these BigQuery roles:

  • BigQuery > BigQuery Data Viewer
  • BigQuery > BigQuery Job User
  • BigQuery > BigQuery Metadata Viewer
  • BigQuery > BigQuery Read Session User
  • BigQuery > BigQuery Resource Viewer

To work with External Tables, Lightup needs the following additional role

  • Cloud Storage > Storage Object User

Create a service account and download the JSON credentials certificate

❗️

You must have Google Cloud admin permissions to create a service account. Google has documentation on creating a service account and generating a private key.

  1. Open the credentials page in the Google Cloud Platform API Manager and, if necessary, select your project:

  1. Click CREATE CREDENTIALS and choose Service account:

  1. Enter a name for the new service account, optionally add a description, and click CREATE:

  1. Your service account requires these Google BigQuery predefined roles:

    • BigQuery > BigQuery Data Viewer
    • BigQuery > BigQuery Job User
    • BigQuery > BigQuery Metadata Viewer
    • BigQuery > BigQuery Read Session User
    • BigQuery > BigQuery Resource Viewer

Select the first role in the Select a role field, then click ADD ANOTHER ROLE and select the next role.
Repeat to add the remaining roles, then click CONTINUE.

  1. Click + ADD KEY > Create new key.
  2. Under Key type, select JSON and then click CREATE.

  1. The JSON key will be saved to your computer. BE SURE TO REMEMBER WHERE IT IS SAVED. After noting the download location, click CLOSE:

  1. At the bottom left, click DONE.

Connect to a BigQuery datasource

  1. In the left pane select Datasources*
  2. In the main page select Create Datasource +.
  3. Enter a Datasource Name, then for Connector Type select BigQuery.
  4. Under Configure connector, do one of the following using your BigQuery service account key:
    • Drag the service account key file to the Upload tab; or select BROWSE on the Upload tab and then browse to and select your key file.
    • Open the key and copy its JSON code into the JSON tab.
  5. After entering the required settings and any optional settings that apply, below the Configure connector section select Test Connection.
  6. After a successful connection test, select Save.
  7. Your new datasource appears in the list of available datasources. By default, these are listed in alphabetical order, so you might have to scroll or change the sort order to see your new datasource.

Advanced/Schema scan frequency

You can adjust how often scans run for a datasource.

  • In section 3 - Advanced, select a value for Schema scan frequency: Hourly, Daily, or Weekly.

Query Governance

BigQuery datasources support the Scheduling, Enable data storage, Maximum backfill duration, and Maximum distinct values query governance settings. For steps, see Set query governance settings for a datasource.

Date/time data types

These BigQuery date/time data types are supported:

  • DATE
  • DATETIME
  • TIMESTAMP

Object types

These BigQuery object types are supported:

  • Tables
  • Views
  • Materialized Views
  • External Tables

Updated about 2 months ago