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

في هذه المقالة، ستتعلم كيفية كتابة استعلام باستخدام تجمع SQL بدون خادم في Azure Synapse Analytics. سيقوم الاستعلام بقراءة أنواع Parquet المتداخلة. الأنواع المتداخلة هي بنيات معقدة تمثل عناصر أو صفائف. يمكن تخزين الأنواع المتداخلة في:

• Parquet، حيث يمكنك الحصول على أعمدة معقدة متعددة تحتوي على صفائف وعناصر.
• ملفات JSON الهرمية، حيث يمكنك قراءة مستند JSON معقد كعمود واحد.
• مجموعات Azure Cosmos DB (حالياً تحت معاينة عامة مسورة)، حيث يمكن أن يحتوي كل مستند على خصائص معقدة متداخلة.

ينسق تجمع SQL بلا خادم جميع الأنواع المتداخلة كعناصر ومصفوفات JSON. حتى تتمكن من استخراج أو تعديل العناصر المعقدة باستخدام وظائف JSON أو تحليل بيانات JSON باستخدام الدالة OPENJSON.

فيما يلي مثال على استعلام يستخرج القيم العددية والعنصرية من ملف COVID-19 Open Research Dataset JSON، الذي يحتوي على عناصر متداخلة:

SELECT
    title = JSON_VALUE(doc, '$.metadata.title'),
    first_author = JSON_QUERY(doc, '$.metadata.authors[0]'),
    first_author_name = JSON_VALUE(doc, '$.metadata.authors[0].first'),
    complex_object = doc
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/covid19temp/comm_use_subset/pdf_json/000b7d1517ceebb34e1e3e817695b6de03e2fa78.json',
        FORMAT='CSV', FIELDTERMINATOR ='0x0b', FIELDQUOTE = '0x0b', ROWTERMINATOR = '0x0b'
    )
    WITH ( doc varchar(MAX) ) AS docs;

ترجع JSON_VALUE الدالة قيمة عددية من الحقل في المسار المحدد. ترجع JSON_QUERY الدالة عنصراً منسقاً كـ JSON من الحقل في المسار المحدد.

هام

يستخدم هذا المثال ملفاً من مجموعة بيانات البحث المفتوح COVID-19. راجع الترخيص وهيكل البيانات هنا.

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

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

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

يمكن أن يحتوي ملف Parquet على أعمدة متعددة مع أنواع معقدة. يتم تنسيق القيم من هذه الأعمدة كنص JSON ويتم إرجاعها كأعمدة VARCHAR. يقرأ الاستعلام التالي ملف structExample.parquet ويوضح كيفية قراءة قيم الأعمدة المتداخلة:

SELECT
    DateStruct, TimeStruct, TimestampStruct, DecimalStruct, FloatStruct
