Google BigQuery

Google Cloud Analytics

Synopsis

Creates a Google BigQuery target that streams data directly into BigQuery tables using the streaming insert API. Supports multiple tables, custom schemas, and field normalization.

Schema

- name: <string>
  description: <string>
  type: bigquery
  pipelines: <pipeline[]>
  status: <boolean>
  properties:
    project_id: <string>
    dataset_id: <string>
    credentials: <string>
    batch_size: <numeric>
    max_bytes: <numeric>
    timeout: <numeric>
    drop_unknown_stream_events: <boolean>
    ignore_unknown_values: <boolean>
    skip_invalid_rows: <boolean>
    max_bad_records: <numeric>
    field_format: <string>
    tables:
      - name: <string>
        schema: <string>
    debug:
      status: <boolean>
      dont_send_logs: <boolean>

Configuration

The following fields are used to define the target:

Field	Required	Default	Description
name	Y	-	Target name
description	N	-	Optional description
type	Y	-	Must be bigquery
pipelines	N	-	Optional post-processor pipelines
status	N	true	Enable/disable the target

Google Cloud

Field	Required	Default	Description
project_id	Y	-	Google Cloud project ID
dataset_id	Y	-	BigQuery dataset ID
credentials	N	-	Service account credentials JSON (uses default credentials if not provided)

Tables

Field	Required	Default	Description
tables	Y	-	Array of target tables (at least one required). See Table Definitions below
tables[].name	Y	-	BigQuery table name. The table must already exist
tables[].schema	N	-	Optional schema definition string, recording the columns the table is expected to have. It is not applied to BigQuery. See Schema Format below

Streaming Options

Field	Required	Default	Description
drop_unknown_stream_events	N	true	Skip events for tables not defined in tables
batch_size	N	1000	Maximum number of rows per batch. Values above 10000 are clamped to 10000 with a warning
max_bytes	N	10485760	Maximum size in bytes of a single streaming insert request. 0 and values above 10485760 are clamped to 10485760; values between 1 and 256 are rejected
timeout	N	30	Seconds allowed for each table's insert request, including the retries the BigQuery client performs internally
ignore_unknown_values	N	false	Accept rows with values that don't match the schema
skip_invalid_rows	N	false	Skip rows with errors and insert valid rows
max_bad_records	N	0	Number of rejected rows to ignore per load. The default of 0 tolerates none, so any refused row surfaces as an error — it does not mean "no limit"
field_format	N	-	Data normalization format. See applicable Normalization section

Table Definitions

You can define multiple tables to stream data into:

targets:
  - name: bigquery_multiple_tables
    type: bigquery
    properties:
      tables:
        - name: "security_logs"
          schema: "timestamp:TIMESTAMP,message:STRING,severity:STRING"
        - name: "system_logs"
          schema: "timestamp:TIMESTAMP,message:STRING,level:STRING"

Schema Format

The schema format follows the pattern: field1:type1,field2:type2,...

Create the table in BigQuery with these columns before you start the target. The string records the layout you expect; it does not create or alter the table.

Supported types:

• STRING - Variable-length character data
• INTEGER or INT64 - 64-bit integer
• FLOAT or FLOAT64 - 64-bit floating point
• BOOLEAN or BOOL - True or false
• TIMESTAMP - Absolute point in time
• DATE - Calendar date
• TIME - Time of day
• DATETIME - Date and time
• BYTES - Binary data
• NUMERIC - Exact numeric value
• BIGNUMERIC - Larger numeric value
• GEOGRAPHY - Geographic data
• JSON - JSON data
• RECORD or STRUCT - Nested structure

Scheduling

See Scheduling and Pool Behavior for interval and cron fields shared by all targets.

Debug Options

Field	Required	Default	Description
debug.status	N	false	Enable debug logging
debug.dont_send_logs	N	false	Process logs but don't send to target (testing)

Details

The Google BigQuery target uses streaming inserts to send data in near real-time. Data is batched locally until batch_size is reached or when an explicit flush is triggered during finalization.

Each log event must specify its target table via the SystemS3 field — the value is matched against the name of an entry in tables. Events whose SystemS3 is empty or does not match any configured table are dropped if drop_unknown_stream_events is true (the default); otherwise they cause an error.

The target automatically parses JSON messages. If the message is not valid JSON, it creates a structured event with message and timestamp fields.

Authentication

The target supports two authentication methods:

1. Service Account JSON: Provide credentials directly in the configuration using credentials
2. Default Credentials: If credentials is not provided, the target uses Google Cloud's default credential chain (environment variables, gcloud CLI, GCE metadata service)

