استخراج، تبدیل و نمایش داده با R مبتنی بر پایگاه داده SQL Server

مقدمه

زبان R، چارچوبی است که به منظور مدل‌سازی، تجزیه-تحلیل و نمایش گرافیکی دادگان از آن استفاده می‌شود. یکی از نکات قابل توجه و با ارزش این زبان این است که با محیطی ساده، امکان اتصال و بهره‌گیری از پایگاه داده‌های قدرتمند دنیا نظیر SQL Server، اوراکل را فراهم می‌آورد. برخلاف مقاله‌ قبلی با عنوان ” مقدمه ای بر سرویس‌های یادگیری ماشین در SQL Server 2017” که از R Services جهت اجرای دستورات R درون SQL Server استفاده شد، در این مقاله سعی می‌شود تا روش دیگری از کار را به نمایش بگذاریم. بدین منظور از نرم افزار محبوب RStudio استفاده و نشان می‌دهیم که چگونه می‌توان با استفاده از این محیط به SQL Server وصل شده و چگونه کارهای چون استخراج، تبدیل و نمایش داده را انجام داد.
با اجرای RStudio، محیطی شبیه به شکل ۱-۱ نمایش داده می‌شود که برای اجرای دستورات می‌توان از Console یا محیط R استفاده کرد.

آماده‌سازی محیط و استخراج داده از SQL Server

به منظور تحلیل‌داده، یکی از گام‌های کلیدی، استخراج داده از منبع‌داده می‌باشد. از آنجایی که در این مقاله منبع داده‌ی مورد نظر SQL Server است، بنابراین باید اقدامات لازم در این خصوص انجام داد. اولین اقدام آماده‌سازی محیط می‌باشد، در واقع باید پکیج‌های لازم برای این کار را به پروژه‌ی تعریف شده اضافه کنیم. پیکچ‌ها در R همان کتابخانه‌ها در زبان‌های برنامه‌نویسی نظیر C++ هستند که می‌توان گفت هر پکیج از چندین تابع تشکیل شده است تا هدف خاصی را دنبال کند.
در این مقاله ما سعی می‌کنیم از سه پکیج RODBC، Dplyr و Ggplot2 بهره‌گیریم. RODBC، شرایطی را فراهم می‌کند که بتوان به منبع داده متصل شد، Dplyr، شامل مجموعه‌ای از ابزارها برای بکارگیری داده‌ها است و Ggplot2که وظیفه نمایش گرافیکی داده را به عهده دارد. باید توجه داشته که به منظور استفاده از پکیج مورد نظر تنها یک بار می‌بایست آن را نصب کنیم و به تعداد دفعات لازم با فراخوانی آن پیکج از آن استفاده کرد. از این رو ما اول نحوه نصب و سپس استفاده از آن را شرح می‌دهیم.
به منظور نصب هر پکیج از دستور()install.packages استفاده می‌شود. به عنوان مثال اگر برای اولین بار است که پکیج RODBC نصب می‌شود، می‌توانید از دستور زیر استفاده کنید.

install.packages(“RODBC”)

از آنجایی که ما هر سه پکیج را برای اولین بار نصب می کنیم، بنابراین از دستور() install.packages برای نصب هر پکیج به طور جداگانه استفاده می‌کنیم.

با نوشتن دستورات لازم مطابق شکل ۲-۱ و فشردن دکمه Run کار دانلود و نصب هر پکیج انجام می‌شود. باید توجه داشت در صورتی که هر پکیج برای اجراء نیاز به پکیج دیگری داشته باشد، پکیج‌های لازم به طور اتوماتیک توسط خود سیستم شناسایی شده و نصب می‌شود. حال که مراحل دانلود و نصب پکیج‌های پیش‌نیاز با موفقیت انجام شد، باید آنها را فراخوانی کنیم. برای فراخوانی پکیج‌ها مطابق شکل ۲-۲ از دستور() library استفاده می‌شود.
تا این مرحله از کار آماده‌سازی محیط با موفقیت انجام شده است، پس از این، لازم است کار اتصال به منبع داده‌ی که در اینجا SQL Server هست، انجام شود. برای این کار باید تابع odbcDriverConnect با آرگومانهای لازم فراخوانی شود. این تابع درون پکیج RODBC قرار دارد. اکنون ما نیاز داریم تا آبجکتی تعریف کنیم که برای اتصال به SQL Server از آن استفاده کنیم.

