표준 SQL로 마이그레이션

BigQuery는 두 가지 SQL 언어인 표준 SQLlegacy SQL을 지원합니다. 이 주제에서는 구문, 함수, 시맨틱스 면에서 이 두 언어의 차이점과 예를 통해 표준 SQL의 주요 특징 일부를 설명합니다.

이전 SQL과 표준 SQL 비교

이전 BigQuery에서는 BigQuery SQL이라 하는 비표준 SQL 언어를 사용하여 쿼리를 실행했습니다. 하지만 BigQuery 2.0부터 BigQuery는 표준 SQL을 지원하고 BigQuery SQL 이름은 legacy SQL로 변경되었습니다. BigQuery에 저장된 데이터를 쿼리하는 데 기본적으로 사용되는 SQL 언어는 표준 SQL입니다.

표준 SQL로 이전해야 하나요?

이전 SQL에서 표준 SQL로 이전하는 것이 좋지만 필수사항은 아닙니다. 예를 들어 이전 SQL을 사용하는 쿼리를 많이 실행하지만 새 쿼리에는 표준 SQL의 기능을 활용하고 싶은 경우, 표준 SQL을 사용하는 쿼리를 만들어 이전 SQL을 사용하는 쿼리와 함께 실행할 수 있습니다.

표준 SQL 사용

BigQuery를 통해 쿼리를 실행할 때 이전 SQL을 사용할지 표준 SQL을 사용할지 선택할 수 있습니다. BigQuery UI, CLI, API, 기타 사용 중인 인터페이스에서 표준 SQL을 사용 설정하는 단계는 표준 SQL 사용을 참조하세요.

표준 SQL의 장점

표준 SQL은 SQL 2011 표준을 준수하며, 중첩 및 반복 데이터 쿼리를 지원하는 확장 프로그램을 포함하고 있습니다. 표준 SQL에는 legacy SQL에 비해 다음과 같은 몇 가지 장점이 있습니다.

이러한 기능 중 일부를 보여 주는 예는 표준 SQL의 주요 특징을 참조하세요.

유형 차이점

이전 SQL과 표준 SQL은 같은 유형을 사용합니다. 다만 일부 유형은 이름이 다릅니다. 다음 표에는 이전 SQL의 데이터 유형과 그에 해당하는 표준 SQL 데이터 유형이 나와 있습니다.

이전 SQL 표준 SQL 참고
BOOL BOOL
INTEGER INT64
FLOAT FLOAT64
STRING STRING
BYTES BYTES
RECORD STRUCT
REPEATED ARRAY
TIMESTAMP TIMESTAMP TIMESTAMP 차이점을 참조하세요.
DATE DATE 이전 SQL은 DATE를 제한적으로 지원합니다.
TIME TIME 이전 SQL은 TIME을 제한적으로 지원합니다.
DATETIME DATETIME 이전 SQL은 DATETIME을 제한적으로 지원합니다.

표준 SQL 유형 시스템에 대한 자세한 내용은 표준 SQL 데이터 유형 참조를 참조하세요. BigQuery의 데이터 유형에 대한 자세한 내용은 BigQuery 데이터 유형 참조를 참조하세요.

TIMESTAMP 차이점

표준 SQL은 legacy SQL에 비해 유효한 TIMESTAMP 값 범위가 더 엄격합니다. 표준 SQL에서 유효한 TIMESTAMP 값 범위는 0001-01-01 00:00:00.000000~9999-12-31 23:59:59.999999입니다. 예를 들어 표준 SQL을 사용할 경우 최소 및 최대 TIMESTAMP 값은 다음과 같이 선택할 수 있습니다.

#standardSQL
SELECT
  min_timestamp,
  max_timestamp,
  UNIX_MICROS(min_timestamp) AS min_unix_micros,
  UNIX_MICROS(max_timestamp) AS max_unix_micros
FROM (
  SELECT
    TIMESTAMP '0001-01-01 00:00:00.000000' AS min_timestamp,
    TIMESTAMP '9999-12-31 23:59:59.999999' AS max_timestamp
);

이 쿼리는 min_unix_micros-62135596800000000을 반환하고, max_unix_micros253402300799999999를 반환합니다.

이 범위를 벗어나는 타임스탬프 값이 포함된 열을 선택하면 오류가 발생합니다.

#standardSQL
SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps;

이 쿼리는 다음과 같은 오류를 반환합니다.

Cannot return an invalid timestamp value of -8446744073709551617
microseconds relative to the Unix epoch. The range of valid
timestamp values is [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]

이 오류를 수정하는 한 가지 방법은 다음과 같이 잘못된 타임스탬프를 필터링하는 사용자 정의 함수를 정의하여 사용하는 것입니다.

#standardSQL
CREATE TEMP FUNCTION TimestampIsValid(t TIMESTAMP) AS (
  t >= TIMESTAMP('0001-01-01 00:00:00') AND
  t <= TIMESTAMP('9999-12-31 23:59:59.999999')
);

SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps
WHERE TimestampIsValid(timestamp_column_with_invalid_values);

이 오류를 수정하는 또 다른 방법은 타임스탬프 열에 SAFE_CAST 함수를 사용하는 것입니다. 예를 들면 다음과 같습니다.

#standardSQL
SELECT SAFE_CAST(timestamp_column_with_invalid_values AS STRING) AS timestamp_string
FROM MyTableWithInvalidTimestamps;

이 쿼리는 잘못된 타임스탬프 값에 대해 타임스탬프 문자열 대신 NULL을 반환합니다.

구문 차이점

예약된 키워드 및 잘못된 식별자 이스케이프

