حل الاستعلامات الفرعية المرتبطة بـ BigQuery وقيود UDF: دليل عملي

BigQuery UDFs والاستعلامات الفرعية المرتبطة: التغلب على التحديات

في سير عمل معالجة البيانات الحديثة، غالبًا ما يتم استخدام BigQuery الخاص بـ Google Cloud Platform للتعامل مع مجموعات البيانات الكبيرة وإجراء عمليات حسابية معقدة. ومع ذلك، يواجه المستخدمون في كثير من الأحيان قيودًا عند تنفيذ منطق عمل محدد من خلال الوظائف المعرفة من قبل المستخدم (UDFs) والاستعلامات الفرعية المرتبطة. يمكن أن يؤدي ذلك إلى خلق تحديات، خاصة عند الرجوع إلى الجداول الديناميكية التي يتم تحديثها بانتظام بواسطة الموظفين، كما هو الحال في حالة أعلام العطلات أو البيانات الأخرى الحساسة للوقت.

تصبح مشكلة الاستعلامات الفرعية المرتبطة في UDFs واضحة عند محاولة دمج بيانات الجدول في الوقت الفعلي مع حسابات الأعمال المستندة إلى التاريخ. في مثل هذه السيناريوهات، يمكن أن تفشل العمليات الحسابية عند تضمين عدة جداول ومنطق شرطي. يعد هذا مشكلة بشكل خاص عندما تعمل القيم المضمنة، ولكن تفشل البيانات الديناميكية بسبب هذه القيود.

في هذه المقالة، سنتناول مثالًا محددًا لمشكلة حيث يُقصد من UDF حساب إجمالي التأخير بين تاريخين، مع الأخذ في الاعتبار أيام العطل وأيام العطل، ولكنه يفشل بسبب قيود BigQuery على الاستعلامات الفرعية المرتبطة. سنستكشف أيضًا الحلول المحتملة وأفضل الممارسات لمعالجة هذه المشكلة.

إذا كنت تواجه تحديات مماثلة، فسيوفر لك هذا الدليل رؤى حول التعامل مع أخطاء الاستعلامات الفرعية المرتبطة وتحسين UDFs في BigQuery. دعونا نتعمق في هذا المثال ونستكشف كيفية التغلب على هذه العوائق الشائعة.

فهم BigQuery UDFs وحلول الاستعلامات الفرعية المرتبطة

الهدف الأساسي من البرامج النصية المذكورة أعلاه هو حساب إجمالي ساعات العمل بين طابعين زمنيين مع مراعاة العناصر الخاصة بالأعمال مثل العطلات وعطلات نهاية الأسبوع. يعد هذا الحساب ضروريًا لعمليات إعداد التقارير التي تقيس فترات العمل مع استبعاد أيام عدم العمل. يتم استخدام وظيفة محددة من قبل المستخدم (UDF) هنا لتغليف هذا المنطق في Google BigQuery. أحد التحديات الرئيسية التي تم تناولها هو التعامل معها الاستعلامات الفرعية المرتبطة داخل UDFs، مما قد يؤدي إلى حدوث أخطاء ومشكلات في الأداء عند الاستعلام عن مجموعات بيانات كبيرة.

أحد المكونات الرئيسية للبرنامج النصي هو استخدام GENERATE_DATE_ARRAY وظيفة. تقوم هذه الوظيفة بإنشاء قائمة بجميع التواريخ بين طابعين زمنيين محددين. من خلال إنشاء نطاق زمني، يمكن للبرنامج النصي حساب عدد أيام العمل الموجودة بين وقت بدء المهمة وانتهائها بدقة. لتصفية العطلات وعطلات نهاية الأسبوع من هذه القائمة، يستخدم البرنامج النصي ARRAY_AGG وظيفة لتخزين بيانات العطلة و أونيست وظيفة لتحويل المصفوفات إلى صفوف لتسهيل المقارنة.

