파티션을 나눈 테이블 쿼리

파티션을 나눈 테이블 쿼리

다음 방법으로 파티션을 나눈 테이블을 쿼리할 수 있습니다.

  • GCP Console 또는 기본 BigQuery 웹 UI 사용
  • 명령줄 도구의 bq query 명령어 사용
  • jobs.insert API 메소드 호출 및 쿼리 작업 구성

쿼리 실행에 대한 자세한 내용은 양방향 및 일괄 쿼리 실행을 참조하세요.

필수 권한

데이터세트 수준에서 파티션을 나눈 테이블을 쿼리하려면 테이블이 포함된 데이터세트에 대한 READER 액세스 권한이 필요합니다.

데이터세트 수준의 권한을 사용하는 대신 bigquery.tables.getData 권한을 포함한 프로젝트 수준의 IAM 역할을 활용할 수 있습니다. bigquery.tables.getData 권한은 쿼리 중인 테이블의 데이터를 읽을 때 필요합니다.

사전 정의된 프로젝트 수준의 모든 IAM 역할에는 bigquery.user, bigquery.jobUser, bigquery.metadataViewer제외한 bigquery.tables.getData 권한이 포함됩니다.

쿼리 작업을 실행하려면 bigquery.jobs.create 권한도 부여받아야 합니다. 아래의 사전 정의된 프로젝트 수준 IAM 역할에는 bigquery.jobs.create 권한이 있습니다.

BigQuery에서 IAM 역할 및 권한에 대한 자세한 내용은 액세스 제어를 참조하세요. 데이터세트 수준 역할에 대한 자세한 내용은 데이터세트 기본 역할을 참조하세요.

파티션을 나눈 내부 데이터화-시간 테이블 유사 열

파티션을 나눈 내부 데이터화-시간 테이블을 만들면 2개의 유사 열, 즉 _PARTITIONTIME 유사 열 및 _PARTITIONDATE 유사 열이 테이블에 추가됩니다. _PARTITIONTIME 유사 열에는 테이블에 로드된 데이터의 날짜 기반 타임스탬프가 포함됩니다. _PARTITIONDATE 유사 열에는 날짜 표시가 포함됩니다. 두 유사 열 이름 모두 예약되어 있으므로 테이블에 해당 이름을 사용한 열을 만들 수 없습니다.

_PARTITIONTIME_PARTITIONDATE는 파티션을 나눈 내부 데이터화-시간 테이블에서만 사용할 수 있습니다. 파티션을 나눈 테이블에는 유사 열이 없습니다. 파티션을 나눈 테이블의 쿼리에 대한 자세한 내용은 파티션을 나눈 테이블 쿼리를 참조하세요.

_PARTITIONTIME 유사 열

_PARTITIONTIME 유사 열에는 UTC 시간을 기반으로 한 타임스탬프가 포함되며 UNIX 기점을 기준으로 하는 마이크로초 단위의 숫자가 표시됩니다. 예를 들어 2016년 4월 15일에 데이터가 테이블에 추가된 경우 해당 날짜에 추가된 모든 데이터 행의 _PARTITIONTIME 열에 TIMESTAMP("2016-04-15") 값이 포함됩니다.

_PARTITIONTIME 유사 열을 쿼리하려면 별칭을 사용해야 합니다. 예를 들어 다음 쿼리에서는 별칭 pt를 유사 열에 할당해 _PARTITIONTIME을 선택합니다.

SELECT
  _PARTITIONTIME AS pt,
  [COLUMN]
FROM
  [DATASET].[TABLE]

각 항목의 의미는 다음과 같습니다.

  • [COLUMN]은 쿼리할 열의 이름입니다. 쉼표로 구분된 목록을 사용해 여러 열을 지정할 수 있습니다.
  • [DATASET]는 파티션을 나눈 테이블이 포함된 데이터세트입니다.
  • [TABLE]은 파티션을 나눈 테이블입니다.

스트리밍 버퍼의 데이터에는 _PARTITIONTIME 열에 NULL 값이 있습니다.

_PARTITIONDATE 유사 열