이전 SQL에서는 예약된 키워드나 잘못된 문자(예: 공백 또는 하이픈 -)가 포함된 식별자를 대괄호 []로 묶어 이스케이프합니다. 표준 SQL에서는 이러한 키워드와 식별자를 역따옴표 `로 묶어 이스케이프합니다. 예를 들면 다음과 같습니다.

#standardSQL
SELECT
  word,
  SUM(word_count) AS word_count
FROM
  `bigquery-public-data.samples.shakespeare`
WHERE word IN ('me', 'I', 'you')
GROUP BY word;

이전 SQL은 표준 SQL에서는 허용되지 않는 몇몇 위치에서 예약된 키워드를 허용합니다. 예를 들어 다음 쿼리는 표준 SQL을 사용할 경우 Syntax error로 인해 실패합니다.

#standardSQL
SELECT
  COUNT(*) AS rows
FROM
  `bigquery-public-data.samples.shakespeare`;

이 오류를 수정하려면 다음과 같이 별칭 rows를 역따옴표로 묶어 이스케이프합니다.

#standardSQL
SELECT
  COUNT(*) AS `rows`
FROM
  `bigquery-public-data.samples.shakespeare`;

예약된 키워드와 유효한 식별자를 구성하는 요소는 어휘 구조에서 확인할 수 있습니다.

프로젝트 한정 테이블 이름

이전 SQL에서는 프로젝트 한정 이름이 있는 테이블을 쿼리하려면 구분자로 콜론(:)을 사용합니다. 예를 들면 다음과 같습니다.

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT 1;

표준 SQL에서는 그 대신 마침표(.)를 사용합니다. 예를 들면 다음과 같습니다.

#standardSQL
SELECT
  word
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

example.com:myproject와 같이 프로젝트 이름에 도메인이 포함된 경우에는 콜론(:)을 포함한 example.com:myproject를 프로젝트 이름으로 사용합니다.

테이블 데코레이터 및 와일드 카드 함수

표준 SQL은 TABLE_DATE_RANGE, TABLE_DATE_RANGE_STRICT, TABLE_QUERY 함수를 지원하지 않습니다.

_TABLE_SUFFIX 유사 열에서 필터를 사용하면 TABLE_DATE_RANGETABLE_QUERY와 동일한 의미 체계를 적용할 수 있습니다. 예를 들어 다음의 이전 SQL 쿼리는 미국 국립해양대기국의 2010년 및 2011년 GSOD(Global Summary of the Day) 테이블에 포함된 행 수를 계산합니다.

#legacySQL
SELECT COUNT(*)
FROM TABLE_QUERY([bigquery-public-data:noaa_gsod],
                 'table_id IN ("gsod2010", "gsod2011")');

이에 해당하는 표준 SQL 쿼리는 다음과 같습니다.

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.noaa_gsod.*`
WHERE _TABLE_SUFFIX IN ("gsod2010", "gsod2011");

TABLE_DATE_RANGE의 사용 예를 포함한 자세한 내용은 테이블 데코레이터 및 와일드 카드 함수를 참조하세요.

SELECT 목록의 후행 쉼표

이전 SQL과 달리 표준 SQL에서는 FROM 절 앞의 후행 쉼표가 허용되지 않습니다. 다음은 잘못된 쿼리의 예입니다.

#standardSQL
SELECT
  word,
  corpus,  -- Error due to trailing comma
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

이 오류를 수정하려면 corpus 뒤의 쉼표를 제거합니다.

#standardSQL
SELECT
  word,
  corpus
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

테이블에 대한 쉼표 연산자

이전 SQL의 경우 테이블에 적용된 쉼표 연산자(,)는 비표준 방식으로 UNION ALL의 의미를 나타냅니다. 표준 SQL의 경우 쉼표 연산자는 JOIN의 표준 의미를 나타냅니다. 다음의 이전 SQL 쿼리를 예로 들어 보겠습니다.

#legacySQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y),
  (SELECT 2 AS x, "bar" AS y);

이 예에 해당하는 표준 SQL 쿼리는 다음과 같습니다.

#standardSQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y UNION ALL
   SELECT 2 AS x, "bar" AS y);

또한 표준 SQL에서 UNION ALL은 이름이 아니라 위치를 기준으로 열을 연결한다는 점에 주의해야 합니다. 위의 쿼리는 다음과 같습니다.

#standardSQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y UNION ALL
   SELECT 2, "bar");

표준 SQL에서 쉼표 연산자의 일반적인 용도는 배열과 JOIN하는 것입니다. 예를 들면 다음과 같습니다.

#standardSQL
WITH T AS (
  SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
  SELECT 1, [4, 5])
SELECT
  x,
  y
FROM
  T,
  UNNEST(arr) AS y;

이 쿼리는 T 테이블과 arr 내 요소의 교차곱을 반환합니다. 이 쿼리를 다음과 같이 표준 SQL로 나타낼 수도 있습니다.

#standardSQL
WITH T AS (
  SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
  SELECT 1, [4, 5])
SELECT
  x,
  y
FROM
  T
JOIN
  UNNEST(arr) AS y;

이 쿼리에서 JOIN은 위의 예에서 TUNNEST(arr) AS y를 구분하는 쉼표 연산자(,)와 같은 의미입니다.

논리적 뷰

표준 SQL을 사용하여 이전 SQL로 정의된 논리적 뷰를 쿼리하거나 그 반대로 쿼리할 수 없습니다. 이는 두 방언의 구문 및 의미 체계에 차이가 있기 때문입니다. 대신 표준 SQL을 사용하는 새 값을 다른 이름으로 만들어 이전 SQL을 사용하는 값을 대체해야 합니다.

