Cloud SQL 인스턴스를 쿼리하는 데 필요한 권한을 얻으려면 관리자에게 프로젝트에 대한 BigQuery 연결 사용자(roles/bigquery.connectionUser) IAM 역할을 부여해 달라고 요청하세요.
역할 부여에 대한 자세한 내용은 프로젝트, 폴더, 조직에 대한 액세스 관리를 참조하세요.
고객 테이블은 BigQuery에, 판매 테이블은 Cloud SQL에 저장되어 있는 상황에서 하나의 쿼리로 두 테이블을 조인하려는 경우가 있습니다. 다음 예시에서는 orders라는 Cloud SQL 테이블에 통합 쿼리를 만들고 결과를 mydataset.customers라는 BigQuery 테이블과 조인합니다.
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() 함수를 통해 각 고객의 첫 주문 날짜를 구합니다.
customer_id를 기준으로 BigQuery에서 외부 쿼리 결과 테이블을 고객 테이블과 조인합니다.
고객 정보와 첫 주문 날짜를 선택합니다.
Cloud SQL 테이블 스키마 보기
information_schema 테이블을 쿼리하기 위해 EXTERNAL_QUERY() 함수를 사용해 데이터베이스 메타데이터에 액세스하여 데이터베이스의 모든 테이블을 나열하거나 테이블 스키마를 볼 수 있습니다. 다음 예시의 information_schema 쿼리는 MySQL과 PostgreSQL에서 모두 작동합니다. MySQL information_schema 테이블 및 PostgreSQL information_schema 테이블에서 더 자세히 알아볼 수 있습니다.
-- List all tables in a database.SELECT*FROMEXTERNAL_QUERY("connection_id","select * from information_schema.tables;");
-- List all columns in a table.SELECT*FROMEXTERNAL_QUERY("connection_id","select * from information_schema.columns where table_name='x';");
연결 세부정보
다음 표에서는 Cloud SQL 연결 속성을 보여줍니다.
속성 이름
값
설명
name
문자열
연결 리소스의 이름입니다(형식: project_id.location_id.connection_id).
location
문자열
Cloud SQL 인스턴스 위치와 일치하거나 해당 관할 구역의 멀티 리전이어야 하는 연결의 위치입니다. 예를 들어 us-east4의 Cloud SQL 인스턴스는 US를 사용할 수 있는 반면 europe-north1의 Cloud SQL 인스턴스는 EU를 사용할 수 있습니다. 이 위치에서 실행되는 BigQuery 쿼리만 이 연결을 사용할 수 있습니다.
resource.type="cloudsql_database"
textPayload=~"Federated query from BigQuery"
쿼리 실행을 클릭합니다.
BigQuery 통합 쿼리에 사용할 수 있는 레코드가 있는 경우 쿼리 결과에 다음과 유사한 레코드 목록이 표시됩니다.
YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT 1 FROM (SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/
YYYY-MM-DD hh:mm:ss.millis UTC [3210532]: [2-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT "company_id", "company type_id" FROM
(SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/
문제 해결
이 섹션은 통합 쿼리를 Cloud SQL로 전송할 때 발생할 수 있는 문제를 해결하는 데 도움이 됩니다.
문제: 데이터베이스 서버에 연결하지 못했습니다. MySQL 데이터베이스를 쿼리하는 경우 다음 오류가 발생할 수 있습니다.
Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query.
또는 PostgreSQL 데이터베이스를 쿼리하는 경우 다음 오류가 발생할 수 있습니다.
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.
해결 방법: 유효한 사용자 인증 정보가 사용되었고 모든 기본 요건을 따라 Cloud SQL에 대한 연결을 생성했는지 확인합니다.
Cloud SQL에 연결할 때 자동으로 생성되는 서비스 계정에 Cloud SQL 클라이언트(roles/cloudsql.client) 역할이 있는지 확인합니다. 서비스 계정의 형식은 다음과 같습니다.
service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com
자세한 안내는 서비스 계정에 액세스 권한 부여를 참조하세요.
[[["이해하기 쉬움","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-26(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)."]]
