البرنامج التعليمي: استكشاف وتحليل مستودعات البيانات مع تجمع SQL بلا خادم

تتعلم في هذا البرنامج التعليمي كيفية إجراء تحليل البيانات الاستكشافية. يمكنك دمج Azure Open Datasets المفتوحة المختلفة باستخدام تجمع SQL بلا خادم. يمكنك بعد ذلك تصور النتائج في Synapse Studio الخاص بـ Azure Synapse Analytics.

تتيح لك الدالة OPENROWSET(BULK...) الوصول إلى الملفات في Azure Storage. [OPENROWSET](develop-openrowset.md) يقرأ محتوى مصدر بيانات بعيد، مثل ملف، ويعيد المحتوى كمجموعة من الصفوف.

استنتاج المخطط التلقائي

بحكم أن البيانات مخزنة بتنسيق ملف Parquet، يتوفر استنتاج تلقائي للمخطط. يمكنك الاستعلام عن البيانات دون سرد أنواع البيانات لجميع الأعمدة في الملفات. يمكنك أيضًا استخدام آلية العمود الافتراضي والدالة filepath لتصفية مجموعة فرعية معينة من الملفات.

ملاحظة

الترتيب الافتراضي هو SQL_Latin1_General_CP1_CI_ASIf. بالنسبة للترتيب غير الافتراضي، ضع في الاعتبار حساسية الحالة.

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

سيكون اسم العمود tpepPickupDateTime صحيحًا بينما tpeppickupdatetime لن يعمل في ترتيب غير افتراضي.

يستخدم هذا البرنامج التعليمي مجموعة بيانات عن سيارة أجرة في مدينة نيويورك (NYC):

• مواعيد وأوقات الإقلال والإنزال
• مواقع الإقلال والإنزال
• مسافات خاصة بالرحلة
• أسعار مفصلة
• أنواع الأجور
• أنواع السداد
• عدد الركاب المبلغ عنه من جانب السائق

للتعرف على بيانات سيارة الأجرة الخاصة بمدينة نيويورك، شغّل الاستعلام التالي:

SELECT TOP 100 * FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]

وبالمثل، يمكنك الاستعلام عن مجموعة بيانات العطلات الرسمية باستخدام الاستعلام التالي:

SELECT TOP 100 * FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
        FORMAT='PARQUET'
    ) AS [holidays]

يمكنك أيضًا الاستعلام عن مجموعة بيانات الطقس باستخدام الاستعلام التالي:

SELECT
    TOP 100 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [weather]

يمكنك معرفة المزيد حول معنى الأعمدة الفردية في أوصاف مجموعات البيانات:

• ⁧⁩تاكسي مدينة نيويورك⁧⁩
• ⁧⁩العطلات الرسمية⁧⁩
• ⁧⁩بيانات الطقس⁧⁩

سلسلة زمنية، موسمية، وتحليل أبعد من ذلك

يمكنك أن تلخص العدد السنوي لركوب سيارات الأجرة عن طريق استخدام الاستعلام التالي:

SELECT
    YEAR(tpepPickupDateTime) AS current_year,
    COUNT(*) AS rides_per_year
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) >= '2009' AND nyc.filepath(1) <= '2019'
GROUP BY YEAR(tpepPickupDateTime)
ORDER BY 1 ASC

يُظهر المقتطف التالي نتيجة العدد السنوي لرحلات سيارات الأجرة:

يمكن تصور البيانات في Synapse Studio عن طريق التبديل من ⁧⁩الجدول⁧⁩ إلى طريقة عرض ⁧⁩المخطط⁧⁩. يمكنك الاختيار من بين أنواع مختلفة من المخططات، مثل ⁧⁩المنطقة⁧⁩ و⁧⁩الشريطي⁧⁩ و⁧⁩العمودي⁧⁩ و⁧⁩الخطي⁧⁩ و⁧⁩الدائري⁧⁩ و⁧⁩المبعثر⁧⁩. في هذه الحالة، ارسم المخطط ⁧⁩العمودي⁧⁩ مع تعيين ⁧⁩فئة⁧⁩العمود إلى ⁧⁩current_year⁧⁩:

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

ملاحظة

عند كتابة هذا البرنامج التعليمي، تكون بيانات عام 2019 غير كاملة. ونتيجة لذلك، هناك انخفاض كبير في عدد الرحلات لتلك السنة.

يمكنك تركيز التحليل على عام واحد، على سبيل المثال، 2016. يرجع الاستعلام التالي بالعدد اليومي للرحلات خلال تلك السنة:

SELECT
    CAST([tpepPickupDateTime] AS DATE) AS [current_day],
    COUNT(*) as rides_per_day
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) = '2016'
GROUP BY CAST([tpepPickupDateTime] AS DATE)
ORDER BY 1 ASC

