چگونه به عنوان یک مهندس داده در SQL پیشرفت کنیم؟

مقدمه

دانش SQL از مهمترین مهارت‌هایی است که یک مهندس داده باید داشته باشد. تسلط بر SQL و درک آنچه که می‌توان با آن انجام داد قطعا شما را به یک مهندس داده بهتری تبدیل کند.

اگر شما هم به موارد زیر فکر می کنید؟

• در مورد چگونگی بهبود دانش SQL خود فراتر از اصول اولیه اطلاعی ندارید.
• آموزش‌های آنلاین به اندازه کافی عمیق نیستند.
• اینکه شما برخی از مهارت‌های کلیدی SQL را به خوبی یاد نگرفته‌اید.

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

مهارت‌های SQL

به‌ عنوان یک مهندس داده، مهارت‌های SQL شما برای مدل‌سازی داده‌ها و یا وظایف تحلیلی بسیار مهم است. ما برای این مقاله فقط بر روی انبارهای داده تمرکز خواهیم کرد.

 مدل‌سازی داده‌ها

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

 الزامات جمع‌آوری

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

۱. این داده‌ها نشان دهنده چیست و چرا به آن‌ها نیاز است؟
۲. کاربر نهایی این جداول چه کسانی هستند؟
۳. روال کسب و کاری که این داده‌ها را تولید می‌کند کدام است؟ این داده‌ها چگونه تولید می‌شوند؟
۴. چه تعداد کوئری متفاوت، انتظار می‌رود کاربر نهایی اجرا کند؟
۵. تعداد مورد انتظار کوئری‌های خوانده شده در دقیقه چقدر است؟
۶. زمان قابل قبول برای اجرای کوئری و خواندن از جداول چقدر است؟
۷. تعداد رکوردهای روزانه مورد انتظار چقدر است؟
۸. محدوده تاریخ و یا سایر فیلترها برای کوئری‌های خوانده شده کدام است؟
۹. محدوده تاریخی داده‌هایی که برای کوئری باید در دسترس باشد کدام است؟

پاسخ به این سؤالات نحوه مدل‌سازی و تبدیل داده‌ها را مشخص می‌کند.

 کاوش داده‌ها

این گام مربوط به کاوش داده‌ها است. کاوش داده‌ها عبارتند از: بررسی مشکلات داده‌ها، اعتبارسنجی مفروضات، افزایش اندازه تقریبی داده‌ها، اعتبار سنجی قوانین تجاری، بررسی ردیف‌های مفقود یا تکراری در پیوندها و غیره است.
شما باید داده‌های خام را در انبار داده خود بارگذاری کنید. راه‌های مختلفی برای بارگذاری داده‌ها در انبار داده وجود دارد. برای کاوش، داده‌ها را در یک سیستم ذخیره‌سازی ابری ریخته و از دستور COPY INTO برای بارگذاری داده‌های خام در انبار داده می‌توانید استفاده کنید.
برخی از نکاتی که برای مدل‌سازی نیاز به بررسی آن دارید عبارتند از:

۱. بررسی شِمای داده‌ها

آیا نوع داده‌ها با ستون‌ها سازگار هستند؟
آیا نام ستون‌ها یکسان است؟

۲. بررسی کیفیت داده‌ها

آیا تمام رکوردهای مربوط به فایل‌های خام در جداول مربوطه بارگذاری شده است؟
بررسی کنید ستون با مقادیر خالی نداشته باشید.
آیا هر یک از مقادیر ستون، دارای فیلد جداکننده است؟ اکثر انبارهای داده گزینه‌هایی برای مدیریت این موارد دارند.

۳. اعتبارسنجی مفروضات تجاری

اگر داده‌ها را با سایر جداول مرتبط با کسب‌وکار پیوند دهید، آیا ردیف‌های تکراری غیرمنتظره یا از دست رفته داده خواهید داشت؟ اگر چنین اتفاقاتی افتاد بررسی کنید علت از کجاست؟
آیا تعداد ردیف‌ها در هر روز الگوهای واضحی را نشان می‌دهد؟
آیا همه جداول دارای شناسه منحصر به فرد هستند؟
آیا برای هر جدول موجودیت تجاری، جدولی وجود دارد که هر به روز رسانی انجام شده در آن جدول ثبت شود؟
مقادیر با معنای خاص را بشناسید. به عنوان مثال. گاهی اوقات مقدار -۹۹۹۹ می‌تواند برای نشان دادن NULL استفاده شود.