_PARTITIONDATE 유사 열에는 _PARTITIONTIME 유사 열의 값에 해당하는 UTC 날짜가 포함됩니다.

_PARTITIONDATE 유사 열을 쿼리하려면 별칭을 사용해야 합니다. 예를 들어 다음 쿼리에서는 pd를 유사 열에 할당해 _PARTITIONDATE를 선택합니다.

SELECT
  _PARTITIONDATE AS pd,
  [COLUMN]
FROM
  [DATASET].[TABLE]

각 항목의 의미는 다음과 같습니다.

  • [COLUMN]은 쿼리할 열의 이름입니다. 쉼표로 구분된 목록을 사용해 여러 열을 지정할 수 있습니다.
  • [DATASET]는 파티션을 나눈 테이블이 포함된 데이터세트입니다.
  • [TABLE]은 파티션을 나눈 테이블입니다.

스트리밍 버퍼의 데이터에는 _PARTITIONDATE 열에 NULL 값이 있습니다.

유사 열을 사용한 파티션을 나눈 내부 데이터화-시간 테이블 쿼리

파티션을 나눈 내부 데이터화-시간 테이블의 데이터를 쿼리할 때는 _PARTITIONTIME 또는 _PARTITIONDATE 유사 열에 값을 지정해 특정 파티션을 참조합니다. 예를 들면 다음과 같습니다.

  • _PARTITIONTIME >= "2018-01-29 00:00:00" AND _PARTITIONTIME < "2018-01-30 00:00:00"
  • _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')

또는

  • _PARTITIONDATE >= "2018-01-29" AND _PARTITIONDATE < "2018-01-30"
  • _PARTITIONDATE BETWEEN '2016-01-01' AND '2016-01-02'

유사 열을 사용한 쿼리되는 파티션 제한

_PARTITIONTIME_PARTITIONDATE 유사 열을 사용해 쿼리 중에 검색되는 파티션의 수를 제한합니다. 이를 파티션 프루닝이라고도 합니다. 파티션 프루닝은 입력 검색에서 불필요한 파티션을 제거하기 위해 BigQuery에서 사용하는 메커니즘입니다. 쿼리에서 검색되는 바이트 수를 계산할 때 프루닝을 거친 파티션은 포함되지 않아 주문형 분석 비용이 절감됩니다. 일반적으로 파티션 프루닝 시 하위 쿼리 평가 또는 데이터 검색 없이 쿼리 초기에 필터를 평가할 수 있어 쿼리 비용이 줄어듭니다.

예를 들어 다음 쿼리는 파티션을 나눈 테이블에서 2016년 1월 1일부터 2016년 1월 2일 사이의 파티션만 검색합니다.

_PARTITIONTIME

SELECT
  [COLUMN]
FROM
  [DATASET].[TABLE]
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01')
  AND TIMESTAMP('2016-01-02')

_PARTITIONDATE

SELECT
  [COLUMN]
FROM
  [DATASET].[TABLE]
WHERE
  _PARTITIONDATE BETWEEN '2016-01-01'
  AND '2016-01-02'

파티션 프루닝의 예

이 예에서는 하위 쿼리에서 유사 열 필터를 사용해 검색되는 파티션의 수를 제한하는 것을 보여줍니다.

_PARTITIONTIME

SELECT
  [COLUMN1],
  [COLUMN2]
FROM (
  SELECT
    [COLUMN1],
    [COLUMN2]
  FROM
    [DATASET].[TABLE]
  WHERE
    _PARTITIONTIME = TIMESTAMP('2016-03-28')) t1
CROSS JOIN
  [DATASET].[TABLE2] t2
WHERE
  t1.[COLUMN2] = "one"

_PARTITIONDATE

SELECT
  [COLUMN1],
  [COLUMN2]
FROM (
  SELECT
    [COLUMN1],
    [COLUMN2]
  FROM
    [DATASET].[TABLE]
  WHERE
    _PARTITIONDATE = '2016-03-28') t1
CROSS JOIN
  [DATASET].[TABLE2] t2
WHERE
  t1.[COLUMN2] = "one"

