Giải quyết các truy vấn con tương quan với BigQuery và các hạn chế của UDF: Hướng dẫn thực hành

UDF BigQuery và các truy vấn con tương quan: Vượt qua các thách thức

Trong quy trình xử lý dữ liệu hiện đại, BigQuery của Google Cloud Platform thường được sử dụng để xử lý các tập dữ liệu lớn và thực hiện các phép tính phức tạp. Tuy nhiên, người dùng thường gặp phải những hạn chế khi triển khai logic nghiệp vụ cụ thể thông qua Hàm do người dùng xác định (UDF) và các truy vấn phụ tương quan. Điều này có thể tạo ra thách thức, đặc biệt là khi tham chiếu các bảng động được nhân viên cập nhật thường xuyên, như trong trường hợp cờ ngày lễ hoặc dữ liệu nhạy cảm với thời gian khác.

Vấn đề về các truy vấn con tương quan trong UDF trở nên rõ ràng khi cố gắng tích hợp dữ liệu bảng thời gian thực với các phép tính kinh doanh theo ngày. Trong những trường hợp như vậy, các phép tính có thể không thành công khi có nhiều bảng và logic có điều kiện tham gia. Điều này đặc biệt có vấn đề khi các giá trị được mã hóa cứng hoạt động nhưng dữ liệu động không thành công do những hạn chế này.

Trong bài viết này, chúng ta sẽ xem xét một ví dụ cụ thể về một vấn đề trong đó UDF dùng để tính toán tổng độ trễ giữa hai ngày, tính cả ngày nghỉ và ngày không làm việc, nhưng không thành công do các hạn chế của BigQuery đối với các truy vấn phụ tương quan. Chúng tôi cũng sẽ khám phá các giải pháp tiềm năng và các phương pháp hay nhất để giải quyết vấn đề này.

Nếu bạn đang gặp phải những thách thức tương tự, hướng dẫn này sẽ cung cấp thông tin chi tiết về cách xử lý các lỗi truy vấn con tương quan và tối ưu hóa UDF của bạn trong BigQuery. Hãy cùng đi sâu vào ví dụ và khám phá cách vượt qua những rào cản phổ biến này.

Tìm hiểu UDF BigQuery và các giải pháp truy vấn con tương quan

Mục tiêu chính của các tập lệnh được cung cấp ở trên là tính toán tổng số giờ làm việc giữa hai dấu thời gian trong khi tính đến các yếu tố dành riêng cho doanh nghiệp như ngày lễ và cuối tuần. Tính toán này rất quan trọng đối với các quy trình báo cáo đo lường thời lượng công việc trong khi loại trừ những ngày không làm việc. Ở đây, Hàm do người dùng xác định (UDF) được sử dụng để gói gọn logic này trong Google BigQuery. Một trong những thách thức chính được giải quyết là giải quyết truy vấn con tương quan trong UDF, điều này có thể dẫn đến lỗi và các vấn đề về hiệu suất khi truy vấn các tập dữ liệu lớn.

Một trong những thành phần chính của tập lệnh là việc sử dụng GENERATE_DATE_ARRAY chức năng. Hàm này tạo danh sách tất cả các ngày giữa hai dấu thời gian nhất định. Bằng cách tạo phạm vi ngày, tập lệnh có thể tính toán chính xác số ngày làm việc tồn tại giữa thời gian bắt đầu và kết thúc công việc. Để lọc các ngày nghỉ và ngày cuối tuần khỏi danh sách này, tập lệnh sử dụng ARRAY_AGG chức năng lưu trữ dữ liệu ngày lễ và KHÔNG CÓ Chức năng chuyển đổi mảng thành hàng để dễ so sánh hơn.

