What's new ⬇ Download Reportworq
⬇ Guide PDF

Connect Google BigQuery#

Reportworq connects to Google BigQuery through a CData driver, which presents your BigQuery datasets as a relational, tabular source you query with the same table or SQL modes as any database. In the connector catalog the tile is Google BigQuery.

This page sets up an unattended connection that signs in as a Google Cloud service account, the setup we recommend for scheduled distribution jobs. For the shared CData add-and-test flow, see Connect CData sources.

Before you begin#

Create a service account and its key#

  1. In the Google Cloud console, select your project and go to IAM & Admin > Service Accounts.
  2. Select Create service account, give it a name such as reportworq-bigquery, and create it.
  3. Grant the service account these two roles on the project:
    • BigQuery Data Viewer, so it can read your datasets and tables.
    • BigQuery Job User, so it can run queries. BigQuery runs every query as a job, so without this role the connection can see tables but cannot read them.
  4. Open the service account, go to Keys > Add key > Create new key, choose JSON, and create it. Your browser downloads a .json key file.

Treat the key file as a secret. Anyone who holds it can query your data as the service account. Do not email it or store it in a shared folder, and delete the local copy once it is in Reportworq. If a key is exposed, delete it in the Google Cloud console and create a new one.

Add the connection in Reportworq#

  1. Sign in to Reportworq as an administrator and go to Settings > Integrations.

  2. Select Add integration, find Google BigQuery in the catalog, and select Add. The connection editor opens.

  3. In the General section, select Enable Integration, give the connection a Display name, and choose its Availability. The display name is how models and formulas refer to the connection, so it must be unique across the instance.

  4. Set the connection properties:

    Property Value
    Auth Scheme OAuthJWT. This is the service-account sign-in.
    OAuth JWT Cert Paste the entire contents of the JSON key file, from the opening { to the closing }.
    OAuth JWT Cert Type GOOGLEJSONBLOB. See the note below: this is the setting most often missed.
    OAuth JWT Subject Leave empty.
    Initiate OAuth Leave at GETANDREFRESH.
    Project Id Your project's ID, for example acme-analytics.
    Dataset Id Optional. See Optional properties.
    Billing Project Id Optional. See Optional properties.
  5. Select Save Changes, then Test Connection. A working connection reports Success.

Choose GOOGLEJSONBLOB when you paste the key. OAuth JWT Cert Type offers two Google types. GOOGLEJSON expects OAuth JWT Cert to hold the path to a key file on the Reportworq server. GOOGLEJSONBLOB expects the JSON content itself, which is what you pasted. If the type is left at GOOGLEJSON with pasted content, the driver treats the JSON as a file name and the test fails.

The Google BigQuery connection editor. Auth Scheme is OAuthJWT, the OAuth JWT Cert field shows only masking dots, OAuth JWT Cert Type is GOOGLEJSONBLOB, Initiate OAuth is GETANDREFRESH and Project Id is set
The Google BigQuery connection editor. Auth Scheme is OAuthJWT, the OAuth JWT Cert field shows only masking dots, OAuth JWT Cert Type is GOOGLEJSONBLOB, Initiate OAuth is GETANDREFRESH and Project Id is setTap or click the image to view it full screen

Once saved, the key is never shown again: OAuth JWT Cert is a password field, so the editor displays only dots.

Test Connection on the Google BigQuery connection, with the Connection Test dialog reporting Success
Test Connection on the Google BigQuery connection, with the Connection Test dialog reporting SuccessTap or click the image to view it full screen

Optional properties#

Query your BigQuery data#

Once the connection tests, a model author uses it like any other relational source:

  1. Create a data model on the connection, or add the connection as a source of an existing model. See Data models.
  2. On the model's Source screen, open the Tables tab. Reportworq reads BigQuery's own metadata and lists your tables and views, grouped by dataset.
  3. Select a table to preview its rows, read live from BigQuery.
  4. Build views over the tables on each view's Design tab, starting from a table or writing SQL. See Build a view with the designer.
A data model over Google BigQuery. The Tables tab lists the REPORTWORQ_DEMO and TIMBERLINE datasets with their tables, and the names_by_state table is selected, previewing its rows
A data model over Google BigQuery. The Tables tab lists the REPORTWORQ_DEMO and TIMBERLINE datasets with their tables, and the names_by_state table is selected, previewing its rowsTap or click the image to view it full screen

The preview reads the first 1,000 rows and shows the first 100. Copy and download include all 1,000.

How your key stays secure#

Other sign-in options#

Auth Scheme also offers OAuth, a sign-in with a person's own Google account, and the workload identity schemes GCPInstanceAccount, AWSWorkloadIdentity and AzureWorkloadIdentity. This page does not cover them in detail. A service account suits scheduled jobs because it does not depend on one person's account; for the other schemes, see CData's Google BigQuery ADO.NET provider documentation.

Troubleshooting#

Symptom Likely cause What to do
Test Connection fails with a file-not-found error, or says the key or certificate is not valid OAuth JWT Cert Type is GOOGLEJSON (a file path) while OAuth JWT Cert holds pasted JSON, or only part of the key was pasted Set OAuth JWT Cert Type to GOOGLEJSONBLOB, paste the whole key file again from { to }, save, and test.
An access denied or permission error, often naming bigquery.jobs.create The service account is missing BigQuery Job User, so it cannot run queries Grant BigQuery Job User on the project (and on the Billing Project Id project, if you set one).
Access denied when reading a table, or the Tables tab is empty The service account is missing BigQuery Data Viewer on the project or dataset Grant BigQuery Data Viewer on the project, or on each dataset Reportworq should read.
A project-not-found error, or the tables you expect are missing Project Id is wrong: a display name or project number instead of the project ID, or a different project Enter the project ID exactly as the Google Cloud console's project picker shows it, and grant the service account its roles in that project.
A query names a table that does not exist Dataset Id points at a different dataset, or the SQL omits the dataset Name tables as dataset.table, or set Dataset Id to the dataset that holds them.

Feedback on this page

Comments, questions, requests, or something missing or unclear? Email us - the page you are on is filled in for you.

Email feedback on this page

Or write to support@reportworq.com directly.