Cloud SQL 페더레이션 쿼리

BigQuery의 제휴 쿼리를 사용하면 데이터를 복사하거나 이동하지 않고도 Cloud SQL에 있는 데이터를 실시간으로 쿼리할 수 있습니다. 쿼리 통합은 Cloud SQL에서 MySQL용 Cloud SQL 및 PostgreSQL용 Cloud SQL 인스턴스를 모두 지원합니다.

또는 Cloud Data Fusion 또는 Datastream을 사용하여 데이터를 BigQuery로 복제할 수 있습니다. Cloud Data Fusion 사용에 대한 자세한 내용은 MySQL에서 BigQuery로 데이터 복제를 참조하세요.

시작하기 전에

Cloud SQL의 데이터에서 페더레이션 쿼리를 실행하려면 다음을 수행합니다.

1. BigQuery 관리자가 Cloud SQL 연결을 만들고 연결을 공유했는지 확인합니다.
2. BigQuery Connection 서비스 에이전트에 Cloud SQL 클라이언트 (roles/cloudsql.client) 역할이 부여되었는지 확인합니다. 자세한 내용은 서비스 에이전트에 액세스 권한 부여를 참조하세요.
3. Cloud SQL 인스턴스에서 비공개 IP 주소를 사용하는 경우 인스턴스를 만들 때 비공개 경로가 사용 설정되었는지 확인합니다. 자세한 내용은 Cloud SQL에 연결 및 비공개 IP 구성을 참조하세요.

필요한 역할

Cloud SQL 인스턴스를 쿼리하는 데 필요한 권한을 얻으려면 관리자에게 프로젝트에 대한 BigQuery 연결 사용자 (roles/bigquery.connectionUser) IAM 역할을 부여해 달라고 요청하세요. 역할 부여에 대한 자세한 내용은 프로젝트, 폴더, 조직에 대한 액세스 관리를 참조하세요.

커스텀 역할이나 다른 사전 정의된 역할을 통해 필요한 권한을 얻을 수도 있습니다.

데이터 쿼리

GoogleSQL 쿼리에서 Cloud SQL로 페더레이션 쿼리를 보내려면 EXTERNAL_QUERY 함수를 사용합니다.

연결 식별자 형식

EXTERNAL_QUERY 함수의 첫 번째 인수는 연결 식별자입니다. 다음 형식 중 하나를 사용하여 연결 식별자를 지정할 수 있습니다.

• Shorts: [LOCATION.]CONNECTION_ID (예: 'us.my-connection' 또는 'my-connection')
• 정규화된 형식: projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID
• 친숙한 경로 형식: PROJECT_ID.LOCATION.CONNECTION_ID (예: 'my-project.us.my-connection')

여러 프로젝트에서 공유 뷰로 쿼리를 실행하는 경우 잘못된 프로젝트에 연결되지 않도록 항상 정규화된 연결 식별자를 사용하세요.

제휴 쿼리 실행

고객 테이블은 BigQuery에, 주문 테이블은 Cloud SQL에 저장되어 있는 상황에서 하나의 쿼리로 두 테이블을 조인하려는 경우가 있습니다. 다음 예시에서는 orders라는 Cloud SQL 테이블에 페더레이션 쿼리를 만들고 결과를 mydataset.customers라는 BigQuery 테이블과 조인합니다.

다음 옵션 중 하나를 선택하여 페더레이션 쿼리를 실행합니다.

콘솔

1. Google Cloud 콘솔에서 BigQuery 페이지로 이동합니다.

   BigQuery로 이동

