ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • [Bigquery] Resources exceeded during query execution: The query could not be executed in the allotted memory 해결 방법
    DB/Bigquery 2022. 1. 16. 17:06

    원인

    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에서 데이터를 읽어와 조회한 작업입니다.

    댓글