Uploading Jsonlines to Google Bucket With Ruby

• Why Google
• Solutions
• Products
• Pricing
• Getting started
• Docs
  • Overview
  • Guides
  • Reference
  • Samples
  • Support
  • Resources
• Support
• Panel
• Contact U.s.a.
• Get started for free

Loading JSON data from Cloud Storage

Loading JSON files from Cloud Storage

You lot tin load newline delimited JSON data from Cloud Storage into a new table or segmentation, or append to or overwrite an existing tabular array or partition. When your data is loaded into BigQuery, it is converted into columnar format for Capacitor (BigQuery'south storage format).

When you lot 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 newline delimited JSON format is the same format as the JSON Lines format.

For information about loading JSON information from a local file, run across Loading data from local files.

Limitations

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

• JSON information must be newline delimited. Each JSON object must be on a split up line in the file.
• If you use gzip pinch, BigQuery cannot read the information in parallel. Loading compressed JSON information into BigQuery is slower than loading uncompressed data.
• Yous cannot include both compressed and uncompressed files in the same load job.
• The maximum size for a gzip file is 4 GB.

• BigQuery does non support maps or dictionaries in JSON, due to potential lack of schema information in a pure JSON dictionary. For case, to represent a list of products in a cart "products": {"my_product": forty.0, "product2" : xvi.five} is not valid, just "products": [{"product_name": "my_product", "amount": 40.0}, {"product_name": "product2", "amount": 16.five}] is valid.

  If you need to keep the entire JSON object, then information technology should be put into a string column, which can exist queried using JSON functions.

• If you lot use the BigQuery API to load an integer outside the range of [-2⁵³+i, 2⁵³-1] (usually this means larger than nine,007,199,254,740,991), into an integer (INT64) cavalcade, pass it as a string to avoid data corruption. This outcome is caused by a limitation on integer size in JSON/ECMAScript. For more data, run across the Numbers department of RFC 7159.

• When you load CSV or JSON data, values in Engagement columns must use the dash (-) separator and the date must be in the post-obit format: YYYY-MM-DD (year-month-day).
• When y'all load JSON or CSV data, values in TIMESTAMP columns must utilise a dash (-) separator for the date portion of the timestamp, and the appointment must exist in the following format: YYYY-MM-DD (yr-month-twenty-four hour period). The hh:mm:ss (hour-minute-2nd) portion of the timestamp must use a colon (:) separator.

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each job in this certificate.

Required permissions

To load data into BigQuery, you demand 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 saucepan that contains your information.

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 post-obit IAM permissions:

• bigquery.tables.create
• bigquery.tables.updateData
• bigquery.tables.update
• bigquery.jobs.create

Each of the following predefined IAM roles includes the permissions that y'all demand in club to load data into a BigQuery table or partitioning:

• roles/bigquery.dataEditor
• roles/bigquery.dataOwner
• roles/bigquery.admin (includes the bigquery.jobs.create permission)
• bigquery.user (includes the bigquery.jobs.create permission)
• bigquery.jobUser (includes the bigquery.jobs.create permission)

Additionally, if you accept 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 information from Cloud Storage

To load information from a Deject Storage bucket, you need the following IAM permissions:

• storage.objects.go
• storage.objects.listing (required if y'all are using a URI wildcard)

The predefined IAM role roles/storage.objectViewer includes all the permissions you need in order to load information from a Cloud Storage bucket.

Loading JSON data into a new table

Y'all can load newline delimited JSON information from Cloud Storage into a new BigQuery tabular array by using one of the following:

• The Cloud Panel
• The bq control-line tool'south bq load control
• The jobs.insert API method and configuring a load job
• The client libraries

To load JSON information from Deject Storage into a new BigQuery table:

Console

1. In the Deject Console, open the BigQuery page.

   Get to BigQuery

2. In the Explorer console, aggrandize your project and select a dataset.

3. Expand the Actions option and click Open.

4. In the details panel, click Create table .

5. On the Create table page, in the Source department:

   • For Create table from, select Cloud Storage.

   • In the source field, scan to or enter the Cloud Storage URI. You cannot include multiple URIs in the Cloud Console, merely wildcards are supported. The Cloud Storage saucepan must exist in the same location as the dataset that contains the table you're creating.

     

   • For File format, select JSON (Newline delimited).

6. On the Create tabular array folio, in the Destination department:

   • For Dataset proper name, choose the appropriate dataset.

     

   • Verify that Tabular array type is fix to Native table.

   • In the Table name field, enter the proper name of the table you're creating in BigQuery.

7. In the Schema section, for Automobile detect, cheque Schema and input parameters to enable schema auto detection. Alternatively, yous can manually enter the schema definition by:

   • Enabling Edit every bit text and entering the tabular array schema as a JSON array.

     

   • Using Add field to manually input the schema.

     

8. (Optional) To partition the table, choose your options in the Division and cluster settings. For more than information, see Creating partitioned tables.

9. (Optional) For Partitioning filter, click the Require partition filter box to require users to include a WHERE clause that specifies the partitions to query. Requiring a partition filter tin reduce toll and improve performance. For more data, run into Querying partitioned tables. This option is unavailable if No partitioning is selected.

10. (Optional) To cluster the table, in the Clustering club box, enter between i and iv field names.

11. (Optional) Click Advanced options.

    • For Write preference, leave Write if empty selected. This selection 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 results in an invalid message and fails.
    • For Unknown values, check Ignore unknown values to ignore any values in a row that are non present in the table's schema.
    • For Encryption, click Customer-managed key to utilise a Cloud Key Management Service key. If you exit the Google-managed key setting, BigQuery encrypts the data at residue.

12. Click Create table.

bq

Use the bq load command, specify NEWLINE_DELIMITED_JSON using the --source_format flag, and include a Cloud Storage URI. Yous tin include a single URI, a comma-separated listing of URIs, or a URI containing a wildcard. Supply the schema inline, in a schema definition file, or use schema machine-detect.

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

Other optional flags include:

• --max_bad_records: An integer that specifies the maximum number of bad records allowed before the entire job fails. The default value is 0. At most, five errors of any blazon are returned regardless of the --max_bad_records value.
• --ignore_unknown_values: When specified, allows and ignores actress, unrecognized values in CSV or JSON information.
• --autodetect: When specified, enable schema auto-detection for CSV and JSON data.
• --time_partitioning_type: Enables time-based partitioning on a table and sets the partition blazon. Possible values are HOUR, Twenty-four hours, MONTH, and Year. This flag is optional when you create a tabular array partitioned on a DATE, DATETIME, or TIMESTAMP column. The default partitioning blazon for time-based partitioning is Twenty-four hours. You cannot change the partitioning specification on an existing table.
• --time_partitioning_expiration: An integer that specifies (in seconds) when a time-based sectionalisation should be deleted. The expiration time evaluates to the partition's UTC date plus the integer value.
• --time_partitioning_field: The DATE or TIMESTAMP cavalcade used to create a partitioned tabular array. If time-based sectionalization is enabled without this value, an ingestion-time partitioned tabular array is created.
• --require_partition_filter: When enabled, this option requires users to include a WHERE clause that specifies the partitions to query. Requiring a sectionalization filter can reduce toll and better functioning. For more information, encounter Querying partitioned tables.
• --clustering_fields: A comma-separated list of upwardly to four cavalcade names used to create a clustered tabular array.

• --destination_kms_key: The Cloud KMS key for encryption of the table data.

  For more information on partitioned tables, see:

  • Creating partitioned tables

  For more information on clustered tables, see:

  • Creating and using clustered tables

  For more information on table encryption, see:

  • Protecting data with Cloud KMS keys

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

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

Supplant the following:

• LOCATION : your location. The --location flag is optional. For example, if y'all are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.
• FORMAT : NEWLINE_DELIMITED_JSON.
• DATASET : an existing dataset.
• Table : the proper noun of the table into which y'all're loading data.
• PATH_TO_SOURCE : a fully qualified Cloud Storage URI or a comma-separated list of URIs. Wildcards are also supported.
• SCHEMA : a valid schema. The schema can be a local JSON file, or it can be typed inline every bit part of the command. If yous employ a schema file, practise non give it an extension. Y'all can also utilise the --autodetect flag instead of supplying a schema definition.

Examples:

The following command loads information from gs://mybucket/mydata.json into a tabular array named mytable in mydataset. The schema is divers 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-fourth dimension 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 cavalcade. The schema is divers 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 information 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 control 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:Cord                                              

The post-obit 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 belongings in the jobReference section of the chore resource.

3. The source URIs property must exist fully qualified, in the format gs://Bucket/OBJECT . Each URI can contain one '*' wildcard character.

4. Specify the JSON data format past setting the sourceFormat property to NEWLINE_DELIMITED_JSON.

5. To check the chore status, call jobs.get(JOB_ID*), replacing JOB_ID with the ID of the job returned by the initial request.

   • If status.country = Washed, the task completed successfully.
   • If the status.errorResult property is nowadays, the request failed, and that object includes information describing what went incorrect. When a request fails, no tabular array is created and no information is loaded.
   • If status.errorResult is absent-minded, the chore finished successfully; although, in that location might have been some nonfatal errors, such equally problems importing a few rows. Nonfatal errors are listed in the returned job object's status.errors property.

API notes:

• Load jobs are diminutive and consequent; if a load job fails, none of the data is bachelor, and if a load chore succeeds, all of the data is available.

• As a best practice, generate a unique ID and pass information technology as jobReference.jobId when calling jobs.insert to create a load chore. This arroyo is more than robust to network failure because the customer tin can poll or retry on the known chore ID.

• Calling jobs.insert on a given chore ID is idempotent. You can retry as many times as you like on the aforementioned chore ID, and at most, one of those operations succeed.

C#

Before trying this sample, follow the C# setup instructions in the BigQuery quickstart using customer libraries. For more information, meet the BigQuery C# API reference documentation.

Use the BigQueryClient.CreateLoadJob() method to first a load task from Cloud Storage. To use newline-delimited JSON, create a CreateLoadJobOptions object and set its SourceFormat property to FileFormat.NewlineDelimitedJson.

Go

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

Coffee

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

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

Node.js

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

PHP

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

Python

Earlier trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, run across the BigQuery Python API reference documentation.

Use the Client.load_table_from_uri() method to beginning a load chore from Cloud Storage. To use newline-delimited JSON, set the LoadJobConfig.source_format belongings to the cord NEWLINE_DELIMITED_JSON and pass the task config every bit the job_config argument to the load_table_from_uri() method.

Ruby

Before trying this sample, follow the Cherry setup instructions in the BigQuery quickstart using client libraries. For more data, see the BigQuery Ruby-red API reference documentation.

Use the Dataset.load_job() method to start a load job from Cloud Storage. To apply newline-delimited JSON, set the format parameter to "json".

Loading nested and repeated JSON information

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

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

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

• id
• first_name
• last_name
• dob (appointment of nascency)
• addresses (a nested and repeated field)
  • addresses.status (current or previous)
  • addresses.address
  • addresses.urban center
  • addresses.state
  • addresses.zippo
  • addresses.numberOfYears (years at the accost)

The JSON data file would look like the post-obit. Detect that the accost 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 Artery","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"condition":"previous","address":"456 Main Street","metropolis":"Portland","state":"OR","cypher":"22222","numberOfYears":"v"}]} {"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-ten-16","addresses":[{"status":"current","accost":"789 Whatsoever Avenue","metropolis":"New York","country":"NY","zip":"33333","numberOfYears":"two"},{"condition":"previous","accost":"321 Main Street","city":"Hoboken","state":"NJ","aught":"44444","numberOfYears":"3"}]}                  