다음 쿼리에서는 필터 조건, _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')에 따라 일부 파티션을 제한합니다. 하위 쿼리와 관련된 조건으로 인한 제한은 없습니다.

_PARTITIONTIME

SELECT
  [COLUMN]
FROM
  [DATASET].[TABLE2]
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')
  AND _PARTITIONTIME = (SELECT MAX(timestamp) from [DATASET].[TABLE1])

_PARTITIONDATE

SELECT
  [COLUMN]
FROM
  [DATASET].[TABLE2]
WHERE
  _PARTITIONDATE BETWEEN '2017-01-01' AND '2017-03-01'
  AND _PARTITIONDATE = (SELECT MAX(date) from [DATASET].[TABLE1])

모든 파티션을 검색하는 유사 열 쿼리

다음 예에서는 유사 열을 사용하지만 파티션을 나눈 시간 테이블의 모든 파티션을 검색합니다.

이전 SQL에서는 테이블 이름과 최대한 가깝게 지정된 경우에만 _PARTITIONTIME 필터가 작동합니다. 예를 들어 다음 쿼리는 _PARTITIONTIME 필터가 있어도 table1의 모든 파티션을 검색합니다.

#legacySQL
# Scans all partitions on t1
SELECT
  t1.field1,
  t2.field1
FROM
  mydataset.table2 t1
CROSS JOIN
  mydataset.table2 t2
WHERE
  t1._PARTITIONTIME = TIMESTAMP('2016-03-28')
  AND t1.field2 = "one"

_PARTITIONTIME 필터에 다른 열을 포함시키면 안 됩니다. 예를 들어 다음 쿼리에서는 field2가 테이블의 열이며 BigQuery에서 선택할 파티션을 미리 결정할 수 없기 때문에 검색되는 파티션이 제한되지 않습니다.

# Scans all partitions of table2
SELECT
  field1
FROM
  mydataset.table2
WHERE
  _PARTITIONTIME + field2 = TIMESTAMP('2016-03-28');

하위 쿼리를 포함하는 _PARTITIONTIME의 필터로는 파티션을 나눈 테이블에서 검색되는 파티션 수를 제한할 수 없습니다. 예를 들어 다음 쿼리는 mydataset.table2 테이블에서 검색되는 파티션을 제한하지 않습니다.

# Scans all partitions of table2
SELECT
  field1
FROM
  mydataset.table2
WHERE
  _PARTITIONTIME = (SELECT MAX(timestamp) FROM mydataset.table1)

유사 열을 통한 성능 향상

쿼리 성능을 개선하려면 비교 왼쪽에 _PARTITIONTIME 유사 열을 단독으로 사용합니다. 예를 들어 다음 두 쿼리에서는 동일량의 데이터를 처리하지만 두 번째 예에서 보다 나은 성능을 제공할 수 있습니다.

예 1: 다음 쿼리는 유사 열 값과 WHERE 필터의 다른 연산을 결합하기 때문에 속도가 더 느릴 수 있습니다.

표준 SQL

#standardSQL
/* Can be slower */
SELECT
  field1
FROM
  mydataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15")

이전 SQL

#legacySQL
/* Can be slower */
SELECT
  field1
FROM
  mydataset.table1
WHERE
  DATE_ADD(_PARTITIONTIME, 5, "DAY") > TIMESTAMP("2016-04-15")

예 2: 다음 쿼리는 필터 비교 왼쪽에 유사 열을 단독으로 배치하여 보다 우수한 성능을 제공할 수 있습니다.

표준 SQL

#standardSQL
/* Often performs better */
SELECT
  field1
FROM
  mydataset.table1
WHERE
  _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY)

이전 SQL

#legacySQL
/* Often performs better */
SELECT
  field1
FROM
  mydataset.table1
WHERE
  _PARTITIONTIME > DATE_ADD(TIMESTAMP('2016-04-15'), -5, "DAY")

