مجلدات الاستعلام وملفات متعددة

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

يدعم تجمع SQL بلا خادم قراءة ملفات/مجلدات متعددة باستخدام أحرف البدل، والتي تشبه أحرف البدل المستخدمة في نظام التشغيل Windows. ومع ذلك، هناك مرونة أكبر نظرًا إلى السماح بأحرف بدل متعددة.

المتطلبات الأساسية

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

ستستخدم المجلد csv/taxi لمتابعة نماذج الاستعلامات. تحتوي على سيارة أجرة في مدينة نيويورك - بيانات سجلات رحلات سيارات الأجرة الصفراء من يوليو 2016 إلى يونيو 2018. تتم تسمية الملفات في csv/taxi باسم السنة والشهر باستخدام النمط التالي: yellow_tripdata_<year>-<month>.csv

قراءة كافة الملفات في المجلد

المثال أدناه يقرأ جميع ملفات بيانات NYC Yellow Taxi من مجلد csv/taxiويعيد العدد الإجمالي للركاب والرحلات سنويًا. كما يظهر استخدام الوظائف التجميعية.

SELECT 
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/taxi/*.csv',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        pickup_datetime DATETIME2 2, 
        passenger_count INT 4
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

ملاحظة

يجب أن يكون لدى كافة الملفات التي تم الوصول إليها باستخدام OPENROWSET واحد نفس البنية (أي عدد أعمدة وأنواع البيانات الخاصة بها).

قراءة مجموعة فرعية من الملفات في المجلد

يقرأ المثال أدناه ملفات بيانات 2017 NYC Yellow Taxi من مجلد csv/taxiباستخدام حرف بدل ويعيد إجمالي مبلغ الأجرة لكل نوع دفع.

SELECT 
    payment_type,  
    SUM(fare_amount) AS fare_total
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        payment_type INT 10,
        fare_amount FLOAT 11
    ) AS nyc
GROUP BY payment_type
ORDER BY payment_type;

ملاحظة

يجب أن يكون لدى كافة الملفات التي تم الوصول إليها باستخدام OPENROWSET واحد نفس البنية (أي عدد أعمدة وأنواع البيانات الخاصة بها).

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

المثال أدناه يقرأ ملفات بيانات التاكسي الأصفر 2017 من مجلدcsv/taxi باستخدام مساري ملفين، الأولً بالمسار الكامل للملف الذي يحتوي على بيانات من شهر يناير والثاني بقراءة حرف بدل لشهري نوفمبر وديسمبر والذي تعيد إجمالي مبلغ الأجرة لكل نوع دفع.

SELECT 
    payment_type,  
    SUM(fare_amount) AS fare_total
FROM OPENROWSET(
        BULK (
            'csv/taxi/yellow_tripdata_2017-01.csv',
            'csv/taxi/yellow_tripdata_2017-1*.csv'
        ),
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        payment_type INT 10,
        fare_amount FLOAT 11
    ) AS nyc
GROUP BY payment_type
ORDER BY payment_type;

ملاحظة

يجب أن يكون لدى كافة الملفات التي تم الوصول إليها باستخدام OPENROWSET واحد نفس البنية (أي عدد أعمدة وأنواع البيانات الخاصة بها).

قراءة المجلدات

يمكن أن يكون المسار الذي قمت بتوفيره إلى OPENROWSET أيضًا مسارًا إلى مجلد. تتضمن الأقسام التالية أنواع الاستعلام هذه.

اقرأ جميع الملفات من مجلد معين

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

إذا كان المسار المتوفر في OPENROWSET يشير إلى مجلد، فسيتم استخدام جميع الملفات في هذا المجلد كمصدر لاستعلامك. سيقرأ الاستعلام التالي جميع الملفات الموجودة في المجلد csv/taxi.

ملاحظة

لاحظ وجود/في نهاية المسار في الاستعلام أدناه. فإنه يدل على مجلد. إذا تم الحذف /، فسيستهدف الاستعلام ملفًا يسمى تاكسي بدلاً من ذلك.

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/taxi/',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

ملاحظة

يجب أن يكون لدى كافة الملفات التي تم الوصول إليها باستخدام OPENROWSET واحد نفس البنية (أي عدد أعمدة وأنواع البيانات الخاصة بها).

قراءة كافة الملفات من مجلدات متعددة

من الممكن قراءة الملفات من مجلدات متعددة باستخدام حرف بدل. سيقرأ الاستعلام التالي جميع الملفات من كافة المجلدات الموجودة في المجلد csv والتي تبدأ أسماؤها بـ t وتنتهي بـ i.

ملاحظة

لاحظ وجود/في نهاية المسار في الاستعلام أدناه. فإنه يدل على مجلد. إذا تم حذف /، فسيستهدف الاستعلام الملفات المسماة t * i بدلاً من ذلك.

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/t*i/', 
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

ملاحظة

يجب أن يكون لدى كافة الملفات التي تم الوصول إليها باستخدام OPENROWSET واحد نفس البنية (أي عدد أعمدة وأنواع البيانات الخاصة بها).

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

اجتياز مجلدات بشكل متكرر

يمكن لمجمع SQL بلا خادم اجتياز المجلدات بشكل متكرر إذا حددت **/ في نهاية المسار. سيقرأ الاستعلام التالي جميع الملفات من جميع الملفات والمجلدات الفرعية الموجودة في المجلد csv/taxi.

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/taxi/**', 
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

ملاحظة

يجب أن يكون لدى كافة الملفات التي تم الوصول إليها باستخدام OPENROWSET واحد نفس البنية (أي عدد أعمدة وأنواع البيانات الخاصة بها).

أحرف البدل المتعددة

يمكنك استخدام أحرف بدل متعددة على مستويات مختلفة للمسار. على سبيل المثال، يمكنك إثراء الاستعلام السابق لقراءة الملفات التي تحتوي على بيانات 2017 فقط، من جميع المجلدات التي تبدأ الأسماء بـ t وتنتهي بـ i.

ملاحظة

لاحظ وجود/في نهاية المسار في الاستعلام أدناه. فإنه يدل على مجلد. إذا تم حذف /، فسيستهدف الاستعلام الملفات المسماة t * i بدلاً من ذلك. يوجد حد أقصى من 10 أحرف بدل لكل استعلام.

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/t*i/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

ملاحظة

يجب أن يكون لدى كافة الملفات التي تم الوصول إليها باستخدام OPENROWSET واحد نفس البنية (أي عدد أعمدة وأنواع البيانات الخاصة بها).

نظرًا لأن لديك مجلدًا واحدًا فقط يطابق المعايير، فإن نتيجة الاستعلام هي نفس نتيجة اقرأ المجموعة الفرعية من الملفات في المجلد واقرأ جميع الملفات من مجلد معين. يتم تغطية سيناريوهات استخدام أحرف البدل الأكثر تعقيدًا في ملفات الاستعلام Parquet.

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

يمكن العثور على مزيد من المعلومات في مقالة استعلام ملفات محددة.