Query JSON files using serverless SQL pool in Azure Synapse Analytics

تلميح

Microsoft Fabric Data Warehouse هو مستودع علائقي على نطاق مؤسسي قائم على أساس بحيرة البيانات، مع بنية جاهزة للمستقبل، وذكاء اصطناعي مدمج، وميزات جديدة. إذا كنت جديدا في مستودع البيانات، ابدأ ب Fabric Data Warehouse. يمكن لأحمال عمل تجمع SQL المخصصة الحالية الترقية إلى Fabric للوصول إلى قدرات جديدة في علوم البيانات، والتحليلات اللحظية، والتقارير.

في هذا المقال، ستتعلم كيفية كتابة استعلام باستخدام مجموعة 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"}

إذا كان الملف متاحا للجمهور، أو إذا كان هويتك على 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 وإعادتها كقيم علائقية، كما هو موضح في المثال التالي:

date_rep cases 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 و FIELDQUOTE 0x0b. إذا كان لديك تنسيق JSON قياسي، عليك ضبطه ROWTERMINATOR على 0x0b.

استعلام ملفات 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. سيستعيد إحصائيات كوفيد المبلغ عنها في صربيا:

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 إذا كنت بحاجة لتحليل المصفوفة من كل مستند، وربطها بالصف الأب.

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

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

  • Last updated on