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

مقالة
06/01/2023

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

مساحة السطح ⁧⁩SELECT⁧⁩ الكاملة، بما فيها غالبية ⁧⁩دوال وعوامل تشغيل SQL⁧⁩.
Create EXTERNAL TABLE AS SELECT (⁧⁩CETAS⁧⁩) ينشئ ⁧⁩جدولاً خارجيًا⁧⁩ ثم يقوم على التوازي بتصدير نتائج جملة Transact-SQL SELECT إلى Azure تخزين.

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

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

نظرة عامة

لدعم تجربة سلسة للاستعلام عن البيانات الموجودة في ملفات تخزين Azure، يستخدم تجمع 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⁩. راجع مقالة ⁧⁩الاستعلام عن تنسيق دلتا ليك⁧⁩ للحصول على أمثلة على الاستخدام.

مخطط الملف

تُمكّنك لغة 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

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

للاستعلام عن ملفات معينة، اقرأ قسم اسم الملف في مقالة ⁧⁩ملفات خاصة بالاستعلام⁧⁩.

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

دالة Filepath

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

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

للحصول على معلومات إضافية، اقرأ قسم "مسار الملف" في مقالة ⁧⁩ملفات خاصة بالاستعلام⁧⁩.

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

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

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

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

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

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

لمزيد من المعلومات التفصيلية، ارجع إلى مشروع قسم البيانات المتداخلة أو المتكررة في مقالة ⁧⁩الاستعلام عن الأنواع المتداخلة لـ 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 من النوع (8000)varchar.
إذا تعذر العثور على الخاصية عند اسم العمود المحدد، تقوم الدالة بإرجاع خطأ.
إذا تعذر العثور على الخاصية في مسار العمود المحدد، بناءً على ⁧⁩وضع المسار⁧⁩، تقوم الدالة بإرجاع خطأ عندما تكون في الوضع المقيد أو إرجاع قيمة فارغة عندما تكون في وضع التراخي.

بالنسبة إلى نماذج الاستعلام، راجع عناصر الوصول من قسم الأعمدة المتداخلة في مقالة ⁧⁩إصدار استعلام إلى الأنواع المتداخلة لـ Parquet⁧⁩.

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

للوصول إلى العناصر من عمود مكرر، مثل عنصر مصفوفة أو خريطة، استخدم الدالة ⁧⁩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]

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

عينات الاستعلام

يمكنك معرفة المزيد حول الاستعلام عن أنواع مختلفة من البيانات باستخدام نماذج الاستعلامات.

الأدوات

الأدوات التي تحتاجها لإصدار الاستعلامات: - Azure Synapse Studio - Azure Data Studio - SQL Server Management Studio

الإعداد التجريبي

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

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

ملاحظة

تُستخدم قواعد البيانات فقط لعرض بيانات التعريف، وليس للبيانات الفعلية. اكتب اسم قاعدة البيانات الذي تستخدمه، وستحتاج إليه لاحقًا.

CREATE DATABASE mydbname;

توفير البيانات التجريبية

تحتوي البيانات التجريبية على مجموعات البيانات التالية:

NYC Taxi - Yellow Taxi Trip Records - جزء من مجموعة بيانات مدينة نيويورك العامة بتنسيق CSV وParquet
مجموعة بيانات السكان بتنسيق CSV
عينة من ملفات Parquet ذات الأعمدة المتداخلة
كتب بتنسيق JSON

مسار المجلد	الوصف
/csv/	المجلد الأصل للبيانات بتنسيق CSV
/csv/population/ /csv/population-unix/ /csv/population-unix-hdr/ /csv/population-unix-hdr-escape /csv/population-unix-hdr-quoted	المجلدات التي تحتوي على ملفات بيانات السكان بتنسيقات CSV مختلفة.
/csv/taxi/	مجلد يحتوي على ملفات البيانات العامة الخاصة بمدينة نيويورك بتنسيق CSV
/parquet/	المجلد الأصل للبيانات بتنسيق Parquet
/parquet/taxi	ملفات البيانات العامة لمدينة نيويورك بتنسيق Parquet، مقسمة حسب السنة والشهر باستخدام مخطط تقسيم Hive/Hadoop.
/parquet/nested/	عينة من ملفات Parquet ذات الأعمدة المتداخلة
/json/	المجلد الأصلي للبيانات بتنسيق JSON
/json/books/	ملفات JSON مع بيانات الكتب

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

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

مشاركة عبر