Introduction to external data sources

This page provides an overview of querying data stored outside ofBigQuery.

An external data source is a data source that you can query directly fromBigQuery, even though the data is not stored inBigQuery storage. For example, you might have data in adifferent Google Cloud database, in files in Cloud Storage, or in adifferent cloud product altogether that you would like to analyze inBigQuery, but that you aren't prepared to migrate.

Use cases for external data sources include the following:

• For extract-load-transform (ELT) workloads, loading and cleaning your datain one pass and writing the cleaned result into BigQuerystorage, by using aCREATE TABLE ... AS SELECT query.
• Joining BigQuery tables with frequently changing data froman external data source. By querying the external data source directly, youdon't need to reload the data into BigQuery storage everytime it changes.

BigQuery has two different mechanisms for querying externaldata: external tables and federated queries.

External tables

External tables are similar to standard BigQuery tables, inthat these tables store their metadata and schema in BigQuerystorage. However, their data resides in an external source.

External tables are contained inside a dataset, and you manage them inthe same way that you manage a standardBigQuery table. For example, you canview the table's properties,set access controls, and soforth. You can query these tables and in most cases you can join them withother tables.

There are four kinds of external tables:

• BigLake tables
• BigQuery Omni tables
• Object tables
• Non-BigLake external tables

BigLake tables

BigLake tables let you query structured data inexternal data stores with access delegation. Access delegationdecouples access to the BigLake table from access tothe underlying data store. Anexternal connectionassociated with a service account is used to connect to the data store. Becausethe service account handles retrieving data from the data store, you only haveto grant users access to the BigLake table. This lets you enforcefine-grained security at the table level, includingrow-level andcolumn-level security. ForBigLake tables based on Cloud Storage, you can also usedynamic data masking. To learn more aboutmulti-cloud analytic solutions using BigLake tables withAmazon S3 or Blob Storage data, seeBigQuery Omni.

For more information, seeIntroduction to BigLake tables.

Object tables

Object tables let you analyze unstructured data inCloud Storage. You can perform analysis with remote functions orperform inference by using BigQuery ML, and then join the results ofthese operations with the rest of your structured data in BigQuery.

Like BigLake tables, object tables use access delegation,which decouples access to the object table from access to theCloud Storage objects. Anexternal connectionassociated with a service account is used to connect to Cloud Storage,so you only have to grant users access to the object table. This lets youenforcerow-level security and managewhich objects users have access to.

For more information, seeIntroduction to object tables.

Non-BigLake external tables

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

For more information, seeIntroduction to external tables.

Federated queries

Federated queries let you send a query statement to AlloyDB, Spanner, or Cloud SQL databases and get the result back as a temporary table. Federated queries use the BigQuery Connection API to establish a connection with AlloyDB, Spanner, or Cloud SQL. In your query, you use theEXTERNAL_QUERY function to send a query statement to the external database, using that database's SQL dialect. The results are converted to GoogleSQL data types.

For more information, seeIntroduction to federated queries.

External data source feature comparison

The following table compares the behavior of external data sources:

What's next

• Learn more aboutBigLake tables.
• Learn more aboutobject tables
• Learn more aboutexternal tables.
• Learn more aboutfederated queries.
• Learn aboutBigQuery pricing.