Console

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the left pane, clickexploreExplorer.
3. In theExplorer pane, expand your project, clickDatasets, and then select a dataset.
4. In theDataset info section, clickadd_boxCreate table.
5. In theCreate table pane, specify the following details:
1. In theSource section, selectGoogle Cloud Storage in theCreate 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, butwildcards 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.
   2. ForFile format, selectJSONL (Newline delimited JSON).
2. In theDestination section, specify the following details:
   1. ForDataset, select the dataset in which you want to create the table.
   2. In theTable field, enter the name of the table that you want to create.
   3. Verify that theTable type field is set toNative table.
3. In theSchema section, enter theschema definition. To enable theauto detection of a schema, selectAuto detect. You can enter schema information manually by using one of the following methods:
   • Option 1: ClickEdit 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 ascreating a JSON schema file. You can view the schema of an existing table in JSON format by entering the following command:

     bqshow--format=prettyjsondataset.table

   • Option 2: Clickadd_boxAdd field and enter the table schema. Specify each field'sName,Type, andMode.
4. Optional: SpecifyPartition and cluster settings. For more information, seeCreating partitioned tables andCreating and using clustered tables.
5. ClickAdvanced options and do the following:
   • ForWrite preference, leaveWrite if empty selected. This option creates a new table and loads your data into it.
   • ForNumber of errors allowed, accept the default value of0 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 aninvalid message and fail. This option applies only to CSV and JSON files.
   • ForTime 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).
   • ForDate 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).
   • ForDatetime 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).
   • ForTime 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).
   • ForTimestamp 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 selectUnknown values.
   • ForEncryption, clickCustomer-managed key to use aCloud Key Management Service key. If you leave theGoogle-managed key setting, BigQueryencrypts the data at rest.
6. ClickCreate table.

SQL

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

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the query editor, enter the following statement:

   LOADDATAOVERWRITEmydataset.mytable(xINT64,ySTRING)FROMFILES(format='JSON',uris=['gs://bucket/path/file.json']);

3. Clickplay_circleRun.

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

bq

Use thebq load command, specifyNEWLINE_DELIMITED_JSON using the--source_format flag, and include aCloud Storage URI.You can include a single URI, a comma-separated list of URIs, or a URIcontaining awildcard.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:

• --max_bad_records: An integer that specifies the maximum number of badrecords allowed before the entire job fails. The default value is0. Atmost, five 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 data.
• --time_zone: (Preview) An optionaldefault time zone that will apply when parsing timestamp values that have nospecific time zone in CSV or JSON data.
• --date_format: (Preview) An optionalcustom string that defines how the DATE values are formatted in CSV or JSONdata.
• --datetime_format: (Preview) Anoptional custom string that defines how the DATETIME values are formatted inCSV or JSON data.
• --time_format: (Preview) An optionalcustom string that defines how the TIME values are formatted in CSV or JSONdata.
• --timestamp_format: (Preview) Anoptional custom string that defines how the TIMESTAMP values are formattedin CSV or JSON data.
• --autodetect: When specified, enable schema auto-detection for CSV andJSON data.
• --time_partitioning_type: Enables time-based partitioning on a table andsets the partition type. Possible values areHOUR,DAY,MONTH, andYEAR. This flag is optional when you create atable partitioned on aDATE,DATETIME, orTIMESTAMP column. Thedefault partition type for time-based partitioning isDAY. You cannotchange the partitioning specification on an existing table.
• --time_partitioning_expiration: An integer that specifies (in seconds)when a time-based partition should be deleted. The expiration time evaluatesto the partition's UTC date plus the integer value.
• --time_partitioning_field: TheDATE orTIMESTAMP column used tocreate a partitioned table. If time-based partitioning is enabled withoutthis value, an ingestion-time partitioned table is created.
• --require_partition_filter: When enabled, this option requires usersto include aWHERE clause that specifies the partitions to query.Requiring a partition filter can reduce cost and improve performance.For more information, seeRequire a partition filter in queries.
• --clustering_fields: A comma-separated list of up to four column namesused to create aclustered table.

