Skip to main content

​
Installation

Install the BigQuery connector: 
npm install @evidence-dev/bigquery

​
Authentication Methods

BigQuery supports three authentication methods:
  1. Service Account (recommended for production)
  2. GCloud CLI (recommended for local development)
  3. OAuth Access Token

​
Configuration

​
Service Account Authentication

Create a service account in Google Cloud and download the JSON key file. 
name: bigquery
type: bigquery
options:
  project_id: ${BQ_PROJECT_ID}
  location: US
  authenticator: service-account
  client_email: ${BQ_CLIENT_EMAIL}
  private_key: ${BQ_PRIVATE_KEY}

​
GCloud CLI Authentication

Use your local gcloud credentials (requires gcloud auth login).
connection.yaml
name: bigquery
type: bigquery
options:
  project_id: ${BQ_PROJECT_ID}
  location: US
  authenticator: gcloud-cli

​
OAuth Access Token

Use a pre-obtained OAuth access token.
connection.yaml
name: bigquery
type: bigquery
options:
  project_id: ${BQ_PROJECT_ID}
  location: US
  authenticator: oauth
  token: ${BQ_ACCESS_TOKEN}

​
Configuration Parameters

​
project_id
string
required
Google Cloud project ID
​
location
string
default:"US"
BigQuery dataset location/region (e.g., US, EU, us-west1)
​
authenticator
string
default:"service-account"
required
Authentication method: service-account, gcloud-cli, or oauth

​
Service Account Parameters

​
client_email
string
required
Service account email address from the credentials JSON file
​
private_key
string
required
Private key from the credentials JSON file. Newlines should be preserved as \n
​
enable_connected_sheets
boolean
default:false
Enable access to Google Sheets. Adds additional OAuth scopes for Drive API access

​
OAuth Parameters

​
token
string
required
OAuth 2.0 access token

​
Using Credentials File

You can reference a credentials JSON file directly:
connection.yaml
name: bigquery
type: bigquery
options:
  project_id: ${keyfile.project_id}
  authenticator: service-account
  client_email: ${keyfile.client_email}
  private_key: ${keyfile.private_key}
  keyfile: ./path/to/credentials.json

​
Features

​
Type Mapping

BigQuery types are mapped to Evidence types:
  • Numbers: INT64, NUMERIC, BIGNUMERIC, FLOAT64, DECIMAL
  • Strings: STRING, BYTES, GEOGRAPHY, TIME
  • Dates: TIMESTAMP, DATE, DATETIME
  • Booleans: BOOL, BOOLEAN

​
Special Types

  • GEOGRAPHY: Returned as string values
  • TIME: Returned as string values
  • BYTES: Returned as base64-encoded strings
  • Large integers: Automatically converted to numbers

​
Streaming Results

The connector streams query results in batches (default 100,000 rows) for memory-efficient processing of large datasets.

​
Example Query

Create a SQL file in your Evidence project:
queries/sales_by_region.sql
SELECT 
  region,
  SUM(revenue) as total_revenue,
  COUNT(*) as order_count
FROM `my-project.analytics.sales`
WHERE order_date >= CURRENT_DATE() - 30
GROUP BY region
ORDER BY total_revenue DESC

​
Troubleshooting

Ensure newlines in the private key are escaped as \n. When using environment variables, the entire key should be on one line with escaped newlines:
BQ_PRIVATE_KEY="-----BEGIN PRIVATE KEY-----\nMIIE...\n-----END PRIVATE KEY-----"
Verify that:
  • The project ID is correct
  • The service account has the necessary permissions (BigQuery User, BigQuery Data Viewer)
  • Billing is enabled for the project
To query Google Sheets data, set enable_connected_sheets: true. This adds Drive API scopes to the service account.

Build docs developers (and LLMs) love

Get started for freeTalk to us