Wykonywanie zapytań względem folderów i wielu plików

Z tego artykułu dowiesz się, jak napisać zapytanie przy użyciu bezserwerowej puli SQL w usłudze Azure Synapse Analytics.

Bezserwerowa pula SQL obsługuje odczytywanie wielu plików lub folderów przy użyciu symboli wieloznacznych, które są podobne do symboli wieloznacznych używanych w systemie Windows. Jednak większa elastyczność jest obecna, ponieważ dozwolone są wiele symboli wieloznacznych.

Wymagania wstępne

Pierwszym krokiem jest utworzenie bazy danych , w której można wykonywać zapytania. Następnie zainicjuj obiekty, wykonując skrypt instalacyjny w tej bazie danych. Ten skrypt instalacyjny tworzy źródła danych, poświadczenia o zakresie bazy danych i zewnętrzne formaty plików, które są używane w tych przykładach.

Użyj folderu csv/taxi , aby postępować zgodnie z przykładowymi zapytaniami. Zawiera dane NYC Taxi - Yellow Taxi Trip Records od lipca 2016 r. do czerwca 2018 r. Pliki w pliku csv/taxi są nazwane po roku i miesiącu przy użyciu następującego wzorca:

yellow_tripdata_<year>-<month>.csv*

Odczytywanie wszystkich plików w folderze

Poniższy przykład odczytuje wszystkie pliki danych żółtych taksówek NYC z folderu csv/taxi , a następnie zwraca łączną liczbę pasażerów i przejazdów rocznie. Pokazuje również użycie funkcji agregujących.

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

Uwaga

Wszystkie pliki, do których uzyskujesz dostęp za pomocą pojedynczego pliku OPENROWSET , muszą mieć taką samą strukturę (liczbę kolumn i ich typów danych).

Odczytywanie podzbioru plików w folderze

Poniższy przykład odczytuje pliki danych żółtych taksówek 2017 NYC z folderu csv/taxi przy użyciu symbolu wieloznakowego i zwraca łączną kwotę taryfy za typ płatności.

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;

Uwaga

Wszystkie pliki, do których uzyskujesz dostęp za pomocą pojedynczego pliku OPENROWSET , muszą mieć taką samą strukturę (liczbę kolumn i ich typów danych).

Odczytywanie podzbioru plików w folderze przy użyciu wielu ścieżek plików

Poniższy przykład odczytuje pliki danych żółtych taksówek 2017 NYC z folderu csv/taxi przy użyciu dwóch ścieżek plików. Pierwszy używa pełnej ścieżki do pliku zawierającego dane z miesiąca stycznia, a drugi używa symbolu wieloznacznego do odczytania miesięcy października, listopada i grudnia. Dla każdej ścieżki zwracana jest łączna kwota taryfy za typ płatności.

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;

Uwaga

Wszystkie pliki, do których uzyskujesz dostęp za pomocą pojedynczego pliku OPENROWSET , muszą mieć taką samą strukturę (liczbę kolumn i ich typów danych).

Odczytywanie folderów

Ścieżka, którą podajesz OPENROWSET , może być również ścieżką do folderu. Poniższe sekcje zawierają te typy zapytań.

Odczytywanie wszystkich plików z określonego folderu

Wszystkie pliki w folderze można odczytać przy użyciu symbolu wieloznakowego na poziomie pliku, jak pokazano w temacie Odczyt wszystkich plików w folderze. Jednak istnieje sposób wykonywania zapytań o folder i korzystania ze wszystkich plików w tym folderze.

Jeśli ścieżka podana w OPENROWSET punktach do folderu, wszystkie pliki w tym folderze są używane jako źródło zapytania. Poniższe zapytanie odczytuje wszystkie pliki w folderze csv/taxi .

Uwaga

Zwróć uwagę na istnienie / ścieżki na końcu ścieżki w zapytaniu. Oznacza folder. / Jeśli obiekt zostanie pominięty, zapytanie dotyczy pliku o nazwie taxi zamiast tego.

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

Uwaga

Wszystkie pliki, do których uzyskujesz dostęp za pomocą pojedynczego pliku OPENROWSET , muszą mieć taką samą strukturę (liczbę kolumn i ich typów danych).

Odczytywanie wszystkich plików z wielu folderów

Można odczytywać pliki z wielu folderów przy użyciu symbolu wieloznakowego. Poniższe zapytanie odczytuje wszystkie pliki ze wszystkich folderów znajdujących się w folderze csv , które mają nazwy rozpoczynające się od t i kończące się na i.

Uwaga

Zwróć uwagę na istnienie / ścieżki na końcu ścieżki w zapytaniu. Oznacza folder. / Jeśli obiekt zostanie pominięty, zapytanie dotyczy plików o nazwie t*i zamiast tego.

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

Uwaga

Wszystkie pliki, do których uzyskujesz dostęp za pomocą pojedynczego pliku OPENROWSET , muszą mieć taką samą strukturę (liczbę kolumn i ich typów danych).

Ponieważ masz tylko jeden folder, który spełnia kryteria, wynik zapytania jest taki sam jak Odczyt wszystkich plików w folderze.

Przechodzenie folderów rekursywnie

Bezserwerowa pula SQL może rekursywnie przechodzić przez foldery, jeśli zostanie określona /** na końcu ścieżki. Poniższe zapytanie odczytuje wszystkie pliki ze wszystkich folderów i podfolderów znajdujących się w folderze 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);

Uwaga

Wszystkie pliki, do których uzyskujesz dostęp za pomocą pojedynczego pliku OPENROWSET , muszą mieć taką samą strukturę (liczbę kolumn i ich typów danych).

Używanie wielu symboli wieloznacznych

Można użyć wielu symboli wieloznacznych na różnych poziomach ścieżki. Można na przykład wzbogacić poprzednie zapytanie o odczytywanie plików tylko z danymi z 2017 r., ze wszystkich folderów, w których nazwy zaczynają się od t i kończą się na i.

Uwaga

Zwróć uwagę na istnienie / ścieżki na końcu ścieżki w zapytaniu. Oznacza folder. / Jeśli obiekt zostanie pominięty, zapytanie dotyczy plików o nazwie t*i zamiast tego. Istnieje maksymalny limit 10 symboli wieloznacznych na zapytanie.

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

Uwaga

Wszystkie pliki, do których uzyskujesz dostęp za pomocą pojedynczego pliku OPENROWSET , muszą mieć taką samą strukturę (liczbę kolumn i ich typów danych).

Ponieważ masz tylko jeden folder, który spełnia kryteria, wynik zapytania jest taki sam jak Odczyt podzestaw plików w folderze i Odczyt wszystkich plików z określonego folderu. Aby uzyskać bardziej złożone scenariusze użycia symboli wieloznacznych, zobacz Query Parquet files (Wykonywanie zapytań w plikach Parquet).

Następny krok

Używanie metadanych plików w zapytaniach bezserwerowej puli SQL