예를 들어 다음과 같이 이전 SQL을 사용하여 정의된 V 뷰가 있다고 가정하겠습니다.

#legacySQL
SELECT *, UTC_USEC_TO_DAY(timestamp_col) AS day
FROM MyTable;

또한 W 뷰는 다음과 같이 이전 SQL을 사용하여 정의되어 있다고 가정합니다.

#legacySQL
SELECT user, action, day
FROM V;

다음과 같은 이전 SQL 쿼리를 매일 실행하고 있지만 표준 SQL을 대신 사용하기 위해 이 쿼리를 이전하려고 합니다.

#legacySQL
SELECT EXACT_COUNT_DISTINCT(user), action, day
FROM W
GROUP BY action, day;

가능한 이전 방법 중 하나는 다른 이름을 사용하여 새 값을 만드는 것입니다. 구체적인 단계는 다음과 같습니다.

표준 SQL을 사용하여 다음과 같은 내용으로 V2라는 뷰를 만듭니다.

#standardSQL
SELECT *, EXTRACT(DAY FROM timestamp_col) AS day
FROM MyTable;

표준 SQL을 사용하여 다음과 같은 내용으로 W2라는 뷰를 만듭니다.

#standardSQL
SELECT user, action, day
FROM V2;

매일 실행하는 쿼리를 표준 SQL을 사용하고 W2를 대신 참조하도록 변경합니다.

#standardSQL
SELECT COUNT(DISTINCT user), action, day
FROM W2
GROUP BY action, day;

또 다른 방법은 VW 뷰를 삭제한 다음 표준 SQL을 사용하여 두 뷰를 이전과 같은 이름으로 다시 만드는 것입니다. 하지만 이 방법을 사용할 경우에는 V 또는 W를 참조하는 모든 쿼리를 동시에 이전하여 표준 SQL을 사용하도록 해야 합니다.

함수 비교

다음 목록에는 이전 SQL 함수와 그에 해당하는 표준 SQL 함수가 일부 나와 있습니다.

이전 SQL 표준 SQL 참고
INTEGER(x) SAFE_CAST(x AS INT64)
CAST(x AS INTEGER) SAFE_CAST(x AS INT64)
DATEDIFF(t1, t2) TIMESTAMP_DIFF(t1, t2, DAY)
NOW CURRENT_TIMESTAMP
STRFTIME_UTC_USEC(t, fmt) FORMAT_TIMESTAMP(fmt, t)
UTC_USEC_TO_DAY(t) TIMESTAMP_TRUNC(t, DAY)
REGEXP_MATCH(s, pattern) REGEXP_CONTAINS(s, pattern)
IS_NULL(x) x IS NULL
LEFT(s, len) SUBSTR(s, 0, len)
RIGHT(s, len) SUBSTR(s, -len)
s CONTAINS "foo" STRPOS(s, "foo") > 0 또는 s LIKE '%foo%'
x % y MOD(x, y)
NEST(x) ARRAY_AGG(x)
ANY(x) ANY_VALUE(x)
GROUP_CONCAT_UNQUOTED(s, sep) STRING_AGG(s, sep)
SOME(x) IFNULL(LOGICAL_OR(x), false)
EVERY(x) IFNULL(LOGICAL_AND(x), true)
COUNT(DISTINCT x) APPROX_COUNT_DISTINCT(x) 아래 설명 참조
EXACT_COUNT_DISTINCT(x) COUNT(DISTINCT x) 아래 설명 참조
QUANTILES(x, buckets + 1) APPROX_QUANTILES(x, buckets)
TOP(x, num), COUNT(*) APPROX_TOP_COUNT(x, num)
NTH(index, arr) WITHIN RECORD arr[SAFE_ORDINAL(index)]
COUNT(arr) WITHIN RECORD ARRAY_LENGTH(arr)
HOST(url) NET.HOST(url) 아래의 차이점 참조
TLD(url) NET.PUBLIC_SUFFIX(url) 아래의 차이점 참조
DOMAIN(url) NET.REG_DOMAIN(url) 아래의 차이점 참조
PARSE_IP(addr_string) NET.IPV4_TO_INT64(NET.IP_FROM_STRING(addr_string))
FORMAT_IP(addr_int64) NET.IP_TO_STRING(NET.IPV4_FROM_INT64(addr_int64 & 0xFFFFFFFF))
PARSE_PACKED_IP(addr_string) NET.IP_FROM_STRING(addr_string)
FORMAT_PACKED_IP(addr_bytes) NET.IP_TO_STRING(addr_bytes)

표준 SQL 함수에 대한 자세한 내용은 함수 및 연산자 항목을 참조하세요.

COUNT 함수 비교

이전 SQL과 표준 SQL 모두에 COUNT 함수가 포함되어 있습니다. 하지만 각 함수의 동작 방식은 사용하는 SQL 방언에 따라 다릅니다.

이전 SQL에서 COUNT(DISTINCT x)는 대략적인 개수를 반환합니다. 하지만 표준 SQL에서는 정확한 개수를 반환합니다. 고유한 값의 대략적인 개수를 더 적은 리소스로 더 빠르게 확인하려면 APPROX_COUNT_DISTINCT을 사용합니다.

URL 함수 비교

legacy SQL과 표준 SQL 모두에 URL을 파싱하는 함수가 포함되어 있습니다. 이러한 함수에는 legacy SQL의 경우 HOST(url), TLD(url), DOMAIN(url), 표준 SQL의 경우에는 NET.HOST(url), NET.PUBLIC_SUFFIX(url), NET.REG_DOMAIN(url)이 있습니다.

