---
title: "Google BigQuery Connection Setup"
canonical: "https://docs.vaultspeed.com/space/VPD/3780935696/Google%20BigQuery%20Connection%20Setup"
format: markdown
---
This guide outlines the necessary steps to set up a connection to Google BigQuery using a specific JDBC driver. 

## Prerequisites

- **Google BigQuery Account**: Ensure you have access to your Google BigQuery account with a valid project set up.
- **Service Account with Appropriate Permissions**:  
You must have a Google BigQuery Service Account with the following permissions:
  - **BigQuery Data Viewer**: To allow the service account to read data from BigQuery tables.
  - **BigQuery Job User**: To allow the service account to run queries and load jobs.
  - **BigQuery Read Session User** (optional): Needed if you are connecting with high-volume analytics requiring sessions.
  - **Storage Object Viewer**: If your queries access data from external Google Cloud Storage buckets.
  These permissions can be assigned directly via IAM roles in your Google Cloud Console:
  - Go to **IAM & Admin** > **IAM** in the Google Cloud Console.
  - Locate your service account.
  - Assign the required roles listed above to the service account.
  For more information, refer to the [Google Cloud IAM Documentation](https://cloud.google.com/iam/docs).

## Steps to Set Up the Connection

### 1. Download the JDBC Driver:

- Download the "bq-driver-all.jar" file below:

  file.

### 2. Place the JDBC Driver

- Save the 'bq-driver-all.jar' file in a directory on your agent environment.

> 📝 **Note: **Creating a subfolder (eg: google_connection) in the agent’s location is recommended.
> 📝 
> 📝 The full path to this file will be needed in the configuration.

### 3. Create a GBQ Service Account and Key File

- Log in to your Google BigQuery environment.
- Access your Google Cloud Console and navigate to IAM & Admin > Service Accounts.
- Create a new service account or use an existing one.
- Generate a new key in JSON format and download it.
- Save the JSON key file in the same folder as the JDBC driver.

> 📝 **Note: **The full path to this key file will be needed in the configuration.

### 4. Configure the Connection Properties

ModifyRestart your agent to apply changes.

- Create the database link in VaultSpeed and test the connection. the 'connections.properties' file of your agent with the following content, replacing placeholders with your actual values:

```properties
<your connection name>.driverName=com.simba.googlebigquery.jdbc.Driver 
<your connection name>.driverPath="<your file location>/bq-driver-all.jar" 
<your connection name>.url="jdbc:bigquery://<https://www.googleapis.com/bigquery/v2:443;ProjectId=<your> project id>;OAuthType=0;OAuthServiceAcctEmail=<your service account mail>;OAuthPvtKeyPath=<keyPath json file full location>;Location=<region code>;"
```

> 📝 **Note**: Replace placeholders with actual values: 
> 📝 
> 📝 - `'<your connection name>'` : the name you want to give to your connection, to uniquely identify this database connection
> 📝 - `'<your file location>'`: The directory where `bq-driver-all.jar` is saved.
> 📝 - `'<your project id>'`: Your Google Cloud project ID.
> 📝 - `'<your service account mail>'`: The email address of your service account.
> 📝 - `'<keyPath json file full location>'`: The full path to the JSON key file.
> 📝 - Location=<region code>: <region code>: Code of the region, where BigQuery dataset will be created. Examples of region codes: US, EU

### 5. Test the Connection

- Restart your agent to apply changes.
- Create the database link in VaultSpeed and test the connection.


![image-20240606-084906.png](media://2ebaa850-ec12-4244-b47b-8e27007cd8dd)

### 7. Test the connection

Once created you can test the connection using VaultSpeed:


![image-20240606-084943.png](media://9405b700-f0c9-4edc-a196-f138b56cbfcf)

### 8. Results

Check the result of the test in the task overview:


![image-20240606-085031.png](media://6de69026-3ffd-4565-a477-8924e780110e)

To ensure your connection has access to the metadata you want to connect on create a source based on that db link and do a “Get Objects”.  This should show you the list of tables that are located in your GBQ database.

### 9. Important notice

> ❌ It has to be noted that struct and JSON nested fields cannot be accessed using a [dot operator ](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/operators#field_access_operator)e.g. myStruct.address.zipcode.  
> ❌ Nested fields should be accessed only using a basic [subscript operator](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/operators#struct_subscript_operator), using offest or ordinal in a subscript is also not supported. 
> ❌ 
> ❌ Supported version: something like myStruct[0][2]

### Troubleshooting

If you encounter issues, verify that all paths and credentials are correct. Check the permissions of the service account to ensure it has access to BigQuery.