와일드 카드 테이블을 사용하여 여러 테이블 쿼리

와일드 카드 테이블을 사용하면 간단한 SQL 문으로 여러 테이블을 쿼리할 수 있습니다. 와일드 카드 테이블은 GoogleSQL에서만 사용할 수 있습니다. legacy SQL의 해당 기능은 테이블 와일드 카드 함수를 확인하세요.

와일드 카드 테이블은 와일드 카드 표현식과 일치하는 테이블의 모음입니다. 예를 들어 다음 FROM 절은 와일드 카드 표현식 gsod*을 사용하여 gsod 문자열로 시작하는noaa_gsod 데이터세트의 모든 테이블과 일치하도록 합니다.

FROM
  `bigquery-public-data.noaa_gsod.gsod*`

와일드 카드 테이블의 각 행에는 와일드 카드 문자와 일치하는 값을 포함하는 _TABLE_SUFFIX라는 특수한 열이 있습니다.

제한사항

와일드 카드 테이블 쿼리에는 다음과 같은 제한사항이 적용됩니다.

와일드 카드 테이블 기능은 뷰를 지원하지 않습니다. 와일드 카드 테이블이 데이터 세트의 뷰와 일치하면 뷰를 필터링하기 위해 _TABLE_SUFFIX 유사 열에 WHERE 절이 포함된 쿼리더라도 쿼리에서 오류를 반환합니다.
캐시 결과 사용 옵션을 선택하더라도 와일드 카드를 사용하는 여러 테이블을 대상으로 하는 쿼리에서 캐시된 결과가 지원되지 않습니다. 동일한 와일드 카드 쿼리를 여러 번 실행하면 각 쿼리마다 비용이 청구됩니다.
와일드 카드 테이블은 기본 제공 BigQuery 스토리지만 지원합니다. 외부 테이블 또는 뷰 쿼리에는 와일드 카드를 사용할 수 없습니다.
호환되지 않는 파티션 나누기가 있는 테이블이나 파티션을 나눈 테이블과 파티션을 나누지 않은 테이블이 혼합된 경우에는 와일드 카드 쿼리를 사용할 수 없습니다. 쿼리된 테이블에도 동일한 클러스터링 사양이 있어야 합니다.
파티션을 나눈 테이블에 와일드 카드 테이블을 사용할 수 있으며 파티션 프루닝과 클러스터 프루닝이 모두 지원됩니다. 하지만 클러스터링되었지만 파티션을 나누지 않은 테이블은 와일드 카드 사용 시 클러스터 프루닝의 이점을 누릴 수 없습니다.
데이터 조작 언어(DML) 문을 포함하는 쿼리는 와일드 카드 테이블을 쿼리 대상으로 사용할 수 없습니다. 예를 들어 와일드 카드 테이블은 UPDATE 쿼리의 FROM 절에서 사용할 수 있지만 UPDATE 작업의 대상으로 사용할 수 없습니다.
JavaScript 사용자 정의 함수가 포함된 _TABLE_SUFFIX 또는 _PARTITIONTIME 유사 열에 대한 필터는 와일드 카드 테이블에서 스캔되는 테이블의 수를 제한하지 않습니다.
고객 관리 암호화 키(CMEK)로 보호되는 테이블에서는 와일드 카드 쿼리가 지원되지 않습니다.
와일드 카드 쿼리에서 참조하는 모든 테이블은 태그 키와 값 집합이 정확히 동일해야 합니다.
와일드카드 테이블을 사용하는 경우 _TABLE_SUFFIX가 REGEXP_CONTAINS와 함께 사용되고 ^[0-9]{2}$와 같은 정규 표현식이 제공되더라도 * 앞의 테이블 이름으로 시작하는 데이터 세트의 모든 테이블이 스캔됩니다. 예를 들면 다음과 같습니다.
```
SELECT *
FROM `my_project.my_dataset.my_table_*`
WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, '^[0-9]{2}$');
```
단일 스캔 테이블에 스키마 불일치가 있는 경우(즉, 이름이 같은 열이 다른 유형인 경우) 쿼리가 X 유형의 필드를 Y 필드로 읽을 수 없음: column_name 오류와 함께 실패합니다. 등호 연산자 =를 사용하는 경우에도 모든 테이블이 일치합니다. 예를 들어 다음 쿼리에서는 my_dataset.my_table_03_backup 테이블도 스캔됩니다. 따라서 스키마 불일치로 인해 쿼리가 실패할 수 있습니다. 하지만 스키마 불일치가 없으면 예상대로 결과가 my_dataset.my_table_03 테이블에서만 발생합니다.
```
SELECT *
FROM my_project.my_dataset.my_table_*
WHERE _TABLE_SUFFIX = '03'
```