Một phần quan trọng khác của giải pháp là xử lý dữ liệu ngày lễ. Bảng ngày lễ được nhân viên cập nhật thường xuyên, được lưu trữ thành một mảng và dùng để lọc ra bất kỳ ngày nào trùng với ngày lễ hoặc cuối tuần. Điều này đạt được bằng cách sử dụng sự kết hợp của THAM GIA TRÁI và CHIẾT XUẤT chức năng tách biệt các phần cụ thể của ngày, chẳng hạn như ngày trong tuần. Lọc ra các ngày cuối tuần (Thứ Bảy và Chủ nhật) đảm bảo rằng chỉ những ngày làm việc mới góp phần tính toán độ trễ cuối cùng.

Cuối cùng, UDF thực hiện một số xác nhận ngày tháng để đảm bảo các giá trị đầu vào ở định dạng chính xác bằng cách sử dụng SAFE_CAST chức năng. Chức năng này ngăn không cho UDF bị lỗi nếu nhập định dạng ngày không hợp lệ, cung cấp thêm một lớp bảo mật. Kết quả cuối cùng được tính bằng cách tổng hợp số ngày làm việc và điều chỉnh thời gian bắt đầu và kết thúc của một phần ngày làm việc. Cách tiếp cận này cung cấp một giải pháp linh hoạt và có thể tái sử dụng cho vấn đề phức tạp trong việc tính toán độ trễ trong BigQuery trong khi vẫn tuân thủ các giới hạn của UDF.

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));

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));

Khắc phục các hạn chế của BigQuery UDF: Tối ưu hóa hiệu suất truy vấn

Trong bất kỳ hoạt động dữ liệu quy mô lớn nào, hiệu suất và hiệu quả là điều cần thiết. Một thách thức lớn nảy sinh trong BigQuery là khả năng hạn chế của Hàm do người dùng xác định (UDF) để xử lý các truy vấn con tương quan một cách hiệu quả, đặc biệt khi UDF tham chiếu các bảng bên ngoài hoặc cần thực hiện nhiều phép nối. Những vấn đề này thường dẫn đến hiệu suất chậm hơn hoặc thậm chí xảy ra lỗi. Điều này đặc biệt có vấn đề trong trường hợp logic cần lấy dữ liệu động cập nhật thường xuyên, chẳng hạn như các bảng ngày lễ. Để khắc phục điều này, điều quan trọng là phải tìm những cách khác để cấu trúc truy vấn của bạn nhằm vượt qua những hạn chế này.

Một cách tiếp cận là giảm sự phụ thuộc vào các truy vấn con tương quan bằng cách sử dụng các phép tính trung gian hoặc dữ liệu được lưu vào bộ nhớ đệm trước thời hạn. Ví dụ: thay vì tham chiếu bảng ngày lễ nhiều lần trong hàm của bạn, hãy cân nhắc việc lưu trữ thông tin ngày lễ ở định dạng dễ truy cập hơn, như mảng tổng hợp hoặc bảng tạm thời. Điều này giảm thiểu nhu cầu tham gia thời gian thực trong quá trình thực thi UDF của bạn. Hơn nữa, tận dụng hàm mảng giống ARRAY_AGG() Và UNNEST() đảm bảo rằng bạn có thể xử lý các cấu trúc dữ liệu phức tạp mà không bị ảnh hưởng về hiệu suất liên quan đến các truy vấn con lặp lại.

Một chiến lược khác liên quan đến việc sử dụng BigQuery SAFE_CAST() để xử lý các vấn đề định dạng tiềm ẩn một cách khéo léo, vì điều này ngăn ngừa các lỗi truy vấn không cần thiết. Bằng cách đảm bảo tính ổn định của dữ liệu đầu vào và xử lý lỗi nội bộ, bạn có thể ngăn chặn các sự cố thời gian chạy có thể khiến UDF của bạn bị lỗi. Ngoài ra, hãy luôn cân nhắc xem liệu một phép tính cụ thể có thể được đơn giản hóa hoặc giảm tải bên ngoài UDF để hợp lý hóa quá trình xử lý hay không. Các phương pháp như vậy đảm bảo rằng UDF của bạn chạy hiệu quả hơn trong khi vẫn tuân thủ các giới hạn của môi trường thực thi của BigQuery.