Loading JSON data from Cloud Storage (original) (raw)

Skip to main content

You can load newline-delimited JSON (ndJSON) data from Cloud Storage into a new table or partition, or append to or overwrite an existing table or partition. When your data is loaded into BigQuery, it is converted into columnar format for Capacitor(BigQuery's storage format).

When you load data from Cloud Storage into a BigQuery table, the dataset that contains the table must be in the same regional or multi- regional location as the Cloud Storage bucket.

The ndJSON format is the same format as theJSON Lines format.

Limitations

You are subject to the following limitations when you load data into BigQuery from a Cloud Storage bucket:

When you load JSON files into BigQuery, note the following:

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document, and create a dataset to store your data.

Required permissions

To load data into BigQuery, you need IAM permissions to run a load job and load data into BigQuery tables and partitions. If you are loading data from Cloud Storage, you also need IAM permissions to access the bucket that contains your data.

Permissions to load data into BigQuery

To load data into a new BigQuery table or partition or to append or overwrite an existing table or partition, you need the following IAM permissions:

Each of the following predefined IAM roles includes the permissions that you need in order to load data into a BigQuery table or partition:

Additionally, if you have the bigquery.datasets.create permission, you can create and update tables using a load job in the datasets that you create.

For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Permissions to load data from Cloud Storage

To get the permissions that you need to load data from a Cloud Storage bucket, ask your administrator to grant you theStorage Admin (roles/storage.admin) IAM role on the bucket. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the permissions required to load data from a Cloud Storage bucket. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to load data from a Cloud Storage bucket:

You might also be able to get these permissions with custom roles or other predefined roles.

Create a dataset

Create a BigQuery dataset to store your data.

JSON compression

You can use the gzip utility to compress JSON files. Note that gzip performs full file compression, unlike the file content compression performed by compression codecs for other file formats, such as Avro. Using gzip to compress your JSON files might have a performance impact; for more information about the trade-offs, seeLoading compressed and uncompressed data.

Loading JSON data into a new table

To load JSON data from Cloud Storage into a new BigQuery table:

Console

  1. In the Google Cloud console, go to the BigQuery page.
    Go to BigQuery
  2. In the Explorer pane, expand your project, and then select a dataset.
  3. In the Dataset info section, click Create table.
  4. In the Create table panel, specify the following details:
    1. In the Source section, select Google Cloud Storage in the Create table from list. Then, do the following:
      1. Select a file from the Cloud Storage bucket, or enter theCloud Storage URI. You cannot include multiple URIs in the Google Cloud console, but wildcards are supported. The Cloud Storage bucket must be in the same location as the dataset that contains the table you want to create, append, or overwrite.select source file to create a BigQuery table
      2. For File format, selectJSONL (Newline delimited JSON).
    2. In the Destination section, specify the following details:
      1. For Dataset, select the dataset in which you want to create the table.
      2. In the Table field, enter the name of the table that you want to create.
      3. Verify that the Table type field is set to Native table.
    3. In the Schema section, enter the schema definition. To enable the auto detection of a schema, select Auto detect. You can enter schema information manually by using one of the following methods:
      • Option 1: Click Edit as text and paste the schema in the form of a JSON array. When you use a JSON array, you generate the schema using the same process as creating a JSON schema file. You can view the schema of an existing table in JSON format by entering the following command:
        bq show --format=prettyjson dataset.table
      • Option 2: Click Add field and enter the table schema. Specify each field's Name,Type, and Mode.
    4. Optional: Specify Partition and cluster settings. For more information, seeCreating partitioned tables andCreating and using clustered tables.
    5. Click Advanced options and do the following:
      • For Write preference, leave Write if empty selected. This option creates a new table and loads your data into it.
      • For Number of errors allowed, accept the default value of 0 or enter the maximum number of rows containing errors that can be ignored. If the number of rows with errors exceeds this value, the job will result in an invalid message and fail. This option applies only to CSV and JSON files.
      • For Time zone, enter the default time zone that will apply when parsing timestamp values that have no specific time zone. Checkhere for more valid time zone names. If this value is not present, the timestamp values without specific time zone is parsed using default time zone UTC. (Preview).
      • For Date Format, enter the format elements that define how the DATE values are formatted in the input files. This field expects SQL styles format (for example, MM/DD/YYYY). If this value is present, this format is the only compatible DATE format.Schema autodetection will also decide DATE column type based on this format instead of the existing format. If this value is not present, the DATE field is parsed with thedefault formats. (Preview).
      • For Datetime Format, enter the format elements that define how the DATETIME values are formatted in the input files. This field expects SQL styles format (for example, MM/DD/YYYY HH24:MI:SS.FF3). If this value is present, this format is the only compatible DATETIME format.Schema autodetection will also decide DATETIME column type based on this format instead of the existing format. If this value is not present, the DATETIME field is parsed with thedefault formats. (Preview).
      • For Time Format, enter the format elements that define how the TIME values are formatted in the input files. This field expects SQL styles format (for example, HH24:MI:SS.FF3). If this value is present, this format is the only compatible TIME format.Schema autodetection will also decide TIME column type based on this format instead of the existing format. If this value is not present, the TIME field is parsed with thedefault formats. (Preview).
      • For Timestamp Format, enter the format elements that define how the TIMESTAMP values are formatted in the input files. This field expects SQL styles format (for example, MM/DD/YYYY HH24:MI:SS.FF3). If this value is present, this format is the only compatible TIMESTAMP format.Schema autodetection will also decide TIMESTAMP column type based on this format instead of the existing format. If this value is not present, the TIMESTAMP field is parsed with thedefault formats. (Preview).
      • If you want to ignore values in a row that are not present in the table's schema, then select Unknown values.
      • For Encryption, click Customer-managed key to use aCloud Key Management Service key. If you leave the Google-managed key setting, BigQueryencrypts the data at rest.
    6. Click Create table.

SQL

Use theLOAD DATA DDL statement. The following example loads a JSON file into the new table mytable:

  1. In the Google Cloud console, go to the BigQuery page.
    Go to BigQuery
  2. In the query editor, enter the following statement:
    LOAD DATA OVERWRITE mydataset.mytable
    (x INT64,y STRING)
    FROM FILES (
    format = 'JSON',
    uris = ['gs://bucket/path/file.json']);
  3. Click Run.

For more information about how to run queries, see Run an interactive query.

bq

Use the bq load command, specify NEWLINE_DELIMITED_JSON using the--source_format flag, and include a Cloud Storage URI. You can include a single URI, a comma-separated list of URIs, or a URI containing a wildcard. Supply the schema inline, in a schema definition file, or useschema auto-detect.

(Optional) Supply the --location flag and set the value to yourlocation.

Other optional flags include:

To load JSON data into BigQuery, enter the following command:

bq --location=LOCATION load
--source_format=FORMAT
DATASET.TABLE
PATH_TO_SOURCE
SCHEMA

Replace the following:

Examples:

The following command loads data from gs://mybucket/mydata.json into a table named mytable in mydataset. The schema is defined in a local schema file named myschema.

    bq load \
    --source_format=NEWLINE_DELIMITED_JSON \
    mydataset.mytable \
    gs://mybucket/mydata.json \
    ./myschema

The following command loads data from gs://mybucket/mydata.json into a new ingestion-time partitioned table named mytable in mydataset. The schema is defined in a local schema file named myschema.

    bq load \
    --source_format=NEWLINE_DELIMITED_JSON \
    --time_partitioning_type=DAY \
    mydataset.mytable \
    gs://mybucket/mydata.json \
    ./myschema

The following command loads data from gs://mybucket/mydata.json into a partitioned table named mytable in mydataset. The table is partitioned on the mytimestamp column. The schema is defined in a local schema file named myschema.

    bq load \
    --source_format=NEWLINE_DELIMITED_JSON \
    --time_partitioning_field mytimestamp \
    mydataset.mytable \
    gs://mybucket/mydata.json \
    ./myschema

The following command loads data from gs://mybucket/mydata.json into a table named mytable in mydataset. The schema is auto detected.

    bq load \
    --autodetect \
    --source_format=NEWLINE_DELIMITED_JSON \
    mydataset.mytable \
    gs://mybucket/mydata.json

The following command loads data from gs://mybucket/mydata.json into a table named mytable in mydataset. The schema is defined inline in the format FIELD:DATA_TYPE, FIELD:DATA_TYPE.

    bq load \
    --source_format=NEWLINE_DELIMITED_JSON \
    mydataset.mytable \
    gs://mybucket/mydata.json \
    qtr:STRING,sales:FLOAT,year:STRING

The following command loads data from multiple files in gs://mybucket/into a table named mytable in mydataset. The Cloud Storage URI uses a wildcard. The schema is auto detected.

    bq load \
    --autodetect \
    --source_format=NEWLINE_DELIMITED_JSON \
    mydataset.mytable \
    gs://mybucket/mydata*.json

The following command loads data from multiple files in gs://mybucket/into a table named mytable in mydataset. The command includes a comma- separated list of Cloud Storage URIs with wildcards. The schema is defined in a local schema file named myschema.

    bq load \
    --source_format=NEWLINE_DELIMITED_JSON \
    mydataset.mytable \
    "gs://mybucket/00/*.json","gs://mybucket/01/*.json" \
    ./myschema

API

  1. Create a load job that points to the source data in Cloud Storage.
  2. (Optional) Specify your location in the location property in the jobReference section of thejob resource.
  3. The source URIs property must be fully qualified, in the formatgs://BUCKET/OBJECT. Each URI can contain one '*'wildcard character.
  4. Specify the JSON data format by setting the sourceFormat property toNEWLINE_DELIMITED_JSON.
  5. To check the job status, calljobs.get(JOB_ID*), replacing JOB_ID with the ID of the job returned by the initial request.
    • If status.state = DONE, the job completed successfully.
    • If the status.errorResult property is present, the request failed, and that object includes information describing what went wrong. When a request fails, no table is created and no data is loaded.
    • If status.errorResult is absent, the job finished successfully; although, there might have been some nonfatal errors, such as problems importing a few rows. Nonfatal errors are listed in the returned job object's status.errors property.

API notes:

C#

Before trying this sample, follow the C# setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery C# API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Use theBigQueryClient.CreateLoadJob()method to start a load job from Cloud Storage. To use JSONL, create aCreateLoadJobOptionsobject and set itsSourceFormatproperty toFileFormat.NewlineDelimitedJson.

Go

Before trying this sample, follow the Go setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Java

Before trying this sample, follow the Java setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Use theLoadJobConfiguration.builder(tableId, sourceUri)method to start a load job from Cloud Storage. To use newline-delimited JSON, use the LoadJobConfiguration.setFormatOptions(FormatOptions.json()).

Node.js

Before trying this sample, follow the Node.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

PHP

Before trying this sample, follow the PHP setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery PHP API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Python

Before trying this sample, follow the Python setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Use theClient.load_table_from_uri()method to start a load job from Cloud Storage. To use JSONL, set the LoadJobConfig.source_format propertyto the string NEWLINE_DELIMITED_JSON and pass the job config as thejob_config argument to the load_table_from_uri() method.

Ruby

Before trying this sample, follow the Ruby setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Ruby API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Use theDataset.load_job()method to start a load job from Cloud Storage. To use JSONL, set the format parameter to "json".

Loading nested and repeated JSON data

BigQuery supports loading nested and repeated data from source formats that support object-based schemas, such as JSON, Avro, ORC, Parquet, Firestore, and Datastore.

One JSON object, including any nested or repeated fields, must appear on each line.

The following example shows sample nested or repeated data. This table contains information about people. It consists of the following fields:

The JSON data file would look like the following. Notice that the address field contains an array of values (indicated by [ ]).

{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]} {"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}

The schema for this table would look like the following:

[ { "name": "id", "type": "STRING", "mode": "NULLABLE" }, { "name": "first_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "last_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "dob", "type": "DATE", "mode": "NULLABLE" }, { "name": "addresses", "type": "RECORD", "mode": "REPEATED", "fields": [ { "name": "status", "type": "STRING", "mode": "NULLABLE" }, { "name": "address", "type": "STRING", "mode": "NULLABLE" }, { "name": "city", "type": "STRING", "mode": "NULLABLE" }, { "name": "state", "type": "STRING", "mode": "NULLABLE" }, { "name": "zip", "type": "STRING", "mode": "NULLABLE" }, { "name": "numberOfYears", "type": "STRING", "mode": "NULLABLE" } ] } ]

For information on specifying a nested and repeated schema, seeSpecifying nested and repeated fields.

Loading semi-structured JSON data

BigQuery supports loading semi-structured data, in which a field can take values of different types. The following example shows data similar to the precedingnested and repeated JSON dataexample, except that the address field can be a STRING, a STRUCT, or an ARRAY:

{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","address":"123 First Avenue, Seattle WA 11111"}

{"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","address":{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"}}

{"id":"3","first_name":"Bob","last_name":"Doe","dob":"1982-01-10","address":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"}, "321 Main Street Hoboken NJ 44444"]}

You can load this data into BigQuery by using the following schema:

[ { "name": "id", "type": "STRING", "mode": "NULLABLE" }, { "name": "first_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "last_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "dob", "type": "DATE", "mode": "NULLABLE" }, { "name": "address", "type": "JSON", "mode": "NULLABLE" } ]

The address field is loaded into a column with typeJSON that allows it to hold the mixed types in the example. You can ingest data as JSON whether it contains mixed types or not. For example, you could specify JSON instead ofSTRING as the type for the first_name field. For more information, seeWorking with JSON data in GoogleSQL.

Appending to or overwriting a table with JSON data

You can load additional data into a table either from source files or by appending query results.

In the Google Cloud console, use the Write preference option to specify what action to take when you load data from a source file or from a query result.

You have the following options when you load additional data into a table:

Console option bq tool flag BigQuery API property Description
Write if empty Not supported WRITE_EMPTY Writes the data only if the table is empty.
Append to table --noreplace or --replace=false; if--[no]replace is unspecified, the default is append WRITE_APPEND (Default) Appends the data to the end of the table.
Overwrite table --replace or --replace=true WRITE_TRUNCATE Erases all existing data in a table before writing the new data. This action also deletes the table schema, row level security, and removes any Cloud KMS key.

If you load data into an existing table, the load job can append the data or overwrite the table.

You can append or overwrite a table by using one of the following:

Console

  1. In the Google Cloud console, go to the BigQuery page.
    Go to BigQuery
  2. In the Explorer pane, expand your project, and then select a dataset.
  3. In the Dataset info section, click Create table.
  4. In the Create table panel, specify the following details:
    1. In the Source section, select Google Cloud Storage in the Create table from list. Then, do the following:
      1. Select a file from the Cloud Storage bucket, or enter theCloud Storage URI. You cannot include multiple URIs in the Google Cloud console, but wildcards are supported. The Cloud Storage bucket must be in the same location as the dataset that contains the table you want to create, append, or overwrite.select source file to create a BigQuery table
      2. For File format, selectJSONL (Newline delimited JSON).
    2. In the Destination section, specify the following details:
      1. For Dataset, select the dataset in which you want to create the table.
      2. In the Table field, enter the name of the table that you want to create.
      3. Verify that the Table type field is set to Native table.
    3. In the Schema section, enter the schema definition. To enable the auto detection of a schema, select Auto detect. You can enter schema information manually by using one of the following methods:
      • Option 1: Click Edit as text and paste the schema in the form of a JSON array. When you use a JSON array, you generate the schema using the same process as creating a JSON schema file. You can view the schema of an existing table in JSON format by entering the following command:
        bq show --format=prettyjson dataset.table
      • Option 2: Click Add field and enter the table schema. Specify each field's Name,Type, and Mode.
    4. Optional: Specify Partition and cluster settings. For more information, seeCreating partitioned tables andCreating and using clustered tables. You cannot convert a table to a partitioned or clustered table by appending or overwriting it. The Google Cloud console does not support appending to or overwriting partitioned or clustered tables in a load job.
    5. Click Advanced options and do the following:
      • For Write preference, choose Append to table or Overwrite table.
      • For Number of errors allowed, accept the default value of 0 or enter the maximum number of rows containing errors that can be ignored. If the number of rows with errors exceeds this value, the job will result in an invalid message and fail. This option applies only to CSV and JSON files.
      • For Time zone, enter the default time zone that will apply when parsing timestamp values that have no specific time zone. Checkhere for more valid time zone names. If this value is not present, the timestamp values without specific time zone is parsed using default time zone UTC. (Preview).
      • For Date Format, enter the format elements that define how the DATE values are formatted in the input files. This field expects SQL styles format (for example, MM/DD/YYYY). If this value is present, this format is the only compatible DATE format.Schema autodetection will also decide DATE column type based on this format instead of the existing format. If this value is not present, the DATE field is parsed with thedefault formats. (Preview).
      • For Datetime Format, enter the format elements that define how the DATETIME values are formatted in the input files. This field expects SQL styles format (for example, MM/DD/YYYY HH24:MI:SS.FF3). If this value is present, this format is the only compatible DATETIME format.Schema autodetection will also decide DATETIME column type based on this format instead of the existing format. If this value is not present, the DATETIME field is parsed with thedefault formats. (Preview).
      • For Time Format, enter the format elements that define how the TIME values are formatted in the input files. This field expects SQL styles format (for example, HH24:MI:SS.FF3). If this value is present, this format is the only compatible TIME format.Schema autodetection will also decide TIME column type based on this format instead of the existing format. If this value is not present, the TIME field is parsed with thedefault formats. (Preview).
      • For Timestamp Format, enter the format elements that define how the TIMESTAMP values are formatted in the input files. This field expects SQL styles format (for example, MM/DD/YYYY HH24:MI:SS.FF3). If this value is present, this format is the only compatible TIMESTAMP format.Schema autodetection will also decide TIMESTAMP column type based on this format instead of the existing format. If this value is not present, the TIMESTAMP field is parsed with thedefault formats. (Preview).
      • If you want to ignore values in a row that are not present in the table's schema, then select Unknown values.
      • For Encryption, click Customer-managed key to use aCloud Key Management Service key. If you leave the Google-managed key setting, BigQueryencrypts the data at rest.
    6. Click Create table.

SQL

Use theLOAD DATA DDL statement. The following example appends a JSON file to the table mytable:

  1. In the Google Cloud console, go to the BigQuery page.
    Go to BigQuery
  2. In the query editor, enter the following statement:
    LOAD DATA INTO mydataset.mytable
    FROM FILES (
    format = 'JSON',
    uris = ['gs://bucket/path/file.json']);
  3. Click Run.

For more information about how to run queries, see Run an interactive query.

bq

Use the bq load command, specify NEWLINE_DELIMITED_JSON using the--source_format flag, and include a Cloud Storage URI. You can include a single URI, a comma-separated list of URIs, or a URI containing a wildcard.

Supply the schema inline, in a schema definition file, or useschema auto-detect.

Specify the --replace flag to overwrite the table. Use the --noreplace flag to append data to the table. If no flag is specified, the default is to append data.

It is possible to modify the table's schema when you append or overwrite it. For more information on supported schema changes during a load operation, see Modifying table schemas.

(Optional) Supply the --location flag and set the value to yourlocation.

Other optional flags include:

bq --location=LOCATION load
--[no]replace
--source_format=FORMAT
DATASET.TABLE
PATH_TO_SOURCE
SCHEMA

Replace the following:

Examples:

The following command loads data from gs://mybucket/mydata.json and overwrites a table named mytable in mydataset. The schema is defined using schema auto-detection.

    bq load \
    --autodetect \
    --replace \
    --source_format=NEWLINE_DELIMITED_JSON \
    mydataset.mytable \
    gs://mybucket/mydata.json

The following command loads data from gs://mybucket/mydata.json and appends data to a table named mytable in mydataset. The schema is defined using a JSON schema file — myschema.

    bq load \
    --noreplace \
    --source_format=NEWLINE_DELIMITED_JSON \
    mydataset.mytable \
    gs://mybucket/mydata.json \
    ./myschema

API

  1. Create a load job that points to the source data in Cloud Storage.
  2. (Optional) Specify your location in the location property in the jobReference section of the job resource.
  3. The source URIs property must be fully-qualified, in the formatgs://BUCKET/OBJECT. You can include multiple URIs as a comma-separated list. Thewildcards are also supported.
  4. Specify the data format by setting theconfiguration.load.sourceFormat property to NEWLINE_DELIMITED_JSON.
  5. Specify the write preference by setting theconfiguration.load.writeDisposition property to WRITE_TRUNCATE orWRITE_APPEND.

Go

Before trying this sample, follow the Go setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Java

Node.js

Before trying this sample, follow the Node.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

PHP

Before trying this sample, follow the PHP setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery PHP API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Python

To replace the rows in an existing table, set theLoadJobConfig.write_disposition propertyto the string WRITE_TRUNCATE.

Before trying this sample, follow the Python setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Ruby

To replace the rows in an existing table, set the write parameter ofTable.load_job()to "WRITE_TRUNCATE".

Before trying this sample, follow the Ruby setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Ruby API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Loading hive-partitioned JSON data

BigQuery supports loading hive partitioned JSON data stored on Cloud Storage and populates the hive partitioning columns as columns in the destination BigQuery managed table. For more information, seeLoading externally partitioned data.

Details of loading JSON data

This section describes how BigQuery parses various data types when loading JSON data.

Data types

Boolean. BigQuery can parse any of the following pairs for Boolean data: 1 or 0, true or false, t or f, yes or no, or y or n (all case insensitive). Schema autodetectionautomatically detects any of these except 0 and 1.

Bytes. Columns with BYTES types must be encoded as Base64.

Date. Columns with DATE types must be in the format YYYY-MM-DD.

Datetime. Columns with DATETIME types must be in the format YYYY-MM-DD HH:MM:SS[.SSSSSS].

Geography. Columns with GEOGRAPHY types must contain strings in one of the following formats:

If you use WKB, the value should be hex encoded.

The following list shows examples of valid data:

Before loading GEOGRAPHY data, also readLoading geospatial data.

Interval. Columns with INTERVAL types must be inISO 8601 formatPYMDTHMS, where:

You can indicate a negative value by prepending a dash (-).

The following list shows examples of valid data:

To load INTERVAL data, you must use the bq load command and use the --schemaflag to specify a schema. You can't upload INTERVAL data by using the console.

Time. Columns with TIME types must be in the format HH:MM:SS[.SSSSSS].

Timestamp. BigQuery accepts various timestamp formats. The timestamp must include a date portion and a time portion.

For example, any of the following are valid timestamp values:

If you provide a schema, BigQuery also accepts Unix epoch time for timestamp values. However, schema autodetection doesn't detect this case, and treats the value as a numeric or string type instead.

Examples of Unix epoch timestamp values:

Array (repeated field). The value must be a JSON array or null. JSONnull is converted to SQL NULL. The array itself cannot contain nullvalues.

Schema auto-detection

This section describes the behavior ofschema auto-detection when loading JSON files.

JSON nested and repeated fields

BigQuery infers nested and repeated fields in JSON files. If a field value is a JSON object, then BigQuery loads the column as aRECORD type. If a field value is an array, then BigQuery loads the column as a repeated column. For an example of JSON data with nested and repeated data, seeLoading nested and repeated JSON data.

String conversion

If you enable schema auto-detection, then BigQuery converts strings into Boolean, numeric, or date/time types when possible. For example, using the following JSON data, schema auto-detection converts the id field to an INTEGER column:

{ "name":"Alice","id":"12"}
{ "name":"Bob","id":"34"}
{ "name":"Charles","id":"45"}

Encoding types

BigQuery expects JSON data to be UTF-8 encoded. If you have JSON files with other supported encoding types, you should explicitly specify the encoding by using the --encoding flag so that BigQuery converts the data to UTF-8.

BigQuery supports the following encoding types for JSON files:

JSON options

To change how BigQuery parses JSON data, specify additional options in the Google Cloud console, the bq command-line tool, the API, or the client libraries.

JSON option Console option bq tool flag BigQuery API property Description
Number of bad records allowed Number of errors allowed --max_bad_records maxBadRecords (Java,Python) (Optional) The maximum number of bad records that BigQuery can ignore when running the job. If the number of bad records exceeds this value, an invalid error is returned in the job result. The default value is `0`, which requires that all records are valid.
Unknown values Ignore unknown values --ignore_unknown_values ignoreUnknownValues (Java,Python) (Optional) Indicates whether BigQuery should allow extra values that are not represented in the table schema. If true, the extra values are ignored. If false, records with extra columns are treated as bad records, and if there are too many bad records, an invalid error is returned in the job result. The default value is false. The `sourceFormat` property determines what BigQuery treats as an extra value: CSV: trailing columns, JSON: named values that don't match any column names.
Encoding None -E or --encoding encoding (Python) (Optional) The character encoding of the data. The supported values are UTF-8, ISO-8859-1, UTF-16BE, UTF-16LE, UTF-32BE, or UTF-32LE. The default value is UTF-8.
Time Zone Time Zone --time_zone None (Preview) (Optional) Default time zone that is applied when parsing timestamp values that have no specific time zone. Checkvalid time zone names. If this value is not present, the timestamp values without specific time zone is parsed using default time zone UTC.
Date Format Date Format --date_format None (Preview) (Optional)Format elements that define how the DATE values are formatted in the input files (for example, MM/DD/YYYY). If this value is present, this format is the only compatible DATE format.Schema autodetection will also decide DATE column type based on this format instead of the existing format. If this value is not present, the DATE field is parsed with thedefault formats.
Datetime Format Datetime Format --datetime_format None (Preview) (Optional)Format elements that define how the DATETIME values are formatted in the input files (for example, MM/DD/YYYY HH24:MI:SS.FF3). If this value is present, this format is the only compatible DATETIME format.Schema autodetection will also decide DATETIME column type based on this format instead of the existing format. If this value is not present, the DATETIME field is parsed with thedefault formats.
Time Format Time Format --time_format None (Preview) (Optional)Format elements that define how the TIME values are formatted in the input files (for example, HH24:MI:SS.FF3). If this value is present, this format is the only compatible TIME format.Schema autodetection will also decide TIME column type based on this format instead of the existing format. If this value is not present, the TIME field is parsed with thedefault formats.
Timestamp Format Timestamp Format --timestamp_format None (Preview) (Optional)Format elements that define how the TIMESTAMP values are formatted in the input files (for example, MM/DD/YYYY HH24:MI:SS.FF3). If this value is present, this format is the only compatible TIMESTAMP format.Schema autodetection will also decide TIMESTAMP column type based on this format instead of the existing format. If this value is not present, the TIMESTAMP field is parsed with thedefault formats.

What's next

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-06-12 UTC.