المحاكاة الافتراضية البيانات باستخدام “Azure SQL Managed Instance” (معاينة)

ينطبق على: مثيل Azure SQL المُدار

تتيح لك المحاكاة الافتراضية للبيانات باستخدام “Azure SQL Managed Instance” تنفيذ استعلامات SQL (T-SQL) مقابل البيانات من الملفات المخزنة في Azure Data Lake Storage Gen2 أو Azure Blob Storage، ودمجها مع البيانات الارتباطية المخزنة محليًا باستخدام الصلات. وبهذه الطريقة يمكنك الوصول بشفافية إلى البيانات الخارجية مع الاحتفاظ بها في شكلها الأصلي وموقعها - المعروف أيضًا باسم المحاكاة الافتراضية للبيانات.

المحاكاة الافتراضية للبيانات قيد المعاينة حاليًا لـ “Azure SQL Managed Instance”.

نظرة عامة

توفر المحاكاة الافتراضية للبيانات طريقتين للاستعلام عن الملفات الخارجية المخزنة في Azure Data Lake Storage أو Azure Blob Storage، مخصصة لسيناريوهات مختلفة:

• صيغة OPENROWSET - مُحسّنة للاستعلام عن الملفات. تستخدم عادة لاستكشاف محتوى وبنية مجموعة جديدة من الملفات بسرعة.
• الجداول الخارجية - تم تحسينها للاستعلام المتكرر عن الملفات باستخدام صيغة متطابقة كما لو كانت البيانات مخزنة محليًا في قاعدة البيانات. تتطلب الجداول الخارجية عدة خطوات إعداد مقارنة بصيغة OPENROWSET، ولكنها تسمح بمزيد من التحكم في الوصول إلى البيانات. تستخدم الجداول الخارجية عادة لأعباء العمل التحليلية وإعداد التقارير.

يتم دعم تنسيقات ملفات “Parquet ” والنصوص المحددة (CSV) مباشرة. يتم دعم تنسيق ملف JSON بشكل غير مباشر من خلال تحديد تنسيق ملف CSV حيث تقوم الاستعلامات بإرجاع كل مستند كصف منفصل. من الممكن تحليل الصفوف بشكل أكبر باستخدام JSON_VALUE و OPENJSON.

الشروع في العمل

استخدم SQL (T-SQL) لتمكين ميزة المحاكاة الافتراضية للبيانات بشكل صريح قبل استخدامها.

لتمكين إمكانات المحاكاة الافتراضية للبيانات، قم بتشغيل الأمر التالي:

exec sp_configure 'polybase_enabled', 1;
go
reconfigure;
go

قم بتوفير موقع الملف (الملفات) الذي تنوي الاستعلام عنه باستخدام بادئة الموقع المقابلة لنوع المصدر الخارجي ونقطة النهاية أو البروتوكول، مثل الأمثلة التالية:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet

هام

يتم تثبيط استخدام البادئة العامة https:// وسيتم تعطيلها في المستقبل. تأكد من استخدام بادئات خاصة بنقطة النهاية لتجنب الانقطاعات.

إذا كنت جديدًا على المحاكاة الافتراضية للبيانات وتريد اختبار الوظائف بسرعة، فابدأ بالاستعلام عن مجموعات البيانات المتاحة للجمهور المتوفرة في Azure Open Datasets، مثل مجموعة بيانات Bing COVID-19 التي تسمح بالوصول المجهول.

استخدم نقاط النهاية التالية للاستعلام عن مجموعة بيانات Bing COVID-19:

• Parquet: ⁧ abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet ⁩
• CSV: ⁧ abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv ⁩

بمجرد تنفيذ استعلامات مجموعة البيانات العامة بنجاح، فكر في التبديل إلى مجموعات البيانات الخاصة التي تتطلب تكوين حقوق محددة و/أو قواعد جدار الحماية.

