BigQuery में Arrays - क्वेरी प्रदर्शन को बेहतर बनाने और भंडारण को अनुकूलित करने के लिए कैसे

इस पोस्ट के माध्यम से, मैं पारंपरिक मॉडल मॉडल पर विश्लेषणात्मक उपयोग के मामलों के लिए BigQuery की शक्ति का उपयोग करने के लिए Bigquery सरणियों का उपयोग करने के लाभों को चित्रित करना चाहता हूं।

वेयरहाउस के लिए DataModel विकल्प

आमतौर पर डेटा वेयरहाउस विश्लेषणात्मक और रिपोर्टिंग आवश्यकताओं के अनुरूप OLAP सुविधाओं के साथ निर्मित डेटाबेस का एक विस्तार है। डेटा मॉडल के दृष्टिकोण से, वे अभी भी अंतर्निहित तालिका संरचनाओं में RDBMS की नकल करते हैं। प्रदर्शन के लिए, टेबल में शामिल होने के ओवरहेड को कम करने के लिए गोदाम में टेबल को सामान्यीकृत किया जाता है। यह अनावश्यक डेटा स्टोर करने की लागत में जुड़ने की लागत को कम करने के लिए एक सिद्ध सबसे अच्छा अभ्यास है।

प्रेरणा

BigQuery Arrays के साथ हम बेमानी भंडारण को कम करने के लिए डेटा मॉडल को ट्विट करके अगले स्तर तक इस लाभ को ले सकते हैं। क्लाउड आधारित डेटा गोदामों के साथ जहां हर बाइट संग्रहीत और संसाधित होती है, लागत में योगदान देती है, लागत बचाने के लिए प्रश्नों की दक्षता एक महत्वपूर्ण कारक है।

इस अवधारणा को समझने के लिए, एक काल्पनिक फ़ुटबॉल स्टोर के ऑर्डरिंग सिस्टम के निम्नलिखित तीन डेटा मॉडल की खोज करके विभिन्न डेटा मॉडल की दक्षता का मूल्यांकन करने देता है।

  1. एक सामान्यीकृत डेटा मॉडल जहां ऑर्डर और ऑर्डर लाइनें दो अलग-अलग तालिकाओं के रूप में संग्रहीत की जाती हैं
  2. एक डी-सामान्यीकृत डेटा मॉडल जहां ऑर्डर और ऑर्डर लाइनें उसी तालिका में संग्रहीत की जाती हैं जहां लाइन स्तर पर ऑर्डर डेटा दोहराया जाता है
  3. एक डी-सामान्यीकृत डेटा मॉडल का उपयोग BigQuery सरणियों का लाभ उठाते हैं जहां ऑर्डर और ऑर्डर लाइनें एक ही तालिका में संग्रहीत की जाती हैं, लेकिन ऑर्डर डेटा दोहराया नहीं जाता है

BigQuery में आता है

एरे, बिगविले में बहु-मूल्य वाले स्तंभ हैं, जहाँ एक सरणी फ़ील्ड में एक से अधिक मान होते हैं (एक ही डेटा प्रकार के)

प्रत्येक पंक्ति में बहु-मूल्य वाले कॉलम हो सकते हैं

सरणियों के साथ यह सारणी संरचना प्रजाति_ समूह (स्तनधारियों) में मान के बीच एक संबंध बनाए रखती है, जिसमें एरे प्रजाति_लिस्ट में प्रत्येक मूल्य के साथ इसके मूल्य को दोहराए बिना होता है। इस प्रकार सरणियों का उपयोग करते हुए डेटा संग्रहीत करना भंडारण को कम करता है (स्तनधारियों को केवल 3 बार के बजाय एक बार संग्रहीत किया जाता है)। लेकिन यह संरचना विशिष्ट SQL शैली विश्लेषण का समर्थन नहीं करती है। इस संरचना को एक SQL स्टाइल परिणाम सेट में परिवर्तित करने के लिए, UNNEST का उपयोग करें, जो सरणी को अनपैक करता है और प्रत्येक प्रजाति_लिस्ट के मान को क्रॉस क्रॉस की तरह जोड़ता है।

SQL स्टाइल परिणाम सेट, किसी भी SQL प्रकार के विश्लेषण को लागू करने के लिए फिट है
नोट: स्तनधारियों (16 बाइट्स) और सरीसृप (18 बाइट्स) को एक बार स्टोर करके, हमने सिर्फ एक कॉलम पर स्टोरेज (72%) का बाइट (72%) बचाया। इसे कई कॉलम के साथ टेराबाइट स्केल टेबल के लिए प्रोजेक्ट करें, यह तुरंत स्टोरेज कॉस्ट सेविंग में बदल जाता है।

डेटाटाइप्स के लिए भंडारण आकार की गणना और यहां सरणियों पर और अधिक के लिए BigQuery संग्रहण मूल्य देखें

नमूना डेटा

सरणियों और यादृच्छिक संख्याओं के जादू का उपयोग करके, यह क्वेरी प्रत्येक 5 सेकंड के लिए एक ऑर्डर बनाकर एक ऑर्डरिंग सिस्टम का अनुकरण करती है, जिससे हमें लगभग 518K महीने का ऑर्डर मिलता है। एक वर्ष तक विस्तारित, क्वेरी तीन डेटा मॉडल के प्रदर्शन का परीक्षण करने के लिए पर्याप्त ऑर्डर वॉल्यूम बनाते हुए कुल 22M ऑर्डर लाइनों के साथ 6.3M यादृच्छिक आदेश उत्पन्न करता है।

डेटा सेटअप

