# Google BigQuery

{% embed url="<https://www.youtube.com/watch?v=eLe7KgPm4Pk>" %}

As a best practice, set up a dedicated Google Cloud Platform (GCP) Service Account user and assign it a specific role(s) before configuring Connecty. This approach isolates your integration credentials and simplifies permission management, ensuring a seamless no-code connection.

### Prerequisites

* GCP Project Identifier where your BigQuery is setup.
* Dedicated Service Account (SA) user for Connecty.
* Appropriate roles and permissions for the dedicated SA user.
* The SA user access key.
* Multi-region data location of your BigQuery dataset.

### Project ID

Your **Project ID** uniquely identifies your BigQuery project. To get the project ID you can:

* Go to [Google Cloud Console](https://console.cloud.google.com/).
* In the top-left Project selector dropdown, locate your project (under which your BigQuery instance is setup).
* Copy the `ID`.

Examples:

* ✅ `my-project-connecty`  - correct and expected identifier.
* ❌ `950600139040` - incorrect. That is usually a project number or an organisation ID

### Permissions

To enable Connecty AI to access and query data within your BigQuery service, the designated GCP role must contains the following GCP permissions:

{% hint style="info" %}
**Recommended**: The combination of GCP built-in roles (**`BigQuery Data Viewer + BigQuery Job User + BigQuery Resource Viewer + BigQuery Data Editor`**) inherently satisfy the requirements, however you can also create a custom role that includes only the minimum necessary privileges outlined below.
{% endhint %}

1. **Metadata Access**
   1. `bigquery.datasets.get`
   2. `bigquery.datasets.getIamPolicy`
   3. `bigquery.models.list`
   4. `bigquery.models.getMetadata`&#x20;
   5. `bigquery.routines.list`&#x20;
   6. `bigquery.routines.get`&#x20;
2. **READ permissions**
   1. `bigquery.tables.list`
   2. `bigquery.tables.get`
   3. `bigquery.tables.getData`
   4. `bigquery.tables.getIamPolicy`&#x20;
3. **WRITE permissions**
   1. `bigquery.tables.create` (on destination dataset)
   2. `bigquery.tables.updateData` (on destination dataset)
   3. `bigquery.jobs.create` (on the billing project)
   4. `bigquery.tables.update`
   5. `bigquery.tables.get`
   6. `bigquery.tables.delete`
4. **Query (job) history access**
   1. `bigquery.jobs.list`
   2. `bigquery.jobs.listAll`
   3. `bigquery.jobs.get`
   4. `bigquery.jobs.create`&#x20;

### Custom SA user and Role (Optional)

The following script is an example how one can create new dedicate Service Account user in GCP for ConnectyAI with granted new custom role with all required permissions. This script assumes that `gcloud` is installed locally. In case you don't have it, please follow [the instruction](https://cloud.google.com/sdk/docs/install).

> :warning: Please remember to replace `PROJECT_ID` and potentially `ROLE_ID` accordingly to your GCP setup.

```bash
#!/usr/bin/env bash
set -euo pipefail
trap 'echo "❌ Error on line $LINENO" >&2' ERR

PROJECT_ID="<YOUR_PROJECT_ID>"
SA_ID="connecty-ai-sa-user"
SA_EMAIL="${SA_ID}@${PROJECT_ID}.iam.gserviceaccount.com"
SA_DISPLAY="Connecty AI Service Account User"
ROLE_ID="bigqueryConnectyAIRole"                     
ROLE_NAME="projects/${PROJECT_ID}/roles/${ROLE_ID}"
ROLE_TITLE="BigQuery Connecty AI Read-Write Role"               
ROLE_DESC="BigQuery READ/WRITE access + Metadata + Materialization & Replace permissions"

PERMS=(
  # --- MANDATORY WRITE, MATERIALIZATION & REPLACE ---
  bigquery.tables.create        # Create new tables
  bigquery.tables.updateData    # Write/Append/Overwrite data rows
  bigquery.tables.update        # Update table properties/metadata
  bigquery.tables.delete        # Essential for "CREATE OR REPLACE" operations
  bigquery.jobs.create          # Execute the query job at project level
  
  # --- METADATA & DISCOVERY ---
  bigquery.datasets.get
  bigquery.datasets.getIamPolicy
  bigquery.tables.list
  bigquery.tables.get           # Check if destination exists
  bigquery.tables.getData
  bigquery.tables.getIamPolicy

  # --- OPTIONAL: MODELS & ROUTINES ---
  bigquery.models.list
  bigquery.models.getMetadata
  bigquery.routines.list
  bigquery.routines.get

  # --- JOB MANAGEMENT ---
  bigquery.jobs.list
  bigquery.jobs.listAll
  bigquery.jobs.get
)

PERMS_CSV=$(IFS=,; echo "${PERMS[*]}")

# 1. Create SA if it doesn't exist
if gcloud iam service-accounts list \
     --project="$PROJECT_ID" \
     --filter="email:${SA_EMAIL}" \
     --format="value(email)" | grep -q .; then
  echo "ℹ️  Service account already exists: ${SA_EMAIL}"
else
  echo "➡️  Creating service account ${SA_EMAIL}…"
  gcloud iam service-accounts create "$SA_ID" \
    --project="$PROJECT_ID" \
    --display-name="$SA_DISPLAY"
fi

# 2. Create or Update custom role
if gcloud iam roles describe "${ROLE_ID}" \
     --project="${PROJECT_ID}" \
     --quiet &>/dev/null; then

  echo "ℹ️  Role already exists, updating with REPLACE/DELETE permissions…"
  gcloud iam roles update "${ROLE_ID}" \
    --project="${PROJECT_ID}" \
    --permissions="${PERMS_CSV}" \
    --stage="GA"

else
  echo "➡️  Creating custom role ${ROLE_ID}…"
  gcloud iam roles create "$ROLE_ID" \
    --project="$PROJECT_ID" \
    --title="$ROLE_TITLE" \
    --description="$ROLE_DESC" \
    --permissions="$PERMS_CSV" \
    --stage="GA"
fi

# 3. Bind role
echo "➡️  Granting ${ROLE_NAME} to ${SA_EMAIL}…"
gcloud projects add-iam-policy-binding "$PROJECT_ID" \
  --member="serviceAccount:${SA_EMAIL}" \
  --role="${ROLE_NAME}"

echo "✅ Done."



```

### SA user access key

To generate an access key for Service Account user you can either use GCP console or `gcloud` CLI interface.

#### GCP Console

1. Go to IAM service in [Google Cloud Console](https://console.cloud.google.com/).
2. Go to `Service Accounts` and found the apprioprate SA user.
3. Click on that user and open `Keys` tab.
4. Click on `Add Key` and then `Create new key` . Select JSON format for the key.
5. Download generated access key file.

#### Command line interface

In case when you have `gcloud` installed locally and authenticated for your project in GCP, you can generate new access key file for a SA user using the following command:

```bash
PROJECT_ID="<YOUR_PROJECT_ID>"
SA_ID="connecty-ai-sa-user"
SA_EMAIL="${SA_ID}@${PROJECT_ID}.iam.gserviceaccount.com"

gcloud iam service-accounts keys create ~/key_for_connectyai.json \
  --iam-account="$SA_EMAIL" \
  --project="$PROJECT_ID"

```

> :warning: In case you've changed SA user ID or email, please update the values in the script above. Values in the script corresponds to setup mentioned in [#custom-sa-user-and-role](#custom-sa-user-and-role "mention").

### Multi-region data location

To fully setup Connecty integration with BigQuery, we need infromation in which [BigQuery multi-region](https://cloud.google.com/bigquery/docs/locations#multi-regions) your datasets are stored. It can be either `US` or `EU` . The easiest way to found out which multi-region your dataset uses is to by clicking on any object in that dataset and go to `details` tab, like on the following screenshot example:

<figure><img src="/files/nx5NY7n2ev4tYrSVs9xK" alt=""><figcaption><p>BigQuery UI, object->Details tab.</p></figcaption></figure>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://connectyai.gitbook.io/documentation/getting-started/data-connections-guide/google-bigquery.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.