مشاركة عبر

البرنامج التعليمي: إنشاء مستودع البيانات المنطقية مع تجمع SQL بلا خادم

  • مقالة

في هذا البرنامج التعليمي، سوف تتعلم كيفية إنشاء مستودع البيانات المنطقية (LDW) على رأس تخزين Azure وAzure Cosmos DB.

LDW عبارة عن طبقة علائقية تم إنشاؤها فوق مصادر بيانات Azure مثل تخزين Azure Data Lake (ADLS) أو التخزين التحليلي Azure Cosmos DB أو تخزين مخزن Azure للكائنات الثنائية كبيرة الحجم.

إنشاء قاعدة بيانات LDW

تحتاج إلى إنشاء قاعدة بيانات مخصصة حيث ستخزن الجداول الخارجية وطرق العرض التي تشير إلى مصادر البيانات الخارجية.

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

سيوفر هذا الترتيب الأداء الأمثل أثناء قراءة Parquet وAzure Cosmos DB. إذا كنت لا تريد تحديد ترتيب قاعدة البيانات، فتأكد من تحديد هذا الترتيب في تعريف العمود.

تكوين مصادر البيانات وتنسيقاتها

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

إنشاء مصدر بيانات

تمثل مصادر البيانات معلومات سلسلة الاتصال التي تصف مكان وضع البيانات وكيفية المصادقة على مصدر البيانات.

مثال واحد من تعريف مصدر البيانات الذي يشير إلى مجموعة البيانات المفتوحة ECDC COVID 19 Azure العامة هو موضح في المثال التالي:

CREATE EXTERNAL DATA SOURCE ecdc_cases WITH (
    LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/'
);

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

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

وكشرط أساسي، ستحتاج إلى إنشاء مفتاح رئيسي في قاعدة البيانات:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Setup you password - you need to create master key only once';

في مصدر البيانات الخارجية التالي، يجب أن يستخدم تجمع Synapse SQL هوية مُدارة لمساحة العمل للوصول إلى البيانات الموجودة في التخزين.

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
WITH IDENTITY = 'Managed Identity';
GO
CREATE EXTERNAL DATA SOURCE ecdc_cases WITH (
    LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/',
    CREDENTIAL = WorkspaceIdentity
);

للوصول إلى التخزين التحليلي ل Azure Cosmos DB، تحتاج إلى تعريف بيانات اعتماد تحتوي على مفتاح حساب Azure Cosmos DB للقراءة فقط.

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';

يمكن لأي مستخدم له دور Synapse مسؤول istrator استخدام بيانات الاعتماد هذه للوصول إلى تخزين Azure Data Lake أو التخزين التحليلي ل Azure Cosmos DB. إذا كان لديك مستخدمون ذوو مميزات منخفضة وليس لهم دور مسؤول Synapse، فقد تحتاج إلى منحهم إذنًا صريحًا للإشارة إلى بيانات الاعتماد في نطاق قاعدة البيانات هذه:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::WorkspaceIdentity TO <user>
GO
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::MyCosmosDbAccountCredential TO <user>
GO

يمكنك العثور على مزيد من التفاصيل في صفحة أذونات اعتماد نطاق قاعدة البيانات.

تعريف تنسيقات الملفات الخارجية

تحدد تنسيقات الملفات الخارجية بنية الملفات المخزنة على مصدر بيانات خارجي. يمكنك تحديد تنسيقات الملفات الخارجية Parquet وCSV:

CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT CsvFormat WITH (  FORMAT_TYPE = DELIMITEDTEXT );

لمزيد من المعلومات، راجع استخدام الجداول الخارجية مع Synapse SQL وإنشاء تنسيق ملف خارجي لوصف تنسيق ملفات CSV أو Parquet.

استكشف بياناتك

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

select top 10  *
from openrowset(bulk 'latest/ecdc_cases.parquet',
                data_source = 'ecdc_cases',
                format='parquet') as a

OPENROWSETسوف تعطيك الدالة معلومات حول الأعمدة الموجودة في الملفات الخارجية أو الحاويات وتمكنك من تعريف مخطط للجداول وطرق العرض الخارجية.

إنشاء جداول خارجية على تخزين Azure

بمجرد اكتشاف المخطط، يمكنك إنشاء جداول وطرق عرض خارجية أعلى مصادر البيانات الخارجية. التدريب العملي الجيد هو تنظيم الجداول وطرق العرض في مخططات قواعد البيانات. في الاستعلام التالي، يمكنك إنشاء مخطط حيث ستضع جميع الكائنات التي تصل إلى مجموعة بيانات ECDC COVID في Azure data Lake storage:

create schema ecdc_adls;

مخططات قاعدة البيانات مفيدة لتجميع الكائنات وتعريف الأذونات لكل مخطط.

