Console

1. In the Google Cloud console, open the BigQuery page.

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

   If you don't see the left pane, clicklast_pageExpand left pane to open the pane.

3. In theExplorer pane, expand the project, clickDatasets, andthen click the name of your dataset.

4. In the details pane, clickCreate tableadd_box.

5. On theCreate table page, in theSource section:

   • ForCreate table from, selectGoogle Cloud Storage.

   • In the source field, browse to or enter theCloud Storage URI.Note that you cannot include multiple URIs in theGoogle Cloud console, butwildcards aresupported. The Cloud Storage bucket must be in the same locationas the dataset that contains the table you're creating.

     

   • ForFile format, selectAvro.

6. On theCreate table page, in theDestination section:

   • ForDataset name, choose the appropriate dataset.
   • Verify thatTable type is set toNative table.
   • In theTable name field, enter the name of the table you'recreating in BigQuery.

7. In theSchema section, no action is necessary. The schema isself-described in Avro files.

8. (Optional) To partition the table, choose your options in thePartition and cluster settings. For more information, seeCreating partitioned tables.

9. (Optional) ForPartitioning filter, click theRequire partitionfilter box to require users to include aWHERE clause that specifies thepartitions to query. Requiring a partition filter may reduce cost andimprove performance. For more information, seeRequire a partition filter in queries.This option is unavailable ifNo partitioning is selected.

10. (Optional) Tocluster thetable, in theClustering order box, enter between one and four fieldnames.

11. (Optional) ClickAdvanced options.

    • ForWrite preference, leaveWrite if empty selected. Thisoption creates a new table and loads your data into it.
    • ForUnknown values, leaveIgnore unknown values cleared.This option applies only to CSV and JSON files.
    • ForEncryption, clickCustomer-managed key to use aCloud Key Management Service key.If you leave theGoogle-managed key setting,BigQueryencrypts the data at rest.

12. ClickCreate table.

SQL

Use theLOAD DATA DDL statement.The following example loads an Avro 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.mytableFROMFILES(format='avro',uris=['gs://bucket/path/file.avro']);

3. Clickplay_circleRun.

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

bq

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

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

Other optional flags include:

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

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

Replace the following:

• 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 isAVRO.
• 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.

Examples:

The following command loads data fromgs://mybucket/mydata.avro into atable namedmytable inmydataset.

bqload\--source_format=AVRO\mydataset.mytable\gs://mybucket/mydata.avro

The following command loads data fromgs://mybucket/mydata.avro into aningestion-time partitioned table namedmytable inmydataset.

bqload\--source_format=AVRO\--time_partitioning_type=DAY\mydataset.mytable\gs://mybucket/mydata.avro

The following command loads data fromgs://mybucket/mydata.avro into a newpartitioned table namedmytable inmydataset. The table is partitionedon themytimestamp column.

bqload\--source_format=AVRO\--time_partitioning_fieldmytimestamp\mydataset.mytable\gs://mybucket/mydata.avro

The following command loads data from multiple files ings://mybucket/into a table namedmytable inmydataset. The Cloud Storage URI uses awildcard.

bqload\--source_format=AVRO\mydataset.mytable\gs://mybucket/mydata*.avro

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.

bqload\--source_format=AVRO\mydataset.mytable\"gs://mybucket/00/*.avro","gs://mybucket/01/*.avro"

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 Avro data format by setting thesourceFormat property toAVRO.

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 non-fatal errors, such as problemsimporting a few rows. Non-fatal 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.

Go

import("context""fmt""cloud.google.com/go/bigquery")// importAvro demonstrates loading Apache Avro data from Cloud Storage into a table.funcimportAvro(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.avro")gcsRef.SourceFormat=bigquery.Avroloader:=client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)job,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.LoadJobConfiguration;importcom.google.cloud.bigquery.TableId;// Sample to load Avro data from Cloud Storage into a new BigQuery tablepublicclassLoadAvroFromGCS{publicstaticvoidrunLoadAvroFromGCS(){// 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.avro";loadAvroFromGCS(datasetName,tableName,sourceUri);}publicstaticvoidloadAvroFromGCS(StringdatasetName,StringtableName,StringsourceUri){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.of(tableId,sourceUri,FormatOptions.avro());// Load data from a GCS Avro 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("Avro 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 Avro file at * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.avro * * TODO(developer): Replace the following lines with the path to your file. */constbucketName='cloud-samples-data';constfilename='bigquery/us-states/us-states.avro';asyncfunctionloadTableGCSAvro(){// Imports a GCS file into a table with Avro source format./**   * TODO(developer): Uncomment the following lines before running the sample.   */// const datasetId = 'my_dataset';// const tableId = 'us_states';// Configure the load job. For full list of options, see:// https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoadconstjobConfigurationLoad={load:{sourceFormat:'AVRO'},};// Load data from a Google Cloud Storage file into the tableconst[job]=awaitbigquery.dataset(datasetId).table(tableId).load(storage.bucket(bucketName).file(filename),jobConfigurationLoad);// 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;}}

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_namejob_config=bigquery.LoadJobConfig(source_format=bigquery.SourceFormat.AVRO)uri="gs://cloud-samples-data/bigquery/us-states/us-states.avro"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))