legacy SQL 함수에 비해 향상된 기능

  • 표준 SQL URL 함수는 '//'로 시작하는 URL을 파싱할 수 있습니다.
  • 입력이 RFC 3986을 준수하지 않거나 URL이 아니면(예: 'mailto:?to=&subject=&body=') 입력을 파싱하는 데 다른 규칙이 적용됩니다. 특히 표준 SQL URL 함수는 'www.google.com'과 같이 '//'가 없는 비표준 입력을 파싱할 수 있습니다. 최상의 결과를 얻으려면 입력이 URL이고 RFC 3986을 준수하는지 확인하는 것이 좋습니다.
  • NET.PUBLIC_SUFFIX는 선행 마침표가 없는 결과를 반환합니다. 예를 들어 '.com' 대신 'com'을 반환합니다. 이 형식은 공개 서픽스 목록의 형식을 준수합니다.
  • NET.PUBLIC_SUFFIXNET.REG_DOMAIN은 대문자와 다국어 도메인 이름을 지원합니다. TLDDOMAIN은 이를 지원하지 않으므로 예기치 않은 결과가 반환될 수 있습니다.

특이 사례에서의 기타 차이점

  • 입력에 공개 서픽스 목록의 서픽스가 포함되어 있지 않은 경우 NET.PUBLIC_SUFFIXNET.REG_DOMAIN은 NULL을 반환하지만 TLDDOMAIN은 NULL이 아닌 최선의 추측값을 반환합니다.
  • 입력에 공개 서픽스만 포함되어 있고 앞의 라벨이 없는 경우(예: 'http://com'), NET.PUBLIC_SUFFIX는 공개 서픽스를 반환하는 반면 TLD는 빈 문자열을 반환합니다. 마찬가지로 NET.REG_DOMAIN은 NULL을 반환하는 반면 DOMAIN은 공개 서픽스를 반환합니다.
  • IPv6 호스트가 포함된 입력의 경우 NET.HOSTRFC 3986에 지정된 대로 결과에서 괄호를 제거하지 않습니다.
  • IPv4 호스트가 포함된 입력의 경우 NET.REG_DOMAIN은 NULL을 반환하는 반면 DOMAIN은 처음 3개의 옥텟을 반환합니다.

아래 테이블에서 회색 텍스트는 이전 SQL과 표준 SQL에서 동일한 결과를 나타냅니다.

URL(설명) HOST NET.HOST TLD NET.PUBLIC _SUFFIX DOMAIN NET.REG_DOMAIN
"//google.com"
("//"로 시작)
NULL "google.com" NULL "com" NULL "google.com"
"google.com"
(비표준. "//" 없음)
NULL "google.com" NULL "com" NULL "google.com"
"http://user:pass@word@x.com"
(비표준. 여러 개의 "@" 포함)
"word@x.com" "x.com" ".com" "com" "word@x.com" "x.com"
"http://foo.com:1:2"
(비표준. 여러 개의 ":" 포함)
"foo.com:1" "foo.com" ".com:1" "com" "foo.com" "foo.com"
"http://x.Co.uk"
(대문자)
"x.Co.uk" "x.Co.uk" ".uk" "Co.uk" "Co.uk" "x.Co.uk"
"http://a.b"
(공개 서픽스 없음)
"a.b" "a.b" ".b" NULL "a.b" NULL
"http://com"
(호스트에 공개 서픽스만 포함됨)
"com" "com" "" "com" "com" NULL
"http://[::1]"
(IPv6 호스트. 공개 서픽스 없음)
"::1" "[::1]" "" NULL "::1" NULL
"http://1.2.3.4"
(IPv4 호스트. 공개 서픽스 없음)
"1.2.3.4" "1.2.3.4" "" NULL "1.2.3" NULL

반복 필드 처리의 차이점

이전 SQL의 REPEATED 유형은 표준 SQL에서는 해당 유형의 ARRAY에 해당합니다. 예를 들어 REPEATED INTEGER는 표준 SQL의 ARRAY<INT64>에 해당합니다. 다음 섹션에서는 이전 SQL과 표준 SQL에서 반복 필드에 대한 작업에 어떤 차이점이 있는지 설명합니다.

NULL 요소와 NULL 배열

표준 SQL은 NULL 배열 요소를 지원하지만 쿼리 결과에 NULL 배열 요소가 있으면 오류가 발생합니다. 쿼리 결과에 NULL 배열을 포함하는 열이 있는 경우 표준 SQL에서는 해당 배열이 빈 배열로 저장됩니다.

중첩된 반복 리프 필드 선택

이전 SQL을 사용하는 경우에는 반복이 발생하는 위치를 고려할 필요 없이 중첩된 반복 필드에 '점' 표기법을 사용할 수 있습니다. 표준 SQL에서는 중첩된 반복 필드에 '점' 표기법을 사용하면 오류가 발생합니다. 예를 들면 다음과 같습니다.

#standardSQL
SELECT
  repository.url,
  payload.pages.page_name
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

이 쿼리를 실행하면 다음과 같은 결과가 반환됩니다.

Cannot access field page_name on a value with type
ARRAY<STRUCT<action STRING, html_url STRING, page_name STRING, ...>>

이 오류를 수정하고 결과에 page_name의 배열이 반환되도록 하려면 대신 ARRAY 하위 쿼리를 사용해야 합니다. 예를 들면 다음과 같습니다.

#standardSQL
SELECT
  repository.url,
  ARRAY(SELECT page_name FROM UNNEST(payload.pages)) AS page_names
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

