संचालन बनाम विश्लेषणात्मक डेटाबेस: स्तंभ बनाम पंक्ति भंडारण

डेटाबेस एक्सेल, GSheet या बड़े ORM सिस्टम का उपयोग करके कार्यान्वित किया जा सकता है। बिजनेस एनालिटिक्स के अपने अभ्यास में, मैं विभिन्न समाधानों के साथ आया। और जब से मैं वित्त और लेखा परीक्षा से व्यावसायिक विश्लेषण के लिए आया था, हर बार जब मैं एक नई प्रणाली से मिलता था, तो मैं खुद से सवाल पूछता था - वे सभी एक-दूसरे से कैसे भिन्न होते हैं और वे किस कार्य को हल करते हैं? कुछ उत्तर मिले। यह लेख डेटाबेस के दो मुख्य उद्देश्यों को कवर करेगा:

1 - संचालन का लेखा,
2 - डेटा विश्लेषण

पहले प्रकार के कार्यों को ओएलटीपी सिस्टम द्वारा हल किया जाता है: ओ एन एल ine टी रैंसेशन पी रोसेसिंग से। दूसरा प्रकार OLAP सिस्टम द्वारा हल किया जाता है: O n L ine A nalytical Processing से

OLTP

ओएलटीपी स्टोरेज मॉडल की तुलना फोन बुक प्रविष्टियों से की जा सकती है। तालिका में पंक्ति को एक सूचकांक और संबंधित डेटा सूचकांक के रूप में प्रस्तुत किया जाता है: (indexN, data)। इसलिए, ऐसी तालिका को तालिका नहीं कहा जा सकता है। यह बल्कि एक साधारण पुस्तक है, जिसमें गिने हुए रेखाएँ हैं। यदि आपको पुस्तक में एक नया ऑपरेशन लिखने की आवश्यकता है, तो एक पंक्ति जोड़ें, एक इंडेक्स असाइन करें और पुस्तक को बंद करें। पुस्तक से चिपके हुए लेबल जो आपको जल्दी से ओ (लॉग एन) की अनुमति देते हैं, वांछित लाइन ढूंढते हैं और सीआरयूडी करते हैं।

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

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

OLAP और OLTP के बीच का ट्रेडऑफ़

1 सी समाधानों में, एक समझौता निम्नानुसार लागू किया जाता है। डेटाबेस में लिखते समय होने वाली घटनाओं को एक साथ कई स्थानों पर लिखा जाता है। एक स्थान पर, रिकॉर्ड में कुछ सूचकांक होते हैं और OLTP भार के लिए अनुकूलित होते हैं, दूसरी जगह पर, रिकॉर्ड सभी क्षेत्रों द्वारा अनुक्रमित किए जाते हैं और OLAP भार के लिए अनुकूलित होते हैं। ऐसी तालिकाओं को संचय रजिस्टर और सूचना रजिस्टर कहा जाता है। चूंकि कई स्थानों पर रिकॉर्डिंग से कब्जे वाली जगह कई गुना बढ़ जाती है, अर्थव्यवस्था की खातिर सभी लेनदेन विशेषताएँ रजिस्टर में नहीं आती हैं, लेकिन केवल वे जिन्हें विश्लेषणात्मक लेखांकन के इस खंड के लिए महत्वपूर्ण माना जाता है। इस तरह के एक समझौता को रॉप मॉडल कहा जाता है, अर्थात संबंधपरक विश्लेषणात्मक मानचित्रण।

OLAP

एसएपी में, जर्मन समकक्ष 1 सी आगे चला गया। इस सॉफ्टवेयर में रिलेशनल OLTP मॉडल को OLAP मॉडल में दोहराया जा सकता है। एसएपी हाना एक भंडारण स्तंभ संरचना को लागू करता है। इसका मतलब यह है कि "टेबल" को वहां पंक्तियों के एक सेट के रूप में नहीं, बल्कि स्तंभों के एक सेट के रूप में संग्रहीत किया जाता है।

