Mappák és több fájl lekérdezése

Ebből a cikkből megtudhatja, hogyan írhat lekérdezést kiszolgáló nélküli SQL-készlet használatával az Azure Synapse Analyticsben.

A kiszolgáló nélküli SQL-készlet támogatja több fájl vagy mappa olvasását helyettesítő karakterek használatával, amelyek hasonlóak a Windowsban használt helyettesítő karakterekhez. Nagyobb rugalmasság azonban rendelkezésre áll, mivel több helyettesítő karakter is engedélyezett.

Előfeltételek

Első lépésként hozzon létre egy adatbázist , ahol végrehajthatja a lekérdezéseket. Ezután inicializálja az objektumokat egy beállítási szkript végrehajtásával az adatbázisban. Ez a beállítási szkript létrehozza az ezekben a mintákban használt adatforrásokat, adatbázis-hatókörű hitelesítő adatokat és külső fájlformátumokat.

A minta lekérdezések követéséhez használja a csv/taxi mappát. A 2016 júliusa és 2018 júniusa közötti NYC Taxi - Yellow Taxi Trip Records adatokat tartalmazza. A csv/taxiban lévő fájlok neve évről hónapra a következő mintával történik:

yellow_tripdata_<year>-<month>.csv*

Az összes fájl olvasása a mappában

Az alábbi példa beolvassa az összes NYC sárga taxi adatfájlt a csv/taxi mappából, majd visszaadja az utasok és utazások teljes számát évente. Az összesítő függvények használatát is megjeleníti.

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

Note

Az egyetlen OPENROWSET fájllal elért összes fájlnak ugyanazzal a struktúrával kell rendelkeznie (az oszlopok számának és adattípusainak).

Fájlok részhalmazának olvasása a mappában

Az alábbi példa beolvassa a 2017-es NYC sárga taxi adatfájlokat a csv/taxi mappából helyettesítő karakterrel, és a teljes viteldíj összegét adja vissza fizetési típusonként.

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;

Note

Az egyetlen OPENROWSET fájllal elért összes fájlnak ugyanazzal a struktúrával kell rendelkeznie (az oszlopok számának és adattípusainak).

A mappában lévő fájlok részhalmazának olvasása több fájlelérési útvonal használatával

Az alábbi példa a 2017-es NYC Sárga Taxi adatfájlokat olvassa be a csv/taxi mappából két fájl elérési útján. Az első a január hónap adatait tartalmazó fájl teljes elérési útját használja, a második pedig helyettesítő karaktert használ az októberi, novemberi és decemberi hónapok olvasásához. Minden elérési út esetében a teljes viteldíj összege kifizetéstípusonként lesz visszaadva.

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;

Note

Az egyetlen OPENROWSET fájllal elért összes fájlnak ugyanazzal a struktúrával kell rendelkeznie (az oszlopok számának és adattípusainak).

Mappák olvasása

A megadott OPENROWSET elérési út egy mappa elérési útja is lehet. A következő szakaszok tartalmazzák ezeket a lekérdezéstípusokat.

Az összes fájl beolvasása adott mappából

A mappában lévő összes fájlt a fájlszintű helyettesítő karakterrel olvashatja el, ahogyan az Az összes fájl beolvasása mappában című témakörben látható. De lekérdezhet egy mappát, és felhasználhatja a mappán belüli összes fájlt.

Ha a megadott elérési út OPENROWSET egy mappára mutat, a rendszer a mappában lévő összes fájlt használja a lekérdezés forrásaként. Az alábbi lekérdezés beolvassa a csv/taxi mappában lévő összes fájlt.

Note

Vegye figyelembe, hogy a lekérdezésben az elérési út végén található a /. Egy mappát jelöl. Ha nincs / megadva, a lekérdezés ehelyett egy taxi nevű fájlt céloz meg.

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

Note

Az egyetlen OPENROWSET fájllal elért összes fájlnak ugyanazzal a struktúrával kell rendelkeznie (az oszlopok számának és adattípusainak).

Az összes fájl beolvasása több mappából

Helyettesítő karakterrel több mappából is olvashat fájlokat. Az alábbi lekérdezés beolvassa az összes fájlt a csv mappában található összes mappából, amelyek neve t és i végződésű.

Note

Vegye figyelembe, hogy a lekérdezésben az elérési út végén található a /. Egy mappát jelöl. Ha nincs / megadva, a lekérdezés inkább a t*i nevű fájlokat célozza meg.

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

Note

Az egyetlen OPENROWSET fájllal elért összes fájlnak ugyanazzal a struktúrával kell rendelkeznie (az oszlopok számának és adattípusainak).

Mivel csak egy mappa felel meg a feltételeknek, a lekérdezés eredménye megegyezik a mappában lévő összes fájl olvasásával.

Traverse folders recursively

A kiszolgáló nélküli SQL-készlet rekurzívan átjárhatja a mappákat, ha az elérési út végén adja meg /** . Az alábbi lekérdezés beolvassa az összes fájlt a csv/taxi mappában található összes mappából és almappából.

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

Note

Az egyetlen OPENROWSET fájllal elért összes fájlnak ugyanazzal a struktúrával kell rendelkeznie (az oszlopok számának és adattípusainak).

Több helyettesítő karakter használata

Több helyettesítő karaktert is használhat különböző elérésiút-szinteken. Például bővíthet egy korábbi lekérdezést, hogy csak 2017-ből származó adatokkal olvassa be a fájlokat az összes olyan mappából, ahol a nevek t-vel kezdődnek, és i-vel végződnek.

Note

Vegye figyelembe, hogy a lekérdezésben az elérési út végén található a /. Egy mappát jelöl. Ha nincs / megadva, a lekérdezés inkább a t*i nevű fájlokat célozza meg. Lekérdezésenként legfeljebb 10 helyettesítő karakter lehet.

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

Note

Az egyetlen OPENROWSET fájllal elért összes fájlnak ugyanazzal a struktúrával kell rendelkeznie (az oszlopok számának és adattípusainak).

Mivel csak egy mappa felel meg a feltételeknek, a lekérdezés eredménye megegyezik a mappában lévő fájlok olvasási részhalmazával, és az összes fájl beolvasása adott mappából. For more complex wildcard usage scenarios, see Query Parquet files.

Következő lépés

Fájlok metaadatainak használata kiszolgáló nélküli SQL-készleteken végrehajtott lekérdezésekben