배열과 ARRAY 하위 쿼리에 대한 자세한 내용은 배열 다루기 주제를 참조하세요.

반복 필드 필터링

이전 SQL을 사용하는 경우에는 WHERE 절을 사용하여 반복 필드를 바로 필터링할 수 있습니다. 표준 SQL에서는 JOIN 쉼표 연산자 다음에 필터를 사용하여 유사한 논리를 나타낼 수 있습니다. 다음과 같은 이전 SQL 쿼리를 예로 들어 보겠습니다.

#legacySQL
SELECT
  payload.pages.title
FROM
  [bigquery-public-data:samples.github_nested]
WHERE payload.pages.page_name IN ('db_jobskill', 'Profession');

이 쿼리는 page_namedb_jobskill 또는 Profession인 페이지의 모든 title을 반환합니다. 표준 SQL에서는 이와 유사한 쿼리를 다음과 같이 나타낼 수 있습니다.

#standardSQL
SELECT
  page.title
FROM
  `bigquery-public-data.samples.github_nested`,
  UNNEST(payload.pages) AS page
WHERE page.page_name IN ('db_jobskill', 'Profession');

이러한 legacy SQL 쿼리와 표준 SQL 쿼리의 차이점 중 하나는 Flatten Results(결과 평면화) 옵션을 설정하지 않고 legacy SQL 쿼리를 실행하면 payload.pages.title이 쿼리 결과에서 REPEATED된다는 점입니다. 표준 SQL에서 동일한 의미 체계를 적용하고 title 열에 대한 배열이 반환되도록 하려면 ARRAY 하위 쿼리를 대신 사용해야 합니다.

#standardSQL
SELECT
  title
FROM (
  SELECT
    ARRAY(SELECT title FROM UNNEST(payload.pages)
          WHERE page_name IN ('db_jobskill', 'Profession')) AS title
  FROM
    `bigquery-public-data.samples.github_nested`)
WHERE ARRAY_LENGTH(title) > 0;

이 쿼리는 page_name'db_jobskill' 또는 'Profession'title의 배열을 생성한 다음 ARRAY_LENGTH(title) > 0을 사용하여 해당 배열에서 지정된 조건과 일치하지 않는 모든 행을 필터링합니다.

배열에 대한 자세한 내용은 배열 다루기를 참조하세요.

선택된 중첩 리프 필드의 구조

Flatten Results(결과 평면화) 옵션을 설정하지 않으면 legacy SQL은 SELECT 목록에서 중첩 리프 필드 구조를 유지하지만 표준 SQL은 그렇지 않습니다. 다음과 같은 이전 SQL 쿼리를 예로 들어 보겠습니다.

#legacySQL
SELECT
  repository.url,
  repository.has_downloads
FROM
  [bigquery-public-data.samples.github_nested]
LIMIT 5;

Flatten Results(결과 평면화)를 설정하지 않으면 이 쿼리는 repository라는 레코드에 있는 urlhas_downloads를 반환합니다. 이번에는 다음과 같은 표준 SQL 쿼리를 예로 들어 보겠습니다.

#standardSQL
SELECT
  repository.url,
  repository.has_downloads
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

이 쿼리는 urlhas_downloadsrepository 레코드 또는 구조체의 일부가 아니라 최상위 열로 반환합니다. 이를 구조체의 일부로 반환하도록 하려면 STRUCT 연산자를 사용합니다.

#standardSQL
SELECT
  STRUCT(
    repository.url,
    repository.has_downloads) AS repository
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

FLATTEN을 사용하여 반복 제거

표준 SQL에는 이전 SQL과 같은 FLATTEN 함수가 없지만 JOIN(쉼표) 연산자를 사용하여 유사한 의미 체계를 적용할 수 있습니다. 다음과 같은 이전 SQL 쿼리를 예로 들어 보겠습니다.

#legacySQL
SELECT
  repository.url,
  payload.pages.page_name
FROM
  FLATTEN([bigquery-public-data:samples.github_nested], payload.pages.page_name)
LIMIT 5;

표준 SQL에서는 이와 유사한 쿼리를 다음과 같이 나타낼 수 있습니다.

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`,
  UNNEST(payload.pages) AS page
LIMIT 5;

또는 쉼표(,) 연산자 대신 다음과 같이 JOIN을 사용할 수도 있습니다.

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`
JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