IAM Permissions

The service account requires the following IAM role:

IAM Role	Role ID	Purpose
BigQuery Data Editor	roles/bigquery.dataEditor	Insert rows into BigQuery tables via streaming API

Minimum permissions: bigquery.tables.updateData and bigquery.tables.get on each table in tables, and bigquery.datasets.get on the dataset in dataset_id. The streaming insert checks all three. Granting the role above on the dataset in dataset_id covers them. See Which permission is missing? for the errors a missing grant produces.

Error Handling

The target provides flexible error handling:

• ignore_unknown_values: Allows inserting rows with extra fields not in the schema
• skip_invalid_rows: Continues inserting valid rows even if some rows fail
• max_bad_records: How many rejected rows to ignore before the load is treated as failed. 0, the default, ignores none

Whenever BigQuery refuses rows, the count is logged as N of M rows were refused by table <name>, whatever these three settings are. With debug.status enabled, each refused row is logged individually with its column, message and reason. See Troubleshooting for how to read those rows.

note

BigQuery streaming inserts have quotas and limits. Ensure your project has adequate quota for your ingestion rate.

Examples

Basic

Minimum configuration using default credentials:

targets:
  - name: basic_bigquery
    type: bigquery
    properties:
      project_id: "my-project"
      dataset_id: "logs"
      tables:
        - name: "system_events"

With Credentials

Configuration with explicit service account credentials:

targets:
  - name: auth_bigquery
    type: bigquery
    properties:
      project_id: "my-project"
      dataset_id: "logs"
      tables:
        - name: "application_logs"
      credentials: |
        {
          "type": "service_account",
          "project_id": "my-project",
          "private_key_id": "key-id",
          "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
          "client_email": "service-account@my-project.iam.gserviceaccount.com",
          "client_id": "123456789",
          "auth_uri": "https://accounts.google.com/o/oauth2/auth",
          "token_uri": "https://oauth2.googleapis.com/token"
        }

Multiple Tables

Configuration with multiple target tables and schemas:

targets:
  - name: multi_table_bigquery
    type: bigquery
    properties:
      project_id: "my-project"
      dataset_id: "security_data"
      batch_size: 500
      tables:
        - name: "firewall_events"
          schema: "timestamp:TIMESTAMP,src_ip:STRING,dst_ip:STRING,action:STRING,bytes:INTEGER"
        - name: "authentication_events"
          schema: "timestamp:TIMESTAMP,username:STRING,success:BOOLEAN,source:STRING"
        - name: "dns_queries"
          schema: "timestamp:TIMESTAMP,query:STRING,response:STRING,client_ip:STRING"

High-Volume

Configuration optimized for high-volume streaming:

targets:
  - name: highvol_bigquery
    type: bigquery
    properties:
      project_id: "my-project"
      dataset_id: "metrics"
      tables:
        - name: "performance_data"
      batch_size: 5000
      timeout: 60
      skip_invalid_rows: true
      max_bad_records: 100

With Error Handling

Configuration with flexible error handling:

targets:
  - name: flexible_bigquery
    type: bigquery
    properties:
      project_id: "my-project"
      dataset_id: "logs"
      tables:
        - name: "app_logs"
      ignore_unknown_values: true
      skip_invalid_rows: true
      max_bad_records: 50

Normalized

Using field normalization for enhanced compatibility:

targets:
  - name: normalized_bigquery
    type: bigquery
    properties:
      project_id: "my-project"
      dataset_id: "security"
      tables:
        - name: "normalized_events"
      field_format: "ecs"

With Debugging

Configuration with debug options for testing:

targets:
  - name: debug_bigquery
    type: bigquery
    properties:
      project_id: "my-project"
      dataset_id: "logs"
      tables:
        - name: "test_events"
      debug:
        status: true
        dont_send_logs: true

Environment Variables

Using environment variables for sensitive data:

targets:
  - name: secure_bigquery
    type: bigquery
    properties:
      project_id: "${GCP_PROJECT_ID}"
      dataset_id: "${BIGQUERY_DATASET}"
      tables:
        - name: "secure_logs"
      credentials: "${GCP_CREDENTIALS_JSON}"

Troubleshooting

This section covers the errors you are most likely to see with the bigquery target, what causes each one, and how to fix it.

Where to look:

• Director logs. Target errors are tagged with the target name and carry "Section":"SenderPool". The part after Reason: or after the last colon is the actual cause.
• The target's connection status in the web interface. It shows the same reason as the log line, prefixed with connection failed for <target name>:.

