استعلام البيانات الضخمة على مستوى الثواني: Google BigQuery Modern Data Warehouse من البدء إلى إتقان البرنامج التعليمي

في عصر اليوم حيث تبدأ البيانات على مستوى تيرابايت و PB ، سيواجه كل فريق إنترنت تقريبًا عنق الزجاجة التكنولوجي الكبير:

تم التحقق من تقرير البيانات ببطء شديد.

قواعد البيانات التجارية التقليدية (مثل MySQL و PostgreSQL) تواجه مئات الملايين من تحليل السجل أو تدفق التجارة الإلكترونية ، حتى لو قمت ببناء الفهرسة بشكل مثالي ، فهي معقدة

GROUP BY

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

في بيئة Google Cloud(GCP ، Google Cloud) ، هناك خدعة كبيرة لخفض الأبعاد تم إنشاؤها لحل التحليل الشامل ، والتي تسمى

Google BigQuery

.

تتميّز منطقته الأساسية بالصفاء التام:

بنية Serverless (بدون خادم) مستضافة بالكامل + تخزين عمودي موزع على نطاق واسع

. لا تحتاج إلى التحكم في أي تكوين خادم أساسي ، ولا تحتاج إلى فهرسة ، ورمي مئات أو آلاف غيغابايت من الملفات مباشرة إليه ، ويمكنه استخدام عبارات SQL القياسية لبث نتيجة التجميع النهائية لك في غضون ثوانٍ قليلة.

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

المرحلة الأولى: التفكيك العميق ، لماذا يمكن لـ BigQuery "الاستعلام في الثانية" ؟

قبل أن تتمكن من كتابة SQL ، يجب عليك إنشاء نموذج العالم المادي الأساسي لـ BigQuery في عقلك ، وإلا سيكون من الصعب عليك فهم سبب تشغيله بهذه السرعة دون الحاجة إلى فهرسة.

الجزء السفلي من BigQuery يستخدم

يتم فصل الحساب تمامًا عن التخزين

العمارة التخريبية:

محطة الحاويات (التخزين الموزع Colossus): وضع البيانات الخاصة بك. يستخدم BigQuery تخزين العمود (تنسيق Capacitor). قاعدة البيانات التقليدية (التخزين): من أجل التحقق من عمر جميع المستخدمين ، يجب قراءة كل سطر من البيانات بما في ذلك الاسم والعنوان وكلمة المرور وما إلى ذلك من القرص الصلب ، مما يتسبب في إهدار كبير للإدخال/الإخراج. BigQuery (تخزين العمود): يتم تخزين البيانات في مجموعات حسب العمود. عندما تحقق من العمر ، فإنه يقرأ فقط بيانات عمود "العمر" بدقة ، والأعمدة الأخرى لا تلمسها. تم قطع القرص الصلب I/O مباشرة بأكثر من 90 ٪.

Super Engine (Dremel Computing Cluster): عند كتابة سطر من الاستعلامات المعقدة SQL على وحدة التحكم والنقر فوق التنفيذ ، ستقوم Google بإرسال مئات أو آلاف الأشخاص في الخلفية على الفور.

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

الاستنتاج الأساسي: أنت تدفع مقابل الاستعلام عن كمية البيانات الممسوحة ضوئيًا ** (حوالي 5 دولارات لكل 1 تيرابايت) ، أو تشتري موارد حوسبة ثابتة. لذلك ، فإن كيفية كتابة SQL "توفير المال والكفاءة" هي نقطة تحول بين المبتدئين والمهندسين المعماريين الكبار.

المرحلة الثانية: التمرين القتالي الفعلي 1-التجربة الأولى لاستيراد البيانات والاستعلام عن المستوى الثاني

تأكد من أن لديك بالفعل حساب GCP. نحتاج أولاً إلى استيراد سجل سلوك المستخدم بتنسيق CSV الأصلي بأكثر من خمسة ملايين سطر إلى BigQuery.

1. إنشاء مجموعة بيانات (Dataset)

في BigQuery ، تكون بنية البيانات واضحة جدًا: المشروع (المشروع)-> مجموعة البيانات (البيانات ، أي ما يعادل قاعدة البيانات)-> جدول البيانات (الجدول).

قم بتسجيل الدخول إلى وحدة تحكم GCP ، وابحث واذهب إلى صفحة BigQuery.

في قائمة Explorer على اليسار ، انقر على النقاط الثلاث على الجانب الأيمن من مشروعك واختر "إنشاء مجموعة بيانات".

معرف مجموعة البيانات: يسمى ecommerce_analytics.

موقع البيانات (موقع البيانات): يوصى باختيار asia-east1 (تايوان) ، بالقرب من البلاد وبسرعة. انقر على إنشاء.

