Load statements in GoogleSQL

LOAD DATA statement

Loads data from one or more files into a table. The statement can create a new table, append data into an existing table or partition, or overwrite an existing table or partition. If the LOAD DATA statement fails, the table into which you are loading data remains unchanged.

Syntax

LOAD DATA {OVERWRITE|INTO}  [{TEMP|TEMPORARY} TABLE]
[[project_name.]dataset_name.]table_name
[(
  column_list
)]
[[OVERWRITE] PARTITIONS (partition_column_name=partition_value)]
[PARTITION BY partition_expression 
]
[CLUSTER BY clustering_column_list]
[OPTIONS ( table_option_list 
)]
FROM FILES( load_option_list 
)
[WITH PARTITION COLUMNS
  [(partition_column_list)]
]
[WITH CONNECTION connection_name]

column_list: column 
[, ...]

partition_column_list: partition_column_name, partition_column_type[, ...]

Arguments

• INTO : If a table with this name already exists, the statement appends data to the table. You must use INTO instead of OVERWRITE if your statement includes the PARTITIONS clause.

• OVERWRITE : If a table with this name already exists, the statement overwrites the table.

• {TEMP|TEMPORARY} TABLE : Use this clause to create or write to a temporary table.

• project_name : The name of the project for the table. The value defaults to the project that runs this DDL query.

• dataset_name : The name of the dataset for the table.

• table_name : The name of the table.

• column_list : Contains the table's schema information as a list of table columns. For more information about table schemas, see Specifying a schema . If you don't specify a schema, BigQuery uses schema auto-detection to infer the schema.

  When you load hive-partitioned data into a new table or overwrite an existing table, then that table schema contains the hive-partitioned columns and the columns in the column_list .

  If you append hive-partitioned data to an existing table, then the hive-partitioned columns and column_list can be a subset of the existing columns. If the combined list of columns in not a subset of the existing columns, then the following rules apply:

  • If your data is self-describing, such as ORC, PARQUET, or AVRO, then columns in the source file that are omitted from the column_list are ignored. Columns in the column_list that don't exist in the source file are written with NULL values. If a column is in the column_list and the source file, then their types must match.

  • If your data is not self-describing, such as CSV or JSON, then columns in the source file that are omitted from the column_list are only ignored if you set ignore_unknown_values to TRUE . Otherwise this statement returns an error. You can't list columns in the column_list that don't exist in the source file.

• [OVERWRITE] PARTITIONS : Use this clause to write to or overwrite exactly one partition. When you use this clause, the statement must begin with LOAD DATA INTO .

• partition_column_name : The name of the partitioned column to write to. If you use both the PARTITIONS and the PARTITION BY clauses, then the column names must match.

• partition_value : The partition_id of the partition to append or overwrite. To find the partition_id values of a table, query the INFORMATION_SCHEMA.PARTITIONS view . You can't set the partition_value to __NULL__ or __UNPARTITIONED__ . You can only append to or overwrite one partition. If your data contains values that belong to multiple partitions, then the statement fails with an error. This partition_value must be literal value.

• partition_expression : Specifies the table partitioning when creating a new table.

• clustering_column_list : Specifies table clustering when creating a new table. The value is a comma-separated list of column names, with up to four columns.

• table_option_list : Specifies options for creating the table. If you include this clause and the table already exists, then the options must match the existing table specification.

• partition_column_list : A list of external partitioning columns.

• connection_name : The connection name that is used to read the source files from an external data source .

• load_option_list : Specifies options for loading the data.

If no table exists with the specified name, then the statement creates a new table. If a table already exists with the specified name, then the behavior depends on the INTO or OVERWRITE keyword. The INTO keyword appends the data to the table, and the OVERWRITE keyword overwrites the table.

If your external data uses a hive-partitioned layout , then include the WITH PARTITION COLUMNS clause. If you include the WITH PARTITION COLUMNS clause without partition_column_list , then BigQuery infers the partitioning from the data layout. If you include both column_list and WITH PARTITION COLUMNS , then partition_column_list is required.