See Target Delivery Errors for how Director logs and retries target failures.

Two facts about this target shape every entry below. Starting the target only reads and parses the credentials. No call is made to BigQuery, so a wrong project_id, a missing dataset or table, and a missing role are all reported on the first flush instead of at startup, while the connection status still shows connected. The target also never creates anything in BigQuery. The dataset and every table in tables must exist before you start it, with columns matching the fields you send.

Which permission is missing?

Match the error you see against this table first. The identity is the service account in credentials, or the one the host's default credentials resolve to when credentials is empty.

Error text	Missing role or permission	Grant it on
googleapi: Error 403 with reason accessDenied, typically naming bigquery.tables.updateData, bigquery.tables.get or bigquery.datasets.get	roles/bigquery.dataEditor	The dataset in dataset_id, or each table listed in tables
googleapi: Error 403 with reason accessNotConfigured	The BigQuery API, enabled	The project in project_id
googleapi: Error 403 that typically mentions billing, reason billingNotEnabled	Billing, enabled. Streaming inserts are not available without it	The project in project_id
auth: cannot fetch token with invalid_grant, or could not find default credentials	None. The credential itself cannot be used. See the two entries below	The Director host

The minimum for streaming inserts is roles/bigquery.dataEditor on the dataset. It carries the three permissions the insert checks: bigquery.tables.updateData and bigquery.tables.get on the table, and bigquery.datasets.get on the dataset. roles/bigquery.dataOwner and roles/bigquery.admin carry them too. A custom role needs all three. The target itself reads no metadata and runs no jobs, but BigQuery checks the two metadata permissions on the insert regardless, so a role holding only bigquery.tables.updateData fails on the first flush. When credentials is empty and Director runs on a Compute Engine VM, that VM's service account also needs the https://www.googleapis.com/auth/bigquery scope.

note

A new role assignment is picked up on the next retry. Director keeps redelivering the failed batch, so no restart is needed.

"failed to create BigQuery client" with "invalid character" or "could not find default credentials"

[Error] [director] [target-<target id>] [basic_bigquery] Failed to reinitialize target "basic_bigquery" (attempt 2). Reason: failed to create BigQuery client: bigquery: constructing client: invalid character '$' looking for beginning of value

Cause: the text in credentials is not a service account key body. invalid character '$' is the common case: the ${GCP_CREDENTIALS_JSON} reference was never expanded, so the literal placeholder reached the client as the key and parsing failed on its first character. invalid character '/' means a file path was pasted instead of the key, which is also what a $path{...} reference resolves to. Related shapes are credentials: unsupported unidentified file type for an OAuth client or user credentials file rather than a service account key, failed to parse private key when the \n sequences inside private_key were mangled by YAML quoting, and could not find default credentials when credentials is empty and the host has none. That last message carries either a credentials: or a google: prefix.

Fix: paste the whole key JSON with a block scalar, as in the With Credentials example above, or resolve it from a secret store with $secret{...}. A path is not accepted in this field. To use default credentials instead, point GOOGLE_APPLICATION_CREDENTIALS at an existing key file in the Director service's own environment, or run Director on a Compute Engine VM whose service account has the BigQuery scope.

Nothing is sent while this lasts. Incoming data waits in the queue and is delivered once the target starts.

"failed to resolve credentials"

[Error] [director] [target-<target id>] [basic_bigquery] Failed to reinitialize target "basic_bigquery" (attempt 1). Reason: failed to resolve credentials: credential: env variable "GCP_CREDENTIALS_JSON" is not set

Cause: the reference in credentials could not be resolved at all. env variable ... is not set means the variable is missing from the environment of the Director service, which is not the same environment as your shell. A $secret{...} reference instead reports secret with id ... not found, store ... not found in configuration, provider type ... is not registered, or the store's own error when it cannot be reached within 30 seconds.

Fix: export the variable for the Director service and restart the service, or correct the secret reference and confirm the store is reachable from the Director host.

Nothing is sent while this lasts, and the attempt counter grows for as long as the cause persists.

"googleapi: Error 403" with reason "accessDenied"

Sender worker 1 Finalize failed on flush for target "basic_bigquery": failed to insert rows into table my_table: googleapi: Error 403: <access denied message>, accessDenied

Cause: the credentials are valid, but the service account may not write to the table. The text before the reason typically names the missing permission, bigquery.tables.updateData, and the full table id in the form my-project:my_dataset.my_table. Because nothing is verified at startup, this is the first sign that the role was never granted. A 403 with reason accessNotConfigured, or one whose message mentions billing, points at the project rather than the dataset.