2. استيراد البيانات المهيكلة بنقرة واحدة

انقر فوق مجموعة بيانات ecommerce_analytics التي تم إنشاؤها حديثًا واختر "إنشاء جدول".

المصدر: اختر "تحميل" الملفات المحلية من Google Cloud Storage (تخزين كائنات GCS) أو مباشرة.

تنسيق الملف: اختر CSV.

اسم الجدول المستهدف: أدخل user_logs.

الهندسة المعمارية: ضع علامة على "Auto detect". سيقوم BigQuery تلقائيًا بمسح السطر الأول من ملفاتك بشكل ذكي للغاية ، ويميز تلقائيًا العمود الذي هو سلسلة أو العمود الذي هو طابع رقمي أو زمني.

انقر على إنشاء جدول. بعد ثوانٍ قليلة ، تم وضع أكثر من 5 ملايين سطر من البيانات بثبات في التخزين الموزع في Google Cloud.

3. التحقق من سحب المستوى الثاني

في محرر الاستعلام ، اكتب SQL المجمع القياسي التالي لمعرفة من هي أفضل 10 منتجات ذات أعلى مبلغ شراء في الثلاثين يومًا الماضية:

إس كيو إل

SELECT

Product_category ،

COUNT(order_id) AS total_sales ،

SUM(price) AS total_revenue

FROM

"Ecommerce _ analytics.us er_logs"

WHERE

Event_time> = TIMESTAMP_SUB(CURRENT_TIMESTAMP() ، INTERVAL 30 DAY)

التجميع حسب

Product_category

ORDER BY

Total_revenue DESC

LIMIT 10 ؛

انقر على تشغيل. يحدق في الموقت في الزاوية اليمنى العليا من الشاشة:

0.8 ثانية!

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

المرحلة الثالثة: تحسين الأداء على مستوى المؤسسة-تأمين مزدوج "لتوفير المال وتسريع" اللحام

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

بصفتك كبير مهندسي البيانات ، يجب عليك وضع مجموعتي دفاعات الدفاع المادية التاليتين على الفور عند إنشاء الجدول:

1. خط الدفاع الأول: تقسيم المناطق-قطع المسح غير الفعال

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

تكوين المواصفات الأساسية: عند إنشاء جدول أو تصميم خط التجميع ، حدد "التقسيم" وفقًا لعمود الوقت (مثل event_date).

مقارنة التأثير: بعد فتح القسم ، عندما تحد من WHERE event_date = "2026-05-30" في شروط WHERE ، سيذهب BigQuery مباشرة إلى الدرج المعزول جسديًا في 30 مايو مثل قلب الكتاب في الطابق السفلي. لا يمكنك حتى قراءة أدراج الآلاف من التواريخ. تم تخفيض حجم المسح الضوئي على الفور من 100 جيجابايت إلى 1 جيجابايت ، وتم تخفيض تكلفة الفاتورة مباشرة بنسبة 99 ٪.

2. خط الدفاع الثاني: Clustering (Clustering)-دع البيانات "تجمع الأشياء حسب الفئة"

لا يكفي وجود قسم زمني ، ماذا لو كنت أرغب أيضًا في فحص المستخدمين في "بلد معين" أو "مصدر معين" بشكل عالي التردد ؟

تكوين المواصفات الصعبة: على أساس تقسيم الوقت ، حدد "الكتلة" لأعمدة البلد والمصدر.

القصة الداخلية الأساسية: سيقوم BigQuery تلقائيًا بترتيب البيانات التي تنتمي إلى نفس البلد والقناة في الخلفية بإحكام في التخزين المادي. مع استخدام التقسيم ، يمكن أن ترتفع سرعة تحليل القمع متعدد الأبعاد مرة أخرى.

المرحلة الرابعة: البيانات الكبيرة على مستوى الأعمال

مواصفات التطوير وتاريخ الدم والدموع اليومية

الأدوات منعشة للغاية في الاستخدام ، ولكن في المشهد الحقيقي ، غالبًا ما يخطو عدد لا يحصى من المبتدئين في الحفرتين الدمويتين التاليتين لأنهم لا يفهمون القواعد الخفية لـ BigQuery:

1. الاستخدام ممنوع منعا باتا

SELECT *

(مصدر كل الشرور ، أراك برأسك)

في MySQL التقليدية ، اعتدنا على طرق

SELECT * FROM table LIMIT 10

دعونا نرى كيف يبدو السطح.

