통합 쿼리 함수

BigQuery용 GoogleSQL은 다음과 같은 통합 쿼리 함수를 지원합니다.

함수 목록

이름 요약
EXTERNAL_QUERY 외부 데이터베이스에서 쿼리를 실행하고 결과를 임시 테이블로 반환합니다.

EXTERNAL_QUERY

EXTERNAL_QUERY('connection_id', '''external_database_query'''[, 'options'])

Description(설명)

외부 데이터베이스에서 쿼리를 실행하고 결과를 임시 테이블로 반환합니다. 외부 데이터베이스 데이터 유형은 이 데이터 유형 매핑을 사용하여 임시 결과 테이블에서 GoogleSQL 데이터 유형으로 변환됩니다.

  • external_database_query: 외부 데이터베이스에서 실행할 쿼리입니다.
  • connection_id: 연결 리소스의 ID입니다. 연결 리소스에는 외부 데이터베이스와 BigQuery 간의 연결 설정이 포함됩니다. 기본 프로젝트를 구성하지 않은 경우 다음 형식으로 연결 ID 앞에 프로젝트 ID를 붙입니다.

    projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID
    

    다음을 바꿉니다.

    • PROJECT_ID: 프로젝트 ID입니다.
    • LOCATION: 연결의 위치입니다.
    • CONNECTION_ID: 연결 ID입니다.

    예를 들면 projects/example-project/locations/us/connections/sql-bq입니다. 자세한 내용은 연결 리소스 만들기를 참조하세요.

+ options: 옵션 이름 및 값의 키-값 쌍이 포함된 JSON 형식 맵의 선택적 문자열입니다(모두 대소문자를 구분함).

For example::
``` '{"default_type_for_decimal_columns":"numeric"}' ```

Supported options:

|Option Name | Description
|-------- | -------
|"default_type_for_decimal_columns" | Can be "float64", "numeric", "bignumeric" or "string". With this option, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to the provided BigQuery type. When this option is not provided, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to BigQuery NUMERIC type.
|"query_execution_priority" | Can be "low", "medium" or "high". Only supported in Spanner. Specifies priority for execution of the query. Execution priority is "medium" by default.

기타 참고사항:

  • EXTERNAL_QUERY 함수는 일반적으로 FROM 절에 사용됩니다.
  • EXTERNAL_QUERY() 함수를 사용하여 외부 데이터베이스에 대한 메타데이터에 액세스할 수 있습니다.
  • EXTERNAL_QUERY()는 외부 쿼리에 ORDER BY가 포함된 경우에도 외부 쿼리 결과의 순서를 무시합니다.

반환 데이터 유형

BigQuery 테이블

예시

보고서에 포함하기 위해 각 고객의 첫 주문 날짜가 필요하다고 가정해 보겠습니다. 데이터는 현재 BigQuery에 없지만 운영 PostgreSQL 데이터베이스에 있습니다. 3개 부분으로 구성되는 다음 통합 쿼리 예시를 사용해 이를 수행할 수 있습니다.

  1. 운영 PostgreSQL 데이터베이스에서 외부 쿼리 SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id를 실행하여 EXTERNAL_QUERY() 함수를 통해 각 고객의 첫 주문 날짜를 구합니다.
  2. customer_id를 기준으로 BigQuery에서 외부 쿼리 결과 테이블을 고객 테이블과 조인합니다.
  3. 고객 정보와 첫 주문 날짜를 선택합니다.
SELECT
  c.customer_id, c.name, SUM(t.amount) AS total_revenue, rq.first_order_date
FROM customers AS c
INNER JOIN transaction_fact AS t ON c.customer_id = t.customer_id
LEFT OUTER JOIN
  EXTERNAL_QUERY(
    '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;

information_schema 테이블을 쿼리하기 위해 EXTERNAL_QUERY() 함수를 사용해 데이터베이스 메타데이터에 액세스하여 데이터베이스의 모든 테이블을 나열하거나 테이블 스키마를 볼 수 있습니다. 다음 예시의 information_schema 쿼리는 MySQLPostgreSQL 모두에서 작동합니다.

-- 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';'''
  );