이전 SQL 쿼리는 payload.pages가 비어 있는 경우 payload.pages.page_nameNULL인 행을 반환한다는 중요한 차이점이 있습니다. 하지만 표준 SQL 쿼리는 payload.pages가 비어 있으면 행을 반환하지 않습니다. 정확히 동일한 의미 체계를 적용하려면 LEFT JOIN 또는 LEFT OUTER JOIN을 사용합니다. 예를 들면 다음과 같습니다.

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`
LEFT JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

배열에 대한 자세한 내용은 배열 다루기를 참조하세요. UNNEST에 대한 자세한 내용은 UNNEST 주제를 참조하세요.

OMIT RECORD IF를 사용하여 행 필터링

이전 SQL의 OMIT IF 절을 사용하면 반복 필드에 적용할 수 있는 조건을 기준으로 행을 필터링할 수 있습니다. 표준 SQL에서는 EXISTS 절이나 IN 절, 또는 단순 필터를 사용하여 OMIT IF 절과 유사한 기능을 적용할 수 있습니다. 다음과 같은 이전 SQL 쿼리를 예로 들어 보겠습니다.

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  EVERY(payload.pages.page_name != 'db_jobskill'
        AND payload.pages.page_name != 'Profession');

이와 유사한 표준 SQL 쿼리는 다음과 같습니다.

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE EXISTS (
  SELECT 1 FROM UNNEST(payload.pages)
  WHERE page_name = 'db_jobskill'
    OR page_name = 'Profession');

여기서 EXISTS 절은 payload.pages에 페이지 이름이 'db_jobskill' 또는 'Profession'인 요소가 하나 이상 있는 경우 true로 평가됩니다.

또는 이전 SQL에서 다음과 같이 IN을 사용할 수도 있습니다.

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF NOT
  SOME(payload.pages.page_name IN ('db_jobskill', 'Profession'));

표준 SQL에서는 EXISTS 절과 IN을 사용하여 이 쿼리를 나타낼 수 있습니다.

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE EXISTS (
  SELECT 1 FROM UNNEST(payload.pages)
  WHERE page_name IN ('db_jobskill', 'Profession'));

다음의 이전 SQL 쿼리는 페이지 수가 80개 이하인 레코드를 필터링합니다.

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  COUNT(payload.pages.page_name) <= 80;

이 경우 표준 SQL에서는 다음과 같이 ARRAY_LENGTH를 사용하여 필터링할 수 있습니다.

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE
  ARRAY_LENGTH(payload.pages) > 80;

ARRAY_LENGTH 함수는 이전 SQL 쿼리에서처럼 중첩 필드 payload.pages.page_name이 아니라 반복 필드 payload.pages에 바로 적용됩니다.

배열과 ARRAY 하위 쿼리에 대한 자세한 내용은 배열 다루기 주제를 참조하세요.

의미 체계 차이점

일부 작업은 이전 SQL과 표준 SQL에서 서로 다른 의미 체계를 갖습니다.

자동 데이터 유형 강제 변환

이전 SQL과 표준 SQL은 모두 일부 데이터 유형 간의 강제 변환(자동 변환)을 지원합니다. 예를 들어 쿼리에서 FLOAT64 입력이 필요한 함수에 INT64 유형의 값을 전달할 경우 BigQuery는 이 값을 FLOAT64로 강제 변환합니다. 이전 SQL에서 지원되는 다음과 같은 강제 변환은 표준 SQL에서 지원되지 않습니다. 대신 명시적 CAST를 사용해야 합니다.

  • INT64 리터럴에서 TIMESTAMP로 변환. 대신 TIMESTAMP_MICROS(micros_value)를 사용합니다.
  • STRING 리터럴에서 INT64, FLOAT64 또는 BOOL로 변환. 대신 CAST(str AS INT64), CAST(str AS FLOAT64) 또는 CAST(str AS BOOL)을 사용합니다.
  • STRING에서 BYTES로 변환. 대신 CAST(str AS BYTES)를 사용합니다.

런타임 오류

이전 SQL의 일부 함수는 입력이 잘못된 경우 NULL을 반환하므로 쿼리 또는 데이터의 문제를 정확히 알 수 없습니다. 표준 SQL은 일반적으로 더욱 엄격하므로 입력이 잘못되면 오류가 발생합니다.

  • 모든 수학 함수 및 연산의 경우 legacy SQL은 오버플로를 확인하지 않습니다. 표준 SQL에는 오버플로 검사가 추가되어 계산이 오버플로되면 오류가 발생합니다. +, -, * 연산자와 SUM, AVG, STDDEV 집계 함수 등이 이에 해당됩니다.
  • 표준 SQL에서는 0으로 나누기 오류가 발생하지만 legacy SQL에서는 NULL이 반환됩니다. 표준 SQL에서 0으로 나누기에 NULL이 반환되게 하려면 SAFE_DIVIDE를 사용합니다.
  • 표준 SQL에서는 CAST가 잘못된 입력 형식이거나 대상 유형의 범위를 벗어나면 오류가 발생하지만 legacy SQL에서는 NULL이 반환됩니다. 잘못된 cast 오류를 방지하려면 SAFE_CAST를 사용합니다.

중첩 반복 결과

표준 SQL을 사용하여 실행된 쿼리는 결과에서 열의 중첩 및 반복을 유지하며 Flatten Results(결과 평면화) 옵션이 적용되지 않습니다. 중첩 필드의 최상위 열이 반환되도록 하려면 구조체 열에 .* 연산자를 사용해야 합니다. 예를 들면 다음과 같습니다.

#standardSQL
SELECT
  repository.*
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

반복 중첩 필드(STRUCTARRAY)의 최상위 열이 반환되도록 하려면 JOIN을 사용하여 테이블 행과 반복 중첩 필드의 요소에 대한 교차곱을 구합니다. 예를 들면 다음과 같습니다.

#standardSQL
SELECT
  repository.url,
  page.*
FROM
  `bigquery-public-data.samples.github_nested`
JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

배열과 ARRAY 하위 쿼리에 대한 자세한 내용은 배열 다루기 주제를 참조하세요.

NOT IN 조건과 NULL

이전 SQL은 표준 SQL과 달리 NOT IN 조건에서 NULL을 처리할 때 SQL 표준을 준수하지 않습니다. 예를 들어 다음의 이전 SQL 쿼리는 GitHub 샘플 테이블에 위치로 나타나지 않는 단어의 수를 찾습니다.

#legacySQL
SELECT COUNT(*)
FROM [bigquery-public-data.samples.shakespeare]
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM [bigquery-public-data.samples.github_nested]
);

이 쿼리는 개수로 163,716을 반환하여 GitHub 테이블에 위치로 나타나지 않는 단어가 163,716개 있음을 나타냅니다. 이번에는 다음과 같은 표준 SQL 쿼리를 예로 들어 보겠습니다.

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM `bigquery-public-data.samples.github_nested`
);

이 쿼리는 개수로 0을 반환합니다. 이러한 차이가 발생하는 것은 표준 SQL에서는 NOT IN의 의미 체계가 달라서 오른쪽의 값이 NULL일 경우 NULL이 반환되기 때문입니다. 이전 SQL 쿼리와 같은 결과를 얻으려면 다음과 같이 WHERE 절을 사용하여 NULL 값을 제외해야 합니다.

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM `bigquery-public-data.samples.github_nested`
  WHERE actor_attributes.location IS NOT NULL
);

이 쿼리는 개수로 163,716을 반환합니다. 또는 다음과 같이 NOT EXISTS를 사용할 수 있습니다.

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare` AS t
WHERE NOT EXISTS (
  SELECT 1
  FROM `bigquery-public-data.samples.github_nested`
  WHERE t.word = actor_attributes.location
);

