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

  • مقالة
  • قراءة خلال 4 دقائق

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

مثال على التشغيل السريع

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

قراءة ملف Parquet

أسهل طريقة لمعرفة محتوى ملفك PARQUET هي توفير عنوان URL للملف للعمل OPENROWSET وتحديد الباركيه FORMAT. إذا كان الملف متوفرًا للعموم أو كان بإمكان هوية Azure AD الخاصة بك الوصول إلى هذا الملف، فستكون قادرًا على الاطلاع على محتوى الملف باستخدام الاستعلام مثل المبين في المثال التالي:

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'

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

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

يستخدم المثال السابق مسارا كاملا إلى الملف. كبديل، يمكنك إنشاء مصدر بيانات خارجي مع الموقع الذي يشير إلى المجلد الجذر للتخزين، واستخدام مصدر البيانات هذا والمسار النسبي للملف في 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 (على سبيل المثال) لكافة أعمدة السلسلة في WITH الجملة أو تعيين بعض ترتيب UTF-8 على Latin1_General_100_BIN2_UTF8مستوى قاعدة البيانات. قد يؤدي عدم التطابق بين ترميز النص في الملف وترتيب أعمدة السلسلة إلى حدوث أخطاء تحويل غير متوقعة. يمكنك بسهولة تغيير الترتيب الافتراضي لقاعدة البيانات الحالية باستخدام عبارة T-SQL التالية:alter database current collate Latin1_General_100_BIN2_UTF8 يمكنك بسهولة تعيين الترتيب على أنواع colum باستخدام التعريف التالي: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8

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

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

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

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

يتم استخدام مجموعة بيانات التاكسي الأصفر في مدينة نيويورك في هذه العينة. يمكنك الاستعلام عن ملفات Parquet بنفس الطريقة التي تقرأ بها ملفات CSV. الفرق الوحيد هو أنه يجب تعيين المعلمة FILEFORMAT على 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.

تظهر العينة أدناه إمكانيات الاستدلال للمخطط التلقائي لملفات 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;

تعيين النوع

لتعيين نوع الباركيه إلى SQL نوع التحقق الأصلي نوع التعيين للباركيه.

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

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