للوصول إلى موقع خاص، استخدم “Shared Access Signature (SAS)” مع أذونات الوصول المناسبة وفترة الصلاحية للمصادقة على حساب التخزين. قم بإنشاء بيانات اعتماد ذات نطاق قاعدة بيانات باستخدام مفتاح SAS، بدلًا من توفيره مباشرة في كل استعلام. ثم يتم استخدام بيانات الاعتماد كمعلمة للوصول إلى مصدر البيانات الخارجية.

مصدر البيانات الخارجية

مصادر البيانات الخارجية هي مصادر تجريدية تهدف إلى تسهيل إدارة مواقع الملفات عبر استعلامات متعددة، والإشارة إلى معلمات المصادقة التي يتم تغليفها ضمن بيانات اعتماد نطاق قاعدة البيانات.

عند الوصول إلى موقع عام، أضف موقع الملف عند الاستعلام عن مصدر البيانات الخارجية:

-- Don't forget to enable data virtualization capabilities first, if this is the first time you are running this type of query  

CREATE EXTERNAL DATA SOURCE DemoPublicExternalDataSource
WITH (
	LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
--  LOCATION = 'abs://<container>@<storage_account>.blob.core.windows.net/<path>' 
)

عند الوصول إلى موقع خاص، قم بتضمين مسار الملف وبيانات الاعتماد عند الاستعلام عن مصدر البيانات الخارجية:

--Don't forget to enable data virtualization capabilities first, if this is the first time you are running this type of query  

-- Step0 (optional): Create master key if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Put Some Very Strong Password Here>'
-- GO

--Step1: Create database-scoped credential (requires database master key to exist):
CREATE DATABASE SCOPED CREDENTIAL [DemoCredential]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<your SAS key without leading "?" mark>';
GO

--Step2: Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE DemoPrivateExternalDataSource
WITH (
	LOCATION = 'abs://<container>@<storage_account>.blob.core.windows.net/<path>',
    CREDENTIAL = [DemoCredential] 
)

مصادر بيانات الاستعلام باستخدام OPENROWSET

تتيح صيغة OPENROWSET الاستعلام الفوري المخصص أثناء إنشاء الحد الأدنى من عناصر قاعدة البيانات الضرورية فقط. OPENROWSET يتطلب فقط إنشاء مصدر البيانات الخارجية (وربما بيانات الاعتماد) بدلًا من نهج الجدول الخارجي الذي يتطلب تنسيق ملف خارجي والجدول الخارجي نفسه.

يتم إعداد قيمة المعلمة DATA_SOURCE تلقائيًا إلى المعلمة BULK لتشكيل المسار الكامل إلى الملف.

عند استخدام OPENROWSET توفير تنسيق الملف، كما بالمثال التالي، الذي يستفسر عن ملف واحد:

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'bing_covid-19_data.parquet',
 DATA_SOURCE = 'DemoPublicExternalDataSource',
 FORMAT = 'parquet'
) AS filerows

الاستعلام عن ملفات ومجلدات متعددة

OPENROWSET يسمح الأمر أيضًا بالاستعلام عن ملفات أو مجلدات متعددة باستخدام أحرف البدل في مسار BULK.

يستخدم المثال التالي مجموعة البيانات المفتوحة لسجلات رحلات سيارات الأجرة الصفراء في مدينة نيويورك:

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',--You need to create the data source first
 FORMAT = 'parquet'
) AS filerows

عند الاستعلام عن ملفات أو مجلدات متعددة، يجب أن يكون لجميع الملفات التي يتم الوصول إليها باستخدام مفرد OPENROWSET نفس البنية (مثل نفس عدد الأعمدة وأنواع البيانات). لا يمكن اجتياز المجلدات بشكل متكرر.

استنتاج المخطط

يساعدك الاستنتاج التلقائي للمخطط على كتابة الاستعلامات بسرعة واستكشاف البيانات عندما لا تعرف مخططات الملفات. يعمل استنتاج المخطط فقط مع ملفات تنسيق “parquet”.

