تحلیل داده‌های حجیم با SQL و Python: از الگوی اجرا تا معماری عملی

در تحلیل داده‌های حجیم، تصمیم اصلی انتخاب بین SQL و Python نیست؛ تصمیم اصلی این است که محاسبه کجا اجرا شود. قاعده عملی این است که فیلتر، اتصال (Join)، تجمیع و پنجره‌بندی در لایه SQL و نزدیک به محل ذخیره داده انجام شود و Python فقط روی نتیجه فشرده‌شده برای مدل‌سازی، آماره‌های پیشرفته و اتوماسیون کار کند. سازمان‌هایی که این مرز را جابه‌جا می‌کنند، به‌جای مسئله تحلیلی با مسئله حافظه و زمان اجرا دست‌وپنجه نرم می‌کنند.
این مقاله بخشی از راهنمای جامع تحلیل داده در سازمان‌ها است و روی لایه اجرا تمرکز دارد، نه انتخاب محصول.

۱. مرز درست میان SQL و Python چیست؟

بیشتر پروژه‌های تحلیل داده حجیم در ایران با یک الگوی غلط شروع می‌شوند: یک SELECT * از جدول تراکنش‌های Oracle یا SQL Server، انتقال چند ده میلیون رکورد به یک نوت‌بوک Python و سپس تلاش برای groupby روی چیزی که در حافظه جا نمی‌شود. مشکل این الگو کندی نیست؛ مشکل این است که کل داده از جایی که بهینه‌سازی شده بیرون کشیده می‌شود و به جایی می‌رود که هیچ آماره، ایندکس و اجرای موازی‌ای در اختیار ندارد.

تقسیم کار قابل دفاع:

پرسش راهنما در هر گام این است: «آیا این عملیات حجم داده را کم می‌کند؟» اگر پاسخ مثبت است، تا حد امکان باید در SQL بماند.

۲. اصل اول: محاسبه را به داده نزدیک کن

دو تکنیک، بیشترین اثر را در کارایی تحلیل داده حجیم دارند و هر دو به معنای فشار دادن منطق به پایین‌ترین لایه ممکن هستند:
Projection Pushdown: فقط ستون‌های لازم خوانده شوند. در یک جدول ۸۰ ستونی، خواندن ۴ ستون به معنای صرفه‌جویی مستقیم در I/O است.
Predicate Pushdown: شرط فیلتر تا لایه خواندن فایل یا بلوک داده منتقل شود تا بخش‌هایی که قطعاً شرط را برآورده نمی‌کنند، هرگز خوانده نشوند.
مستندات DuckDB تصریح می‌کند قالب Parquet با نگه‌داشتن آماره در سطح Row Group و zonemap، هر دو نوع فشار به پایین را ممکن می‌کند و بارهای کاری که فیلتر، انتخاب ستون و تجمیع را ترکیب می‌کنند، روی Parquet عملکرد مناسبی دارند.

