통합 쿼리 함수

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입니다. 자세한 내용은 연결 리소스 만들기를 참조하세요.

주의: EXTERNAL_QUERY를 사용하는 여러 프로젝트에서 공유되는 뷰가 있는 경우 항상 정규화된 연결 ID(projects/PROJECT_ID/locations/LOCATION/connection/CONNECTION_ID)을 사용하세요. 그렇지 않으면 잘못된 프로젝트가 사용될 수 있습니다.

+ 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개 부분으로 구성되는 다음 통합 쿼리 예시를 사용해 이를 수행할 수 있습니다.

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

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 쿼리는 MySQL과 PostgreSQL 모두에서 작동합니다.

-- 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에서 BigQuery 및 PostgreSQL에서 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)')); 이 명령어는 지원되지 않는 데이터 유형 GEOMETRY를 STRING으로 전송합니다.
지원되지 않는 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)); 이 명령어는 지원되지 않는 데이터 유형 money를 string으로 전송합니다.

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 오류가 발생합니다.

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