import("context""fmt""os""cloud.google.com/go/bigquery")// relaxTableImport demonstrates amending the schema of a table to relax columns from// not allowing NULL values to allowing them.funcrelaxTableImport(projectID,datasetID,tableID,filenamestring)error{// projectID := "my-project-id"// datasetID := "mydataset"// tableID := "mytable"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %w",err)}deferclient.Close()sampleSchema:=bigquery.Schema{{Name:"full_name",Type:bigquery.StringFieldType,Required:true},{Name:"age",Type:bigquery.IntegerFieldType,Required:true},}meta:=&bigquery.TableMetadata{Schema:sampleSchema,}tableRef:=client.Dataset(datasetID).Table(tableID)iferr:=tableRef.Create(ctx,meta);err!=nil{returnerr}// Now, import data from a local file, but specify relaxation of required// fields as a side effect while the data is appended.f,err:=os.Open(filename)iferr!=nil{returnerr}source:=bigquery.NewReaderSource(f)source.AutoDetect=true// Allow BigQuery to determine schema.source.SkipLeadingRows=1// CSV has a single header line.loader:=client.Dataset(datasetID).Table(tableID).LoaderFrom(source)loader.SchemaUpdateOptions=[]string{"ALLOW_FIELD_RELAXATION"}job,err:=loader.Run(ctx)iferr!=nil{returnerr}status,err:=job.Wait(ctx)iferr!=nil{returnerr}iferr:=status.Err();err!=nil{returnerr}returnnil}

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.Table;importcom.google.cloud.bigquery.TableId;importcom.google.common.collect.ImmutableList;// Sample to append relax column in a table.publicclassRelaxColumnLoadAppend{publicstaticvoidmain(String[]args){// 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";relaxColumnLoadAppend(datasetName,tableName,sourceUri);}publicstaticvoidrelaxColumnLoadAppend(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();// Retrieve destination table referenceTabletable=bigquery.getTable(TableId.of(datasetName,tableName));// column as a 'REQUIRED' field.Fieldname=Field.newBuilder("name",StandardSQLTypeName.STRING).setMode(Field.Mode.REQUIRED).build();FieldpostAbbr=Field.newBuilder("post_abbr",StandardSQLTypeName.STRING).setMode(Field.Mode.REQUIRED).build();Schemaschema=Schema.of(name,postAbbr);// Skip header row in the file.CsvOptionscsvOptions=CsvOptions.newBuilder().setSkipLeadingRows(1).build();// Set job optionsLoadJobConfigurationloadConfig=LoadJobConfiguration.newBuilder(table.getTableId(),sourceUri).setSchema(schema).setFormatOptions(csvOptions).setSchemaUpdateOptions(ImmutableList.of(JobInfo.SchemaUpdateOption.ALLOW_FIELD_RELAXATION)).setWriteDisposition(JobInfo.WriteDisposition.WRITE_APPEND).build();// Create a load job and wait for it to complete.Jobjob=bigquery.create(JobInfo.of(loadConfig));job=job.waitFor();// Check the job's status for errorsif(job.isDone() &&job.getStatus().getError()==null){System.out.println("Relax column append 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());}}}

// Import the Google Cloud client librariesconst{BigQuery}=require('@google-cloud/bigquery');// Instantiate clientconstbigquery=newBigQuery();asyncfunctionrelaxColumnLoadAppend(){// Changes required column to nullable in load append job./**   * TODO(developer): Uncomment the following lines before running the sample.   */// const fileName = '/path/to/file.csv';// const datasetId = 'my_dataset';// const tableId = 'my_table';// In this example, the existing table contains the 'Name'// column as a 'REQUIRED' field.constschema='Age:INTEGER, Weight:FLOAT, IsMagic:BOOLEAN';// Retrieve destination table referenceconst[table]=awaitbigquery.dataset(datasetId).table(tableId).get();constdestinationTableRef=table.metadata.tableReference;// Set load job optionsconstoptions={schema:schema,schemaUpdateOptions:['ALLOW_FIELD_RELAXATION'],writeDisposition:'WRITE_APPEND',destinationTable:destinationTableRef,};// Load data from a local file into the tableconst[job]=awaitbigquery.dataset(datasetId).table(tableId).load(fileName,options);console.log(`Job${job.id} completed.`);// Check the job's status for errorsconsterrors=job.status.errors;if(errors &&errors.length >0){throwerrors;}}

# from google.cloud import bigquery# client = bigquery.Client()# project = client.project# dataset_ref = bigquery.DatasetReference(project, 'my_dataset')# filepath = 'path/to/your_file.csv'# Retrieves the destination table and checks the number of required fieldstable_id="my_table"table_ref=dataset_ref.table(table_id)table=client.get_table(table_ref)original_required_fields=sum(field.mode=="REQUIRED"forfieldintable.schema)# In this example, the existing table has 3 required fields.print("{} fields in the schema are required.".format(original_required_fields))# Configures the load job to append the data to a destination table,# allowing field relaxationjob_config=bigquery.LoadJobConfig()job_config.write_disposition=bigquery.WriteDisposition.WRITE_APPENDjob_config.schema_update_options=[bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION]# In this example, the existing table contains three required fields# ('full_name', 'age', and 'favorite_color'), while the data to load# contains only the first two fields.job_config.schema=[bigquery.SchemaField("full_name","STRING",mode="REQUIRED"),bigquery.SchemaField("age","INTEGER",mode="REQUIRED"),]job_config.source_format=bigquery.SourceFormat.CSVjob_config.skip_leading_rows=1withopen(filepath,"rb")assource_file:job=client.load_table_from_file(source_file,table_ref,location="US",# Must match the destination dataset location.job_config=job_config,)# API requestjob.result()# Waits for table load to complete.print("Loaded{} rows into{}:{}.".format(job.output_rows,dataset_id,table_ref.table_id))# Checks the updated number of required fieldstable=client.get_table(table)current_required_fields=sum(field.mode=="REQUIRED"forfieldintable.schema)print("{} fields in the schema are now required.".format(current_required_fields))