Skip to main content
Ask your AI

Google BigQuery

Server Source code Package

Server-side event streaming to Google BigQuery via the Storage Write API for low-latency analytics, machine learning workloads, and data warehousing. The @walkeros/server-destination-gcp package also ships destinationPubSub for publishing events to Pub/Sub topics; this page covers BigQuery only.

Where this fits

GCP BigQuery is a server destination in the walkerOS flow:

Streams events to Google BigQuery for data warehousing, analytics dashboards, and machine learning workloads.

Installation​

npm install @walkeros/collector @walkeros/server-destination-gcp
import { startFlow } from '@walkeros/collector';
import { destinationBigQuery } from '@walkeros/server-destination-gcp';

await startFlow({
  destinations: {
    bigquery: {
      code: destinationBigQuery,
      config: {
        settings: {
          projectId: 'YOUR_PROJECT_ID',
        },
      },
    },
  },
});

Configuration​

This destination uses the standard destination config wrapper (consent, data, env, id, ...). For the shared fields see destination configuration. Package-specific fields live under config.settings and are listed below.

Settings​

PropertyTypeDescriptionMore
clientanyGoogle Cloud BigQuery client instance (like new BigQuery({ projectId, keyFilename })). Used by walkeros setup only; event writes authenticate with config.credentials, settings.bigquery or the default credentials.
projectId*stringGoogle Cloud Project ID
datasetIdstringBigQuery dataset ID where events will be stored
tableIdstringBigQuery table ID for event storage
locationstringNot read by the destination. walkeros setup creates the dataset in config.setup.location (like US), default EU.
bigqueryanyAdditional BigQuery client configuration options
* Required fields

Mapping​

This package does not define custom rule-level settings. For the standard rule fields (consent, condition, data, batch, name, policy) see mapping.

Examples

Page view

A page view is appended as one row through the BigQuery Storage Write API JSONWriter. Nested objects/arrays in data, source, etc. are JSON-stringified by eventToRow.

Event
{
  "name": "page view",
  "data": {
    "title": "Documentation",
    "url": "https://example.com/docs"
  },
  "context": {
    "dev": [
      "test",
      1
    ]
  },
  "globals": {
    "pagegroup": "docs"
  },
  "custom": {
    "completely": "random"
  },
  "user": {
    "id": "us3r",
    "device": "c00k13",
    "session": "s3ss10n"
  },
  "nested": [
    {
      "entity": "child",
      "data": {
        "is": "subordinated"
      }
    }
  ],
  "consent": {
    "functional": true
  },
  "id": "f0cc5f6fec3d8eeb",
  "trigger": "load",
  "entity": "page",
  "action": "view",
  "timestamp": 1700001100000,
  "timing": 3.14,
  "source": {
    "count": 1,
    "trace": "0a1b2c3d4e5f60718293a4b5c6d7e8f9",
    "type": "express",
    "platform": "server"
  }
}
Out
appendRows([
  {
    "name": "page view",
    "data": "{\"title\":\"Documentation\",\"url\":\"https://example.com/docs\"}",
    "context": "{\"dev\":[\"test\",1]}",
    "globals": "{\"pagegroup\":\"docs\"}",
    "custom": "{\"completely\":\"random\"}",
    "user": "{\"id\":\"us3r\",\"device\":\"c00k13\",\"session\":\"s3ss10n\"}",
    "nested": "[{\"entity\":\"child\",\"data\":{\"is\":\"subordinated\"}}]",
    "consent": "{\"functional\":true}",
    "id": "f0cc5f6fec3d8eeb",
    "trigger": "load",
    "entity": "page",
    "action": "view",
    "timestamp": 1700001100000000,
    "timing": 3.14,
    "source": "{\"count\":1,\"trace\":\"0a1b2c3d4e5f60718293a4b5c6d7e8f9\",\"type\":\"express\",\"platform\":\"server\"}"
  }
])

Purchase

