Target-BigQuery

https://github.com/z3z1ma/target-bigquery
Admin
Target-BigQuery is a highly versatile and performant Singer target for Google BigQuery, supporting...
Tokens:7,677
Snippets:47
Trust Score:9.5
License:MIT
Update:11 months ago
Show doc for...
Tokens:
Raw
### Install and Initialize Meltano for Testing

Source: https://github.com/z3z1ma/target-bigquery/blob/main/README.md

Steps to install Meltano, a data orchestration tool, and initialize it within the project directory. This setup is for testing and orchestrating ELT pipelines.

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

--------------------------------

### Install and Verify Target-BigQuery

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Installs the Target-BigQuery using pipx or pip and verifies the installation by checking the version. It also shows how to view all configuration options and capabilities.

```bash
# Install using pipx (recommended) or pip
pipx install z3-target-bigquery

# Verify installation
target-bigquery --version

# View all configuration options and capabilities
target-bigquery --about
```

--------------------------------

### Initialize Development Environment with Poetry

Source: https://github.com/z3z1ma/target-bigquery/blob/main/README.md

Instructions for setting up the development environment using Poetry, a dependency management tool for Python. This includes installing Poetry and then installing project dependencies.

```bash
pipx install poetry
poetry install
```

--------------------------------

### Install and Run Meltano Pipelines

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Installs Meltano and runs an ELT pipeline from a postgres source to a BigQuery target. Demonstrates basic execution, state management, and selecting specific entities.

```bash
# Run Meltano pipeline
meltano install
meltano elt tap-postgres target-bigquery

# Run with state management
meltano elt tap-postgres target-bigquery --state-id postgres-to-bq

# Run specific entities
meltano elt tap-postgres target-bigquery --select customers --select orders
```

--------------------------------

### Install Target-BigQuery with pipx

Source: https://github.com/z3z1ma/target-bigquery/blob/main/README.md

Installs the `z3-target-bigquery` package using pipx, a tool for installing Python applications in isolated environments. It then shows how to verify the installation by checking the executable's version.

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

--------------------------------

### Configure Target-BigQuery with Denormalized Schema and Schema Evolution

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Configuration example for Target-BigQuery using the denormalized schema strategy with schema evolution enabled. This setup is used for loading data with potentially changing structures.

```bash
# Denormalized loads unpack JSON into typed BigQuery columns
tap-stripe | target-bigquery --config denormalized.json

# Configuration
cat > denormalized.json << 'EOF'
{
  "project": "payments-analytics",
  "dataset": "stripe_data",
  "method": "storage_write_api",
  "denormalized": true,
  "schema_resolver_version": 2,
  "credentials_path": "./service-account.json",
  "partition_granularity": "month",
  "cluster_on_key_properties": true
}
EOF

# Resulting BigQuery table has columns like:
# id (STRING), amount (INTEGER), currency (STRING),
# customer (RECORD), metadata (JSON), _sdc_batched_at (TIMESTAMP)
```

--------------------------------

### Example Configuration for Target BigQuery (JSON)

Source: https://github.com/z3z1ma/target-bigquery/blob/main/README.md

This JSON object provides a sample configuration for target-bigquery. It demonstrates key settings such as project, method, credentials, dataset, location, batch size, and nested transformations like snake_case for column names.

```json
{
    "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
    }
}
```

--------------------------------

### Invoke Target BigQuery with Meltano

Source: https://github.com/z3z1ma/target-bigquery/blob/main/README.md

Demonstrates how to invoke the Target BigQuery CLI using Meltano, allowing for integration testing and execution within a Meltano-managed pipeline. It also shows a full ELT pipeline example.

```bash
# 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
```

--------------------------------

### Configure Target-BigQuery for Fixed JSON Schema

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Configuration example for Target-BigQuery when using a fixed JSON schema. This approach is suitable for schemaless sources where data is loaded directly into a JSON column.

```bash
# Fixed JSON schema for schemaless sources
# (No specific code block provided in the text for this configuration, but implies a JSON config similar to other examples with denormalized: false)
```

--------------------------------

### Configure Target BigQuery with Service Account JSON String

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Provides a JSON configuration example for target-bigquery that includes authentication details via a service account JSON string. This method embeds credentials directly into the configuration.

