Block reference / Online services

BigQuery

Run SQL against BigQuery, or insert and upsert rows into a table, signed in with a service account.

In both appsWarpX BrowserWarpX Phone

Use this block to read data for a run from BigQuery, or to store what a run collected. Run a query runs standard SQL with named parameters and hands on the result rows. Insert rows adds rows to an existing table. Upsert rows (update or insert by key) updates rows whose key already exists and adds the rest.

The BigQuery block's settings, filled in as in the example below.

Settings

Service account

Press Select file (json) and choose the JSON key of a service account that can use BigQuery. The button then shows the file's name, and the bin icon removes the key. The block cannot run without one.

Exampleacme-analytics-bq-writer.json
Project ID

The project that runs the job and is billed for it. Leave it empty to use the key file's own project, which the field shows as its placeholder.

Exampleacme-analytics
Location

Where the job runs. Automatic (from the dataset) is right in most cases. Pick a region such as EU (European Union) only for a query that reads no table, or choose Other, type it in… to enter one the list does not have.

ExampleAutomatic (from the dataset)
Action

Run a query, Insert rows, or Upsert rows (update or insert by key).

ExampleRun a query
SQLwhen Action is Run a query

Standard SQL. Write values as named parameters, @status, and give them in Query parameters. The expand icon opens a larger editor.

Example
SELECT order_id, email, total
FROM shop.orders
WHERE status = @status
  AND order_date >= @from
ORDER BY order_date
Query parameterswhen Action is Run a query

Press Add parameter for each: its name, its type, and its value. The value can be a reference, and it reaches BigQuery as a value, so quotes or commas in it never break the SQL. An ARRAY value is a comma-separated list or a JSON list.

Example
@status | STRING | paid
@from | DATE | {{variables.from}}
@ids | ARRAY<INT64> | 1042, 1043
Maximum GB to scan (the query is cancelled above it)when Action is Run a query

A cost cap in gigabytes. BigQuery refuses the query if it would scan more, and the block fails. Leave it empty for no cap.

Example5
Assign to variablewhen Action is Run a query

Stores the result rows in a variable.

ExampleChecked
Variable namewhen Assign to variable is ticked

The variable for the rows. Later blocks read it as {{variables.paidOrders}}.

ExamplepaidOrders
Add the rows to the workflow table (matched by column name)when Action is Run a query

Adds each result row to the workflow's table, putting each value in the column with the same name.

ExampleUnchecked
Datasetwhen Action is Insert rows or Upsert rows

The dataset that holds the table to write to.

Exampleshop
Tablewhen Action is Insert rows or Upsert rows

The table to write to. It must already exist. Keys with no matching column are skipped, and columns the data does not have are left alone.

Exampleorders
Data fromwhen Action is Insert rows or Upsert rows

Where the rows come from: Variable, a variable holding a list of objects, or Table, the workflow's table. Each object is one row, keyed by column name.

ExampleTable
Variable namewhen Data from is Variable

The variable that holds the rows, as a bare name or as {{variables.orders}}.

Exampleorders
Key columnswhen Action is Upsert rows

The columns that identify a row, separated by commas, such as date, store_id. A row whose key columns match an existing row replaces it. The rest are added.

Exampleorder_id
Assign logs to variablewhen Action is Insert rows or Upsert rows

Saves how many rows were written and which keys had no matching column. The written data itself is not saved.

ExampleChecked
Variable namewhen Assign logs to variable is ticked

The variable for that summary, such as {"affectedRows": 42, "skippedKeys": []}.

ExampleupsertLog

Every block also has the shared block settings — a description, Disable block and what to do on an error. Fields that take text accept data from earlier blocks.

What it hands on

A query hands its rows to the next block as {{prevBlockData}}: a list of objects keyed by column name, such as [{"order_id": 1042, "email": "[email protected]", "total": 129.5}]. A statement that returns no rows, such as an UPDATE, hands on {"affectedRows": 12} instead. Insert hands on {"insertedRows": 40, "skippedKeys": []} and Upsert {"affectedRows": 40, "skippedKeys": []}. The block leaves by its single output.

Example: Store scraped order statuses, one row per order

  1. Start
  2. Open URL
  3. Get text
  4. BigQuery
Where BigQuery sits in the example workflow.
  1. In Google Cloud, create a service account in acme-analytics. Give it BigQuery Job User on the project and BigQuery Data Editor on the shop dataset, then download a JSON key.
  2. Make sure the table shop.orders exists with the columns order_id, status and checked_at.
  3. In the editor's Table, add the columns order_id, status and checked_at. Let the page blocks fill them with Insert to table.
  4. Add BigQuery at the end. Press Select file (json) and pick the key. Leave Project ID empty to use the key's project.
  5. Set Action to Upsert rows (update or insert by key), Dataset to shop, Table to orders, Data from to Table and Key columns to order_id.
  6. Tick Assign logs to variable and name it upsertLog. Run the workflow and open its log: the variable shows how many rows changed.

Good to know

  • The block signs in only with the service account key you pick. Unlike a sheet, there is nothing to share: grant the service account roles in Google Cloud instead. It needs BigQuery Job User on the project to run anything, BigQuery Data Viewer on a dataset to read it, and BigQuery Data Editor to insert or upsert.
  • The key file's whole contents are stored in the block, so an exported copy of the workflow carries the key. The WarpX Browser assistant never sees it and cannot set it.
  • Dry run under the SQL asks BigQuery how much the query would scan, such as The query would scan 1.2 GB, without running it. Parameters that hold references are replaced by a sample value of the right type for this check.
  • References written straight into SQL are pasted in as text before the query runs. That suits a table name, but values belong in Query parameters.
  • Parameter types: STRING, INT64, FLOAT64, NUMERIC, BOOL, DATE, DATETIME, TIMESTAMP, ARRAY<STRING>, ARRAY<INT64>. A name may be written with or without its @.
  • Result values are converted: whole numbers become numbers while they fit, BOOL becomes true or false, TIMESTAMP becomes text like 2026-09-19T08:30:00.000Z, and records become nested objects.
  • Insert and Upsert match keys to columns ignoring letter case. Keys with no column are skipped rather than failing, and listed as skippedKeys. Empty data writes nothing and does not fail.
  • Insert uses a load job, not streaming, so the rows can be updated or deleted right away. Write many rows in one block, from the table or a list variable, rather than one row per loop pass.
  • Upsert loads the data into a temporary table in the same dataset, merges it, then deletes it. If the delete fails, the temporary table removes itself after an hour. When the data has the same key twice, the last one wins, and only the columns the data has are updated.
  • Stopping the workflow stops the block from waiting. A job BigQuery has already started may still finish.

See also

  • Google Sheets — Read rows from a Google Sheet, keep only the ones you want, or write, append and clear cells.
  • Loop data — Run the blocks after it once for each row, number, list item or element.
  • JavaScript code — Run your own JavaScript in the page, and pass data back to the workflow.