SELECTc.customer_id,c.name,rq.first_order_dateFROMmydataset.customersAScLEFTOUTERJOINEXTERNAL_QUERY('us.connection_id','''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;
このサンプルクエリは 3 つの部分から構成されています。
稼働中の PostgreSQL データベースで外部クエリ SELECT customer_id, MIN(order_date) AS
first_order_date FROM orders GROUP BY customer_id を実行し、EXTERNAL_QUERY() 関数によって各顧客の最初の注文日を取得します。
Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query.
Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
[[["わかりやすい","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-08-21 UTC。"],[[["\u003cp\u003eCloud SQL federated queries allow data analysts to query data in Cloud SQL directly from BigQuery in real time, without the need to copy or move the data, supporting both MySQL and PostgreSQL instances.\u003c/p\u003e\n"],["\u003cp\u003eTo perform a federated query, you must use the \u003ccode\u003eEXTERNAL_QUERY\u003c/code\u003e function within a GoogleSQL query, and a Cloud SQL connection must have been created and shared with you by your administrator, while ensuring you have the required BigQuery Connection User IAM role.\u003c/p\u003e\n"],["\u003cp\u003eYou can join data from a Cloud SQL table with a BigQuery table in a single query using the \u003ccode\u003eEXTERNAL_QUERY\u003c/code\u003e function, as demonstrated by an example joining a Cloud SQL \u003ccode\u003eorders\u003c/code\u003e table with a BigQuery \u003ccode\u003ecustomers\u003c/code\u003e table.\u003c/p\u003e\n"],["\u003cp\u003eThe \u003ccode\u003eEXTERNAL_QUERY()\u003c/code\u003e function can also be used to access Cloud SQL database metadata, such as listing all tables or showing a specific table's schema by querying the \u003ccode\u003einformation_schema\u003c/code\u003e tables.\u003c/p\u003e\n"],["\u003cp\u003eWhen a federated query is run, BigQuery annotates the query with a comment including the Project ID and BigQuery Job ID, which can be used to identify these queries in the database logs using the Logs Explorer.\u003c/p\u003e\n"]]],[],null,["# Cloud SQL federated queries\n===========================\n\nAs a data analyst, you can query data in Cloud SQL from BigQuery\nusing [federated queries](/bigquery/docs/federated-queries-intro).\n\nBigQuery Cloud SQL federation enables BigQuery\nto query data residing in Cloud SQL in real time, without copying or\nmoving data. Query federation supports both MySQL (2nd generation) and PostgreSQL\ninstances in Cloud SQL.\n\nAlternatively, to replicate data into\nBigQuery, you can also use Cloud Data Fusion or\n[Datastream](/datastream/docs/overview). For more about using\nCloud Data Fusion, see [Replicating data from MySQL to\nBigQuery](/data-fusion/docs/tutorials/replicating-data/mysql-to-bigquery).\n\nBefore you begin\n----------------\n\n- Ensure that your BigQuery administrator has created a [Cloud SQL connection](/bigquery/docs/connect-to-sql#create-sql-connection) and [shared](/bigquery/docs/connect-to-sql#share_connections) it with you.\n-\n\n To get the permissions that\n you need to query a Cloud SQL instance,\n\n ask your administrator to grant you the\n\n\n [BigQuery Connection User](/iam/docs/roles-permissions/bigquery#bigquery.connectionUser) (`roles/bigquery.connectionUser`)\n IAM role on your project.\n\n\n For more information about granting roles, see [Manage access to projects, folders, and organizations](/iam/docs/granting-changing-revoking-access).\n\n\n You might also be able to get\n the required permissions through [custom\n roles](/iam/docs/creating-custom-roles) or other [predefined\n roles](/iam/docs/roles-overview#predefined).\n\nQuery data\n----------\n\nTo send a federated query to Cloud SQL from a\nGoogleSQL query, use the\n[`EXTERNAL_QUERY` function](/bigquery/docs/reference/standard-sql/federated_query_functions#external_query).\n\nSuppose that you store a customer table in BigQuery, while\nstoring a sales table in Cloud SQL, and want to join the two tables in\na single query. The following example makes a federated query to a\nCloud SQL table named `orders` and joins the results with a\nBigQuery table named `mydataset.customers`. \n\n SELECT c.customer_id, c.name, rq.first_order_date\n FROM mydataset.customers AS c\n LEFT OUTER JOIN EXTERNAL_QUERY(\n 'us.connection_id',\n '''SELECT customer_id, MIN(order_date) AS first_order_date\n FROM orders\n GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id\n GROUP BY c.customer_id, c.name, rq.first_order_date;\n\nThe example query includes 3 parts:\n\n1. Run the external query `SELECT customer_id, MIN(order_date) AS\n first_order_date FROM orders GROUP BY customer_id` in the operational PostgreSQL database to get the first order date for each customer through the `EXTERNAL_QUERY()` function.\n2. Join the external query result table with the customers table in BigQuery by `customer_id`.\n3. Select customer information and first order date.\n\nView a Cloud SQL table schema\n-----------------------------\n\nYou can use the `EXTERNAL_QUERY()` function to query information_schema tables\nto access database metadata, such as list all tables in the database or show\ntable schema. The following example information_schema queries work in both\nMySQL and PostgreSQL. You can learn more from\n[MySQL information_schema tables](https://dev.mysql.com/doc/refman/8.0/en/information-schema-introduction.html)\nand\n[PostgreSQL information_schema tables](https://www.postgresql.org/docs/9.1/information-schema.html). \n\n -- List all tables in a database.\n SELECT * FROM EXTERNAL_QUERY(\"connection_id\",\n \"select * from information_schema.tables;\");\n\n -- List all columns in a table.\n SELECT * FROM EXTERNAL_QUERY(\"connection_id\",\n \"select * from information_schema.columns where table_name='x';\");\n\nConnection details\n------------------\n\nThe following table shows the Cloud SQL connection properties:\n\nThe following table shows the properties for the Cloud SQL instance credential:\n\nTrack BigQuery federated queries\n--------------------------------\n\nWhen you run a federated query against Cloud SQL,\nBigQuery annotates the query with a comment similar to the following: \n\n```\n/* Federated query from BigQuery. Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID. */\n```\n\nIf you are monitoring logs for query usage on a MySQL or PostgreSQL database,\nthe following annotation can help you identify queries coming from BigQuery.\n\n1. Go to the **Logs Explorer** page.\n\n [Go to the Logs Explorer](https://console.cloud.google.com/logs/query)\n2. In the **Query** tab, enter the following query:\n\n resource.type=\"cloudsql_database\"\n textPayload=~\"Federated query from BigQuery\"\n\n3. Click **Run query**.\n\n If there are records available for BigQuery federated queries,\n a list of records similar to the following appears in **Query results**: \n\n ```\n YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]\n db=DATABASE, user=USER_ACCOUNT\n STATEMENT: SELECT 1 FROM (SELECT FROM company_name_table) t;\n /* Federated query from BigQuery.\n Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID\n */\n\n YYYY-MM-DD hh:mm:ss.millis UTC [3210532]: [2-1]\n db=DATABASE, user=USER_ACCOUNT\n STATEMENT: SELECT \"company_id\", \"company type_id\" FROM\n (SELECT FROM company_name_table) t;\n /* Federated query from BigQuery.\n Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID\n */\n ```\n\nTroubleshooting\n---------------\n\nThis section helps you troubleshoot issues you might encounter when sending\na federated query to Cloud SQL.\n\n**Issue:** Failed to connect to database server. If you are querying a MySQL\ndatabase, you might encounter the following error:\n\n`Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query.`\n\nAlternatively, if you are querying a PostgreSQL database, you might encounter\nthe following error:\n\n`Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.`\n: **Resolution:** Ensure that valid credentials were used and all prerequisites\n were followed to create the [connection for Cloud SQL](/bigquery/docs/connect-to-sql).\n Check if the\n service account that is automatically created\n when a connection to Cloud SQL is\n created has the Cloud SQL Client (`roles/cloudsql.client`) role. The service\n account is of the following format:\n `service-`\u003cvar translate=\"no\"\u003ePROJECT_NUMBER\u003c/var\u003e`@gcp-sa-bigqueryconnection.iam.gserviceaccount.com`.\n For detailed instructions, see\n [Grant access to the service account](/bigquery/docs/connect-to-sql#access-sql).\n\nWhat's next\n-----------\n\n- Learn about [federated queries](/bigquery/docs/federated-queries-intro).\n- Learn about [MySQL to BigQuery data type mapping](/bigquery/docs/reference/standard-sql/federated_query_functions#mysql_mapping).\n- Learn about [PostgreSQL to BigQuery data type mapping](/bigquery/docs/reference/standard-sql/federated_query_functions#postgresql_mapping).\n- Learn about [unsupported data types](/bigquery/docs/reference/standard-sql/federated_query_functions#unsupported_data_types)."]]
