Relax a column

  import 
  
 ( 
  
 "context" 
  
 "fmt" 
  
 "cloud.google.com/go/bigquery" 
 ) 
 // relaxTableAPI demonstrates modifying the schema of a table to remove the requirement that columns allow 
 // no NULL values. 
 func 
  
 relaxTableAPI 
 ( 
 projectID 
 , 
  
 datasetID 
 , 
  
 tableID 
  
 string 
 ) 
  
 error 
  
 { 
  
 // projectID := "my-project-id" 
  
 // datasetID := "mydatasetid" 
  
 // tableID := "mytableid" 
  
 ctx 
  
 := 
  
 context 
 . 
 Background 
 () 
  
 client 
 , 
  
 err 
  
 := 
  
 bigquery 
 . 
 NewClient 
 ( 
 ctx 
 , 
  
 projectID 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 fmt 
 . 
 Errorf 
 ( 
 "bigquery.NewClient: %w" 
 , 
  
 err 
 ) 
  
 } 
  
 defer 
  
 client 
 . 
 Close 
 () 
  
 // Setup: We first create a table with a schema that's restricts NULL values. 
  
 sampleSchema 
  
 := 
  
 bigquery 
 . 
  Schema 
 
 { 
  
 { 
 Name 
 : 
  
 "full_name" 
 , 
  
 Type 
 : 
  
 bigquery 
 . 
  StringFieldType 
 
 , 
  
 Required 
 : 
  
 true 
 }, 
  
 { 
 Name 
 : 
  
 "age" 
 , 
  
 Type 
 : 
  
 bigquery 
 . 
  IntegerFieldType 
 
 , 
  
 Required 
 : 
  
 true 
 }, 
  
 } 
  
 original 
  
 := 
  
& bigquery 
 . 
  TableMetadata 
 
 { 
  
 Schema 
 : 
  
 sampleSchema 
 , 
  
 } 
  
 if 
  
 err 
  
 := 
  
 client 
 . 
 Dataset 
 ( 
 datasetID 
 ). 
 Table 
 ( 
 tableID 
 ). 
 Create 
 ( 
 ctx 
 , 
  
 original 
 ); 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 tableRef 
  
 := 
  
 client 
 . 
 Dataset 
 ( 
 datasetID 
 ). 
 Table 
 ( 
 tableID 
 ) 
  
 meta 
 , 
  
 err 
  
 := 
  
 tableRef 
 . 
 Metadata 
 ( 
 ctx 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 // Iterate through the schema to set all Required fields to false (nullable). 
  
 var 
  
 relaxed 
  
 bigquery 
 . 
  Schema 
 
  
 for 
  
 _ 
 , 
  
 v 
  
 := 
  
 range 
  
 meta 
 . 
  Schema 
 
  
 { 
  
 v 
 . 
 Required 
  
 = 
  
 false 
  
 relaxed 
  
 = 
  
 append 
 ( 
 relaxed 
 , 
  
 v 
 ) 
  
 } 
  
 newMeta 
  
 := 
  
 bigquery 
 . 
  TableMetadataToUpdate 
 
 { 
  
 Schema 
 : 
  
 relaxed 
 , 
  
 } 
  
 if 
  
 _ 
 , 
  
 err 
  
 := 
  
 tableRef 
 . 
 Update 
 ( 
 ctx 
 , 
  
 newMeta 
 , 
  
 meta 
 . 
 ETag 
 ); 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 return 
  
 nil 
 } 
 

  import 
  
 com.google.cloud.bigquery. BigQuery 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryException 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryOptions 
 
 ; 
 import 
  
 com.google.cloud.bigquery. Field 
 
 ; 
 import 
  
 com.google.cloud.bigquery. LegacySQLTypeName 
 
 ; 
 import 
  
 com.google.cloud.bigquery. Schema 
 
 ; 
 import 
  
 com.google.cloud.bigquery. StandardTableDefinition 
 
 ; 
 import 
  
 com.google.cloud.bigquery. Table 
 
 ; 
 public 
  
 class 
 RelaxColumnMode 
  
 { 
  
 public 
  
 static 
  
 void 
  
 main 
 ( 
 String 
 [] 
  
 args 
 ) 
  
 { 
  
 // TODO(developer): Replace these variables before running the sample. 
  
 String 
  
 datasetName 
  
 = 
  
 "MY_DATASET_NAME" 
 ; 
  
 String 
  
 tableId 
  
 = 
  
 "MY_TABLE_NAME" 
 ; 
  
 relaxColumnMode 
 ( 
 datasetName 
 , 
  
 tableId 
 ); 
  
 } 
  
 public 
  
 static 
  
 void 
  
 relaxColumnMode 
 ( 
 String 
  
 datasetName 
 , 
  
 String 
  
 tableId 
 ) 
  
 { 
  
 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. 
  
  BigQuery 
 
  
 bigquery 
  
 = 
  
  BigQueryOptions 
 
 . 
 getDefaultInstance 
 (). 
 getService 
 (); 
  
  Table 
 
  
 table 
  
 = 
  
 bigquery 
 . 
  getTable 
 
 ( 
 datasetName 
 , 
  
 tableId 
 ); 
  
 // Create new relaxed schema based on the existing table schema 
  
  Schema 
 
  
 relaxedSchema 
  
 = 
  
  Schema 
 
 . 
 of 
 ( 
  
 // The only supported modification you can make to a column's mode is changing it from 
  
 // REQUIRED to NULLABLE 
  
 // Changing a column's mode from REQUIRED to NULLABLE is also called column relaxation 
  
 // INFO: LegacySQLTypeName will be updated to StandardSQLTypeName in release 1.103.0 
  
  Field 
 
 . 
 newBuilder 
 ( 
 "word" 
 , 
  
  LegacySQLTypeName 
 
 . 
 STRING 
 ) 
  
 . 
 setMode 
 ( 
  Field 
 
 . 
 Mode 
 . 
 NULLABLE 
 ) 
  
 . 
 build 
 (), 
  
  Field 
 
 . 
 newBuilder 
 ( 
 "word_count" 
 , 
  
  LegacySQLTypeName 
 
 . 
 STRING 
 ) 
  
 . 
 setMode 
 ( 
  Field 
 
 . 
 Mode 
 . 
 NULLABLE 
 ) 
  
 . 
 build 
 (), 
  
  Field 
 
 . 
 newBuilder 
 ( 
 "corpus" 
 , 
  
  LegacySQLTypeName 
 
 . 
 STRING 
 ) 
  
 . 
 setMode 
 ( 
  Field 
 
 . 
 Mode 
 . 
 NULLABLE 
 ) 
  
 . 
 build 
 (), 
  
  Field 
 
 . 
 newBuilder 
 ( 
 "corpus_date" 
 , 
  
  LegacySQLTypeName 
 
 . 
 STRING 
 ) 
  
 . 
 setMode 
 ( 
  Field 
 
 . 
 Mode 
 . 
 NULLABLE 
 ) 
  
 . 
 build 
 ()); 
  
 // Update the table with the new schema 
  
  Table 
 
  
 updatedTable 
  
 = 
  
 table 
 . 
  toBuilder 
 
 (). 
 setDefinition 
 ( 
  StandardTableDefinition 
 
 . 
 of 
 ( 
 relaxedSchema 
 )). 
 build 
 (); 
  
  update 
 
dTable . 
  update 
 
 (); 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Table schema successfully relaxed." 
 ); 
  
 } 
  
 catch 
  
 ( 
  BigQueryException 
 
  
 e 
 ) 
  
 { 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Table schema not relaxed \n" 
  
 + 
  
 e 
 . 
 toString 
 ()); 
  
 } 
  
 } 
 } 
 

  // Import the Google Cloud client library and create a client 
 const 
  
 { 
 BigQuery 
 } 
  
 = 
  
 require 
 ( 
 ' @google-cloud/bigquery 
' 
 ); 
 const 
  
 bigquery 
  
 = 
  
 new 
  
  BigQuery 
 
 (); 
 async 
  
 function 
  
 relaxColumn 
 () 
  
 { 
  
 /** 
 * Changes columns from required to nullable. 
 * Assumes existing table with the following schema: 
 * [{name: 'Name', type: 'STRING', mode: 'REQUIRED'}, 
 * {name: 'Age', type: 'INTEGER'}, 
 * {name: 'Weight', type: 'FLOAT'}, 
 * {name: 'IsMagic', type: 'BOOLEAN'}]; 
 */ 
  
 /** 
 * TODO(developer): Uncomment the following lines before running the sample. 
 */ 
  
 // const datasetId = 'my_dataset'; 
  
 // const tableId = 'my_table'; 
  
 const 
  
 newSchema 
  
 = 
  
 [ 
  
 { 
 name 
 : 
  
 'Name' 
 , 
  
 type 
 : 
  
 'STRING' 
 , 
  
 mode 
 : 
  
 ' NULLABLE 
' 
 }, 
  
 { 
 name 
 : 
  
 'Age' 
 , 
  
 type 
 : 
  
 ' INTEGER 
' 
 }, 
  
 { 
 name 
 : 
  
 'Weight' 
 , 
  
 type 
 : 
  
 'FLOAT' 
 }, 
  
 { 
 name 
 : 
  
 'IsMagic' 
 , 
  
 type 
 : 
  
 ' BOOLEAN 
' 
 }, 
  
 ]; 
  
 // Retrieve current table metadata 
  
 const 
  
 table 
  
 = 
  
 bigquery 
 . 
 dataset 
 ( 
 datasetId 
 ). 
 table 
 ( 
 tableId 
 ); 
  
 const 
  
 [ 
 metadata 
 ] 
  
 = 
  
 await 
  
 table 
 . 
 getMetadata 
 (); 
  
 // Update schema 
  
 metadata 
 . 
 schema 
  
 = 
  
 newSchema 
 ; 
  
 const 
  
 [ 
 apiResponse 
 ] 
  
 = 
  
 await 
  
 table 
 . 
 setMetadata 
 ( 
 metadata 
 ); 
  
 console 
 . 
 log 
 ( 
 apiResponse 
 . 
 schema 
 . 
 fields 
 ); 
 } 
 

  from 
  
 google.cloud 
  
 import 
  bigquery 
 
 client 
 = 
  bigquery 
 
 . 
  Client 
 
 () 
 # TODO(dev): Change table_id to full name of the table you want to create. 
 table_id 
 = 
 "your-project.your_dataset.your_table" 
 table 
 = 
 client 
 . 
  get_table 
 
 ( 
 table_id 
 ) 
 new_schema 
 = 
 [] 
 for 
 field 
 in 
 table 
 . 
 schema 
 : 
 if 
 field 
 . 
 mode 
 != 
 "REQUIRED" 
 : 
 new_schema 
 . 
 append 
 ( 
 field 
 ) 
 else 
 : 
 # SchemaField properties cannot be edited after initialization. 
 # To make changes, construct new SchemaField objects. 
 new_field 
 = 
 field 
 . 
 to_api_repr 
 () 
 new_field 
 [ 
 "mode" 
 ] 
 = 
 "NULLABLE" 
 relaxed_field 
 = 
  bigquery 
 
 . 
  SchemaField 
 
 . 
 from_api_repr 
 ( 
 new_field 
 ) 
 new_schema 
 . 
 append 
 ( 
 relaxed_field 
 ) 
 table 
 . 
 schema 
 = 
 new_schema 
 table 
 = 
 client 
 . 
  update_table 
 
 ( 
 table 
 , 
 [ 
 "schema" 
 ]) 
 print 
 ( 
 f 
 "Updated 
 { 
 table_id 
 } 
 schema: 
 { 
 table 
 . 
 schema 
 } 
 ." 
 )