이 쿼리도 개수로 163,716을 반환합니다. 자세한 내용은 이 문서의 IN, NOT IN, EXISTS 및 다른 비교 연산자의 시맨틱스를 설명하는 비교 연산자 섹션을 참조하세요.

사용자 정의 자바스크립트 함수의 차이점

표준 SQL에서 자바스크립트 사용자 정의 함수를 사용하는 방법은 사용자 정의 함수 항목에서 설명되어 있습니다. 이 섹션에서는 이전 SQL과 표준 SQL의 사용자 정의 함수에 어떤 차이점이 있는지 설명합니다.

쿼리 텍스트 내의 함수

표준 SQL에서는 맞춤설정 함수를 별도로 지정하지 않고 CREATE TEMPORARY FUNCTION을 쿼리 본문의 일부로 사용합니다. 함수를 별도로 정의하는 방법에는 BigQuery 웹 UI에서 UDF 편집기를 사용하거나 bq CLI를 사용할 때 --udf_resource 플래그를 사용하는 방법이 있습니다.

다음과 같은 표준 SQL 쿼리를 예로 들어 보겠습니다.

#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
--   n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>)
  RETURNS FLOAT64 LANGUAGE js AS """
var sum_of_reciprocals = 0;
for (var i = 0; i < arr.length; ++i) {
  sum_of_reciprocals += 1 / arr[i];
}
return arr.length / sum_of_reciprocals;
""";

WITH T AS (
  SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
  FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;

이 쿼리는 HarmonicMean이라는 자바스크립트 함수를 정의한 다음 이를 T의 배열 열 arr에 적용합니다.

사용자 정의 함수에 대한 자세한 내용은 사용자 정의 함수 항목을 확인하세요.

행이 아니라 값에 대해 작동하는 함수

이전 SQL에서 자바스크립트 함수는 테이블의 행에 대해 작동합니다. 표준 SQL에서 자바스크립트 함수는 위의 예와 같이 값에 대해 작동합니다. 표준 SQL을 사용하여 자바스크립트 함수에 행 값을 전달하려면 테이블과 동일한 행 유형의 구조체를 사용하는 함수를 정의합니다. 예를 들면 다음과 같습니다.

#standardSQL
-- Takes a struct of x, y, and z and returns a struct with a new field foo.
CREATE TEMPORARY FUNCTION AddField(s STRUCT<x FLOAT64, y BOOL, z STRING>)
  RETURNS STRUCT<x FLOAT64, y BOOL, z STRING, foo STRING> LANGUAGE js AS """
var new_struct = new Object();
new_struct.x = s.x;
new_struct.y = s.y;
new_struct.z = s.z;
if (s.y) {
  new_struct.foo = 'bar';
} else {
  new_struct.foo = 'baz';
}

return new_struct;
""";

WITH T AS (
  SELECT x, MOD(off, 2) = 0 AS y, CAST(x AS STRING) AS z
  FROM UNNEST([5.0, 4.0, 3.0, 2.0, 1.0]) AS x WITH OFFSET off
)
SELECT AddField(t).*
FROM T AS t;

이 쿼리는 T와 동일한 행 유형이 포함된 구조체를 받아 foo라는 추가 필드가 포함된 새로운 구조체를 만드는 자바스크립트 함수를 정의합니다. SELECT 문은 이 함수에 t 행을 입력으로 전달하고 .*를 사용하여 결과 구조체의 필드를 출력에 반환합니다.

표준 SQL의 주요 특징

이 섹션에서는 표준 SQL의 주요 특징 몇 가지를 이전 SQL과 비교해서 설명합니다.

WITH 절을 사용하여 작성 가능

이 페이지의 몇 가지 표준 SQL 예에서는 명명된 하위 쿼리를 추출 또는 재사용할 수 있도록 하는 WITH을 사용합니다. 예를 들면 다음과 같습니다.

#standardSQL
WITH T AS (
  SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
)
SELECT x / (SELECT SUM(x) FROM T) AS weighted_x
FROM T;

이 쿼리는 x 값 1, 2, 3, 4가 포함된 명명된 하위 쿼리 T를 정의합니다. 이 쿼리는 T에서 x 값을 선택하고 이를 T에 포함된 모든 x 값의 합계로 나눕니다. 이 쿼리는 다음과 같이 T의 내용을 인라인 함수로 사용하는 쿼리와 동일합니다.

#standardSQL
SELECT
  x / (SELECT SUM(x)
       FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x)) AS weighted_x
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);

또 다른 예로, 여러 개의 명명된 하위 쿼리를 사용하는 다음 쿼리를 살펴보겠습니다.

