PostgreSQL 페더레이션 쿼리 실행

데이터 분석가는 데이터를 복사하거나 이동하지 않고도 BigQuery에서 직접 PostgreSQL에 있는 관계형 데이터를 실시간으로 쿼리할 수 있습니다.

BigQuery PostgreSQL 통합은 BigQuery Connection API를 사용하여 PostgreSQL 데이터베이스 엔진에 연결합니다. 그런 다음 EXTERNAL_QUERY 함수를 사용하여 SQL 쿼리를 PostgreSQL로 직접 전송하여 실행할 수 있으며 결과가 직접 다시 스트리밍됩니다.

제한사항

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

  • EXTERNAL_QUERY 함수를 통해서는 읽기 전용 쿼리만 실행할 수 있습니다. 데이터 정의 언어(DDL) 및 데이터 조작 언어(DML) 문은 지원되지 않습니다.
  • 제휴는 EXTERNAL_QUERY 함수를 통해서만 액세스할 수 있습니다. PostgreSQL 테이블에 직접 매핑되는 BigQuery 외부 테이블 또는 영구 데이터 세트를 만드는 것은 지원되지 않습니다.
  • 페더레이션 쿼리 지연 시간은 PostgreSQL에서 직접 쿼리를 실행하는 것보다 눈에 띄게 길 수 있습니다.
  • 기본 연결 서비스는 비활성 연결을 최대 절전 모드로 전환할 수 있습니다. 비활성 기간 후 PostgreSQL 연결을 사용하는 첫 번째 쿼리는 커넥터가 초기화되는 동안 실행하는 데 1분 이상 걸릴 수 있습니다.
  • DNS 피어링은 지원되지 않습니다. VPC 네트워크 내에서 IP 주소 또는 완전히 확인 가능한 호스트 이름을 사용하여 연결해야 합니다.
  • PostgreSQL SQL 쿼리는 함수 결과 또는 표현식이 포함된 모든 열에 대해 명시적 열 별칭을 지정해야 합니다(예: SELECT COUNT(*) AS total_count). 별칭이 지정되지 않은 표현식은 BigQuery 테이블 스키마 정의에서 유효하지 않은 특수문자가 포함된 열 이름을 생성합니다.
  • 쿼리 프로젝트에서 Compute Engine API 사용이 VPC 서비스 제어에 의해 제한되면 연결에서 네트워크 연결을 사용하는 경우 페더레이션 쿼리가 실패합니다. 적절한 VPC 서비스 제어 인그레스 및 이그레스 규칙이 구성되어 있는지 확인합니다.

  • PostgreSQL에는 추가 SQL 푸시다운이 지원되지 않습니다. 외부 BigQuery 쿼리의 필터 및 집계는 EXTERNAL_QUERY 함수 내의 내부 쿼리로 푸시다운되지 않습니다. 네트워크를 통해 전송되는 행과 열을 제한하려면 내부 PostgreSQL SQL 문 내에 조건자 및 열 프로젝션을 직접 지정해야 합니다.

  • PostgreSQL 데이터베이스 사용자 이름 및 비밀번호 인증만 지원됩니다. 서드 파티 ID 공급업체, Kerberos, 클라우드 제공업체 IAM 인증은 지원되지 않습니다.

  • 쿼리가 16비트 정수 열(int2, smallint, smallserial) 또는 단정밀도 부동 소수점 열(float4, real)을 선택하는 경우 CREATE TABLE ... AS SELECT(CTAS) 문이 Unsupported type INT32 또는 Unsupported type FLOAT 오류와 함께 실패합니다. 쿼리 결과를 새 BigQuery 테이블에 저장하려면 쿼리에서 정수 열을 INT64로, 부동 소수점 열을 FLOAT64로 명시적으로 변환합니다.

    CREATE TABLE mydataset.mytable AS
SELECT
CAST(small_col AS INT64) AS small_col,
CAST(real_col AS FLOAT64) AS real_col,
...
FROM EXTERNAL_QUERY(...);

    BigQuery는 대상 테이블의 INT64 및 FLOAT64 열과 일치하도록 INT32 및 FLOAT 값을 자동으로 확장하므로 수동 변환 없이 INSERT INTO <table> SELECT ...를 사용하여 기존 테이블에 삽입할 수 있습니다.

  • time [ without time zone ] 및 time with time zone(timetz) 모두 1초 미만의 소수(마이크로초) 값을 정수 초로 자릅니다. 마이크로초 정밀도가 필요한 경우 EXTERNAL_QUERY 문에서 열을 text 또는 varchar로 형식 지정하거나 변환합니다.

  • 모든 PostgreSQL numeric 및 decimal 열은 BIGNUMERIC(76, 38)에 매핑됩니다. 소수 자릿수가 38보다 큰 열은 소수점 이하 38자리로 반올림되고 BIGNUMERIC 범위를 초과하는 값(전체 정수 자릿수 38개 초과)은 범위를 벗어남 오류로 인해 실패합니다. 또한 특수 값 NaN, Infinity, -Infinity는 지원되지 않으며 런타임에 실패합니다. 정수 부분이 더 크거나, 소수점 정밀도가 더 높거나, 특수 값이 있는 숫자를 쿼리하려면 EXTERNAL_QUERY 문에서 열을 text로 변환합니다.

  • 고정 길이 character(n) / char(n)(bpchar) 값은 BigQuery STRING에 매핑될 때 최대 길이 n까지 후행 공백을 유지합니다.

  • 1582년 10월 15일 이전의 날짜와 타임스탬프는 역사적인 그레고리력 전환으로 인해 10일의 달력 이동이 적용됩니다. 정확한 날짜를 보존하려면 EXTERNAL_QUERY 문에서 이전 날짜를 text로 변환합니다.

