الوصول إلى التخزين الخارجي باستخدام تجمع SQL بدون خادم في Azure Synapse Analytics

مقالة
10/18/2023

توضح هذه المقالة كيفية قراءة المستخدمين للبيانات من الملفات المخزنة على تخزين Azure في تجمع SQL بدون ملقم. لدى المستخدمين الخيارات التالية للوصول إلى التخزين:

الدالة OPENROWSET التي تمكن الاستعلامات المخصصة عبر الملفات في Azure Storage.
جدول خارجي عبارة عن بنية بيانات معرفة مسبقا مبنية على مجموعة من الملفات الخارجية.

يمكن للمستخدم استخدام أساليب مصادقة مختلفة مثل مصادقة المرور من Microsoft Entra (الافتراضية لأساسيات Microsoft Entra) ومصادقة SAS (افتراضية لكيانات SQL).

ملفات الاستعلام باستخدام OPENROWSET

OPENROWSET تمكن المستخدمين من الاستعلام عن الملفات الخارجية على تخزين Azure إذا كان لديهم حق الوصول إلى التخزين. يجب على المستخدم المتصل بتجمع SQL بدون خادم استخدام الاستعلام التالي لقراءة محتوى الملفات على تخزين Azure:

SELECT * FROM
 OPENROWSET(BULK 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/*.parquet', format= 'parquet') as rows

يمكن للمستخدم الوصول إلى التخزين باستخدام قواعد الوصول التالية:

مستخدم Microsoft Entra - OPENROWSET سيستخدم هوية Microsoft Entra للمتصل للوصول إلى Azure Storage أو الوصول إلى التخزين مع وصول مجهول.
مستخدم SQL - OPENROWSET سوف يصل إلى التخزين مع وصول مجهول أو يمكن انتحاله باستخدام رمز SAS المميز أو الهوية المدارة لمساحة العمل.

الانتحال
الوصول المباشر

يمكن أيضًا استخدام مبادئ SQL OPENROWSET للاستعلام مباشرة الملفات المحمية مع رموز SAS المميزة أو "الهوية المدارة" لمساحة العمل. إذا قام مستخدم SQL بتنفيذ هذه الدالة، يجب على مستخدم الطاقة الذي لديه ALTER ANY CREDENTIAL إذن إنشاء بيانات اعتماد ذات نطاق خادم تطابق عنوان URL في الدالة (باستخدام اسم التخزين والحاوية) ومنح REFERENCES إذنا لبيانات الاعتماد هذه لمتصل الدالة OPENROWSET:

EXECUTE AS somepoweruser

CREATE CREDENTIAL [https://<storage_account>.dfs.core.windows.net/<container>]
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sas token';

GRANT REFERENCES ON CREDENTIAL::[https://<storage_account>.dfs.core.windows.net/<container>] TO sqluser

إذا لم يكن هناك بيانات اعتماد على مستوى الخادم تطابق عنوان URL، أو لم يكن لدى المستخدم SQL إذن مراجع لبيانات الاعتماد هذه، فسيتم إرجاع الخطأ. لا يمكن لكيانات SQL انتحال شخصية باستخدام بعض هوية Microsoft Entra.

إشعار

تم تصميم هذا الإصدار من OPENROWSET لاستكشاف البيانات بسرعة وسهولة باستخدام المصادقة الافتراضية. للاستفادة من انتحال الهوية أو الهوية المُدارة، استخدم OPENROWSET مع DATA_SOURCE الموضح في القسم التالي.

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

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

SELECT * FROM
 OPENROWSET(BULK 'file/path/*.parquet',
 DATA_SOURCE = MyAzureInvoices,
 FORMAT= 'parquet') as rows

يجب أن يكون المستخدم الذي ينفذ هذا الاستعلام قادرًا على الوصول إلى الملفات. يجب انتحال هوية المستخدمين باستخدام رمز SAS المميز أو الهوية المدارة لمساحة العمل إذا لم يتمكنوا من الوصول مباشرة إلى الملفات باستخدام هوية Microsoft Entra أو الوصول المجهول.

الانتحال
الوصول المباشر

DATABASE SCOPED CREDENTIAL يحدد كيفية الوصول إلى الملفات على مصدر البيانات المشار إليه (SAS حاليا والهوية المدارة). سيحتاج مستخدم الطاقة الذي CONTROL DATABASE له إذن إلى إنشاء DATABASE SCOPED CREDENTIAL الذي سيتم استخدامه للوصول إلى التخزين ويحدد EXTERNAL DATA SOURCE عنوان URL لمصدر البيانات وبيانات الاعتماد التي يجب استخدامها:

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&amp;sp=rwac&amp;se=2017-02-01T00:55:34Z&amp;st=201********' ;

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/' ,
 CREDENTIAL = AccessAzureInvoices) ;

يجب أن يكون لدى المتصل أحد الأذونات التالية لتنفيذ الدالة OPENROWSET:

أحد الأذونات لتنفيذ OPENROWSET:
- ADMINISTER BULK OPERATIONS تمكين تسجيل الدخول لتنفيذ الدالة OPENROWSET.
- ADMINISTER DATABASE BULK OPERATIONS تمكين المستخدم المحدد في نطاق قاعدة البيانات لتنفيذ الدالة OPENROWSET.
REFERENCES DATABASE SCOPED CREDENTIAL إلى بيانات الاعتماد المشار إليها في EXTERNAL DATA SOURCE.

يمكن للمستخدم إنشاء مصدر بيانات خارجي بدون بيانات اعتماد تشير إلى تخزين الوصول العام أو استخدام مصادقة المرور من Microsoft Entra:

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>') ;

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

يمكن للمستخدم الذي لديه أذونات قراءة الجدول الوصول إلى الملفات الخارجية باستخدام جدول خارجي تم إنشاؤه أعلى مجموعة من مجلدات وملفات Azure Storage.

يمكن للمستخدم الذي لديه أذونات لإنشاء جدول خارجي (على سبيل المثال CREATE TABLE وALTER ANY CREDENTIAL أو REFERENCES DATABASE SCOPED CREDENTIAL) استخدام البرنامج النصي التالي لإنشاء جدول أعلى مصدر بيانات Azure Storage:

CREATE EXTERNAL TABLE [dbo].[DimProductexternal]
( ProductKey int, ProductLabel nvarchar, ProductName nvarchar )
WITH
(
LOCATION='/DimProduct/year=*/month=*' ,
DATA_SOURCE = AzureDataLakeStore ,
FILE_FORMAT = TextFileFormat
) ;

