Introduction to external tables

Important: The term "BigLake" on this page refers to an accessdelegation functionality for external tables in BigQuery. Forinformation about BigLake, the stand-alone Google Cloudproduct that includes BigLake metastore, the Apache Iceberg REST catalog,and BigLake tables for Apache Iceberg seeBigLake overview.

This document describes how to work with data stored outside of BigQuery in external tables. To work with external data sources, you can also useExternal datasets.

Non-BigLake external tables let you query structured datain external data stores. To query a non-BigLake externaltable, you must have permissions to both the external table and theexternal data source. For example, to query a non-BigLakeexternal table that uses a data source in Cloud Storage,you must have the following permissions:

• bigquery.tables.getData
• bigquery.jobs.create
• storage.buckets.get
• storage.objects.get

Supported data stores

You can use non-BigLake external tables with thefollowing data stores:

• Cloud Storage
• Bigtable
• Google Drive

Temporary table support

You can query an external data source in BigQuery by using a permanent table or atemporary table. A permanent table is a table that is created in a dataset and is linked to yourexternal data source. Because the table is permanent, you can useaccess controls to share thetable with others who also have access to the underlying external data source, and you can query thetable at any time.

When you query an external data source using a temporary table, you submit a command thatincludes a query and creates a non-permanent table linked to the external data source. When you usea temporary table, you do not create a table in one of your BigQuery datasets.Because the table is not permanently stored in a dataset, it cannot be shared with others. Queryingan external data source using a temporary table is useful for one-time, ad-hoc queries over externaldata, or for extract, transform, and load (ETL) processes.

Multiple source files

If you create a non-BigLake external table based onCloud Storage, then you can use multiple external data sources,provided those data sources have the same schema. This isn't supportedfor non-BigLake external table based on Bigtableor Google Drive.

Limitations

The following limitations apply to external tables:

• BigQuery does not guarantee data consistency for externaldata tables. Changes to the underlying data while a query is running canresult in unexpected behavior.
• Query performance for external tables might be slow compared to queryingdata in a standard BigQuery table. If query speed is apriority,load the data into BigQueryinstead of setting up an external data source. The performance of a querythat includes an external table depends on the external storage type.For example, querying data stored in Cloud Storage is faster thanquerying data stored in Google Drive. In general, the query performancefor an external table should be equivalent to reading the data directlyfrom the data source.
• You cannot modify external data tables using DML or other methods. Externaltables are read-only for BigQuery.
• You cannot use theTableDataList JSON API method to retrieve data fromexternal tables. For more information, seetabledata.list.To work around this limitation, you can save query results in a destinationtable. You can then use theTableDataList method on the results table.
• You cannot run a BigQuery job that exports data from anexternal table.To work around this limitation, you can save query results in a destinationtable. Then, run an extract job against the results table.
• You cannot copy an external table.
• You cannot reference an external table in awildcard tablequery.
• External tables don't support clustering. They support partitioning inlimited ways. For details, seeQuerying externally partitioned data.
• When you query an external data source other than Cloud Storage,the results are notcached.(GoogleSQL queries on Cloud Storage are supported.)You are charged for each query against an external table even if you issuethe same query multiple times. If you need to repeatedly issue a queryagainst an external table that does not change frequently, considerwriting the query results to a permanent tableand run the queries against the permanent table instead.
• You are limited to 16 concurrent queries against a Bigtableexternal data source.
• A dry run of a federated query that uses an external table mightreport a lower bound of 0 bytes of data, even if rows are returned. This isbecause the amount of data processed from the external table can't bedetermined until the actual query completes. Running the federated queryincurs a cost for processing this data.
• You can't use_object_metadata as a column name in external tables. It isreserved for internal use.
• BigQuery doesn't support the display of table storagestatistics for external tables.
• External tables don't supportflexible column names.
• BI Engine doesn't support queries to external tables.
• BigQuery doesn't supportData Boost for Spannerforreading Bigtable data from BigQuery.
• BigQuery doesn't supporttime travel or fail-safe dataretention windows for external tables.However, for Apache Iceberg external tables, you can use theFOR SYSTEM_TIME AS OF clauseto access snapshots that are retained in your Icebergmetadata.
• All format specific limitations apply:
  • CSV limitations
  • JSON limitations
  • Parquet limitations
  • ORC limitations
  • Avro limitations
  • Iceberg limitations
  • Delta Lake limitations

Location considerations

When you choose a location for your external table, you need to take intoconsideration both the location of the BigQuery dataset andthe external data source.

Cloud Storage

When you query data in Cloud Storage by using aBigLake or anon-BigLake external table,the bucket must be colocated with your BigQuery datasetthat contains the external table definition. For example:

• Single region buckets

  If your Cloud Storage bucket is in theus-central1 (Iowa)region, your BigQuery dataset must be in theus-central1 (Iowa) region ortheUS multi-region.

  If your Cloud Storage bucket is in theeurope-west4 (Netherlands)region, your BigQuery dataset must in theeurope-west4 (Netherlands)or theEU multi-region.

  If your Cloud Storage bucket is in theeurope-west1 (Belgium)region, the corresponding BigQuerydataset must also be in theeurope-west1 (Belgium) or theEUmulti-region.