```json
{
  "project": "my-project",
  "dataset": "my_dataset",
  "method": "storage_write_api",
  "denormalized": true,
  "credentials_json": "{\"type\": \"service_account\", \"project_id\": \"my-project\", \"private_key_id\": \"...\", \"private_key\": \"...\", \"client_email\": \"...\", \"client_id\": \"...\", \"auth_uri\": \"https://accounts.google.com/o/oauth2/auth\", \"token_uri\": \"https://oauth2.googleapis.com/token\"}"
}
```

--------------------------------

### Target BigQuery Stream Maps for Field Transformation

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Configures target-bigquery to use stream maps for transforming data. This example shows how to select fields and apply transformations, such as concatenating strings and extracting parts of strings.

```json
{
  "project": "analytics",
  "dataset": "transformed_data",
  "method": "storage_write_api",
  "denormalized": true,
  "credentials_path": "./service-account.json",
  "stream_maps": {
    "customers": {
      "id": "id",
      "full_name": "first_name + ' ' + last_name",
      "email_domain": "email.split('@')[1]",
      "__else__": null
    }
  },
  "stream_map_config": {
    "hash_seed": 12345
  }
}
```

--------------------------------

### Target BigQuery Stream Maps for Filtering Records

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Demonstrates using stream maps in target-bigquery to filter records based on specified conditions. This example filters 'orders' to include only those that are 'completed' and have an 'amount' greater than 100.

```json
{
  "project": "analytics",
  "dataset": "filtered_data",
  "method": "storage_write_api",
  "denormalized": true,
  "credentials_path": "./service-account.json",
  "stream_maps": {
    "orders": {
      "__filter__": "status == 'completed' and amount > 100"
    }
  }
}
```

--------------------------------

### Enable Schema Flattening in Target BigQuery

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Configures target-bigquery to automatically flatten nested JSON schemas. This example enables flattening and sets a maximum depth for the flattening process, showing the transformation of nested fields.

```json
{
  "project": "analytics",
  "dataset": "flat_schema",
  "method": "storage_write_api",
  "denormalized": true,
  "credentials_path": "./service-account.json",
  "flattening_enabled": true,
  "flattening_max_depth": 2
}
```

```bash
# Input nested structure:
# {"customer": {"address": {"city": "NYC", "zip": "10001"}}, "amount": 100}
#
# With flattening_enabled=true and flattening_max_depth=2:
# customer__address__city: "NYC"
# customer__address__zip: "10001"
# amount: 100
```

--------------------------------

### Configure Target BigQuery with Environment Variables

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Demonstrates setting up target-bigquery configuration using environment variables. A .env file is created and sourced, and then the target-bigquery is run referencing the 'ENV' config.

```bash
# Create .env file
cat > .env << 'EOF'
TARGET_BIGQUERY_PROJECT=analytics-prod
TARGET_BIGQUERY_DATASET=raw_data
TARGET_BIGQUERY_METHOD=storage_write_api
TARGET_BIGQUERY_DENORMALIZED=true
TARGET_BIGQUERY_CREDENTIALS_PATH=/path/to/service-account.json
TARGET_BIGQUERY_BATCH_SIZE=500
EOF

# Run with environment variables
target-bigquery --config ENV < input.jsonl
```

--------------------------------

### Run Target-BigQuery with Batch Job Method

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Demonstrates using Target-BigQuery with the Batch Job method, optimized for high-volume loads. Includes a sample configuration file (`batch-config.json`) for this method.

```bash
# Batch Job method optimized for high-volume loads
tap-postgres | target-bigquery --config batch-config.json

# Configuration for Batch Job
cat > batch-config.json << 'EOF'
{
  "project": "datawarehouse",
  "dataset": "staging",
  "method": "batch_job",
  "denormalized": true,
  "batch_size": 50000,
  "timeout": 1200,
  "credentials_path": "./service-account.json"
}
EOF
```

--------------------------------

### Run Target-BigQuery with Legacy Streaming Insert

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Uses Target-BigQuery with the Legacy Streaming Insert method, suitable for small-volume use cases. Includes a sample configuration file (`streaming-config.json`).