این روال بررسی، مداوم خواهد بود. از آنجایی که فرایند تولید داده در فرایندهای بالادستی می‌تواند تغییر کند، ممکن است مشکلات داده‌های اضافی و غیره پیدا کنید.

 مدل‌سازی

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

برخی از نکاتی که برای مدل‌سازی نیاز به بررسی دارید عبارتند از:

۱. قراردادهای نام‌گذاری: هر شرکتی استاندارد نام‌گذاری خود را دارد. اگر شرکت شما این استاندارد را ندارد حتما آن را پایه‌گذاری کنید.
۲. جداول SCD2: اکثر جداول موجودیت تجاری دارای این خصیصه هستند که در طول زمان تغییر می‌کنند. ایجاد یک جدول SCD2 برای ثبت تغییرات تاریخی را در نظر بگیرید.
۳. پیش جمع‌آوری داده‌ها: گاهی اوقات، الگوی کوئری مورد انتظار، نیاز به جمع‌آوری داده‌ها با جزئیات بالاتری دارد. در این موارد، اگر زمان خواندن بیشتر از زمان مورد انتظار بود، باید داده‌های خود را در یک برنامه زمان‌بندی شده از قبل جمع‌آوری کنید. پیش جمع‌آوری داده‌ها باعث می‌شود «کوئری‌های خواندنی» بسیار سریع‌تر اجرا شوند، اما سربارهای اضافی ایجاد، زمان‌بندی و نگهداری خط پردازش داده را مدنظر داشته باشید.
۴. جداول مسطح: اگرچه مدل کیمبال بسیار محبوب است، اما کوئری و پیوند زدن میان چندین جدول برای کاربر نهایی خسته کننده است. راه حل تیم داده برای ارائه یک رابط روان برای کاربر نهایی، ایجاد یک جدول (یا view) مسطح است. جدول مسطح جدولی است که تمام ستون‌های جداول حقایق و ابعادی را دارا است. کاربر نهایی نیازی به درگیر شدن با پیوند میان جداول ندارد و می‌تواند روی تجزیه و تحلیل داده‌ها تمرکز کند.

نکته مهم در مورد استفاده از جداول مسطح: چنان که می‌دانید در یک جدول مسطح، اگر برخی از ویژگی‌های جداول ابعادی در طول زمان تغییر کنند، اجرای یک کوئری روی آن‌ها ممکن است نتایج نادرستی ایجاد کند. برای رفع این مشکل می‌توانید با داشتن دو جدول (دو view)، یکی با ویژگی‌های جدول ابعادی با توجه به لحظه زمانی و دیگری با جدیدترین ویژگی‌های جدول ابعادی، این مشکل را برطرف می شود.

 ذخیره‌سازی داده‌ها

ذخیره‌سازی داده‌ها در قالب مناسب می‌تواند به طور قابل توجهی بر عملکرد کوئری‌ها تأثیرگذار باشد. هنگام مدل‌سازی جداول کاربر نهایی، مطمئن شوید که تأثیر ذخیره‌سازی داده‌ها بر کوئری‌های نوع خواندنی را در نظر گرفته‌اید.

درک مفاهیم زیر بسیار مهم است:

• پارتیشن‌بندی: پارتیشن‌بندی یا خوشه‌بندی می‌تواند میزان داده‌های اسکن شده را به میزان قابل توجهی کاهش دهد و در نتیجه باعث کاهش هزینه شود.
• فرمت‌های ذخیره‌سازی: فرمت‌های Parquet یا ORC می‌توانند اندازه داده‌ها را به میزان قابل توجهی کاهش داده و سرعت تبدیل را افزایش دهند.
• مرتب‌سازی: مرتب‌سازی می‌تواند میزان داده‌های خوانده شده را کاهش دهد و تبدیل‌ها را کارآمدتر کند.
• ذخیره‌سازی ابری: جداول خارجی این امکان را فراهم می‌آورد تا داده‌ها در یک سیستم ذخیره‌سازی ابری ذخیره شوند و در صورت لزوم خوانده شوند.

هر انبار داده در رابطه با موارد فوق دارای شرایط خاص خودش است. به عنوان مثال، Snowflake به طور خودکار بسیاری از این موارد را برای شما انجام می‌دهد، در حالی که Redshift به رویکرد دستی بیشتری نیاز دارد. برای فهمیدن این موضوع داکیومنت‌های انبار داده‌تان را مطالعه کنید.

تبدیل داده‌ها

در زمان تبدیل داده‌های خام به مدل داده‌های کاربر نهایی موارد زیر می‌تواند تأثیرگذار باشد

