أنشئ جداول خارجية أصلية واستخدمها باستخدام تجمعات SQL في Azure Synapse Analytics

في هذا القسم، ستتعلم كيفية إنشاء واستخدام جداول خارجية أصلية في تجمعات Synapse SQL. تتميز الجداول الخارجية الأصلية بأداء أفضل عند مقارنتها بالجداول الخارجية بـ TYPE=HADOOP في تعريف مصدر البيانات الخارجي. ذلك لأن الجداول الخارجية الأصلية تستخدم تعليمة برمجية أصلية للوصول إلى البيانات الخارجية.

تعد الجداول الخارجية مفيدة عندما تريد التحكم في الوصول إلى البيانات الخارجية في مجمع Synapse SQL. تعد الجداول الخارجية مفيدة أيضًا إذا كنت تريد استخدام أدوات، مثل Power BI، جنبًا إلى جنب مع مجمع Synapse SQL. يمكن للجداول الخارجية الوصول إلى نوعين من التخزين:

• التخزين العام حيث يستطيع المستخدمون الوصول إلى ملفات التخزين العامة.
• التخزين المحمي حيث يصل المستخدمون إلى ملفات التخزين باستخدام بيانات اعتماد SAS أو هوية Microsoft Entra أو الهوية المدارة لمساحة عمل Synapse.

إشعار

في تجمعات SQL المخصصة يمكنك فقط استخدام الجداول الخارجية الأصلية مع ملف من نوع Parquet، وهذه الميزة موجودة في المعاينة العامة. إذا كنت ترغب في استخدام وظائف قارئ Parquet المتاحة بشكل عام في تجمعات SQL المخصصة، أو تحتاج إلى الوصول إلى ملفات CSV أو ORC، استخدم جداول Hadoop الخارجية. تتوفر الجداول الخارجية الأصلية بشكل عام في تجمعات SQL بدون خادم. تعرف على المزيد عن الاختلافات بين جداول Hadoop الخارجية الأصلية في استخدام الجداول الخارجية مع Synapse SQL.

يسرد الجدول التالي تنسيقات البيانات المعتمدة:

تنسيق البيانات (جداول خارجية أصلية)	تجمع SQL بلا خادم	تجمع SQL المخصص
Parquet	نعم (GA)	نعم (معاينة عامة)
CSV	‏‏نعم‬	لا (بدلا من ذلك، استخدم جداول Hadoop الخارجية)
delta	‏‏نعم‬	لا
Spark	‏‏نعم‬	لا
Dataverse	‏‏نعم‬	لا
تنسيقات بيانات Azure Cosmos DB (JSON وBSON وما إلى ذلك)	لا (بدلا من ذلك، إنشاء طرق عرض)	لا

المتطلبات الأساسية

تتمثل الخطوة الأولى في إنشاء قاعدة بيانات حيث سيتم إنشاء الجداول. قبل إنشاء بيانات اعتماد محددة النطاق لقاعدة البيانات، يجب أن تحتوي قاعدة البيانات على مفتاح رئيسي لحماية بيانات الاعتماد. لمزيد من المعلومات حول هذا، راجع إنشاء مفتاح رئيسي (Transact-SQL). ثم قم بإنشاء الكائنات التالية المستخدمة في هذا النموذج:

• قاعدة بيانات نطاق بيانات الاعتماد sqlondemandالتي تتيح الوصول إلى حساب تخزين Azurehttps://sqlondemandstorage.blob.core.windows.net المحمي بواسطة SAS.

  CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
  WITH IDENTITY='SHARED ACCESS SIGNATURE',  
  SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
  

• مصدر البيانات الخارجي sqlondemanddemoيشير إلى حساب تخزين تجريبي محمي بمفتاح SAS، بينما مصدر البيانات الخارجي nyctlc يشير إلى حساب تخزين Azure المتاح للجمهور في الموقع https://azureopendatastorage.blob.core.windows.net/nyctlc/.

  CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
      LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
      CREDENTIAL = sqlondemand
  );
  GO
  CREATE EXTERNAL DATA SOURCE nyctlc
  WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/')
  GO
  CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
  WITH ( location = 'https://sqlondemandstorage.blob.core.windows.net/delta-lake/' );
  

• تنسيقات الملفات QuotedCSVWithHeaderFormat وParquetFormat التي تصف أنواع ملفات CSV وملفات Parquet.

  CREATE EXTERNAL FILE FORMAT QuotedCsvWithHeaderFormat
  WITH (  
      FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2   )
  );
  GO
  CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
  GO
  CREATE EXTERNAL FILE FORMAT DeltaLakeFormat WITH (  FORMAT_TYPE = DELTA );
  GO
  

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

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

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