#standardSQL
WITH T AS (
  SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
),
TPlusOne AS (
  SELECT x + 1 AS y
  FROM T
),
TPlusOneTimesTwo AS (
  SELECT y * 2 AS z
  FROM TPlusOne
)
SELECT z
FROM TPlusOneTimesTwo;

이 쿼리는 원본 데이터의 변환 시퀀스를 정의하고 그 뒤에 TPlusOneTimesTwo에 대한 SELECT 문을 정의합니다. 이 쿼리는 계산이 인라인으로 이루어지는 다음 쿼리와 동일합니다.

#standardSQL
SELECT (x + 1) * 2 AS z
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);

자세한 내용은 이 문서의 WITH 주제를 참조하세요.

SQL 함수를 사용하여 작성 가능

표준 SQL에서는 사용자 정의 SQL 함수가 지원됩니다. 사용자 정의 SQL 함수를 사용하여 공통 표현식을 정의한 다음 쿼리에서 이를 참조할 수 있습니다. 예를 들면 다음과 같습니다.

#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
--   n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>) AS
(
  ARRAY_LENGTH(arr) / (SELECT SUM(1 / x) FROM UNNEST(arr) AS x)
);

WITH T AS (
  SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
  FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;

이 쿼리는 HarmonicMean이라는 SQL 함수를 정의한 다음 이를 T의 배열 열 arr에 적용합니다.

다양한 위치에서 하위 쿼리 지원

표준 SQL은 SELECT 목록, WHERE 절을 비롯하여 표현식이 필요한 쿼리 내의 기타 모든 위치에서 하위 쿼리를 지원합니다. 예를 들어 다음의 표준 SQL 쿼리는 시애틀에서 2015년 중 기온이 따뜻했던 날의 비율을 계산합니다.

#standardSQL
WITH SeattleWeather AS (
  SELECT *
  FROM `bigquery-public-data.noaa_gsod.gsod2015`
  WHERE stn = '994014'
)
SELECT
  COUNTIF(max >= 70) /
    (SELECT COUNT(*) FROM SeattleWeather) AS warm_days_fraction
FROM SeattleWeather;

시애틀 기상 관측소의 ID는 '994014'입니다. 이 쿼리는 일일 최대 기온이 화씨 70도(섭씨 약 21도)에 도달했는지 여부를 기준으로 따뜻했던 날의 일수를 계산하고 이를 해당 관측소에서 기록된 2015년의 전체 일수로 나눕니다.

상관 하위 쿼리

표준 SQL에서 하위 쿼리는 상관 관계가 있는 열, 즉 외부 쿼리에서 조회한 열을 참조할 수 있습니다. 다음과 같은 표준 SQL 쿼리를 예로 들어 보겠습니다.

#standardSQL
WITH WashingtonStations AS (
  SELECT weather.stn AS station_id, ANY_VALUE(station.name) AS name
  FROM `bigquery-public-data.noaa_gsod.stations` AS station
  INNER JOIN `bigquery-public-data.noaa_gsod.gsod2015` AS weather
  ON station.usaf = weather.stn
  WHERE station.state = 'WA' AND station.usaf != '999999'
  GROUP BY station_id
)
SELECT washington_stations.name,
  (SELECT COUNT(*)
   FROM `bigquery-public-data.noaa_gsod.gsod2015` AS weather
   WHERE washington_stations.station_id = weather.stn
   AND max >= 70) AS warm_days
FROM WashingtonStations AS washington_stations
ORDER BY warm_days DESC;

이 쿼리는 워싱턴주의 기상 관측소 이름별로 2015년 중 일일 최대 기온이 화씨 70도(섭씨 약 21도)에 도달했던 날의 일수를 계산합니다. SELECT 리스트에는 하위 쿼리가 있으며 이 하위 쿼리는 외부 범위, 즉 FROM WashingtonStations AS washington_stationswashington_stations.station_id를 참조합니다.

배열 및 구조체

ARRAYSTRUCT는 표준 SQL의 강력한 개념입니다. 이 둘을 모두 사용하는 예로, HackerNews 데이터세트에서 날짜별로 상위 두 개의 기사를 조회하는 다음 쿼리를 살펴보겠습니다.

#standardSQL
WITH TitlesAndScores AS (
  SELECT
    ARRAY_AGG(STRUCT(title, score)) AS titles,
    EXTRACT(DATE FROM time_ts) AS date
  FROM `bigquery-public-data.hacker_news.stories`
  WHERE score IS NOT NULL AND title IS NOT NULL
  GROUP BY date)
SELECT date,
  ARRAY(SELECT AS STRUCT title, score
        FROM UNNEST(titles)
        ORDER BY score DESC
        LIMIT 2)
  AS top_articles
FROM TitlesAndScores
ORDER BY date DESC;

WITH 절은 두 개의 열을 포함하는 TitlesAndScores를 정의합니다. 첫 번째 열은 한 필드가 기사 제목을 나타내는 구조체의 배열이고 두 번째 열은 점수 열입니다. ARRAY_AGG 표현식은 이러한 구조체의 날짜별 배열을 반환합니다.

WITH 절 다음의 SELECT 문은 ARRAY 하위 쿼리를 사용해서 score를 기준으로 한 각 배열 내 상위 두 개의 기사를 반환한 다음 결과를 날짜 기준 내림차순으로 반환합니다.

배열과 ARRAY 하위 쿼리에 대한 자세한 내용은 배열 다루기 주제를 참조하세요. 배열구조체의 참조 문서도 참조하세요.

이 페이지가 도움이 되었나요? 평가를 부탁드립니다.

다음에 대한 의견 보내기...

도움이 필요하시나요? 지원 페이지를 방문하세요.