الاستعلام عن ملفات التخزين باستخدام تجمع SQL بلا خادم

يسمح لك تجمع SQL بلا خادم بالاستعلام عن البيانات في مستودع البيانات الخاص بك. يوفر مساحة سطح استعلام Transact-SQL (T-SQL) التي تستوعب استعلامات البيانات شبه المنظمة وغير المنظمة. بخصوص إجراء الاستعلام، يتم دعم جوانب T-SQL التالية:

• مساحة سطح SELECT الكاملة، بما في ذلك معظم وظائف وعوامل تشغيل SQL.
• ينشئ CREATE EXTERNAL TABLE AS SELECT (CETAS) جدولا خارجيا ثم يصدر، بالتوازي، نتائج عبارة T-SQL SELECT إلى Azure Storage.

لمزيد من المعلومات حول ما هو أو غير مدعوم حاليا، اقرأ نظرة عامة على تجمع SQL بلا خادم، أو المقالات التالية:

• تطوير الوصول إلى التخزين حيث يمكنك استخدام الجداول الخارجية ودالة OPENROWSET لقراءة البيانات من التخزين.
• التحكم في الوصول إلى التخزين حيث يمكنك معرفة كيفية تمكين Synapse SQL من الوصول إلى التخزين باستخدام مصادقة SAS أو الهوية المدارة لمساحة العمل.

نظرة عامة

لدعم تجربة سلسة للاستعلام الموضعي عن البيانات الموجودة في ملفات Azure Storage، يستخدم تجمع SQL بلا خادم وظيفة OPENROWSET مع المزيد من الإمكانات:

• الاستعلام عن ملفات PARQUET
• الاستعلام عن ملفات CSV والنص المحدد (فاصل الحقل، فاصل الصف، حرف الإلغاء)
• استعلام تنسيق DELTA LAKE
• قراء مجموعة فرعية مختارة من الأعمدة
• استنتاج المخطط
• الاستعلام عن ملفات أو مجلدات متعددة
• الدالة Filename
• الدالة Filepath
• العمل مع الأنواع المعقدة وبنى البيانات المتداخلة أو المتكررة

الاستعلام عن ملفات PARQUET

للاستعلام عن بيانات مصدر Parquet، استخدم FORMAT = 'PARQUET':

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

للحصول على أمثلة الاستخدام، راجع ملفات Query Parquet.

الاستعلام عن ملفات CSV

للاستعلام عن بيانات مصدر CSV، استخدم FORMAT = 'CSV'. يمكنك تحديد مخطط ملف CSV كجزء من الدالة OPENROWSET عند الاستعلام عن ملفات CSV:

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.csv', FORMAT = 'CSV', PARSER_VERSION='2.0') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

هناك بعض الخيارات الإضافية التي يمكن استخدامها لضبط قواعد التحليل بتنسيق CSV المخصص:

• ESCAPE_CHAR = 'char' تحديد الحرف في الملف المستخدم للهروب من نفسه وكافة قيم المحدد في الملف. إذا كان الإلغاء متبوعًا بقيمة أخرى غير نفسها، أو أي من قيم المحدد، فسيتم إسقاط حرف الإلغاء عند قراءة القيمة. ESCAPE_CHAR يتم تطبيق المعلمة FIELDQUOTE سواء تم تمكين أو لم يتم تمكينها. لا يتم استخدامه للهروب من حرف الاقتباس. يجب أن يكون حرف الاقتباس تم إلغاؤه بحرف اقتباس آخر. يمكن أن يظهر حرف اقتباس داخل قيمة العمود فقط إذا كانت القيمة مغلفة بأحرف اقتباس.
• FIELDTERMINATOR ='field_terminator' تحديد منتهي الحقل الذي سيتم استخدامه. فاصل الحقل الافتراضي هو فاصلة (,).
• ROWTERMINATOR ='row_terminator' تحديد فاصل الصف الذي سيتم استخدامه. فاصل الصف الافتراضي هو حرف سطر جديد (\r\n).

الاستعلام عن تنسيق DELTA LAKE

للاستعلام عن بيانات مصدر Delta Lake، استخدم FORMAT = 'DELTA' المجلد الجذر الذي يحتوي على ملفات Delta Lake والإشارة إليه.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

يجب أن يحتوي المجلد الجذر على مجلد فرعي يسمى _delta_log. للحصول على أمثلة الاستخدام، راجع ملفات Query Delta Lake (v1).

مخطط الملف

تسمح لك لغة SQL في Synapse SQL بتعريف مخطط الملف كجزء من OPENROWSET الدالة وقراءة كل أو مجموعة فرعية من الأعمدة، أو تحاول تحديد أنواع الأعمدة تلقائيا من الملف باستخدام استنتاج المخطط.

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

لتحديد الأعمدة التي تريد قراءتها، يمكنك توفير عبارة اختيارية WITH ضمن العبارة الخاصة بك OPENROWSET .

• إذا كانت هناك ملفات بيانات CSV، فوفر أسماء الأعمدة وأنواع البيانات الخاصة بها لقراءة جميع الأعمدة. إذا كنت تريد مجموعة فرعية من الأعمدة، فاستخدم الأرقام الترتيبية لاختيار الأعمدة من ملفات البيانات الأصلية حسب الترتيب. ترتبط الأعمدة بالتعيين الترتيبي.
• إذا كانت هناك ملفات بيانات Parquet، فقم بتوفير أسماء الأعمدة التي تطابق أسماء الأعمدة في ملفات البيانات الأصلية. الأعمدة مرتبطة بالاسم.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (
      C1 int, 
      C2 varchar(20),
      C3 varchar(max)
) as rows;