You can't use the LOAD DATA statement to load data into a temporary table.

column

(column_name column_schema[, ...]) contains the table's schema information in a comma-separated list.

 column 
  
 := 
  
 column_name 
  
 column_schema 
 column_schema 
  
 := 
  
 { 
  
 simple_type 
  
 | 
  
 STRUCT<field_list> 
  
 | 
  
 ARRAY<array_element_schema> 
  
 } 
  
 [ 
 PRIMARY 
  
 KEY 
  
 NOT 
  
 ENFORCED 
  
 | 
  
 REFERENCES 
  
 table_name 
 ( 
 column_name 
 ) 
  
 NOT 
  
 ENFORCED 
 ] 
  
 [ 
 DEFAULT 
  
 default_expression 
 ] 
  
 [ 
 NOT 
  
 NULL 
 ] 
  
 [ 
 OPTIONS 
 ( 
  column_option_list 
 
 ) 
 ] 
 simple_type 
  
 := 
  
 { 
  
 data_type 
  
 | 
  
 STRING 
  
 COLLATE 
  
 collate_specification 
  
 } 
 field_list 
  
 := 
  
 field_name 
  
 column_schema 
  
 [ 
 , 
  
 ... 
 ] 
 array_element_schema 
  
 := 
  
 { 
  
 simple_type 
  
 | 
  
 STRUCT<field_list> 
  
 } 
  
 [ 
 NOT 
  
 NULL 
 ] 

• column_name is the name of the column. A column name:

  • Must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_)
  • Must start with a letter or underscore
  • Can be up to 300 characters

• column_schema : Similar to a data type , but supports an optional NOT NULL constraint for types other than ARRAY . column_schema also supports options on top-level columns and STRUCT fields.

  column_schema can be used only in the column definition list of CREATE TABLE statements. It cannot be used as a type in expressions.

• simple_type : Any supported data type aside from STRUCT and ARRAY .

  If simple_type is a STRING , it supports an additional clause for collation , which defines how a resulting STRING can be compared and sorted. The syntax looks like this:

    STRING 
    
   COLLATE 
    
   collate_specification 
   
  

  If you have DEFAULT COLLATE collate_specification assigned to the table, the collation specification for a column overrides the specification for the table.

• default_expression : The default value assigned to the column.

• field_list : Represents the fields in a struct.

• field_name : The name of the struct field. Struct field names have the same restrictions as column names.

• NOT NULL : When the NOT NULL constraint is present for a column or field, the column or field is created with REQUIRED mode. Conversely, when the NOT NULL constraint is absent, the column or field is created with NULLABLE mode.

  Columns and fields of ARRAY type do not support the NOT NULL modifier. For example, a column_schema of ARRAY<INT64> NOT NULL is invalid, since ARRAY columns have REPEATED mode and can be empty but cannot be NULL . An array element in a table can never be NULL , regardless of whether the NOT NULL constraint is specified. For example, ARRAY<INT64> is equivalent to ARRAY<INT64 NOT NULL> .

  The NOT NULL attribute of a table's column_schema does not propagate through queries over the table. If table T contains a column declared as x INT64 NOT NULL , for example, CREATE TABLE dataset.newtable AS SELECT x FROM T creates a table named dataset.newtable in which x is NULLABLE .

column_option_list

Specify a column option list in the following format:

NAME=VALUE, ...

NAME and VALUE must be one of the following combinations:

VALUE is a constant expression containing only literals, query parameters, and scalar functions.

The constant expression cannotcontain:

• A reference to a table
• Subqueries or SQL statements such as SELECT , CREATE , or UPDATE
• User-defined functions, aggregate functions, or analytic functions
• The following scalar functions:
  • ARRAY_TO_STRING
  • REPLACE
  • REGEXP_REPLACE
  • RAND
  • FORMAT
  • LPAD
  • RPAD
  • REPEAT
  • SESSION_USER
  • GENERATE_ARRAY
  • GENERATE_DATE_ARRAY

