bigquery-schema-generator

bigquery-schema-generator is a Python command-line tool and library that reads an entire newline-delimited JSON or CSV data file and produces a BigQuery-compatible schema in JSON format. Google’s own bq load auto-detection only examines the first 500 records of a dataset, which fails silently when later records introduce new fields, type changes, or nested structures that weren’t present up front — a common problem with data pulled from REST APIs or event streams that evolve over time.

The tool ships as the generate-schema console script for pipeline use, and as an importable SchemaGenerator class for programmatic integration with custom readers (including csv.DictReader for non-comma delimiters). It handles type widening (INTEGER to FLOAT, for example), REPEATED and RECORD (nested) fields, NULLABLE vs REQUIRED inference, and can merge new data against an existing schema fetched via bq show --schema so schemas evolve safely instead of being clobbered on each load.

What You Get

• A generate-schema CLI that reads JSON or CSV from STDIN and writes a bq load-compatible schema to STDOUT
• A SchemaGenerator Python class for embedding schema deduction inside custom ETL or ingestion scripts
• Support for CSV via csv.DictReader, enabling non-standard delimiters like tabs through the csvdictreader input format
• Type inference across BOOLEAN, INTEGER, FLOAT, STRING, TIMESTAMP, DATE, TIME, and nested RECORD/REPEATED fields
• Schema merging against an existing table schema (--existing_schema_path) so new fields are added without breaking prior columns
• Flags for NULLABLE/REQUIRED inference, name sanitization to BigQuery’s naming rules, and tolerant handling of malformed input lines

Common Use Cases

• Generating a schema file for bq load from a REST API export whose records grew new fields over time
• Embedding schema deduction inside a Python ETL job that loads streaming JSON events into BigQuery
• Producing a schema for CSV exports where delimiters or column sets aren’t guaranteed to be simple commas
• Safely evolving an existing BigQuery table’s schema by merging newly observed fields with the current one

Under The Hood

Architecture The project is a single-module Python tool centered on the SchemaGenerator class in bigquery_schema_generator/generate_schema.py, which exposes deduce_schema() (accepts an iterable of dicts, a JSON line reader, or a CSV DictReader) and flatten_schema_map() (converts the internal recursive metadata map into the final bq load JSON schema). The CLI’s main() function is a thin argparse wrapper that configures a SchemaGenerator instance and calls run(), which reads STDIN, calls deduce_schema(), and writes the flattened schema to STDOUT — the same entry points are reused whether invoked from the shell or imported as a library, so there’s no divergence between the two usage modes. Nested objects and arrays recurse into the same schema_map structure, and an --existing_schema_path schema can be pre-loaded into that map so new records only add or widen fields rather than starting from scratch.

Tech Stack Pure Python 3.6+ standard library — argparse for the CLI, csv and json for parsing, re for type-pattern matching (TIMESTAMP/DATE/TIME regexes), and collections.OrderedDict for deterministic field ordering. There are no runtime third-party dependencies, which keeps the package lightweight and simple to vendor into other pipelines. Packaging is a plain setup.py with a single generate-schema console-script entry point.

Code Quality The project has an extensive test suite (tests/test_generate_schema.py, roughly 750 lines) covering the type matchers, schema deduction, and edge cases like mixed types and null handling, plus a data_reader.py harness for table-driven fixture tests and a separate test_anonymize.py. CI runs the suite across Python 3.7 through 3.11 via GitHub Actions with flake8 linting. Code style is straightforward, well-commented Python with docstrings on the main class explaining schema_map structure and each CLI flag documented in both argparse help text and the README.

API Design The library API is compact and purpose-built: instantiate SchemaGenerator with the desired flags, call deduce_schema() with any of a file handle, a plain iterable of dicts, or a CSV DictReader, then flatten_schema() to get the final schema. This mirrors the CLI’s own flag set one-to-one, so behavior learned from the command line transfers directly to library use, and the examples/ directory (csvreader.py, jsonreader.py, dictreader.py, generatorrun.py) demonstrates each integration path directly.