지원되는 PostgreSQL 환경 및 버전

쿼리 통합은 다음 배포 환경에서 PostgreSQL 데이터베이스를 지원합니다.

  • 공개 IP 주소 또는 네트워크 연결(Cloud VPN 또는 Cloud Interconnect를 통해)을 통해 액세스할 수 있는 클라우드 호스팅 또는 온프레미스 PostgreSQL 데이터베이스
  • Compute Engine 또는 기타 인프라의 자체 호스팅 PostgreSQL 인스턴스
  • PostgreSQL 16(권장) 및 PostgreSQL 15

시작하기 전에

BigQuery 관리자가 PostgreSQL 연결을 만들고 사용자와 공유했는지 확인합니다.

필요한 역할

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

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

데이터 쿼리

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

다음 예시는 mydataset.customers라는 BigQuery의 고객 레코드를 PostgreSQL의 트랜잭션 판매 주문과 조인하는 페더레이션 쿼리입니다.

SELECT
  c.customer_id,
  c.name,
  fo.first_order_date
FROM
  mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'projects/my-project/locations/us-central1/connections/postgres_prod_conn',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
     FROM orders
     GROUP BY customer_id''') AS fo
  ON fo.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.name,
  fo.first_order_date;

PostgreSQL 테이블 스키마 보기

EXTERNAL_QUERY를 사용하여 PostgreSQL information_schema 테이블을 쿼리하여 데이터베이스 메타데이터를 검색할 수 있습니다.

스키마의 모든 테이블 나열

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT table_schema, table_name, table_type
     FROM information_schema.tables
     WHERE table_schema = 'SCHEMA_NAME'
     ORDER BY table_name''');

테이블의 열 및 데이터 유형 보기

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT column_name, data_type, is_nullable
     FROM information_schema.columns
     WHERE table_schema = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
     ORDER BY ordinal_position''');

데이터 유형 매핑

페더레이션 쿼리를 실행하면 BigQuery는 다음 표에 따라 PostgreSQL의 데이터를 GoogleSQL 데이터 유형으로 변환합니다.

PostgreSQL 데이터 유형 BigQuery 데이터 유형
불리언 유형
bool, boolean BOOL
숫자 유형
int2, smallint, smallserial INT64(대화형 쿼리, CREATE TABLE ... AS SELECT의 제한사항 참조)
int4, integer, serial INT64
int8, bigint, bigserial INT64
numeric, decimal BIGNUMERIC(76, 38)
float4, real FLOAT64(대화형 쿼리, CREATE TABLE ... AS SELECT의 제한사항 참조)
float8, double precision FLOAT64
날짜 및 시간 유형
date DATE
timestamp [ without time zone ] DATETIME
timestamp with time zone, timestamptz TIMESTAMP
time [ without time zone ] TIME
time with time zone, timetz TIME
문자 및 텍스트 유형
character(n), char(n), bpchar STRING
character varying(n), varchar(n) STRING
text STRING

가격 책정

페더레이션 쿼리 실행 비용은 다음 세 가지 요소를 기반으로 합니다.

  • PostgreSQL에서 쿼리를 실행하는 컴퓨팅 비용(PostgreSQL 데이터베이스 제공업체 또는 호스팅 환경에서 청구)
  • PostgreSQL에서 BigQuery로 쿼리 결과를 전송하는 대역폭 비용(데이터베이스 호스팅 제공업체의 이그레스 네트워크 요금)
  • BigQuery에서 쿼리를 실행하는 데 드는 컴퓨팅 비용

PostgreSQL 컴퓨팅 및 이그레스 비용은 Google이 아닌 PostgreSQL 제공업체 또는 인프라 호스트에서 직접 청구합니다. 대역폭 비용을 제한하려면 최종 결과를 계산하는 데 필요하지 않은 모든 열과 행을 제외하도록 EXTERNAL_QUERY 문에 쿼리를 작성하는 것이 좋습니다.

BigQuery에서 페더레이션 쿼리를 실행하는 데 추가 비용이 들지 않습니다. BigQuery 가격에 대한 자세한 내용은 가격 책정을 참조하세요.

연결에서 Google Cloud 네트워킹 구성요소를 사용하는 경우 다음 가격 책정이 적용될 수도 있습니다.

다음 단계