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

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

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

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

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

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

yellow_tripdata_<year>-<month>.csv*

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

يقرأ المثال التالي جميع ملفات بيانات سيارة الأجرة الصفراء في مدينة نيويورك من مجلد 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 من مجلد 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 على نفس البنية (عدد الأعمدة وأنواع البيانات الخاصة بها).

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

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

استخدام بيانات تعريف الملف في استعلامات تجمع SQL بلا خادم