با افتخار اعلام میکنیم که پشتیبانی از توابع تجمیعی (aggregation) مانند GROUP BY، SUM و سایر عملگرها به موتور کوئری SQL مبتنی بر ابر Cloudflare، یعنی R2 SQL اضافه شده است. این قابلیت جدید امکان شناسایی روندها و تغییرات مهم در دادهها، تولید گزارشهای دقیقتر و یافتن ناهنجاریها در لاگها را برای کاربران فراهم میسازد.
در این مطلب قصد داریم به بررسی جنبههای مختلف توابع تجمیعی و چالشهای پیادهسازی آنها در R2 SQL بپردازیم. نکتهی قابل توجه این است که R2 SQL با استفاده از یک معماری توزیعشده، قادر است کوئریها را روی حجم عظیمی از دادهها ذخیره شده در R2 Data Catalog اجرا کند.
توابع تجمیعی: خلاصهسازی دادهها
به طور خلاصه، توابع تجمیعی یا همان GROUP BY، خلاصهای از اطلاعات موجود در مجموعه داده را ارائه میدهند. به عنوان مثال، فرض کنید میخواهیم نامبردهترین بخشهای سازمانی (departments) را شناسایی کنیم؛ در این حالت میتوانیم از دستور ORDER BY استفاده نماییم تا نتایج را بر اساس حجم کاری مرتب کنیم.
یک ویژگی جالب در مورد کوئریهای تجمیعی این است که آنها میتوانند به ستونهایی ارجاع دهند که در اصل در هیچکجا ذخیره نشدهاند. برای مثال، تابع sum(value) یک ستون محاسبهشده را تولید میکند که با ستون department متفاوت است؛ ستونی که از فایلهای Parquet موجود در R2 استخراج میشود.
این تفاوت ظریف به این معنی است که هر کوئریای که به توابع تجمیعی مانند sum، count و غیره ارجاع میدهد، باید به دو فاز تقسیم شود. در اولین فاز، ستونهای جدید محاسبه میشوند. اگر قصد مرتبسازی دادهها بر اساس ستونی نظیر count() را داشته باشیم یا بخواهیم ردیفها را بر اساس آن فیلتر کنیم (با استفاده از دستور HAVING)، باید مقادیر این ستون را بدانیم.
البته، اگر کوئری شامل توابع تجمیعی در قسمت HAVING یا ORDER BY نباشد اما به طور همزمان از آنها در بخش SELECT استفاده شود، میتوانیم از یک روش جالب استفاده کنیم. از آنجایی که نیازی به مقادیر این توابع تا زمان نهایی وجود ندارد، میتوانیم آنها را بهصورت جزئی محاسبه کرده و نتایج را قبل از ارسال به کاربر با یکدیگر ادغام نماییم.
رویکردهای مختلف برای تجمیع داده
تفاوت اصلی بین دو رویکرد این است که توابع تجمیعی چه زمانی محاسبه میشوند: آیا باید آنها را بهصورت پیشفرض و برای انجام محاسبات اضافی در آینده محاسبه کنیم، یا اینکه به صورت تدریجی نتایج مورد نیاز کاربر را بسازیم؟
روش Scatter-Gather Aggregation
ابتدا روش ساخت نتایج بهطور تدریجی – که ما آن را «توابع تجمیعی Scatter-Gather» مینامیم – را بررسی خواهیم کرد. سپس، بر اساس این رویکرد، به معرفی «توابع تجمیعی Shuffling» میپردازیم که قادرند محاسبات اضافی مانند دستورات HAVING و ORDER BY را روی توابع تجمیعی اعمال کنند.
کوئریهای تجمیعی بدون وجود دستورات HAVING و ORDER BY میتوانند به شیوهای مشابه با کوئریهای فیلتر اجرا شوند. در این حالت، R2 SQL یک گره (node) را به عنوان هماهنگکننده انتخاب میکند. این گره کوئری را تجزیه و تحلیل کرده و برای یافتن ردیفهایی که ممکن است حاوی دادههای مورد نیاز باشند، با R2 Data Catalog مشورت میکند.
هر ردیف از فایل Parquet نشاندهندهی یک واحد کار نسبتاً کوچک است که میتواند توسط یک گره محاسباتی به تنهایی انجام شود. گره هماهنگکننده، کار را بین چندین گرهی فعال توزیع کرده و نتایج را جمعآوری میکند تا آنها را به کاربر بازگرداند.
برای اجرای کوئریهای تجمیعی، تمام مراحل مشابه با کوئریهای فیلتر دنبال میشوند و قطعات کوچک کار بین گرهها توزیع میگردد. در این حالت، علاوه بر فیلتر کردن ردیفها بر اساس شرط موجود در دستور WHERE، گرهها نیز محاسبات از پیش تعیین شده (pre-aggregates) را انجام میدهند.
محاسبات از پیش تعیین شده نشاندهنده وضعیت میانی یک تجمیع هستند و یک قطعه دادهی ناقص را شامل میشوند که نمایانگر مقدار جزئی تابع تجمیعی بر روی زیرمجموعهای از دادهها است. چندین محاسبه از پیش تعیینشده میتوانند با یکدیگر ادغام شوند تا مقدار نهایی تابع تجمیعی محاسبه شود.
برای مثال، محاسبهی از پیش تعیین شده برای count() به سادگی یک عدد است که نشاندهنده تعداد ردیفها در زیرمجموعهای از دادههاست. محاسبهی مقدار نهایی count() به آسانی با جمع کردن این اعداد بهدست میآید. محاسبات از پیش تعیین شده برای avg(value) شامل دو عدد: `sum(value)` و `count()` است. در نتیجه، مقادیر `sum(value)` و `count()` را به ترتیب با یکدیگر جمع کرده و سپس تقسیم میکنیم تا مقدار نهاییِ میانگین (average) بهدست آید.
پس از اتمام محاسبهی محاسبات از پیش تعیین شده توسط گرهها، آنها نتایج خود را به صورت استریم به گره هماهنگکننده ارسال میکنند. گره هماهنگکننده تمام نتایج را جمعآوری کرده و مقادیر نهایی توابع تجمیعی را از محاسبات از پیش تعیینشده محاسبه میکند و نتیجه را برای کاربر برمیگرداند.
روش Shuffling Aggregation
اگر کوئری شامل مرتبسازی نتایج بر اساس مجموعهی کلی باشد، گره هماهنگکننده نمیتواند به نتایج از پیش فیلتر شدهی فرستاده شده توسط کارگران اعتماد کند. در این حالت باید تعداد کل را برای هر دپارتمان از تمام کارگران درخواست کرده و قبل از مرتبسازی آنها آنها را با هم جمعآوری کند. اگر گروه بندی بر اساس ستونهایی با کاردینالیته بالا مانند آدرسهای IP یا شناسه کاربر باشد، گره هماهنگکننده مجبور به دریافت و ادغام میلیونها ردیف میشود که این امر منجر به ایجاد گلوگاه منابع در یک گره میشود.
برای رفع این مشکل، از shuffling استفاده میکنیم – روشی برای قرار دادن مجدد دادهها برای گروههای خاص قبل از تجمیع نهایی.
این مسیریابی بر اساس پارتیشنبندی هش قطعی (deterministic hash partitioning) استوار است. هنگامی که یک گره ردیفی را پردازش میکند، آن را با استفاده از ستون GROUP BY به منظور شناسایی کارگر مقصد هش می کند. از آنجا که این هش قطعی است، هر کارگر در خوشه بهطور مستقل توافق میکند که دادههای خاصی کجا ارسال شود. اگر
📌 توجه: این مطلب از منابع بینالمللی ترجمه و بازنویسی شده است.