كارثة قاتلة: في تخزين العمود مثل BigQuery ، لا يمكن لـ LIMIT 10 مساعدتك في توفير المال على الإطلاق! نظرًا لأن BigQuery يقرأ وفقًا للعمود ، عند كتابة SELECT * ، فإنه سيسحب بالقوة جميع الأعمدة الأساسية وجميع البيانات من القرص الصلب ، حتى لو كنت بحاجة فقط إلى 10 أسطر في النهاية. إذا كان هذا الجدول يحتوي على 100 جيجابايت ، فإن هذا الخط من الأوامر العادية سينتج مباشرة خصم 100 جيجابايت للمسح الضوئي.

الحل القياسي لـ Dachang: إذا كنت تريد فقط إلقاء نظرة على هيكل الجدول وعينة البيانات ، فلا تنقر على الاستعلام! انقر مباشرة على اسم الجدول ، ثم انتقل إلى علامة التبويب "معاينة". وظيفة المعاينة عرض البيانات مجانية تمامًا ولا يتم مسحها ضوئيًا. إذا كان عليك كتابة SQL ، فيجب عليك كتابة الأعمدة التي تحتاجها بوضوح (مثل SELECT user_id ، age).

2. احتضان "تسطيح كل شيء" (التخلي عن النماذج الثلاثة التقليدية الصلابة)

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

JOIN

إجبارهم على القتال.

يقوم المهندس المعماري بتعديل القصة الداخلية: في مستودعات البيانات الموزعة الحديثة ، تعد JOIN عملية باهظة الثمن تستهلك قوة الحوسبة العنقودية (لأنها ستؤدي إلى خلط البيانات بين العقد الضخمة Shuffle). الهيكل المفضل لـ BigQuery هو جدول De-normalized.

الموقف الصحيح: استخدم أنواع RECORD (الحقول المتداخلة) و REPEATED (الحقول المكررة) الفريدة من BigQuery. يمكنك وضع جميع الطلبات وجميع المسارات التاريخية للمستخدم مباشرة كمصفوفة متداخلة (Array) في هذا الخط. عند الاستعلام ، استخدم وظيفة UNNEST للتخلص من الحزمة مثل البرق ، وإزالة الأداء تمامًا.

المرحلة الخامسة: التطور المتقدم-فتح معالجة التدفق في الوقت الحقيقي والشاشة الكبيرة لبيان BI

إذا كان عمل شركتك يحتاج إلى النظر إلى "السوق في الوقت الفعلي" (مثل التغييرات في الوقت الفعلي لـ GMV كل ثانية أثناء الترويج) ، يمكن لـ BigQuery أيضًا اللعب بسهولة:

Streaming Inserts: تطبيق خلفي أو محرك معالجة التدفق (مثل Apache Beam و Cloud Dataflow) ، يمكن أن يكون عبر BigQuery

تنتج واجهة برمجة تطبيقات Storage Write عشرات الآلاف من سجلات سلوك المستخدم كل ثانية ، ويتم حقنها باستمرار في جدول user_logs في الوقت الفعلي مثل المياه الجارية.

الوصول بدون تأخير إلى شاشة BI الكبيرة: انقر مباشرة على "Explore Data" في الجزء العلوي من BigQuery ، وافتح Looker Studio الخاص بـ Google أو طاولة الطرف الثالث بنقرة واحدة.

لأن BigQuery مدمج

BI Engine (محرك تسريع الذاكرة)

، سوف تخزن مؤشرات التردد العالي في الذاكرة. عندما يقوم رئيس الأعمال والعمليات بسحب وفحص التقارير من أي أبعاد بشكل متكرر على الشاشة الأمامية الكبيرة ، يتم تحديث المخطط الأساسي بالكامل

اكتمل في عشرات المللي ثانية

، حققت حقًا "رؤية البيانات عند الهبوط ، والملاحظات العالمية على مستوى ثانية".

الخلاصة

باستخدام Google BigQuery لبناء مستودع بيانات حديث على مستوى المؤسسة ، يكمن جوهر المستوى الصناعي الأساسي في الواقع في ستة عشر كلمة:

تسريع التخزين ، أقفال القسم ، معاينة العداءة البيضاء ، عرض واسع

.

لقد تخلصت تمامًا من المعاناة الأصلية المتمثلة في أنه من أجل إجراء تحليل بيانات كبير ، كنت بحاجة إلى بناء مجموعة من الأجهزة بنفسك ، وتحدق في قرص I/O للآلة المادية كل يوم. قم باستضافة كل قوة الحوسبة والتخزين وقابلية التوسع إلى أكبر سيرفيس في العالم من Google. بغض النظر عن مقدار البيانات البركانية التي ينتجها العمل في المستقبل ، يمكنك الجلوس أمام الكمبيوتر وتحقيق النصر ، والسماح للبيانات الضخمة بصق قيمة تجارية حقيقية لك في لحظة.