• --destination_kms_key: The Cloud KMS key for encryption of thetable 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=LOCATIONload\--source_format=FORMAT\DATASET.TABLE\PATH_TO_SOURCE\SCHEMA

Replace the following:

• LOCATION: your location. The--location flag isoptional. For example, if you are using BigQuery in theTokyo region, you can set the flag's value toasia-northeast1. You canset a default value for the location using the.bigqueryrc file.
• FORMAT:NEWLINE_DELIMITED_JSON.
• DATASET: an existing dataset.
• TABLE: the name of the table into which you'reloading data.
• PATH_TO_SOURCE: a fully qualifiedCloud Storage URIor a comma-separated list of URIs.Wildcardsare also supported.
• SCHEMA: a valid schema. The schema can be a localJSON file, or it can be typed inline as part of the command. If you use aschema file, do not give it an extension. You can alsouse the--autodetect flag instead of supplying a schema definition.

Examples:

The following command loads data fromgs://mybucket/mydata.json into atable namedmytable inmydataset. The schema is defined in a localschema file namedmyschema.

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

The following command loads data fromgs://mybucket/mydata.json into a newingestion-time partitioned table namedmytable inmydataset. The schemais defined in a local schema file namedmyschema.

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

The following command loads data fromgs://mybucket/mydata.json into apartitioned table namedmytable inmydataset. The table is partitionedon themytimestamp column. The schema is defined in a local schema filenamedmyschema.

bqload\--source_format=NEWLINE_DELIMITED_JSON\--time_partitioning_fieldmytimestamp\mydataset.mytable\gs://mybucket/mydata.json\./myschema

The following command loads data fromgs://mybucket/mydata.json into atable namedmytable inmydataset. The schema is auto detected.

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

The following command loads data fromgs://mybucket/mydata.json into atable namedmytable inmydataset. The schema is defined inline in theformatFIELD:DATA_TYPE,FIELD:DATA_TYPE.

bqload\--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 ings://mybucket/into a table namedmytable inmydataset. The Cloud Storage URI uses awildcard. The schema is auto detected.

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

The following command loads data from multiple files ings://mybucket/into a table namedmytable inmydataset. The command includes a comma-separated list of Cloud Storage URIs with wildcards. The schema isdefined in a local schema file namedmyschema.

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

API

1. Create aload job that points to the source data in Cloud Storage.

2. (Optional) Specify yourlocation inthelocation property in thejobReference section of thejob resource.

3. Thesource URIs property must be fully qualified, in the formatgs://BUCKET/OBJECT.Each URI can contain one '*'wildcard character.

4. Specify theJSON data format by setting thesourceFormat property toNEWLINE_DELIMITED_JSON.

5. To check the job status, calljobs.get(JOB_ID*),replacingJOB_ID with the ID of the job returned bythe initial request.

   • Ifstatus.state = DONE, the job completed successfully.
   • If thestatus.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.
   • Ifstatus.errorResult is absent, the job finished successfully;although, there might have been some nonfatal errors, such as problemsimporting a few rows. Nonfatal errors are listed in the returned jobobject'sstatus.errors property.

API notes:

• Load jobs are atomic and consistent; if a load job fails, none of the datais available, and if a load job succeeds, all of the data is available.

• As a best practice, generate a unique ID and pass it asjobReference.jobId when callingjobs.insert to create a load job. Thisapproach is more robust to network failure because the client can poll orretry on the known job ID.

• Callingjobs.insert on a given job ID is idempotent. You can retry asmany times as you like on the same job ID, and at most, one of thoseoperations succeed.

C#

