كيفية إلغاء تحميل البيانات باستخدام بنية متداخلة من Google BigQuery باستخدام مثال المعلمات المخصصة في Google Analytics

Google BigQuery هي قاعدة بيانات سحابية شهيرة تستخدمها الشركات في جميع أنحاء العالم. إنه ملائم بشكل خاص للعمل مع بيانات Google Analytics الخام: في GA 360 ، يتم تكوين التكامل مع BigQuery ببضع نقرات ، وللنسخة المجانية هناك برامج نصية ووحدات خارجية.

في البيانات الأولية لبرنامج Google Analytics ، يتوافق كل سجل (صف) مع جلسة. داخل هذا الإدخال ، توجد حقول متداخلة تتوافق مع مشاهدات الجلسة:


غالبًا ما تكون بنية البيانات المتداخلة هذه مربكة للمستخدمين الذين لا يفهمون كيفية التعامل معها وإلغاء تحميل هذه البيانات.

باستخدام المعلمات المخصصة في Google Analytics كمثال ، سأحاول شرح "على الأصابع" كيفية تخزين البيانات المضمنة في Google BigQuery وكيف يمكن تحميلها.

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

• تفريغ الصفوف
• تفريغ مع الحفاظ على هيكل التعشيش
• مثال على استبدال قيم معلمات المستخدم

الأساسيات

يدعم BigQuery لهجتي SQL: Legacy و Standard. توصي Google باستخدام معيار SQL الأحدث ، والذي سنقوم بكتابة استعلامات لتفريغه.
كل من عمل مع SQL على الأقل يعرف بناء الاستعلام القياسي:

SELECT *  * FROM *  * WHERE * * 

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


نعتبر الجداول ذات الحقول المتداخلة. هيكل مثل هذا الجدول (على سبيل المثال ، معلمات مستخدم Google Analytics):

خيارات GA مخصصة في BQ

في Google BigQuery ، سيكون لهذا الجدول أسماء الأعمدة التالية (الفاصل "." يُظهر بنية التعشيش):


إذا كيف يمكننا تفريغ البيانات من الحقول المتداخلة؟

تحميل الخط

العودة إلى الجدول مع مثال على المعلمات المخصصة GA في BQ.

تعد أعمدة customDimensions.index و customDimensions.value هي فهارس وقيم الجلسة والأبعاد المخصصة المعرفة من قبل المستخدم .

الأعمدة hits.customDimensions.index و hits.customDimensions.value هي فهارس وقيم ضرب الأبعاد المخصصة.

في Google BigQuery ، يوجد مستوى آخر من إجراءات معلمات المستخدم - المنتج. أسماء وقيم المنتج Custom Dimensions في Google BigQuery موجودة في أعمدة hits.product.customDimensions.index و hits.product.customDimensions.value . يتم تفريغها عن طريق القياس بمعلمات المستخدم المشهور ، ما عليك سوى مراعاة مستوى آخر من التعشيش.

خيارات المستخدم ومستوى الجلسة

ما الذي يجب أن نفعله إذا احتجنا إلى إلغاء تحميل قيم الأبعاد المخصصة للجلسة (المخصصة) لكل تاريخ دون حفظ البنية المتداخلة (أي سطراً سطراً)؟

للإجابة على السؤال ، دعونا نلقي نظرة فاحصة على الجدول مع معلمات المستخدم GA في BQ.
يوضح أن قيم الخلايا في عمود customDimensions هي جدول آخر :


يكفي إجراء استعلام فرعي لهذا الجدول في الاستعلام الرئيسي:

 SELECT --   date, --    value (SELECT value --   customDimensions,     t FROM t.customDimensions --       WHERE index = 1) AS customDimensions1 FROM --    t     `project.dataset.tablename` AS t 

الإخراج هو جدول:


إذا احتجنا إلى إضافة عمود بقيمة معلمة مستخدم آخر ، فإننا نقوم بإجراء استعلام فرعي آخر:

 SELECT date, (SELECT value FROM t.customDimensions WHERE index = 1) AS customDimensions1, --      customDimensions (SELECT value FROM t.customDimensions WHERE index = 2) AS customDimensions2 FROM `project.dataset.tablename` AS t 