يعرض المقتطف التالي نتيجة هذا الاستعلام:

يمكنك بسهولة تصور البيانات برسم المخطط العمودي مرة أخرى مع تعيين العمود الفئة إلى اليوم الحالي وتعيين العمود وسيلة الإيضاح (سلسلة) إلى مشاوير لكل يوم.

من مخطط الرسم البياني، يمكنك أن ترى أن هناك نمطًا أسبوعيًا، مع وضع أيام السبت كيوم ذروة. يقل عدد مرات ركوب سيارات الأجرة خلال أشهر الصيف بسبب الإجازات. كما تلاحظ بعض الانخفاضات الهامة في عدد رحلات سيارات الأجرة دون نمط واضح بشأن وقت الرحلات وسببها.

تحقق فيما بعد مما إذا كان الانخفاض في الرحلات يرتبط بالعطلات الرسمية. تحقق مما إذا كان هناك ارتباط من خلال الانضمام إلى مجموعة بيانات ركوب تاكسي مدينة نيويورك مع مجموعة بيانات الإجازات العامة:

WITH taxi_rides AS (
SELECT
    CAST([tpepPickupDateTime] AS DATE) AS [current_day],
    COUNT(*) as rides_per_day
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) = '2016'
GROUP BY CAST([tpepPickupDateTime] AS DATE)
),
public_holidays AS (
SELECT
    holidayname as holiday,
    date
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
        FORMAT='PARQUET'
    ) AS [holidays]
WHERE countryorregion = 'United States' AND YEAR(date) = 2016
),
joined_data AS (
SELECT
    *
FROM taxi_rides t
LEFT OUTER JOIN public_holidays p on t.current_day = p.date
)

SELECT 
    *,
    holiday_rides = 
    CASE   
      WHEN holiday is null THEN 0   
      WHEN holiday is not null THEN rides_per_day
    END   
FROM joined_data
ORDER BY current_day ASC

تسليط الضوء على عدد مرات ركوب سيارات الأجرة خلال أيام العطلات الرسمية. لهذا الغرض، نختار اليوم الحالي لعمود الفئةرحلات لكل يوم ورحلات يوم العطلة ليكون أعمدة وسيلة الإيضاح (سلسلة).

من مخطط الرسم البياني، يمكنك أن ترى أنه خلال العطلات الرسمية يكون عدد رحلات سيارات الأجرة أقل. لا يزال هناك انخفاض كبير واحد غير مفسر يوم 23 يناير. لنتحقق من حالة الطقس في مدينة نيويورك في ذلك اليوم عن طريق الاستعلام عن مجموعة بيانات الطقس:

SELECT
    AVG(windspeed) AS avg_windspeed,
    MIN(windspeed) AS min_windspeed,
    MAX(windspeed) AS max_windspeed,
    AVG(temperature) AS avg_temperature,
    MIN(temperature) AS min_temperature,
    MAX(temperature) AS max_temperature,
    AVG(sealvlpressure) AS avg_sealvlpressure,
    MIN(sealvlpressure) AS min_sealvlpressure,
    MAX(sealvlpressure) AS max_sealvlpressure,
    AVG(precipdepth) AS avg_precipdepth,
    MIN(precipdepth) AS min_precipdepth,
    MAX(precipdepth) AS max_precipdepth,
    AVG(snowdepth) AS avg_snowdepth,
    MIN(snowdepth) AS min_snowdepth,
    MAX(snowdepth) AS max_snowdepth
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [weather]
WHERE countryorregion = 'US' AND CAST([datetime] AS DATE) = '2016-01-23' AND stationname = 'JOHN F KENNEDY INTERNATIONAL AIRPORT'

تشير نتائج الاستعلام إلى حدوث انخفاض في عدد سيارات الأجرة للسبب التالي:

• كانت هناك عاصفة ثلجية في ذلك اليوم في مدينة نيويورك مع تساقط كثيف للثلوج (~ 30 سم).
• كان الجو باردًا (درجة الحرارة كانت أقل من صفر درجة مئوية).
• كان الجو عاصفًا (~ 10 م / ث).

أظهر هذا البرنامج التعليمي كيف يمكن لمحلل البيانات إجراء تحليل البيانات الاستكشافية بسرعة. يمكنك دمج مجموعات بيانات مختلفة باستخدام تجمع SQL بلا خادم وتصور النتائج باستخدام Azure Synapse Studio.

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

معرفة كيفية توصيل تجمع SQL بدون خادم بـ Power BI Desktop وإنشاء التقارير، راجع توصيل تجمع SQL بلا خادم بـ Power BI Desktop وإنشاء التقارير.

لمعرفة كيفية استخدام الجداول الخارجية في تجمع SQL بلا خادم، راجع استخدام الجداول الخارجية مع Synapse SQL