Console

1. In the Google Cloud console, open the BigQuery page.

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

3. In theExplorer pane, expand the project, clickDatasets, andthen click the name of your dataset.

4. In the details pane, clickCreate tableadd_box.

5. On theCreate table page, in theSource section:

   • ForCreate table from, select Cloud Storage.

   • In the source field, browse to orenter theCloud Storage URI. Note that you cannotinclude multiple URIs in the Google Cloud console, butwildcardsare supported. The Cloud Storage bucket must be in the same locationas the dataset that contains the table you're appending or overwriting.

     

   • ForFile format, selectAvro.

6. On theCreate table page, in theDestination section:

   • ForDataset name, choose the appropriate dataset.

     

   • In theTable name field, enter the name of the table you'reappending or overwriting in BigQuery.

   • Verify thatTable type is set toNative table.

7. In theSchema section, no action is necessary. The schema isself-described in Avro files.

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

8. ForPartition and cluster settings, leave the default values. Youcannot convert a table to a partitioned or clustered table by appending oroverwriting it, and the Google Cloud console does not supportappending to or overwriting partitioned or clustered tables in a load job.

9. ClickAdvanced options.

   • ForWrite preference, chooseAppend to table orOverwritetable.
   • ForUnknown values, leaveIgnore unknown values cleared.This option applies only to CSV and JSON files.
   • ForEncryption, clickCustomer-managed key to use aCloud Key Management Service key.If you leave theGoogle-owned and managed key setting,BigQueryencrypts the data at rest.

10. ClickCreate table.

SQL

Use theLOAD DATA DDL statement.The following example appends an Avro 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='avro',uris=['gs://bucket/path/file.avro']);

3. Clickplay_circleRun.

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

bq

Enter thebq load command with 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. Supply the--source_format flagand set it toAVRO. Because Avro schemas are automatically retrievedfrom the self-describing source data, you do not need to provide a schemadefinition.

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

Other optional flags include:

• --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

Replace the following:

• location is yourlocation.The--location flag is optional. You can set a default value for thelocation by using the.bigqueryrc file.
• format isAVRO.
• 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.

Examples:

The following command loads data fromgs://mybucket/mydata.avro andoverwrites a table namedmytable inmydataset.

bqload\--replace\--source_format=AVRO\mydataset.mytable\gs://mybucket/mydata.avro

The following command loads data fromgs://mybucket/mydata.avro andappends data to a table namedmytable inmydataset.

bqload\--noreplace\--source_format=AVRO\mydataset.mytable\gs://mybucket/mydata.avro

For information on appending and overwriting partitioned tables using thebq command-line tool, see:Appending to and overwriting partitioned table data.

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

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

Go

import("context""fmt""cloud.google.com/go/bigquery")// importAvroTruncate demonstrates loading Apache Avro data from Cloud Storage into a table// and overwriting/truncating existing data in the table.funcimportAvroTruncate(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.avro")gcsRef.SourceFormat=bigquery.Avroloader:=client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)// Default for import jobs is to append data to a table.  WriteTruncate// specifies that existing data should instead be replaced/overwritten.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.LoadJobConfiguration;importcom.google.cloud.bigquery.TableId;// Sample to overwrite the BigQuery table data by loading a AVRO file from GCSpublicclassLoadAvroFromGCSTruncate{publicstaticvoidrunLoadAvroFromGCSTruncate(){// 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.avro";loadAvroFromGCSTruncate(datasetName,tableName,sourceUri);}publicstaticvoidloadAvroFromGCSTruncate(StringdatasetName,StringtableName,StringsourceUri){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.avro())// Set the write disposition to overwrite existing table data.setWriteDisposition(JobInfo.WriteDisposition.WRITE_TRUNCATE).build();// Load data from a GCS Avro 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 AVRO 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 Avro file at * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.avro * * TODO(developer): Replace the following lines with the path to your file. */constbucketName='cloud-samples-data';constfilename='bigquery/us-states/us-states.avro';asyncfunctionloadTableGCSAvroTruncate(){/**   * 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 = 'us_states';// Configure the load job. For full list of options, see:// https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoadconstjobConfigurationLoad={load:{sourceFormat:'AVRO',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),jobConfigurationLoad);// 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;}}

Python

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.AVRO,)uri="gs://cloud-samples-data/bigquery/us-states/us-states.avro"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))