Console

To follow step-by-step guidance for this task directly in the Cloud Shell Editor, clickGuide me:

Guide me

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, selectCSV.
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.
   • ForField delimiter, choose the character that separates the cells in your CSV file:Comma,Tab,Pipe, orCustom. If you chooseCustom, enter the delimiter in theCustom field delimiter box. The default value isComma.
   • ForSource column match, choose one of the following strategies used to match the loaded columns to the schema (Preview).
   • Default: Default behavior is chosen based on how the schema is provided. If autodetect is enabled, then the default behavior is to match columns by name. Otherwise, the default is to match columns by position. This is done to keep the behavior backward-compatible.
   • Position: Matches columns by position, assuming that the columns are ordered the same way as the schema.
   • Name: Matches by name by reading the header row as the column names and reordering columns to match the field names in the schema. Column names are read from the last skipped row based onHeader rows to skip.
   • ForHeader rows to skip, enter the number of header rows to skip at the top of the CSV file. The default value is0.
   • ForQuoted newlines, checkAllow quoted newlines to allow quoted data sections that contain newline characters in a CSV file. The default value isfalse.
   • ForJagged rows, checkAllow jagged rows to accept rows in CSV files that are missing trailing optional columns. The missing values are treated as nulls. If unchecked, records with missing trailing 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 isfalse.
   • ForNull markers, enter a list of custom strings that represents a NULL value in CSV data. (Preview).
   • 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 CSV 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='CSV',uris=['gs://bucket/path/file.csv']);

3. Clickplay_circleRun.

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

bq

Use thebq load command, specifyCSV 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. If you don't specify aschema, and--autodetect isfalse, and the destinationtable exists, then the schema of the destination table is used.

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

Other optional flags include:

• --allow_jagged_rows: When specified, accept rows in CSV files that aremissing trailing optional columns. The missing values are treated as nulls.If unchecked, records with missing trailing columns are treated as badrecords, and if there are too many bad records, an invalid error is returnedin the job result. The default value isfalse.
• --allow_quoted_newlines: When specified, allows quoted data sectionsthat contain newline characters in a CSV file. The default value isfalse.
• --field_delimiter: The character that indicates the boundary betweencolumns in the data. Both\t andtab are allowed for tab delimiters.The default value is,.
• --null_marker: An optional custom string that represents a NULL value inCSV data.
• --null_markers: (Preview) An optionalcomma-separated list of custom strings that represent NULL values in CSVdata. This option cannot be used with--null_marker flag.
• --source_column_match: (Preview)Specifies the strategy used to match loaded columns to the schema. You canspecifyPOSITION to match loaded columns by position, assuming that thecolumns are ordered the same way as the schema. You can also specifyNAMEto match by name by reading the header row as the column names andreordering columns to match the field names in the schema. If this value isunspecified, then the default is based on how the schema is provided. If--autodetect is enabled, then the default behavior is to match columns byname. Otherwise, the default is to match columns by position.
• --skip_leading_rows: Specifies the number of header rows to skipat the top of the CSV file. The default value is0.
• --quote: The quote character to use to enclose records. The defaultvalue is". To indicate no quote character, use an empty string.
• --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 may reduce cost and improve performance.For more information, seeQuerying partitioned tables.
• --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.

• --column_name_character_map: Defines the scope and handling ofcharacters in column names, with the option of enablingflexible column names.Requires the--autodetect option for CSV files.For more information, seeload_option_list.

  For more information on thebq load command, see:

  • Command-line reference

  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 CSV data into BigQuery, enter the following command:

bq--location=locationload\--source_format=format\dataset.table\path_to_source\schema

Where:

• location is your location. The--location flag is optional.For example, if you are using BigQuery in the Tokyo region,you can set the flag's value toasia-northeast1. You can set a defaultvalue for the location using the.bigqueryrc file.
• format isCSV.
• dataset is an existing dataset.
• table is the name of the table into which you're loading data.
• path_to_source is a fully-qualifiedCloud Storage URIor a comma-separated list of URIs.Wildcardsare also supported.
• schema is a valid schema. The schema can be a local JSON file,or it can be typed inline as part of the command. You can also use the--autodetect flag instead of supplying a schema definition.