FROM
    OPENROWSET(
        BULK 'parquet/nested/structExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    )
    WITH (
        DateStruct VARCHAR(8000),
        TimeStruct VARCHAR(8000),
        TimestampStruct VARCHAR(8000),
        DecimalStruct VARCHAR(8000),
        FloatStruct VARCHAR(8000)
    ) AS [r];

يعرض هذا الاستعلام المعدل النتائج التالية. يتم إرجاع محتوى كل عنصر متداخل كنص JSON.

DateStruct	TimeStruct	TimestampStruct	DecimalStruct	FloatStruct
{"Date":"2009-04-25"}	{"Time":"20:51:54.3598000"}	{"Timestamp":"5501-04-08 12:13:57.4821000"}	{"Decimal":11143412.25350}	{"Float":0.5}
{"Date":"1916-04-29"}	{"Time":"00:16:04.6778000"}	{"Timestamp":"1990-06-30 20:50:52.6828000"}	{"Decimal":1963545.62800}	{"Float":-2.125}

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

SELECT
    SimpleArray
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

سيرجع هذا الاستعلام النتيجة التالية:

SimpleArray
[11،12،13]
[21،22،23]

قراءة الخصائص من أعمدة العنصر المتداخلة

JSON_VALUE تمكنك الدالة من إرجاع القيم من الأعمدة المنسقة كنص JSON:

SELECT
    title = JSON_VALUE(complex_column, '$.metadata.title'),
    first_author_name = JSON_VALUE(complex_column, '$.metadata.authors[0].first'),
    body_text = JSON_VALUE(complex_column, '$.body_text.text'),
    complex_column
FROM
    OPENROWSET( BULK 'https://azureopendatastorage.blob.core.windows.net/covid19temp/comm_use_subset/pdf_json/000b7d1517ceebb34e1e3e817695b6de03e2fa78.json',
                FORMAT='CSV', FIELDTERMINATOR ='0x0b', FIELDQUOTE = '0x0b', ROWTERMINATOR = '0x0b' ) WITH ( complex_column varchar(MAX) ) AS docs;

تظهر النتيجة في الجدول التالي:

العنوان	first_author_name	body_text	complex_column
معلومات تكميلية عن وباء بيئي…	جوليان	- الشكل S1 : Phylogeny من...	{ "paper_id": "000b7d1517ceebb34e1e3e817695b6de03e2fa78", "metadata": { "title": "Supplementary Information An eco-epidemiological study of Morbilli-related paramyxovirus infection in Madagascar bats reveals host-switching as the dominant macro-evolutionary mechanism", "authors": [ { "first": "Julien"

على عكس ملفات JSON، التي تُرجع في معظم الحالات عموداً واحداً يحتوي على عنصر JSON معقد، يمكن أن تحتوي ملفات parquet على أعمدة معقدة متعددة. يمكنك قراءة خصائص الأعمدة المتداخلة باستخدام الدالة على JSON_VALUE كل عمود. OPENROWSET يمكنك من تحديد مسارات الخصائص المتداخلة في عبارة WITH مباشرة. يمكنك تعيين المسارات كاسم لعمود، أو يمكنك إضافة تعبير مسار JSON بعد نوع العمود.

يقرأ الاستعلام التالي ملف structExample.parquet ويوضح كيفية قراءة قيم الأعمدة المتداخلة. هناك طريقتان للإشارة إلى قيمة متداخلة:

• عن طريق تحديد تعبير مسار القيمة المتداخلة بعد مواصفات النوع.
• من خلال تنسيق اسم العمود كمسار متداخل باستخدام do "." للإشارة إلى الحقول.

SELECT
    *
FROM
    OPENROWSET(
        BULK 'parquet/nested/structExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    )
    WITH (
        [DateValue] DATE '$.DateStruct.Date',
        [TimeStruct.Time] TIME,
        [TimestampStruct.Timestamp] DATETIME2,
        DecimalValue DECIMAL(18, 5) '$.DecimalStruct.Decimal',
        [FloatStruct.Float] FLOAT
    ) AS [r];

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

يقرأ الاستعلام التالي ملف justSimpleArray.parquet ويستخدم JSON_VALUE لاسترداد عنصر عددي من داخل عمود متكرر، مثل صفيف أو خريطة:

SELECT
    *,
    JSON_VALUE(SimpleArray, '$[0]') AS FirstElement,
    JSON_VALUE(SimpleArray, '$[1]') AS SecondElement,
    JSON_VALUE(SimpleArray, '$[2]') AS ThirdElement
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

ها هي النتيجة:

SimpleArray	FirstElement	SecondElement	ThirdElement
[11،12،13]	11	12	13
[21،22،23]	21	22	23

الوصول إلى العناصر الفرعية من الأعمدة المعقدة

يقرأ الاستعلام التالي ملف mapExample.parquet ويستخدم JSON_QUERY لاسترداد عنصر غير عددي من داخل عمود متكرر، مثل صفيف أو خريطة:

SELECT
    MapOfPersons,
    JSON_QUERY(MapOfPersons, '$."John Doe"') AS [John]
FROM
    OPENROWSET(
        BULK 'parquet/nested/mapExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

يمكنك أيضاً الرجوع بشكل صريح إلى الأعمدة التي تريد إرجاعها في WITH عبارة:

SELECT DocId,
    MapOfPersons,
    JSON_QUERY(MapOfPersons, '$."John Doe"') AS [John]
FROM
    OPENROWSET(
        BULK 'parquet/nested/mapExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) 
    WITH (DocId bigint, MapOfPersons VARCHAR(max)) AS [r];

يتم إرجاع البنية MapOfPersons كعمود VARCHAR وتنسيقها كسلسلة JSON.

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

إذا كان لديك صفيف من القيم العددية (على سبيل المثال [1,2,3]) في بعض الأعمدة، يمكنك توسيعها بسهولة وربطها بالصف الرئيسي باستخدام هذا البرنامج النصي:

SELECT
    SimpleArray, Element
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS arrays
    CROSS APPLY OPENJSON (SimpleArray) WITH (Element int '$') as array_values

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

ستوضح لك المقالة التالية كيفية الاستعلام عن ملفات JSON.