테이블 크기에 따라 달라지지만 > 비교 연산자 왼쪽에 _PARTITIONTIME을 단독으로 배치한 두 번째 쿼리가 첫 번째 쿼리보다 우수한 성능을 제공할 수 있습니다. 두 쿼리에서 처리하는 데이터 양이 같으므로 청구되는 바이트 수 역시 두 경우 모두 동일합니다.

와일드 카드 테이블을 사용한 파티션을 나눈 내부 데이터화-시간 테이블 쿼리

유사 열을 사용하면 쿼리 중에 검색되는 파티션 수를 제한하는 것 외에도 와일드 카드 테이블을 통해 파티션을 나눈 테이블을 여러 개 쿼리할 수도 있습니다. 파티션을 나눈 테이블의 와일드 카드 테이블 사용에 대한 자세한 내용은 _PARTITIONTIME을 사용한 파티션을 나눈 여러 테이블 검색을 참조하세요.

시간대를 사용한 파티션을 나눈 내부 데이터화-시간 테이블 쿼리

_PARTITIONTIME의 값은 필드가 채워지는 UTC 날짜를 기반으로 합니다. 즉, 오전 12시 UTC를 기준으로 파티션을 나눕니다. UTC 외의 시간대를 기준으로 데이터를 쿼리하려면 데이터를 테이블에 로드하기 전에 다음 옵션 중 하나를 선택해야 합니다.

UTC외의 커스텀 시간대를 사용해 파티션을 나눈 데이터의 데이터를 쿼리하는 방법에는 2가지가 있습니다. 별도의 타임스탬프 열을 만들거나 파티션 데코레이터를 사용해 데이터를 특정 파티션에 로드하면 됩니다.

타임스탬프 열을 사용하는 경우 기본 UTC 기반 파티션 나누기를 사용하고 SQL 쿼리의 시간대 차이를 설명하면 됩니다. 또는 UTC 외의 시간대를 기준으로 그룹화된 파티션이 필요하다면 파티션 데코레이터를 사용해 다른 시간대를 기반으로 한 파티션에 데이터를 로드하면 됩니다.

타임스탬프 열을 사용한 시간대 쿼리

타임스탬프를 사용해 시간대에 맞게 조정하려는 경우 별도의 열을 만들어 타임스탬프를 저장하면 시간 또는 분 단위로 행을 처리할 수 있습니다.

UTC 외의 시간대를 기반으로 하는 데이터를 쿼리하려면 _PARTITIONTIME 유사 열과 커스텀 타임스탬프 열을 모두 사용합니다. _PARTITIONTIME을 사용하면 테이블 검색이 관련 파티션으로 제한되며 커스텀 타임스탬프를 통해 추가로 결과가 시간대로 제한됩니다. 예를 들어 2016-05-01 08:00:00 PST에서 2016-05-05 14:00:00 PST 사이에 추가된 데이터를 대상으로 타임스탬프 필드 [MY_TIMESTAMP_FIELD]를 사용해 파티션을 나눈 테이블(mydataset.partitioned_table)의 데이터를 쿼리해 보겠습니다.

표준 SQL

#standardSQL
SELECT
  field1
FROM
  mydataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
  AND TIMESTAMP("2016-05-06")
  AND TIMESTAMP_ADD([MY_TIMESTAMP_FIELD], INTERVAL 8 HOUR) BETWEEN TIMESTAMP("2016-05-01 12:00:00")
  AND TIMESTAMP("2016-05-05 14:00:00");

이전 SQL

#legacySQL
SELECT
  field1
FROM
  mydataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
  AND TIMESTAMP("2016-05-06")
  AND DATE_ADD([MY_TIMESTAMP_FIELD], 8, 'HOUR') BETWEEN TIMESTAMP("2016-05-01 12:00:00")
  AND TIMESTAMP("2016-05-05 14:00:00");

파티션을 나눈 내부 데이터화-시간 테이블의 유사 열을 사용한 보기 만들기

쿼리에서 읽은 데이터 양을 일정 파티션 집합으로 제한하려면 _PARTITIONTIME 또는 _PARTITIONDATE 유사 열에 필터가 포함된 보기를 만듭니다. 예를 들어 다음 쿼리를 사용하면 mydataset.partitioned_table이라는 이름의 테이블에서 최근 7일 간의 데이터만 포함하는 보기를 만들 수 있습니다.

