Klasörleri ve birden çok dosyayı sorgulama

Bu makalede, Azure Synapse Analytics'te sunucusuz SQL havuzunu kullanarak sorgu yazmayı öğreneceksiniz.

Sunucusuz SQL havuzu, Windows işletim sisteminde kullanılan joker karakterlere benzer joker karakterler kullanarak birden çok dosya/klasör okumayı destekler. Ancak, birden çok joker karaktere izin verildiğinden daha fazla esneklik sağlanır.

Önkoşullar

İlk adımınız sorguları yürüteceğiniz bir veritabanı oluşturmaktır . Ardından bu veritabanında kurulum betiğini yürüterek nesneleri başlatın. Bu kurulum betiği, bu örneklerde kullanılan veri kaynaklarını, veritabanı kapsamlı kimlik bilgilerini ve dış dosya biçimlerini oluşturur.

Örnek sorguları izlemek için csv/taxi klasörünü kullanacaksınız. Temmuz 2016 ile Haziran 2018 arasında NYC Taxi - Yellow Taxi Trip Records verilerini içerir. Csv/taxi dosyasındaki dosyalar, yıl ve ay adıyla şu desen kullanılarak adlandırılır: yellow_tripdata_<>year-month<>.csv

Klasördeki tüm dosyaları okuma

Aşağıdaki örnek csv/taxi klasöründeki tüm NYC Yellow Taxi veri dosyalarını okur ve yıllık toplam yolcu ve yolculuk sayısını döndürür. Ayrıca toplama işlevlerinin kullanımını da gösterir.

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);

Not

Tek OPENROWSET ile erişilen tüm dosyalar aynı yapıya (sütun sayısı ve veri türleri) sahip olmalıdır.

Klasördeki dosyaların alt kümesini okuma

Aşağıdaki örnekte csv/taxi klasöründeki 2017 NYC Yellow Taxi veri dosyaları joker karakter kullanılarak okunur ve ödeme türü başına toplam ücret tutarı döndürülmektedir.

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;

Not

Tek OPENROWSET ile erişilen tüm dosyalar aynı yapıya (sütun sayısı ve veri türleri) sahip olmalıdır.

Birden çok dosya yolu kullanarak klasördeki dosyaların alt kümesini okuma

Aşağıdaki örnekte csv/taxi klasöründeki 2017 NYC Yellow Taxi veri dosyaları, ilki Ocak ayından tam yolu ve ikinci aya ait verileri içeren dosyanın tam yolunu ve kasım ve aralık aylarını içeren ve ödeme türüne göre toplam ücret tutarını döndüren joker karakter içeren dosya yolunu kullanarak csv/taxi klasöründeki 2017 tarihli sarı taksi veri dosyalarını okur.

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;

Not

Tek OPENROWSET ile erişilen tüm dosyalar aynı yapıya (sütun sayısı ve veri türleri) sahip olmalıdır.

Klasörleri okuma

OPENROWSET'e sağladığınız yol, bir klasörün yolu da olabilir. Aşağıdaki bölümler bu sorgu türlerini içerir.

Belirli bir klasörden tüm dosyaları okuma

Klasördeki tüm dosyaları okuma bölümünde gösterildiği gibi dosya düzeyi joker karakteri kullanarak bir klasördeki tüm dosyaları okuyabilirsiniz. Ancak, bir klasörü sorgulamanın ve bu klasördeki tüm dosyaları kullanmanın bir yolu vardır.

OPENROWSET'te sağlanan yol bir klasörü işaret ederse, bu klasördeki tüm dosyalar sorgunuz için kaynak olarak kullanılır. Aşağıdaki sorgu csv/taxi klasöründeki tüm dosyaları okur.

Not

Aşağıdaki sorguda yolun sonundaki / öğesinin varlığına dikkat edin. Bir klasörü belirtir. / belirtilmezse, sorgu bunun yerine taxi adlı bir dosyayı hedefler.

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);

Not

Tek OPENROWSET ile erişilen tüm dosyalar aynı yapıya (sütun sayısı ve veri türleri) sahip olmalıdır.

Birden çok klasördeki tüm dosyaları okuma

Joker karakter kullanarak birden çok klasördeki dosyaları okumak mümkündür. Aşağıdaki sorgu , csv klasöründe bulunan ve adları t ile başlayan ve i ile biten tüm klasörlerdeki tüm dosyaları okur.

Not

Aşağıdaki sorguda yolun sonundaki / öğesinin varlığına dikkat edin. Bir klasörü belirtir. / belirtilmezse, sorgu bunun yerine t*i adlı dosyaları hedefler.

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);

Not

Tek OPENROWSET ile erişilen tüm dosyalar aynı yapıya (sütun sayısı ve veri türleri) sahip olmalıdır.

Ölçütlere uyan tek bir klasörünüz olduğundan, sorgu sonucu Klasördeki tüm dosyaları oku ile aynıdır.

Klasörleri özyinelemeli olarak çapraz geçiş yapma

Yolun sonunda /** belirtirseniz sunucusuz SQL havuzu klasörlerde yinelemeli olarak geçiş yapabilir. Aşağıdaki sorgu csv/taxi klasöründe bulunan tüm klasörlerdeki ve alt klasörlerdeki tüm dosyaları okur.

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);

Not

Tek OPENROWSET ile erişilen tüm dosyalar aynı yapıya (sütun sayısı ve veri türleri) sahip olmalıdır.

Birden çok joker karakter

Farklı yol düzeylerinde birden çok joker karakter kullanabilirsiniz. Örneğin, adları t ile başlayan ve i ile biten tüm klasörlerden, dosyaları yalnızca 2017 verileriyle okumak için önceki sorguyu zenginleştirebilirsiniz.

Not

Aşağıdaki sorguda yolun sonundaki / öğesinin varlığına dikkat edin. Bir klasörü belirtir. / belirtilmezse, sorgu bunun yerine t*i adlı dosyaları hedefler. Sorgu başına en fazla 10 joker karakter sınırı vardır.

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);

Not

Tek OPENROWSET ile erişilen tüm dosyalar aynı yapıya (sütun sayısı ve veri türleri) sahip olmalıdır.

Ölçütlere uyan tek bir klasörünüz olduğundan, sorgu sonucu Klasördeki dosyaların alt kümesini okuma ve Belirli bir klasördeki tüm dosyaları okuma ile aynıdır. Sorgu Parquet dosyalarında daha karmaşık joker karakter kullanımı senaryoları ele alınmıştır.

Sonraki adımlar

Daha fazla bilgi için bkz. Belirli dosyaları sorgulama makalesi.