• زمان پردازش داده‌ها.
• هزینه انبار داده.
• سرعت توسعه خط پردازش داده.

هنگام برنامه‌ریزی برای تبدیل داده، سؤالات زیر را در نظر بگیرید:

• چگونه داده‌های ذخیره شده به حافظه موتور انبار داده منتقل و پردازش شوند؟
• چگونه می‌توانیم برنامه‌های موتور انبار داده را برای اجرای تبدیلات بررسی کنیم؟

انواع تبدیلات

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

دو نوع اصلی از تبدیل وجود دارد که در زیر توضیح داده شده است.

 تبدیلات باریک

تبدیلات باریک درگیر جا به جایی داده‌ها در ماشین‌های داخل انبار نمی‌شوند. این تبدیلات بدون نیاز به جا به جایی ردیف‌ها به ماشین‌های دیگر در انبار، اعمال می‌شوند. تبدیلاتی مانند توابع Lower()، Concat() و غیره مستقیماً روی داده‌های حافظه اعمال می‌شوند.

 تبدیلات عریض

تبدیلات عریض درگیر مسئله جابه‌جایی داده‌ها در ماشین‌های داخل انبار می‌شوند. به عنوان مثال، وقتی میان دو جدول عمل پیوند انجام می‌شود، موتور انبار داده، داده‌های جدول کوچک‌تر را به ماشینی که داده‌های جدول بزرگ‌تر در آن قرار دارد منتقل می‌کند. این انتقال برای پیوند میان دو جدول لازم است. انتقال داده‌ها در یک سیستم توزیع شده یک عملیات پرهزینه است. به همین علت موتور انبار داده باید بهینه شود تا جابجایی داده‌ها را به حداقل برساند.

عملیات زیر برای انجام نیاز به جا به جایی داده دارند:

• عملیات پیوند (join) و آنتی پیوند (anti join)
• توابع رشته ای (string)، عددی (numeric) و تاریخ (date)
• توابع گروه‌بندی (Group by)، تجمیع (aggregates)، مرتب‌سازی (order by)، اتصال (union)، داشتن (having)
• CTEها
• توابع Window
• Parsing JSON
• Stored procedures, sub queries

برخی از نکاتی که نیاز به بررسی آن‌ها دارید عبارتند از:

۱. چگونه زمان تبدیل با افزایش اندازه داده افزایش می‌یابد؟ این افزایش به صورت خطی است یا حتی بدتر؟ نکته: cross join به صورت خطی افزایش نمی‌یابد.
۲. داکیومنت انبار داده را بخوانید تا از ویژگی‌هایی موجود آگاه شوید. این کار به شما امکان می‌دهد در صورت نیاز به استفاده از یک ویژگی به داکیومنت بازگردید. اکثر تغییرات را می‌توان در انبار داده انجام داد.
۳. هنگام ارزیابی عملکرد، از خوانده شدن کش در کوئری‌های بعدی کسب اطلاع کنید.
۴. در صورت امکان، داده‌ها را قبل از عمل تبدیل و یا در حین انجام تبدیل فیلتر کنید.
۵. اکثر کوئری‌های SQL ترکیبی از تبدیل‌های گسترده و باریک هستند.

 طراح کوئری

طراح کوئری به شما امکان می‌دهد مشاهده کنید موتور انبار داده چه مراحلی را برای اجرای کوئری شما انجام می‌دهد. برای مشاهده روال انجام کوئری می‌توانید از دستور EXPLAIN استفاده کنید. اکثر داکیومنت‌های انبار داده دارای مراحلی هستند که می‌توانید برای بهینه‌سازی کوئری‌های خود اقدام کنید.
به طور خلاصه

• برای دیدن روال انجام کوئری از دستور EXPLAIN استفاده کنید.
• مراحلی را که بیشترین هزینه را دارند بهینه کنید.
• از داکیومنت‌های انبار داده موجود برای گرفتن کمک جهت بهینه‌سازی استفاده کنید.

 تجزیه و تحلیل داده‌ها

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

چرا مورد X اتفاق می‌افتد؟ یا چرا مورد X اتفاق نمی‌افتد؟
چرا مورد X با وجود Y اتفاق می‌افتد؟ یا چرا مورد X با وجود Y اتفاق نمی‌افتد؟
آیا داده‌ها اشتباه است؟
چرا تغییر در یک روند خاص وجود دارد؟
چرا X برای همه نهادهای تجاری اتفاق می‌افتد اما برای این یک نهاد خاص اتفاق نمی‌افتد؟

پاسخ به این نوع سوالات به طور کلی شامل موارد زیر است:

• برای درک روشنی از سوال توضیحات بیشتر بخواهید.
• در صورتی که ادعای کاربر نهایی صحت داشته باشد، باید بررسی انجام ‌شود.
• درک و اعتبارسنجی کوئری کاربر نهایی.
• بررسی اینکه آیا فیلترهای مورد نیاز از دست رفته است یا خیر.
• بررسی اینکه خط پردازش داده‌ای که داده‌ها را تولید می‌کند به درستی اجرا شده است یا خیر.
• داده‌ها بررسی کنید تا بتوانید به سوالات پاسخ دهید. گاهی اوقات ممکن است داده‌های کافی برای پاسخ به سوال وجود نداشته باشد. این فرصتی است برای
• راه‌اندازی فرایندی برای گرفتن آن داده‌ها.

از دانش خود در مورد داده‌ها، ذخیره‌سازی، تبدیل و خط پردازش داده برای پاسخ به این نوع سوالات استفاده کنید. مشکلات مطرح شده در سایت Leetcode مکان خوبی برای تمرین کوئری‌های تحلیلی است.

تمرین

تمرین یکی از بهترین راه‌ها برای بهبود دانش SQL است. TPC-H یک مجموعه‌داده محبوب است که برای ارزیابی عملکرد انبار داده استفاده می‌شود. این مجموعه‌داده در شِمای داده زیر نشان داده شده است.

داده‌ها را می‌توان با استفاده از ابزار tpch-dbgen تولید کرد. می‌توانید آن را نصب کنید و مانند شکل زیر شروع به تولید داده کنید. توجه داشته باشید که با این کار ۱ گیگابایت داده تولید می‌شود. می‌توانید مجموعه‌داده‌هایی در اندازه‌های ۱، ۱۰، ۱۰۰ گیگ و غیره ایجاد کنید.

git clone https://github.com/electrum/tpch-dbgen.git
cd tpch-dbgen
make
./dbgen
ls -ltha | grep .tbl
/*
# 389B Oct 26 19:18 region.tbl
# ۲.2K Oct 26 19:18 nation.tbl
# 113M Oct 26 19:18 partsupp.tbl
# 23M Oct 26 19:18 part.tbl
# 725M Oct 26 19:18 lineitem.tbl
# 164M Oct 26 19:18 orders.tbl
# 23M Oct 26 19:18 customer.tbl
# ۱.3M Oct 26 19:18 supplier.tbl
*/
head -3 lineitem.tbl # to see the data, delimiter, etc

برای تمرین مهارت‌های SQL خود مراحل زیر را دنبال کنید:

• جداول انبار داده را برای کوئری‌های تحلیلی ایجاد کنید. با SLA اکثر خواندن‌ها کمتر از ۵ ثانیه طول خواهد کشید.
• به ۲۲ سوال TPC-H پاسخ دهید و عملکرد آنها را با استفاده از قسمت طراح کوئری مورد تجزیه و تحلیل قرار دهید. این سوالات را می‌توانید در صفحات ۲۹ تا ۶۶ در مشخصات استاندارد TPC-H بیابید.
• تمرین بالا را در انبارهای داده مختلف انجام دهید تا الگوهای مشابه و تفاوت‌های ظریف میان آن‌ها را درک کنید.

نتیجه‌گیری

امیدواریم این مقاله به شما ایده خوبی در مورد چگونگی بهبود مهارت‌های SQL تان داده باشد. به طور خلاصه، بهتر شدن در دانش SQL شامل موارد زیر است.

• مدل‌سازی داده‌ها: کاوش داده‌ها، مدل‌سازی و ذخیره‌سازی.
• تبدیل داده‌ها: به حداقل رساندن جا به جایی داده‌ها در انبار داده، بهینه‌سازی روال انجام شدن کوئری و مدیریت دسترسی به داده‌ها.
• تجزیه و تحلیل داده‌ها: درک فرایند تولید داده، جداول انبار داده و پرسیدن سوالات روشن.
• تمرین: اگرچه در این مقاله یک بخش تمرین با استفاده از مجموعه‌داده TPC-H ارائه شد، اما دقت داشته باشید داده‌های واقعی در محیط تجاری نامرتب خواهند بود.

شما باید نحوه تولید داده‌ها را بیابید، مفروضات تجاری را بررسی کنید، فرایندی را برای جمع‌آوری داده‌های بیشتر راه‌اندازی کنید، جداول ابعادی ایجاد کنید و غیره.

منابع

https://www.startdataengineering.com/post/improve-sql-skills-de