usingGoogle.Apis.Bigquery.v2.Data;usingGoogle.Cloud.BigQuery.V2;usingSystem;publicclassBigQueryLoadTableGcsJson{publicvoidLoadTableGcsJson(stringprojectId="your-project-id",stringdatasetId="your_dataset_id"){BigQueryClientclient=BigQueryClient.Create(projectId);vargcsURI="gs://cloud-samples-data/bigquery/us-states/us-states.json";vardataset=client.GetDataset(datasetId);varschema=newTableSchemaBuilder{{"name",BigQueryDbType.String},{"post_abbr",BigQueryDbType.String}}.Build();TableReferencedestinationTableRef=dataset.GetTableReference(tableId:"us_states");// Create job configurationvarjobOptions=newCreateLoadJobOptions(){SourceFormat=FileFormat.NewlineDelimitedJson};// Create and run jobBigQueryJobloadJob=client.CreateLoadJob(sourceUri:gcsURI,destination:destinationTableRef,schema:schema,options:jobOptions);loadJob=loadJob.PollUntilCompleted().ThrowOnAnyError();// Waits for the job to complete.// Display the number of rows uploadedBigQueryTabletable=client.GetTable(destinationTableRef);Console.WriteLine($"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}");}}

Go

import("context""fmt""cloud.google.com/go/bigquery")// importJSONExplicitSchema demonstrates loading newline-delimited JSON data from Cloud Storage// into a BigQuery table and providing an explicit schema for the data.funcimportJSONExplicitSchema(projectID,datasetID,tableIDstring)error{// projectID := "my-project-id"// datasetID := "mydataset"// tableID := "mytable"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()gcsRef:=bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states.json")gcsRef.SourceFormat=bigquery.JSONgcsRef.Schema=bigquery.Schema{{Name:"name",Type:bigquery.StringFieldType},{Name:"post_abbr",Type:bigquery.StringFieldType},}loader:=client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)loader.WriteDisposition=bigquery.WriteEmptyjob,err:=loader.Run(ctx)iferr!=nil{returnerr}status,err:=job.Wait(ctx)iferr!=nil{returnerr}ifstatus.Err()!=nil{returnfmt.Errorf("job completed with error: %v",status.Err())}returnnil}

Java

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.Field;importcom.google.cloud.bigquery.FormatOptions;importcom.google.cloud.bigquery.Job;importcom.google.cloud.bigquery.JobInfo;importcom.google.cloud.bigquery.LoadJobConfiguration;importcom.google.cloud.bigquery.Schema;importcom.google.cloud.bigquery.StandardSQLTypeName;importcom.google.cloud.bigquery.TableId;// Sample to load JSON data from Cloud Storage into a new BigQuery tablepublicclassLoadJsonFromGCS{publicstaticvoidrunLoadJsonFromGCS(){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringtableName="MY_TABLE_NAME";StringsourceUri="gs://cloud-samples-data/bigquery/us-states/us-states.json";Schemaschema=Schema.of(Field.of("name",StandardSQLTypeName.STRING),Field.of("post_abbr",StandardSQLTypeName.STRING));loadJsonFromGCS(datasetName,tableName,sourceUri,schema);}publicstaticvoidloadJsonFromGCS(StringdatasetName,StringtableName,StringsourceUri,Schemaschema){try{// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();TableIdtableId=TableId.of(datasetName,tableName);LoadJobConfigurationloadConfig=LoadJobConfiguration.newBuilder(tableId,sourceUri).setFormatOptions(FormatOptions.json()).setSchema(schema).build();// Load data from a GCS JSON file into the tableJobjob=bigquery.create(JobInfo.of(loadConfig));// Blocks until this load table job completes its execution, either failing or succeeding.job=job.waitFor();if(job.isDone()){System.out.println("Json from GCS successfully loaded in a table");}else{System.out.println("BigQuery was unable to load into the table due to an error:"+job.getStatus().getError());}}catch(BigQueryException|InterruptedExceptione){System.out.println("Column not added during load append \n"+e.toString());}}}

Node.js

// Import the Google Cloud client librariesconst{BigQuery}=require('@google-cloud/bigquery');const{Storage}=require('@google-cloud/storage');// Instantiate clientsconstbigquery=newBigQuery();conststorage=newStorage();/** * This sample loads the json file at * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.json * * TODO(developer): Replace the following lines with the path to your file. */constbucketName='cloud-samples-data';constfilename='bigquery/us-states/us-states.json';asyncfunctionloadJSONFromGCS(){// Imports a GCS file into a table with manually defined schema./**   * TODO(developer): Uncomment the following lines before running the sample.   */// const datasetId = "my_dataset";// const tableId = "my_table";// Configure the load job. For full list of options, see:// https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoadconstmetadata={sourceFormat:'NEWLINE_DELIMITED_JSON',schema:{fields:[{name:'name',type:'STRING'},{name:'post_abbr',type:'STRING'},],},location:'US',};// Load data from a Google Cloud Storage file into the tableconst[job]=awaitbigquery.dataset(datasetId).table(tableId).load(storage.bucket(bucketName).file(filename),metadata);// load() waits for the job to finishconsole.log(`Job${job.id} completed.`);// Check the job's status for errorsconsterrors=job.status.errors;if(errors &&errors.length >0){throwerrors;}}

PHP

use Google\Cloud\BigQuery\BigQueryClient;use Google\Cloud\Core\ExponentialBackoff;/** Uncomment and populate these variables in your code */// $projectId  = 'The Google project ID';// $datasetId  = 'The BigQuery dataset ID';// instantiate the bigquery table service$bigQuery = new BigQueryClient([    'projectId' => $projectId,]);$dataset = $bigQuery->dataset($datasetId);$table = $dataset->table('us_states');// create the import job$gcsUri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json';$schema = [    'fields' => [        ['name' => 'name', 'type' => 'string'],        ['name' => 'post_abbr', 'type' => 'string']    ]];$loadConfig = $table->loadFromStorage($gcsUri)->schema($schema)->sourceFormat('NEWLINE_DELIMITED_JSON');$job = $table->runJob($loadConfig);// poll the job until it is complete$backoff = new ExponentialBackoff(10);$backoff->execute(function () use ($job) {    print('Waiting for job to complete' . PHP_EOL);    $job->reload();    if (!$job->isComplete()) {        throw new Exception('Job has not yet completed', 500);    }});// check if the job has errorsif (isset($job->info()['status']['errorResult'])) {    $error = $job->info()['status']['errorResult']['message'];    printf('Error running job: %s' . PHP_EOL, $error);} else {    print('Data imported successfully' . PHP_EOL);}

Python

fromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()# TODO(developer): Set table_id to the ID of the table to create.# table_id = "your-project.your_dataset.your_table_name"job_config=bigquery.LoadJobConfig(schema=[bigquery.SchemaField("name","STRING"),bigquery.SchemaField("post_abbr","STRING"),],source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,)uri="gs://cloud-samples-data/bigquery/us-states/us-states.json"load_job=client.load_table_from_uri(uri,table_id,location="US",# Must match the destination dataset location.job_config=job_config,)# Make an API request.load_job.result()# Waits for the job to complete.destination_table=client.get_table(table_id)print("Loaded{} rows.".format(destination_table.num_rows))

Ruby

require"google/cloud/bigquery"defload_table_gcs_jsondataset_id="your_dataset_id"bigquery=Google::Cloud::Bigquery.newdataset=bigquery.datasetdataset_idgcs_uri="gs://cloud-samples-data/bigquery/us-states/us-states.json"table_id="us_states"load_job=dataset.load_jobtable_id,gcs_uri,format:"json"do|schema|schema.string"name"schema.string"post_abbr"endputs"Starting job#{load_job.job_id}"load_job.wait_until_done!# Waits for table load to complete.puts"Job finished."table=dataset.tabletable_idputs"Loaded#{table.rows_count} rows to table#{table.id}"end

Console

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the left pane, clickexploreExplorer.
3. In theExplorer pane, expand your project, clickDatasets, and then select a dataset.
4. In theDataset info section, clickadd_boxCreate table.
5. In theCreate table pane, specify the following details:
1. In theSource section, selectGoogle Cloud Storage in theCreate 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, butwildcards 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.
   2. ForFile format, selectJSONL (Newline delimited JSON).
Note: It is possible to modify the table's schema when you append or overwrite it. For more information about supported schema changes during a load operation, seeModifying table schemas.
2. In theDestination section, specify the following details:
   1. ForDataset, select the dataset in which you want to create the table.
   2. In theTable field, enter the name of the table that you want to create.
   3. Verify that theTable type field is set toNative table.
3. In theSchema section, enter theschema definition. To enable theauto detection of a schema, selectAuto detect. You can enter schema information manually by using one of the following methods:
   • Option 1: ClickEdit 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 ascreating a JSON schema file. You can view the schema of an existing table in JSON format by entering the following command:

     bqshow--format=prettyjsondataset.table

   • Option 2: Clickadd_boxAdd field and enter the table schema. Specify each field'sName,Type, andMode.
   Note: It is possible to modify the table's schema when you append or overwrite it. For more information about supported schema changes during a load operation, seeModifying table schemas.
4. Optional: SpecifyPartition 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. ClickAdvanced options and do the following:
   • ForWrite preference, chooseAppend to table orOverwrite table.
   • ForNumber of errors allowed, accept the default value of0 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 aninvalid message and fail. This option applies only to CSV and JSON files.
   • ForTime 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).
   • ForDate 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).
   • ForDatetime 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).
   • ForTime 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).
   • ForTimestamp 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 selectUnknown values.
   • ForEncryption, clickCustomer-managed key to use aCloud Key Management Service key. If you leave theGoogle-managed key setting, BigQueryencrypts the data at rest.
