> ## Documentation Index
> Fetch the complete documentation index at: https://docs.textql.com/llms.txt
> Use this file to discover all available pages before exploring further.

# BigQuery Connector

> Connecting TextQL to Google BigQuery

<iframe className="w-full aspect-video rounded-xl" src="https://www.youtube.com/embed/0z9YL8Z1BHQ" title="How to Connect BigQuery to TextQL | AI Data Analysis Tutorial" frameBorder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen />

## Overview

To connect TextQL with Google BigQuery, you will need to set up a service account with appropriate permissions. The service account requires the following roles:

* BigQuery Data Viewer: For accessing and reading data
* BigQuery Job User: For executing queries

### Creating a Service Account

1. Navigate to the [Google Cloud Console](https://cloud.google.com/iam/docs/keys-create-delete#console) to create a service account.
2. Grant the required roles (BigQuery Data Viewer and BigQuery Job User).
3. Create and download the service account key file (JSON format).

### Creating the Connector in TextQL

Navigate to the [TextQL Connectors Page](/core/datasources/the-connectors-page) and click **Create New Connector**. Select BigQuery from the available connectors to open the configuration form.

The form requires the following information:

* **Connector Name:** A descriptive name to identify this specific BigQuery connection in TextQL.
* **Project ID:** Your Google Cloud project identifier.
* **Dataset ID:** The specific BigQuery dataset to connect to (optional). When provided, TextQL scopes all schema introspection and connection tests to that dataset only.
* **Credentials:** The JSON contents of a service account key, or a Workload Identity Federation credential configuration (see below).
* **Region Qualifier:** The geographic location of your BigQuery resources.

### Authenticating with Workload Identity Federation

If your organization disables service account key creation (the `iam.disableServiceAccountKeyCreation` org policy), the connector also accepts a [Workload Identity Federation](https://cloud.google.com/iam/docs/workload-identity-federation) credential configuration (`"type": "external_account"`) in the same Credentials field. No long-lived secret is stored; the TextQL deployment exchanges its own workload identity for short-lived Google Cloud tokens at query time.

1. Ask your TextQL contact for the identity your deployment presents (for dedicated deployments, typically the cluster's OIDC issuer URL and the token audience).
2. In your Google Cloud project, create a workload identity pool and provider that trusts that issuer.
3. Create a service account with the **BigQuery Data Viewer** and **BigQuery Job User** roles, and grant the pool identity `roles/iam.workloadIdentityUser` on it.
4. Generate the credential configuration with `gcloud iam workload-identity-pools create-cred-config`, and paste the resulting JSON into the Credentials field.

For security, TextQL validates the configuration before use: `token_url` must be `https://sts.googleapis.com/v1/token`, service account impersonation must go through `iamcredentials.googleapis.com`, and executable credential sources are rejected. File-sourced subject tokens must live under `/var/run/secrets/` in the deployment, which requires the token volume to be enabled for your deployment.

### Google Sign-In (Per-Member OAuth)

Instead of a shared service account, each TextQL member can sign in with their own Google account. Every query then runs as that member against BigQuery, so their own IAM grants, row/column-level policies, and BigQuery audit logs apply — TextQL never holds a credential that can read more than the person asking.

#### Prerequisites

* **An OAuth 2.0 client in your Google Cloud project** — in **APIs & Services → Credentials**, create an **OAuth client ID** of type **Web application**, and note the **Client ID** and **Client secret**.
* **Authorized redirect URI** — add `https://<your-textql-host>/auth/bigquery/callback` to the client's **Authorized redirect URIs**. The connector form shows the exact URI for your deployment with a copy button.
* **Consent screen scopes** — the client requests `https://www.googleapis.com/auth/bigquery` (Google's read-and-manage BigQuery scope, required to run query jobs — what a member can actually reach is still bounded by their IAM), `https://www.googleapis.com/auth/drive.readonly` (for Drive-federated external tables), and `openid`/`email`/`profile`. Internal-audience apps need no verification; external-audience apps must have these scopes approved.
* **Per-member IAM** — each member needs **BigQuery Job User** on the project and read access to the dataset. Members without those grants get a permission error from BigQuery rather than seeing another member's data.

#### Connector Fields

Select **Google Sign-In** under **Authentication Method** and fill in:

| Field | Required | Description |
| - | - | - |
| **OAuth Client ID** | Yes | Client ID of the Google Cloud OAuth client |
| **OAuth Client Secret** | Yes | Client secret of the Google Cloud OAuth client |

Project ID, Dataset ID, and Region Qualifier work exactly as with service account auth. No Credentials JSON is stored.

#### Member Sign-In

Once the connector is saved, each member clicks **Connect** on the connector (or is prompted the first time they run a query) and signs in with Google. Tokens are encrypted at rest and refreshed automatically; members can re-authenticate at any time from the Connectors page.

<Note>
  In this mode the connection test only confirms that the Project ID, Client ID, and Client Secret are filled in — it does not contact Google. The client secret is first exercised when a member signs in, so complete one member sign-in to confirm the OAuth client is configured correctly.
</Note>

### SSO via Workforce Identity Federation

If your members sign in to TextQL through an OIDC identity provider and your identities are not in Google, the connector can exchange each member's SSO login token for short-lived Google Cloud credentials through [Workforce Identity Federation](https://cloud.google.com/iam/docs/workforce-identity-federation). There is no popup, no Google account, and no stored Google credential. Every query runs as the member's federated identity, so BigQuery IAM, row-level security (`SESSION_USER()`), and audit logs all see the individual user. Any identity provider that Google workforce pools accept as an OIDC provider works, including Microsoft Entra ID, Okta, and Ping.

#### How it works

1. The member logs in to TextQL through your [SSO configuration](/core/admin/sso). TextQL keeps the identity provider's ID token and refresh token encrypted at rest.
2. When the member first queries the connector, TextQL sends the ID token to Google STS (`https://sts.googleapis.com/v1/token`) with your workforce pool provider as the audience and the billing project in the request options.
3. Google validates the issuer, audience, and subject against the provider and returns a one-hour access token for the federated principal.
4. TextQL runs the query with that token and re-exchanges automatically when it expires. If the SSO session itself has expired and cannot be refreshed, the member is asked to sign in to TextQL again.

#### Prerequisites

* **OIDC SSO for TextQL.** Members must log in through your identity provider's OIDC integration. The application used for TextQL login is the one Google will trust, because Google checks the ID token's `aud` claim against the provider's client ID.
* **A workforce identity pool and OIDC provider** in your Google Cloud organization that trusts your identity provider. Set the provider's issuer to your IdP's OIDC issuer URL, its client ID to the TextQL application's client ID, and map `google.subject` to a stable, unique user claim.
* **IAM grants for the pool principals.** Grant `roles/bigquery.jobUser` on the project and `roles/bigquery.dataViewer` on the datasets to the pool (`principalSet://iam.googleapis.com/locations/global/workforcePools/<pool>/*`) or to individual subjects. The principals also need `serviceusage.services.use` on the billing project.
* **Refresh tokens.** The identity provider must issue a refresh token at login so the ID token can be renewed without another sign-in. TextQL requests `offline_access` automatically for Microsoft Entra ID; for other providers, add the equivalent scope under additional scopes in the SSO configuration if your IdP requires it.

The generic settings map to Microsoft Entra ID as follows:

| Setting | Generic value | Microsoft Entra ID |
| - | - | - |
| Provider issuer | Your IdP's OIDC issuer URL | `https://login.microsoftonline.com/<tenant-id>/v2.0` |
| Provider client ID | Client ID of the TextQL SSO application | Application (client) ID of the app registration |
| `google.subject` mapping | A stable, unique user claim | `assertion.oid` |
| Refresh scope | Provider-specific offline scope | `offline_access`, requested automatically |

#### Example: Microsoft Entra ID

Workforce pools are organization-level resources, so the Google Cloud steps are usually done by your organization's IAM administrator. Replace `ORG_ID`, `TENANT_ID`, `APP_CLIENT_ID`, `POOL_ID`, `PROJECT_ID`, `DATASET_ID`, and `GROUP_OBJECT_ID` with your values.

**1. Prepare the Entra app registration.** In the Entra admin center, open the app registration that TextQL uses for SSO login and note its **Application (client) ID** and **Directory (tenant) ID**. Under **Authentication**, select the **ID tokens** checkbox and save; Google requires it for a provider configured with the `id-token` response type. v2.0 ID tokens already carry the `oid` claim. If you want to restrict access by group in step 3, also enable the **groups** claim under **Token configuration**.

**2. Create the workforce pool and the Entra provider.**

```bash theme={null}
gcloud iam workforce-pools create POOL_ID \
  --organization=ORG_ID \
  --location=global \
  --display-name="TextQL workforce pool"

gcloud iam workforce-pools providers create-oidc entra \
  --workforce-pool=POOL_ID \
  --location=global \
  --display-name="Microsoft Entra ID" \
  --issuer-uri="https://login.microsoftonline.com/TENANT_ID/v2.0" \
  --client-id="APP_CLIENT_ID" \
  --web-sso-response-type="id-token" \
  --web-sso-assertion-claims-behavior="only-id-token-claims" \
  --attribute-mapping="google.subject=assertion.oid,google.display_name=assertion.preferred_username" \
  --detailed-audit-logging
```

`--web-sso-response-type` and `--web-sso-assertion-claims-behavior` are required by gcloud even though TextQL exchanges tokens programmatically. `--detailed-audit-logging` adds the token exchange details to Cloud Audit Logs, which is useful for the audit trail.

**3. Optionally restrict who can federate.** With the groups claim enabled in step 1, add `google.groups=assertion.groups` to the attribute mapping and an attribute condition so only members of a specific Entra group receive Google credentials:

```bash theme={null}
gcloud iam workforce-pools providers update-oidc entra \
  --workforce-pool=POOL_ID \
  --location=global \
  --attribute-mapping="google.subject=assertion.oid,google.display_name=assertion.preferred_username,google.groups=assertion.groups" \
  --attribute-condition="'GROUP_OBJECT_ID' in assertion.groups"
```

**4. Grant BigQuery access to the pool principals.** Job User and Service Usage Consumer go on the project through gcloud. Data Viewer is best granted per dataset through SQL so the pool only reaches the datasets you choose.

```bash theme={null}
gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="principalSet://iam.googleapis.com/locations/global/workforcePools/POOL_ID/*" \
  --role="roles/bigquery.jobUser"

gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="principalSet://iam.googleapis.com/locations/global/workforcePools/POOL_ID/*" \
  --role="roles/serviceusage.serviceUsageConsumer"
```

```sql theme={null}
GRANT `roles/bigquery.dataViewer`
  ON SCHEMA `PROJECT_ID`.DATASET_ID
  TO "principalSet://iam.googleapis.com/locations/global/workforcePools/POOL_ID/*";
```

When groups are mapped, `principalSet://iam.googleapis.com/locations/global/workforcePools/POOL_ID/group/GROUP_OBJECT_ID` scopes a grant to one Entra group. Row access policies accept a single federated user as `principal://iam.googleapis.com/locations/global/workforcePools/POOL_ID/subject/OID`, and `SESSION_USER()` returns the federated principal rather than an email address, so run `SELECT SESSION_USER()` as a federated user and write row filters against that value.

**5. Configure the connector.** In TextQL, select **SSO Federation**, set **Workforce Identity Provider** to `//iam.googleapis.com/locations/global/workforcePools/POOL_ID/providers/entra`, and set **Billing Project** to the project that should absorb query quota, or leave it empty to use the Project ID. TextQL sends it as the `userProject` option on the STS exchange, which is why the Service Usage Consumer grant above is needed.

**6. Verify.** Sign in to TextQL as an Entra user and run a query against the connector. The Connectors page shows **Connected** for that member once the first exchange succeeds. If it fails, compare the `aud` claim of the member's ID token with the provider's client ID, and check the pool's IAM bindings on the project.

#### Connector Fields

Select **SSO Federation** under **Authentication Method** and fill in:

| Field | Required | Description |
| - | - | - |
| **Workforce Identity Provider** | Yes | Full resource name of the provider: `//iam.googleapis.com/locations/global/workforcePools/<pool>/providers/<provider>` |
| **Billing Project** | No | Project number or ID charged for quota and billing on the exchanged token. Defaults to the Project ID. |

Project ID, Dataset ID, and Region Qualifier work exactly as with service account auth. No Credentials JSON or OAuth client is stored.

<Note>
  The connection test only confirms that the provider resource name is well-formed. The exchange is first exercised when a member runs a query, so have one SSO user run a query to confirm the pool trusts your identity provider.
</Note>

### Testing the Connection

After entering your credentials, click **Create** to establish the connection. TextQL will validate your service account permissions and create the connector.

* If a **Dataset ID** is provided, the connection test verifies access to that specific dataset.
* If no Dataset ID is provided, TextQL checks that the service account can list datasets in the project.

If the connection fails, verify your service account key and ensure all required permissions are granted.

<Note>
  Having trouble connecting? See the [Network Configuration Guide](/core/datasources/databases/network-configuration) for firewall and IP whitelisting setup.
</Note>

### Next Steps

Once connected, you can use TextQL to query your BigQuery datasets. For optimal performance and security:

* Use a dedicated service account for TextQL connections
* Apply principle of least privilege when assigning roles
* Consider setting up dataset-level access controls
* Monitor query costs through BigQuery's pricing calculator


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.