Examples:

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

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

The following command loads data fromgs://mybucket/mydata.csv into atable namedmytable inmydataset. The schema is defined in a localschema file namedmyschema.json. The CSV file includes two header rows.If--skip_leading_rows is unspecified, the default behavior is to assumethe file does not contain headers.

bqload\--source_format=CSV\--skip_leading_rows=2mydataset.mytable\gs://mybucket/mydata.csv\./myschema.json

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

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

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

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

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

bqload\--autodetect\--source_format=CSV\mydataset.mytable\gs://mybucket/mydata.csv

The following command loads data fromgs://mybucket/mydata.csv into atable namedmytable inmydataset. The schema is defined inline in theformatfield:data_type,field:data_type.

bqload\--source_format=CSV\mydataset.mytable\gs://mybucket/mydata.csv\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=CSV\mydataset.mytable\gs://mybucket/mydata*.csv

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.json.

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

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 the CSV data format by setting thesourceFormat property toCSV.

5. To check the job status, calljobs.get(job_id*),wherejob_id is the ID of the job returned by the initialrequest.

   • Ifstatus.state = DONE, the job completed successfully.
   • If thestatus.errorResult property is present, the request failed,and that object will include 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 will succeed.

C#

usingGoogle.Cloud.BigQuery.V2;usingSystem;publicclassBigQueryLoadTableGcsCsv{publicvoidLoadTableGcsCsv(stringprojectId="your-project-id",stringdatasetId="your_dataset_id"){BigQueryClientclient=BigQueryClient.Create(projectId);vargcsURI="gs://cloud-samples-data/bigquery/us-states/us-states.csv";vardataset=client.GetDataset(datasetId);varschema=newTableSchemaBuilder{{"name",BigQueryDbType.String},{"post_abbr",BigQueryDbType.String}}.Build();vardestinationTableRef=dataset.GetTableReference(tableId:"us_states");// Create job configurationvarjobOptions=newCreateLoadJobOptions(){// The source format defaults to CSV; line below is optional.SourceFormat=FileFormat.Csv,SkipLeadingRows=1};// Create and run jobvarloadJob=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")// importCSVExplicitSchema demonstrates loading CSV data from Cloud Storage into a BigQuery// table and providing an explicit schema for the data.funcimportCSVExplicitSchema(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.csv")gcsRef.SkipLeadingRows=1gcsRef.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.CsvOptions;importcom.google.cloud.bigquery.Field;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 CSV data from Cloud Storage into a new BigQuery tablepublicclassLoadCsvFromGcs{publicstaticvoidrunLoadCsvFromGcs()throwsException{// 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.csv";Schemaschema=Schema.of(Field.of("name",StandardSQLTypeName.STRING),Field.of("post_abbr",StandardSQLTypeName.STRING));loadCsvFromGcs(datasetName,tableName,sourceUri,schema);}publicstaticvoidloadCsvFromGcs(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();// Skip header row in the file.CsvOptionscsvOptions=CsvOptions.newBuilder().setSkipLeadingRows(1).build();TableIdtableId=TableId.of(datasetName,tableName);LoadJobConfigurationloadConfig=LoadJobConfiguration.newBuilder(tableId,sourceUri,csvOptions).setSchema(schema).build();// Load data from a GCS CSV 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("CSV from GCS successfully added during load append job");}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 CSV file at * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.csv * * TODO(developer): Replace the following lines with the path to your file. */constbucketName='cloud-samples-data';constfilename='bigquery/us-states/us-states.csv';asyncfunctionloadCSVFromGCS(){// 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:'CSV',skipLeadingRows:1,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.csv';$schema = [    'fields' => [        ['name' => 'name', 'type' => 'string'],        ['name' => 'post_abbr', 'type' => 'string']    ]];$loadConfig = $table->loadFromStorage($gcsUri)->schema($schema)->skipLeadingRows(1);$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"),],skip_leading_rows=1,# The source format defaults to CSV, so the line below is optional.source_format=bigquery.SourceFormat.CSV,)uri="gs://cloud-samples-data/bigquery/us-states/us-states.csv"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)# Make an API request.print("Loaded{} rows.".format(destination_table.num_rows))

Ruby

require"google/cloud/bigquery"defload_table_gcs_csvdataset_id="your_dataset_id"bigquery=Google::Cloud::Bigquery.newdataset=bigquery.datasetdataset_idgcs_uri="gs://cloud-samples-data/bigquery/us-states/us-states.csv"table_id="us_states"load_job=dataset.load_jobtable_id,gcs_uri,skip_leading:1do|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

Go

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

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

import("context""fmt""time""cloud.google.com/go/bigquery")// importPartitionedTable demonstrates specifing time partitioning for a BigQuery table when loading// CSV data from Cloud Storage.funcimportPartitionedTable(projectID,destDatasetID,destTableIDstring)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-by-date.csv")gcsRef.SkipLeadingRows=1gcsRef.Schema=bigquery.Schema{{Name:"name",Type:bigquery.StringFieldType},{Name:"post_abbr",Type:bigquery.StringFieldType},{Name:"date",Type:bigquery.DateFieldType},}loader:=client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)loader.TimePartitioning=&bigquery.TimePartitioning{Field:"date",Expiration:90*24*time.Hour,}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

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

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

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.JobId;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;importcom.google.cloud.bigquery.TimePartitioning;importjava.time.Duration;importjava.time.temporal.ChronoUnit;importjava.util.UUID;publicclassLoadPartitionedTable{publicstaticvoidrunLoadPartitionedTable()throwsException{// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringtableName="MY_TABLE_NAME";StringsourceUri="/path/to/file.csv";loadPartitionedTable(datasetName,tableName,sourceUri);}publicstaticvoidloadPartitionedTable(StringdatasetName,StringtableName,StringsourceUri)throwsException{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);Schemaschema=Schema.of(Field.of("name",StandardSQLTypeName.STRING),Field.of("post_abbr",StandardSQLTypeName.STRING),Field.of("date",StandardSQLTypeName.DATE));// Configure time partitioning. For full list of options, see:// https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#TimePartitioningTimePartitioningpartitioning=TimePartitioning.newBuilder(TimePartitioning.Type.DAY).setField("date").setExpirationMs(Duration.of(90,ChronoUnit.DAYS).toMillis()).build();LoadJobConfigurationloadJobConfig=LoadJobConfiguration.builder(tableId,sourceUri).setFormatOptions(FormatOptions.csv()).setSchema(schema).setTimePartitioning(partitioning).build();// Create a job ID so that we can safely retry.JobIdjobId=JobId.of(UUID.randomUUID().toString());JobloadJob=bigquery.create(JobInfo.newBuilder(loadJobConfig).setJobId(jobId).build());// Load data from a GCS parquet file into the table// Blocks until this load table job completes its execution, either failing or succeeding.JobcompletedJob=loadJob.waitFor();// Check for errorsif(completedJob==null){thrownewException("Job not executed since it no longer exists.");}elseif(completedJob.getStatus().getError()!=null){// You can also look at queryJob.getStatus().getExecutionErrors() for all// errors, not just the latest one.thrownewException("BigQuery was unable to load into the table due to an error: \n"+loadJob.getStatus().getError());}System.out.println("Data successfully loaded into time partitioned table during load job");}catch(BigQueryException|InterruptedExceptione){System.out.println("Data not loaded into time partitioned table during load job \n"+e.toString());}}}

Node.js

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

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

// 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 CSV file at * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.csv * * TODO(developer): Replace the following lines with the path to your file. */constbucketName='cloud-samples-data';constfilename='bigquery/us-states/us-states-by-date.csv';asyncfunctionloadTablePartitioned(){// Load data into a table that uses column-based time partitioning./**   * TODO(developer): Uncomment the following lines before running the sample.   */// const datasetId = 'my_dataset';// const tableId = 'my_new_table';// Configure the load job. For full list of options, see:// https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoadconstpartitionConfig={type:'DAY',expirationMs:'7776000000',// 90 daysfield:'date',};constmetadata={sourceFormat:'CSV',skipLeadingRows:1,schema:{fields:[{name:'name',type:'STRING'},{name:'post_abbr',type:'STRING'},{name:'date',type:'DATE'},],},location:'US',timePartitioning:partitionConfig,};// 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.`);}

Python

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

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

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"),bigquery.SchemaField("date","DATE"),],skip_leading_rows=1,time_partitioning=bigquery.TimePartitioning(type_=bigquery.TimePartitioningType.DAY,field="date",# Name of the column to use for partitioning.expiration_ms=7776000000,# 90 days.),)uri="gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv"load_job=client.load_table_from_uri(uri,table_id,job_config=job_config)# Make an API request.load_job.result()# Wait for the job to complete.table=client.get_table(table_id)print("Loaded{} rows to table{}".format(table.num_rows,table_id))

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, selectCSV.
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.
   • ForField delimiter, choose the character that separates the cells in your CSV file:Comma,Tab,Pipe, orCustom. If you chooseCustom, enter the delimiter in theCustom field delimiter box. The default value isComma.
   • ForSource column match, choose one of the following strategies used to match the loaded columns to the schema (Preview).
   • Default: Default behavior is chosen based on how the schema is provided. If autodetect is enabled, then the default behavior is to match columns by name. Otherwise, the default is to match columns by position. This is done to keep the behavior backward-compatible.
   • Position: Matches columns by position, assuming that the columns are ordered the same way as the schema.
   • Name: Matches by name by reading the header row as the column names and reordering columns to match the field names in the schema. Column names are read from the last skipped row based onHeader rows to skip.
   • ForHeader rows to skip, enter the number of header rows to skip at the top of the CSV file. The default value is0.
   • ForQuoted newlines, checkAllow quoted newlines to allow quoted data sections that contain newline characters in a CSV file. The default value isfalse.
   • ForJagged rows, checkAllow jagged rows to accept rows in CSV files that are missing trailing optional columns. The missing values are treated as nulls. If unchecked, records with missing trailing 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 isfalse.
   • ForNull markers, enter a list of custom strings that represents a NULL value in CSV data. (Preview).
   • 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 CSV 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='CSV',uris=['gs://bucket/path/file.csv']);

3. Clickplay_circleRun.

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

bq

Use thebq load command, specifyCSV 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. If you don't specify aschema, and--autodetect isfalse, and the destinationtable exists, then the schema of the destination table is used.

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:

• --allow_jagged_rows: When specified, accept rows in CSV files that aremissing trailing optional columns. The missing values are treated as nulls.If unchecked, records with missing trailing columns are treated as badrecords, and if there are too many bad records, an invalid error is returnedin the job result. The default value isfalse.
• --allow_quoted_newlines: When specified, allows quoted data sectionsthat contain newline characters in a CSV file. The default value isfalse.
• --field_delimiter: The character that indicates the boundary betweencolumns in the data. Both\t andtab are allowed for tab delimiters.The default value is,.
• --null_marker: An optional custom string that represents a NULL value inCSV data.
• --null_markers: (Preview) An optionalcomma-separated list of custom strings that represent NULL values in CSVdata. This option cannot be used with--null_marker flag.
• --source_column_match: (Preview)Specifies the strategy used to match loaded columns to the schema. You canspecifyPOSITION to match loaded columns by position, assuming that thecolumns are ordered the same way as the schema. You can also specifyNAMEto match by name by reading the header row as the column names andreordering columns to match the field names in the schema. If this value isunspecified, then the default is based on how the schema is provided. If--autodetect is enabled, then the default behavior is to match columns byname. Otherwise, the default is to match columns by position.
• --skip_leading_rows: Specifies the number of header rows to skipat the top of the CSV file. The default value is0.
• --quote: The quote character to use to enclose records. The defaultvalue is". To indicate no quote character, use an empty string.
• --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

where:

• location is yourlocation.The--location flag is optional. You can set a default value for thelocation using the.bigqueryrc file.
• format isCSV.
• dataset is an existing dataset.
• table is the name of the table into which you're loading data.
• path_to_source is a fully-qualifiedCloud Storage URIor a comma-separated list of URIs.Wildcardsare also supported.
• schema is a valid schema. The schema can be a local JSON file,or it can be typed inline as part of the command. You can also use the--autodetect flag instead of supplying a schema definition.

Examples:

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

bqload\--autodetect\--replace\--source_format=CSV\mydataset.mytable\gs://mybucket/mydata.csv

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

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

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. Note thatwildcards arealso supported.

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

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

Go

import("context""fmt""cloud.google.com/go/bigquery")// importCSVTruncate demonstrates loading data from CSV data in Cloud Storage and overwriting/truncating// data in the existing table.funcimportCSVTruncate(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.csv")gcsRef.SourceFormat=bigquery.CSVgcsRef.AutoDetect=truegcsRef.SkipLeadingRows=1loader:=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.FormatOptions;importcom.google.cloud.bigquery.Job;importcom.google.cloud.bigquery.JobInfo;importcom.google.cloud.bigquery.JobInfo.WriteDisposition;importcom.google.cloud.bigquery.LoadJobConfiguration;importcom.google.cloud.bigquery.TableId;// Sample to overwrite the BigQuery table data by loading a CSV file from GCSpublicclassLoadCsvFromGcsTruncate{publicstaticvoidrunLoadCsvFromGcsTruncate()throwsException{// 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.csv";loadCsvFromGcsTruncate(datasetName,tableName,sourceUri);}publicstaticvoidloadCsvFromGcsTruncate(StringdatasetName,StringtableName,StringsourceUri)throwsException{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);LoadJobConfigurationconfiguration=LoadJobConfiguration.builder(tableId,sourceUri).setFormatOptions(FormatOptions.csv())// Set the write disposition to overwrite existing table data.setWriteDisposition(WriteDisposition.WRITE_TRUNCATE).build();// For more information on Job see:// https://googleapis.dev/java/google-cloud-clients/latest/index.html?com/google/cloud/bigquery/package-summary.html// Load the tableJobloadJob=bigquery.create(JobInfo.of(configuration));// Load data from a GCS parquet file into the table// Blocks until this load table job completes its execution, either failing or succeeding.JobcompletedJob=loadJob.waitFor();// Check for errorsif(completedJob==null){thrownewException("Job not executed since it no longer exists.");}elseif(completedJob.getStatus().getError()!=null){// You can also look at queryJob.getStatus().getExecutionErrors() for all// errors, not just the latest one.thrownewException("BigQuery was unable to load into the table due to an error: \n"+loadJob.getStatus().getError());}System.out.println("Table is successfully overwritten by CSV file loaded from GCS");}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 CSV file at * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.csv * * TODO(developer): Replace the following lines with the path to your file. */constbucketName='cloud-samples-data';constfilename='bigquery/us-states/us-states.csv';asyncfunctionloadCSVFromGCSTruncate(){/**   * 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:'CSV',skipLeadingRows:1,schema:{fields:[{name:'name',type:'STRING'},{name:'post_abbr',type:'STRING'},],},// Set the write disposition to overwrite existing table data.writeDisposition:'WRITE_TRUNCATE',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;}}

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.csv';$loadConfig = $table->loadFromStorage($gcsUri)->skipLeadingRows(1)->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

importsixfromgoogle.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=six.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.CSV,skip_leading_rows=1,)uri="gs://cloud-samples-data/bigquery/us-states/us-states.csv"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))