표준 SQL

#standardSQL
SELECT
  *
FROM
  mydataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 * 24 HOUR),DAY)
  AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(),DAY);

이전 SQL

#legacySQL
SELECT
  *
FROM
  mydataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP(UTC_USEC_TO_DAY(NOW() - 7 * 60 * 60 * 24 * 1000000))
  AND TIMESTAMP(UTC_USEC_TO_DAY(CURRENT_TIMESTAMP()));

뷰 만들기에 대한 자세한 내용은 뷰 만들기를 참조하세요.

파티션을 나눈 내부 데이터화-시간 테이블의 _UNPARTITIONED_ 파티션

__UNPARTITIONED__ 파티션에서는 파티션을 나눈 테이블에 스트리밍되는 데이터를 스트리밍 버퍼에 있는 동안 일시적으로 보유합니다. 파티션을 나눈 테이블의 특정 파티션에 직접 스트리밍 되는 데이터는 __UNPARTITIONED__ 파티션을 사용하지 않습니다. 대신 이 데이터는 해당 파티션에 바로 스트리밍됩니다. 자세한 내용은 파티션을 나눈 테이블로 스트리밍을 참조하세요.

__UNPARTITIONED__ 파티션의 데이터를 쿼리하려면 _PARTITIONTIME 유사 열에 NULL 값을 사용합니다. 예를 들면 다음과 같습니다.

SELECT
  [COLUMN]
FROM
  [DATASET].[TABLE]
WHERE
  _PARTITIONTIME IS NULL

각 항목의 의미는 다음과 같습니다.

  • [COLUMN]은 쿼리할 열의 이름입니다. 쉼표로 구분된 목록을 사용해 여러 열을 지정할 수 있습니다.
  • [DATASET]는 파티션을 나눈 테이블이 포함된 데이터세트입니다.
  • [TABLE]은 파티션을 나눈 테이블입니다.

파티션을 나눈 테이블 쿼리

TIMESTAMP 또는 DATE 열을 기준으로 파티션을 나눈 테이블에는 유사 열이 없습니다. 파티션을 나눈 테이블을 쿼리할 때 검색되는 파티션 수를 제한하려면 조건부 필터(WHERE 절)를 사용합니다.

파티션 프루닝(제한)

조건부 필터는 테이블 식별자와 최대한 가깝게 표현해야 합니다. 조건부(내부 쿼리 또는 하위 쿼리 등) 해결을 위해 한 쿼리의 여러 단계를 평가해야 하는 복잡한 쿼리에서는 쿼리의 파티션을 프루닝하지 않습니다.

예를 들어 다음 쿼리에서는 파티션을 프루닝합니다.

#standardSQL
SELECT
  t1.name,
  t2.category
FROM
  table1 t1
INNER JOIN
  table2 t2
ON t1.id_field = t2 field2
WHERE
  t1.ts = CURRENT_TIMESTAMP()

다음 쿼리는 파티션을 프루닝하지 않습니다(하위 쿼리의 사용에 주목).

#standardSQL
SELECT
  t1.name,
  t2.category
FROM
  table1 t1
INNER JOIN
  table2 t2
ON
  t1.id_field = t2.field2
WHERE
  t1.ts = (SELECT timestamp from table3 where key = 2)

필터의 파티션 열 분리

필터를 표현할 때는 파티션 열을 분리해야 합니다. 계산을 위해 여러 필드의 데이터를 필요로 하는 필터에서는 파티션을 프루닝하지 않습니다. 예를 들어 파티션을 나눈 열과 두 번째 필드를 사용한 데이터 비교가 포함된 쿼리 또는 필드연결을 포함한 쿼리에서는 파티션을 프루닝하지 않습니다.

예를 들어 다음 필터는 파티션을 나눈 ts 필드 및 두 번째 필드 ts2를 사용한 계산을 요하므로 파티션을 프루닝하지 않습니다.

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

다음 단계

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

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

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