يجب أن يكون المستخدم الذي يقرأ البيانات من هذا الجدول قادرًا على الوصول إلى الملفات. يجب انتحال هوية المستخدمين باستخدام رمز SAS المميز أو الهوية المدارة لمساحة العمل إذا لم يتمكنوا من الوصول مباشرة إلى الملفات باستخدام هوية Microsoft Entra أو الوصول المجهول.

الانتحال
الوصول المباشر

DATABASE SCOPED CREDENTIAL تحديد كيفية الوصول إلى الملفات على مصدر البيانات المشار إليه. سيحتاج المستخدم الذي لديه إذن CONTROL DATABASE إلى إنشاء قاعدة بيانات SCOPED CREDENTIAL التي سيتم استخدامها للوصول إلى التخزين، ومصدر البيانات الخارجي الذي يحدد URL لمصدر البيانات، وبيانات الاعتماد التي يجب استخدامها:

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL cred
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=201********' ;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>' ,
 CREDENTIAL = cred
 ) ;

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>') ;

قراءة الملفات الخارجية مع جدول خارجي

يتيح لك الجدول الخارجي قراءة البيانات من الملفات المشار إليها عبر مصدر البيانات باستخدام عبارة SQL SELECT القياسية:

SELECT *
FROM dbo.DimProductsExternal

يجب أن يكون لدى المتصل الأذونات التالية لقراءة البيانات:

SELECT إذن تشغيل جدول خارجي
REFERENCES DATABASE SCOPED CREDENTIAL إذن إذا كان DATA SOURCE لديه CREDENTIAL

الأذونات

يسرد الجدول التالي الأذونات المطلوبة للعمليات المذكورة أعلاه.

الاستعلام	الأذونات المطلوبة
OPENROWSET(مجمع) بدون مصدر بيانات	`ADMINISTER BULK OPERATIONS`، أو`ADMINISTER DATABASE BULK OPERATIONS`، أو تسجيل الدخول إلى SQL يجب أن يحتوي على REFERENCES CREDENTIAL :: <URL> للتخزين المحمي بـ SAS
OPENROWSET(BULK) مع مصدر بيانات بدون بيانات الاعتماد	`ADMINISTER BULK OPERATIONS` أو `ADMINISTER DATABASE BULK OPERATIONS`،
OPENROWSET(BULK) مع مصدر بيانات مع بيانات الاعتماد	`REFERENCES DATABASE SCOPED CREDENTIAL` وواحد من `ADMINISTER BULK OPERATIONS` أو `ADMINISTER DATABASE BULK OPERATIONS`
إنشاء مصدر بيانات خارجي	`ALTER ANY EXTERNAL DATA SOURCE` و`REFERENCES DATABASE SCOPED CREDENTIAL`
إنشاء جدول خارجي	`CREATE TABLE`و `ALTER ANY SCHEMAALTER ANY EXTERNAL FILE FORMAT`و`ALTER ANY EXTERNAL DATA SOURCE`
التحديد من جدول خارجي	`SELECT TABLE` و`REFERENCES DATABASE SCOPED CREDENTIAL`
CETAS	لإنشاء جدول - `CREATE TABLE`و `ALTER ANY SCHEMAALTER ANY DATA SOURCE`و`ALTER ANY EXTERNAL FILE FORMAT`. لقراءة البيانات: `ADMINISTER BULK OPERATIONS` أو أو `REFERENCES CREDENTIAL` `SELECT TABLE` لكل جدول/عرض/دالة في الاستعلام + إذن R/W على التخزين

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

أنت الآن جاهز لمتابعة مقالات "الكيفية" التالية:

مشاركة عبر