Google Bigquery, Microsoft SSAS Tabular, Amazon Redshift, Yandex ClickHouse जैसे समाधानों में एक समान संग्रहण योजना लागू की गई है।

स्तंभ संग्रहण और पंक्ति संग्रहण के बीच का अंतर

यदि एक पंक्ति-वार संरचना में डेटा "क्षैतिज" ट्यूपल्स के रूप में संग्रहीत किया जाता है, जिनमें से प्रत्येक एक लेनदेन है:

period, product, department (Q1, SKU1, 1) (Q1, SKU2, 1) (Q1, SKU1, 1) ... (Q2, SKU1, 1) (Q2, SKU1, 1) (Q3, SKU1, 1) (Q3, SKU1, 1) ... 

फिर कॉलम में ऐसा डेटा "लंबवत" संग्रहीत किया जाता है:

 (Q1, Q1, Q1, ... Q2, Q2, Q3, Q3, ...) (SKU1, SKU2, SKU1, ... SKU1, SKU1, SKU1, SKU1, ...) (1,1,1, ... 1,1,1,1, ...) 

पुनरावृत्तियाँ सशर्त रूप से अनुकूलित की जा सकती हैं:

 period = (Q1, {start: 0, count: n}, Q2, {start: n+1; count: m}, ...) product = (SKU1, {start: 0, count: 1}, SKU2, {start: 1; count: 1}, SKU1, {start: 2; count: m}, ...) department = (1,{start:0, count:m}...) 

यदि कोई ऐसा कॉलम है जिसके लिए इस तरह का अनुकूलन प्रारंभिक मात्रा को कम नहीं करेगा, तो डेटा अपने मूल रूप में संग्रहीत किया जाता है।

कॉलम टेबल इंजन स्वयं कॉलम सॉर्टिंग अनुक्रम का चयन करता है, लेकिन यदि आप अपने डेटा को जानते हैं और उन्हें मैन्युअल रूप से सॉर्ट करते हैं, तो यह अक्सर संपीड़न को बढ़ाता है और विश्लेषणात्मक वर्कलोड को आसान बनाता है। व्यक्तिगत तालिकाओं का मेरा संपीड़न 300 गुना से अधिक हो गया। व्यवहार में, ऐसी डेटा संग्रहण संरचना:

1. आपको डेटा को उस स्तर पर संपीड़ित करने की अनुमति देता है जब उन्हें रैम में रखा जाता है, अर्थात। मेक इन मेमोरी मेमोरी उपलब्ध है जो रिलेशनल डेटाबेस के लिए प्रश्नों के साथ गति में तुलनीय नहीं है
2. डेटा मॉडल के निर्माण के लिए अपने नियम सेट करता है, अब ओएलटीपी में इस तरह के सामान्यीकरण की आवश्यकता नहीं है
3. विश्लेषणात्मक अभिव्यक्तियों के निर्माण के लिए इसके शब्दार्थ को परिभाषित करता है।

अभिव्यक्तियों की बारीकियों का विस्तार से वर्णन किया गया है:
यहाँ Google BigQuery के लिए है।
यहाँ Microsoft DAX के लिए है।

स्तंभ आधार संरचना के रूप में बीआई

बीआई एक समाधान है जो विश्लेषणात्मक भार परोसता है। और वे स्तंभ डेटाबेस के शीर्ष पर निर्मित होने पर जीवन को बहुत आसान बनाते हैं। यह एक होममेड ClickHouse-Grafana-Python गुच्छा या Google स्टैक बंडल हो सकता है: Bigquery-Data Studio-Dataprep-Dataflow या अखंड पावर BI।

बहुआयामी क्यूब्स स्तंभ भंडारण के लिए एक और OLAP विकल्प हैं। लेकिन मेरे लिए, MDX अभिव्यक्तियाँ, जब BQ या DAX में SQL की तुलना में, बेमानी और जटिल हैं।