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

خلال هذه المقالة، ستتعلم كيفية كتابة استعلام باستخدام تجمع SQL بلا خادم في Azure Synapse Analytics. يمثل هدف الاستعلام قراءة ملفات JSON باستخدام OPENROWSET.

• ملفات JSON القياسية، حيث يتم تخزين وثائق JSON متعددة كمصفوفة JSON.
• ملفات JSON المحددة بسطور، حيث يتم فصل مستندات JSON بحرف سطر جديد. الامتدادات الشائعة لهذه الأنواع من الملفات هي jsonl وldjson وndjson.

قراءة وثائق JSON

إن أسهل طريقة للاطلاع على محتوى ملف JSON الخاص بك هي تقديم عنوان URL للملف إلى الدالة OPENROWSET، وتحديد csv FORMAT، وتعيين قيم 0x0b لـ fieldterminator وfieldquote. إذا كنت بحاجة إلى قراءة ملف JSON محدد الأسطر، فهذا يكفي. إذا كان لديك ملف JSON كلاسيكي، فستحتاج إلى تعيين قيم 0x0b ل rowterminator. ستقوم الدالة OPENROWSET بتحليل JSON وإرجاع كل مستند بالتنسيق التالي:

إذا كان الملف متوفرا للجمهور، أو إذا كانت هوية Microsoft Entra الخاصة بك يمكنها الوصول إلى هذا الملف، يجب أن تشاهد محتوى الملف باستخدام الاستعلام مثل الاستعلام الموضح في الأمثلة التالية.

قراءة ملفات JSON

يقرأ نموذج الاستعلام التالي ملفات JSON وملفات JSON المحددة بسطر، ويعيد كل مستند كصف منفصل.

select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.jsonl',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.json',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
    ) with (doc nvarchar(max)) as rows

يتضمن مستند JSON في استعلام العينة السابقة صفيفًا من العناصر. يقوم الاستعلام بإرجاع كل عنصر كسجل منفصل في مجموعة النتائج. تأكد من أنه يمكنك الوصول إلى هذا الملف. إذا كان الملف محميًا بمفتاح SAS أو هوية مخصصة، فستحتاج إلى ⁧⁩إعداد معلومات تسجيل الدخول على مستوى الخادم لتسجيل الدخول على SQL⁧⁩.

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

يستخدم المثال السابق المسار الكامل للملف. كبديل، يمكنك إنشاء مصدر بيانات خارجي بالموقع الذي يشير إلى المجلد الجذر للتخزين، واستخدام مصدر البيانات هذا والمسار النسبي للملف في دالة 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.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.json',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
    ) with (doc nvarchar(max)) as rows

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

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

توزيع مستندات JSON

تقوم الاستعلامات في الأمثلة السابقة بإرجاع كل مستند JSON كسلسلة واحدة في سجل منفصل من مجموعة النتائج. يمكنك استخدام الدالتين JSON_VALUE وOPENJSON لتحليل القيم في مستندات JSON وإعادتها كقيم علائقية، كما هو موضح في المثال التالي:

نموذج مستند JSON

تقرأ أمثلة الاستعلام ملفات json التي تحتوي على مستندات بالبنية التالية:

{
    "date_rep":"2020-07-24",
    "day":24,"month":7,"year":2020,
    "cases":13,"deaths":0,
    "countries_and_territories":"Afghanistan",
    "geo_id":"AF",
    "country_territory_code":"AFG",
    "continent_exp":"Asia",
    "load_date":"2020-07-25 00:05:14",
    "iso_country":"AF"
}

الاستعلام عن ملفات JSON باستخدام JSON_VALUE

يوضح لك الاستعلام أدناه كيفية استخدام JSON_VALUE لاسترداد القيم العددية (date_rep، countries_and_territories، cases) من مستندات JSON:

select
    JSON_VALUE(doc, '$.date_rep') AS date_reported,
    JSON_VALUE(doc, '$.countries_and_territories') AS country,
    CAST(JSON_VALUE(doc, '$.deaths') AS INT) as fatal,
    JSON_VALUE(doc, '$.cases') as cases,
    doc
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
order by JSON_VALUE(doc, '$.geo_id') desc

بمجرد استخراج خصائص JSON من وثيقة JSON، يمكنك تحديد الأسماء المستعارة للعمود وتحويل القيمة النصية إلى نوع ما اختياريًا.

الاستعلام عن ملفات JSON باستخدام OPENJSON

يستخدم الاستعلام التالي OPENJSON. وسوف تسترجع إحصاءات COVID المبلغ عنها في صربيا:

select
    *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
    cross apply openjson (doc)
        with (  date_rep datetime2,
                cases int,
                fatal int '$.deaths',
                country varchar(100) '$.countries_and_territories')
where country = 'Serbia'
order by country, date_rep desc;

النتائج وظيفية مماثلة للنتائج التي تم إرجاعها باستخدام الدالة JSON_VALUE. في بعض الحالات، قد يكون لـ OPENJSON ميزة على JSON_VALUE:

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

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

ستوضح المقالات التالية في هذه السلسلة كيفية:

• مجلدات الاستعلام وملفات متعددة
• إنشاء طرق العرض واستخدامها