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개 부분으로 구성되는 다음 통합 쿼리 예시를 사용해 이를 수행할 수 있습니다.
- 운영 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
값에 매핑됩니다. BigQueryNUMERIC
값 범위는 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
오류가 발생합니다.
외부 쿼리에 통합 쿼리에 지원되지 않는 데이터 유형이 포함되면 쿼리가 즉시 실패합니다. 지원되지 않는 데이터 유형을 지원되는 데이터 유형으로 변환할 수 있습니다.