6. ClickCreate table.

SQL

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

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the query editor, enter the following statement:

   LOADDATAINTOmydataset.mytableFROMFILES(format='JSON',uris=['gs://bucket/path/file.json']);

3. Clickplay_circleRun.

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

bq

Use thebq load command, specifyNEWLINE_DELIMITED_JSON using the--source_format flag, and include aCloud Storage URI.You can include a single URI, a comma-separated list of URIs, or a URIcontaining awildcard.

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

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

It is possible to modify the table's schema when you append oroverwrite it. For more information on supported schema changes during a loadoperation, seeModifying table schemas.

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

Other optional flags include:

• --max_bad_records: An integer that specifies the maximum number of badrecords allowed before the entire job fails. The default value is0. Atmost, five 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 data.
• --time_zone: (Preview) An optionaldefault time zone that will apply when parsing timestamp values that have nospecific time zone in CSV or JSON data.
• --date_format: (Preview) An optionalcustom string that defines how the DATE values are formatted in CSV or JSONdata.
• --datetime_format: (Preview) Anoptional custom string that defines how the DATETIME values are formatted inCSV or JSON data.
• --time_format: (Preview) An optionalcustom string that defines how the TIME values are formatted in CSV or JSONdata.
• --timestamp_format: (Preview) Anoptional custom string that defines how the TIMESTAMP values are formattedin CSV or JSON data.
• --autodetect: When specified, enable schema auto-detection for CSV andJSON data.
• --destination_kms_key: The Cloud KMS key for encryption of thetable data.