EXTERNAL_QUERY()는 외부 쿼리에 ORDER BY가 포함된 경우에도 외부 쿼리 결과의 순서를 무시합니다. 다음 예시 쿼리는 외부 데이터베이스의 고객 ID별로 행을 정렬하지만 BigQuery는 이 순서대로 결과 행을 출력하지 않습니다.

-- ORDER BY will not order rows.
SELECT *
FROM
  EXTERNAL_QUERY(
    'connection_id',
    '''SELECT * FROM customers AS c ORDER BY c.customer_id'''
  );

데이터 유형 매핑

통합 쿼리를 실행할 때 외부 데이터베이스의 데이터가 GoogleSQL 유형으로 변환됩니다. 다음은 MySQL에서 BigQueryPostgreSQL에서 BigQuery의 데이터 유형 매핑입니다.

매핑에 대해 알아야 할 사항은 다음과 같습니다.

  • 대부분의 MySQL 데이터 유형은 decimal, timestamp, time과 같은 일부 예외를 빼면 동일한 BigQuery 데이터 유형과 짝지을 수 있습니다.
  • PostgreSQL은 BigQuery에서 지원되지 않는 많은 비표준 데이터 유형(예: money, path, uuid, boxer 등)을 지원합니다.
  • MySQL 및 PostgreSQL의 숫자 데이터 유형은 기본적으로 BigQuery NUMERIC 값에 매핑됩니다. BigQuery NUMERIC 값 범위는 MySQL 및 PostgreSQL보다 작습니다. 또한 EXTERNAL_QUERY 옵션에서 "default_type_for_decimal_columns"BIGNUMERIC, FLOAT64, STRING에 매핑됩니다.

오류 처리

외부 쿼리에 BigQuery에서 지원되지 않는 데이터 유형이 포함되면 쿼리는 즉시 실패합니다. 지원되지 않는 데이터 유형을 지원되는 다른 MySQL / PostgreSQL 데이터 유형으로 변환할 수 있습니다. cast 변환 방법은 지원되지 않는 데이터 유형을 참조하세요.

MySQL에서 BigQuery로 유형 매핑

MySQL 유형 MySQL 설명 BigQuery 유형 유형 차이
Integer
INT 4바이트, 2^32 - 1 INT64
TINYINT 1바이트, 2^8 - 1 INT64
SMALLINT 2바이트, 2^16 - 1 INT64
MEDIUMINT 3바이트, 2^24 - 1 INT64
BIGINT 8바이트, 2^64 - 1 INT64
UNSIGNED BIGINT 8바이트, 2^64 - 1 NUMERIC
정확한 숫자
DECIMAL (M,D) (M,D)로 표현되는 소수. 여기서 M은 총 자릿수, D는 소수 자릿수입니다. M <= 65 NUMERIC, BIGNUMERIC, FLOAT64, STRING

