Target-BigQuery

A rare 💎 you have stumbled upon

target-bigquery is a Singer target for BigQuery.

It is the most versatile target for BigQuery. Extremely performant, resource efficient, and fast in all configurations enabling 20 different ingestion patterns. Denormalized variants indicate data is unpacked during load with a resultant schema in BigQuery based on the tap schema. Non-denormalized means we have a fixed schema which loads all data into an unstructured JSON column. They are both useful patterns. The latter allowing BigQuery to work with schemaless or rapidly changing sources such as MongoDB instantly, while the former is more performant and convenient to start modeling quickly.

Patterns 🛠 (more details below)

• Batch Job, Denormalized, Overwrite
• Batch Job, Denormalized, Upsert
• Batch Job, Denormalized, Append
• Batch Job, Fixed Schema, Overwrite
• Batch Job, Fixed Schema, Append
• GCS Staging Data Lake, Denormalized, Overwrite
• GCS Staging Data Lake, Denormalized, Upsert
• GCS Staging Data Lake, Denormalized, Append
• GCS Staging Data Lake, Fixed Schema, Overwrite
• GCS Staging Data Lake, Fixed Schema, Append
• Storage Write API, Denormalized, Overwrite
• Storage Write API, Denormalized, Upsert
• Storage Write API, Denormalized, Append
• Storage Write API, Fixed Schema, Overwrite
• Storage Write API, Fixed Schema, Append
• Legacy Streaming API, Denormalized, Overwrite
• Legacy Streaming API, Denormalized, Upsert
• Legacy Streaming API, Denormalized, Append
• Legacy Streaming API, Fixed Schema, Overwrite
• Legacy Streaming API, Fixed Schema, Append

Installation 📈

The package on pypi is named z3-target-bigquery but the executable it ships with is simply target-bigquery. This allows me to release work without concerns of naming conflicts on the package index.

# Use pipx or pip
pipx install z3-target-bigquery
# Verify it is installed
target-bigquery --version

Usage Notes

Denormalization

So denormalized is a loaded term, so lets clarify here what we mean by it:

> In the context of JSON objects, denormalization refers to the process of flattening a hierarchical or nested JSON object into a simpler, more "denormalized" structure that can be easier to work with for certain use cases.

Denormalized=False (default) means we load all data into a single JSON column. This means all access requires an accessor such as SELECT data.my_column FROM table instead of SELECT my_column FROM table. Hence the term denormalized is relative to the data column which is the default for this target. This is a tradeoff between convenience/resilience and performance. This is the default because most resilient. IE a load will never fail due to a schema change or an invalid schema. You can always denormalize later via dbt. However, it is not the most performant and slower to transform. If your tap has a high quality and consistent schema, denormalization is the way to go to get the best performance and start modeling quickly.

Denormalized=True means we unpack the data into a schema which is derived from the tap schema. It does not mean we will flatten the data. There is a separate option for flattening. We will convert arrays to repeated fields and records to structs. All top level keys will end up as columns which is was you might expect from more typical targets.

Resolver Versions

There are 2 resolver versions. The config option schema_resolver_version lets you select which version you want to use. This versioning exists because we want to support evolving how we resolve schemas whilst not creating breaking changes for long-time users dependent on how a schema is resolved. The default is 1 which behaves very similarly to existing flavors of target-bigquery. It works well enough but has plenty of edge cases where it simply cannot resolve valid jsonschemas to a bq schema. The new version 2 is much more robust and will resolve most, if not all schemas due to it falling back to JSON when in doubt. You must opt-in to this version by setting schema_resolver_version: 2 in your config.

Overwrite vs Append

Sometimes you want to overwrite a table on every load. This can be achieved by either setting overwrite: true which will full refresh ALL tables or setting overwrite: [table1, table2, table_*_other, !table_v1_other] which will only overwrite the specified tables and supports pattern matching. This is useful if you have a table which is a lookup table and you want to overwrite it on every load. You can also set overwrite: false which will append to the table. This is the default behavior.

Upsert (aka Merge)

If you want to merge data into a table, you can set merge: true which will use the MERGE statement to upsert data. This supports pattern matching like the above setting. It requires denormalized: true takes precedence over overwrite. It will only work on tables which have a primary key as defined by the key_properties sent by the tap. There is a supporting config option called dedupe_before_upsert which will dedupe the data before upserting. This is useful if you are replicating data which has a primary key but is not unique. This occurs when you are replicating data from a source which has a primary key but does not enforce it. This is the case for MongoDB. It can also happen when moving data from a data lake in S3/GCS to a database. This is not the default behavior because it is slower and requires more resources.

Features ✨

