Parquet-fájlok lekérdezése kiszolgáló nélküli SQL-készlet használatával az Azure Synapse Analyticsben

Ebben a cikkben megtudhatja, hogyan írhat lekérdezést kiszolgáló nélküli SQL-készlet használatával, amely Parquet-fájlokat olvas.

Rövid útmutató – példa

OPENROWSET függvény lehetővé teszi a parquet-fájl tartalmának olvasását a fájl URL-címének megadásával.

Parquet-fájl olvasása

A legegyszerűbben úgy tekintheti PARQUET meg a fájl tartalmát, ha megadja a fájl URL-címét a függvényhez OPENROWSET , és megadja a parquetet FORMAT. Ha a fájl nyilvánosan elérhető, vagy ha a Microsoft Entra-identitása hozzáfér ehhez a fájlhoz, akkor a következő példában látható lekérdezéshez hasonlóan a fájl tartalmát is látnia kell:

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.parquet',
    format = 'parquet') as rows

Győződjön meg arról, hogy hozzáfér ehhez a fájlhoz. Ha a fájl SAS-kulccsal vagy egyéni Azure-identitással van védve, kiszolgálószintű hitelesítő adatokat kell beállítania az SQL-bejelentkezéshez.

Fontos

Győződjön meg arról, hogy UTF-8 adatbázis-rendezést használ (például Latin1_General_100_BIN2_UTF8), mert a PARQUET-fájlok sztringértékei UTF-8 kódolással vannak kódolva. A PARQUET-fájl szövegkódolása és a rendezés közötti eltérés váratlan konverziós hibákat okozhat. Az aktuális adatbázis alapértelmezett rendezése egyszerűen módosítható a következő T-SQL utasítással: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; A rendezésekkel kapcsolatos további információkért lásd a Synapse SQL által támogatott rendezési típusokat.

Ha a Latin1_General_100_BIN2_UTF8 rendezést használja, a többi rendezéshez képest további teljesítménynövelést fog kapni. A Latin1_General_100_BIN2_UTF8 rendezés kompatibilis a parquet sztring rendezési szabályaival. Az SQL-készlet képes kiküszöbölni a parquet-fájlok egyes részeit, amelyek nem tartalmaznak a lekérdezésekben szükséges adatokat (fájl/oszlopszegmens metszése). Ha más rendezéseket használ, a parquet-fájlok összes adata betöltődik a Synapse SQL-be, és a szűrés az SQL-folyamaton belül történik. A Latin1_General_100_BIN2_UTF8 rendezés további teljesítményoptimalizálással rendelkezik, amely csak a parquet és a Cosmos DB esetében működik. A hátránya az, hogy elveszíti a részletes összehasonlítási szabályokat, például a kis- és nagybetűk érzéketlenségét.

Adatforrások használata

Az előző példa a fájl teljes elérési útját használja. Másik lehetőségként létrehozhat egy külső adatforrást a tároló gyökérmappájára mutató hellyel, és használhatja ezt az adatforrást és a függvényben lévő OPENROWSET fájl relatív elérési útját:

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) as rows

Ha egy adatforrás SAS-kulccsal vagy egyéni identitással van védve, az adatforrást adatbázis-hatókörű hitelesítő adatokkal konfigurálhatja.

Explicit módon adja meg a sémát

OPENROWSET lehetővé teszi, hogy explicit módon adja meg, hogy milyen oszlopokat szeretne olvasni a fájlból záradék használatával WITH :

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) with ( date_rep date, cases int, geo_id varchar(6) ) as rows

Fontos

Győződjön meg arról, hogy az UTF-8 rendezést (például Latin1_General_100_BIN2_UTF8) célszerű megadni a záradék összes sztringoszlopához WITH , vagy állítson be valamilyen UTF-8 rendezést adatbázisszinten. A fájl szövegkódolása és a sztringoszlopok rendezése közötti eltérés váratlan konverziós hibákat okozhat. Az aktuális adatbázis alapértelmezett rendezése egyszerűen módosítható a következő T-SQL utasítással: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; A kolumtípusokon egyszerűen beállíthatja a rendezést, például: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8 A rendezésekkel kapcsolatos további információkért lásd a Synapse SQL által támogatott rendezési típusokat.

A következő szakaszokban megtudhatja, hogyan kérdezhet le különböző típusú PARQUET-fájlokat.

Előfeltételek

Első lépésként hozzon létre egy adatbázist egy olyan adatforrással, amely az NYC Yellow Taxi storage-fiókra hivatkozik. Ezután inicializálja az objektumokat az adatbázis telepítőszkriptjének végrehajtásával. 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.

Adathalmaz

Ebben a mintában nyc sárga taxi adatkészletet használunk. A Parquet-fájlokat ugyanúgy kérdezheti le, mint a CSV-fájlokat. Az egyetlen különbség az, hogy a FILEFORMAT paramétert be kell állítani PARQUET. A cikkben szereplő példák a Parquet-fájlok olvasásának sajátosságait mutatják be.

Parquet-fájlok lekérdezése

Parquet-fájlok lekérdezésekor csak az érdekes oszlopokat adhatja meg.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) WITH (
        tpepPickupDateTime DATETIME2,
        passengerCount INT
    ) AS nyc
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Automatikus sémakövetkeztetés

Parquet-fájlok olvasásakor nem kell használnia az OPENROW Standard kiadás T WITH záradékot. Az oszlopnevek és adattípusok automatikusan beolvashatók a Parquet-fájlokból.

Ne feledje, hogy ha egyszerre több fájlt olvas be, a rendszer a sémát, az oszlopneveket és az adattípusokat a tárból érkező első fájlszolgáltatásból fogja kikövetkeztetni. Ez azt jelentheti, hogy a várt oszlopok némelyike hiányzik, mindezt azért, mert a szolgáltatás által a séma meghatározására használt fájl nem tartalmazza ezeket az oszlopokat. A séma explicit megadásához használja az OPENROW Standard kiadás T WITH záradékot.

Az alábbi minta a Parquet-fájlok automatikus sémakövető képességeit mutatja be. A 2018. szeptemberi sorok számát adja vissza séma megadása nélkül.

Megjegyzés:

Parquet-fájlok olvasásakor nem kell oszlopokat megadnia az OPENROW Standard kiadás T WITH záradékban. Ebben az esetben a kiszolgáló nélküli SQL-készlet lekérdezési szolgáltatása metaadatokat használ a Parquet-fájlban, és név szerint köti össze az oszlopokat.

SELECT TOP 10 *
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=9/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) AS nyc

Particionált adatok lekérdezése

Az ebben a mintában megadott adatkészlet külön almappákra van osztva (particionált). A filepath függvénnyel meghatározott partíciókat célozhat meg. Ez a példa 2017 első három hónapjának viteldíjösszegét mutatja be év, hónap és payment_type szerint.

Megjegyzés:

A kiszolgáló nélküli SQL-készlet lekérdezése kompatibilis a Hive/Hadoop particionálási sémával.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=*/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) nyc
WHERE
    nyc.filepath(1) = 2017
    AND nyc.filepath(2) IN (1, 2, 3)
    AND tpepPickupDateTime BETWEEN CAST('1/1/2017' AS datetime) AND CAST('3/31/2017' AS datetime)
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Típusleképezés

Ha parquet típusú megfeleltetést szeretne létrehozni natív SQL-típusra, ellenőrizze a Parquet típusleképezését.

Következő lépések

A következő cikkből megtudhatja, hogyan kérdezheti le a parquet beágyazott típusait.