```bash
# Legacy streaming for small-volume use cases
tap-github | target-bigquery --config streaming-config.json

# Configuration for Streaming Insert
cat > streaming-config.json << 'EOF'
{
  "project": "analytics-dev",
  "dataset": "github_events",
  "method": "streaming_insert",
  "denormalized": true,
  "batch_size": 500,
  "credentials_path": "./service-account.json"
}
EOF
```

--------------------------------

### Run Target-BigQuery with Storage Write API

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Executes Target-BigQuery using the Storage Write API in streaming mode, piping data from a tap. Includes a sample configuration file (`config.json`) for this method.

```bash
# Run with Storage Write API in streaming mode (default)
tap-mongodb | target-bigquery --config config.json

# Configuration for Storage Write API
cat > config.json << 'EOF'
{
  "project": "analytics-prod",
  "dataset": "mongodb_replica",
  "method": "storage_write_api",
  "denormalized": true,
  "credentials_path": "./service-account.json",
  "batch_size": 500,
  "options": {
    "storage_write_batch_mode": false,
    "max_workers": 8
  }
}
EOF
```

--------------------------------

### Configure Time-Based Partitioning and Clustering for target-bigquery

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Sets up target-bigquery for daily time-based partitioning with a 90-day expiration and enables clustering on key properties for improved query performance. Requires a service account JSON file.

```bash
# Configure partitioning and clustering
tap-analytics | target-bigquery --config partitioned.json

cat > partitioned.json << 'EOF'
{
  "project": "analytics-warehouse",
  "dataset": "events",
  "method": "storage_write_api",
  "denormalized": true,
  "partition_granularity": "day",
  "partition_expiration_days": 90,
  "cluster_on_key_properties": true,
  "credentials_path": "./service-account.json"
}
EOF

# Creates tables with:
# - Daily partitions on _sdc_batched_at column
# - 90-day partition expiration for cost savings
# - Clustering on key_properties for query performance
```

--------------------------------

### Run Target-BigQuery with GCS Staging

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Configures and runs Target-BigQuery using the GCS Staging method to create an organized data lake structure. Includes a sample configuration file (`gcs-config.json`).

```bash
# GCS staging creates organized data lake structure
tap-salesforce | target-bigquery --config gcs-config.json

# Configuration for GCS Staging
cat > gcs-config.json << 'EOF'
{
  "project": "analytics-prod",
  "dataset": "salesforce_raw",
  "method": "gcs_stage",
  "denormalized": true,
  "bucket": "my-data-lake-bucket",
  "location": "us-central1",
  "batch_size": 10000,
  "credentials_path": "./service-account.json"
}
EOF
```

--------------------------------

### Execute Target BigQuery CLI Commands

Source: https://github.com/z3z1ma/target-bigquery/blob/main/README.md

Demonstrates how to execute the Target BigQuery command-line interface (CLI) for various purposes, such as checking the version, displaying help information, or running a pipeline with a sample tap.

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

--------------------------------

### Configure Target-BigQuery with Denormalized Schema

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Basic configuration for Target-BigQuery using the denormalized schema strategy. This JSON configuration specifies the GCP project, dataset, load method, and path to credentials.

```json
{
  "project": "my-gcp-project",
  "dataset": "raw_data",
  "method": "storage_write_api",
  "denormalized": true,
  "credentials_path": "/path/to/service-account.json",
  "location": "US",
  "batch_size": 500,
  "partition_granularity": "day",
  "cluster_on_key_properties": true
}
```

--------------------------------

### Configure Target BigQuery with Application Default Credentials (ADC)

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Demonstrates configuration for target-bigquery using Application Default Credentials (ADC). ADC allows the tool to automatically discover credentials from the environment, gcloud CLI, or Google Cloud metadata service.

```json
{
  "project": "my-project",
  "dataset": "my_dataset",
  "method": "storage_write_api",
  "denormalized": true
}
```

```bash
# ADC credentials discovery order:
# 1. GOOGLE_APPLICATION_CREDENTIALS environment variable
# 2. gcloud CLI credentials (gcloud auth application-default login)
# 3. GCE/GKE metadata service when running on Google Cloud

gcloud auth application-default login
target-bigquery --config config.json < input.jsonl
```

