Google Bigquery

1. Overview

Google BigQuery is Google Cloud's fully managed analytical data warehouse. It is designed for large-scale SQL analytics, structured event storage, and operational reporting pipelines.

Through GoInsight's Google BigQuery node, you can integrate both analytical reads and structured row writes into your workflows. The current BigQuery toolset supports:

• Executing SQL queries with optional default dataset binding, bounded synchronous single-page retrieval, and continuation through NextPageToken.
• Inserting JSON rows into BigQuery tables using the tabledata.insertAll API.
• Returning agent-friendly metadata such as result summary, retry hint, and upstream HTTP diagnostics.

2. Prerequisites

Before using this node, make sure the following conditions are met:

• BigQuery API is enabled in your Google Cloud project.
• Your credential has valid OAuth access to the target project and datasets.
• The target tables and datasets already exist before write operations.
• For Insert Rows, the target project must support streaming inserts. BigQuery free tier / sandbox projects may reject insertAll with a 403 error.

3. Credentials

This node uses Google BigQuery OAuth credentials. For credential setup instructions, please refer to our official guide: Credentials Configuration Guide.

4. Supported Operations

Summary

Operation Details

Execute a SQL Query

Execute a synchronous SQL query in BigQuery and return parsed rows, schema information, total row count, one result page at a time, and query job metadata.

Input Parameters:

• ProjectId: Google Cloud project ID that runs the query job. Example: my-gcp-project
• Query: SQL query text to execute. Use GoogleSQL by default unless Legacy SQL is explicitly required. Example: SELECT 1 AS health_check

Options:

• DatasetId: Optional default dataset ID used to resolve unqualified table names.
• PageToken: Opaque pagination token returned by a previous response. Leave empty on the first request and pass back NextPageToken unchanged to continue the same result set.
• UseLegacySql: Whether to execute using BigQuery Legacy SQL dialect instead of GoogleSQL.
• MaxResults: Maximum number of rows returned in this response page before you must narrow the query or use NextPageToken for additional pages. Range: 1 to 10000.
• TimeoutMs: Maximum query wait time in milliseconds before returning incomplete status. Range: 1000 to 120000.

Output:

• Rows (object-array): Query result records. Each object maps selected column names to row values.
• Schema (object-array): Schema metadata for the returned rows. Each object includes Name, Type, and Mode.
• ReturnedCount (number): Number of rows returned in this response payload.
• TotalRows (number): Total number of rows matched by the query according to BigQuery.
• JobId (string): BigQuery query job identifier for diagnostics and traceability.
• JobComplete (bool): Whether BigQuery completed query execution within TimeoutMs.
• NextPageToken (string): Opaque pagination token for the next result page. Empty string means there are no more pages available for this query result set.
• Summary (string): One-sentence success summary for downstream agent orchestration.
• Hint (string): Actionable failure guidance when the request does not succeed.
• Retryable (bool): Whether retrying the same request may succeed.
• OriginalStatusCode (number): Upstream HTTP status code from BigQuery API. 0 means no upstream response was received.
• StatusCode (number): Tool-level status code. 200 means upstream completed and response parsed, -1 means local parameter validation error, 500 means local network/system error.
• ErrorMessage (string): Primary success/failure signal. Empty string means success.

Notes:

• This action returns one response page at a time. Pass NextPageToken back through PageToken until it becomes empty.
• This action is suitable for bounded analytical reads, validation checks, and row sampling.
• For large extraction or long-running ETL jobs, use asynchronous BigQuery job workflows instead of this synchronous action.

Insert Rows

Insert one or more JSON rows into a target BigQuery table using the tabledata.insertAll API.

Input Parameters:

• ProjectId: Google Cloud project ID that owns the target table.
• DatasetId: BigQuery dataset ID containing the destination table.
• TableId: BigQuery table ID that receives inserted rows.
• Rows: Array of JSON row objects to insert. Each object should match the table schema.

Options:

• SkipInvalidRows: Whether valid rows should still be inserted when some rows are invalid.
• IgnoreUnknownValues: Whether fields not defined in the table schema should be ignored.

Output:

• InsertedCount (number): Number of rows inserted successfully.
• InsertErrors (object-array): Row-level insertion errors. Each item includes row Index and upstream Errors.
• TableReference (string): Fully qualified table reference in project.dataset.table format.
• Summary (string): One-sentence success summary for downstream agent orchestration.
• Hint (string): Actionable failure guidance when the request does not succeed.
• Retryable (bool): Whether retrying the same request may succeed.
• OriginalStatusCode (number): Upstream HTTP status code from BigQuery API. 0 means no upstream response was received.
• StatusCode (number): Tool-level status code. 200 means upstream completed and response parsed, -1 means local parameter validation error, 500 means local network/system error.
• ErrorMessage (string): Primary success/failure signal. Empty string means success.

Warnings:

• This action performs a real write into the target table.
• Retries with the same row payload reuse deterministic insertId values to reduce duplicate writes within BigQuery's best-effort deduplication window.
• Intentionally repeating the same rows in separate logical operations can still create duplicate rows.
• BigQuery sandbox / free-tier projects may reject streaming inserts with:

Access Denied: BigQuery BigQuery: Streaming insert is not allowed in the free tier

Use a billed project when validating write behavior.

5. Example Usage

Scenario A: Quick health-check query

Use Execute a SQL Query with:

{
  "ProjectId": "my-gcp-project",
  "Query": "SELECT 1 AS health_check",
  "PageToken": "",
  "MaxResults": 100,
  "TimeoutMs": 30000
}

This is useful for validating that the credential, project access, and BigQuery API are all working correctly.

Scenario B: Insert analytics rows

Use Insert Rows with:

{
  "ProjectId": "my-gcp-project",
  "DatasetId": "gi_bigquery_test",
  "TableId": "daily_metrics",
  "Rows": [
    {
      "event_date": "2026-06-02",
      "platform": "android",
      "crash_count": 17,
      "health_check": 1
    }
  ],
  "SkipInvalidRows": false,
  "IgnoreUnknownValues": false
}

This is suitable for controlled validation writes into a dedicated test table.

6. FAQs

Q: Why does query work but insert fail with 403?

A: BigQuery insertAll uses streaming insert. If the target project is in free tier / sandbox mode, BigQuery may reject the write even though read APIs still work.

Q: How do I read the next page of query results?

A: Read NextPageToken from the previous response. If it is non-empty, pass it back unchanged as PageToken in the next Execute a SQL Query call for the same query result set.

Q: Do I have to create dataset and table before testing writes?

A: Yes. Insert Rows does not create datasets or tables. You must prepare the target dataset and table in advance.

Q: Will retrying Insert Rows always create duplicates?

A: Not always. The tool reuses deterministic insertId values for the same row payload during retries, which helps BigQuery perform best-effort deduplication. Separate logical writes with the same row content can still create duplicates.

Q: Does this tool support full-table export?

A: Not directly. Execute a SQL Query is synchronous and intended for bounded reads. For large extraction, use asynchronous BigQuery jobs or external export workflows.