DECIMAL(M,D)은 기본적으로 NUMERIC에 매핑되거나 default_type_for_decimal_columns를 사용하여 BIGNUMERIC, FLOAT64 또는 STRING에 매핑될 수 있습니다.
대략적인 숫자
FLOAT (M,D) 4바이트, M <= 23 FLOAT64
DOUBLE (M,D) 8바이트, M <= 53 FLOAT64
날짜 및 시간
TIMESTAMP '1970-01-01 00:00:01'UTC ~ '2038-01-19 03:14:07' UTC. TIMESTAMP MySQL TIMESTAMP는 사용자가 BigQuery를 호출하는 위치에 관계없이 UTC 시간대로 검색됩니다.
DATETIME '1000-01-01 00:00:00' ~ '9999-12-31 23:59:59' DATETIME
DATE '1000-01-01' ~ '9999-12-31'. DATE
TIME 시간은 'HH:MM:SS' 형식으로
'-838:59:59' ~'838:59:59'입니다.
TIME
BigQuery TIME 범위는 더 작은 00:00:00~23:59:59입니다.
YEAR INT64
문자 및 문자열
ENUM 허용된 값 목록에서 선택된 값이 있는 문자열 객체 STRING
CHAR (M) 1~255자 사이의 고정 길이 문자열 STRING
VARCHAR (M) 1~255자 사이의 가변 길이 문자열 STRING
TEXT 최대 길이가 65,535자인 필드 STRING
TINYTEXT 최대 길이가 255자인 TEXT 열 STRING
MEDIUMTEXT 최대 길이가 16,777,215자인 TEXT 열 STRING
LONGTEXT 최대 길이가 4,294,967,295자인 TEXT 열 STRING
바이너리
BLOB 최대 길이가 65,535자인 바이너리 대형 객체 BYTES
MEDIUM_BLOB 최대 길이가 16,777,215자인 BLOB BYTES
LONG_BLOB 최대 길이가 4,294,967,295자인 BLOB BYTES
TINY_BLOB 최대 길이가 255자인 BLOB BYTES
바이너리 1~255자 사이의 고정 길이 바이너리 문자열 BYTES
VARBINARY 1~255자 사이의 가변 길이 바이너리 문자열 BYTES
기타
SET SET 열 선언 시 일부 값을 사전 정의합니다. 그런 다음 이 열에 사전 정의된 아무 값 집합을 INSERT합니다. STRING
GEOMETRY GEOGRAPHY 아직 지원되지 않음
BIT INT64 아직 지원되지 않음

PostgreSQL에서 BigQuery로 유형 매핑

이름 설명 BigQuery 유형 유형 차이
Integer
smallint 2바이트, -32768~+32767 INT64
smallserial smallint 참조 INT64
integer 4바이트, -2147483648~+2147483647 INT64
serial integer 참조 INT64
bigint 8바이트, -9223372036854775808~9223372036854775807 INT64
bigserial bigint 참조 INT64
정확한 숫자
numeric [ (p, s) ] 최대 1,000의 정밀도 NUMERIC, BIGNUMERIC, FLOAT64, STRING 숫자 [ (p, s) ]는 기본적으로 NUMERIC에 매핑되거나 default_type_for_decimal_columns를 사용하여 BIGNUMERIC, FLOAT64 또는 STRING에 매핑할 수 있습니다.
Decimal [ (p, s) ] numeric 참조 NUMERIC numeric 참조
money 8바이트, 2자릿수 척도, -92233720368547758.08~+92233720368547758.07 지원되지 않음
대략적인 숫자
real 4바이트, 단일 정밀도 부동 소수점 수 FLOAT64
double precision 8바이트, 배정밀도 부동 소수점 수 FLOAT64
날짜 및 시간
date 달력 날짜(연, 월, 일) DATE
time [ (p) ] [ 시간대 제외 ] 시간(시간대 없음) TIME
time [ (p) ](시간대 포함) 시간(시간대 포함) 지원되지 않음
timestamp [ (p) ] [ 시간대 제외 ] 날짜 및 시간(시간대 없음) DATETIME
timestamp [ (p) ](시간대 포함) 날짜 및 시간(시간대 포함) TIMESTAMP PostgreSQL TIMESTAMP는 사용자가 BigQuery를 호출한 위치에 관계없이 UTC 시간대로 검색됩니다.
interval 지속 시간 지원되지 않음
문자 및 문자열
character [ (n) ] 고정 길이 문자 문자열 STRING
character varying [ (n) ] 가변 길이 문자 문자열 STRING
text 가변 길이 문자 문자열 STRING
바이너리
bytea 바이너리 데이터('바이트 배열') BYTES
bit [ (n) ] 고정 길이 비트 문자열 BYTES
bit varying [ (n) ] 가변 길이 비트 문자열 BYTES
기타
boolean 논리 부울(true/false) BOOL
inet IPv4 또는 IPv6 호스트 주소 지원되지 않음
path 평면상의 기하학적 경로 지원되지 않음
pg_lsn PostgreSQL 로그 시퀀스 번호 지원되지 않음
point 평면상의 기하학적 점 지원되지 않음
polygon 평면상의 닫힌 기하학적 경로 지원되지 않음
tsquery 텍스트 검색어 지원되지 않음
tsvector 텍스트 검색 문서 지원되지 않음
txid_snapshot 사용자 수준 트랜잭션 ID 스냅샷 지원되지 않음
uuid 범용 고유 식별자 지원되지 않음
xml XML 데이터 STRING
box 평면상의 직사각형 상자 지원되지 않음
cidr IPv4 또는 IPv6 네트워크 주소 지원되지 않음
circle 평면상의 원 지원되지 않음
interval [ fields ] [ (p) ] 시간 범위 지원되지 않음
json 텍스트 JSON 데이터 STRING
jsonb 바이너리 JSON 데이터(분할됨) 지원되지 않음
line 평면상의 무한 선 지원되지 않음
lseg 평면상의 선분 지원되지 않음
macaddr MAC(미디어 액세스 제어) 주소 지원되지 않음
macaddr8 MAC(미디어 액세스 제어) 주소(EUI-64 형식) 지원되지 않음

