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/mappa olvasását helyettesítő karakterek használatával, amelyek hasonlóak a Windows operációs rendszerben használt helyettesítő karakterekhez. Ugyanakkor nagyobb rugalmasság érhető el, mivel több helyettesítő karakter is engedélyezett.

Előfeltételek

Első lépésként hozzon létre egy adatbázist , amelyben végrehajtja a lekérdezéseket. Ezután inicializálja az objektumokat úgy, hogy telepítőszkriptet futtat az adatbázison. 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 csv/taxi mappát fogja használni a mintalekérdezések követéséhez. 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 év és hónap után a következő mintával történik: yellow_tripdata_<year-month<>>.csv

A mappában lévő összes fájl olvasása

Az alábbi példa beolvassa az összes NYC Yellow Taxi adatfájlt a csv/taxi mappából, és visszaadja az utasok és az utazások teljes számát évente. Emellett 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);

Megjegyzés

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

Fájlok olvasási részhalmaza a mappában

Az alábbi példa beolvassa a csv/taxi mappából a 2017-es NYC Yellow Taxi adatfájlokat helyettesítő karakterrel, és a teljes viteldíj összegét adja vissza a 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;

Megjegyzés

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

A mappában lévő fájlok részhalmazának olvasása több fájlútvonallal

Az alábbi példa a csv/taxi mappából olvassa be a 2017-es NYC Yellow Taxi adatfájlokat 2 fájlútvonallal, elsőként a januári adatokat tartalmazó fájl teljes elérési útjával, a második pedig a novemberi és decemberi helyettesítő karakteres olvasási hónapokkal, amely 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-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;

Megjegyzés

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

Mappák olvasása

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

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

A mappában lévő összes fájlt a fájlszintű helyettesítő karakterrel olvashatja el, ahogy 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 az abban a mappában lévő összes fájlt.

Ha az OPENROWSET-ben megadott elérési út egy mappára mutat, a rendszer az adott mappában lévő összes fájlt használja a lekérdezés forrásaként. A következő lekérdezés beolvassa a csv/taxi mappában lévő összes fájlt.

Megjegyzés

Jegyezze fel az alábbi lekérdezésben az elérési út végén található / értékét. Egy mappát jelöl. Ha a /értéke 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);

Megjegyzés

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

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

Helyettesítő karakterrel több mappából is olvashat fájlokat. A következő lekérdezés beolvassa a csv mappában található összes olyan fájlt, amelynek neve t-vel kezdődik és i végződésű.

Megjegyzés

Jegyezze fel az alábbi lekérdezésben az elérési út végén található / értékét. Egy mappát jelöl. Ha a / hiányzik, a lekérdezés 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);

Megjegyzés

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

Mivel csak egy olyan mappája van, amely megfelel a feltételeknek, a lekérdezés eredménye megegyezik a Mappában lévő összes fájl olvasása lehetőségével.

Rekurzív mappák átjárása

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

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

Megjegyzés

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

Több helyettesítő karakter

Több helyettesítő karaktert is használhat különböző elérési utak szintjén. Bővítheti például a korábbi lekérdezést, hogy csak a 2017-ben tárolt adatokkal olvassa be a fájlokat az összes olyan mappából, amelynek a neve t-vel kezdődik, és i-vel végződik.

Megjegyzés

Jegyezze fel az alábbi lekérdezésben az elérési út végén található / értékét. Egy mappát jelöl. Ha a / hiányzik, a lekérdezés 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);

Megjegyzés

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

Mivel csak egy olyan mappája van, amely megfelel 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 olvasása adott mappából. Az összetettebb helyettesítő karakterhasználati forgatókönyveket a Lekérdezési parquet-fájlok ismertetik.

Következő lépések

További információt a Lekérdezésspecifikus fájlok című cikkben talál.