استخدام جداول خارجية مع SQL Synapse

يشير جدول خارجي إلى البيانات الموجودة في Hadoop أو كائن ثنائي كبير الحجم للتخزين لـ Azure أو Azure Data Lake Storage. يمكنك استخدام الجداول الخارجية لقراءة البيانات من الملفات أو كتابة البيانات إلى الملفات في Azure Storage.

باستخدام Synapse SQL، يمكنك استخدام جداول خارجية لقراءة البيانات الخارجية باستخدام تجمع SQL مخصص أو تجمع SQL بدون خادم.

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

• الجداول الخارجية Hadoop التي يمكنك استخدامها لقراءة وتصدير البيانات في تنسيقات البيانات المختلفة مثل CSV وParquet وORC. تتوفر الجداول الخارجية Hadoop في تجمعات SQL مخصصة، ولكنها غير متوفرة في تجمعات SQL بدون خادم.
• الجداول الخارجية الأصلية التي يمكنك استخدامها لقراءة وتصدير البيانات بتنسيقات بيانات مختلفة مثل CSV وParquet. تتوفر الجداول الخارجية الأصلية في تجمعات SQL بدون خادم، وهي في معاينة عامة في تجمعات SQL مخصصة. تتوفر كتابة/تصدير البيانات باستخدام CETAS والجداول الخارجية الأصلية فقط في تجمع SQL بلا خادم، ولكن ليس في تجمعات SQL المخصصة.

الاختلافات الرئيسية بين Hadoop والجداول الخارجية الأصلية:

نوع الجدول الخارجي	Hadoop	أصلي
تجمع SQL المخصص	متوفر‬ة	تتوفر جداول Parquet فقط في المعاينة العامة.
تجمع SQL بلا خادم	غير متوفرة	متوفر‬ة
التنسيقات المدعومة	محدد/CSV وParquet وORC وHive RC وRC	تجمع SQL بدون خادم: محدد/CSV، وParquet، وDelta Lake تجمع SQL مخصص: Parquet (معاينة)
إزالة قسم المجلد	لا	يتوفر حذف القسم فقط في الجداول المقسمة التي تم إنشاؤها على تنسيقات Parquet أو CSV التي تتم مزامنتها من تجمعات Apache Spark. يمكنك إنشاء جداول خارجية على مجلدات Parquet المقسمة، ولكن يتعذر الوصول إلى أعمدة التقسيم وتجاهلها، بينما لن يتم تطبيق إزالة القسم. لا تقم بإنشاء جداول خارجية على مجلدات Delta Lake لأنها غير مدعومة. استخدم طرق عرض دلتا المقسمة إذا كنت بحاجة إلى الاستعلام عن بيانات Delta Lake المقسمة.
إزالة الملفات (دفعة التقييم)	لا	نعم في تجمع SQL بدون خادم. بخصوص ضغط السلسلة، تحتاج إلى استخدام Latin1_General_100_BIN2_UTF8الترتيب على الأعمدة VARCHAR لتفعيل الضغط. لمزيد من المعلومات حول الترتيب، راجع أنواع الترتيب المدعومة ل Synapse SQL.
تنسيق مخصص للموقع	لا	نعم، استخدام أحرف البدل مثل /year=*/month=*/day=* لتنسيقات Parquet أو CSV. مسارات المجلدات المخصصة غير متوفرة في Delta Lake. في تجمع SQL بلا خادم، يمكنك أيضا استخدام أحرف /logs/** البدل المتكررة للإشارة إلى ملفات Parquet أو CSV في أي مجلد فرعي أسفل المجلد المشار إليه.
فحص المجلد المتكرر	‏‏نعم‬	نعم. في SQL بدون خادم يجب أن تكون التجمعات محددة /** في نهاية مسار الموقع. في التجمع المخصص، يتم دائما مسح المجلدات ضوئيا بشكل متكرر.
مصادقة التخزين	مفتاح الوصول إلى التخزين (SAK)، تمرير Microsoft Entra، الهوية المدارة، هوية Microsoft Entra للتطبيق المخصص	توقيع الوصول المشترك (SAS) ، تمرير Microsoft Entra ، الهوية المدارة ، التطبيق المخصص هوية Microsoft Entra.
تعيين العمود	الترتيبي - يتم تعيين الأعمدة في تعريف الجدول الخارجي إلى الأعمدة الموجودة في ملفات Parquet الأساسية حسب الموضع.	تجمع بلا خادم: حسب الاسم. يتم تعيين الأعمدة الموجودة في تعريف الجدول الخارجي إلى الأعمدة الموجودة في ملفات Parquet الأساسية حسب مطابقة اسم العمود. تجمع مخصص: مطابقة ترتيبية. يتم تعيين الأعمدة الموجودة في تعريف الجدول الخارجي إلى الأعمدة الموجودة في ملفات Parquet الأساسية حسب الموضع.
CETAS (التصدير/التحويل)	‏‏نعم‬	يعمل CETAS مع الجداول الأصلية كهدف فقط في تجمع SQL بلا خادم. لا يمكنك استخدام تجمعات SQL المخصصة لتصدير البيانات باستخدام الجداول الأصلية.

إشعار

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

جداول خارجية في تجمع SQL مخصص وتجمع SQL بلا خادم

يمكنك استخدام الجداول الخارجية بهدف:

• الاستعلام عن Azure Blob Storage وAzure Data Lake Gen2 بعبارات Transact-SQL.
• تخزين نتائج الاستعلام إلى ملفات في Azure Blob Storage أو Azure Data Lake Storage باستخدام CETAS.
• استيراد البيانات من Azure Blob Storage وAzure Data Lake Storage وتخزينها في تجمع SQL مخصص (جداول Hadoop فقط في تجمع مخصص).

إشعار

عند استخدامها مع عبارة CREATE TABLE AS SELECTالمختارة من جدول خارجي يستورد البيانات إلى جدول داخل تجمع SQL مخصص.

إذا كان أداء جداول Hadoop الخارجية في التجمعات المخصصة لا يفي بأهداف الأداء الخاصة بك، ففكر في تحميل البيانات الخارجية في جداول Datawarehouse باستخدام عبارة COPY.

لتحميل برنامج تعليمي، انظر Use PolyBase لتحميل البيانات من Azure Blob Storage.

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

1. CREATE EXTERNAL DATA SOURCEللإشارة إلى وحدة تخزين Azure خارجية وتحديد بيانات الاعتماد التي يجب استخدامها للوصول إلى التخزين.
2. إنشاء تنسيق ملف خارجيلوصف تنسيق ملفات CSV أو Parquet.
3. إنشاء جدول خارجيأعلى الملفات الموضوعة على مصدر البيانات بنفس تنسيق الملف.

استبعاد قسم المجلدات

الجداول الخارجية الأصلية في تجمعات Synapse قادرة على تجاهل الملفات الموضوعة في المجلدات غير ذات الصلة للاستعلامات. إذا تم تخزين ملفاتك في تسلسل هرمي للمجلد (على سبيل المثال - /year=2020/month=03/day=16) وتم عرض قيم yearو monthو day كأعمدة، فستقرأ الاستعلامات التي تحتوي على عوامل تصفية مثل year=2020 الملفات فقط من المجلدات الفرعية الموضوعة year=2020 داخل المجلد. سيتم تجاهل الملفات والمجلدات الموضوعة في مجلدات أخرى (year=2021 أو year=2022) في هذا الاستعلام. يعرف هذا الاستبعاد باسم إزالة القسم.

يتوفر حذف قسم المجلد في الجداول الخارجية الأصلية التي تتم مزامنتها من تجمعات Synapse Spark. إذا كنت قد قمت بتقسيم مجموعة البيانات وكنت ترغب في الاستفادة من إزالة القسم باستخدام الجداول الخارجية التي تقوم بإنشائها، فاستخدم طرق العرض المقسمة بدلًا من الجداول الخارجية.

إزالة الملفات

تحتوي بعض تنسيقات البيانات مثل Parquet وData على إحصائيات الملفات لكل عمود (على سبيل المثال، الحد الأدنى/الحد الأقصى للقيم لكل عمود). لن تقرأ الاستعلامات التي تقوم بتصفية البيانات الملفات التي لا توجد فيها قيم العمود المطلوبة. سيستكشف الاستعلام أولًا الحد الأدنى/الحد الأقصى للقيم للأعمدة المستخدمة في دالة تقييم الاستعلام للعثور على الملفات التي لا تحتوي على البيانات المطلوبة. سيتم تجاهل هذه الملفات وإزالتها من خطة الاستعلام. تعرف هذه التقنية أيضا باسم تنازلي دالة تقييم التصفية ويمكنها تحسين أداء الاستعلامات الخاصة بك. يتوفر الضغط على عامل التصفية في تجمعات SQL بلا خادم على تنسيقات Parquet وDelta. للاستفادة من دفع عامل التصفية إلى أنواع السلاسل، استخدم نوع VARCHAR مع الترتيب Latin1_General_100_BIN2_UTF8. لمزيد من المعلومات حول الترتيب، راجع أنواع الترتيب المدعومة ل Synapse SQL.

الأمان

يتعين على المستخدم الحصول على إذن SELECTعلى أي جدول خارجي لقراءة البيانات. الوصول إلى الجداول الخارجية التي تحدد تخزين Azure باستخدام بيانات اعتماد قاعدة البيانات المحددة النطاق المعرفة في مصدر البيانات باستخدام القواعد التالية:

• يمكِّن مصدر البيانات بدون بيانات اعتماد الجداول الخارجية من الوصول إلى ملفات متوفرة بشكل عام على تخزين Azure.
• يمكن أن يكون لمصدر البيانات بيانات اعتماد تمكِّن الجداول الخارجية من الوصول فقط إلى الملفات على تخزين Azure باستخدام الرمز المميز SAS أو الهوية المدارة لمساحة العمل - على سبيل المثال، انظر مقالة تطوير التحكم في الوصول إلى تخزين ملفات التخزين.

مثال لإنشاء مصدر بيانات خارجي

Hadoop

ينشئ المثال التالي مصدر بيانات خارجيًّا Hadoop في تجمع SQL مخصص لـ Azure Data Lake Gen2 يشير إلى مجموعة بيانات نيويورك:

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
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'
GO
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

ينشئ المثال التالي مصدر بيانات خارجيًّا لـ Azure Data Lake Gen2 يشير إلى مجموعة بيانات نيويورك المتوفرة للجمهور:

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

أصلي

ينشئ المثال التالي مصدر بيانات خارجيًّا في تجمع SQL بدون خادم أو مخصص لـ Azure Data Lake Gen2 التي يمكن الوصول إليها باستخدام بيانات اعتماد 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'
GO
CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);

إشعار

يحتاج مستخدمو SQL إلى الحصول على أذونات مناسبة على بيانات اعتماد نطاق قاعدة البيانات للوصول إلى مصدر البيانات في Azure Synapse Analytics Serverless SQL Pool. الوصول إلى التخزين الخارجي باستخدام تجمع SQL بدون خادم في Azure Synapse Analytics. ينشئ المثال التالي مصدر بيانات خارجيًّا لـ Azure Data Lake Gen2 يشير إلى مجموعة بيانات نيويورك المتوفرة للجمهور:

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/')

مثال لإنشاء تنسيق ملف خارجي

ينشئ المثال التالي تنسيق ملف خارجي لملفات التعداد:

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

على سبيل المثال إنشاء جدول خارجي

ينشئ المثال التالي جدولاً خارجيًّا. ترجع الصف الأول:

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)
GO

SELECT TOP 1 * FROM census_external_table

إنشاء جداول خارجية والاستعلام عنها من ملف في Azure Data Lake

باستخدام قدرات استكشاف Data Lake من Synapse Studio يمكنك الآن إنشاء والاستعلام عن أي جدول خارجي باستخدام تجمع Synapse SQL مع نقر زر الماوس الأيمن ببساطة على الملف. يتم دعم إيماءة بنقرة واحدة لإنشاء جداول خارجية من حساب تخزين ADLS Gen2 فقط لملفات Parquet.

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

• يجب أن يكون لديك حق الوصول إلى مساحة العمل مع دور الوصول على الأقل Storage Blob Data Contributor إلى حساب ADLS Gen2 أو قوائم التحكم بالوصول (ACL) التي تمكنك من الاستعلام عن الملفات.

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

من لوحة البيانات، حدد الملف الذي ترغب في إنشاء الجدول الخارجي منه:

ستفتح نافذة حوار. حدد تجمع SQL مخصصًا أو تجمع SQL بلا خادم، ثم قم بإعطاء اسم للجدول وحدد فتح برنامج نصي:

يتم إنشاء البرنامج النصي SQL تلقائيًّا باستنتاج المخطط من الملف:

قم بتشغيل البرنامج النصي سيشغل البرنامج النصي تلقائيًّا «حدد الأعلى» 100 *.:

يتم الآن إنشاء الجدول الخارجي لاستكشاف محتوى هذا الجدول الخارجي في المستقبل يمكن للمستخدم الاستعلام عنه مباشرةً من جزء البيانات:

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

انظر المقالة CETASلمعرفة كيفية حفظ نتائج الاستعلام لأي جدول خارجي في Azure Storage. أو يمكنك بدء الاستعلام عن Apache Spark للجداول الخارجية لـ Azure Synapse.