Setting the VALUE replaces the existing value of that option for the column, if there was one. Setting the VALUE to NULL clears the column's value for that option.

partition_expression

PARTITION BY is an optional clause that controls table and vector index partitioning. partition_expression is an expression that determines how to partition the table or vector index. The partition expression can contain the following values:

• _PARTITIONDATE . Partition by ingestion time with daily partitions. This syntax cannot be used with the AS query_statement clause.
• DATE(_PARTITIONTIME) . Equivalent to _PARTITIONDATE . This syntax cannot be used with the AS query_statement clause.
• <date_column> . Partition by a DATE column with daily partitions.
• DATE({ <timestamp_column> | <datetime_column> }) . Partition by a TIMESTAMP or DATETIME column with daily partitions.
• DATETIME_TRUNC(<datetime_column>, { DAY | HOUR | MONTH | YEAR }) . Partition by a DATETIME column with the specified partitioning type.
• TIMESTAMP_TRUNC(<timestamp_column>, { DAY | HOUR | MONTH | YEAR }) . Partition by a TIMESTAMP column with the specified partitioning type.
• TIMESTAMP_TRUNC(_PARTITIONTIME, { DAY | HOUR | MONTH | YEAR }) . Partition by ingestion time with the specified partitioning type. This syntax cannot be used with the AS query_statement clause.
• DATE_TRUNC(<date_column>, { MONTH | YEAR }) . Partition by a DATE column with the specified partitioning type.

• RANGE_BUCKET(<int64_column>, GENERATE_ARRAY(<start>, <end>[, <interval>])) . Partition by an integer column with the specified range, where:

  • start is the start of range partitioning, inclusive.
  • end is the end of range partitioning, exclusive.
  • interval is the width of each range within the partition. Defaults to 1.

table_option_list

The option list lets you set table options such as a label and an expiration time. You can include multiple options using a comma-separated list.

Specify a table option list in the following format:

NAME=VALUE, ...

NAME and VALUE must be one of the following combinations:

VALUE is a constant expression containing only literals, query parameters, and scalar functions.

The constant expression cannotcontain:

• A reference to a table
• Subqueries or SQL statements such as SELECT , CREATE , or UPDATE
• User-defined functions, aggregate functions, or analytic functions
• The following scalar functions:
  • ARRAY_TO_STRING
  • REPLACE
  • REGEXP_REPLACE
  • RAND
  • FORMAT
  • LPAD
  • RPAD
  • REPEAT
  • SESSION_USER
  • GENERATE_ARRAY
  • GENERATE_DATE_ARRAY

load_option_list

Specifies options for loading data from external files. The format and uris options are required. Specify the option list in the following format: NAME=VALUE, ...

Examples

The following examples show common use cases for the LOAD DATA statement.

Load data into a table

The following example loads an Avro file into a table. Avro is a self-describing format, so BigQuery infers the schema.

 LOAD 
  
  DATA 
 
  
 INTO 
  
 mydataset 
 . 
 table1 
  
 FROM 
  
  FILES 
 
 ( 
  
 format 
 = 
 'AVRO' 
 , 
  
 uris 
  
 = 
  
 [ 
 'gs://bucket/path/file.avro' 
 ] 
  
 ) 

The following example loads two CSV files into a table, using schema autodetection.

 LOAD 
  
  DATA 
 
  
 INTO 
  
 mydataset 
 . 
 table1 
  
 FROM 
  
  FILES 
 
 ( 
  
 format 
 = 
 'CSV' 
 , 
  
 uris 
  
 = 
  
 [ 
 'gs://bucket/path/file1.csv' 
 , 
  
 'gs://bucket/path/file2.csv' 
 ] 
  
 ) 

Load data using a schema