على الرغم من أنها مريحة، إلا أن التكلفة هي أن أنواع البيانات المستنتجة قد تكون أكبر من أنواع البيانات الفعلية. يمكن أن يؤدي ذلك إلى ضعف أداء الاستعلام نظرًا لعدم وجود معلومات كافية في الملفات المصدر لضمان استخدام نوع البيانات المناسب. على سبيل المثال، لا تحتوي ملفات “parquet ” على بيانات تعريف حول الحد الأقصى لطول عمود الحرف، لذلك يستنتج المثيل أنه varchar(8000).

استخدم الإجراء sp_describe_first_results_set المخزن للتحقق من أنواع البيانات الناتجة عن الاستعلام، كما في المثال التالي:

EXEC sp_describe_first_result_set N'
 SELECT
 vendor_id, pickup_datetime, passenger_count
 FROM 
 OPENROWSET(
  BULK ''taxi/*/*/*'',
  DATA_SOURCE = ''NYCTaxiDemoDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

بمجرد معرفة أنواع البيانات، يمكنك تحديدها باستخدام WITH العبارة لتحسين الأداء:

SELECT TOP 100
 vendor_id, pickup_datetime, passenger_count
FROM
OPENROWSET(
 BULK 'taxi/*/*/*',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendor_id varchar(4), -- we're using length of 4 instead of the inferred 8000
pickup_datetime datetime2,
passenger_count int
) AS nyc;

نظرًا لأنه لا يمكن تحديد مخطط ملفات CSV تلقائيًا، حدد الأعمدة صراحة باستخدام WITH العبارة :

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'population/population.csv',
 DATA_SOURCE = 'PopulationDemoDataSourceCSV',
 FORMAT = 'CSV')
WITH (
 [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
 [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
 [year] smallint,
 [population] bigint
) AS filerows

دوال بيانات تعريف الملف

عند الاستعلام عن ملفات أو مجلدات متعددة، يمكنك استخدام Filepath و Filename وظائف لقراءة بيانات تعريف الملف والحصول على جزء من المسار أو المسار بالكامل واسم الملف الذي ينشأ منه الصف في مجموعة النتائج:

--Query all files and project file path and file name information for each row:
SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder],
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], *
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT = 'parquet') AS filerows
--List all paths:
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder]
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT = 'parquet') AS filerows

عندما يتم استدعاؤه بدون معلمة، Filepath يتم إرجاع مسار الملف الكامل الذي ينشأ منه الصف. عند DATA_SOURCE استخدامه في OPENROWSET، فإنه يرجع المسار بالنسبة إلى DATA_SOURCE، وإلا فإنه يرجع مسار الملف الكامل.

عندما يتم استدعاؤها باستخدام المعلمة، فإنها تُرجع جزءًا من المسار الذي يطابق حرف البدل في الموضع المحدد في المعلمة. على سبيل المثال، قد تُرجع قيمة المعلمة 1 جزءًا من المسار يطابق حرف البدل الأول.

يمكن أيضًا استخدام الوظيفة Filepath لتصفية الصفوف وتجميعها:

SELECT
 r.filepath() AS filepath
 ,r.filepath(1) AS [year]
 ,r.filepath(2) AS [month]
 ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
DATA_SOURCE = 'NYCTaxiDemoDataSource',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2017')
 AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
 r.filepath()
 ,r.filepath(1)
 ,r.filepath(2)
ORDER BY
 filepath;

إنشاء طريقة عرض أعلى OPENROWSET

يمكنك إنشاء طرق عرض واستخدامها لتضمين استعلامات OPENROWSET بحيث يمكنك بسهولة إعادة استخدام الاستعلام الأساسي:

CREATE VIEW TaxiRides AS
SELECT *
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT = 'parquet'
) AS filerows