bq--location=LOCATIONload\--[no]replace\--source_format=FORMAT\DATASET.TABLE\PATH_TO_SOURCE\SCHEMA

Replace the following:

• LOCATION: yourlocation. The--location flag isoptional. You can set a default value for the location using the.bigqueryrc file.
• FORMAT:NEWLINE_DELIMITED_JSON.
• DATASET: an existing dataset.
• TABLE: the name of the table into which you'reloading data.
• PATH_TO_SOURCE: a fully qualifiedCloud Storage URIor a comma-separated list of URIs.Wildcardsare also supported.
• SCHEMA: a valid schema. The schema can be a localJSON file, or it can be typed inline as part of the command. You can alsouse the--autodetect flag instead of supplying a schema definition.

Examples:

The following command loads data fromgs://mybucket/mydata.json andoverwrites a table namedmytable inmydataset. The schema is definedusingschema auto-detection.

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

The following command loads data fromgs://mybucket/mydata.json andappends data to a table namedmytable inmydataset. The schema isdefined using a JSON schema file —myschema.

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

API

1. Create aload job that points to the source data in Cloud Storage.

2. (Optional) Specify yourlocation inthelocation property in thejobReference section of thejob resource.

3. Thesource URIs propertymust be fully-qualified, in the formatgs://BUCKET/OBJECT. You caninclude multiple URIs as a comma-separated list. Thewildcards arealso supported.