The schema for this tabular array would look similar the following:

[     {         "name": "id",         "type": "STRING",         "way": "NULLABLE"     },     {         "proper noun": "first_name",         "type": "Cord",         "mode": "NULLABLE"     },     {         "name": "last_name",         "type": "Cord",         "fashion": "NULLABLE"     },     {         "proper name": "dob",         "type": "Date",         "manner": "NULLABLE"     },     {         "name": "addresses",         "blazon": "RECORD",         "mode": "REPEATED",         "fields": [             {                 "name": "status",                 "type": "STRING",                 "mode": "NULLABLE"             },             {                 "name": "accost",                 "blazon": "STRING",                 "way": "NULLABLE"             },             {                 "name": "city",                 "blazon": "Cord",                 "mode": "NULLABLE"             },             {                 "name": "land",                 "blazon": "STRING",                 "style": "NULLABLE"             },             {                 "name": "zip",                 "type": "STRING",                 "mode": "NULLABLE"             },             {                 "name": "numberOfYears",                 "type": "STRING",                 "manner": "NULLABLE"             }         ]     } ]                  

For data on specifying a nested and repeated schema, see Specifying nested and repeated fields.

Appending to or overwriting a table with JSON data

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

In the Cloud Panel, utilise the Write preference selection to specify what action to have when you lot load information from a source file or from a query result.