ينشئ الاستعلام التالي جدولًا خارجيًا يقرأ ملف Population.csv من حساب تخزين Azure التجريبي SynapseSQL الذي تتم الإشارة إليه باستخدام sqlondemanddemoمصدر بيانات ومحمي ببيانات اعتماد محددة نطاق قاعدة البيانات تسمى sqlondemand.

يتم إنشاء مصدر البيانات وبيانات الاعتماد المحددة في نطاق قاعدة البيانات في البرنامج النصي للإعداد.

إشعار

قم بتغيير السطر الأول في الاستعلام، أي [mydbname]، بحيث تستخدم قاعدة البيانات التي قمت بإنشائها.

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

تتوفر جداول CSV الأصلية حاليًا فقط في تجمعات SQL التي لا تحتوي على خادم.

جدول خارجي على مجموعة من الملفات

يمكنك إنشاء جداول خارجية تقرأ البيانات من مجموعة ملفات موضوعة على تخزين Azure:

CREATE EXTERNAL TABLE Taxi (
     vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
     pickup_datetime DATETIME2, 
     dropoff_datetime DATETIME2,
     passenger_count INT,
     trip_distance FLOAT,
     fare_amount FLOAT,
     tip_amount FLOAT,
     tolls_amount FLOAT,
     total_amount FLOAT
) WITH (
         LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
         DATA_SOURCE = nyctlc,
         FILE_FORMAT = ParquetFormat
);

يمكنك تحديد النمط الذي يجب أن تفي به الملفات حتى تتم الإشارة إليها بواسطة الجدول الخارجي. النمط مطلوب فقط لطاولات الParquet وCSV. إذا كنت تستخدم تنسيق Delta Lake، فأنت بحاجة إلى تحديد مجلد جذر فقط، وسيقوم الجدول الخارجي تلقائيًا بالعثور على النمط.

إشعار

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

جدول خارجي على الملفات القابلة للإلحاق

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

CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat,
    TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
);

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

هذا الخيار متاح فقط في الجداول الخارجية التي تم إنشاؤها بتنسيق ملف CSV.

إشعار

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

طاولة دلتا ليك الخارجية

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

مثال على تعريف الجدول الذي تم إنشاؤه في مجلد Delta Lake هو:

CREATE EXTERNAL TABLE Covid (
     date_rep date,
     cases int,
     geo_id varchar(6)
) WITH (
        LOCATION = 'covid', --> the root folder containing the Delta Lake files
        data_source = DeltaLakeStorage,
        FILE_FORMAT = DeltaLakeFormat
);

لا يمكن إنشاء جداول خارجية على مجلد مقسم. راجع المشكلات المعروفة في صفحة المساعدة الذاتية لتجمع SQL بلا خادم Synapse.

جداول دلتا على المجلدات المقسمة

لا تدعم الجداول الخارجية في تجمعات SQL بلا خادم التقسيم بتنسيق Delta Lake. استخدم طرق عرض دلتا المقسمة بدلًا من الجداول إذا قمت بتقسيم مجموعات بيانات Delta Lake.

هام

لا تقم بإنشاء جداول خارجية على مجلدات Delta Lake المقسمة حتى إذا رأيت أنها قد تعمل في بعض الحالات. قد يؤدي استخدام ميزات غير مدعومة مثل الجداول الخارجية على مجلدات دلتا المقسمة إلى حدوث مشكلات أو عدم استقرار التجمع بلا خادم. لن يتمكن دعم Azure من حل أي مشكلة إذا كان يستخدم جداول على مجلدات مقسمة. سيطلب منك الانتقال إلى طرق عرض دلتا المقسمة وإعادة كتابة التعليمات البرمجية الخاصة بك لاستخدام الميزة المدعومة فقط قبل المتابعة مع حل المشكلة.

استخدام جدول خارجي

يمكنك استخدام الجداول الخارجية في استعلاماتك بنفس الطريقة التي تستخدمها في استعلامات SQL Server.

يوضح الاستعلام التالي ذلك باستخدام الجدول الخارجي population الذي أنشأناه في القسم السابق. وهو يُرجع أسماء البلدان/المناطق مع عدد سكانها في عام 2019 بترتيب تنازلي.

إشعار

قم بتغيير السطر الأول في الاستعلام، أي [mydbname]، بحيث تستخدم قاعدة البيانات التي قمت بإنشائها.

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationExternalTable
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

قد يختلف أداء هذا الاستعلام تبعًا للمنطقة. قد لا يتم وضع مساحة العمل الخاصة بك في نفس المنطقة مثل حسابات تخزين Azure المستخدمة في نماذج العينات هذه. لأحمال عمل الإنتاج، ضع مساحة عمل Synapse وتخزين Azure في نفس المنطقة.

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

للحصول على معلومات حول كيفية تخزين نتائج استعلام في وحدة التخزين، راجع مقالة تخزين نتائج الاستعلام في وحدة التخزين.