آرین تاپ لرن

تاریخ انتشار:۱۰:۳۴ ۱۳۹۹/۲/۲۹

Polybase امکانی را به وجود می‌آورد که، SQL Server بتواند از طریق این امکان و با استفاده از T-SQL کوئری، داده‌های موجود در یک منبع داده خارجی را بخواند. در واقع Polybase یک تکنولوژی است، که در SQL Server 2016 معرفی شده و در SQL Server 2019 گسترش یافته است. هدف Polybase برطرف نمودن مشکل Data Movement یا انتقال داده است.

عمل انتقال داده، شامل ایجاد مراحل سنگین و پیچیده Extract Transform Load یا به اختصار ETL، جهت پردازش و انتقال داده از سایر Data Sourceها به درون جداول SQL Server می‌باشد.

Polybase مشکل پیچیده بودن عملیات ETL را از طریق ارائه راه حلی با عنوان Data Virtualization برطرف می‌سازد.

در Data Virtualization، برخلاف عملیات سنتی ETL که بر پایه انتقال داده می‌باشد، داده‌ها در منبع داده باقی می‌مانند و دسترسی Real-Time به سیستم منبع (Source System) جهت خواندن Data ، فراهم می‌شود. مفهوم کلیدی در Data Virtualization این است که انتقال داده صورت نمی‌گیرد و هر زمان که نیاز باشد به Data Source وصل می‌شویم و داده‌ها را می‌خوانیم.

منظور از انتقال داده، خواندن Data از یک منبع داده (مانند DB2، Oracle یا یک SQL Server دیگر) و ذخیره آن در درون جداول SQL Server می‌باشد.

Data Virtualization امکان Integrate نمودن داده‌ها را بدون نیاز به انتقال آن‌ها فراهم می‌سازد.

درSQL Server 2016 و بالاتر امکان دسترسی به External Data در Hadoop و Azure Blob Storage وجود دارد.

در SQL Server 2019 با استفاده از Polybase می‌توان به External Data در SQL Server، Oracle، Teradata و MongoDB دسترسی پیدا کرد.

در ادامه به نحوه نصب و راه اندازی Polybase در SQL Server 2019 و چگونگی استفاده از آن با بررسی یک مثال می‌پردازیم.

نصب Polybase

به این نکته باید توجه داشته باشیم که Polybase فقط به ازای یک SQL Server Instance روی هر کامپیوتر می‌تواند نصب شود.

جهت نصب Polybase، در هنگام نصب SQL Server در قسمت Feature Selection گزینه Polybase Query Service For External Data را انتخاب می‌نماییم.

ما Polybase را به صورت Single Node نصب می نماییم. جهت این کار در هنگام نصب SQL Server در قسمت Polybase Configuration گزینه اول را انتخاب می‌نماییم که به صورت Default نیز انتخاب شده است.

برای اینکه ویژگی Polybase به درستی کار کند باید TCP/IP در حالت Enabled باشد.

تصویر زیر سرویس های مربوط به Polybase را نمایش می‌دهد.

بعد از مرحله نصب باید Polybase را با استفاده از دستور زیر فعال نماییم:

Exec sp_configure N 'Polybase Enabled' , 1

Reconfigure

دستور زیر نصب بودن Polybase را نمایش می‌دهد.

;SELECT SERVERPROPERTY ( 'IsPolyBaseInstalled' ) AS IsPolyBaseInstalled

سه دیتابیس با نام‌های DWConfiguration، DWDiagnostics و DWQueue ایجاد و از آن‌ها استفاده می‌نماید، این دیتابیس‌ها نباید Alter یا حذف شوند.

ما از ورژن SQL Server 2019 (RTM-CU2) جهت پیاده سازی یک مثال استفاده می‌نماییم. در این مثال نحوه خواندن داده‌ها از یک منبع داده خارجی با استفاده از Polybase نمایش داده می‌شود. در اینجا منبع داده خارجی یک SQL Server دیگر است اما در سناریوهای مختلف می‌تواند Oracle، DB2 و غیره باشد.