--------------------------------

### Test Target BigQuery CLI with Poetry

Source: https://github.com/z3z1ma/target-bigquery/blob/main/README.md

Shows how to test the Target BigQuery CLI interface directly using the `poetry run` command, which ensures the CLI is executed within the project's managed environment.

```bash
poetry run target-bigquery --help
```

--------------------------------

### Configure Fixed Schema with View Generation for target-bigquery

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Sets up target-bigquery to store all data in a JSON column and generate an auto-unpacked view. Requires a service account JSON file for authentication.

```bash
# Fixed schema stores everything in a JSON column
tap-mongodb | target-bigquery --config fixed-schema.json

# Configuration with view generation
cat > fixed-schema.json << 'EOF'
{
  "project": "mongodb-warehouse",
  "dataset": "raw_collections",
  "method": "storage_write_api",
  "denormalized": false,
  "generate_view": true,
  "credentials_path": "./service-account.json",
  "column_name_transforms": {
    "snake_case": true,
    "replace_period_with_underscore": true
  }
}
EOF

# Resulting table has schema:
# data (JSON), _sdc_extracted_at (TIMESTAMP), _sdc_received_at (TIMESTAMP),
# _sdc_batched_at (TIMESTAMP), _sdc_deleted_at (TIMESTAMP)
#
# Plus an auto-generated view that unpacks the JSON:
# my_collection_view with typed columns derived from tap schema
```

--------------------------------

### Authenticate with Service Account JSON File

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Configures authentication for target-bigquery using a service account JSON file by setting the GOOGLE_APPLICATION_CREDENTIALS environment variable or providing the path in the configuration.

```bash
# Set environment variable
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account.json

# Run with credentials_path
target-bigquery --config config.json < input.jsonl
```

--------------------------------

### Configure Target-BigQuery with Fixed JSON Schema and View Generation

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Configuration for Target-BigQuery using a fixed JSON schema strategy, enabling view generation. This JSON includes project, dataset, load method, and options for column name transformations.

```json
{
  "project": "my-gcp-project",
  "dataset": "raw_data",
  "method": "storage_write_api",
  "denormalized": false,
  "generate_view": true,
  "credentials_json": "{\"type\": \"service_account\", \"project_id\": \"...\"}",
  "batch_size": 500,
  "column_name_transforms": {
    "snake_case": true,
    "lower": true
  }
}
```

--------------------------------

### Target BigQuery Fail-Fast Configuration

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Shows the JSON configuration for target-bigquery enabling fail-fast mode, which halts the pipeline immediately upon encountering an error. This is the default behavior.

```json
{
  "project": "production",
  "dataset": "critical_data",
  "method": "storage_write_api",
  "denormalized": true,
  "fail_fast": true,
  "credentials_path": "./service-account.json"
}
```

--------------------------------

### Configure Upsert Mode with Deduplication for target-bigquery

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Sets up target-bigquery for upsert (merge) operations based on specified primary keys and deduplicates records before merging. Supports pattern matching for table selection. Requires a service account JSON file.

```bash
# Upsert merges data based on primary keys
tap-mysql | target-bigquery --config upsert.json

cat > upsert.json << 'EOF'
{
  "project": "analytics",
  "dataset": "dim_tables",
  "method": "storage_write_api",
  "denormalized": true,
  "upsert": ["customer*", "product_*", "!product_events_*"],
  "dedupe_before_upsert": ["customer*"],
  "credentials_path": "./service-account.json"
}
EOF

# Pattern matching:
# - "customer*" matches customer_dim, customers, customer_addresses
# - "product_*" matches product_catalog, product_categories
# - "!product_events_*" excludes product_events_log from upsert
# Tables with upsert perform MERGE operation using key_properties
```

--------------------------------

### Configure Overwrite Mode with Pattern Matching for target-bigquery

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Configures target-bigquery to overwrite specific tables based on pattern matching. This mode fully refreshes matching tables on each run using a transaction-safe swap method. Requires a service account JSON file.

