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

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

مثال على البدء السريع

تتيح لك وظيفة OPENROWSET قراءة محتوى ملف Parquet من خلال توفير عنوان URL لملفك.

قراءة ملف Parquet

إن أسهل طريقة للاطلاع على محتوى ملف PARQUET الخاص بك هي توفير عنوان URL للملف OPENROWSET للوظيفة وتحديد Parquet FORMAT. إذا كان الملف متوفرا بشكل عام أو إذا كانت هوية Microsoft Entra الخاصة بك يمكنها الوصول إلى هذا الملف، يجب أن تكون قادرا على رؤية محتوى الملف باستخدام الاستعلام مثل الاستعلام الموضح في المثال التالي:

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.parquet',
    format = 'parquet') as rows

تأكد من أنه يمكنك الوصول إلى هذا الملف. إذا كان الملف محميا بمفتاح SAS أو هوية Azure المخصصة، فستحتاج إلى إعداد بيانات اعتماد مستوى الخادم لتسجيل الدخول إلى sql.

هام

تأكد من أنك تستخدم ترتيب قاعدة بيانات UTF-8 (على سبيل المثال Latin1_General_100_BIN2_UTF8) لأن قيم السلسلة في ملفات PARQUET يتم ترميزها باستخدام ترميز UTF-8. قد يتسبب عدم تطابق بين ترميز النص في ملف PARQUET والترتيب في حدوث أخطاء تحويل غير متوقعة. يمكنك بسهولة تغيير الترتيب الافتراضي لقاعدة البيانات الحالية باستخدام عبارة T-SQL التالية: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; لمزيد من المعلومات حول الترتيب، راجع أنواع الترتيب المدعومة ل Synapse SQL.

إذا كنت تستخدم الترتيب Latin1_General_100_BIN2_UTF8، فستحصل على تحسين أداء إضافي مقارنة بالترتيبات الأخرى. الترتيب Latin1_General_100_BIN2_UTF8 متوافق مع قواعد فرز سلسلة Parquet. يمكن لمجمّع SQL إزالة بعض أجزاء ملفات Parquet التي لن تحتوي على البيانات المطلوبة في الاستعلامات (تقليم ملف/عمود - مقطع). إذا كنت تستخدم عمليات ترتيب أخرى، فسيتم تحميل جميع البيانات من ملفات Parquet في Synapse SQL وتحدث التصفية داخل عملية SQL. Latin1_General_100_BIN2_UTF8 يحتوي الترتيب على تحسين أداء إضافي يعمل فقط مع parquet وCosmos DB. الجانب السلبي هو أنك تفقد قواعد المقارنة الدقيقة مثل عدم الحساسية لحالة الأحرف.

استخدام مصادر البيانات

يستخدم المثال السابق المسار الكامل للملف. كبديل، يمكنك إنشاء مصدر بيانات خارجي بالموقع الذي يشير إلى المجلد الجذر للتخزين، واستخدام مصدر البيانات هذا والمسار النسبي للملف في وظيفة OPENROWSET :

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) as rows

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

تحديد المخطط بشكل صريح

يمكّنك OPENROWSET من التحديد الصريح للأعمدة التي تريد قراءتها من الملف باستخدام عبارة WITH:

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) with ( date_rep date, cases int, geo_id varchar(6) ) as rows

هام

تأكد من أنك تحدد بوضوح بعض ترتيب UTF-8 (على سبيل المثال Latin1_General_100_BIN2_UTF8) لجميع أعمدة السلسلة في عبارة WITH أو قم بتعيين بعض ترتيب UTF-8 على مستوى قاعدة البيانات. عدم التطابق بين ترميز النص في الملف وترتيب عمود السلسلة إلى قد يؤدي حدوث أخطاء تحويل غير متوقعة. يمكنك بسهولة تغيير الترتيب الافتراضي لقاعدة البيانات الحالية باستخدام عبارة T-SQL التالية: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; يمكنك بسهولة تعيين الترتيب على أنواع الكولوم، على سبيل المثال: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8 لمزيد من المعلومات حول الترتيب، راجع أنواع الترتيب المدعومة ل Synapse SQL.

في الأقسام التالية، يمكنك معرفة كيفية الاستعلام عن أنواع مختلفة من ملفات PARQUET.

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

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

مجموعة البيانات

يتم استخدام مجموعة بيانات NYC Yellow Taxi في هذا النموذج. يمكنك الاستعلام عن ملفات Parquet بنفس الطريقة التي تقرأ بها ملفات CSV. الاختلاف الوحيد هو أنه يجب تعيين المعلمة FILEFORMAT على PARQUET. تظهر الأمثلة في هذه المقالة تفاصيل قراءة ملفات Parquet.

مجموعة الاستعلام عن ملفات Parquet

يمكنك تحديد الأعمدة ذات الأهمية فقط عند الاستعلام عن ملفات Parquet.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) WITH (
        tpepPickupDateTime DATETIME2,
        passengerCount INT
    ) AS nyc
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

استنتاج المخطط التلقائي

لا تحتاج إلى استخدام جملة OPENROWSET WITH عند قراءة ملفات Parquet. أسماء الأعمدة وأنواع البيانات تتم قراءتها تلقائيًا من ملفات Parquet.

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

يظهر النموذج التالي قدرات الاستدلال التلقائي للمخطط لملفات Parquet. تقوم بإرجاع عدد الصفوف في سبتمبر 2018 دون تحديد مخطط.

إشعار

ليس عليك تحديد أعمدة في جملة OPENROWSET WITH عند قراءة ملفات Parquet. في هذه الحالة، ستستخدم خدمة استعلام تجمع SQL بلا خادم بيانات التعريف في ملف Parquet وربط الأعمدة بالاسم.

SELECT TOP 10 *
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=9/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) AS nyc

بيانات مقسمة للاستعلام

مجموعة البيانات الواردة في هذه العينة مقسمة (مقسمة) إلى مجلدات فرعية منفصلة. يمكنك استهداف أقسام معينة باستخدام دالة filepath. يوضح هذا المثال قيم الأجرة لكل سنة وشهر وpayment_type للأشهر الثلاثة الأولى من عام 2017.

إشعار

استعلام تجمع SQL بلا خادم متوافق مع نظام التقسيم Hive/Hadoop.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=*/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) nyc
WHERE
    nyc.filepath(1) = 2017
    AND nyc.filepath(2) IN (1, 2, 3)
    AND tpepPickupDateTime BETWEEN CAST('1/1/2017' AS datetime) AND CAST('3/31/2017' AS datetime)
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

نوع التعيين

لتعيين نوع Parquet إلى النوع الأصلي من SQL، حدد تعيين النوع لـ Parquet.

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

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