지원되지 않는 MySQL 및 PostgreSQL 데이터 유형

외부 쿼리에 BigQuery에서 지원되지 않는 데이터 유형이 포함되면 쿼리는 즉시 실패합니다. 지원되지 않는 데이터 유형을 다른 지원되는 MySQL / PostgreSQL 데이터 유형으로 변환할 수 있습니다.

  • 지원되지 않는 MySQL 데이터 유형
    • 오류 메시지: Invalid table-valued function external_query Found unsupported MySQL type in BigQuery. at [1:15]
    • 지원되지 않는 유형: GEOMETRY, BIT
    • 해결 방법: 지원되지 않는 데이터 유형을 STRING으로 변환합니다.
    • 예: SELECT ST_AsText(ST_GeomFromText('POINT(1 1)')); 이 명령어는 지원되지 않는 데이터 유형 GEOMETRYSTRING으로 전송합니다.
  • 지원되지 않는 PostgreSQL 데이터 유형
    • 오류 메시지: Invalid table-valued function external_query Postgres type (OID = 790) is not supported now at [1:15]
    • 지원되지 않는 유형: money, time with time zone, inet, path, pg_lsn, point, polygon, tsquery, tsvector, txid_snapshot, uuid, box, cidr, circle, interval, jsonb, line, lseg, macaddr, macaddr8
    • 해결 방법: 지원되지 않는 데이터 유형을 STRING으로 변환합니다.
    • 예: SELECT CAST('12.34'::float8::numeric::money AS varchar(30)); 이 명령어는 지원되지 않는 데이터 유형 moneystring으로 전송합니다.

Spanner에서 BigQuery로 유형 매핑

Spanner 통합 쿼리를 실행하면 Spanner의 데이터는 GoogleSQL 유형으로 변환됩니다.

Spanner GoogleSQL 유형 Spanner PostgreSQL 유형 BigQuery 유형
ARRAY - ARRAY
BOOL bool BOOL
BYTES bytea BYTES
DATE date DATE
FLOAT64 float8 FLOAT64
INT64 bigint INT64
JSON JSONB JSON
NUMERIC numeric* NUMERIC
STRING varchar STRING
STRUCT - Spanner 통합 쿼리에는 지원되지 않습니다.
TIMESTAMP timestamptz TIMESTAMP(나노초 잘림)

* 정밀도가 BigQuery에서 지원하는 정밀도보다 큰 PostgreSQL 숫자 값은 반올림됩니다. 값이 최댓값보다 크면 Invalid NUMERIC value 오류가 발생합니다.

외부 쿼리에 통합 쿼리에 지원되지 않는 데이터 유형이 포함되면 쿼리가 즉시 실패합니다. 지원되지 않는 데이터 유형을 지원되는 데이터 유형으로 변환할 수 있습니다.