# Ingesting Data from Google BigQuery

## Connecting to Google BigQuery

To connect to Google BigQuery, you need to set up authentication using a Google Cloud service account and provide the following information:

- **Name**: A friendly name for your connection to easily identify and reuse it for ingesting additional tables

- **Dataset Name**: The BigQuery dataset containing the tables you want to ingest

- **Service Account JSON Key File - Optional**: Paste your complete Google Cloud service account JSON key file content. If provided, this will be used instead of the individual fields below.
  - **Note**: If you use this option, you can skip the individual fields below
- **Or Enter Details Individually**:
  - **Client ID**: The unique identifier for the service account client (from `client_id` in JSON key file)
  - **Client Email**: The service account email address for authentication (from `client_email` in JSON key file)
  - **Private Key ID**: The unique identifier for the private key (from `private_key_id` in JSON key file)
  - **Private Key**: The private key for the service account in PEM format (from `private_key` in JSON key file)
  - **Project ID**: The Google Cloud Project ID containing your BigQuery datasets (from `project_id` in JSON key file)
  - **Client X509 Certificate URL**: The URL for the X.509 certificate associated with the service account (from `client_x509_cert_url` in JSON key file)

## Prerequisites

Before connecting to Google BigQuery, ensure that:

- Your Google Cloud Project is accessible and billing is enabled
- You have a valid service account with appropriate permissions
- The specified dataset exists and is accessible
- Network connectivity allows HTTPS access to BigQuery APIs
- Service account authentication is properly configured

## Service Account Setup

To set up service account authentication for BigQuery:

1. **Create a service account** in your Google Cloud Project
2. **Generate a JSON key file** containing all the required credentials
3. **Assign appropriate BigQuery roles** such as:

- `BigQuery Data Viewer` for read access to datasets and tables
   - `BigQuery Job User` for running queries
4. **Use the JSON key file** by either:

- **Recommended**: Pasting the entire JSON key file content into the connection form
   - **Alternative**: Extracting individual fields from the JSON key file for manual entry

## Required Permissions

The service account connecting to BigQuery must have the following permissions:

- `bigquery.datasets.get` permission on the target dataset
- `bigquery.tables.list` permission to list tables in the dataset
- `bigquery.tables.get` permission on the tables you want to ingest
- `bigquery.tables.getData` permission to read table data
- `bigquery.jobs.create` permission to run queries

## Example Configuration

Here’s an example of a typical BigQuery connection configuration:

| Field | Example Value |
| --- | --- |
| **Name** | Production BigQuery Analytics |
| **Dataset Name** | analytics\_dataset |
| **Service Account JSON Key File** | `{ "type": "service_account", "project_id": "...", ... }` |
| **Client Email** | `vendia-service@your-project.iam.gserviceaccount.com` |
| **Private Key** | `-----BEGIN PRIVATE KEY-----\n...` |
| **Project ID** | `your-google-cloud-project-id` |
| **Private Key ID** | `abc123def456ghi789` |
| **Client ID** | `123456789012345678901` |
| **Client X509 Cert URL** | `https://www.googleapis.com/robot/v1/metadata/x509/...` |

## Vendia Supported and Unsupported BigQuery Data Types

| Vendia Supported BigQuery Data Types | Vendia Unsupported BigQuery Data Types |
| --- | --- |
| BOOLEAN (BOOL) | ARRAY |
| BYTES | BIGNUMERIC (BIGDECIMAL) |
| DATE | GEOGRAPHY |
| DATETIME | INTERVAL |
| FLOAT64 (FLOAT) | JSON |
| INT64 (INTEGER, INT, BIGINT, etc.) | RANGE |
| NUMERIC (DECIMAL) | STRUCT (RECORD) |
| STRING |  |
| TIME |  |
| TIMESTAMP |  |

## Best Practices

- **Security**: Use service accounts with minimal required permissions instead of user accounts
- **Key Management**: Store private keys securely and rotate them regularly
- **Cost Optimization**: Use appropriate BigQuery slot reservations and partitioned tables
- **Testing**: Test connectivity with a small sample table before ingesting large datasets
- **Dataset Organization**: Use descriptive dataset and table names for better organization

## Troubleshooting

If you encounter connection issues:

1. **Authentication Failed**: Verify that service account credentials and JSON key file are valid
2. **Project Not Found**: Check that the Project ID is correct and accessible
3. **Dataset Not Found**: Confirm that the dataset exists and the service account has access
4. **Permission Denied**: Ensure that the service account has the required BigQuery permissions
5. **API Errors**: Verify that the BigQuery API is enabled for your Google Cloud Project
6. **Network Issues**: Check that firewall rules allow HTTPS connections to Google APIs
7. **Quota Exceeded**: Monitor BigQuery quotas and request increases if needed
8. **Invalid Private Key**: Ensure that the private key is in the correct PEM format

## Next Steps

After successfully connecting to your BigQuery dataset, you can:

- Select specific tables to ingest
- Configure data transformations and mappings
- Set up incremental data ingestion jobs
- Schedule regular data synchronization tasks