• Dual-region buckets

  If your Cloud Storage bucket is in theNAM4 predefineddual-region or any configurable dual-region that includes theus-central1 (Iowa) region, the corresponding BigQuerydataset must be in theus-central1 (Iowa) regionor theUS multi-region.

  If your Cloud Storage bucket is in theEUR4 predefineddual-region or any configurable dual-region that includes theeurope-west4 (Netherlands) region, the corresponding BigQuerydataset must be in theeurope-west4 (Netherlands) regionor theEU multi-region.

  If your Cloud Storage bucket is in theASIA1 predefineddual-region, the corresponding BigQuerydataset must be in theasia-northeast1 (Tokyo)or theasia-northeast2 (Osaka) region.

  If your Cloud Storage bucket uses a configurable dual-region thatincludes theaustralia-southeast1 (Sydney) and theaustralia-southeast2 (Melbourne)region, the corresponding BigQuerybucket must be in either theaustralia-southeast1 (Sydney)or theaustralia-southeast2 (Melbourne) region.

• Multi-region buckets

  Using multi-region dataset locations with multi-region Cloud Storagebuckets isnot recommended for external tables, because external queryperformance depends on minimal latency and optimal network bandwidth.

  If your BigQuery dataset is in theUS multi-region, thecorresponding Cloud Storage bucket must be in theUS multi-region,in the single regionus-central1 (Iowa), or in a dual-region that includesus-central1 (Iowa), like theNAM4 dual-region, or in a configurabledual-region that includesus-central1.

  If your BigQuery dataset is in theEU multi-region, thecorresponding Cloud Storage bucket must be in theEU multi-region,in the single regioneurope-west1 (Belgium) oreurope-west4 (Netherlands),or a dual-region that includeseurope-west1 (Belgium) oreurope-west4 (Netherlands),like theEUR4 dual-region, or in a configurable dual-region that includeseurope-west1 oreurope-west4.

For more information about supported Cloud Storage locations, seeBucket locations in theCloud Storage documentation.

Bigtable

When youquery data in Bigtablethrough a BigQuery external table,your Bigtable instance must be in the same location as yourBigQuery dataset:

• Single region: If your BigQuery dataset is in the Belgium(europe-west1) regional location, the correspondingBigtable instance must be in the Belgium region.
• Multi-region: Because external query performance depends on minimal latencyand optimal network bandwidth, using multi-region dataset locations isnot recommended for external tables on Bigtable.

For more information about supported Bigtable locations, seeBigtable locations.

Google Drive

Location considerations don't apply toGoogle Driveexternal data sources.

Moving data between locations

To manually move a dataset from one location to another, follow these steps:

1. Export the data from your BigQuery tables to a Cloud Storage bucket.

   There are no charges for exporting data from BigQuery, but you do incur charges forstoring the exported data in Cloud Storage. BigQuery exports are subject to the limits onextract jobs.

2. Copy or move the data from your export Cloud Storage bucket to a new bucket you created in the destination location. For example, if you are moving your data from theUS multi-region to theasia-northeast1 Tokyo region, you would transfer the data to a bucket that you created in Tokyo. For information about transferring Cloud Storage objects, seeCopy, rename, and move objects in the Cloud Storage documentation.

   Transferring data between regions incursnetwork egress charges in Cloud Storage.

3. Create a new BigQuery dataset in the new location, and then load your data from the Cloud Storage bucket into the new dataset.

   You are not charged for loading the data into BigQuery, but you will incur charges for storing the data in Cloud Storage until you delete the data or the bucket. You are also charged for storing the data in BigQuery after it is loaded. Loading data into BigQuery is subject to theload jobs limits.

You can also use Cloud Composer to move and copy large datasets programmatically.

For more information about using Cloud Storage to store and move large datasets, seeUse Cloud Storage with big data.

Pricing

When querying an external table from BigQuery, you arecharged for running the query and the applicable bytes read if usingBigQuery on-demand (perTiB) pricing or slot consumption if usingBigQuery capacity (per slot-hour) pricing.

If your data is stored in ORC or Parquet on Cloud Storage, seeData size calculation.

You are also charged for storing the data and any resources used by the sourceapplication, subject to the application's pricing guidelines:

• For information on Cloud Storage pricing, seeCloud Storage pricing.Cloud Storage charges might include the following:

  • Data storage costs.
  • Data retrieval costs for accessing data inNearline,Coldline,andArchive storageclasses.
  • Network usage costs for data that you read across different regions.
  • Data processing charges. However, you aren't charged for API calls that aremade by BigQuery on your behalf.

• For information on Bigtable pricing, seePricing.

• For information on Drive pricing, seePricing.

What's next

• Learn how tocreate a Bigtable external table.
• Learn how tocreate a Cloud Storage external table.
• Learn how tocreate a Drive external table.
• Learn how toschedule and run data quality checks with Dataplex Universal Catalog.