Fix: grant roles/bigquery.dataEditor to the service account on the dataset in dataset_id, and check that the BigQuery API and billing are enabled on the project in project_id.

The batch is retried until the role is in place, so nothing is lost, but the queue grows while the error persists.

"googleapi: Error 404" with reason "notFound"

Sender worker 1 Finalize failed on flush for target "basic_bigquery": failed to insert rows into table my_table: googleapi: Error 404: <not found message>, notFound

Cause: the dataset or the table does not exist as addressed. The message typically starts with Not found: Table or Not found: Dataset and repeats the id, which tells you which of the two is wrong. Dataset and table ids are case sensitive, so My_Table and my_table are different tables.

Fix: create the dataset and every table in tables in BigQuery first, then check project_id, dataset_id, and each tables[].name against the id shown in the message. Director never creates a table, and the schema string under tables[] is not applied to BigQuery, so it cannot add the missing columns either.

Retried until fixed. No data is lost, but nothing lands until the ids match.

"N of N rows were refused by table ..." and "too many bad records"

[Error] [director] [target-<target id>] [basic_bigquery] 1000 of 1000 rows were refused by table my_table
[Error] [director] [target-<target id>] [basic_bigquery] 999 of 1000 refused rows in table my_table were only abandoned because the insert is atomic — set skip_invalid_rows to true so they are delivered instead of dropped with the batch
[Error] [director] [target-<target id>] [basic_bigquery] Sender worker 1 Finalize failed on flush for target "basic_bigquery": record rejected by target: too many bad records (1000) for table my_table: 1000 row insertions failed (insertion of row [insertID: "<insert id>"; insertIndex: 1] failed with error: {Location: "src_ip"; Message: "no such field"; Reason: "invalid"}, ...)

Cause: BigQuery accepted the request and then refused rows inside it. Reason: "invalid" with Message: "no such field" means the row carries a field the table does not declare, and Location names it. A value that does not fit the column type reports invalid as well. Reason: "stopped" marks rows BigQuery never evaluated: with skip_invalid_rows at its default of false the insert is atomic, so one refused row abandons every sibling in the batch.

Fix: add the missing column to the table, or set ignore_unknown_values: true so BigQuery accepts the row and ignores fields the table does not declare. Set skip_invalid_rows: true so the rows BigQuery does accept land instead of being abandoned. Correct value types in a pipeline. If the refused rows carry messages that were not JSON, the table needs message and timestamp columns, because that is how such messages are wrapped.

This is data loss. The whole batch, including the rows marked stopped, is dropped after four deliveries, logged as dropping <payload> for target "basic_bigquery" after 4 rejected flush attempts.

"target not initialized (table: ...)" or "no table specified in SystemS3"

Sender worker 3 execute() failed for basic_bigquery: target not initialized (table: my_table)

Cause: the record names a table that has no entry in tables, or names no table at all, and drop_unknown_stream_events is set to false. The message is misleading, because the target is initialized. What is missing is a tables entry whose name matches the value the route or pipeline put in SystemS3. The comparison is exact, so a difference in case is enough.

Fix: add the table under tables, or correct the name the route sets. Where the second message appears, the route sets no table at all and has to be given one.

Retried until fixed. Every payload carrying that table is redelivered about every five seconds and never succeeds, so the queue grows. At the default drop_unknown_stream_events: true the same records are dropped silently instead.

The target is healthy but no rows arrive

Check these in order.

1. Events are dropped as unknown tables. With drop_unknown_stream_events at its default of true, any event whose SystemS3 value does not match an entry in tables is discarded without a log line. Check the target's dropped counters in the stats view. If they climb while the delivered count stays at zero, the route is not setting the table, or the name does not match. Set drop_unknown_stream_events: false temporarily to turn the drop into the visible error above.
2. debug.dont_send_logs is enabled. Events are processed and never sent. The Log sending is disabled line at startup is only written when debug.status is also true, so read the configuration rather than trusting the log.
3. Refused rows are being tolerated. This needs skip_invalid_rows: true as well, because at its default one refused row stops its siblings. With that set and max_bad_records above 0, up to that many refused rows per flush are discarded and the rest land. The only signs are the N of N rows were refused by table my_table line and the dropped counter.
4. Columns are dropped instead of rows. With ignore_unknown_values: true, BigQuery accepts rows and discards fields the table does not declare. Rows land with columns missing and nothing is logged. Add the columns, or set the flag back to false so the mismatch becomes visible.
5. A tables entry has an empty name. It is skipped at startup without a message, and everything routed to it is then dropped as an unknown table.