An order event is appended as a single row through JSONWriter.appendRows. The entire nested data object (including arrays like items) is JSON-stringified into the data column via eventToRow().

Event
{
  "name": "order complete",
  "data": {
    "id": "ORD-500",
    "value": 199.99,
    "items": [
      {
        "sku": "SKU-1",
        "qty": 2
      }
    ]
  },
  "context": {
    "shopping": [
      "complete",
      0
    ]
  },
  "globals": {
    "pagegroup": "shop"
  },
  "custom": {
    "completely": "random"
  },
  "user": {
    "id": "us3r",
    "device": "c00k13",
    "session": "s3ss10n"
  },
  "nested": [
    {
      "entity": "product",
      "data": {
        "id": "ers",
        "name": "Everyday Ruck Snack",
        "color": "black",
        "size": "l",
        "price": 420
      },
      "context": {
        "shopping": [
          "complete",
          0
        ]
      },
      "nested": []
    },
    {
      "entity": "product",
      "data": {
        "id": "cc",
        "name": "Cool Cap",
        "size": "one size",
        "price": 42
      },
      "context": {
        "shopping": [
          "complete",
          0
        ]
      },
      "nested": []
    },
    {
      "entity": "gift",
      "data": {
        "name": "Surprise"
      },
      "context": {
        "shopping": [
          "complete",
          0
        ]
      },
      "nested": []
    }
  ],
  "consent": {
    "functional": true
  },
  "id": "235d0d7bd7a213f2",
  "trigger": "load",
  "entity": "order",
  "action": "complete",
  "timestamp": 1700001101000,
  "timing": 3.14,
  "source": {
    "count": 1,
    "trace": "0a1b2c3d4e5f60718293a4b5c6d7e8f9",
    "type": "express",
    "platform": "server"
  }
}
Out
appendRows([
  {
    "name": "order complete",
    "data": "{\"id\":\"ORD-500\",\"value\":199.99,\"items\":[{\"sku\":\"SKU-1\",\"qty\":2}]}",
    "context": "{\"shopping\":[\"complete\",0]}",
    "globals": "{\"pagegroup\":\"shop\"}",
    "custom": "{\"completely\":\"random\"}",
    "user": "{\"id\":\"us3r\",\"device\":\"c00k13\",\"session\":\"s3ss10n\"}",
    "nested": "[{\"entity\":\"product\",\"data\":{\"id\":\"ers\",\"name\":\"Everyday Ruck Snack\",\"color\":\"black\",\"size\":\"l\",\"price\":420},\"context\":{\"shopping\":[\"complete\",0]},\"nested\":[]},{\"entity\":\"product\",\"data\":{\"id\":\"cc\",\"name\":\"Cool Cap\",\"size\":\"one size\",\"price\":42},\"context\":{\"shopping\":[\"complete\",0]},\"nested\":[]},{\"entity\":\"gift\",\"data\":{\"name\":\"Surprise\"},\"context\":{\"shopping\":[\"complete\",0]},\"nested\":[]}]",
    "consent": "{\"functional\":true}",
    "id": "235d0d7bd7a213f2",
    "trigger": "load",
    "entity": "order",
    "action": "complete",
    "timestamp": 1700001101000000,
    "timing": 3.14,
    "source": "{\"count\":1,\"trace\":\"0a1b2c3d4e5f60718293a4b5c6d7e8f9\",\"type\":\"express\",\"platform\":\"server\"}"
  }
])

When it fails

  • append failed: A failed Storage Write append fails the push, retryable, instead of counting as delivered. Reported as failed with code transport.

Prerequisites​

Setup lifecycle​

The destination writes to an existing table and never creates one while it runs. Provision the dataset and table once per environment: set config.setup on the destination, then run the CLI.

"bigquery": {
"package": "@walkeros/server-destination-gcp",
"import": "destinationBigQuery",
"config": {
"setup": true,
"settings": { "projectId": "YOUR_PROJECT_ID" }
}
}
walkeros setup destination.bigquery

