Admin: Google BigQuery ODBC driver configuration

Overview

RPI can use Google BigQuery as a data warehouse. Query execution against BigQuery (for example, from a SQL Database Definition or a selection rule) goes through the Simba Open Database Connectivity (ODBC) driver, which supports two ways to authenticate:

• Service-account key file: a downloaded Google credential file, mounted into the RPI containers. Works everywhere and supports every BigQuery operation RPI performs.

• Workload Identity (keyless): available from RPI v7.8 on Google Kubernetes Engine (GKE) only. RPI doesn't store or mount a credential file; the pod authenticates as a Google service account through GKE's own identity mechanism.

This topic covers configuring the BigQuery connection via the Helm chart.

Which authentication method do I need?

Configure the connection: Kubernetes Helm chart (v7.8+)

Option A: Service-account key file

Provide the key file as a ConfigMap and reference it per connection:

global:
  deployment:
    platform: google

cloudIdentity:
  google:
    configMapName: my-bigquery-creds     # ConfigMap holding the key file
    configMapFilePath: /app/google-creds
    keyName: my-bigquery-creds.json

databases:
  datawarehouse:
    bigquery:
      enabled: true
      connections:
        - name: gbq-tenant1
          projectId: my-google-project
          credentialsType: serviceAccount
          serviceAccountEmail: gbq-tenant1@my-google-project.iam.gserviceaccount.com

The chart mounts the key file and points the ODBC connection at it automatically. Create the ConfigMap from your downloaded key before deploying:

kubectl create configmap my-bigquery-creds \
  --namespace <rpi-namespace> \
  --from-file=my-bigquery-creds.json=/path/to/downloaded-key.json

Option B: Workload Identity (keyless, GKE only)

Query traffic authenticates as a Google service account bound to the pod's Kubernetes identity. RPI doesn't store a key file anywhere. This reuses the same Workload Identity binding used for Google Cloud SQL with IAM authentication; if you've already set that up for your operational database, the binding step is the same mechanism, applied for BigQuery access instead.

global:
  deployment:
    platform: google

cloudIdentity:
  enabled: true
  google:
    serviceAccountEmail: gbq-tenant1@my-google-project.iam.gserviceaccount.com
    configMapName: ""          # empty = keyless

databases:
  datawarehouse:
    bigquery:
      enabled: true
      connections:
        - name: gbq-tenant1
          projectId: my-google-project
          credentialsType: workloadIdentity
          OAuthMechanism: 3

Google Cloud prerequisites (once per service account, provisioned on the Google side; RPI doesn't create these):

1. Grant the service account the ability to read BigQuery data and run BigQuery jobs, for example:

   Bash

   gcloud projects add-iam-policy-binding my-google-project \
     --member="serviceAccount:gbq-tenant1@my-google-project.iam.gserviceaccount.com" \
     --role="roles/bigquery.dataViewer"
   gcloud projects add-iam-policy-binding my-google-project \
     --member="serviceAccount:gbq-tenant1@my-google-project.iam.gserviceaccount.com" \
     --role="roles/bigquery.jobUser"
   

   Adjust to the specific BigQuery datasets and least-privilege policy your organization requires.

2. Bind the Kubernetes service account to the Google service account (Workload Identity):

   Bash

   gcloud iam service-accounts add-iam-policy-binding \
     gbq-tenant1@my-google-project.iam.gserviceaccount.com \
     --role="roles/iam.workloadIdentityUser" \
     --member="serviceAccount:my-google-project.svc.id.goog[<namespace>/<ksa-name>]"
   

   With cloudIdentity.serviceAccount.mode: shared, RPI uses one Kubernetes service account (redpoint-rpi) for the binding. With per-service (the default), bind each RPI service's own account that needs BigQuery access (typically Execution Service, Node Manager, Interaction API, Integration API, Queue Reader, and Callback API).

Register the connection in RPI

Once the chart has the ODBC Data Source Name (DSN) configured (Option A or B above), point RPI at it from the client:

1. In the RPI client, open Administration.

2. Locate the BigQuery data warehouse (auxiliary database) definition, or create one.

3. Set Database Type to Google BigQuery.

4. Enter the DSN Name you used in connections[].name.

5. In Additional Configuration, enter:

   JSON

   {"ProjectId":"my-google-project","ServiceAccountJsonPath":"/app/google-creds/my-bigquery-creds.json"}
   

   • ProjectId is your Google Cloud project ID.

   • ServiceAccountJsonPath is the in-pod path to the mounted key file. This path must be present and must match where the chart mounts it (cloudIdentity.google.configMapFilePath / keyName, or the connection's own ConfigMapFilePath/keyName if set) for schema changes, bulk loads, and nested-type lookups to work.

   • If you're using Workload Identity only for querying and haven't mounted a key file, leave ServiceAccountJsonPath unset. Queries will work, but attempting a schema change, bulk load, or nested-type lookup on this connection will fail until you add a key file.

6. Save your changes and, if you changed chart values, run helm upgrade.

Verify the connection

Confirm the DSN rendered correctly and the driver can connect:

kubectl exec -n <rpi-namespace> deploy/rpi-executionservice -- isql <connection-name> -v

Last updated: November 12, 2026