نحصل على ما يلي:

خيارات مخصصة على مستوى ضرب

يتم إلغاء تحميل معلمات المستخدم الناجحة بنفس طريقة تحميل معلمات جلسة العمل (المستخدم) ، باستثناء أن الاستعلام الفرعي يحتاج إلى جدول مرات الدخول المتداخلة. بمعنى آخر ، فإن قيم خلايا عمود النتائج في جدول بيانات Google Analytics الخام هي جدول متداخل يتداخل فيه جدول customDimensions:


سيكون طلب تنزيل معلمات المستخدم الناصحة سطرا على النحو التالي:

 SELECT --   date, --    value (SELECT value --   customDimensions,     h FROM h.customDimensions --       WHERE index = 3) AS customDimensions3 FROM --    t     `project.dataset.tablename` AS t, --   t.hits  h     t.hits AS h 

ستكون نتيجة الاستعلام عبارة عن جدول:


يمكنك إلغاء تحميل العديد من معلمات مستخدم hit وإضافة المعلمة hitNumber (رقم تسلسل عدد مرات الدخول في الجلسة):

 SELECT date, h.hitNumber AS hitNumber, (SELECT value FROM h.customDimensions WHERE index = 3) AS customDimensions3, --        h.customDimensions (SELECT value FROM h.customDimensions WHERE index = 4) AS customDimensions4 FROM `project.dataset.tablename` AS t, t.hits AS h 

الحصول على الجدول:

الجلسة (المستخدم) + ضرب المعلمات المستخدم

إذا أردنا في استعلام واحد إلغاء تحميل الجلسة وضرب معلمات المستخدم ، فأنت تحتاج فقط إلى إجراء الاستعلامات الفرعية اللازمة للجداول الرئيسية والمتداخلة:

 SELECT date, h.hitNumber AS hitNumber, --     (SELECT value FROM t.customDimensions WHERE index=1) AS customDimensions1, (SELECT value FROM t.customDimensions WHERE index=2) AS customDimensions2, --     (SELECT value FROM h.customDimensions WHERE index=3) AS customDimensions3, (SELECT value FROM h.customDimensions WHERE index=4) AS customDimensions4 FROM `project.dataset.tablename` AS t, t.hits AS h 

الجدول الذي سيتم الحصول عليه نتيجة الاستعلام:

تفريغ مع الحفاظ على هيكل التعشيش

قد يكون هذا التفريغ ضروريًا عند استبدال قيم معلمة المستخدم في Google BigQuery.

مثال
في Google Analytics ، يتم نقل اسم البلد بالتنسيق الكامل إلى معلمة مستخدم الجلسة مع الفهرس 12 ومعلمة المستخدم الناجحة مع الفهرس 25 للمستخدمين من روسيا: روسيا. من الضروري تغيير تنسيق البلد إلى اختصار: RUS.

للقيام بذلك ، تحتاج إلى استبدال القيم المطلوبة لمعلمة المستخدم بدولة المستخدم لكامل سجل البيانات في Google BigQuery.

الإجراء الخاص بحل المشكلة:

1. تفريغ جميع البيانات مع الحفاظ على بنية التعشيش
2. استبدال قيمة معلمة المستخدم بالبلد
3. أعد كتابة الجدول

لتحميل البيانات مع الحفاظ على بنية التعشيش ، يجب عليك استخدام وظيفة ARRAY وبناء SELECT AS STRUCT. دعونا معرفة ما هو عليه.

بناء جملة الدالة ARRAY كالتالي:

 ARRAY(**) 

تقوم بإرجاع مجموعة من العناصر.

مقارنة صفيف مع تسجيل سطري:

على اليسار هو مجموعة ، على اليمين هو تسجيل خط

إذا أردنا حفظ بنية متداخلة وإلغاء تحميل صفيف بعدة أعمدة ، فيجب علينا استخدام ARRAY (SELECT AS STRUCT ...) :