• Autoscaling self-healing worker pool using either threads (default) or multiprocessing, configurable by the user for the fastest possible data ingestion. Particularly when leveraging colocated compute in GCP.
• Denormalized load pattern where data is unpacked in flight into a statically typed BigQuery schema derived from the input stream json schemas.
• Fix schema load pattern where all data is loaded into a JSON column which has been GA in BigQuery since mid 2022.
• Autogenerated VIEW support for fixed schema load patterns which essentially overlays a statically typed schema allowing you to get the best of both worlds when using fixed schema ingestion.
• JIT compilation of protobuf schemas allowing the Storage Write API to use a denormalized load pattern.
• BATCH message support 😎

Load Patterns 🏎

• Batch Load Job ingestion pattern using in memory compression (fixed schema + denormalized)
• Storage Write API ingestion pattern using gRPC and protocol buffers supporting both streaming and batch patterns. Capable of JIT compilation of BQ schemas to protobuf to enable denormalized loads of input structures only known at runtime. (fixed schema + denormalized 🎉)
• GCS Staging ingestion pattern using in memory compression and a GCS staging layer which generates a well organized data lake which backs the data warehouse providing additional failsafes and data sources (fixed schema + denormalized)
• Legacy Streaming ingestion pattern which emphasizes simplicity and fast start up / tear down. I would highly recommend the storage write API instead unless data volume is small (fixed schema + denormalized)

Choosing between denormalized and fixed schema (JSON support)? 🙇🏾‍♂️

The gap between the methods is closed due in part to the target's ability to automatically generating a VIEW which will unpack (or rather provide typing as a more accurate take) a JSON based ingestion source for you. Unless operating at tens of millions of rows with JSON objects containing multiple hundreds of keys, its quite performant. Particularly if accessing a small subset of keys. It does however fall off (quite hard) given enough scale as I mentioned (I've pushed it to the limits). Denormalized is recommended for high volume where the schema is fairly consistent. Fixed is recommended for lower volume, inconsistent schemas or for taps which are inherently schemaless in which case its the ideal (only...) logical pattern. Fixed schema can also be used for taps which routinely break down on BQ due to json schemas being inexpressible in a static way (ie patternProperties, additionalProperties...)

Old Header (still true, here for posterity) 🧪

This is the first truly unstructured sink for BigQuery leveraging the recent GA feature in BigQuery for JSON support. This allows this target to load from essentially any tap regardless of the quality or explicitness of its jsonschema. Observations in existing taps note things such as patternProperties used in jsonschema objects which break down on all existing BigQuery taps due to the previous need for strong typing. Also taps such as MongoDB which inherently deal with unstructured data are seamlessly enabled by this target without klutzy collection scraping of a sample of records which we hope are repesentative of all documents.

Built with the Meltano Target SDK.

Configuration 🔨

Settings

First a valid example to give context to the below including a nested key example (denoted via a . in the setting path) as seen with column_name_transforms.snake_case

{
    "project": "my-bq-project",
    "method": "storage_write_api",
    "denormalized": true,
    "credentials_path": "...",
    "dataset": "my_dataset",
    "location": "us-central1",
    "batch_size": 500,
    "column_name_transforms": {
      "snake_case": true
    }
}

A full list of supported settings and capabilities is available by running: target-bigquery --about

Configure using environment variables ✏️

This Singer target will automatically import any environment variables within the working directory's .env if the --config=ENV is provided, such that config values will be considered if a matching environment variable is set either in the terminal context or in the .env file.

Source Authentication and Authorization 👮🏽‍♂️

Authenticate via service account key file or Application Default Credentials (ADC) https://cloud.google.com/bigquery/docs/authentication

Capabilities ✨

• about
• stream-maps
• schema-flattening
• batch

Usage 👷‍♀️

You can easily run target-bigquery by itself or in a pipeline using Meltano.

Executing the Target Directly 🚧

target-bigquery --version
target-bigquery --help
# Test using the "Carbon Intensity" sample:
tap-carbon-intensity | target-bigquery --config /path/to/target-bigquery-config.json

Developer Resources 👩🏼‍💻

Initialize your Development Environment

pipx install poetry
poetry install

Create and Run Tests

Create tests within the target_bigquery/tests subfolder and then run:

poetry run pytest

You can also test the target-bigquery CLI interface directly using poetry run:

poetry run target-bigquery --help

Testing with Meltano

Note: This target will work in any Singer environment and does not require Meltano. Examples here are for convenience and to streamline end-to-end orchestration scenarios.

Next, install Meltano (if you haven't already) and any needed plugins:

# Install meltano
pipx install meltano
# Initialize meltano within this directory
cd target-bigquery
meltano install

Now you can test and orchestrate using Meltano:

# Test invocation:
meltano invoke target-bigquery --version
# OR run a test `elt` pipeline with the Carbon Intensity sample tap:
meltano elt tap-carbon-intensity target-bigquery

SDK Dev Guide

See the dev guide for more instructions on how to use the Meltano SDK to develop your own Singer taps and targets.