Loading Parquet data from Cloud Storage

This page provides an overview of loading Parquet data from Cloud Storageinto BigQuery.

Parquet is anopen source column-oriented data format that is widely used in theApache Hadoop ecosystem.

When you load Parquet data from Cloud Storage, you can load the data into anew table or partition, or you can append to or overwrite an existing table orpartition. When your data is loaded into BigQuery, it isconverted into columnar format forCapacitor(BigQuery's storage format).

When you load data from Cloud Storage into a BigQuery table,the dataset that contains the table must be in the same regional or multi-regional location as the Cloud Storage bucket.

For information about loading Parquet data from a local file, seeLoading data from local files.

Limitations

You are subject to the following limitations when you load data intoBigQuery from a Cloud Storage bucket:

• BigQuery does not guarantee data consistency for external datasources. Changes to the underlying data while a query is running can result inunexpected behavior.

• BigQuery doesn't supportCloud Storage object versioning. If youinclude a generation number in the Cloud Storage URI, then the load jobfails.

• You can't use a wildcard in the Cloud Storage URI if any of thefiles to be loaded have different schemas. Any difference in the position ofcolumns qualifies as a different schema.

Input file requirements

To avoidresourcesExceeded errors when loading Parquet files intoBigQuery, follow these guidelines:

• Keep row sizes to 50 MB or less.
• If your input data contains more than 100 columns, consider reducing the pagesize to be smaller than the default page size (1 * 1024 * 1024 bytes). Thisis especially helpful if you are using significant compression.
• For optimal performance, aim for row group sizes of at least 16 MiB.Smaller row group sizes increase I/O and slow down loads and queries.

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessarypermissions to perform each task in this document, and create a datasetto store your data.

Required permissions

To load data into BigQuery, you need IAM permissions to run a load job and load data into BigQuery tables and partitions. If you are loading data from Cloud Storage, you also need IAM permissions to access the bucket that contains your data.

Permissions to load data into BigQuery

To load data into a new BigQuery table or partition or to append or overwrite an existing table or partition, you need the following IAM permissions:

• bigquery.tables.create
• bigquery.tables.updateData
• bigquery.tables.update
• bigquery.jobs.create

Each of the following predefined IAM roles includes the permissions that you need in order to load data into a BigQuery table or partition:

• roles/bigquery.dataEditor
• roles/bigquery.dataOwner
• roles/bigquery.admin (includes thebigquery.jobs.create permission)
• bigquery.user (includes thebigquery.jobs.create permission)
• bigquery.jobUser (includes thebigquery.jobs.create permission)

Additionally, if you have thebigquery.datasets.create permission, you can create andupdate tables using a load job in the datasets that you create.

For more information on IAM roles and permissions inBigQuery, seePredefined roles and permissions.

Permissions to load data from Cloud Storage

To get the permissions that you need to load data from a Cloud Storage bucket, ask your administrator to grant you theStorage Admin (roles/storage.admin) IAM role on the bucket. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to load data from a Cloud Storage bucket. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

Create a dataset

Create aBigQuery dataset to storeyour data.

Parquet schemas

When you load Parquet files into BigQuery, the table schema isautomatically retrieved from the self-describing source data. WhenBigQuery retrieves the schema from the source data, thealphabetically last file is used.

For example, you have the following Parquet files in Cloud Storage:

gs://mybucket/00/  a.parquet  z.parquetgs://mybucket/01/  b.parquet

Running this command in the bq command-line tool loads all of the files (as acomma-separated list), and the schema is derived frommybucket/01/b.parquet:

bqload\--source_format=PARQUET\dataset.table\"gs://mybucket/00/*.parquet","gs://mybucket/01/*.parquet"

When you load multiple Parquet files that have different schemas, identicalcolumns specified in multiple schemas must have the samemodein each schema definition.

When BigQuery detects the schema, some Parquet data types areconverted to BigQuery data types to make them compatible withGoogleSQL syntax. For more information, seeParquet conversions.

For example,--reference_file_schema_uri="gs://mybucket/schema.parquet".

Parquet compression

BigQuery supports the following compression codecs forParquet file contents:

• GZip
• LZO_1C
• LZO_1X
• LZ4_RAW
• Snappy
• ZSTD

Loading Parquet data into a new table

You can load Parquet data into a new table by using one of the following:

• The Google Cloud console
• The bq command-line tool'sbq load command
• Thejobs.insert API method and configuring aload job
• The client libraries

To load Parquet data from Cloud Storage into a new BigQuerytable:

bq--location=LOCATIONload\--source_format=FORMAT\DATASET.TABLE\PATH_TO_SOURCE

bqload\--source_format=PARQUET\mydataset.mytable\gs://mybucket/mydata.parquet

bqload\--source_format=PARQUET\--time_partitioning_type=DAY\mydataset.mytable\gs://mybucket/mydata.parquet

bqload\--source_format=PARQUET\--time_partitioning_fieldmytimestamp\mydataset.mytable\gs://mybucket/mydata.parquet

bqload\--source_format=PARQUET\mydataset.mytable\gs://mybucket/mydata*.parquet

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

import("context""fmt""cloud.google.com/go/bigquery")// importParquet demonstrates loading Apache Parquet data from Cloud Storage into a table.funcimportParquet(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.parquet")gcsRef.SourceFormat=bigquery.ParquetgcsRef.AutoDetect=trueloader:=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}

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;importjava.math.BigInteger;publicclassLoadParquet{publicstaticvoidrunLoadParquet(){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";loadParquet(datasetName);}publicstaticvoidloadParquet(StringdatasetName){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();StringsourceUri="gs://cloud-samples-data/bigquery/us-states/us-states.parquet";TableIdtableId=TableId.of(datasetName,"us_states");LoadJobConfigurationconfiguration=LoadJobConfiguration.builder(tableId,sourceUri).setFormatOptions(FormatOptions.parquet()).build();// For more information on Job see:// https://googleapis.dev/java/google-cloud-clients/latest/index.html?com/google/cloud/bigquery/package-summary.html// Load the tableJobjob=bigquery.create(JobInfo.of(configuration));// Blocks until this load table job completes its execution, either failing or succeeding.JobcompletedJob=job.waitFor();if(completedJob==null){System.out.println("Job not executed since it no longer exists.");return;}elseif(completedJob.getStatus().getError()!=null){System.out.println("BigQuery was unable to load the table due to an error: \n"+job.getStatus().getError());return;}// Check number of rows loaded into the tableBigIntegernumRows=bigquery.getTable(tableId).getNumRows();System.out.printf("Loaded %d rows. \n",numRows);System.out.println("GCS parquet loaded successfully.");}catch(BigQueryException|InterruptedExceptione){System.out.println("GCS Parquet was not loaded. \n"+e.toString());}}}

// 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 Parquet file at * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.parquet * * TODO(developer): Replace the following lines with the path to your file. */constbucketName='cloud-samples-data';constfilename='bigquery/us-states/us-states.parquet';asyncfunctionloadTableGCSParquet(){// Imports a GCS file into a table with Parquet source format./**   * TODO(developer): Uncomment the following lines before running the sample.   */// const datasetId = 'my_dataset';// const tableId = 'my_table';// Configure the load job. For full list of options, see:// https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoadconstmetadata={sourceFormat:'PARQUET',location:'US',};// Load data from a Google Cloud Storage file into the tableconst[job]=awaitbigquery.dataset(datasetId).table(tableId).load(storage.bucket(bucketName).file(filename),metadata);// 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;}}

use Google\Cloud\BigQuery\BigQueryClient;use Google\Cloud\Core\ExponentialBackoff;/** Uncomment and populate these variables in your code */// $projectId  = 'The Google project ID';// $datasetId  = 'The BigQuery dataset ID';// instantiate the bigquery table service$bigQuery = new BigQueryClient([    'projectId' => $projectId,]);$dataset = $bigQuery->dataset($datasetId);$table = $dataset->table('us_states');// create the import job$gcsUri = 'gs://cloud-samples-data/bigquery/us-states/us-states.parquet';$loadConfig = $table->loadFromStorage($gcsUri)->sourceFormat('PARQUET');$job = $table->runJob($loadConfig);// poll the job until it is complete$backoff = new ExponentialBackoff(10);$backoff->execute(function () use ($job) {    print('Waiting for job to complete' . PHP_EOL);    $job->reload();    if (!$job->isComplete()) {        throw new Exception('Job has not yet completed', 500);    }});// check if the job has errorsif (isset($job->info()['status']['errorResult'])) {    $error = $job->info()['status']['errorResult']['message'];    printf('Error running job: %s' . PHP_EOL, $error);} else {    print('Data imported successfully' . PHP_EOL);}

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_name"job_config=bigquery.LoadJobConfig(source_format=bigquery.SourceFormat.PARQUET,)uri="gs://cloud-samples-data/bigquery/us-states/us-states.parquet"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))

Appending to or overwriting a table with Parquet data

You can load additional data into a table either from source files or byappending query results.

In the Google Cloud console, use theWrite preference option to specifywhat action to take when you load data from a source file or from a queryresult.

You have the following options when you load additional data into a table:

If you load data into an existing table, the load job can append the data oroverwrite the table.

You can append or overwrite a table by using one of the following:

• The Google Cloud console
• The bq command-line tool'sbq load command
• Thejobs.insert API method and configuring aload job
• The client libraries

To append or overwrite a table with Parquet data:

bq--location=LOCATIONload\--[no]replace\--source_format=FORMAT\DATASET.TABLE\PATH_TO_SOURCE

bqload\--replace\--source_format=PARQUET\mydataset.mytable\gs://mybucket/mydata.parquet

bqload\--noreplace\--source_format=PARQUET\mydataset.mytable\gs://mybucket/mydata.parquet

import("context""fmt""cloud.google.com/go/bigquery")// importParquetTruncate demonstrates loading Apache Parquet data from Cloud Storage into a table// and overwriting/truncating existing data in the table.funcimportParquetTruncate(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.parquet")gcsRef.SourceFormat=bigquery.ParquetgcsRef.AutoDetect=trueloader:=client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)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}

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.JobInfo.WriteDisposition;importcom.google.cloud.bigquery.LoadJobConfiguration;importcom.google.cloud.bigquery.TableId;importjava.math.BigInteger;publicclassLoadParquetReplaceTable{publicstaticvoidrunLoadParquetReplaceTable(){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";loadParquetReplaceTable(datasetName);}publicstaticvoidloadParquetReplaceTable(StringdatasetName){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();// Imports a GCS file into a table and overwrites table data if table already exists.// This sample loads CSV file at:// https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.csvStringsourceUri="gs://cloud-samples-data/bigquery/us-states/us-states.parquet";TableIdtableId=TableId.of(datasetName,"us_states");// For more information on LoadJobConfiguration see:// https://googleapis.dev/java/google-cloud-clients/latest/com/google/cloud/bigquery/LoadJobConfiguration.Builder.htmlLoadJobConfigurationconfiguration=LoadJobConfiguration.builder(tableId,sourceUri).setFormatOptions(FormatOptions.parquet())// Set the write disposition to overwrite existing table data..setWriteDisposition(WriteDisposition.WRITE_TRUNCATE).build();// For more information on Job see:// https://googleapis.dev/java/google-cloud-clients/latest/index.html?com/google/cloud/bigquery/package-summary.html// Load the tableJobjob=bigquery.create(JobInfo.of(configuration));// Load data from a GCS parquet file into the table// Blocks until this load table job completes its execution, either failing or succeeding.JobcompletedJob=job.waitFor();if(completedJob==null){System.out.println("Job not executed since it no longer exists.");return;}elseif(completedJob.getStatus().getError()!=null){System.out.println("BigQuery was unable to load into the table due to an error: \n"+job.getStatus().getError());return;}// Check number of rows loaded into the tableBigIntegernumRows=bigquery.getTable(tableId).getNumRows();System.out.printf("Loaded %d rows. \n",numRows);System.out.println("GCS parquet overwrote existing table successfully.");}catch(BigQueryException|InterruptedExceptione){System.out.println("Table extraction job was interrupted. \n"+e.toString());}}}

// 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 CSV file at * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.csv * * TODO(developer): Replace the following lines with the path to your file. */constbucketName='cloud-samples-data';constfilename='bigquery/us-states/us-states.parquet';asyncfunctionloadParquetFromGCSTruncate(){/**   * 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 = "my_table";// Configure the load job. For full list of options, see:// https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoadconstmetadata={sourceFormat:'PARQUET',// Set the write disposition to overwrite existing table data.writeDisposition:'WRITE_TRUNCATE',location:'US',};// Load data from a Google Cloud Storage file into the tableconst[job]=awaitbigquery.dataset(datasetId).table(tableId).load(storage.bucket(bucketName).file(filename),metadata);// 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;}}

use Google\Cloud\BigQuery\BigQueryClient;use Google\Cloud\Core\ExponentialBackoff;/** Uncomment and populate these variables in your code */// $projectId = 'The Google project ID';// $datasetId = 'The BigQuery dataset ID';// $tableID = 'The BigQuery table ID';// instantiate the bigquery table service$bigQuery = new BigQueryClient([    'projectId' => $projectId,]);$table = $bigQuery->dataset($datasetId)->table($tableId);// create the import job$gcsUri = 'gs://cloud-samples-data/bigquery/us-states/us-states.parquet';$loadConfig = $table->loadFromStorage($gcsUri)->sourceFormat('PARQUET')->writeDisposition('WRITE_TRUNCATE');$job = $table->runJob($loadConfig);// poll the job until it is complete$backoff = new ExponentialBackoff(10);$backoff->execute(function () use ($job) {    print('Waiting for job to complete' . PHP_EOL);    $job->reload();    if (!$job->isComplete()) {        throw new Exception('Job has not yet completed', 500);    }});// check if the job has errorsif (isset($job->info()['status']['errorResult'])) {    $error = $job->info()['status']['errorResult']['message'];    printf('Error running job: %s' . PHP_EOL, $error);} else {    print('Data imported successfully' . PHP_EOL);}

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

Loading hive-partitioned Parquet data

BigQuery supports loading hive partitioned Parquet data stored onCloud Storage and populates the hive partitioning columns as columns inthe destination BigQuery managed table. For more information, seeLoading externally partitioned data.

Parquet conversions

This section describes how BigQuery parses various data types when loading Parquet data.

Some Parquet data types (such asINT32,INT64,BYTE_ARRAY, andFIXED_LEN_BYTE_ARRAY) can be converted into multiple BigQuery data types. To ensure BigQuery converts the Parquet data types correctly, specify the appropriate data type in the Parquet file.

For example, to convert the ParquetINT32 data type to the BigQueryDATE data type, specify the following:

optional int32 date_col (DATE);

BigQuery converts Parquet data types to theBigQuery data types that are described in the following sections.

Type conversions

Nested groups are converted intoSTRUCT types.Other combinations of Parquet types and converted types are not supported.

Unsigned logical types

The ParquetUINT_8,UINT_16,UINT_32, andUINT_64 types are unsigned.BigQuery will treat values with these types as unsigned when loading into aBigQuery signedINTEGER column. In the case ofUINT_64, an error will be returnedif the unsigned value exceeds the maximumINTEGER value of9,223,372,036,854,775,807.

Decimal logical type

Decimal logical types can be converted toNUMERIC,BIGNUMERIC, orSTRING types. The converted type dependson the precision and scale parameters of thedecimal logical type and thespecified decimal target types. Specify the decimal target type as follows:

• For aload job using thejobs.insert API: use theJobConfigurationLoad.decimalTargetTypesfield.
• For aload job using thebq load command in the bq command-line tool:use the--decimal_target_typesflag.
• For a query against atable with external sources:use theExternalDataConfiguration.decimalTargetTypesfield.
• For apersistent external table created with DDL:use thedecimal_target_types option.

Enum logical type

Enum logical types can be converted toSTRING orBYTES. Specify the converted target type as follows:

• For aload job using thejobs.insert API: use theJobConfigurationLoad.parquetOptionsfield.
• For aload job using thebq load command in the bq command-line tool:use the--parquet_enum_as_string flag.
• For a persistent external table created withbq mk:use the--parquet_enum_as_string flag.

List logical type

You can enable schema inference for ParquetLIST logical types. BigQuerychecks whether theLIST node is in thestandard form or in one of the forms described by thebackward-compatibility rules:

// standard form<optional|required>group<name>(LIST){repeatedgrouplist{<optional|required><element-type>element;}}

If yes, the corresponding field for theLIST node in the converted schema is treatedas if the node has the following schema:

repeated<element-type><name>

The nodes "list" and "element" are omitted.

• For aload job using thejobs.insert API, use theJobConfigurationLoad.parquetOptions field.
• For aload job using thebq load command in the bq command-line tool,use the--parquet_enable_list_inference flag.
• For a persistent external table created withbq mk,use the--parquet_enable_list_inference flag.
• For a persistent external table created with theCREATE EXTERNAL TABLE statement,use theenable_list_inference option.

Geospatial data

You can load Parquet files that containWKT, hex-encodedWKB, orGeoJSON in aSTRING column, orWKB in aBYTE_ARRAY column by specifying aBigQuery schema with the typeGEOGRAPHY. For more information,seeLoading geospatial data.

You can also loadGeoParquet files. In this case, thecolumns described by the GeoParquet metadata are interpreted as typeGEOGRAPHYby default. You can also load the raw WKB data into aBYTES column byproviding an explicit schema. For more information, seeLoading GeoParquetfiles.

Column name conversions

A column name can contain letters (a-z, A-Z), numbers (0-9), or underscores(_), and it must start with a letter or underscore. If you use flexible columnnames, BigQuery supports starting a column name with a number.Exercise caution when starting columns with a number, since using flexiblecolumn names with the BigQuery Storage Read API orBigQuery Storage Write API requires special handling. For more information aboutflexible column name support, seeflexible column names.

Column names have a maximum length of 300 characters. Column names can't use anyof the following prefixes:

• _TABLE_
• _FILE_
• _PARTITION
• _ROW_TIMESTAMP
• __ROOT__
• _COLIDENTIFIER
• _CHANGE_SEQUENCE_NUMBER
• _CHANGE_TYPE
• _CHANGE_TIMESTAMP

Duplicate column names are not allowed even if the case differs. For example, acolumn namedColumn1 is considered identical to a column namedcolumn1. Tolearn more about column naming rules, seeColumnnames in theGoogleSQL reference.

If a table name (for example,test) is the same as one of its column names(for example,test), theSELECT expression interprets thetest column asaSTRUCT containing all other table columns. To avoid this collision, useone of the following methods:

• Avoid using the same name for a table and its columns.

• Avoid using_field_ as a column name prefix. System-reserved prefixescause automatic renaming during queries. For example, theSELECT _field_ FROM project1.dataset.test query returns a column named_field_1. If you must query a column with this name, use an alias tocontrol the output.

• Assign the table a different alias. For example, the following query assignsa table aliast to the tableproject1.dataset.test:

  SELECTtestFROMproject1.dataset.testASt;

• Include the table name when referencing a column. For example:

  SELECTtest.testFROMproject1.dataset.test;

Flexible column names

You have more flexibility in what you name columns, including expanded accessto characters in languages other than English as well as additional symbols.Make sure to use backtick (`) characters to enclose flexible column names if they areQuoted Identifiers.

Flexible column names support the following characters:

• Any letter in any language, as represented by the Unicode regular expression\p{L}.
• Any numeric character in any language as represented by the Unicode regularexpression\p{N}.
• Any connector punctuation character, including underscores, as representedby the Unicode regular expression\p{Pc}.
• A hyphen or dash as represented by the Unicode regular expression\p{Pd}.
• Any mark intended to accompany another character as represented by theUnicode regular expression\p{M}.For example, accents, umlauts, or enclosing boxes.
• The following special characters:
  • An ampersand (&) as represented by the Unicode regularexpression\u0026.
  • A percent sign (%) as represented by the Unicode regularexpression\u0025.
  • An equals sign (=) as represented by the Unicode regularexpression\u003D.
  • A plus sign (+) as represented by the Unicode regularexpression\u002B.
  • A colon (:) as represented by the Unicode regularexpression\u003A.
  • An apostrophe (') as represented by the Unicode regularexpression\u0027.
  • A less-than sign (<) as represented by the Unicode regularexpression\u003C.
  • A greater-than sign (>) as represented by the Unicode regularexpression\u003E.
  • A number sign (#) as represented by the Unicode regularexpression\u0023.
  • A vertical line (|) as represented by the Unicode regularexpression\u007c.
  • Whitespace.

Flexible column names don't support the following special characters:

• An exclamation mark (!) as represented by the Unicode regularexpression\u0021.
• A quotation mark (") as represented by the Unicode regularexpression\u0022.
• A dollar sign ($) as represented by the Unicode regularexpression\u0024.
• A left parenthesis (() as represented by the Unicode regularexpression\u0028.
• A right parenthesis ()) as represented by the Unicode regularexpression\u0029.
• An asterisk (*) as represented by the Unicode regularexpression\u002A.
• A comma (,) as represented by the Unicode regularexpression\u002C.
• A period (.) as represented by the Unicode regularexpression\u002E. Periods arenot replaced by underscores in Parquet filecolumn names when a column name character map is used. For more information,seeflexible column limitations.
• A slash (/) as represented by the Unicode regularexpression\u002F.
• A semicolon (;) as represented by the Unicode regularexpression\u003B.
• A question mark (?) as represented by the Unicode regularexpression\u003F.
• An at sign (@) as represented by the Unicode regularexpression\u0040.
• A left square bracket ([) as represented by the Unicode regularexpression\u005B.
• A backslash (\) as represented by the Unicode regularexpression\u005C.
• A right square bracket (]) as represented by the Unicode regularexpression\u005D.
• A circumflex accent (^) as represented by the Unicode regularexpression\u005E.
• A grave accent (`) as represented by the Unicode regularexpression\u0060.
• A left curly bracket {{) as represented by the Unicode regularexpression\u007B.
• A right curly bracket (}) as represented by the Unicode regularexpression\u007D.
• A tilde (~) as represented by the Unicode regular expression\u007E.

For additional guidelines, seeColumn names.

The expanded column characters are supported by both the BigQuery Storage Read APIand the BigQuery Storage Write API. To use the expanded list of Unicode characterswith the BigQuery Storage Read API, you must set a flag. You can use thedisplayName attribute to retrieve the column name. The following exampleshows how to set a flag with the Python client:

fromgoogle.cloud.bigquery_storageimporttypesrequested_session=types.ReadSession()#set avro serialization options for flexible column.options=types.AvroSerializationOptions()options.enable_display_name_attribute=Truerequested_session.read_options.avro_serialization_options=options

To use the expanded list of Unicode characters with the BigQuery Storage Write API,you must provide the schema withcolumn_name notation, unless you are usingtheJsonStreamWriter writer object. The following example shows how toprovide the schema:

syntax="proto2";packagemypackage;// Source protos located in github.com/googleapis/googleapisimport"google/cloud/bigquery/storage/v1/annotations.proto";messageFlexibleSchema{optionalstringitem_name_column=1[(.google.cloud.bigquery.storage.v1.column_name)="name-列"];optionalstringitem_description_column=2[(.google.cloud.bigquery.storage.v1.column_name)="description-列"];}

In this example,item_name_column anditem_description_column areplaceholder names which need to be compliant with theprotocol buffer namingconvention. Note thatcolumn_name annotations always take precedence overplaceholder names.

Limitations

• Flexible column names are not supported withexternal tables.

• You cannot load Parquet files containing columns that have a period(.) in the column name.

• Column names from Parquet files are treated as case-insensitive when loadedinto BigQuery. Identical case-insensitive names will causecollisions. To avoid this, either append an underscore to one of the duplicatecolumn names or rename the columns before loading.

Debugging your Parquet file

If your load jobs fail with data errors, you can usePyArrow to verify if yourParquet data files are corrupted. If PyArrow fails to read the files, the filesare likely to be rejected by the BigQuery load job. The followingexample shows how to read the contents of a Parquet file by using PyArrow:

frompyarrowimportparquetaspq# Read the entire filepq.read_table('your_sample_file.parquet')# Read specific columnspq.read_table('your_sample_file.parquet',columns=['some_column','another_column'])# Read the metadata of specific columnsfile_metadata=pq.read_metadata('your_sample_file.parquet')forcolinfile_metadata.row_group(0).to_dict()['columns']:printcol['column_path_in_schema']printcol['num_values']

For more information, see thePyArrow docs.