4. Specify the data format by setting theconfiguration.load.sourceFormat property toNEWLINE_DELIMITED_JSON.

5. Specify the write preference by setting theconfiguration.load.writeDisposition property toWRITE_TRUNCATE orWRITE_APPEND.

Go

import("context""fmt""cloud.google.com/go/bigquery")// importJSONTruncate demonstrates loading data from newline-delimeted JSON data in Cloud Storage// and overwriting/truncating data in the existing table.funcimportJSONTruncate(projectID,datasetID,tableIDstring)error{// projectID := "my-project-id"// datasetID := "mydataset"// tableID := "mytable"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()gcsRef:=bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states.json")gcsRef.SourceFormat=bigquery.JSONgcsRef.AutoDetect=trueloader:=client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)loader.WriteDisposition=bigquery.WriteTruncatejob,err:=loader.Run(ctx)iferr!=nil{returnerr}status,err:=job.Wait(ctx)iferr!=nil{returnerr}ifstatus.Err()!=nil{returnfmt.Errorf("job completed with error: %v",status.Err())}returnnil}

Java

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.Field;importcom.google.cloud.bigquery.FormatOptions;importcom.google.cloud.bigquery.Job;importcom.google.cloud.bigquery.JobInfo;importcom.google.cloud.bigquery.LoadJobConfiguration;importcom.google.cloud.bigquery.Schema;importcom.google.cloud.bigquery.StandardSQLTypeName;importcom.google.cloud.bigquery.TableId;// Sample to overwrite the BigQuery table data by loading a JSON file from GCSpublicclassLoadJsonFromGCSTruncate{publicstaticvoidrunLoadJsonFromGCSTruncate(){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringtableName="MY_TABLE_NAME";StringsourceUri="gs://cloud-samples-data/bigquery/us-states/us-states.json";Schemaschema=Schema.of(Field.of("name",StandardSQLTypeName.STRING),Field.of("post_abbr",StandardSQLTypeName.STRING));loadJsonFromGCSTruncate(datasetName,tableName,sourceUri,schema);}publicstaticvoidloadJsonFromGCSTruncate(StringdatasetName,StringtableName,StringsourceUri,Schemaschema){try{// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();TableIdtableId=TableId.of(datasetName,tableName);LoadJobConfigurationloadConfig=LoadJobConfiguration.newBuilder(tableId,sourceUri).setFormatOptions(FormatOptions.json())// Set the write disposition to overwrite existing table data.setWriteDisposition(JobInfo.WriteDisposition.WRITE_TRUNCATE).setSchema(schema).build();// Load data from a GCS JSON file into the tableJobjob=bigquery.create(JobInfo.of(loadConfig));// Blocks until this load table job completes its execution, either failing or succeeding.job=job.waitFor();if(job.isDone()){System.out.println("Table is successfully overwritten by JSON file loaded from GCS");}else{System.out.println("BigQuery was unable to load into the table due to an error:"+job.getStatus().getError());}}catch(BigQueryException|InterruptedExceptione){System.out.println("Column not added during load append \n"+e.toString());}}}

Node.js