The following example loads a CSV file into a table, using a specified table schema.

 LOAD 
  
  DATA 
 
  
 INTO 
  
 mydataset 
 . 
 table1 
 ( 
 x 
  
 INT64 
 , 
  
 y 
  
 STRING 
 ) 
  
 FROM 
  
  FILES 
 
 ( 
  
 skip_leading_rows 
 = 
 1 
 , 
  
 format 
 = 
 'CSV' 
 , 
  
 uris 
  
 = 
  
 [ 
 'gs://bucket/path/file.csv' 
 ] 
  
 ) 

Set options when creating a new table

The following example creates a new table with a description and an expiration time.

 LOAD 
  
  DATA 
 
  
 INTO 
  
 mydataset 
 . 
 table1 
  
  OPTIONS 
 
 ( 
  
 description 
 = 
 "my table" 
 , 
  
 expiration_timestamp 
 = 
 "2025-01-01 00:00:00 UTC" 
  
 ) 
  
 FROM 
  
  FILES 
 
 ( 
  
 format 
 = 
 'AVRO' 
 , 
  
 uris 
  
 = 
  
 [ 
 'gs://bucket/path/file.avro' 
 ] 
  
 ) 

Overwrite an existing table

The following example overwrites an existing table.

 LOAD 
  
  DATA 
  
 OVERWRITE 
 
  
 mydataset 
 . 
 table1 
  
 FROM 
  
  FILES 
 
 ( 
  
 format 
 = 
 'AVRO' 
 , 
  
 uris 
  
 = 
  
 [ 
 'gs://bucket/path/file.avro' 
 ] 
  
 ) 

Load data into a temporary table

The following example loads an Avro file into a temporary table.

 LOAD 
  
  DATA 
  
 INTO 
  
 TEMP 
  
 TABLE 
 
  
 mydataset 
 . 
 table1 
  
 FROM 
  
  FILES 
 
 ( 
  
 format 
 = 
 'AVRO' 
 , 
  
 uris 
  
 = 
  
 [ 
 'gs://bucket/path/file.avro' 
 ] 
  
 ) 

Specify table partitioning and clustering

The following example creates a table that is partitioned by the transaction_date field and clustered by the customer_id field. It also configures the partitions to expire after three days.

 LOAD 
  
  DATA 
 
  
 INTO 
  
 mydataset 
 . 
 table1 
  
  PARTITION 
 
  
 BY 
  
 transaction_date 
  
  CLUSTER 
 
  
 BY 
  
 customer_id 
  
  OPTIONS 
 
 ( 
  
 partition_expiration_days 
 = 
 3 
  
 ) 
  
 FROM 
  
  FILES 
 
 ( 
  
 format 
 = 
 'AVRO' 
 , 
  
 uris 
  
 = 
  
 [ 
 'gs://bucket/path/file.avro' 
 ] 
  
 ) 

Load data into a partition

The following example loads data into a selected partition of an ingestion-time partitioned table:

 LOAD 
  
  DATA 
 
  
 INTO 
  
 mydataset 
 . 
 table1 
  PARTITIONS 
 
 ( 
 _PARTITIONTIME 
  
 = 
  
 TIMESTAMP 
  
 '2016-01-01' 
 ) 
  
  PARTITION 
 
  
 BY 
  
 _PARTITIONTIME 
  
 FROM 
  
  FILES 
 
 ( 
  
 format 
  
 = 
  
 'AVRO' 
 , 
  
 uris 
  
 = 
  
 [ 
 'gs://bucket/path/file.avro' 
 ] 
  
 ) 

Load a file that is externally partitioned