You lot have the following options when you load boosted information into a table:

Panel option	bq tool flag	BigQuery API property	Clarification
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 suspend	WRITE_APPEND	(Default) Appends the information to the end of the table.
Overwrite table	--supplant or --replace=true	WRITE_TRUNCATE	Erases all existing data in a table before writing the new data. This action besides deletes the table schema and removes any Cloud KMS primal.

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

Yous can append or overwrite a tabular array past using one of the post-obit:

• The Deject Console
• The bq command-line tool'southward bq load control
• The jobs.insert API method and configuring a load chore
• The customer libraries

Console

1. In the Cloud Console, open up the BigQuery page.

   Become to BigQuery

2. In the Explorer panel, aggrandize your project and select a dataset.

3. Expand the Actions option and click Open.

4. In the details console, click Create table .

5. On the Create tabular array page, in the Source section:

   • For Create table from, select Cloud Storage.

   • In the source field, browse to or enter the Cloud Storage URI. You cannot include multiple URIs in the Cloud Console, just wildcards are supported. The Cloud Storage bucket must be in the same location as the dataset that contains the table you lot're appending or overwriting.

     

   • For File format, select JSON (Newline delimited).

6. On the Create tabular array page, in the Destination section:

   • For Dataset name, choose the advisable dataset.

     

   • In the Table proper name field, enter the name of the table you're appending or overwriting in BigQuery.

   • Verify that Table blazon is set to Native table.

