<!-- BusyBot node reference — https://busybot.net/tools/google-bigquery/ -->

> Node: Google BigQuery (`google_bigquery`) · Action · v1
> Category: Data & Storage · Credentials: Google BigQuery OAuth2 (`googleBigQueryOAuth2Api`), Google Service Account (`googleApi`)
> Updated: 2026-08-16

# Google BigQuery

> Execute SQL queries and insert rows into Google BigQuery tables via the BigQuery REST API.

## Overview

Execute SQL queries and insert rows into Google BigQuery tables via the BigQuery REST API.

**Category:** Data & Storage  
**Tool Name:** `google_bigquery`  
**Version:** 1

**Appearance:** Icon: `lucide-Database` | Color: `#4285F4`

## Node Type

**Action** — processes input items and produces output

## Input / Output

| Direction | Port(s) |
|-----------|--------|
| Input | `main` |
| Output | `main`, `error` |

## Credentials

This tool supports two authentication methods — configure **one** of them: **Google BigQuery OAuth2** or **Google Service Account**.
See the [Credentials Guide](https://busybot.net/credentials/) for setup instructions.

### Operations

| Operation | Value | Description |
|-----------|-------|-------------|
| Execute Query | `executeQuery` | Execute a SQL query |
| Insert | `insert` | Insert rows in a table |

### Parameters

#### Execute Query (`executeQuery`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| SQL Query | `string` | Yes | — | SQL query to execute. Standard SQL by default; enable "Use Legacy SQL" option for legacy syntax. |
| Return All | `boolean` | No | `false` | Whether to return all result rows or only up to the specified limit. |
| Limit | `number` | No | `50` | Maximum number of rows to return. _(shown when Return All is `false`)_ |
| Query Parameters (Named) | `fixedCollection` | No | `{}` | Named parameters for parameterized queries (standard SQL only). Use @paramName in the query. |
| — Name | `string` | No | — | Parameter name (without the @ prefix). |
| — Value | `string` | No | — | The string value for this parameter. |
| Options (`queryOptions`) | `collection` | No | `{}` | Query behavior and output shaping. |
| — Default Dataset | `string` | No | — | If not set, all table names must be fully qualified (datasetId.tableId). |
| — Dry Run | `boolean` | No | `false` | If true, BigQuery validates but does not execute the query. Returns statistics (bytes processed, etc.). |
| — Include Schema in Output | `boolean` | No | `false` | Whether to include a _schema key in each output row with the table schema. |
| — Location (Region) | `string` | No | — | Location or region where the data will be processed. Required when the dataset is in a non-US/EU location. |
| — Maximum Bytes Billed | `string` | No | — | Limits bytes billed for this query. Queries exceeding this will fail without charge. |
| — Max Results Per Page | `number` | No | `1000` | Maximum number of rows per result page. Does not affect the total rows returned. |
| — Timeout (ms) | `number` | No | `10000` | How long to wait for query completion in milliseconds. |
| — Raw Output | `boolean` | No | `false` | Return the raw BigQuery API response instead of simplified row objects. |
| — Return Integers as Numbers | `boolean` | No | `false` | When enabled, integer and numeric values are returned as JavaScript numbers instead of strings. |
| — Use Legacy SQL | `boolean` | No | `false` | Use BigQuery's legacy SQL dialect instead of standard SQL. |

#### Insert (`insert`)

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Dataset ID | `string` | Yes | — | The ID of the BigQuery dataset containing the target table. |
| Table ID | `string` | Yes | — | The ID of the BigQuery table to insert rows into. |
| Data Mode | `options` | No | `autoMap` | How to map incoming item data to BigQuery table columns. |
| | | | | Options: `autoMap` (automatically map input fields to table columns by matching field names to schema field names), `define` (manually specify each field name and value to insert) |
| Fields to Send | `fixedCollection` | No | `{}` | Field name/value pairs to insert (used in "Map Each Field Below" mode). _(shown when Data Mode is `define`)_ |
| — Field Name | `string` | No | — | The BigQuery table column name. |
| — Field Value | `string` | No | — | The value to insert into this column. |
| Options (`insertOptions`) | `collection` | No | `{}` | Insert behavior. |
| — Batch Size | `number` | No | `100` | Number of rows to send per insertAll API request. Increase for higher throughput, decrease to avoid payload size limits. |
| — Ignore Unknown Values | `boolean` | No | `false` | Whether to accept rows with values for fields not present in the table schema. |
| — Skip Invalid Rows | `boolean` | No | `false` | Whether to insert valid rows even when some rows are invalid. |
| — Template Suffix | `string` | No | — | Creates a new table named {destinationTable}{templateSuffix} and inserts rows there. |
| — Trace ID | `string` | No | — | Unique request ID for debugging (UUID recommended). If omitted, a UUID v4 is auto-generated. |

#### All Operations

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| Authentication | `options` | No | `oAuth2` | Authentication method to use. |
| | | | | Options: `oAuth2` (recommended), `serviceAccount` |
| Google Account | `credential` | No | — | Connect or select your Google account. _(shown when Authentication is `oAuth2`)_ |
| Service Account Email | `string` | Yes | — | The email address of the Google service account. _(shown when Authentication is `serviceAccount`)_ |
| Private Key | `string` | Yes | — | The private key from the service account JSON key file. _(shown when Authentication is `serviceAccount`)_ |
| Project ID | `string` | Yes | — | The Google Cloud project ID that contains the BigQuery dataset. |
| Max Concurrency | `number` | No | `5` | Maximum number of items to process concurrently. Accepts 1–20. |

## Output Data

This node **replaces** the item JSON rather than merging into it — output items carry the query rows or the insert summary and nothing else.

**Execute Query** fans out: each input item runs its own query, and every returned row becomes its own output item. Rows are simplified against the result schema, so each column is a top-level property named after the column:

```json
{
  "customer_id": "1042",
  "order_count": "17",
  "lifetime_value": "8420.50"
}
```

- Values arrive as **strings** by default, the way the BigQuery REST API returns them. Turn on **Return Integers as Numbers** to receive integer and numeric columns as JavaScript numbers.
- With **Include Schema in Output** on, each row also carries `_schema` — the list of field definitions for the result.
- With **Raw Output** on, or on a **Dry Run**, the node emits one item holding BigQuery's unmodified API response instead of simplified rows. A dry run is how you check what a query would cost without running it.
- A query that returns no rows at all emits a single item carrying `success: true`.

**Insert** works differently: every input item is treated as a row and the whole batch is written together, so the node emits **one** summary item for the run rather than one item per input:

```json
{
  "success": true,
  "insertedCount": 250,
  "totalRows": 252,
  "errorCount": 2,
  "errors": [
    { "index": 17, "message": "Row 17 failed: …" }
  ]
}
```

- `errors[].index` is the position of the failing row in the batch, so you can trace it back to the input item.
- With **Skip Invalid Rows** on, BigQuery writes the valid rows and the rejected ones are not reported as item errors.
- If every row fails, the node raises an error for the node as a whole rather than returning a summary.

Reference results downstream by column name, e.g. `{{ $json.customer_id }}` or `{{ $json.insertedCount }}`.

## Usage Examples

- Use Google BigQuery in a workflow to execute SQL queries and insert rows into Google BigQuery tables via the BigQuery REST API

## Example Configuration

Run a query and return the first 100 rows:

```json
{
  "type": "google_bigquery",
  "parameters": {
    "authentication": "oAuth2",
    "operation": "executeQuery",
    "projectId": "my-gcp-project",
    "sqlQuery": "SELECT customer_id, COUNT(*) AS order_count FROM `sales.orders` GROUP BY customer_id",
    "returnAll": false,
    "limit": 100
  }
}
```

Run a parameterized query — reference each parameter as `@name` in the SQL:

```json
{
  "type": "google_bigquery",
  "parameters": {
    "authentication": "oAuth2",
    "operation": "executeQuery",
    "projectId": "my-gcp-project",
    "sqlQuery": "SELECT * FROM `sales.orders` WHERE status = @status AND total > @minTotal",
    "returnAll": true,
    "queryParameters": {
      "namedParameters": [
        { "name": "status", "value": "{{ $json.status }}" },
        { "name": "minTotal", "value": "1000" }
      ]
    },
    "queryOptions": {
      "defaultDataset": "sales",
      "location": "europe-west3",
      "returnAsNumbers": true,
      "maximumBytesBilled": "1000000000"
    }
  }
}
```

Validate a query and see what it would cost, without running it:

```json
{
  "type": "google_bigquery",
  "parameters": {
    "authentication": "oAuth2",
    "operation": "executeQuery",
    "projectId": "my-gcp-project",
    "sqlQuery": "SELECT * FROM `sales.orders`",
    "queryOptions": {
      "dryRun": true,
      "includeSchema": true,
      "timeoutMs": 10000
    }
  }
}
```

Insert every incoming item as a row, matching properties to column names:

```json
{
  "type": "google_bigquery",
  "parameters": {
    "authentication": "serviceAccount",
    "serviceAccountEmail": "my-service-account@my-gcp-project.iam.gserviceaccount.com",
    "operation": "insert",
    "projectId": "my-gcp-project",
    "datasetId": "analytics",
    "tableId": "events",
    "dataMode": "autoMap",
    "insertOptions": {
      "batchSize": 500,
      "ignoreUnknownValues": true,
      "skipInvalidRows": true
    }
  }
}
```

Insert with each column mapped explicitly:

```json
{
  "type": "google_bigquery",
  "parameters": {
    "authentication": "oAuth2",
    "operation": "insert",
    "projectId": "my-gcp-project",
    "datasetId": "analytics",
    "tableId": "events",
    "dataMode": "define",
    "fields": {
      "values": [
        { "fieldId": "event_name", "fieldValue": "{{ $json.name }}" },
        { "fieldId": "user_id", "fieldValue": "{{ $json.userId }}" },
        { "fieldId": "occurred_at", "fieldValue": "{{ $json.timestamp }}" }
      ]
    },
    "insertOptions": {
      "templateSuffix": "_2026"
    }
  }
}
```

### Error Handling

| Mode | Behavior |
|------|----------|
| **stop** | Halts workflow on first error |
| **continue** | Skips failed items, passes successful ones through |
| **errorPort** | Routes failed items to Error output port |

## Tips

Execute SQL queries and insert rows into Google BigQuery tables with support for parameterized queries and multiple SQL dialects. Use when you need to run analytical queries on large datasets or load data into BigQuery. Returns query result rows or insert confirmation.