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 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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "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.