The following example loads a set of external files that use a hive partitioning layout.

 LOAD 
  
  DATA 
 
  
 INTO 
  
 mydataset 
 . 
 table1 
  
 FROM 
  
  FILES 
 
 ( 
  
 format 
 = 
 'AVRO' 
 , 
  
 uris 
  
 = 
  
 [ 
 'gs://bucket/path/*' 
 ], 
  
 hive_partition_uri_prefix 
 = 
 'gs://bucket/path' 
  
 ) 
  
 WITH 
  
  PARTITION 
  
 COLUMNS 
 
 ( 
  
 field_1 
  
 STRING 
 , 
  
 -- column order must match the external path 
  
 field_2 
  
 INT64 
  
 ) 

The following example infers the partitioning layout:

 LOAD 
  
  DATA 
 
  
 INTO 
  
 mydataset 
 . 
 table1 
  
 FROM 
  
  FILES 
 
 ( 
  
 format 
 = 
 'AVRO' 
 , 
  
 uris 
  
 = 
  
 [ 
 'gs://bucket/path/*' 
 ], 
  
 hive_partition_uri_prefix 
 = 
 'gs://bucket/path' 
  
 ) 
  
 WITH 
  
  PARTITION 
  
 COLUMNS 
 

If you include both column_list and WITH PARTITION COLUMNS , then you must explicitly list the partitioning columns. For example, the following query returns an error:

 -- This query returns an error. 
 LOAD 
  
  DATA 
 
  
 INTO 
  
 mydataset 
 . 
 table1 
  
 ( 
  
 x 
  
 INT64 
 , 
  
 -- column_list is given but the partition column list is missing 
  
 y 
  
 STRING 
  
 ) 
  
 FROM 
  
  FILES 
 
 ( 
  
 format 
 = 
 'AVRO' 
 , 
  
 uris 
  
 = 
  
 [ 
 'gs://bucket/path/*' 
 ], 
  
 hive_partition_uri_prefix 
 = 
 'gs://bucket/path' 
  
 ) 
  
 WITH 
  
  PARTITION 
  
 COLUMNS 
 

Load data with cross-cloud transfer

Example 1

The following example loads a parquet file named sample.parquet from an Amazon S3 bucket into the test_parquet table with an auto-detect schema:

 LOAD 
  
 DATA 
  
 INTO 
  
 mydataset 
 . 
 testparquet 
  
 FROM 
  
 FILES 
  
 ( 
  
 uris 
  
 = 
  
 [ 
 's3://test-bucket/sample.parquet' 
 ] 
 , 
  
 format 
  
 = 
  
 'PARQUET' 
  
 ) 
  
 WITH 
  
 CONNECTION 
  
 `aws-us-east-1.test-connection` 

Example 2

The following example loads a CSV file with the prefix sampled* from your Blob Storage into the test_csv table with predefined column partitioning by time:

 LOAD 
  
 DATA 
  
 INTO 
  
 mydataset 
 . 
 test_csv 
  
 ( 
 Number 
  
 INT64 
 , 
  
 Name 
  
 STRING 
 , 
  
 Time 
  
 DATE 
 ) 
  
 PARTITION 
  
 BY 
  
 Time 
  
 FROM 
  
 FILES 
  
 ( 
  
 format 
  
 = 
  
 'CSV' 
 , 
  
 uris 
  
 = 
  
 [ 
 'azure://test.blob.core.windows.net/container/sampled*' 
 ] 
 , 
  
 skip_leading_rows 
 = 
 1 
  
 ) 
  
 WITH 
  
 CONNECTION 
  
 `azure-eastus2.test-connection` 

Example 3

The following example overwrites the existing table test_parquet with data from a file named sample.parquet with an auto-detect schema:

 LOAD 
  
 DATA 
  
 OVERWRITE 
  
 mydataset 
 . 
 testparquet 
  
 FROM 
  
 FILES 
  
 ( 
  
 uris 
  
 = 
  
 [ 
 's3://test-bucket/sample.parquet' 
 ] 
 , 
  
 format 
  
 = 
  
 'PARQUET' 
  
 ) 
  
 WITH 
  
 CONNECTION 
  
 `aws-us-east-1.test-connection`