[Bigquery] Resources exceeded during query execution: The query could not be executed in the allotted memory 해결 방법

원인

bigquery에서 쿼리를 실행하면 아래와 같은 오류가 발생하는 경우가 종종 있습니다.

Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: 140% of limit.
Top memory consumer(s):
ORDER BY operations: 98%
other/unattributed: 2%

bigquery는 분산 데이터베이스 기반으로 쿼리 대부분이 여러 노드에서 실행되지만 계산이 필요한 특정 명령어의 경우에는 단일 노드에서 실행이 되어야 합니다. 이 때, 단일 노드에서 너무 많은 데이터를 처리하려 하면 위와 같이 오류가 발생합니다.

• bigquery는 실패한 쿼리에 대해 요금이 청구되지 않습니다.

단일 노드에서 처리되어야 하는 함수나 쿼리절은 아래와 같습니다.

원인 operations

partition 되지 않은 window function

rank() over() 나 row_number() over() 와 같이 partition by가 지정되지 않으면 단일 노드에서 실행됩니다.

order by

쿼리에 order by 절이 있으면 단일 노드에서 모든 데이터를 정렬하려 합니다.

해결 방법

window function

partition by 를 줄 수 있으면 추가합니다.

예를 들어, row_number() over()를 id로 사용하여 각 행에 시퀀스 번호를 생성하는 경우에는 partition by를 추가합니다.

SELECT ROW_NUMBER() OVER(ORDER BY eventdate) AS STRING) as id

에서

SELECT ROW_NUMBER() OVER(PARTITION BY eventdate) AS STRING) as id

만약 각 행에 고유한 id값이 필요하다면 아래와 같은 조합으로 사용할 수도 있습니다.

SELECT CONCAT(CAST(ROW_NUMBER() OVER(PARTITION BY eventdate) AS STRING),'|',  (CAST(eventdate AS STRING))) as id

쿼리를 분리하고 임시 테이블 사용

쿼리에 order by 절을 제거하는 것이 문제를 해결하는 가장 간단하고 확실한 방법이지만 어쩔 수 없이 해야만 한다면 쿼리를 분리하는 것이 좋습니다. 또한 분리한 쿼리 결과를 임시 테이블에 저장하고 해당 테이블에 접근하는 형식으로 해야 해결할 수 있습니다.

SELECT
    product_id
  , ARRAY_AGG(STRUCT (url, height, width) IGNORE NULLS) AS images
FROM
    (
        SELECT
            product_id
          , CONCAT("사이트", url) as url
          , width
          , height
        FROM
            `테이블`
        WHERE
              image_type = 'TITLE'
          AND is_deleted = false
        ORDER BY product_id, image_type, image_id
    )
GROUP BY
    product_id

위와 같은 쿼리에서 메모리 문제가 발생했다면 아래와 같이 처리하여 문제를 피할 수 있습니다.

CREATE TEMP TABLE image_table
(
    product_id      INT64,
    url    STRING,
    width  INT64,
    height INT64
) AS
SELECT
    product_id
  , CONCAT("사이트", url) as url
  , width
  , height
FROM
    `테이블`
WHERE
      image_type = 'TITLE'
  AND is_deleted = false
ORDER BY product_id, image_type, image_id
;

SELECT
    product_id
  , ARRAY_AGG(STRUCT (url, height, width) IGNORE NULLS) AS images
FROM
    image_table
GROUP BY
    product_id
;

inner query를 임시 테이블에 저장하여 단일 노드로만 실행되는 데이터의 수를 줄이고 저장된 임시테이블에서 다시 데이터를 꺼내와 쿼리하는 형식으로 변경한 케이스입니다.

임시 테이블이므로 1개의 세션에서 실행해야 특별한 문제없이 접근할 수 있습니다.

임시 테이블에 대한 정보는 여기에서 확인할 수 있습니다.

쿼리 세부 정보

SELECT
    product_id
  , ARRAY_AGG(STRUCT (url, height, width) IGNORE NULLS) AS images
FROM
    (
        SELECT
            product_id
          , url
          , width
          , height
        FROM
            `테이블`
        WHERE
              image_type = 'TITLE'
          AND is_deleted = false
        ORDER BY product_id, image_type, image_id
    )
GROUP BY
    product_id

위 쿼리에 대해 분석을 진행해 봅니다. PostgreSQL의 경우, 쿼리에 대해 어떻게 실행이 되는지 explain 할 수 있는 것처럼 bigquery 또한 세부 정보를 확인할 수 있습니다.

첫 번째 S00: INPUT의 경우, ORDER BY를 처리하지 않고 WHERE 절만 처리한 다음, 단계를 넘기는 것을 볼 수 있습니다.

READ:    
$1:image_id, $2:item_id, $3:image_type, $4:url, $5:with, $6:height, $7:is_deleted
FROM 테이블
WHERE and(equal($3, 'TITLE'), equal($7, false))

WRITE:    
$1, $2, $3, $4, $5, $6
TO __stage00_output

다음 실패한 S01: OUTPUT 의 경우, aggregate, compute, sort 전부 처리하는 것을 볼 수 있습니다.

READ:    
$1, $2, $3, $4, $5, $6
FROM __stage00_output
AGGREGATE:    GROUP BY $60 := $50
$30 := ARRAY_AGG($40)
COMPUTE    :
$40 := MAKE_STRUCT(concat('사이트', $51), $53, $52, 1)
SORT    
$2 ASC, $3 ASC, $1 ASC
WRITE    :
$60, $30
TO __stage01_output
BY HASH($60)

이를 쿼리 분리, temp table 사용하는 버전으로 바꾸면 아래와 같은 실행 계획이 나옵니다.

첫 번째 작업은 temp table에 저장한 작업이고 두 번째는 temp table에서 데이터를 읽어와 조회한 작업입니다.