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

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

في هذا البرنامج التعليمي، سوف تتعلّم:

الوصول إلى تجمع SQL بلا خادم

تأتي كل مساحة عمل مع تجمع SQL بلا خادم تم تكوينه مسبقا لاستخدامه يسمى مضمن. للوصول إليه:

1. افتح مساحة العمل الخاصة بك وحدد مركز Develop .
2. + حدد الزر Add new resource.'
3. حدد البرنامج النصي SQL.

يمكنك استخدام هذا البرنامج النصي لاستكشاف بياناتك دون الحاجة إلى حجز سعة SQL.

في حال لم يكن لديك اشتراك Azure، فأنشئ حساباً مجانيّاً قبل البدء.

الوصول إلى بيانات البرنامج التعليمي

يتم إيواء جميع البيانات التي نستخدمها في هذا البرنامج التعليمي في حساب التخزين azureopendatastorage، الذي يحتفظ بمجموعات بيانات Azure المفتوحة للاستخدام المفتوح في برامج تعليمية مثل هذه. يمكنك تشغيل جميع البرامج النصية كما هي مباشرة من مساحة العمل الخاصة بك طالما أن مساحة العمل الخاصة بك يمكنها الوصول إلى شبكة عامة.

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

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

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

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

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]

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

• سيارة أجرة في مدينة نيويورك
• العطلات الرسمية
• بيانات الطقس

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

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

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

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

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:

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

يمكنك تركيز التحليل على عام واحد، على سبيل المثال، 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