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

هل هذه الصفحة مفيدة؟

هل لديك ملاحظات إضافية؟

سيتم إرسال الملاحظات إلى Microsoft: بالضغط على الزر «إرسال»، سيتم استخدام ملاحظاتك لتحسين منتجات Microsoft وخدماتها. نهج الخصوصية.

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

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

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

فيما يلي مثال على استعلام يستخرج القيم العددية وقيم الكائنات من ملف JSON لمجموعة بيانات البحث المفتوح COVID-19 ، والذي يحتوي على كائنات متداخلة:

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	الهيكل الزمني	البنية العشرية	FloatStruct
{"تاريخ":"2009-04-25"}	{"الوقت":"20:51:54.3598000"}	{"الطابع الزمني":"5501-04-08 12:13:57.4821000"}	{"عشري":11143412.25350}	{"تعويم":0.5}
{"التاريخ":"1916-04-29"}	{"الوقت":"00:16:04.6778000"}	{"الطابع الزمني":"1990-06-30 20:50:52.6828000"}	{"عشري":1963545.62800}	{"تعويم":-2.125}

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

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

سيؤدي هذا الاستعلام إلى إرجاع النتيجة التالية:

سيمبل لايف
[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 of ...	{ "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];

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

سيمبل لايف	أولاالعنصر الأول	العنصر الثاني	العنصر الثالث
[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.

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

إذا كان لديك صفيف من القيم العددية (على سبيل المثال [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.