من الملائم أيضًا إضافة أعمدة تحتوي على بيانات موقع الملف إلى طريقة عرض باستخدام Filepath الوظيفة للحصول على تصفية أسهل وأكثر كفاءة. يمكن أن يؤدي استخدام طرق العرض إلى تقليل عدد الملفات وكمية البيانات التي يحتاج الاستعلام الموجود أعلى طريقة العرض إلى قراءتها ومعالجتها عند تصفيتها بواسطة أي من هذه الأعمدة:

CREATE VIEW TaxiRides AS
SELECT *
 ,filerows.filepath(1) AS [year]
 ,filerows.filepath(2) AS [month]
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT = 'parquet'
) AS filerows

تمكن طرق العرض أيضًا أدوات إعداد التقارير والتحليل مثل Power BI من استهلاك نتائج OPENROWSET.

الجداول الخارجية

تقوم الجداول الخارجية بتغليف الوصول إلى الملفات مما يجعل تجربة الاستعلام متطابقة تقريبًا مع الاستعلام عن البيانات الارتباطية المحلية المخزنة في جداول المستخدمين. يتطلب إنشاء جدول خارجي وجود مصدر البيانات الخارجية وكائنات تنسيق الملف الخارجي:

--Create external file format
CREATE EXTERNAL FILE FORMAT DemoFileFormat
WITH (
 FORMAT_TYPE=PARQUET
)
GO

--Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2,
 pickup_datetime DATETIME2,
 dropoff_datetime DATETIME2,
 passenger_count INT,
 trip_distance FLOAT,
 fare_amount FLOAT,
 extra FLOAT,
 mta_tax FLOAT,
 tip_amount FLOAT,
 tolls_amount FLOAT,
 improvement_surcharge FLOAT,
 total_amount FLOAT
)
WITH (
 LOCATION = 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = DemoDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

بمجرد إنشاء الجدول الخارجي، يمكنك الاستعلام عنه تمامًا مثل أي جدول آخر:

SELECT TOP 10 *
FROM tbl_TaxiRides

تمامًا مثل OPENROWSET، تسمح الجداول الخارجية بالاستعلام عن ملفات ومجلدات متعددة باستخدام أحرف البدل. لا يتم دعم استنتاج المخطط ودالات filepath/filename مع الجداول الخارجية.

اعتبارات الأداء

لا يوجد حد ثابت من حيث عدد الملفات أو كمية البيانات التي يمكن الاستعلام عنها، ولكن يعتمد أداء الاستعلام على كمية البيانات وتنسيق البيانات وتعقيد الاستعلامات والصلات.

يعد جمع الإحصائيات حول بياناتك الخارجية أحد أهم الأشياء التي يمكنك القيام بها لتحسين الاستعلام. كلما عرف المثيل عن بياناتك، زادت سرعة تنفيذ الاستعلامات. مُحسِّن استعلام محرك SQL هو مُحسِّن يعتمد على التكلفة. يقارن تكلفة خطط الاستعلام المختلفة ثم يختار الخطة ذات أقل تكلفة. في معظم الحالات، يختار الخطة التي ستنفذ أسرع.

الإنشاء التلقائي للإحصاءات

يقوم “Managed Instance” بتحليل استعلامات المستخدمين الواردة بحثًا عن الإحصاءات المفقودة. في حالة فقدان الإحصائيات، يقوم محسن الاستعلامات بإنشاء إحصائيات على أعمدة فردية في دالة تقييم الاستعلام أو حالة الربط؛ لتحسين تقديرات العلاقة الأساسية لخطة الاستعلام. يتم الإنشاء التلقائي للإحصاءات بشكل متزامن بحيث قد تتكبد أداء استعلام متدهورًا قليلًا إذا كانت أعمدتك تفتقر إلى الإحصاءات. يعتمد وقت إنشاء إحصائيات لعمود واحد على حجم الملفات المستهدفة.

إحصائيات دليل OPENROWSET

يمكن إنشاء إحصائيات عمود واحد للمسار OPENROWSET باستخدام sp_create_openrowset_statistics الإجراء المخزن، عن طريق تمرير استعلام التحديد باستخدام عمود واحد كمعلمة:

EXEC sys.sp_create_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
'

بشكل افتراضي، يستخدم المثيل 100% من البيانات المتوفرة في مجموعة البيانات لإنشاء إحصائيات. يمكنك اختياريًا تحديد حجم العينة كنسبة مئوية باستخدام TABLESAMPLE الخيارات. لإنشاء إحصائيات عمود واحد لأعمدة متعددة، قم بتنفيذ الإجراء المخزن لكل عمود من الأعمدة. لا يمكنك إنشاء إحصائيات متعددة الأعمدة OPENROWSET للمسار.

لتحديث الإحصاءات الموجودة، قم بإسقاطها أولًا sp_drop_openrowset_statisticsباستخدام الإجراء المخزن، ثم أعد إنشائها باستخدام sp_create_openrowset_statistics:

EXEC sys.sp_drop_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
'

إحصائيات دليل الجدول الخارجي

يشبه بناء الجملة الخاص بإنشاء الإحصائيات في الجداول الخارجية تلك المستخدمة في جداول المستخدم العادية. لإنشاء إحصائيات على عمود، أدخل اسمًا لكائن الإحصائيات واسم العمود:

CREATE STATISTICS sVendor
ON tbl_TaxiRides (vendor_id)
WITH FULLSCAN, NORECOMPUTE

WITH الخيارات إلزامية، وبالنسبة لحجم العينة، فإن الخيارات المسموح بها هي FULLSCAN و SAMPLE n النسبة المئوية. لإنشاء إحصائيات عمود واحد لأعمدة متعددة، قم بتنفيذ الإجراء المخزن لكل عمود من الأعمدة. الإحصائيات متعددة الأعمدة غير مدعومة.

استكشاف الأخطاء وإصلاحها

عادة ما تحدث مشكلات في تنفيذ الاستعلام بسبب عدم تمكن المثيل المدار من الوصول إلى موقع الملف. قد تبلغ رسائل الخطأ ذات الصلة عن حقوق وصول غير كافية أو موقع أو مسار ملف غير موجود أو ملف قيد الاستخدام بواسطة عملية أخرى أو لا يمكن سرد هذا الدليل. في معظم الحالات، يشير هذا إلى أن الوصول إلى الملفات محظور بواسطة سياسات التحكم في حركة مرور الشبكة أو بسبب نقص حقوق الوصول. هذا ما يجب التحقق منه:

• مسار موقع خاطئ أو مكتوب بشكل خاطئ.
• صلاحية مفتاح SAS: يمكن أن تنتهي صلاحيته أي خارج فترة صلاحيته، ويحتوي على خطأ مطبعي، بدءًا من علامة استفهام.
• تسمح أذونات مفاتيح SAS بما يلي: “Read” على الأقل، و “List” ما إذا كانت أحرف البدل مستخدمة
• حظر حركة المرور الواردة على حساب التخزين. تحقق من Managing virtual network rules for Azure Storage للحصول على مزيد من التفاصيل وتأكد من السماح بالوصول من المثيل المدار VNet.
• تم حظر حركة المرور الصادرة على المثيل المدار باستخدام سياسة نقطة نهاية التخزين. السماح بحركة المرور الصادرة إلى حساب التخزين.

الخطوات التالية

• لمعرفة المزيد حول خيارات بناء الجملة المتوفرة مع OPENROWSET، راجع OPENROWSET T-SQL.
• لمزيد من المعلومات حول إنشاء جدول خارجي في “SQL Managed Instance”، راجع CREATE EXTERNAL TABLE.
• لمعرفة المزيد حول إنشاء تنسيق ملف خارجي، راجع CREATE EXTERNAL FILE FORMAT