Output: a narrated setup: ok destination.bigquery line. With --json the command prints a JSON envelope instead, with the result under data.result: { datasetCreated, tableCreated }. The command is idempotent, safe to re-run.

config.setup controls provisioning:

  • omitted or false: the command provisions nothing and prints setup: skipped destination.bigquery (config.setup is unset) (or is false).
  • true: provision with the defaults below.
  • an object with the Setup fields location, storageBillingModel, partitioning, clustering and schema: provision with these overrides; fields you leave out keep the defaults.

Defaults​

FieldValue
datasetIdwalkerOS (note capital O, S)
tableIdevents
locationEU
storageBillingModelPHYSICAL (cheaper for compressible JSON)
PartitioningDay partitioning on timestamp
Clustering(name, entity, action)

datasetId and tableId come from settings. The dataset location comes only from config.setup: settings.location is not read, so a dataset in another region needs "setup": { "location": "US" }.

Cost optimization

Physical storage billing charges based on compressed size. Day partitioning and the (name, entity, action) clustering reduce scan costs for typical analytics queries. Always include a timestamp filter.

Drift handling​

If the existing table's partitioning, clustering, or schema differs from the declared configuration, setup logs [destination:<id>] setup.drift {...} (a warning line) and continues. There is no auto-mutation. Migrations are an operator decision.

GCP setup​

Enable BigQuery API​

gcloud services enable bigquery.googleapis.com

Create service accounts​

The provisioning step (setup) and the runtime push path need different permissions. We recommend separating them.

Operator (setup) service account, used by walkeros setup:

  • bigquery.datasets.create
  • bigquery.tables.create
  • bigquery.datasets.get (existence check)
  • bigquery.tables.get (existence check and drift detection)

Runtime service account, used by the running flow:

  • bigquery.tables.updateData (Storage Write API append)
# Runtime service account
gcloud iam service-accounts create walkeros-flow \
  --display-name="walkerOS Flow Runtime"

# Grant Storage Write API append access
gcloud projects add-iam-policy-binding YOUR_PROJECT_ID \
  --member="serviceAccount:walkeros-flow@YOUR_PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataEditor"

Authentication​

For environments where you need explicit credentials (Docker containers, external platforms):

gcloud iam service-accounts keys create ./sa-bigquery.json \
  --iam-account=walkeros-flow@YOUR_PROJECT_ID.iam.gserviceaccount.com

Set the environment variable to use the key:

export GOOGLE_APPLICATION_CREDENTIALS=/path/to/sa-bigquery.json
caution

Keep key files secure. Never commit them to version control or include in public Docker images.

Inline credentials

Instead of relying on GOOGLE_APPLICATION_CREDENTIALS, put the service account key on config.credentials, next to settings, as a $secret whose value is the key file's JSON: "credentials": "$secret.BIGQUERY_SA_KEY". The destination parses the JSON and uses the key for both the control plane (setup, metadata) and the data plane (Storage Write API ingestion). If the secret is unset, the flow stops at startup; if the value is not a service account key's JSON, the destination fails to start with Invalid credentials JSON. An unset $env reference without a default leaves config.credentials empty instead: the destination then authenticates with Application Default Credentials, possibly as another identity, and nothing reports it.

For lower-level control you can still pass auth options through the raw settings.bigquery passthrough (for example keyFilename or credentials). A pre-built settings.client authenticates the control plane only; supply config.credentials or settings.bigquery for the data plane to use non-ADC credentials.

Environment variables​

The destination itself reads no environment variables. The Google client libraries read GOOGLE_APPLICATION_CREDENTIALS (the path to a service account key) when no credentials are configured. To take the project, dataset, table or location from the environment, reference the variables in the flow with $env, as the example flow of the Cloud Run guide does:

"config": {
"setup": { "location": "$env.BQ_LOCATION:EU" },
"settings": {
"projectId": "$env.GCP_PROJECT_ID",
"datasetId": "$env.BQ_DATASET:walkerOS",
"tableId": "$env.BQ_TABLE:events"
}
}