2. 쿼리 편집기에 쿼리를 입력합니다.

   SELECT c.customer_id, c.name, rq.first_order_date
   FROM mydataset.customers AS c
   LEFT OUTER JOIN EXTERNAL_QUERY(
     'us.CONNECTION_ID',
     '''SELECT customer_id, MIN(order_date) AS first_order_date
     FROM orders
     GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
   GROUP BY c.customer_id, c.name, rq.first_order_date;
   

   CONNECTION_ID를 BigQuery 연결 ID로 바꿉니다.

3. 실행을 클릭합니다.

   쿼리 결과가 쿼리 결과 창에 표시됩니다.

bq

2. Google Cloud 콘솔에서 Cloud Shell을 활성화합니다.

   Cloud Shell 활성화

   Google Cloud 콘솔 하단에서 Cloud Shell 세션이 시작되고 명령줄 프롬프트가 표시됩니다. Cloud Shell은 Google Cloud CLI가 사전 설치된 셸 환경으로, 현재 프로젝트의 값이 이미 설정되어 있습니다. 세션이 초기화되는 데 몇 초 정도 걸릴 수 있습니다.

3. --use_legacy_sql=false 플래그와 함께 bq query 명령어를 실행합니다.

   bq query \
       --use_legacy_sql=false \
       'SELECT c.customer_id, c.name, rq.first_order_date
       FROM mydataset.customers AS c
       LEFT OUTER JOIN EXTERNAL_QUERY(
         "us.CONNECTION_ID",
         """SELECT customer_id, MIN(order_date) AS first_order_date
         FROM orders
         GROUP BY customer_id""") AS rq ON rq.customer_id = c.customer_id
       GROUP BY c.customer_id, c.name, rq.first_order_date;'
   

   CONNECTION_ID를 BigQuery 연결 ID로 바꿉니다.

예시 쿼리는 다음의 3개 부분으로 구성됩니다.

1. 운영 Cloud SQL 데이터베이스에서 외부 쿼리 SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id를 실행하여 EXTERNAL_QUERY() 함수를 통해 각 고객의 첫 주문 날짜를 구합니다.
2. customer_id를 기준으로 BigQuery에서 외부 쿼리 결과 테이블을 고객 테이블과 조인합니다.
3. 고객 정보와 첫 주문 날짜를 선택합니다.

Cloud SQL 테이블 스키마 보기

EXTERNAL_QUERY() 함수를 사용하여 INFORMATION_SCHEMA 뷰를 쿼리해 데이터베이스의 모든 테이블 나열 또는 테이블 스키마 표시와 같은 데이터베이스 메타데이터에 액세스할 수 있습니다. 다음 예시의 INFORMATION_SCHEMA 쿼리는 MySQL과 PostgreSQL에서 모두 작동합니다. MySQL information_schema 테이블 및 PostgreSQL information_schema 테이블에서 더 자세히 알아볼 수 있습니다.

-- List all tables in a database.
SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID",
"SELECT * FROM information_schema.tables;");

-- List all columns in a table.
SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID",
"SELECT * FROM information_schema.columns WHERE table_name='x';");

CONNECTION_ID를 BigQuery 연결 ID로 바꿉니다.

BigQuery 페더레이션 쿼리 추적

Cloud SQL에 대해 페더레이션 쿼리를 실행하면 BigQuery는 다음과 유사한 주석으로 쿼리에 주석을 추가합니다.

/* Federated query from BigQuery. Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID. */

MySQL 또는 PostgreSQL 데이터베이스에서 로그의 쿼리 사용량을 모니터링하는 경우 주석을 사용하면 BigQuery에서 시작되는 쿼리를 식별할 수 있습니다.

1. Google Cloud 콘솔에서 로그 탐색기 페이지로 이동합니다.

   로그 탐색기로 이동

2. 쿼리 탭에서 다음 쿼리를 입력합니다.

   resource.type="cloudsql_database"
   textPayload=~"Federated query from BigQuery"
   

3. 쿼리 실행을 클릭합니다.

   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 연결 속성을 보여줍니다.

속성 이름	값	설명
name	문자열	projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID 형식의 연결 리소스 이름입니다.
location	문자열	Cloud SQL 인스턴스 위치와 일치하거나 해당 관할 구역의 멀티 리전이어야 하는 연결의 위치입니다. 예를 들어 us-east4의 Cloud SQL 인스턴스는 US를 사용할 수 있는 반면 europe-north1의 Cloud SQL 인스턴스는 EU를 사용할 수 있습니다. 이 위치에서 실행되는 BigQuery 쿼리만 이 연결을 사용할 수 있습니다.
friendlyName	문자열	연결의 사용자 친화적인 표시 이름입니다.
description	문자열	연결에 대한 설명입니다.
cloudSql.type	문자열	POSTGRES 또는 MYSQL일 수 있습니다.
cloudSql.instanceId	문자열	Cloud SQL 인스턴스의 이름이며 일반적으로 PROJECT_ID:LOCATION:INSTANCE_ID 형식입니다. Cloud SQL 인스턴스 페이지에서 인스턴스 ID를 찾을 수 있습니다.
cloudSql.database	문자열	연결할 Cloud SQL 데이터베이스입니다.
cloudSql.serviceAccountId	문자열	Cloud SQL 데이터베이스에 액세스하도록 구성된 서비스 계정입니다.

다음 표에서는 Cloud SQL 인스턴스 사용자 인증 정보에 대한 속성을 보여줍니다.

속성 이름	값	설명
username	문자열	데이터베이스 사용자 이름입니다.
password	문자열	데이터베이스 비밀번호입니다.

쿼리 성능 및 권장사항

페더레이션 쿼리의 성능을 최적화하고 Cloud SQL 인스턴스에 불필요한 부하를 가하지 않으려면 다음 권장사항을 고려하세요.

• 외부 쿼리에서 조기 필터링: 외부 BigQuery 쿼리에서 필터링하는 대신 EXTERNAL_QUERY SQL 문 내에서 WHERE 필터 및 GROUP BY 집계를 적용합니다. Cloud SQL에서 필터를 실행하면 네트워크를 통해 BigQuery로 전송되는 데이터 양이 줄어듭니다.
• 열 프루닝: SELECT *를 사용하는 대신 EXTERNAL_QUERY 문에 필요한 특정 열만 선택합니다. 이렇게 하면 네트워크 전송량과 쿼리 실행 시간이 줄어듭니다.
• 읽기 복제본 사용: 기본 프로덕션 데이터베이스에서 분석 쿼리 워크로드를 격리하려면 Cloud SQL 읽기 복제본을 만들고 복제본을 쿼리하도록 BigQuery 연결을 구성합니다.
• 데이터 세트 및 인스턴스 공동 배치: 지연 시간을 최소화하려면 BigQuery 데이터 세트와 연결을 Cloud SQL 인스턴스와 동일한 리전 또는 해당 멀티 리전 (예: US 또는 EU)에 배치합니다. 자세한 내용은 지원되는 리전을 참조하세요.

문제 해결

이 섹션은 페더레이션 쿼리를 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.

해결 방법

이 문제를 해결하려면 다음 기본 요건을 확인하세요.

1. Cloud SQL에 대한 연결을 생성할 때 유효한 사용자 인증 정보가 사용되었고 모든 설정 단계를 따랐는지 확인합니다.

2. BigQuery Connection 서비스 에이전트에 Cloud SQL 클라이언트 (roles/cloudsql.client) 역할이 부여되었는지 확인합니다. 서비스 에이전트 이메일은 다음과 같은 형식입니다.

   service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.

   자세한 안내는 서비스 에이전트에 액세스 권한 부여를 참조하세요.

3. Cloud SQL 인스턴스에서 비공개 IP 주소를 사용하는 경우 Cloud SQL 인스턴스를 만들 때 비공개 경로를 사용 설정했는지 확인합니다. 비공개 경로를 사용 설정하면 BigQuery가 Cloud SQL의 데이터에 액세스하고 비공개 연결을 통해 쿼리를 실행할 수 있습니다.

인증 플러그인으로 인해 MySQL 데이터베이스에 연결할 수 없음

MySQL 인스턴스에 대해 페더레이션 쿼리를 실행하면 다음 오류가 발생할 수 있습니다.

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2059): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at [1:15]

해결 방법

이 오류는 페더레이션 쿼리에 지원되지 않는 caching_sha2_password 인증으로 구성된 데이터베이스 사용자 계정에서 발생합니다.

Cloud SQL 인스턴스에서 MySQL 버전 8.0 이하를 실행하는 경우 BigQuery에서 사용하는 사용자 계정의 인증 플러그인을 변경할 수 있습니다. Cloud SQL 인스턴스에서 다음 명령어를 실행합니다.

ALTER USER 'USERNAME'@'%' IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

다음을 바꿉니다.

• USERNAME: BigQuery가 MySQL용 Cloud SQL 인스턴스에 연결하는 데 사용하는 데이터베이스 사용자 계정입니다.
• PASSWORD: 데이터베이스 사용자 계정의 비밀번호입니다.

Cloud SQL 인스턴스에서 MySQL 버전 8.4를 실행하는 경우 mysql_native_password 플러그인이 지원 중단됩니다. 프로덕션 워크로드의 경우 기존 데이터베이스를 8.4에서 8.0으로 다운그레이드하지 않는 것이 좋습니다.

인스턴스를 이전 버전에서 MySQL 8.4로 업그레이드해도 업그레이드 전에 사용자 인증 플러그인을 변경하지 않는 한 기존 데이터베이스 연결에 영향을 미치지 않습니다. mysql_native_password로 생성된 사용자만 페더레이션 쿼리에 사용할 수 있으므로 MySQL 8.4로 전송되는 쿼리는 업그레이드 전에 생성된 사용자 계정을 사용해야 합니다.

제한사항

Cloud SQL의 페더레이션 쿼리에는 다음과 같은 제한사항이 적용됩니다.

• 읽기 전용 쿼리: 페더레이션 쿼리는 읽기 전용입니다. 외부 쿼리는 SELECT 문이어야 합니다. 데이터 정의 언어 (DDL) 및 데이터 조작 언어 (DML) 문은 지원되지 않습니다.
• 쿼리 성능: 페더레이션 쿼리는 BigQuery 스토리지를 쿼리하는 것만큼 빠르지 않습니다. BigQuery는 Cloud SQL이 외부 쿼리를 실행하고 네트워크를 통해 결과 집합을 전송할 때까지 기다려야 합니다.
• 리전 제약 조건: BigQuery 쿼리 처리 위치는 Cloud SQL 인스턴스 위치와 일치하거나 호환되어야 합니다. 자세한 내용은 지원되는 지역을 참조하세요.
• 지원되지 않는 데이터 유형: 외부 쿼리가 BigQuery에서 지원되지 않는 데이터 유형을 반환하면 쿼리가 실패합니다. 외부 쿼리 내에서 지원되지 않는 데이터 유형을 지원되는 데이터 유형으로 변환할 수 있습니다. 자세한 내용은 데이터 유형 매핑을 참조하세요.
• 최대 연결 수: 페더레이션 쿼리는 최대 10개의 고유한 연결을 참조할 수 있습니다.
• 인증 플러그인 제한사항: MySQL caching_sha2_password 플러그인은 페더레이션 쿼리에 지원되지 않습니다. 자세한 내용은 문제 해결을 참조하세요.

다음 단계

• 통합 쿼리 알아보기
• MySQL에서 BigQuery로의 데이터 유형 매핑 알아보기
• PostgreSQL에서 BigQuery로의 데이터 유형 매핑 알아보기
• 지원되지 않는 데이터 유형 알아보기