// Import the Google Cloud client librariesconst{BigQuery}=require('@google-cloud/bigquery');const{Storage}=require('@google-cloud/storage');// Instantiate clientsconstbigquery=newBigQuery();conststorage=newStorage();/** * This sample loads the JSON file at * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.json * * TODO(developer): Replace the following lines with the path to your file. */constbucketName='cloud-samples-data';constfilename='bigquery/us-states/us-states.json';asyncfunctionloadJSONFromGCSTruncate(){/**   * Imports a GCS file into a table and overwrites   * table data if table already exists.   *//**   * TODO(developer): Uncomment the following lines before running the sample.   */// const datasetId = "my_dataset";// const tableId = "my_table";// Configure the load job. For full list of options, see:// https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoadconstmetadata={sourceFormat:'NEWLINE_DELIMITED_JSON',schema:{fields:[{name:'name',type:'STRING'},{name:'post_abbr',type:'STRING'},],},// Set the write disposition to overwrite existing table data.writeDisposition:'WRITE_TRUNCATE',};// Load data from a Google Cloud Storage file into the tableconst[job]=awaitbigquery.dataset(datasetId).table(tableId).load(storage.bucket(bucketName).file(filename),metadata);// load() waits for the job to finishconsole.log(`Job${job.id} completed.`);// Check the job's status for errorsconsterrors=job.status.errors;if(errors &&errors.length >0){throwerrors;}}

PHP

use Google\Cloud\BigQuery\BigQueryClient;use Google\Cloud\Core\ExponentialBackoff;/** Uncomment and populate these variables in your code */// $projectId = 'The Google project ID';// $datasetId = 'The BigQuery dataset ID';// $tableID = 'The BigQuery table ID';// instantiate the bigquery table service$bigQuery = new BigQueryClient([    'projectId' => $projectId,]);$table = $bigQuery->dataset($datasetId)->table($tableId);// create the import job$gcsUri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json';$loadConfig = $table->loadFromStorage($gcsUri)->sourceFormat('NEWLINE_DELIMITED_JSON')->writeDisposition('WRITE_TRUNCATE');$job = $table->runJob($loadConfig);// poll the job until it is complete$backoff = new ExponentialBackoff(10);$backoff->execute(function () use ($job) {    print('Waiting for job to complete' . PHP_EOL);    $job->reload();    if (!$job->isComplete()) {        throw new Exception('Job has not yet completed', 500);    }});// check if the job has errorsif (isset($job->info()['status']['errorResult'])) {    $error = $job->info()['status']['errorResult']['message'];    printf('Error running job: %s' . PHP_EOL, $error);} else {    print('Data imported successfully' . PHP_EOL);}

Python

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

importiofromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()# TODO(developer): Set table_id to the ID of the table to create.# table_id = "your-project.your_dataset.your_table_namejob_config=bigquery.LoadJobConfig(schema=[bigquery.SchemaField("name","STRING"),bigquery.SchemaField("post_abbr","STRING"),],)body=io.BytesIO(b"Washington,WA")client.load_table_from_file(body,table_id,job_config=job_config).result()previous_rows=client.get_table(table_id).num_rowsassertprevious_rows >0job_config=bigquery.LoadJobConfig(write_disposition=bigquery.WriteDisposition.WRITE_TRUNCATE,source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,)uri="gs://cloud-samples-data/bigquery/us-states/us-states.json"load_job=client.load_table_from_uri(uri,table_id,job_config=job_config)# Make an API request.load_job.result()# Waits for the job to complete.destination_table=client.get_table(table_id)print("Loaded{} rows.".format(destination_table.num_rows))

Ruby

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

require"google/cloud/bigquery"defload_table_gcs_json_truncatedataset_id="your_dataset_id",table_id="your_table_id"bigquery=Google::Cloud::Bigquery.newdataset=bigquery.datasetdataset_idgcs_uri="gs://cloud-samples-data/bigquery/us-states/us-states.json"load_job=dataset.load_jobtable_id,gcs_uri,format:"json",write:"truncate"puts"Starting job#{load_job.job_id}"load_job.wait_until_done!# Waits for table load to complete.puts"Job finished."table=dataset.tabletable_idputs"Loaded#{table.rows_count} rows to table#{table.id}"end