7. In the Schema section, for Machine discover, check Schema and input parameters to enable schema auto detection. Alternatively, you can manually enter the schema definition by:

   • Enabling Edit every bit text and entering the table schema equally a JSON assortment.

     

   • Using Add field to manually input the schema.

     

8. For Sectionalization and cluster settings, leave the default values. Y'all cannot convert a tabular array to a partitioned or amassed table by appending or overwriting information technology. The Deject Panel does non back up appending to or overwriting partitioned or clustered tables in a load job.

9. Click Advanced options.

   • 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 task results in an invalid message and fails.
   • For Unknown values, check Ignore unknown values to ignore any values in a row that are non present in the table's schema.

   • For Encryption, click Customer-managed cardinal to use a Deject Key Management Service key. If you get out the Google-managed fundamental setting, BigQuery encrypts the data at rest.

     

10. Click Create table.

bq

Use the bq load control, specify NEWLINE_DELIMITED_JSON using the --source_format flag, and include a Cloud Storage URI. Y'all tin include a unmarried URI, a comma-separated list of URIs, or a URI containing a wildcard.

Supply the schema inline, in a schema definition file, or employ schema auto-notice.

Specify the --supervene upon flag to overwrite the table. Employ the --noreplace flag to append information to the table. If no flag is specified, the default is to append information.

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

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

Other optional flags include:

• --max_bad_records: An integer that specifies the maximum number of bad records allowed before the entire job fails. The default value is 0. At most, 5 errors of any type are returned regardless of the --max_bad_records value.
• --ignore_unknown_values: When specified, allows and ignores extra, unrecognized values in CSV or JSON information.
• --autodetect: When specified, enable schema auto-detection for CSV and JSON information.
• --destination_kms_key: The Cloud KMS key for encryption of the table data.

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

Replace the following:

• LOCATION : your location. The --location flag is optional. Y'all can set a default value for the location using the .bigqueryrc file.
• FORMAT : NEWLINE_DELIMITED_JSON.
• DATASET : an existing dataset.
• Tabular array : the name of the table into which you're loading data.
• PATH_TO_SOURCE : a fully qualified Deject Storage URI or a comma-separated listing of URIs. Wildcards are also supported.
• SCHEMA : a valid schema. The schema can be a local JSON file, or information technology can be typed inline as part of the command. Y'all can also use the --autodetect flag instead of supplying a schema definition.

Examples:

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

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

