استفاده از SQL Server PolyBase برای اتصال به مونگو

امروزه استفاده از دیتابیس مونگو MongoDB در سازمان‌ها مرسوم شده و معمولا پیش میاد که اپلیکیشن‌ها و سرویس‌ها به طور همزمان، به دیتابیس مونگو و دیتابیس SQL Server متصل بشن.

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

• استخراج دیتا از دیتابیس مونگو و درج اون در دیتابیس SQL Server و سپس تهیه گزارش

اینکار زمان‌بر هست و باید به صورت آفلاین انجام بشه، لذا نمی‌تونه گزارش آنلاینی رو برای شما فراهم کنه.

• استفاده از قابلیت PolyBase در SQL Server 2019

قابلیت PolyBase چیست ؟

با استفاده از PolyBase امکان اجرا کوئری T-SQL بر روی منبع دیتا خارجی ( external data source) در SQL Server فراهم میشه. از نسخه 2016 SQL Server امکان اتصال به Hadoop و Azure Blob Storage فراهم شد و با انتشار SQL Server 2019 قابلیت اتصال به external data از نوع Oracle ، Teradata و همچنین MongoDB افزوده شد.

نکته مهم و کاربردی این قابلیت: همان کوئری که به external data دسترسی داره می‌تونه روی دیتابیس اصلی SQL Server هم اجرا بشه. برای مثال ما می‌تونیم یک کوئری Join بین دیتا داخلی و دیتا خارجی که مثلا در دیتابیس مونگو هست اجرا کنیم و به صورت آنلاین گزارش دلخواه رو تولید کنیم.

چطور با PolyBase به دیتابیس مونگو متصل بشیم ؟

برای اینکار یک اینستنس SQL Server 2019 لازم داریم که ویژگی PolyBase در اون نصب شده باشه .

در صورتی که قبلا این قابلیت رو نصب نکردین، به سراغ فایل نصب SQL Server رفته و این feature رو به اینستنس نصب شده، اضافه می‌کنیم.

با اجرای SQL Server Management Studio تصویر زیر رو خواهیم داشت .

خب حالا نیاز داریم یک دیتابیس مونگو داشته باشیم، من قبلا MongoDB رو به همراه IDE مناسب Robo 3T نصب کردم.

با اجرای Robo 3T به دیتابیس مونگو وصل شده و Collection موجود در اون رو باز می‌کنیم.

در این مثال، ما یک دیتابیس با نام ForSQLServer داریم که شامل یک Collection بانام Table1 هست. مفاهیم در مونگو کمی با SQL Server متفاوت هستند، مفهوم Collection میتونه معادل Table در SQL Server در نظر گرفته بشه.

در داخل Table1 فعلا هیج دیتایی وجود نداره.

درج دیتا به صورت Document در مونگو انجام می‌شه. به این معنی که هر رکوردی که در Collection درج کنیم، درواقع یک داکیومنت در اون ایجاد کرده‌ایم .

خب در اینجا ما یک رکورد یا همون داکیومنت در Collection مونگو درج می‌کنیم.

و با اسکریپ اجرا شده، می‌تونیم نتیجه درج داکیومنت رو در مونگو ببینیم.

اتصال PolyBase به مونگو :

در این مرحله به سراغ دیتابیس SQL Server می‌ریم تا تنظیمات لازم رو در اون انجام بدیم.

این تنظیمات شامل 4 مرحله زیر هست :

• ساخت Master key برای دیتابیس

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@123';

• ساخت SCOPED CREDENTIAL برای دیتابیس، دقت کنید که این همان کاربری است که به دیتابیس مونگو وصل میشه، پس لزوما باید دسترسی لازم سمت دیتابیس مونگو رو داشته باشه. اسم این ceredential رو MongoDB گذاشتیم تا بعدا ازش استفاده کنیم.

CREATE DATABASE SCOPED CREDENTIAL MongoDB
WITH IDENTITY = 'username', Secret = 'password';

• یک EXTERNAL DATA SOURCE با نام MongoDBSource می‌سازیم. دقت کنید که Location دقیقا باید آدرس دسترسی به دیتابیس مونگو باشه، در اینجا به صورت لوکال و با پورت پیش فرض 27017 است.

CREATE EXTERNAL DATA SOURCE MongoDBSource
WITH (LOCATION = 'mongodb://localhost:27017',
CREDENTIAL = MongoDB, --specify credential created in step 2
CONNECTION_OPTIONS = 'ssl=false;'
)

• در نهایت ایجاد EXTERNAL TABLE : دقیقا مطابق ساختار داکیومنت درج شده در مونگو، جدول مشابهی در SQL Server می‌سازیم. در انتهای اسکریپت، Location نشون‌دهنده دیتابیس و کالکشن سمت مونگو هست.

CREATE EXTERNAL TABLE Table1
(Name Nvarchar(50) NULL,
lastname Nvarchar(50) NULL,
location Nvarchar(50) NULL,
website Nvarchar(100) NULL )
WITH (
LOCATION='ForSQLServer.Table1',
DATA_SOURCE= MongoDBSource
);

اگر تمام مراحل چهارگانه بالا رو به درستی انجام داده باشیم، حالا یه جدول از نوع External table در دیتابیس SQL Server خواهیم داشت.

همانند جداول داخلی دیتابیس، می‌تونیم روی اون کوئری بزنیم و دیتا مربوط به مونگو رو به صورت آنلاین استخراج کنیم.

مپ نوع دیتا از مونگو به SQL Server :

چند نمونه کوئری SQL در مونگو :

خطاهای احتمالی :

در صورت مشاهده خطا در مرحله EXTERNAL DATA SOURCE با عنوان :

PolyBase Error Msg 46530 – External Data Sources Are Not Supported With Type GENERIC

دو دلیل ممکنه وجود داشته باشه.

1- سرویس polybase بعد از نصب اجرا نشده و فعال نیست. با این اسکریپت میتونین چک کنین :

SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled

2- در صورتی که سرویس فعال بود نیاز هست تا این قابلیت رو روی اینستنس فعال کنید :

exec sp_configure @configname = 'polybase enabled', @configvalue = 1
RECONFIGURE

اگر با خطا

TCP Provider: No connection could be made because the target machine actively refused it.

مواجه شدید معنیش اینکه سروریس PolyBase استاپ شده به SQL Server Configuration Manager مراجعه کنید و سرویس رو استارت کنید.