لكل عمود، تحتاج إلى تحديد اسم العمود وكتابة عبارة .WITH للحصول على عينات، راجع قراءة ملفات CSV دون تحديد كافة الأعمدة.

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

بحذف العبارة WITH من العبارة OPENROWSET ، يمكنك إرشاد الخدمة للكشف التلقائي (الاستدلال) عن المخطط من الملفات الأساسية.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 

تأكد من استخدام أنواع البيانات المستنتجة المناسبة لتحقيق الأداء الأمثل.

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

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

يتم تطبيق القواعد التالية:

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

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

للحصول على أمثلة الاستخدام، راجع مجلدات الاستعلام وملفات متعددة.

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

دالة Filename

تقوم هذه الدالة بإرجاع اسم الملف الذي ينشأ منه الصف.

للاستعلام عن ملفات معينة، اقرأ قسم Filename في مقالة Query specific files .

نوع بيانات الإرجاع هو nvarchar(1024). للحصول على الأداء الأمثل، يتم دائمًا تحويل نتيجة وظيفة اسم الملف إلى نوع البيانات المناسب. إذا كنت تستخدم نوع بيانات الحرف، فتأكد من استخدام الطول المناسب.

دالة Filepath

تقوم هذه الدالة بإرجاع مسار كامل أو جزء من المسار:

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

للحصول على معلومات إضافية، اقرأ قسم Filepath في مقالة Query specific files .

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

العمل مع الأنواع المعقدة وبنى البيانات المتداخلة أو المتكررة

لتمكين تجربة سلسة مع البيانات المخزنة في أنواع البيانات المتداخلة أو المتكررة، كما هو الحال في ملفات Parquet ، أضاف تجمع SQL بلا خادم الملحقات التالية.

بيانات المشروع المتداخلة أو المتكررة

لعرض البيانات، قم بتشغيل عبارة SELECT عبر ملف Parquet الذي يحتوي على أعمدة من أنواع البيانات المتداخلة. في الإخراج، يتم تسلسل القيم المتداخلة في JSON وإرجاعها كنوع بيانات varchar(8000) SQL.

    SELECT * FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

لمزيد من المعلومات، راجع قسم البيانات المتداخلة أو المتكررة في Project في مقالة أنواع Query Parquet المتداخلة.

الوصول إلى العناصر من الأعمدة المتداخلة

للوصول إلى العناصر المتداخلة من عمود متداخل، مثل البنية، استخدم رمز النقطة لتسلسل أسماء الحقول في المسار. قم بتوفير المسار كما column_name في WITH عبارة الدالة OPENROWSET .

فيما يلي مثال على جزء بناء الجملة:

    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    WITH ('column_name' 'column_type')
    [AS alias]
    'column_name' ::= '[field_name.] field_name'

بشكل افتراضي، تتطابق الدالة OPENROWSET مع اسم الحقل المصدر والمسار مع أسماء الأعمدة المتوفرة في العبارة WITH . يمكن الوصول إلى العناصر المضمنة في مستويات تداخل مختلفة داخل نفس ملف Parquet المصدر باستخدام العبارة WITH .

إرجاع القيم

• ترجع الدالة قيمة عددية، مثل int، decimalو، و varchar، من العنصر المحدد، وعلى المسار المحدد، لجميع أنواع Parquet غير الموجودة في مجموعة النوع المتداخل.
• إذا كان المسار يشير إلى عنصر من نوع متداخل، ترجع الدالة جزء JSON بدءا من العنصر العلوي على المسار المحدد. جزء JSON من نوع varchar(8000).
• إذا تعذر العثور على الخاصية في المحدد column_name، ترجع الدالة خطأ.
• إذا تعذر العثور على الخاصية في المحدد column_path، اعتمادا على وضع المسار، ترجع الدالة خطأ عندما تكون في الوضع الصارم أو فارغة عندما تكون في وضع التراخي.

للحصول على نماذج الاستعلام، راجع قسم Read properties from nested object columns في مقالة Query Parquet nested types .

عناصر الوصول من الأعمدة المكررة

للوصول إلى العناصر من عمود متكرر، مثل عنصر صفيف أو خريطة، استخدم الدالة JSON_VALUE لكل عنصر عددي تحتاج إلى عرضه وتوفيره:

• عمود متداخل أو مكرر، كمعلمة أولى
• مسار JSON الذي يحدد العنصر أو الخاصية للوصول إليها، كمعلمة ثانية

للوصول إلى العناصر غير الزخرية من عمود متكرر، استخدم الدالة JSON_QUERY لكل عنصر غير أحرف تحتاج إلى عرضه وتوفيره:

• عمود متداخل أو مكرر، كمعلمة أولى
• مسار JSON الذي يحدد العنصر أو الخاصية للوصول إليها، كمعلمة ثانية

راجع جزء بناء الجملة التالي:

    SELECT
       JSON_VALUE (column_name, path_to_sub_element),
       JSON_QUERY (column_name [ , path_to_sub_element ])
    FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

يمكنك العثور على نماذج الاستعلام للوصول إلى العناصر من الأعمدة المتكررة في مقالة أنواع Query Parquet المتداخلة .

المحتوى ذو الصلة

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

• الاستعلام عن ملفات CSV
• ملفات Parquet للاستعلام
• الاستعلام عن ملفات JSON
• الاستعلام عن القيم المتداخلة
• استعلام عن المجلدات وملفات CSV متعددة
• استخدام بيانات تعريف الملف في الاستعلامات
• إنشاء طرق العرض واستخدامها