The following control 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 holding in the jobReference section of the job resources.

3. The source URIs property must be fully-qualified, in the format gs://Saucepan/OBJECT . You can include multiple URIs as a comma-separated list. The wildcards are too supported.

4. Specify the data format by setting the configuration.load.sourceFormat property to NEWLINE_DELIMITED_JSON.

5. Specify the write preference by setting the configuration.load.writeDisposition holding to WRITE_TRUNCATE or WRITE_APPEND.

Go

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

Java

Node.js

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

PHP

Earlier trying this sample, follow the PHP setup instructions in the BigQuery quickstart using client libraries. For more data, run into the BigQuery PHP API reference documentation.

Python

To replace the rows in an existing table, set the LoadJobConfig.write_disposition belongings to the string WRITE_TRUNCATE.

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

Ruby

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

Before trying this sample, follow the Cherry-red setup instructions in the BigQuery quickstart using client libraries. For more data, encounter the BigQuery Ruby API reference documentation.

Loading hive-partitioned JSON data

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

Details of loading JSON information

This department 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: i or 0, truthful or false, t or f, yes or no, or y or northward (all case insensitive). Schema autodetection automatically detects any of these except 0 and one.

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

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

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

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

• Well-known text (WKT)
• Well-known binary (WKB)
• GeoJSON

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

The following listing shows examples of valid data:

• WKT: POINT(1 2)
• GeoJSON: { "type": "Point", "coordinates": [1, 2] }
• Hex encoded WKB: 0101000000feffffffffffef3f0000000000000040

Earlier loading GEOGRAPHY data, also read Loading geospatial data.

Interval. Columns with INTERVAL types must be in ISO 8601 format PYMDTHMS, where:

• P = Designator that indicates that the value represents a duration. Yous must e'er include this.
• Y = Twelvemonth
• M = Month
• D = Day
• T = Designator that denotes the time portion of the duration. Y'all must always include this.
• H = Hour
• M = Minute
• S = Second. Seconds can exist denoted as a whole value or every bit a fractional value of upwards to half dozen digits, at microsecond precision.

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

The following list shows examples of valid data:

• P-10000Y0M-3660000DT-87840000H0M0S
• P0Y0M0DT0H0M0.000001S
• P10000Y0M3660000DT87840000H0M0S

To load INTERVAL information, you must use the bq load command and use the --schema flag to specify a schema. Y'all can't upload INTERVAL data by using the panel.

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

Timestamp. BigQuery accepts diverse timestamp formats. The timestamp must include a appointment portion and a fourth dimension portion.

• The date portion tin be formatted as YYYY-MM-DD or YYYY/MM/DD.

• The timestamp portion must be formatted as HH:MM[:SS[.SSSSSS]] (seconds and fractions of seconds are optional).

• The engagement and time must be separated by a infinite or 'T'.

• Optionally, the engagement and time can be followed by a UTC beginning or the UTC zone designator (Z). For more information, encounter Time zones.

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

• 2018-08-19 12:11
• 2018-08-xix 12:xi:35
• 2018-08-nineteen 12:11:35.22
• 2018/08/19 12:11
• 2018-07-05 12:54:00 UTC
• 2018-08-19 07:eleven:35.220 -05:00
• 2018-08-19T12:11:35.220Z

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

Examples of Unix epoch timestamp values:

• 1534680695
• 1.534680695e11

Array (repeated field). The value must be a JSON array or zero. JSON null is converted to SQL NULL. The array itself cannot incorporate nil values.

JSON options

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

JSON option	Console option	bq tool flag	BigQuery API property	Clarification
Number of bad records allowed	Number of errors immune	--max_bad_records	maxBadRecords (Java, Python)	(Optional) The maximum number of bad records that BigQuery can ignore when running the task. 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 permit actress values that are not represented in the table schema. If true, the actress values are ignored. If false, records with extra columns are treated as bad records, and if in that location are as well many bad records, an invalid error is returned in the task result. The default value is simulated. The `sourceFormat` property determines what BigQuery treats as an extra value: CSV: trailing columns, JSON: named values that don't match any column names.

Except every bit otherwise noted, the content of this folio is licensed nether the Artistic Commons Attribution iv.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 2022-04-14 UTC.

househusat1994.blogspot.com

Source: https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-json