پیاده سازی یک مثال

در این مثال نحوه دسترسی به یک External Data Source از نوع SQL Server بررسی می‌گردد.

ابتدا از طریق SSMS به یک Instance از SQL Server که به عنوان External Data Source در نظر گرفته شده است وصل می‌شویم، همان طور که گفته شد External Data Source می‌تواند Oracle، DB2، MongoDB و... باشد.

سپس با استفاده از دستورات زیر یک دیتابیس با نام SourceDB ایجاد می‌نماییم:

Use master

GO

If DB_ID ( N 'SourceDB' ) Is Not Null

Begin

Alter Database SourceDB Set Single_User With Rollback Immediate

Drop Database SourceDB

End

GO

Create Database SourceDB

On Primary

(Name = SourceDB, FileName = N 'D:\Databases\Data\SourceDB.mdf' , Size = 128 mb, FileGrowth = 32 ,(mb, Maxsize = Unlimited

FileGroup SourceDBFGData

(Name = SourceDBFData_1, FileName = N 'D:\Databases\Data\SourceDBFData_1.ndf' , Size = 256 mb, FileGrowth = 64 mb, Maxsize = Unlimited)

Log On

(Name = SourceDBLog, FileName = N 'D:\Databases\Data\SourceDB.ldf' , Size = 128 mb, FileGrowth = 32 mb, Maxsize = Unlimited)

GO

Alter Database SourceDB Modify FileGroup SourceDBFGData Default

بعد از ایجاد دیتابیس دو جدول Employees و WorkTime را نیز ایجاد می‌کنیم و آن‌ها را با داده‌های فرضی پر می‌نماییم:

Use SourceDB

GO

Drop Table If Exists WorkTime

Drop Table If Exists Employees

Create Table Employees

(EmpID Int Not Null Primary Key,EmpFName Nvarchar ( 20 ) Not Null , EmpLName Nvarchar ( 30 ) Not Null , EmpAddress Nvarchar ( 50 ) Not Null , [Description] Nvarchar ( 100 ) Not Null )

; With Tbl (r) as ( Select 1 Union All Select r + 1 From Tbl Where r < 50000 )

Insert Into Employees

,'Select r, CONCAT ( 'FName' , r), CONCAT ( 'LName' , r), CONCAT ( 'Address' , r), CONCAT ( 'Description r) From Tbl

Option (Maxrecursion 0 )

GO

Create Table WorkTime

(EmpID Int Not Null ,ClDate Date Not Null , EnterTime Time Not Null , ExitTime Time Not Null )

Create Unique Clustered Index IXCEmpIDClDate On WorkTime (EmpID, ClDate)

Alter Table WorkTime ADD Constraint FK_EmpID Foreign Key ( EMPID ) References Employees ( EMPID )

;( With TblTime (r) as ( Select 1 Union All Select r + 1 From TblTime Where r < 29

Insert Into WorkTime

)Select EmpID, t.* From Employees Cross Apply

,Select Convert (Date, DATEADD (Day,r, '2020-01-31' )) as ClDate

,Convert (Time, DATEADD ( MINUTE , ABS ( CHECKSUM ( NewId ())) % 14 , '07:30:00' ), 108 ) EnterTime

Convert (Time, DATEADD ( MINUTE , ABS ( CHECKSUM ( NewId ())) % 14 , '16:45:00' ), 108 ) ExitTime

From TblTime) as t

GO

برای اینکه بتوانیم با استفاده از Polybase به داده‌های موجود در جداول دیتابیس SourceDB دسترسی پیدا کنیم به یک نام کاربری و رمز عبور نیاز داریم، که با استفاده از دستورات زیر آن‌ها را ایجاد می‌نماییم.

Use master

GO

'Create Login PolybaseLogin With Password = 'QwaL@9854E!!*B

Use SourceDB

GO

Create User PolybaseUser For Login PolybaseLogin

Grant Select On Employees To PolybaseUser

Grant Select On WorkTime To PolybaseUser

بعد از انجام مراحل ذکر شده، با استفاده از SSMS به آن Instance از SQL Server که Polybase بر روی آن نصب شده است وصل می‌شویم و یک دیتابیس با نام SQLServerDB ایجاد می‌نماییم:

Use master

GO

If DB_ID ( N 'SQLServerDB' ) Is Not Null

Begin

Alter Database SQLServerDB Set Single_User With Rollback Immediate

Drop Database SQLServerDB

End

GO

Create Database SQLServerDB

On Primary

(Name = SQLServerDB, FileName = N 'D:\Databases\Data\SQLServerDB.mdf' , Size = 128 mb, FileGrowth ,( 32 mb, Maxsize = Unlimited=

FileGroup SQLServerDBFGData

(Name = SQLServerDBFData_1, FileName = N 'D:\Databases\Data\SQLServerDBFData_1.ndf' , Size = 256 mb, FileGrowth = 64 mb, Maxsize = Unlimited)

Log On

,Name = SQLServerDBLog, FileName = N 'D:\Databases\Data\SQLServerDB.ldf' , Size = 128 mb) FileGrowth = 32 mb, Maxsize = Unlimited)

GO

Alter Database SQLServerDB Modify FileGroup SQLServerDBFGData Default

GO

جهت اتصال به یک External Data Source به احراز هویت یا Authentication نیاز داریم.

Polybase تنها مفهومی به نام Basic Authentication را پشتیبانی می کند و این موضوع بدین معنی است که جهت وصل شدن به یک External Data Source باید یک Identity (or user) و همچنین یک Secret (or Password or key) را در SQL Server ذخیره نماییم، به این Object اصطلاحا Database Scoped Credential گفته می‌شود که توسط SQL Server Master Key رمز می‌گردد.

دیتابیس هر زمانی که نیاز به دسترسی، به External Data داشته باشد از Credential استفاده می‌نماید.

دستورات زیر Master Key و Database Scoped Credential را ایجاد می‌نماید:

Use SQLServerDB

GO

'&CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pk$124*!88

GO

CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials

;'WITH IDENTITY = 'PolybaseLogin' , SECRET = 'QwaL@9854E!!*B

جهت ایجاد یک یا چند External Table ابتدا باید یک بار External Data Source را ایجاد نماییم.

دستور زیر External Data Source را ایجاد می‌نماید:

CREATE EXTERNAL DATA SOURCE SQLServerDataSource

)WITH