conn <- odbcDriverConnect('driver={SQL Server};server=Majid_Javadi;database=
AdventureWorks2016CTP3;trusted_connection=true')

– Conn  آبجکتی برای نگه‌داری جزئیات اتصال است.
– Driver  در این قسمت نوع منبع داده را معرفی می‌کنیم.
– Server  سروری که منبع داده در آن قرار دارد در قسمت مشخص می‌شود.
– Database  نام پایگاه داده مورد نظر جهت اتصال به آن در این قسمت تعریف می‌شود.
– Trusted_Connection  این قسمت مشخص کننده‌ی مجوز دسترسی به منبع داده است.
با اجرای دستور فوق کار اتصال به منبع داده‌ی با موفقیت انجام می‌شود. حال نیاز داریم تا داده‌های مورد نظر را از آن استخراج نمائیم. ما از دستور SQLQuery برای اجرای دستورات SQL استفاده می‌کنیم که داری دو آرگومان به ترتیب آبجکت اتصال و دستور SQL هست. در این مقاله سعی شده است تا اطلاعات موجود در جدول TransactionHistoryArchive از پایگاه داده AdventureWorks2016CTP3 واکشی شود.

data <- sqlQuery(conn, "SELECT * FROM Production.TransactionHistoryArchive;")

با اجرای دستور فوق دادگان مورد نظر واکشی شده و درون آبجکت data قرارمی‌گیرد. در این مثال ما تنها از یک کوئری ساده استفاده کردیم، این درحالیست که می‌توان کوئری‌های پیچیده، فراخوانی روال و سایر روش های دیگر را به منظور واکشی داده استفاده کرد.بعد از اجرای دستورات پنجره Environment همانند شکل ۲-۳ به محتویات قابل مشاهده تغییر می‌کند که اطلاعات کلی از آبجکت‌های ایجاد شده نمایش داده می‌شود. به عنوان مثال، داده‌های که از پایگاه داده واکشی شده‌اند در ۸۹۲۵۳ سطر با ۹ ستون می‌باشد. این دقیقا معادل با نتایج بدست آمده در SQL Server می‌باشد که در شکل ۲-۴ قابل مشاهد است.حال فرض کنید تنها دو ستون TransactionDate و ActualCost را بخواهیم داشته باشیم. برای اینکه می‌توان از تابع Select در محیط RStudio استفاده کرد. این متد درون پکیج Dplyr قرار دارد. با اجرای دستور زیر تعداد ۹ متغییر به ۲ متغییر تغییر یافته و درون آبجکت Data بازنویسی می‌شود. این تغییرات در شکل ۲-۵ قابل مشاهده است.

data <- select(data, TransactionDate, ActualCost)

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

یکی از کارهای رایجی که در محیط‌های تجاری زیاد انجام می‌شود، استفاده از توابع aggregating می‌باشد. در این مقاله فرض کنید می‌خواهیم از داده های بدست آمده در مرحله قبل، جمع هزینه(ActualCost) واقعی به ازای تاریخ تراکنش(TransactionDate ) را بدست آوریم. اگر قرار باشد این کار را با T-SQL انجام دهیم باید دستورات زیر را اجراء کنیم.

SELECT sum(ActualCost) as ActualCostASDate, TransactionDate
FROM Production.TransactionHistoryArchive
group by TransactionDate
order by TransactionDate

افرادی که با T-SQL کار کرده‌اند می‌دانند که با اجرای این دستورات خروجی به شکل ۳-۱ نمایش داده می‌شود. جمع هزینه واقعی به ازای هر تاریخ تراکنش محاسبه شده و در خروجی ظاهر می‌شود.برای اجرای همین عملیات در RStudio باید از دستور Group_by و Summarize استفاده نمود.

data <- group_by(data, TransactionDate)
data <- summarize(data, sum(ActualCost))

با اجرای دستورات فوق خروجی به شکل ۳-۲ بدست می‌آید.خروجی بدست آمده از نوع ،Tibble هست که نشان دهنده زیر مجموعه‌ی از داده می‌باشد. برای ادامه کار این نوع از داده بدست آمده را به فرمت پُر استفاده Data.Frame در R تبدیل می‌کنیم. برای این منظور دستور زیر را درون Console اجراء می‌کنیم.

data <- arrange(data, TransactionDate) %>% as.data.frame
View(data)

با اجرای دستور فوق، تبدیل به موفقیت انجام شده و خروجی به شکل زیر نمایش داده می‌شود.یکی از کارای که در تبدیل داده امکان دارد که لازم داشته باشیم، اضافه کردن یک ستون دیگر به مجموعه داده موجود می‌باشد. در این مثال ما تبدیل TransactionDate به نوع Date را لازم داریم و این ستون را به مجموعه داده موجود اضافه می‌کنیم. برای این امر از دستور mutate با فرمت زیر استفاده می شود.

data <- mutate(data, TransactionDate = as.Date(TransactionDate) )

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

بصری سازی

بصری سازی داده در R می‌تواند هم بسیار ساده و هم بسیار پیچیده باشد. برای این منظور پکیج‌های متعددی وجود دارد که می‌توان از آنها استفاده کرد. با این وجود یکی از محبوب‌ترین آنها استفاده از Plot می‌باشد. استفاده از این تابع بسیار سراست هست، یعنی با مشخص کردن محور x، محور y و نوع آن می‌توان این کار را انجام داد. با اجرای دستور زیر خروجی به شکل ۴-۱ ظاهر می‌شود.

plot(data$"sum(ActualCost)"~data$TransactionDate, type="h")

در اینجا محور x با مقادیر موجود Data$”Sum(ActualCost)” و مقدار y با Data$TransactionDate پُر می‌شود. Type هم نوع نمایش خروجی را مشخص می‌کند. نوع نمایش خروجی در این مثال از H استفاده شده که به معنی هیستوگرام است. P به معنی نمایش نقطه‌ای و l نمایش خطی را مشخص می‌کند.شاید این سوال برای شما پیش بیاد چگونه می‌توان متن سطر و ستون را فارسی و عنوان جدیدی به نمودار خروجی داد. برای این کار باید کمی تغییرات در آرگومان‌های تابع Plot داشته باشیم. با اجرای دستورات زیر این امر اتفاق می‌افتند.

plot(data, main = "هیستوگرام هزینه واقعی بر حسب تاریخ", xlab = "تاریخ", ylab = "هزینه واقعی" , type="h")