Using schema auto-detection

Schema auto-detection

Schema auto-detection enables BigQuery to infer the schema for CSV, JSON, or Google Sheets data. Schema auto-detection is available when you load data into BigQuery and when you query an external data source .

When auto-detection is enabled, BigQuery infers the data type for each column. BigQuery selects a random file in the data source and scans up to the first 500 rows of data to use as a representative sample. BigQuery then examines each field and attempts to assign a data type to that field based on the values in the sample. If all of the rows in a column are empty, auto-detection will default to STRING data type for the column.

If you don't enable schema auto-detection for CSV, JSON, or Google Sheets data, then you must provide the schema manually when creating the table.

You don't need to enable schema auto-detection for Avro, Parquet, ORC, Firestore export, or Datastore export files. These file formats are self-describing, so BigQuery automatically infers the table schema from the source data. For Parquet, Avro, and Orc files, you can optionally provide an explicit schema to override the inferred schema.

You can see the detected schema for a table in the following ways:

• Use the Google Cloud console.
• Use the bq command-line tool's bq show command.

When BigQuery detects schemas, it might, on rare occasions, change a field name to make it compatible with GoogleSQL syntax.

For information about data type conversions, see the following:

• Data type conversion when loading data from Datastore
• Data type conversion when loading data from Firestore
• Avro conversions
• Parquet conversions
• ORC conversions

Loading data using schema auto-detection

To enable schema auto-detection when loading data, use one of these approaches:

• In the Google Cloud console, in the Schemasection, for Auto detect, check the Schema and input parametersoption.
• In the bq command-line tool, use the bq load command with the --autodetect parameter.

When schema auto-detection is enabled, BigQuery makes a best-effort attempt to automatically infer the schema for CSV and JSON files. The auto-detection logic infers the schema field types by reading up to the first 500 rows of data. Leading lines are skipped if the --skip_leading_rows flag is present. The field types are based on the rows having the most fields. Therefore, auto-detection should work as expected as long as there is at least one row of data that has values in every column/field.

Schema auto-detection is not used with Avro files, Parquet files, ORC files, Firestore export files, or Datastore export files. When you load these files into BigQuery, the table schema is automatically retrieved from the self-describing source data.

To use schema auto-detection when you load JSON or CSV data:

bq  
--location = 
 LOCATION 
  
load  
 \ 
--autodetect  
 \ 
--source_format = 
 FORMAT 
  
 \ 
 DATASET 
. TABLE 
  
 \ 
 PATH_TO_SOURCE 

  bq 
  
 load 
  
 -- 
 autodetect 
  
 -- 
 source_format 
 = 
 CSV 
  
 mydataset 
 . 
 mytable 
  
 ./ 
 myfile 
 . 
 csv 
 

  bq 
  
 load 
  
 -- 
 autodetect 
  
 -- 
 source_format 
 = 
 NEWLINE_DELIMITED_JSON 
  
\ mydataset 
 . 
 mytable 
  
 ./ 
 myfile 
 . 
 json 
 

  import 
  
 ( 
  
 "context" 
  
 "fmt" 
  
 "cloud.google.com/go/bigquery" 
 ) 
 // importJSONAutodetectSchema demonstrates loading data from newline-delimited JSON data in Cloud Storage 
 // and using schema autodetection to identify the available columns. 
 func 
  
 importJSONAutodetectSchema 
 ( 
 projectID 
 , 
  
 datasetID 
 , 
  
 tableID 
  
 string 
 ) 
  
 error 
  
 { 
  
 // projectID := "my-project-id" 
  
 // datasetID := "mydataset" 
  
 // tableID := "mytable" 
  
 ctx 
  
 := 
  
 context 
 . 
 Background 
 () 
  
 client 
 , 
  
 err 
  
 := 
  
 bigquery 
 . 
 NewClient 
 ( 
 ctx 
 , 
  
 projectID 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 fmt 
 . 
 Errorf 
 ( 
 "bigquery.NewClient: %v" 
 , 
  
 err 
 ) 
  
 } 
  
 defer 
  
 client 
 . 
 Close 
 () 
  
 gcsRef 
  
 := 
  
 bigquery 
 . 
  NewGCSReference 
 
 ( 
 "gs://cloud-samples-data/bigquery/us-states/us-states.json" 
 ) 
  
 gcsRef 
 . 
 SourceFormat 
  
 = 
  
 bigquery 
 . 
  JSON 
 
  
 gcsRef 
 . 
 AutoDetect 
  
 = 
  
 true 
  
 loader 
  
 := 
  
 client 
 . 
 Dataset 
 ( 
 datasetID 
 ). 
 Table 
 ( 
 tableID 
 ). 
  LoaderFrom 
 
 ( 
 gcsRef 
 ) 
  
 loader 
 . 
 WriteDisposition 
  
 = 
  
 bigquery 
 . 
  WriteEmpty 
 
  
 job 
 , 
  
 err 
  
 := 
  
 loader 
 . 
 Run 
 ( 
 ctx 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 status 
 , 
  
 err 
  
 := 
  
 job 
 . 
 Wait 
 ( 
 ctx 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 if 
  
 status 
 . 
  Err 
 
 () 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 fmt 
 . 
 Errorf 
 ( 
 "job completed with error: %v" 
 , 
  
 status 
 . 
  Err 
 
 ()) 
  
 } 
  
 return 
  
 nil 
 }