BigQuery 상관 하위 쿼리 및 UDF 제한 사항 해결: 실용 가이드

BigQuery UDF 최적화: 상관 하위 쿼리 문제 해결

BigQuery UDF에 최적화된 배열 처리와 함께 표준 SQL을 사용하는 솔루션

CREATE OR REPLACE FUNCTION my.gcp.optimized_function(ip_start_date TIMESTAMP, ip_end_date TIMESTAMP)
RETURNS NUMERIC AS ((
WITH temp_date AS (
  SELECT
    CASE
      WHEN ip_start_date > ip_end_date THEN DATE(ip_end_date)
      ELSE DATE(ip_start_date)
    END AS ip_date_01,
    CASE
      WHEN ip_start_date > ip_end_date THEN DATE(ip_start_date)
      ELSE DATE(ip_end_date)
    END AS ip_date_02
),
holiday_array AS (
  SELECT ARRAY_AGG(STRUCT(DATE(cal_date) AS cal_date, holiday_flag)) AS holidays
  FROM dataset.staff_time
),
working_days AS (
  SELECT
    CASE
      WHEN DATE(ip_start_date) <> DATE(ip_end_date) THEN
        SUM(CASE
          WHEN cal_date NOT IN (SELECT cal_date FROM UNNEST(holiday_array.holidays)) THEN 1
          ELSE 0
        END)
      ELSE 
    END AS working_day
  FROM UNNEST(GENERATE_DATE_ARRAY(ip_start_date, ip_end_date, INTERVAL 1 DAY)) AS cal_date
  WHERE cal_date NOT IN (SELECT cal_date FROM UNNEST(holiday_array.holidays))
),
SELECT working_day
FROM working_days));

하위 쿼리 조인으로 BigQuery UDF 상관 관계 오류 처리

LEFT JOIN을 사용하고 배열 데이터를 처리하여 하위 쿼리 문제를 최소화하는 솔루션

CREATE OR REPLACE FUNCTION my.gcp.function_v2(ip_start_date TIMESTAMP, ip_end_date TIMESTAMP)
RETURNS NUMERIC AS ((
WITH temp_date AS (
  SELECT
    CASE
      WHEN ip_start_date > ip_end_date THEN DATE(ip_end_date)
      ELSE DATE(ip_start_date)
    END AS ip_date_01,
    CASE
      WHEN ip_start_date > ip_end_date THEN DATE(ip_start_date)
      ELSE DATE(ip_end_date)
    END AS ip_date_02
),
holiday_array AS (
  SELECT ARRAY_AGG(STRUCT(DATE(cal_date) AS cal_date, holiday_flag)) AS holidays
  FROM dataset.staff_time
),
working_days AS (
  SELECT
    CASE
      WHEN DATE(ip_start_date) <> DATE(ip_end_date) THEN
        SUM(CASE
          WHEN ot.cal_date IS  AND EXTRACT(DAYOFWEEK FROM cal_date) NOT IN (1, 7) THEN 1
          ELSE 0
        END)
      ELSE 
    END AS working_day
  FROM UNNEST(GENERATE_DATE_ARRAY(SAFE_CAST(ip_start_date AS DATE),
  DATE_SUB(SAFE_CAST(ip_end_date AS DATE), INTERVAL 1 DAY), INTERVAL 1 DAY)) AS cal_date
  LEFT JOIN holiday_array ot
  ON cal_date = ot.cal_date
  WHERE ot.cal_date IS 
    AND EXTRACT(DAYOFWEEK FROM cal_date) NOT IN (1, 7)
),
SELECT working_day
FROM working_days));

BigQuery UDF 및 상관 하위 쿼리에 대해 자주 묻는 질문(FAQ)

1. BigQuery에서 상관 서브 쿼리 오류를 방지하려면 어떻게 해야 하나요?
2. 상관된 하위 쿼리 오류를 방지하려면 쿼리를 재구성하여 사용해보세요. ARRAY_AGG() 그리고 UNNEST() 함수를 사용하거나 데이터를 사전 집계하여 UDF 내부 조인의 필요성을 줄입니다.
3. 외부 테이블을 참조할 때 BigQuery UDF가 느린 이유는 무엇인가요?
4. BigQuery UDF는 특히 상관 서브 쿼리에서 외부 테이블을 반복적으로 참조하면 속도가 느려집니다. 이 문제를 해결하려면 중요한 데이터를 임시 테이블에 저장하거나 캐싱 메커니즘을 사용하여 쿼리 오버헤드를 줄이세요.
5. 역할은 무엇입니까? SAFE_CAST() BigQuery UDF에서?
6. 그만큼 SAFE_CAST() 함수는 값을 안전하게 변환하고 변환이 실패할 경우 을 반환함으로써 잘못된 날짜 형식이나 데이터 유형이 쿼리 실패를 일으키지 않도록 보장합니다.
7. 날짜 범위 및 휴일을 처리하기 위해 UDF를 어떻게 최적화할 수 있습니까?
8. 다음과 같은 기능을 사용하세요. GENERATE_DATE_ARRAY() 날짜 범위를 처리하고 EXTRACT() 계산에서 주말이나 공휴일을 필터링합니다. 이를 통해 UDF의 근무일을 정확하게 처리할 수 있습니다.
9. 대규모 데이터세트에 BigQuery UDF를 사용할 수 있나요?
10. 예, 하지만 쿼리를 신중하게 최적화해야 합니다. 외부 테이블 참조 횟수를 최소화하고 다음과 같은 효율적인 배열 기능을 사용하세요. ARRAY_AGG() 복잡한 데이터 구조를 처리하기 위해

BigQuery UDF 최적화에 대한 최종 생각

상관 서브 쿼리는 BigQuery에서 함수를 개발할 때 주요 제한 사항 중 하나입니다. 사전 집계된 데이터, 배열 작업, 지능형 날짜 처리 등의 대체 방법을 활용하면 이러한 제한이 완화되어 쿼리 성능이 향상됩니다.

쿼리 디자인을 최적화하고 UDF 내의 외부 테이블에 대한 참조를 최소화하면 오류와 속도 저하를 크게 줄일 수 있습니다. 대규모 데이터세트를 사용하는 개발자의 경우 이러한 기술을 적용하면 BigQuery에서 더 효율적으로 보고하고 실행 문제를 줄일 수 있습니다.