,'LOCATION = 'sqlserver://192.168.1.3:49904

CREDENTIAL = SQLServerCredentials

;(

در قسمت Location نوع و همچنین آدرس و شماره Port (SQL Server Port) سروری را که به عنوان منبع داده می‌خواهیم به آن وصل شویم، مشخص می‌نماییم.

اکنون می‌توانیم External Table‌ها را ایجاد نماییم برای این کار از دستوارت زیر استفاده می‌شود:

CREATE EXTERNAL TABLE ExternalEmployess

)

[EmpID] INT NOT NULL , [EmpFName] NVARCHAR ( 20 ) COLLATE SQL_Latin1_General_CP1_CI_AS ,NOT NULL 

,[EmpLName] NVARCHAR ( 30 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 

,[EmpAddress] NVARCHAR ( 50 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 

[Description] NVARCHAR ( 100 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

(

)WITH

,'LOCATION = 'SourceDB.dbo.Employees

DATA_SOURCE= SQLServerDataSource

;(

CREATE EXTERNAL TABLE ExternalWorkTime

)

,[EmpID] INT NOT NULL 

,[ClDate] DATE NOT NULL 

,[EnterTime] TIME ( 7 ) NOT NULL 

[ExitTime] TIME ( 7 ) NOT NULL

(

)WITH

,'LOCATION = 'SourceDB.dbo.WorkTime

DATA_SOURCE= SQLServerDataSource

;(

در قسمت With علاوه بر استفاده از Data Source تعریف شده در مرحله قبل، دیتابیس، اسکیما و همچنین جدولی که محل ذخیره سازی داده‌ها در سرور Source می‌باشند مشخص گردیده‌اند.

به صورت Optional می‌توانیم بر روی ستون‌های یک External Table جهت بهبود و افزایش سرعت اجرای کوئری‌ها Statistics ایجاد نماییم، ایجاد Statistics بویژه زمانی که در کوئری از Join یا Filter یا Aggregate استفاده شده است می‌تواند مفید باشد.

دستورات زیر دو Stats را بر روی External Table‌ها ایجاد می‌نمایند.

CREATE STATISTICS EmpIDStats ON ExternalEmployess (EmpID, EmpFName, EmpLName)

;WITH FULLSCAN

CREATE STATISTICS EmpIDCLDateStats ON ExternalWorkTime (EmpID, ExitTime)

;WITH FULLSCAN

کوئری زیر ماکزیمم زمان خروج، تعداد مشخصی از کارمندان را در یک بازه زمانی مشخص استخراج می‌نماید:

Select e.EmpID, e.EmpFName, e.EmpLName, Max (w.ExitTime) From ExternalEmployess e

Inner Join ExternalWorkTime w

On e.EmpID = w.EmpID

Where w.CLDate BetWeen '2020-02-20' And '2020-02-27' And e.EmpID Between 1000 And 10000

Group By e.EmpID, e.EmpFName, e.EmpLName

به Plan کوئری توجه نمایید:

همان طور که در Actual Execution Plan فوق مشاهده می‌شود Polybase از تکنیک Remote Query استفاده می‌نماید و به دلیل وجود Statisticsها Estimate Number of Rows و Actual Number of Rows بسیار به هم نزدیک هستند که یک حالت ایده آل است.

بررسی چند نکته

1- تنها دستورات DDL زیر به ازای External Tables مجاز می‌باشند:

• Create Table And Drop Table
• Create Statistics And Drop Statistics
• Create View And Drop View

2- عملیات‌های زیر بر روی External Tables پشتیبانی نمی‌شوند:

• ایجاد Default Constraint بر روی External Table
• عملیات‌های DML

برای بروز رسانی Statistics های ایجاد شده بر روی External Table باید آن‌ها را Recreate نماییم.

برخی تفاوت‌های میان Linked Server و Polybase

• Polybase مناسب کوئری‌های آنالیزی است که تعداد زیادی ردیف را پردازش می‌کنند، اما Linked Server مناسب کوئری‌های OLTP است که یک یا چند رکورد را بر می‌گردانند.
• Polybase برای همه Data Sourceها فقط عملیات‌های Read-Only را پشتیبانی می‌کند و برای Hadoop Data Source عملیات Insert را نیز پشتیبانی می‌نماید. Linked Server همه عملیات‌های نوشتن و خواندن را پشتیبانی می‌نماید.
• Polybase به Config مجزا برای Availability Group نیاز ندارد، اما Linked Server نیاز دارد.
• Polybase از ODBC Drivers استفاده می‌کند اما Linked Server از OLEDB Provider استفاده می‌نماید.
• Linked Server ها Instance Scoped هستند اما Polybase به صورت Database Scoped می باشد.
• Polybase از Integrated Security پشتیبانی نمی‌کند.
• Polybase قابلیت Scale out شدن را دارا می‌باشد.

Polybase در SQL Server 2019 می تواند Data را از Data Source های متنوعی بخواند این Data Source ها عباتند از: SQL Server، Oracle، TeraData، MongoDB و انواع Data Sourceهای سازگار با ODBC. Polybase
یک راه حل عالی برای کوئری‌های Real-Time است و قطعا میزان نیاز به عملیات ETL را کاهش می‌دهد اما این موضوع به معنی پایان یافتن فرآیند ETL حداقل در زمان جاری نیست، همچنین با توجه به تفاوت‌های میان Linked Server و Polybase باید به این موضوع توجه داشت که هر کدام از این ابزارها، کاربرد خاص خودشان را دارند.

منبع:nikamooz