SELECT customer_id, SUM(amount) AS total
FROM read_parquet(‘sales/*.parquet’)
WHERE province = ‘THR’
AND sale_date >= DATE ‘۲۰۲۶-۰۱-۰۱’
GROUP BY customer_id;

اگر داده به‌صورت پارتیشن‌بندی‌شده روی دیسک بنشیند، حتی پوشه‌های نامرتبط هم باز نمی‌شوند:

sales/province=THR/year=۲۰۲۶/part-۰۰۰.parquet

این همان نقطه‌ای است که بسیاری از تیم‌ها با «بهینه‌سازی کد Python» دنبال حل مسئله‌ای هستند که ریشه‌اش در لایه ذخیره‌سازی است.

۳. قالب ذخیره‌سازی: چرا CSV گلوگاه پنهان است

CSV یک قالب تبادل است، نه قالب تحلیل. متنی و ردیف‌محور است، نوع داده را نگه نمی‌دارد، آماره ندارد و هر بار خواندن آن به تجزیه متنی کل فایل نیاز دارد. Parquet ستونی، باینری و فشرده است و امکان خواندن انتخابی را می‌دهد.
قاعده عملی: CSV را یک بار دریافت کنید، یک بار به Parquet تبدیل کنید و بعد از آن دیگر به CSV برنگردید.

python
import duckdb
duckdb.sql(“””
COPY (SELECT * FROM read_csv_auto(‘raw/*.csv’))
TO ‘warehouse/sales.parquet’
(FORMAT parquet, COMPRESSION zstd);
“””)

تنظیم اندازه‌ها طبق راهنمای کارایی DuckDB اهمیت دارد:

نکته‌ای که کمتر گفته می‌شود: همان مستندات اشاره می‌کند پرس‌وجو روی فایل Parquet در بنچمارک TPC-H حدود ۱٫۱ تا ۵ برابر کندتر از پرس‌وجو پایگاه داده بومی DuckDB بوده است. یعنی اگر روی یک مجموعه داده ده‌ها پرس‌وجو با Joinهای پیچیده اجرا می‌کنید، بارگذاری آن در پایگاه داده تحلیلی توجیه دارد.

این بخش از زنجیره، پیش‌نیاز خود را از کیفیت داده و پاکسازی داده‌های سازمانی می‌گیرد؛ قالب بهینه، داده نامعتبر را معتبر نمی‌کند.

۴. الگوهای SQL که تحلیل حجیم را ممکن می‌کنند

توابع پنجره‌ای به‌جای حلقه: محاسبه سهم هر مشتری، رتبه فروش در هر استان یا میانگین متحرک، در یک پرس‌وجو و با یک بار پیمایش انجام می‌شود. معادل Python آن معمین متحرک، در یک پرس‌وجو و با یک بار پیمایش انجام می‌شود. معادل Python آن معمTITION BY province

sql
SELECT
province,
sale_month,
SUM(amount) AS monthly_sales,
AVG(SUM(amount)) OVER (
PARTITION BY province
ORDER BY sale_month
ROWS BETWEEN ۲ PRECEDING AND CURRENT ROW
) AS moving_avg_۳m
FROM sales
GROUP BY province, sale_month;

پردازش افزایشی به‌جای پردازش کامل: بازپردازش کل تاریخ در هر اجرا، رایج‌ترین منبع هدررفت منابع در سازمان‌های ایرانی است. تنها پنجره تغییر (مثلاً ۷ روز گذشته) پردازش و جایگزین شود.

آماره تقریبی وقتی دقت مطلق لازم نیست: برای شمارش مقادیر یکتا در مقیاس ده‌ها میلیون رکورد، توابع تقریبی مانند APPROX_COUNT_DISTINCT با خطای کوچک، تفاوت محسوسی در زمان اجرا ایجاد می‌کنند. برای داشبورد روند، این معاوضه منطقی است؛ برای گزارش مالی رسمی، نه.

دیدهای تجمیعی از پیش محاسبه‌شده: در Oracle با Materialized View و در SQL Server با Indexed View، محاسبه سنگین یک بار انجام و در پرس‌وجوهای بعدی مصرف می‌شود.

۵. Python بدون فروپاشی حافظه

سه نکته که در پروژه‌های واقعی تفاوت ایجاد می‌کند:
۱. در pandas ستون و فیلتر را در لحظه خواندن اعمال کنید. مستندات رسمی pandas تأکید می‌کند پارامتر columns فقط ستون‌های خواسته‌شده را می‌خواند و filters از عملگرهای مقایسه‌ای و in/not in پشتیبانی می‌کند؛ با موتور pyarrow، فیلتر می‌تواند در سطح ردیف اعمال شود و از چندریسمانی و مصرف حافظه اقتصادی‌تر بهره ببرد.

python
df = pd.read_parquet(
“warehouse/sales.parquet”,
engine=”pyarrow”,
columns=[“customer_id”, “amount”, “sale_date”],
filters=[(“sale_date”, “>=”, “۲۰۲۶-۰۱-۰۱”)],
dtype_backend=”pyarrow”,
)

۲. با dtype_backend=”pyarrow” حافظه را مهار کنید. pandas سنتی برای هر نوع داده‌ای از ساختارهای NumPy استفاده می‌کرد که در مقادیر گم‌شده (null) یا رشته‌ها حافظه زیادی تلف می‌کردند. با Arrow backend، نوع داده‌های nullable با مصرف حافظه کمتر در دسترس هستند. اما یک نکته مهم: Arrow backend به‌تنهایی پردازش out-of-core ایجاد نمی‌کند؛ pandas همچنان تمام DataFrame را در حافظه اصلی نگه می‌دارد.
۳. پردازش تکه‌ای (Chunking) را برای خروجی‌های بزرگ فعال کنید. اگر مجبورید یک فایل CSV بسیار بزرگ را با pandas پردازش کنید و در حافظه جا نمی‌شود، از Chunking استفاده کنید. این روش حافظه را در حد مرز مشخصی نگه می‌دارد، هرچند همچنان باید کل فایل پارس شود.

python
for chunk in pd.read_csv(“sales.csv”, chunksize=۵۰۰_۰۰۰, dtype_backend=“pyarrow”):
processed_chunk = transform(chunk)
save_chunk(processed_chunk)

۶. انتخاب موتور اجرا در سال ۲۰۲۶

تحقیقات و نتایج بنچمارک‌های منتشر شده ، PDS-H (مشتق‌شده از TPC-H) روی یک ماشین با سخت‌افزار مشخص (AWS c۷a.۲۴xlarge با ۹۶ هسته پردازشی و ۱۹۲ گیگابایت حافظه) الگوی جالبی از زمان کل اجرای کل پرس‌وجوها نشان می‌دهد:
Polars Streaming: ۱.۰ برابر زمان پایه (۳.۸۹ ثانیه)
DuckDB: ۱.۵ برابر زمان پایه (۵.۸۷ ثانیه)
Polars In-memory: ۲.۵ برابر زمان پایه (۹.۶۸ ثانیه)
PySpark: ۳۰.۹ برابر زمان پایه (۱۲۰.۱۱ ثانیه)
pandas: ۹۴.۰ برابر زمان پایه (۳۶۵.۷۱ ثانیه)
تحلیل این ارقام:
در یک سرور منفرد (Single Node): موتورهایی مثل DuckDB و Polars که برای کار روی یک ماشین بهینه‌سازی شده‌اند، با اختلاف زیاد از موتورهایی مثل PySpark که سرریزی ناشی از مدیریت شبکه و توزیع کار در کلاستر را دارند، سریع‌تر هستند.
پردازش خارج از حافظه (Out-of-core): وقتی داده بزرگ‌تر از رم است، DuckDB می‌تواند در صورت نیاز با ریزش موقت به دیسک (Spilling to disk) پردازش را ادامه دهد.
Spark برای مقیاس‌های فراتر: وقتی حجم داده فراتر از گنجایش یک سرور بزرگ است و باید روی ده‌ها ماشین توزیع شود، کلاستر Spark یا Dask معنا پیدا می‌کنند.
توصیه عملی: اگر اندازه داده روی دیسک کمتر از ۱ ترابایت است، به کلاستر فکر نکنید؛ روی یک ماشین قوی با DuckDB یا Polars کار را سریع‌تر و ارزان‌تر تمام کنید. این موضوع ارتباط تنگاتنگی با ابزارهای تحلیل داده سازمانی (مقایسه) دارد.

۷. چارچوب مهار چالش‌های حافظه در عمل

اگر با خطای Out of Memory مواجه می‌شوید، این مراحل را برای پایداری پردازش طی کنید:
محدود کردن دستی منابع موتور: برای جلوگیری از متوقف شدن برنامه توسط سیستم‌عامل (OOM Killer)، محدودیت حافظه و پردازنده را صریحاً تعریف کنید. در DuckDB این کار به شکل زیر انجام می‌شود:

sql
SET threads = ۴;
SET memory_limit = ‘۱۶GB’;
SET preserve_insertion_order = false;

کاهش دقت نوع داده: استفاده از Int۳۲ به‌جای Int۶۴ یا Float۳۲ به‌جای Float۶۴ مصرف حافظه را به نصف کاهش می‌دهد. برای ستون‌های دسته‌بندی با تعداد حالت‌های محدود، تبدیل به نوع Category در pandas یا ستون‌های عددی کوچک مانند TINYINT در پایگاه داده اهمیت دارد.
جداسازی خروجی از حافظه: از نگه‌داشتن جدول‌های میانی در حافظه خودداری کنید. هر جا ممکن است، خروجی را مستقیماً به دیسک هدایت کنید.

sql
COPY (
SELECT customer_id, SUM(amount) AS total_amount
FROM read_parquet(‘sales/*.parquet’)
GROUP BY customer_id
)
TO ‘aggregated.parquet’
(FORMAT parquet, COMPRESSION zstd);

۸. پرسش‌های متداول (FAQ)

آیا باید به‌طور کامل pandas را کنار بگذاریم؟
خیر. pandas برای تحلیل‌های اکتشافی (EDA) و کار روی داده‌های تا چند گیگابایت مناسب است. اما برای داده‌های ده‌ها گیگابایتی یا خط‌لوله‌های تولیدی (Production Pipelines)، استفاده از DuckDB برای تبدیل و تجمیع، و سپس استفاده از pandas روی خروجی‌های خلاصه شده، رویکرد مطمئن‌تری است.
چرا DuckDB روی تک‌سرور از Spark سریع‌تر است؟
Spark برای کار در محیط توزیع‌شده با هزینه ارتباطی شبکه بین گره‌ها طراحی شده است. زمانی که داده در یک سرور جا می‌شود، این ویژگی‌ها به هزینه سربار تبدیل می‌شوند. DuckDB با استفاده از پردازش برداری (Vectorized Execution) و عدم نیاز به راه‌اندازی JVM، پردازنده را بهینه‌تر مصرف می‌کند.
در ایران، با وجود سامانه‌های موروثی و بانک‌های اطلاعاتی قدیمی، چطور از این ابزارها استفاده کنیم؟
در چنین محیط‌هایی، بهترین روش اجرای یک کپی شبانه (Replication) سبک از جدول‌های اصلی به فایل‌های Parquet روی یک فضای ذخیره‌سازی مشترک است. تحلیل‌گران به این فایل‌ها متصل می‌شوند و پردازش تحلیلی بدون اثرگذاری روی پایگاه داده عملیاتی سازمان انجام می‌شود.