BigQuery Schema Generator

This script generates the BigQuery schema from the newline-delimited data records on the STDIN. The records can be in JSON format or CSV format. The BigQuery data importer (bq load) uses only the first 500 records when the schema auto-detection feature is enabled. In contrast, this script uses all data records to generate the schema.

Usage:

$ generate-schema < file.data.json > file.schema.json
$ generate-schema --input_format csv < file.data.csv > file.schema.json

Version: 1.6.1 (2024-01-12)

Changelog: CHANGELOG.md

Table of Contents

• Background
• Installation
  • Ubuntu Linux
  • MacOS
    • MacOS 12 (Monterey)
    • MacOS 11 (Big Sur)
    • MacOS 10.14 (Mojave)
• Usage
  • Command Line
  • Schema Output
  • Command Line Flag Options
    • Help (--help)
    • Input Format (--input_format)
    • Keep Nulls (--keep_nulls)
    • Quoted Values Are Strings(--quoted_values_are_strings)
    • Infer Mode (--infer_mode)
    • Debugging Interval (--debugging_interval)
    • Debugging Map (--debugging_map)
    • Sanitize Names (--sanitize_names)
    • Ignore Invalid Lines (--ignore_invalid_lines)
    • Existing Schema Path (--existing_schema_path)
    • Preserve Input Sort Order (--preserve_input_sort_order)
  • Using as a Library
    • SchemaGenerator.run()
    • SchemaGenerator.deduce_schema() from File
    • SchemaGenerator.deduce_schema() from Dict
    • SchemaGenerator.deduce_schema() from DictReader
• Schema Types
  • Supported Types
  • Type Inference
• Examples
• Benchmarks
• System Requirements
• License
• Feedback and Support
• Authors

Background

Data can be imported into BigQuery using the bq command line tool. It accepts a number of data formats including CSV or newline-delimited JSON. The data can be loaded into an existing table or a new table can be created during the loading process. The structure of the table is defined by its schema. The table's schema can be defined manually or the schema can be auto-detected.

When the auto-detect feature is used, the BigQuery data importer examines only the first 500 records of the input data. In many cases, this is sufficient because the data records were dumped from another database and the exact schema of the source table was known. However, for data extracted from a service (e.g. using a REST API) the record fields could have been organically added at later dates. In this case, the first 500 records do not contain fields which are present in later records. The bq load auto-detection fails and the data fails to load.

The bq load tool does not support the ability to process the entire dataset to determine a more accurate schema. This script fills in that gap. It processes the entire dataset given in the STDIN and outputs the BigQuery schema in JSON format on the STDOUT. This schema file can be fed back into the bq load tool to create a table that is more compatible with the data fields in the input dataset.

Installation

Prerequisite: You need have Python 3.6 or higher.

Install from PyPI repository using pip3. There are too many ways to install packages in Python. The following are in order highest to lowest recommendation:

1) If you are using a virtual environment (such as venv), then use:

$ pip3 install bigquery_schema_generator

2) If you aren't using a virtual environment you can install into your local Python directory:

$ pip3 install --user bigquery_schema_generator

3) If you want to install the package for your entire system globally, use

$ sudo -H pip3 install bigquery_schema_generator

but realize that you will be running code from PyPI as root so this has security implications.

Sometimes, your Python environment gets into a complete mess and the pip3 command won't work. Try typing python3 -m pip instead.

A successful install should print out something like the following (the version number may be different):

Collecting bigquery-schema-generator
Installing collected packages: bigquery-schema-generator
Successfully installed bigquery-schema-generator-1.1

The shell script generate-schema will be installed somewhere in your system, depending on how your Python environment is configured. See below for some notes for Ubuntu Linux and MacOS.

Ubuntu Linux (18.04, 20.04, 22.04)

After running pip3 install bigquery_schema_generator, the generate-schema script may be installed in one the following locations:

• /usr/bin/generate-schema
• /usr/local/bin/generate-schema
• $HOME/.local/bin/generate-schema
• $HOME/.virtualenvs/{your_virtual_env}/bin/generate-schema

MacOS

I don't have any Macs which are able to run the latest macOS, and I don't use them much for software development these days, but here are some notes on older versions of macOS in case they help.

MacOS 12 (Monterey)

Python 2 or 3 is not installed by default on Monterey. If you try to run python3 on the command line, a dialog box asks you to install the Xcode development package. It apparently takes over an hour at 10 MB/s.

You can instead install Python 3 using Homebrew, by installing brew, and typing $ brew install python. Currently, it downloads Python 3.10 in about 1-2 minutes and installs the python3 and pip3 binaries into /usr/local/bin/python3 and /usr/local/bin/pip3. Using brew seems to be easiest option, so let's assume that Python 3 was installed through that.

If you run:

$ pip3 install bigquery_schema_generator

the package will be installed at /usr/local/lib/python3.10/site-packages/, and the generate-schema script will be installed at /usr/local/bin/generate-schema.

If you use the --user flag:

$ pip3 install --user bigquery_schema_generator

