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

مقالة
05/02/2022
قراءة خلال 4 دقائق

في هذه المقالة، ستتعرف على كيفية كتابة استعلام باستخدام تجمع 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 وإرجاع كل مستند بالتنسيق التالي:

دكتور
{"date_rep":"2020-07-24","اليوم":24,"الشهر":7,"السنة":2020,"الحالات":3,"الوفيات":0,"geo_id":"AF"}
{"date_rep":"2020-07-25","اليوم":25,"الشهر":7,"السنة":2020,"الحالات":7,"الوفيات":0,"geo_id":"AF"}
{"date_rep":"2020-07-26","يوم":26,"شهر":7,"سنة":2020,"حالات":4,"وفيات":0,"geo_id":"AF"}
{"date_rep":"2020-07-27","اليوم":27,"الشهر":7,"السنة":2020,"الحالات":8,"الوفيات":0,"geo_id":"AF"}

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

قراءة ملفات 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 وتحليل القيم في مستندات JSON وإرجاعها OPENJSON كقيم ارتباطية، كما هو موضح في المثال التالي:

date_rep	الحالات	geo_id
2020-07-24	3	AF
2020-07-25	7	AF
2020-07-26	4	AF
2020-07-27	8	AF

نموذج مستند 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 محدد بالأسطر ، فستحتاج إلى تعيين FIELDTERMINATOR 0x0b FIELDQUOTE . إذا كان لديك تنسيق JSON قياسي ، فأنت بحاجة إلى تعيينه ROWTERMINATOR إلى 0x0b.

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

يوضح لك الاستعلام أدناه كيفية استخدام JSON_VALUE لاسترداد القيم العددية (date_rep، ، countries_and_territoriescases) من مستندات 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 قد يكون أسرع إذا كنت تقوم بإرجاع عدد كبير من العقارات. إذا كنت تقوم بإرجاع 1-2 خصائص فقط ، OPENJSON فقد تكون الوظيفة عامة.
يجب عليك استخدام الدالة OPENJSON إذا كنت بحاجة إلى تحليل الصفيف من كل مستند، وضمه إلى الصف الأصل.

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

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