# BigQuery Connector

Run SQL queries on Google Cloud BigQuery and save results to your data store.

## Why use this connector?

**Query directly from Valohai:** No need to export data manually. Write SQL, run execution, get CSV output.

**Version your queries:** Every query is saved with the execution. Reproduce results months later by checking which query ran when.

**Feed downstream jobs:** Query outputs get datum URLs. Use them as inputs in other executions or pipelines.

## Requirements

* BigQuery workspace on your GCP account with data.
* GCP Service Account with **BigQuery Data Viewer** and **BigQuery User** roles.
* Authentication via keyfile or machine identity.

## Authentication options

### Option 1: Machine identity (recommended)

If your Valohai workers run on GCP with a service account attached:

1. Attach the service account to your workers (contact Valohai support if needed)
2. Set environment variables:
   * `GCP_PROJECT`: Your GCP project ID
   * `GCP_IAM`: Set to `1`

### Option 2: Service account keyfile

If not using machine identity:

1. Create a service account with required roles
2. Download the JSON keyfile
3. Set environment variables:
   * `GCP_PROJECT`: Your GCP project ID
   * `GCP_IAM`: Set to `0`
   * `GCP_KEYFILE_CONTENTS_JSON`: Paste entire JSON keyfile (**mark as secret**)

## Add environment variables

Environment variables can be added:

* **Project-wide**: Project Settings → Environment Variables
* **Organization-wide**: Admin users can create environment variable groups that can be passed to several projects.
* **Per-execution**: Set when creating the execution

We recommend project or organization settings for credentials.

## Run a query

1. Open your project
2. Click **Create Execution**
3. Expand **valohai-ecosystem** → Select `bigquery-query`
4. Configure parameters:
   * **query**: Your SQL query
   * **output-path**: (Optional) Output filename, default is `results.csv`
   * **datum-alias**: (Optional) Alias for easy reference, e.g., `latest-sales-data`
5. Verify environment variables are set
6. Click **Create Execution**

### Example query

```sql
SELECT
    user_id,
    DATE(event_timestamp) as date,
    COUNT(*) as events,
    SUM(revenue) as total_revenue
FROM `my-project.analytics.events`
WHERE DATE(event_timestamp) >= '2025-01-01'
GROUP BY user_id, date
ORDER BY total_revenue DESC
LIMIT 1000
```

Results are saved as `results.csv` (or your custom output path) and uploaded to your data store.

## Use query results

The output of the execution gets a datum URL. Reference it in other executions by the URL directly or by using the datum alias shown in the example below:

```yaml
- step:
    name: train-model
    image: python:3.11
    command:
      - python train.py
    inputs:
      - name: training-data
        default: datum://latest-sales-data
```

Or use it in a pipeline by passing the execution output to the next node.

## Troubleshooting

### Authentication fails

**Check:**

* `GCP_PROJECT` matches your BigQuery project ID
* Service account has **BigQuery Data Viewer** and **BigQuery User** roles
* If using keyfile: `GCP_KEYFILE_CONTENTS_JSON` contains the full JSON (not just the path)
* If using machine identity: Worker service account is properly attached

### Query returns no results

BigQuery queries run successfully even if they return zero rows. Check your `WHERE` clauses and table names.

### Timeout on large queries

BigQuery has query execution limits. For very large datasets, consider:

* Adding `LIMIT` clauses for testing
* Breaking queries into smaller chunks
* Using BigQuery's table partitioning

## Next steps

**Other database connectors:**

* [AWS Redshift](/reusable-step-libraries/ecosystem-libraries/redshift.md)
* [Snowflake](/reusable-step-libraries/ecosystem-libraries/snowflake.md)

**Build your own:**

* [Create custom library steps](/reusable-step-libraries/build-your-own-library.md)


---

# 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://docs.valohai.com/reusable-step-libraries/ecosystem-libraries/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.