Storage Write API (data plane)​

The destination uses BigQuery's Storage Write API for data ingestion.

  • Cost: Storage Write API ingestion is billed per byte with a monthly free tier; see BigQuery pricing.
  • Rejected appends: an append the server rejects (quota, permission, a dropped table) fails the push. The failure is marked retryable only for the statuses the Storage Write SDK retries itself.
  • First use: the writer opens at the first push, not at startup; a missing dataset or table fails that push with a message to run walkeros setup (set config.setup first, see Setup lifecycle). The collector logs the first failure of each cause as an ERROR line and counts repeats within a minute in status.destinations.<id>.failed; the messages name codes, not row data.
  • Batching: pushBatch is implemented. Set config.batch on the destination (a bare number is the debounce wait in ms) to flush all events in a window as a single appendRows call. No '* *' wildcard mapping rule is needed. A failed append fails the whole batch; rows the server rejects inside an accepted append fail on their own. The collector counts every failed event as failed and keeps it in its dead-letter buffer.

walkeros push --simulate destination.<id> runs offline: the package's mock env replaces the Storage Write API client, so nothing reaches BigQuery, and the output shows each JSONWriter.appendRows call with the rows it would write.

EXPERIMENTAL SDK

The upstream @google-cloud/bigquery-storage package self-marks as EXPERIMENTAL (subject to change). Pinned at ^5.1.0.

Default table schema​

The default 15-column schema follows the walkerOS Event v4 canonical order. Object fields use the native JSON BigQuery type. Only name is REQUIRED; all other columns are NULLABLE for resilience against partial events.

ColumnTypeMode
nameSTRINGREQUIRED
dataJSONNULLABLE
contextJSONNULLABLE
globalsJSONNULLABLE
customJSONNULLABLE
userJSONNULLABLE
nestedJSONNULLABLE
consentJSONNULLABLE
idSTRINGNULLABLE
triggerSTRINGNULLABLE
entitySTRINGNULLABLE
actionSTRINGNULLABLE
timestampTIMESTAMPNULLABLE
timingFLOAT64NULLABLE
sourceJSONNULLABLE

There is no createdAt column. Use timestamp (event time) for partition filters.

Custom schema mapping​

You can send a custom schema by using the data configuration to map specific fields. This is useful when you only need a subset of the event data. The mapped object is the row, so its keys must match the table's columns.

Example: simple schema​

This example sends only name, id, data, and timestamp:

import { startFlow } from '@walkeros/collector';
import { destinationBigQuery } from '@walkeros/server-destination-gcp';

await startFlow({
  destinations: {
    bigquery: {
      code: destinationBigQuery,
      config: {
        settings: {
          projectId: 'YOUR_PROJECT_ID',
          datasetId: 'YOUR_DATASET_ID',
          tableId: 'events_simple',
        },
        data: {
          map: {
            name: 'name',
            id: 'id',
            data: { fn: (event) => JSON.stringify(event.data) },
            timestamp: 'timestamp',
          },
        },
      },
    },
  },
});

With the corresponding simpler table:

CREATE TABLE IF NOT EXISTS `YOUR_PROJECT_ID.YOUR_DATASET_ID.events_simple` (
  name STRING,
  id STRING,
  data STRING,
  timestamp INT64
);

The Storage Write API encodes each value for its column type. A STRING or JSON column takes text, so fn serializes the object in data; an object value would fail the push. A TIMESTAMP column takes microseconds while the event timestamp is in milliseconds, so this table keeps it as INT64 milliseconds. A millisecond value mapped into a TIMESTAMP column is stored as a date in January 1970, and the push still succeeds.

Cleanup​

To remove BigQuery resources:

  • Delete the BigQuery dataset
  • Remove service account IAM bindings from the dataset
  • Delete the service account
  • Remove any downloaded key files
💡 Need implementation support?
elbwalker offers hands-on support: setup review, measurement planning, destination mapping, and live troubleshooting. Book a 2-hour session (€399)