इस github रेपो को डाउनलोड करें और setup.sh चलाएं। स्क्रिप्ट 3 डेटा मॉडल के लिए डेटा को पॉप्युलेट करता है।

डेटा मॉडल

सामान्यीकृत डेटा मॉडल

ऑर्डर और लाइनों के बीच विशिष्ट मास्टर डिटेल संबंध

डी-सामान्यीकृत डेटा मॉडल

प्रत्येक पंक्ति के लिए दोहराया गया ऑर्डर, ग्राहक आईडी और ऑर्डर की तारीख के साथ डी-सामान्यीकृत मॉडल

डी-सामान्यीकृत BigQuery Arrays के साथ

आदेश और लाइनें एक ही पंक्ति में सहेजी गई हैं, लेकिन प्रत्येक पंक्ति के लिए दोहराया नहीं गया है

प्रदर्शन का परीक्षण

इन डेटा मॉडल को खोजने के लिए परीक्षण करने के लिए कुछ रिपोर्टिंग क्वेरीज़ चलाने दें

  1. ग्राहक द्वारा कुल आदेश
  2. महीना द्वारा कुल आदेश
  3. आइटम द्वारा कुल आदेश

यहां प्रत्येक क्वेरी के लिए कैप्चर किए गए कुंजी क्वेरी मैट्रिक्स की तुलना की गई है

डी-सामान्यीकृत मॉडल ने बीक्यू एरे के साथ सभी मैट्रिक्स पर बेहतर प्रदर्शन किया है मॉडल कम से कम बाइट्स संसाधित और टाइम लिया जाता है

डेटा मॉडल द्वारा तालिकाओं के आकार की तुलना करना

क्वेरी मेट्रिक्स

  • बाइट्स संसाधित - तालिकाओं से पढ़े गए डेटा की मात्रा, क्वेरी प्रसंस्करण की लागत में सीधे योगदान देती है
  • बाइट्स शफल्ड - समानांतर प्रोसेसिंग नोड्स के बीच स्थानांतरित डेटा या मध्यवर्ती परिणाम की मात्रा। यह डेटा स्थानांतरित करने के लिए समर्पित प्रसंस्करण शक्ति की मात्रा का एक उपाय है जो प्रतीक्षा समय बढ़ाता है और समानता को सीमित करता है
  • समय निकाला - क्वेरी को निष्पादित करने के लिए बीता हुआ समय
  • स्लॉट उपयोग (सेकंड) - क्वेरी को संसाधित करने के लिए समानांतर नोड्स द्वारा खर्च किए गए समय की संचयी राशि
इन मैट्रिक्स को कम से कम करने के उद्देश्य से डेटा मॉडल और प्रश्नों का डिज़ाइन तैयार किया जाना चाहिए

टिप्पणियों

  • सामान्यीकृत मॉडल ने सबसे अधिक मात्रा में बाइट्स का उपभोग किया और एक परिणाम प्रदान करने में अधिक समय लगा। क्वेरीज़ में सबसे अधिक मात्रा में बाइट्स फेरबदल (सामान्यीकृत मॉडल की तुलना में अधिक परिमाण के आदेश) थे, जो तालिकाओं के बीच जुड़ने का एक परिणाम था। निषेधात्मक रूप से महंगा साबित होता है।
  • डी-सामान्यीकृत मॉडल दोनों ने न्यूनतम बाइट्स का उपभोग किया, क्योंकि डेटा एक ही पंक्ति के भीतर है, किसी भी प्रकार की आवश्यकता नहीं है
  • BigQuery Arrays वाले मॉडल में बाइट्स संसाधित और समय लिया गया सबसे अच्छा मैट्रिक्स था

अनुशंसाएँ

डेटा मॉडल को परिभाषित करते समय इष्टतम प्रदर्शन के लिए निम्नलिखित पर विचार करें

  1. माता-पिता / बाल संबंधों के साथ तालिकाओं को संग्रहीत करने के लिए ऐरे का उपयोग करें। कार्डिनैलिटी जितनी अधिक होगी, लाभ उतना ही अधिक होगा
  2. डी-सामान्यीकृत रूप का उपयोग करें यदि Arrays का उपयोग डेवलपर्स के लिए या तो प्रश्नों को जटिल करता है या इसमें अंतिम उपयोगकर्ताओं / विश्लेषकों के लिए खड़ी सीखने की अवस्था शामिल है जो BigQuery का उपयोग करते हैं
  3. डेटाटाइप्स ऑप्टिमाइज़ करें: तालिकाओं को परिभाषित करते समय उपयुक्त डेटाैटिप्स निर्धारित करें। जहां लागू हो, विकल्पों पर विचार करें।

ये छोटे डिज़ाइन समय निर्णय जल्दी से बचत में जोड़ सकते हैं क्योंकि प्रत्येक अतिरिक्त बाइट भंडारण लागत और क्वेरी लागत में जुड़ जाती है जब इसे पुनर्प्राप्त और संसाधित किया जाता है।

आगे पढ़ने / अभ्यास

  • BigQuery क्वेरी योजनाओं को विज़ुअलाइज़ करते हुए - इन प्रश्नों के प्रदर्शन का प्रतिनिधित्व करते हैं, मैं Stephan Meyn द्वारा इस महान उपकरण का उपयोग करके क्वेरी निष्पादन योजनाओं को चित्रित करने की योजना बना रहा हूं।
  • एनालिटिक्स में इवेंट मापदंडों का विश्लेषण करने के लिए BigQuery में UNNEST फ़ंक्शन का उपयोग कैसे करें - यहां एक लेख है जिसमें UNNEST के उत्कृष्ट उपयोग के साथ टॉड केर्लमैन द्वारा एक सुंदर चित्रण शामिल है।