Using a JSON Schema file in BigQuery for creating a Schema

Question

I don't want to manually create a schema so I have created a JSON schema file for the same. How do I use this file to create a Schema?

Answer 1

After creating your JSON schema file, you can specify it on the command line. You cannot use a schema file with the GCP Console, the classic BigQuery web UI, or the API.

Manually supply the schema file using the:

• load command if you are loading data

• mk command if you are creating an empty table

When you supply a JSON schema file, it must be stored in a locally readable location. You cannot specify a JSON schema file stored in Cloud Storage or Google Drive.

Specifying a schema file when you load data

The following command loads data into a table using the schema definition in a JSON file:

bq --location=location load \
--source_format=format \
project_id:dataset.table \
path_to_data_file \
path_to_schema_file

Where:

• location is the name of your location. 

• format is NEWLINE_DELIMITED_JSON or CSV.

• project_id is your project ID.

• dataset is the dataset that contains the table into which you're loading data.

• table is the name of the table into which you're loading data.

• path_to_data_file is the location of the CSV or JSON data file on your local machine or in Cloud Storage.

• path_to_schema_file is the path to the schema file on your local machine.

Specifying a schema file when you create a table

The following command creates an empty table in an existing dataset using the schema definition in a JSON file:

bq mk --table project_id:dataset.table path_to_schema_file

Where:

• project_id is your project ID.

• dataset is a dataset in your project.

• table is the name of the table you're creating.

• path_to_schema_file is the path to the schema file on your local machine.