the package will be installed at $HOME/Library/Python/3.10/lib/python/site-packages/, and the generate-schema script will be installed at $HOME/Library/Python/3.10/bin/generate-schema.

You may need to add the $HOME/Library/Python/3.10/bin directory to your $PATH variable in your $HOME/.bashrc file.

MacOS 11 (Big Sur)

Python 2.7.16 is installed by default on Big Sur as /usr/bin/python. If you try to run python3 on the command line, a dialog box asks you to install the Xcode development package will be installed, which I think installs Python 3.8 as /usr/bin/python3 (I can't remember, it was installed a long time ago.)

You can instead install Python 3 using Homebrew, by installing brew, and typing $ brew install python. Currently, it downloads Python 3.10 in about 1-2 minutes and installs the python3 and pip3 binaries into /usr/local/bin/python3 and /usr/local/bin/pip3. Using brew seems to be easiest option, so let's assume that Python 3 was installed through that.

If you run:

$ pip3 install bigquery_schema_generator

the package will be installed at /usr/local/lib/python3.10/site-packages/, and the generate-schema script will be installed at /usr/local/bin/generate-schema.

If you use the --user flag:

$ pip3 install --user bigquery_schema_generator

the package will be installed at $HOME/Library/Python/3.10/lib/python/site-packages/, and the generate-schema script will be installed at $HOME/Library/Python/3.10/bin/generate-schema.

You may need to add the $HOME/Library/Python/3.10/bin directory to your $PATH variable in your $HOME/.bashrc file.

MacOS 10.14 (Mojave)

This MacOS version comes with Python 2.7 only. To install Python 3, you can install using:

1)) Downloading the macos installer directly from Python.org.

The python3 binary will be located at /usr/local/bin/python3, and the /usr/local/bin/pip3 is a symlink to /Library/Frameworks/Python.framework/Versions/3.6/bin/pip3.

So running

$ pip3 install --user bigquery_schema_generator

will install generate-schema at /Library/Frameworks/Python.framework/Versions/3.6/bin/generate-schema.

The Python installer updates $HOME/.bash_profile to add /Library/Frameworks/Python.framework/Versions/3.6/bin to the $PATH environment variable. So you should be able to run the generate-schema command without typing in the full path.

2)) Using Homebrew.

In this environment, the generate-schema script will probably be installed in /usr/local/bin but I'm not completely certain.

Usage

Command Line

The generate_schema.py script accepts a newline-delimited JSON or CSV data file on the STDIN. JSON input format has been tested extensively. CSV input format was added more recently (in v0.4) using the --input_format csv flag. The support is not as robust as JSON file. For example, CSV format supports only the comma-separator, and does not support the pipe (|) or tab (\t) character.

Side Note: The input_format parameter now supports (v1.6.0) the csvdictreader option which allows using the csv.DictReader class that can be customized to handle different delimiters such as tabs. But this requires creating a custom Python script using bigquery_schema_generator as a library. See SchemaGenerator.deduce_schema() from csv.DictReader section below. It is probably possible to enable this functionality through the command line script, but it was not obvious how to expose the various options of csv.DictReader through the command line flags. I didn't spend any time on this problem because this is not a feature that I use personally.)

Unlike bq load, the generate_schema.py script reads every record in the input data file to deduce the table's schema. It prints the JSON formatted schema file on the STDOUT.

There are at least 3 ways to run this script:

1) Shell script

If you installed using pip3, then it should have installed a small helper script named generate-schema in your local ./bin directory of your current environment (depending on whether you are using a virtual environment).

$ generate-schema < file.data.json > file.schema.json

2) Python module

You can invoke the module directly using:

$ python3 -m bigquery_schema_generator.generate_schema < file.data.json > file.schema.json

This is essentially what the generate-schema command does.

3) Python script

If you retrieved this code from its GitHub repository, then you can invoke the Python script directly:

$ ./generate_schema.py < file.data.json > file.schema.json

Using the Schema Output

The resulting schema file can be given to the bq load command using the --schema flag:

$ bq load --source_format NEWLINE_DELIMITED_JSON \
    --ignore_unknown_values \
    --schema file.schema.json \
    mydataset.mytable \
    file.data.json

where mydataset.mytable is the target table in BigQuery.

For debugging purposes, here is the equivalent bq load command using schema autodetection:

$ bq load --source_format NEWLINE_DELIMITED_JSON \
    --autodetect \
    mydataset.mytable \
    file.data.json

If the input file is in CSV format, the first line will be the header line which enumerates the names of the columns. But this header line must be skipped when importing the file into the BigQuery table. We accomplish this using --skip_leading_rows flag:

$ bq load --source_format CSV \
    --schema file.schema.json \
    --skip_leading_rows 1 \
    mydataset.mytable \
    file.data.csv

Here is the equivalent bq load command for CSV files using autodetection:

$ bq load --source_format CSV \
    --autodetect \
    mydataset.mytable \
    file.data.csv

A useful flag for bq load, particularly for JSON files, is --ignore_unknown_values, which causes bq load to ignore fields in the input data which are not defined in the schema. When generate_schema.py detects an inconsistency in the definition of a particular field in the input data, it removes the field from the s