```bash
# Overwrite specific tables on each run
tap-rest-api | target-bigquery --config overwrite.json

cat > overwrite.json << 'EOF'
{
  "project": "analytics",
  "dataset": "reference_data",
  "method": "storage_write_api",
  "denormalized": true,
  "overwrite": ["lookup_*", "ref_*", "!ref_changelog"],
  "credentials_path": "./service-account.json"
}
EOF

# Tables matching patterns are fully refreshed on each load
# Uses transaction-safe overwrite (temp table -> swap)
```

--------------------------------

### Configure Append Mode for target-bigquery

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Configures target-bigquery to append all incoming records to the target table. This is the default write mode and does not overwrite existing data. Requires a service account JSON file.

```bash
# Append mode writes all records to target table
tap-postgres --config postgres-config.json | target-bigquery --config append.json

cat > append.json << 'EOF'
{
  "project": "analytics",
  "dataset": "events",
  "method": "storage_write_api",
  "denormalized": true,
  "overwrite": false,
  "credentials_path": "./service-account.json"
}
EOF
```

--------------------------------

### Run Tests with Pytest

Source: https://github.com/z3z1ma/target-bigquery/blob/main/README.md

Commands to run tests for the project using Pytest, a popular Python testing framework. This involves executing Pytest within the project's test directory or via the Poetry runner.

```bash
poetry run pytest
```

--------------------------------

### Target BigQuery Permissive Mode Configuration

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Illustrates the JSON configuration for target-bigquery to operate in permissive mode, allowing the pipeline to continue processing even after encountering errors, with errors logged for later review.

```json
{
  "project": "staging",
  "dataset": "experimental",
  "method": "storage_write_api",
  "denormalized": true,
  "fail_fast": false,
  "credentials_path": "./service-account.json"
}
```

--------------------------------

### Configure Thread Pool for I/O-Bound Workloads in target-bigquery

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Configures target-bigquery to use a thread pool for I/O-bound workloads, which is the default behavior. This setting specifies `process_pool: false` and defines the maximum number of worker threads.

```json
{
  "project": "streaming-pipeline",
  "dataset": "realtime_data",
  "method": "storage_write_api",
  "denormalized": true,
  "batch_size": 500,
  "credentials_path": "./service-account.json",
  "options": {
    "process_pool": false,
    "max_workers": 32
  }
}
```

--------------------------------

### Configure Process Pool for CPU-Intensive Workloads in target-bigquery

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Enables a process pool for target-bigquery, utilizing multiple worker processes to handle CPU-intensive workloads. This configuration specifies `process_pool: true` and sets the maximum number of workers.

```json
{
  "project": "high-volume-etl",
  "dataset": "production_data",
  "method": "storage_write_api",
  "denormalized": true,
  "batch_size": 500,
  "credentials_path": "./service-account.json",
  "options": {
    "process_pool": true,
    "max_workers": 16
  }
}
```

--------------------------------

### Configure Column Name Transformations in target-bigquery JSON

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Specifies column name transformation rules within the target-bigquery configuration JSON. This includes snake_case, lowercasing, adding underscores for invalid characters, and replacing periods. Ensures consistent naming for BigQuery columns.

```json
{
  "project": "analytics",
  "dataset": "clean_data",
  "method": "storage_write_api",
  "denormalized": true,
  "credentials_path": "./service-account.json",
  "column_name_transforms": {
    "snake_case": true,
    "lower": true,
    "add_underscore_when_invalid": true,
    "replace_period_with_underscore": true,
    "quote": false
  }
}
```

--------------------------------

### Python Column Name Transformations for target-bigquery

Source: https://context7.com/z3z1ma/target-bigquery/llms.txt

Demonstrates how to use the `transform_column_name` function in Python to apply various naming conventions to BigQuery columns, including snake_case, lowercasing, and replacing invalid characters. This is often configured within the target's JSON settings.

```python
# Python example showing how transformations affect schema
from target_bigquery.core import transform_column_name

# Configuration
transforms = {
  "snake_case": True,
  "lower": True,
  "add_underscore_when_invalid": True,
  "replace_period_with_underscore": True
}

# Transform examples
original_columns = ["FirstName", "LastName", "Email.Address", "2ndPhone"]
transformed = [transform_column_name(col, **transforms) for col in original_columns]
# Result: ['first_name', 'last_name', 'email_address', '_2nd_phone']
```
Tokens:
Raw