SELECTc.customer_id,c.name,rq.first_order_dateFROMmydataset.customersAScLEFTOUTERJOINEXTERNAL_QUERY('my-project.us.example-db','''SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id''')ASrqONrq.customer_id=c.customer_idGROUPBYc.customer_id,c.name,rq.first_order_date;
BigQuery で開始し、Data Boost クエリを実行する
BigQuery から Spanner データベースへの外部データ接続を作成し、その接続を使用して BigQuery から連携 Data Boost クエリを実行するには、次のいずれかのオプションを選択します。
[[["わかりやすい","easyToUnderstand","thumb-up"],["問題の解決に役立った","solvedMyProblem","thumb-up"],["その他","otherUp","thumb-up"]],[["わかりにくい","hardToUnderstand","thumb-down"],["情報またはサンプルコードが不正確","incorrectInformationOrSampleCode","thumb-down"],["必要な情報 / サンプルがない","missingTheInformationSamplesINeed","thumb-down"],["翻訳に関する問題","translationIssue","thumb-down"],["その他","otherDown","thumb-down"]],["最終更新日 2025-09-05 UTC。"],[],[],null,["# Run federated queries with Data Boost\n\nThis page explains how to use Spanner Data Boost when you run federated queries\nfrom BigQuery to a Spanner database. With\nData Boost, federated queries run with minimal impact to existing\nworkloads on the provisioned Spanner instance. The\nData Boost queries from BigQuery to a\nSpanner database can join BigQuery data with\nSpanner data.\n\nSpanner federation lets BigQuery query data\nresiding in Spanner in real time, without copying or moving data.\nTo learn more about Spanner federated queries, see [Spanner federated queries](/bigquery/docs/spanner-federated-queries).\nTo learn about Data Boost, see [Data Boost\noverview](/spanner/docs/databoost/databoost-overview).\n\nBefore you begin\n----------------\n\nBefore you can run federated queries with Data Boost, you need to\ncomplete the following tasks:\n\n- [Create a Spanner instance and\n database](#create-instance-database).\n\n- [Enable the BigQuery connection API](#enable-bqconnect-API).\n\n- [Grant the required IAM permissions](#grant-databoost-perms).\n\n### Create a Spanner instance and database\n\nIf you don't have a Spanner instance and database, follow the\nsteps in [Create and query a database using the Google Cloud console](/spanner/docs/create-query-database-console) to create them.\n\n### Enable the BigQuery connection API\n\nThe BigQuery connection API lets you manage\nBigQuery connections to external data sources such as a\nSpanner database.\n\n-\n\n\n Enable the BigQuery connection API.\n\n\n [Enable the API](https://console.cloud.google.com/flows/enableapi?apiid=bigqueryconnection.googleapis.com)\n\n\u003cbr /\u003e\n\nFor more information, see [BigQuery connection\nAPI](/bigquery/docs/reference/bigqueryconnection/rest) in the\nBigQuery documentation.\n\n### Grant IAM permissions for Data Boost to principals\n\nA principal must be granted the following permissions to run federated queries\nwith Data Boost:\n\n- `spanner.instances.get` - lets you get the configuration of an instance.\n- `spanner.databases.useDataBoost` - lets you use the [Spanner Data Boost](/spanner/docs/databoost/databoost-overview) compute resources to process [partitioned queries](/spanner/docs/reads#read_data_in_parallel).\n\nFor more information about Spanner permissions, see [Identity and Access Management\n(IAM) permissions](/spanner/docs/iam#permissions).\n\nTo grant these required permissions, we recommend that you use the `Cloud\nSpanner Database Reader With DataBoost`\n(`roles/spanner.databaseReaderWithDataBoost`) IAM role. You can\nadd that role to any principal that needs to run federated queries\nwith Data Boost. To learn more about predefined roles in\nSpanner, see [Predefined roles](/spanner/docs/iam#roles). To learn\nhow to create a custom IAM role, see [Create a custom\nrole](/iam/docs/creating-custom-roles#creating).\n\nRun a federated Data Boost query\n--------------------------------\n\nTo run a Data Boost query from BigQuery to an\nexternal source, you need a BigQuery connection to the external\nsource and the ID of the connection. When you run a federated\nSpanner query with Data Boost, the external source\nis a Spanner database. After you create your connection ID, it's\nused by BigQuery to run a Data Boost\nquery of a Spanner database.\n\nUse one of the following options to create a BigQuery connection\nID, and then use the connection ID to run a Data Boost query from\nBigQuery:\n\n1. [Start in Spanner](#run-query-from-spanner) - Create the\n BigQuery external connection ID in the Spanner\n console. After your connection ID is created in the Spanner\n console, you're redirected to the BigQuery console to run a\n federated Data Boost query to a Spanner\n database.\n\n2. [Start in BigQuery](#run-query-from-bigquery) - Create the\n Data Boost external connection ID in the\n BigQuery console or using the `bq` command-line tool. After\n you create the connection ID, you stay in the BigQuery\n console to run a federated Data Boost query to a\n Spanner database.\n\n### Start in Spanner to run a Data Boost query\n\nTo run a federated Data Boost query starting in the\nSpanner Studio, do the following:\n\n1. Go to the Spanner **Instances** page in the\n Google Cloud console.\n\n [Go to the Instances page](https://console.cloud.google.com/spanner/instances)\n\n The console shows a list of your Spanner\n instances.\n2. Select a Spanner instance, and then select a database.\n\n3. On the **Database overview** page, in the navigation menu, click\n **Spanner Studio**.\n\n4. Click **View in BiqQuery**.\n\n5. In the **View in BigQuery** dialog, enter a connection ID.\n\n The connection ID is used to create a new BigQuery external\n connection to your Spanner database. You reference your\n external connection using the following pattern: \n\n \u003cvar translate=\"no\"\u003ePROJECT-ID\u003c/var\u003e.\u003cvar translate=\"no\"\u003eLOCATION\u003c/var\u003e.\u003cvar translate=\"no\"\u003eCONNECTION-ID\u003c/var\u003e\n\n An error occurs if the ID already exists.\n6. Fill in the rest of the dialog and do the following:\n\n - Select **Read data in parallel**.\n - Select **Use Spanner Data Boost**.\n7. Click **View in BigQuery**.\n\n BigQuery Studio opens with the following query: \n\n SELECT * FROM EXTERNAL_QUERY(\"\u003cvar translate=\"no\"\u003ePROJECT-ID\u003c/var\u003e.\u003cvar translate=\"no\"\u003eLOCATION\u003c/var\u003e.\u003cvar translate=\"no\"\u003eCONNECTION-ID\u003c/var\u003e\", \"SELECT * FROM INFORMATION_SCHEMA.TABLES;\");\n\n You can replace this with your federated query. For example, you might make\n a query that's similar to the following example. This example makes a\n federated query from a table named `orders` in a Spanner\n database and joins the results with a BigQuery table named\n `mydataset.customers`. \n\n ```sql\n SELECT c.customer_id, c.name, rq.first_order_date\n FROM mydataset.customers AS c\n LEFT OUTER JOIN EXTERNAL_QUERY(\n 'my-project.us.example-db',\n '''SELECT customer_id, MIN(order_date) AS first_order_date\n FROM orders\n GROUP BY customer_id''') AS rq\n ON rq.customer_id = c.customer_id\n GROUP BY c.customer_id, c.name, rq.first_order_date;\n ```\n\n### Start in BigQuery to run a Data Boost query\n\nTo create an external data connection from BigQuery to a\nSpanner database and use that connection to run a federated\nData Boost query from BigQuery, select one of the\nfollowing options: \n\n### Console\n\n1. Go to [Create Spanner connections](/bigquery/docs/connect-to-spanner#create-spanner-connection)\n in the BigQuery documentation and follow the instructions on\n the **Console** tab.\n\n2. In the **External data source** pane, do the following:\n\n - Select **Read data in parallel**.\n - Select **Use Spanner Data Boost**.\n\n### bq\n\n1. Go to [Create Spanner connections](/bigquery/docs/connect-to-spanner#create-spanner-connection)\n in the BigQuery documentation and follow instructions on the\n **bq**\\* tab.\n\n2. Set the following connection properties to `true`:\n\n - `useParallelism`\n - `useDataBoost`\n\nThe following example uses the [`bq\nmk`](/bigquery/docs/reference/bq-cli-reference#bq_mk) command to create a\nnew connection named `my_connection` with the two required properties for\nData Boost: \n\n bq mk --connection --connection_type='CLOUD_SPANNER' --location='us' \\\n --properties='{\"database\":\"projects/my-project/instances/my-instance/databases/my-database\", \"useParallelism\":true, \"useDataBoost\": true}' my_connection\n\nWhat's next\n-----------\n\n- Learn about Data Boost in [Data Boost overview](/spanner/docs/databoost/databoost-overview)\n- [Use Data Boost in your applications](/spanner/docs/databoost/databoost-applications)\n- [Monitor Data Boost usage](/spanner/docs/databoost/databoost-monitor)\n- [Monitor and manage Data Boost quota usage](/spanner/docs/databoost/databoost-quotas)"]]