시작하기 전에

GoogleSQL을 사용 중인지 확인합니다. 자세한 내용은 SQL 언어 전환을 참조하세요.
이전 SQL을 사용하는 경우 테이블 와일드 카드 함수를 참조하세요.
이 페이지의 많은 예제에서는 미국 해양대기청(National Oceanic and Atmospheric Administration, NOAA)의 공개 데이터세트를 사용합니다. 데이터에 대한 자세한 내용은 일일 기상 데이터의 NOAA 지구 표면 요약을 참조하세요.

와일드 카드 테이블을 사용해야 하는 경우

와일드 카드 테이블은 데이터세트에 호환 가능한 스키마가 있고 이름이 유사한 테이블이 여러 개 포함된 경우에 유용합니다. 일반적으로 이러한 데이터세트에는 각각 하루, 월 또는 연도의 데이터를 나타내는 테이블이 포함됩니다. 예를 들어 Big Weather에서 BigQuery에서 호스팅하는 공개 데이터세트인 일일 기상 데이터의 NOAA 지구 표면 요약에는 1929년부터 지금까지 각 연도의 테이블이 포함되어 있습니다.

FROM 절에 12개 테이블의 이름을 모두 지정해야 하는 경우 1929년부터 1940년까지의 모든 테이블 ID를 스캔하는 쿼리는 길이가 매우 길어집니다. 이 샘플에서는 대부분의 테이블이 생략되었습니다.

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM (
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1929` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1930` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1931` UNION ALL

  # ... Tables omitted for brevity

  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1940` )
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

동일한 쿼리에 와일드 카드 테이블을 사용하면 훨씬 간결해집니다.

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29'
  AND '40'
ORDER BY
  max DESC

와일드 카드 테이블은 기본 제공 BigQuery 스토리지만 지원합니다. 외부 테이블 또는 뷰를 쿼리할 때는 와일드 카드를 사용할 수 없습니다.

와일드 카드 테이블 구문

와일드 카드 테이블 구문:

SELECT
FROM
  `<project-id>.<dataset-id>.<table-prefix>*`
WHERE
  bool_expression

<project-id>: Cloud Platform 프로젝트 ID입니다. 기본 프로젝트 ID를 사용하는 경우 선택사항입니다.
<dataset-id>: BigQuery 데이터 세트 ID입니다.
<table-prefix>: 와일드 카드 문자와 일치하고 모든 테이블에서 공통적인 문자열입니다. 테이블 프리픽스는 선택사항입니다. 테이블 프리픽스를 생략하면 데이터 세트의 모든 테이블이 일치합니다.
*(와일드 카드 문자): 와일드 카드 문자 '*'는 테이블 이름의 하나 이상의 문자를 나타냅니다. 와일드 카드 문자는 와일드 카드 테이블 이름의 마지막 문자로만 나타날 수 있습니다.

와일드 카드 테이블을 사용하는 쿼리는 WHERE 절의 _TABLE_SUFFIX 유사 열을 지원합니다. 이 열에는 쿼리로 액세스할 테이블을 필터링할 수 있도록 와일드 카드 문자와 일치하는 값이 포함됩니다. 예를 들어 다음 WHERE 절은 비교 연산자를 사용하여 일치하는 테이블을 필터링합니다.

WHERE
  _TABLE_SUFFIX BETWEEN '29' AND '40'

WHERE
  _TABLE_SUFFIX = '1929'

WHERE
  _TABLE_SUFFIX < '1941'

_TABLE_SUFFIX 유사 열에 대한 자세한 내용은 _TABLE_SUFFIX를 사용하여 선택한 테이블 필터링을 확인하세요.

와일드 카드가 있는 테이블 이름을 백틱으로 묶기