"record rejected by target: record size ... exceeds configured max_bytes ..."

Sender worker 2 execute() failed for basic_bigquery: record rejected by target: record size 12582976 exceeds configured max_bytes 10485504

Cause: one record on its own is larger than a single streaming insert request can carry. The number reported as max_bytes is the row budget, which is the configured value less the 256 byte request envelope, so it does not match what you set. Raising max_bytes does not help, because BigQuery's own limit for a single row is 10 MB as well.

Fix: trim the record in a pipeline before it reaches the target, for example by removing a large raw field. Batches are split automatically, so only a single oversized record produces this.

The record is dropped after four deliveries and is lost. If BigQuery instead refuses the whole request with googleapi: Error 400 naming the payload size, lower max_bytes to 8388608: that error is retried until fixed rather than dropped, so it blocks the queue until the value is lowered.

"quotaExceeded", "rateLimitExceeded" or "retry failed with context deadline exceeded"

Sender worker 1 Finalize failed on flush for target "basic_bigquery": failed to insert rows into table my_table: retry failed with context deadline exceeded; last error: googleapi: Error 403: <rate limit message>, rateLimitExceeded

Cause: BigQuery is throttling the insert. rateLimitExceeded and backendError are retried internally with a growing delay, and retry failed with context deadline exceeded means those retries ran past timeout. quotaExceeded, which covers the project's streaming insert quota for bytes, rows or requests per second, is not retried internally and appears immediately.

Fix: raise timeout from its default of 30 seconds so the internal retries have room, lower batch_size or the ingestion rate, spread the load over more tables, or request more streaming quota for the project.

Retried until fixed, so nothing is lost. Rows BigQuery accepted before the failure are sent again on redelivery, so duplicates are possible.

"no such host", "i/o timeout", "proxyconnect" or "x509: certificate signed by unknown authority"

Sender worker 1 Finalize failed on flush for target "basic_bigquery": failed to insert rows into table my_table: Post "https://bigquery.googleapis.com/bigquery/v2/projects/my-project/datasets/my_dataset/tables/my_table/insertAll": dial tcp: lookup bigquery.googleapis.com: no such host

Cause: the Director host cannot reach BigQuery. no such host is DNS, i/o timeout is a blocked egress path, proxyconnect tcp: ... connection refused is a proxy that is wrong or not running, x509: certificate signed by unknown authority is a TLS intercepting proxy whose certificate authority the host does not trust, and context deadline exceeded means the request took longer than timeout.

Fix: allow outbound HTTPS on port 443 from the Director host to bigquery.googleapis.com and to the token endpoint in the key's token_uri, normally oauth2.googleapis.com. Proxy settings come from the HTTPS_PROXY and NO_PROXY variables of the Director service, since the target has no proxy field of its own. There is no certificate authority or verification setting either, so an intercepting proxy's certificate authority has to be installed in the host trust store.

Retried until fixed. A request that timed out may still have landed its rows, so duplicates are possible.

"ValidateConfig failed" or a reinitialize attempt that repeats forever

[Error] [director] [target-<target id>] [basic_bigquery] ValidateConfig failed for target "basic_bigquery": at least one table must be configured
[Error] [director] [target-<target id>] [basic_bigquery] Failed to reinitialize target "basic_bigquery" (attempt 5). Reason: failed to parse schema for table my_table: unsupported field type: VARCHAR

Cause: a property is missing or out of range. project_id is required, dataset_id is required and at least one table must be configured name the field directly. batch_size must be greater than 0 and timeout must be greater than 0 reject zero and negative values, while a batch_size above 10000 is clamped with a warning instead of rejected. max_bytes must not be negative and max_bytes must exceed the 256-byte insertAll request envelope bound that field. The schema messages arrive later, during initialization rather than validation: each tables[].schema entry has to read name:TYPE with a type from Schema Format above. at least one table must be configured at that later stage means every tables entry has an empty name.

Fix: correct the property named in the message and save the target. The attempt counter keeps growing while the cause persists, so a high number is not a separate fault.

Nothing is sent until the configuration is valid. Incoming data waits in the queue.

The same rows appear twice in the table

Cause: a flush that failed after BigQuery had already accepted part of it. Director redelivers the whole batch, and each attempt uses a new insert id, so rows that landed the first time are written again. Timeouts, throttling and network failures during a flush all produce this.

Fix: no setting prevents it. Reduce the exposure by raising timeout and keeping batches within quota, and deduplicate when you query the table if exact counts matter.

No data is lost in this case. The cost is duplicated rows, and the target's delivered count includes both copies.