BigQuery Publishing

LakeXpress can automatically create BigQuery tables from Parquet files stored in GCS.

Prerequisites

1. Google Cloud Permissions

Required IAM roles:

• BigQuery Data Editor (roles/bigquery.dataEditor) - Create datasets and tables
• BigQuery Job User (roles/bigquery.jobUser) - Run load jobs (native tables)
• Storage Object Viewer (roles/storage.objectViewer) - Read GCS files (external tables)

Or a custom role with these permissions:

bigquery.datasets.create
bigquery.datasets.get
bigquery.tables.create
bigquery.tables.get
bigquery.tables.delete
bigquery.tables.updateData
bigquery.jobs.create
storage.objects.get
storage.objects.list

2. GCS Storage Setup

Your credentials.json needs both GCS and BigQuery entries:

{
    "gcs_01": {
        "ds_type": "gcs",
        "auth_mode": "profile",
        "info": {
            "directory": "gs://my-datalake-bucket/lakexpress/",
            "profile": "/path/to/service-account.json"
        }
    },
    "bigquery_prod": {
        "ds_type": "bigquery",
        "auth_mode": "profile",
        "info": {
            "project_id": "my-gcp-project",
            "location": "US",
            "profile": "/path/to/service-account.json"
        }
    }
}

The same service account JSON file can be used for both GCS and BigQuery.

Authentication

LakeXpress uses service account authentication for BigQuery.

{
    "bigquery_prod": {
        "ds_type": "bigquery",
        "auth_mode": "profile",
        "info": {
            "project_id": "my-gcp-project",
            "location": "US",
            "profile": "/path/to/service-account.json"
        }
    }
}

Field	Required	Description
ds_type	Yes	Must be "bigquery"
auth_mode	Yes	Must be "profile"
project_id	Yes	GCP project ID where datasets will be created
location	No	BigQuery dataset location (default: "US")
profile	Yes	Path to service account JSON key file

Common locations: US, EU, us-central1, europe-west1.

Creating a Service Account

1. Go to Google Cloud Console > IAM & Admin > Service Accounts
2. Create a service account with BigQuery and Storage permissions
3. Create and download a JSON key
4. Reference the key file path in your credentials

Configuration Options

Option	Description	Default
--publish_target ID	Credential ID for BigQuery target (required)	-
--publish_schema_pattern PATTERN	BigQuery dataset naming pattern	{schema} for internal, EXT_{schema} for the default external method (unless set explicitly)
--publish_table_pattern PATTERN	Table naming pattern	{table}
--publish_method TYPE	Table type: external or internal	external
--pk_constraints	Add PRIMARY KEY constraints to native (internal) table DDL; not supported for external tables	off
--n_jobs N	Parallel workers for table creation	1

Table Types

External Tables (Default)

Queries data directly from GCS Parquet files without loading into BigQuery storage.

• Fast publishing, no data copying
• No BigQuery storage costs
• Always reflects latest GCS data
• Best for large datasets with infrequent queries
• Query speed depends on GCS read performance
• No clustering or partitioning support

Native Tables

Loads data from GCS into BigQuery's columnar storage.

• Faster queries via optimized columnar storage
• Full BigQuery features (clustering, partitioning, materialized views)
• Best for frequently queried data
• Slower publishing due to data loading
• Incurs BigQuery storage costs
• Data is a point-in-time snapshot

Dynamic Naming Patterns

Dataset and table names support token-based patterns.

Supported Tokens

Token	Description	Example Output
{schema}	Source schema name	tpch_1
{table}	Source table name	customer
{database}	Source database name	tpch
{date}	Current date (YYYYMMDD)	20251210
{timestamp}	Current timestamp (YYYYMMDD_HHMMSS)	20251210_143022
{uuid}	UUID4 identifier (consistent per run)	a1b2c3d4
{subpath}	CLI --sub_path parameter	staging

Common Patterns

Prefixed Datasets

--publish_schema_pattern "lx_{schema}"
--publish_table_pattern "{table}"
--publish_target bigquery_prod

# Results:
# Dataset: lx_tpch_1
# Tables: customer, orders, lineitem

Date-Partitioned Datasets

--publish_schema_pattern "{schema}_{date}"
--publish_table_pattern "{table}"
--publish_target bigquery_prod

# Results:
# Dataset: tpch_1_20251210
# Tables: customer, orders, lineitem

Consolidated Multi-Schema

--source_schema_name schema1,schema2
--publish_schema_pattern "consolidated"
--publish_table_pattern "{schema}_{table}"
--publish_target bigquery_prod

# Results:
# Dataset: consolidated
# Tables: schema1_customer, schema2_customer

Usage Examples

Ready-to-run commands for BigQuery are in Examples & Recipes: BigQuery.

See Also

• BigQuery Reference - Data type mappings, CLI arguments, querying, troubleshooting
• CLI Reference - All command-line options
• Examples & Recipes - Working command examples