와일드 카드 테이블 이름에는 특수문자(*)가 포함되어 있습니다. 즉, 와일드 카드 테이블 이름을 백틱(`) 문자로 묶어야 합니다. 예를 들어, 다음 쿼리는 백틱을 사용하므로 유효합니다.

#standardSQL
/* Valid SQL query */
SELECT
  max
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

다음 쿼리는 백틱으로 적합하게 묶이지 않았으므로 유효하지 않습니다.

#standardSQL
/* Syntax error: Expected end of statement but got "-" at [4:11] */
SELECT
  max
FROM
  # missing backticks
  bigquery-public-data.noaa_gsod.gsod*
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

따옴표는 작동하지 않습니다.

#standardSQL
/* Syntax error: Unexpected string literal: 'bigquery-public-data.noaa_gsod.gsod*' at [4:3] */
SELECT
  max
FROM
  # quotes are not backticks
  'bigquery-public-data.noaa_gsod.gsod*'
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

와일드 카드 테이블을 사용하여 테이블 쿼리

와일드 카드 테이블을 사용하면 여러 테이블을 간단하게 쿼리할 수 있습니다. 예를 들어 Big Weather에서 BigQuery에서 호스팅하는 공개 데이터세트인 일일 기상 데이터의 NOAA 지구 표면 요약에는 1929년부터 지금까지 각 연도의 테이블이 포함되어 있으며, 모두 네 자리 연도 앞에 오는 공통 프리픽스 gsod를 공유합니다. 테이블 이름은 gsod1929, gsod1930, gsod1931 등으로 지정됩니다.

공통 프리픽스를 공유하는 테이블 그룹을 쿼리하려면 FROM 문에서 테이블 프리픽스 다음에 테이블 와일드 카드 기호 (*)를 사용하세요. 예를 들어, 다음 쿼리는 1940년대에 보고된 최고 온도를 찾습니다.

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

_TABLE_SUFFIX를 사용하여 선택한 테이블 필터링

지정된 테이블 세트만 스캔하도록 쿼리를 제한하려면 상수 표현식 조건인 WHERE 절에 _TABLE_SUFFIX 유사 열을 사용합니다.

_TABLE_SUFFIX 유사 열에는 테이블 와일드 카드와 일치하는 값이 포함되어 있습니다. 예를 들어, 1940년대의 모든 테이블을 스캔하는 이전의 샘플 쿼리는 테이블 와일드 카드를 사용하여 연도의 마지막 숫자를 나타냅니다.

FROM
  `bigquery-public-data.noaa_gsod.gsod194*`

이에 해당하는 _TABLE_SUFFIX 유사 열에는 0부터 9까지의 범위에 있는 값이 포함되며, 이는 테이블 gsod1940부터 gsod1949까지를 나타냅니다. 이러한 _TABLE_SUFFIX 값은 특정한 테이블을 필터링하기 위해 WHERE 절에 사용될 수 있습니다.

예를 들어, 1940년부터 1944년까지의 최고 기온을 필터링하려면 _TABLE_SUFFIX에 값 0과 4를 사용합니다.

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
  AND ( _TABLE_SUFFIX = '0'
    OR _TABLE_SUFFIX = '4' )
ORDER BY
  max DESC

_TABLE_SUFFIX를 사용하면 스캔되는 바이트 수가 크게 감소하여 쿼리 실행 비용이 절감됩니다.

그러나 상수 표현식이 없는 조건이 포함된 _TABLE_SUFFIX의 필터는 와일드 카드 테이블에서 검색되는 테이블 수를 제한하지 않습니다. 예를 들어 다음 쿼리는 필터가 table_id 열의 동적 값을 사용하므로 와일드 카드 테이블 bigquery-public-data.noaa_gsod.gsod19*에 대해 검색되는 테이블을 제한하지 않습니다.

#standardSQL
# Scans all tables that match the prefix `gsod19`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

또 다른 예시로, 다음 쿼리는 상수 표현식이기 때문에 첫 번째 필터 조건 _TABLE_SUFFIX BETWEEN '40' and '60'에 따라 검색을 제한합니다. 그러나 다음 쿼리는 동적 표현식이기 때문에 두 번째 필터 조건 _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1) FROM bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'gsod194%')에 따라 검색을 제한하지 않습니다.

#standardSQL
# Scans all tables with names that fall between `gsod1940` and `gsod1960`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX BETWEEN '40' AND '60'
  AND _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

이 문제를 해결하려면 대신 다음과 같이 두 가지 개별 쿼리를 수행해야 합니다.

첫 번째 쿼리:

#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%'

두 번째 쿼리:

#standardSQL
# Construct the second query based on the values from the first query
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = '49'

이 예시 쿼리는 INFORMATION_SCHEMA.TABLES 뷰를 사용합니다. INFORMATION_SCHEMA 테이블에 대한 자세한 내용은 INFORMATION_SCHEMA를 사용하여 테이블 메타데이터 가져오기를 참조하세요.

_TABLE_SUFFIX를 사용하여 테이블 범위 스캔

일정한 범위의 테이블을 스캔하려면 _TABLE_SUFFIX 유사 열과 함께 BETWEEN 절을 사용합니다. 예를 들어, 1929년에서 1935년 사이에 보고된 최고 온도를 찾으려면 테이블 와일드 카드를 사용하여 연도의 마지막 두 자리를 나타냅니다.

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29' and '35'
ORDER BY
  max DESC

_PARTITIONTIME을 사용하여 수집 시간으로 파티션을 나눈 일정 범위의 테이블 스캔

수집 시간으로 파티션을 나눈 일정 범위의 테이블을 스캔하려면 _PARTITIONTIME 유사 열을 _TABLE_SUFFIX 유사 열과 함께 사용합니다. 예를 들어 다음 쿼리는 my_dataset.mytable_id1 테이블에서 2017년 1월 1일 파티션을 검색합니다.

#standardSQL
SELECT
  field1,
  field2,
  field3
FROM
  `my_dataset.mytable_*`
WHERE
  _TABLE_SUFFIX = 'id1'
  AND _PARTITIONTIME = TIMESTAMP('2017-01-01')

데이터세트의 모든 테이블 쿼리하기

데이터 세트의 모든 테이블을 스캔하려면 빈 프리픽스와 테이블 와일드 카드를 사용합니다. 즉, _TABLE_SUFFIX 유사 열에 전체 테이블 이름이 포함되어 있습니다. 예를 들어 다음 FROM 절은 GSOD 데이터세트의 모든 테이블을 스캔합니다.

FROM
  `bigquery-public-data.noaa_gsod.*`

빈 프리픽스를 사용하면 _TABLE_SUFFIX 유사 열에 전체 테이블 이름이 포함됩니다. 예를 들어 다음 쿼리는 1929년과 1935년 사이의 최고 온도를 찾는 예시와 동일하지만 WHERE 절에 전체 테이블 이름을 사용합니다.

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN 'gsod1929' and 'gsod1935'
ORDER BY
  max DESC

하지만 프리픽스가 길수록 일반적으로 성능이 향상됩니다. 자세한 내용은 권장사항을 참조하세요.

쿼리 실행 세부정보

쿼리 평가에 사용되는 스키마

와일드 카드 테이블을 사용하는 GoogleSQL 쿼리를 실행하기 위해 BigQuery는 자동으로 해당 테이블의 스키마를 유추합니다. BigQuery는 와일드 카드와 일치하는 가장 최근에 생성된 테이블의 스키마를 와일드카드 테이블의 스키마로 사용합니다. WHERE 절의 _TABLE_SUFFIX 유사 열을 사용하여 와일드 카드 테이블에서 사용하려는 테이블 수를 제한하더라도 BigQuery는 와일드 카드와 일치하는 가장 최근에 생성된 테이블의 스키마를 사용합니다.

추론된 스키마의 열이 일치하는 테이블에 존재하지 않으면 BigQuery는 해당 열이 없는 테이블의 행에서 해당 열에 대한 NULL 값을 반환합니다.

와일드 카드 쿼리와 일치하는 테이블에서 스키마가 일치하지 않으면 BigQuery에서 오류를 반환합니다. 이 경우는 일치하는 테이블의 열에 다른 데이터 유형이 있거나, 일치하는 모든 테이블에 없는 열이 null 값을 가진 것으로 가정할 수 없는 경우입니다.

권장사항

긴 프리픽스가 짧은 프리픽스보다 일반적으로 성능이 우수합니다. 예를 들어 다음 쿼리는 긴 프리픽스(gsod200)를 사용합니다.

#standardSQL
SELECT
max
FROM
`bigquery-public-data.noaa_gsod.gsod200*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN '0' AND '1'
ORDER BY
max DESC

다음 쿼리는 빈 접두사를 사용하기 때문에 일반적으로 성능이 더 낮습니다.

#standardSQL
SELECT
max
FROM
`bigquery-public-data.noaa_gsod.*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN 'gsod2000' AND 'gsod2001'
ORDER BY
max DESC

파티션을 나눈 테이블이 성능이 더 우수하므로 샤딩보다 파티션 나누기를 권장합니다. 샤딩은 관리할 테이블을 더 만드는 동시에 성능을 저하시킵니다. 자세한 내용은 파티션 나누기와 샤딩 비교를 참조하세요.

BigQuery의 비용 관리 권장사항은 BigQuery의 비용 관리를 참조하세요.

다음 단계

GoogleSQL에 대한 자세한 내용은 GoogleSQL 쿼리 참조를 확인하세요.