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

와일드 카드 테이블을 사용하면 간단한 SQL 문으로 여러 테이블을 쿼리할 수 있습니다. 와일드 카드 테이블은 표준 SQL에서만 사용할 수 있습니다. 이전 SQL에서의 동등한 기능은 테이블 와일드 카드 기능을 참조하세요.

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

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

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

와일드 카드 테이블 구문에 대한 자세한 내용은 표준 SQL 참조의 와일드 카드 테이블을 참조하세요.

제한사항

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

  • 와일드 카드 테이블은 뷰를 지원하지 않습니다. 와일드 카드 테이블이 데이터세트의 뷰와 일치하면 쿼리에서 오류를 반환합니다 이것은 뷰를 필터링하기 위해 _TABLE_SUFFIX 유사 열에 WHERE 절이 포함되어 있는지에 상관없이 true입니다.
  • 현재는 Use Cached Results(캐시 처리된 결과 사용) 옵션을 선택하더라도 와일드 카드를 사용하는 여러 테이블을 대상으로 하는 쿼리에서 캐시된 결과가 지원되지 않습니다. 동일한 와일드 카드 쿼리를 여러 번 실행하면 각 쿼리마다 비용이 청구됩니다.
  • 와일드 카드 테이블은 네이티브 BigQuery 저장소만 지원합니다. 외부 테이블 또는 를 쿼리할 때는 와일드 카드를 사용할 수 없습니다.
  • 데이터 조작 언어(DML) 문을 포함하는 쿼리는 와일드 카드 테이블을 쿼리 대상으로 사용할 수 없습니다. 예를 들어 와일드 카드 테이블은 UPDATE 쿼리의 FROM 절에서 사용할 수 있지만 UPDATE 작업의 대상으로 사용할 수 없습니다.

시작하기 전에

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

와일드 카드 테이블은 데이터세트에 호환 가능한 스키마가 있고 이름이 유사한 테이블이 여러 개 포함된 경우에 유용합니다. 일반적으로 이러한 데이터세트에는 각각 하루, 월 또는 연도의 데이터를 나타내는 테이블이 포함됩니다. 예를 들어 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 저장소만 지원합니다. 외부 테이블 또는 를 쿼리할 때는 와일드 카드를 사용할 수 없습니다.

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

와일드 카드 테이블을 사용하면 여러 테이블을 간단하게 쿼리할 수 있습니다. 예를 들어 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에 값 04를 사용합니다.

#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에 대한 필터는 와일드 카드 테이블을 찾기 위해 스캔되는 테이블의 수를 제한하는 데 사용할 수 없습니다. 예를 들어, 다음 쿼리는 와일드 카드 테이블을 찾기 위해 스캔되는 테이블을 제한하지 않습니다. 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_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id LIKE 'gsod194%')

다음 쿼리는 필터 조건 _TABLE_SUFFIX BETWEEN '40' and '60'을 기준으로 스캔을 제한하지만, 하위 쿼리와 관련된 조건에 따라 스캔을 제한하지는 않습니다.

#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_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id LIKE 'gsod194%')

하위 쿼리와 관련된 조건을 기준으로 제한하려면 별도의 조회 2개를 수행합니다.

첫 번째 쿼리:

#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id 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'

_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

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

쿼리 실행 세부정보

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

와일드 카드 테이블을 사용하는 표준 SQL 쿼리를 실행하기 위해 BigQuery는 자동으로 해당 테이블의 스키마를 유추합니다. BigQuery는 와일드 카드와 일치하는 가장 최근에 생성된 테이블의 스키마를 와일드카드 테이블의 스키마로 사용합니다. 와일드 카드 테이블과 일치하는 테이블에서 스키마가 일치하지 않으면 BigQuery에서 오류를 반환합니다.

권장사항

긴 프리픽스가 짧은 프리픽스보다 일반적으로 성능이 우수합니다. 예를 들어 다음 쿼리는 긴 프리픽스(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

다음 단계

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

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

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