صفيف متداخل

خيارات المستخدم ومستوى الجلسة

لإلغاء التحميل مع الحفاظ على بنية الأبعاد المخصصة للجلسة (المخصصة) ، نستخدم الاستعلام:

 SELECT date, --  ARRAY(SELECT AS STRUCT...)    ARRAY(SELECT AS STRUCT index, value FROM t.customDimensions) AS customDimensions FROM `project.dataset.tablename` AS t 

نتيجة لتنفيذه ، يتم الحصول على جدول يتم فيه حفظ بنية تداخل بيانات Google Analytics "الخام":

خيارات مخصصة على مستوى ضرب

لإلغاء تحميل قيم معلمات المستخدم الناجحة من Google BigQuery مع الحفاظ على بنية التعشيش ، من المهم ملاحظة أن جدول customDimensions متداخل في جدول الزيارات. بمعنى آخر ، تحتاج إلى جعل استعلام ARRAY الفرعي مرتين (SELECT AS STRUCT ...): أولاً إلى جدول الزيارات المتداخلة ، ثم إلى جدول customDimensions المتداخل فيه:

 SELECT date, --    t.hits ARRAY(SELECT AS STRUCT hitNumber, --    h.customDimensions ARRAY(SELECT AS STRUCT index, value FROM h.customDimensions) AS customDimensions FROM t.hits AS h ) AS hits FROM `project.dataset.tablename` AS t 

ستكون نتيجة هذا الاستعلام جدولًا:

الجلسة (المستخدم) + ضرب المعلمات المستخدم

كما هو الحال مع التفريغ سطر بسطر ، نحتاج إلى دمج استعلامات ARRAY الفرعية (SELECT AS STRUCT ...) في نفس الاستعلام إلى الجداول المتداخلة المطلوبة:

 SELECT date, --  () Custom Dimensions ARRAY(SELECT AS STRUCT index, value FROM t.customDimensions ) AS customDimensions, --  Custom Dimensions ARRAY(SELECT AS STRUCT hitNumber, ARRAY(SELECT AS STRUCT index, value FROM h.customDimensions ) AS customDimensions FROM t.hits AS h) AS hits FROM `project.dataset.tablename` AS t 

ما هي النتيجة:

مثال على استبدال قيم معلمات المستخدم

دعنا نعود إلى مثالنا .
في القسم السابق ، تلقينا طلبًا لتحميل الجلسة (المستخدم) وضرب معلمات المستخدم في Google Analytics مع الحفاظ على بنية التعشيش.
نحن نستكمل هذا الاستعلام باستخدام تصميمات SELECT * REPLACE للتفريغ مع الاستبدال و CASE لتحديث قيم معلمات المستخدم الضرورية:

 --      t.customDimensions  t.hits SELECT *REPLACE( --  () Custom Dimensions ARRAY(SELECT AS STRUCT index, --      CASE WHEN index=12 AND value='RUSSIA' THEN 'RUS' ELSE value END AS value FROM t.customDimensions) AS customDimensions, --  Custom Dimensions --   t.hits      h.customDimensions ARRAY(SELECT AS STRUCT *REPLACE( ARRAY(SELECT AS STRUCT index, --      CASE WHEN index=25 AND value='RUSSIA' THEN 'RUS' ELSE value END AS value FROM h.customDimensions) AS customDimensions) FROM t.hits AS h) AS hits) FROM `project.dataset.tablename` AS t 

نتيجة لهذا الطلب ، سنحصل على الجدول الأصلي مع البيانات الأولية من Google Analytics. ستحتفظ تمامًا بالهيكل الأصلي للتداخل ، ولكن سيتم تغيير قيم معلمات المستخدم الضرورية إلى معلمات جديدة.

موضوع العمل مع بنية بيانات متداخلة في Google BigQuery ليس سهلاً.

آمل أن أتمكن من توضيح هذه المشكلة. ولكن دعني أذكرك ، فإن أفضل طريقة لمعرفة كيفية القيام بشيء ما هي ممارسة المزيد.