بمجرد تعريف المخططات، يمكنك إنشاء جداول خارجية تشير إلى الملفات. يشير الجدول الخارجي التالي إلى ملف باركيه COVID ECDC الموضوع في تخزين Azure:

create external table ecdc_adls.cases (
    date_rep                   date,
    day                        smallint,
    month                      smallint,
    year                       smallint,
    cases                      smallint,
    deaths                     smallint,
    countries_and_territories  varchar(256),
    geo_id                     varchar(60),
    country_territory_code     varchar(16),
    pop_data_2018              int,
    continent_exp              varchar(32),
    load_date                  datetime2(7),
    iso_country                varchar(16)
) with (
    data_source= ecdc_cases,
    location = 'latest/ecdc_cases.parquet',
    file_format = ParquetFormat
);

تأكد من استخدام أصغر أنواع ممكنة لأعمدة السلاسل والأرقام لتحسين أداء الاستعلامات.

إنشاء طرق عرض على Azure Cosmos DB

كبديل للجداول الخارجية، يمكنك إنشاء طرق عرض أعلى البيانات الخارجية.

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

create schema ecdc_cosmosdb;

الآن يمكنك إنشاء طريقة عرض في المخطط الذي يشير إلى حاوية Azure Cosmos DB:

CREATE OR ALTER VIEW ecdc_cosmosdb.Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
      OBJECT = 'Ecdc',
      CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) WITH
     ( date_rep varchar(20), 
       cases bigint,
       geo_id varchar(6) 
     ) as rows

لتحسين الأداء، يجب استخدام أصغر الأنواع الممكنة في WITH تعريف المخطط.

إشعار

يجب وضع مفتاح حساب Azure Cosmos DB في بيانات اعتماد منفصلة ثم الرجوع إلى بيانات الاعتماد هذه من OPENROWSET الدالة. لا تحتفظ بمفتاح حسابك في تعريف طريقة العرض.

الوصول والأذونات

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

CREATE USER [jovan@contoso.com] FROM EXTERNAL PROVIDER;
GO

بدلا من أساسيات Microsoft Entra، يمكنك إنشاء أساسيات SQL التي تصادق باسم تسجيل الدخول وكلمة المرور.

CREATE LOGIN [jovan] WITH PASSWORD = 'My Very strong Password ! 1234';
CREATE USER [jovan] FROM LOGIN [jovan];

في كلتا الحالتين، يمكنك تعيين أذونات للمستخدمين.

DENY ADMINISTER DATABASE BULK OPERATIONS TO [jovan@contoso.com]
GO
GRANT SELECT ON SCHEMA::ecdc_adls TO [jovan@contoso.com]
GO
GRANT SELECT ON OBJECT::ecdc_cosmosDB.cases TO [jovan@contoso.com]
GO
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::MyCosmosDbAccountCredential TO [jovan@contoso.com]
GO

تعتمد قواعد الأمان على سياسات الأمان الخاصة بك. بعض المبادئ التوجيهية العامة هي:

  • يجب رفض ADMINISTER DATABASE BULK OPERATIONS الإذن للمستخدمين الجدد لأنهم يجب أن يكونوا قادرين على قراءة البيانات فقط باستخدام الجداول الخارجية وطرق العرض التي قمت بإعدادها.
  • يجب عليك تقديم SELECT الإذن فقط للجداول التي يجب أن يكون بعض المستخدمين قادرين على استخدامها.
  • إذا كنت توفر الوصول إلى البيانات باستخدام طرق العرض، يجب منح REFERENCES الإذن إلى بيانات الاعتماد التي سيتم استخدامها للوصول إلى مصدر البيانات الخارجي.

هذا المستخدم لديه الحد الأدنى من الأذونات اللازمة للاستعلام عن البيانات الخارجية. إذا كنت ترغب في إنشاء مستخدم طاقة يمكنه إعداد الأذونات والجداول الخارجية وطرق العرض، يمكنك منح CONTROL الإذن للمستخدم:

GRANT CONTROL TO [jovan@contoso.com]

أمان يستند إلى الدور

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

CREATE ROLE CovidAnalyst;

ALTER ROLE CovidAnalyst ADD MEMBER [jovan@contoso.com];
ALTER ROLE CovidAnalyst ADD MEMBER [milan@contoso.com];
ALTER ROLE CovidAnalyst ADD MEMBER [petar@contoso.com];

يمكنك تعيين الأذونات لكافة المستخدمين الذين ينتمون إلى المجموعة:

GRANT SELECT ON SCHEMA::ecdc_cosmosdb TO [CovidAnalyst];
GO
DENY SELECT ON SCHEMA::ecdc_adls TO [CovidAnalyst];
GO
DENY ADMINISTER DATABASE BULK OPERATIONS TO [CovidAnalyst];

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

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