جزء مهم آخر من الحل هو التعامل مع بيانات العطلات. يتم تخزين جدول العطلات، الذي يتم تحديثه بانتظام بواسطة الموظفين، في مصفوفة ويستخدم لتصفية أي تواريخ تتزامن مع العطلات أو عطلات نهاية الأسبوع. يتم تحقيق ذلك باستخدام مزيج من الانضمام الأيسر و يستخرج وظيفة تعزل أجزاء معينة من التاريخ، مثل يوم من أيام الأسبوع. تضمن تصفية عطلات نهاية الأسبوع (السبت والأحد) أن أيام العمل فقط هي التي تساهم في حساب التأخير النهائي.

أخيرًا، يقوم UDF بإجراء بعض التحقق من التاريخ للتأكد من أن قيم الإدخال بالتنسيق الصحيح باستخدام SAFE_CAST وظيفة. تمنع هذه الوظيفة فشل UDF في حالة إدخال تنسيق تاريخ غير صالح، مما يوفر طبقة إضافية من الأمان. يتم حساب النتيجة النهائية من خلال جمع أيام العمل وضبط أوقات البدء والانتهاء في أيام العمل الجزئية. يقدم هذا الأسلوب حلاً مرنًا وقابلاً لإعادة الاستخدام للمشكلة المعقدة المتمثلة في حساب التأخيرات في BigQuery مع الالتزام بقيود 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));

التغلب على قيود BigQuery UDF: تحسين أداء الاستعلام

في أي عملية بيانات واسعة النطاق، يعد الأداء والكفاءة أمرًا ضروريًا. أحد التحديات الرئيسية التي تنشأ في BigQuery هو القدرة المحدودة لـ الوظائف المحددة من قبل المستخدم (UDF) للتعامل مع الاستعلامات الفرعية المرتبطة بكفاءة، خاصة عندما يشير UDF إلى جداول خارجية أو يحتاج إلى إجراء عمليات ربط متعددة. غالبًا ما تؤدي هذه المشكلات إلى بطء الأداء أو حتى حدوث أخطاء. يعد هذا مشكلة بشكل خاص في الحالات التي يحتاج فيها المنطق إلى سحب البيانات التي يتم تحديثها بشكل متكرر ديناميكيًا، مثل جداول العطلات. للتغلب على هذه المشكلة، من الضروري العثور على طرق بديلة لتنظيم استعلاماتك لتجاوز هذه القيود.

أحد الأساليب هو تقليل الاعتماد على الاستعلامات الفرعية المرتبطة باستخدام الحسابات المتوسطة أو تخزين البيانات مؤقتًا مسبقًا. على سبيل المثال، بدلاً من الرجوع إلى جدول العطلات عدة مرات في وظيفتك، فكر في تخزين معلومات العطلات بتنسيق يسهل الوصول إليه، مثل مصفوفة مجمعة أو جدول مؤقت. يؤدي هذا إلى تقليل الحاجة إلى عمليات الانضمام في الوقت الفعلي أثناء تنفيذ UDF الخاص بك. علاوة على ذلك، الاستفادة وظائف المصفوفة يحب ARRAY_AGG() و UNNEST() يضمن أنه يمكنك التعامل مع هياكل البيانات المعقدة دون عقوبات الأداء المرتبطة بالاستعلامات الفرعية المتكررة.

تتضمن الإستراتيجية الأخرى استخدام BigQuery SAFE_CAST() للتعامل مع مشكلات التنسيق المحتملة بأمان، حيث يمنع ذلك حالات فشل الاستعلام غير الضرورية. من خلال ضمان قوة بيانات الإدخال ومعالجة الأخطاء داخليًا، يمكنك منع مشكلات وقت التشغيل التي قد تؤدي إلى فشل UDF الخاص بك. بالإضافة إلى ذلك، ضع في اعتبارك دائمًا ما إذا كان من الممكن تبسيط عملية حسابية معينة أو إلغاء تحميلها خارج UDF لتبسيط المعالجة. تضمن هذه الأساليب تشغيل UDFs بكفاءة أكبر مع الالتزام بقيود بيئة تنفيذ BigQuery.