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

W tym artykule 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/folderów przy użyciu symboli wieloznacznych, które są podobne do symboli wieloznacznych używanych w systemie operacyjnym Windows. Jednak większa elastyczność jest obecna, ponieważ dozwolone jest wiele symboli wieloznacznych.

Wymagania wstępne

Pierwszym krokiem jest utworzenie bazy danych , w której będą wykonywane zapytania. Następnie zainicjuj obiekty, wykonując skrypt instalacji w tej bazie danych. Ten skrypt instalacyjny utworzy ź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żyjesz 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_< rok-miesiąc<>>.csv

Odczytywanie wszystkich plików w folderze

Poniższy przykład odczytuje wszystkie pliki danych żółtych taksówek NYC z folderu csv/taxi i zwraca całkowitą 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);

Odczytywanie podzestawu plików w folderze

Poniższy przykład odczytuje pliki danych żółtych taksówek 2017 NYC z folderu csv/taxi przy użyciu symbolu wieloznacznych 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;

Odczytywanie podzestawu 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 2 ścieżek plików pierwszy z pełną ścieżką do pliku zawierającego dane z miesiąca stycznia i drugiego z symbolem wieloznaczny odczyt miesięcy listopada i grudnia, który 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-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;

Odczytywanie folderów

Ścieżka, którą podajesz w pliku 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 wieloznacznych na poziomie pliku, jak pokazano w sekcji Odczytaj wszystkie pliki 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 pliku OPENROWSET wskazuje folder, wszystkie pliki w tym folderze będą używane jako źródło zapytania. Następujące zapytanie odczytuje wszystkie pliki 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);

Odczytywanie wszystkich plików z wielu folderów

Można odczytywać pliki z wielu folderów przy użyciu symbolu wieloznacznych. 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.

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

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 cyklicznie przechodzić przez foldery, jeśli określisz /** na końcu ścieżki. Następujące 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);

Wiele symboli wieloznacznych

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

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

Ponieważ masz tylko jeden folder, który spełnia kryteria, wynik zapytania jest taki sam jak odczyt podzestawu plików w folderze i Odczyt wszystkich plików z określonego folderu. Bardziej złożone scenariusze użycia symboli wieloznacznych są omówione w plikach Query Parquet.

Następne kroki

Więcej informacji można znaleźć w artykule Query specific files (Zapytania dotyczące plików ).