[[["易于理解","easyToUnderstand","thumb-up"],["解决了我的问题","solvedMyProblem","thumb-up"],["其他","otherUp","thumb-up"]],[["很难理解","hardToUnderstand","thumb-down"],["信息或示例代码不正确","incorrectInformationOrSampleCode","thumb-down"],["没有我需要的信息/示例","missingTheInformationSamplesINeed","thumb-down"],["翻译问题","translationIssue","thumb-down"],["其他","otherDown","thumb-down"]],["最后更新时间 (UTC)：2025-08-17。"],[[["\u003cp\u003eExternal data sources allow querying data directly from outside of BigQuery storage, such as other Google Cloud databases, files in Cloud Storage, or other cloud products.\u003c/p\u003e\n"],["\u003cp\u003eBigQuery offers two primary mechanisms for querying external data: external tables, which store metadata in BigQuery but data externally, and federated queries, which send queries directly to databases like AlloyDB, Spanner, or Cloud SQL.\u003c/p\u003e\n"],["\u003cp\u003eExternal tables are categorized into BigLake tables, Object tables, and non-BigLake external tables, each with different functionalities and access delegation capabilities.\u003c/p\u003e\n"],["\u003cp\u003eBigLake tables provide access delegation through service accounts, enabling fine-grained security controls like row-level and column-level security, and support multi-cloud analysis with Amazon S3 and Azure Storage.\u003c/p\u003e\n"],["\u003cp\u003eFederated queries enable real-time querying of external databases using the \u003ccode\u003eEXTERNAL_QUERY\u003c/code\u003e function, converting results to GoogleSQL data types, and support connections through BigQuery Connection API.\u003c/p\u003e\n"]]],[],null,["# Introduction to external data sources\n=====================================\n\nThis page provides an overview of querying data stored outside of\nBigQuery.\n\nAn external data source is a data source that you can query directly from\nBigQuery, even though the data is not stored in\nBigQuery storage. For example, you might have data in a\ndifferent Google Cloud database, in files in Cloud Storage, or in a\ndifferent cloud product altogether that you would like to analyze in\nBigQuery, but that you aren't prepared to migrate.\n\nUse cases for external data sources include the following:\n\n- For extract-load-transform (ELT) workloads, loading and cleaning your data in one pass and writing the cleaned result into BigQuery storage, by using a `CREATE TABLE ... AS SELECT` query.\n- Joining BigQuery tables with frequently changing data from an external data source. By querying the external data source directly, you don't need to reload the data into BigQuery storage every time it changes.\n\nBigQuery has two different mechanisms for querying external\ndata: external tables and federated queries.\n\nExternal tables\n---------------\n\nExternal tables are similar to standard BigQuery tables, in\nthat these tables store their metadata and schema in BigQuery\nstorage. However, their data resides in an external source.\n\nExternal tables are contained inside a dataset, and you manage them in\nthe same way that you manage a standard\nBigQuery table. For example, you can\n[view the table's properties](/bigquery/docs/tables#get_information_about_tables),\n[set access controls](/bigquery/docs/table-access-controls), and so\nforth. You can query these tables and in most cases you can join them with\nother tables.\n\nThere are four kinds of external tables:\n\n- BigLake tables\n- BigQuery Omni tables\n- Object tables\n- Non-BigLake external tables\n\n### BigLake tables\n\nBigLake tables let you query structured data in\nexternal data stores with access delegation. Access delegation\ndecouples access to the BigLake table from access to\nthe underlying data store. An\n[external connection](/bigquery/docs/connections-api-intro)\nassociated with a service account is used to connect to the data store. Because\nthe service account handles retrieving data from the data store, you only have\nto grant users access to the BigLake table. This lets you enforce\nfine-grained security at the table level, including\n[row-level](/bigquery/docs/row-level-security-intro) and\n[column-level](/bigquery/docs/column-level-security-intro) security. For\nBigLake tables based on Cloud Storage, you can also use\n[dynamic data masking](/bigquery/docs/column-data-masking). To learn more about\nmulti-cloud analytic solutions using BigLake tables with\nAmazon S3 or Blob Storage data, see\n[BigQuery Omni](/bigquery/docs/omni-introduction).\n\nFor more information, see\n[Introduction to BigLake tables](/bigquery/docs/biglake-intro).\n\n### Object tables\n\nObject tables let you analyze unstructured data in\nCloud Storage. You can perform analysis with remote functions or\nperform inference by using BigQuery ML, and then join the results of\nthese operations with the rest of your structured data in BigQuery.\n\nLike BigLake tables, object tables use access delegation,\nwhich decouples access to the object table from access to the\nCloud Storage objects. An\n[external connection](/bigquery/docs/working-with-connections)\nassociated with a service account is used to connect to Cloud Storage,\nso you only have to grant users access to the object table. This lets you\nenforce [row-level](/bigquery/docs/row-level-security-intro) security and manage\nwhich objects users have access to.\n\nFor more information, see\n[Introduction to object tables](/bigquery/docs/object-table-introduction).\n\n### Non-BigLake external tables\n\nNon-BigLake external tables let you query structured data\nin external data stores. To query a non-BigLake external\ntable, you must have permissions to both the external table and the\nexternal data source. For example, to query a non-BigLake\nexternal table that uses a data source in Cloud Storage,\nyou must have the following permissions:\n\n- `bigquery.tables.getData`\n- `bigquery.jobs.create`\n- `storage.buckets.get`\n- `storage.objects.get`\n\nFor more information, see\n[Introduction to external tables](/bigquery/docs/external-tables).\n\nFederated queries\n-----------------\n\n\nFederated queries let you send a query statement to AlloyDB, Spanner, or Cloud SQL databases\nand get the result back as a temporary table. Federated queries use the\nBigQuery Connection API to establish a connection with AlloyDB, Spanner, or Cloud SQL.\nIn your query, you use the `EXTERNAL_QUERY` function to send a\nquery statement to the external database, using that database's SQL dialect.\nThe results are converted to GoogleSQL data types.\n\nFor more information, see\n[Introduction to federated queries](/bigquery/docs/federated-queries-intro).\n\nExternal data source feature comparison\n---------------------------------------\n\nThe following table compares the behavior of external data sources:\n\nWhat's next\n-----------\n\n- Learn more about [BigLake tables](/bigquery/docs/biglake-intro).\n- Learn more about [object tables](/bigquery/docs/object-table-introduction)\n- Learn more about [external tables](/bigquery/docs/external-tables).\n- Learn more about [federated queries](/bigquery/docs/federated-queries-intro).\n- Learn about [BigQuery pricing](/bigquery/pricing#on_demand_pricing)."]]
