Nézetek létrehozása és használata kiszolgáló nélküli SQL-készlet használatával az Azure Synapse Analyticsben

Ebben a szakaszban megtudhatja, hogyan hozhat létre és használhat nézeteket kiszolgáló nélküli SQL-készlet-lekérdezések burkolásához. A nézetek lehetővé teszik a lekérdezések újbóli felhasználását. A nézetekre akkor is szükség van, ha olyan eszközöket szeretne használni, mint a Power BI, a kiszolgáló nélküli SQL-készlettel együtt.

Előfeltételek

Első lépésként hozzon létre egy adatbázist, amelyben létrejön a nézet, és inicializálja a hitelesítéshez szükséges objektumokat az Azure Storage-ban az adatbázis telepítési szkriptjének végrehajtásával. A cikkben szereplő összes lekérdezés a mintaadatbázison lesz végrehajtva.

Külső adatok nézetei

Ugyanúgy hozhat létre nézeteket, mint a hagyományos SQL Server-nézeteket. Az alábbi lekérdezés population.csv fájlt beolvasó nézetet hoz létre.

USE [mydbname];
GO

DROP VIEW IF EXISTS populationView;
GO

CREATE VIEW populationView AS
SELECT * 
FROM OPENROWSET(
        BULK 'csv/population/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', 
        FIELDTERMINATOR =',', 
        ROWTERMINATOR = '\n'
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
) AS [r];

A nézet a EXTERNAL DATA SOURCE tár gyökér URL-címével rendelkezik, DATA_SOURCE és relatív fájlelérési utat ad hozzá a fájlokhoz.

Delta Lake-nézetek

Ha a Delta Lake-mappa tetején hozza létre a nézeteket, a beállítás után BULK meg kell adnia a gyökérmappa helyét a fájl elérési útjának megadása helyett.

A OPENROWSET Delta Lake mappából adatokat beolvasó függvény megvizsgálja a mappa struktúráját, és automatikusan azonosítja a fájlhelyeket.

create or alter view CovidDeltaLake
as
select *
from openrowset(
           bulk 'covid',
           data_source = 'DeltaLakeStorage',
           format = 'delta'
    ) with (
           date_rep date,
           cases int,
           geo_id varchar(6)
           ) as rows

További információkért tekintse át a Synapse kiszolgáló nélküli SQL-készlet önsegítő oldalát és az Azure Synapse Analytics ismert problémáit.

Particionált nézetek

Ha olyan fájlkészlettel rendelkezik, amely a hierarchikus mappastruktúrában van particionálva, a partíciómintát a fájl elérési útján található helyettesítő karakterek használatával írhatja le. A függvény használatával FILEPATH particionálási oszlopokként elérhetővé teheti a mappa elérési útjának egyes részeit.

CREATE VIEW TaxiView
AS SELECT *, nyc.filepath(1) AS [year], nyc.filepath(2) AS [month]
FROM
    OPENROWSET(
        BULK 'parquet/taxi/year=*/month=*/*.parquet',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT='PARQUET'
    ) AS nyc

A particionált nézetek a particionálási oszlopok szűrőivel végzett lekérdezések során a partíciók eltávolításával javíthatják a lekérdezések teljesítményét. Azonban nem minden lekérdezés támogatja a partíciók eltávolítását, ezért fontos néhány ajánlott eljárást követni.

A partíciók eltávolításának biztosítása érdekében kerülje az albekérdezések használatát a szűrőkben, mivel ezek zavarhatják a partíciók eltávolításának képességét. Ehelyett adja át az al lekérdezés eredményét változóként a szűrőnek.

HA JOIN-eket használ SQL-lekérdezésekben, deklarálja a szűrő predikátumot NVARCHAR-ként a lekérdezésterv összetettségének csökkentése és a megfelelő partícióelivétel valószínűségének növelése érdekében. A partícióoszlopok általában NVARCHAR(1024) értékkel vannak kikövetkeztetve, így az azonos típusú predikátum használata elkerüli az implicit leadások szükségességét, ami növelheti a lekérdezésterv összetettségét.

Delta Lake particionált nézetek

Ha a Delta Lake Storage tetején hozza létre a particionált nézeteket, csak egy legfelső szintű Delta Lake-mappát adhat meg, és nem kell explicit módon elérhetővé tennie a particionálási oszlopokat a FILEPATH függvény használatával:

CREATE OR ALTER VIEW YellowTaxiView
AS SELECT *
FROM  
    OPENROWSET(
        BULK 'yellow',
        DATA_SOURCE = 'DeltaLakeStorage',
        FORMAT='DELTA'
    ) nyc

A OPENROWSET függvény megvizsgálja a mögöttes Delta Lake-mappa szerkezetét, és automatikusan azonosítja és elérhetővé teszi a particionálási oszlopokat. A particionálás megszüntetése automatikusan megtörténik, ha a particionálási oszlopot egy WHERE lekérdezés záradékába helyezi.

A függvényben (yellowebben a OPENROWSET példában) az adatforrásban DeltaLakeStorage definiált URI-val LOCATION összefűzött mappanévnek a delta lake gyökérmappára kell hivatkoznia, amely egy úgynevezett _delta_logalmappát tartalmaz.

További információkért tekintse át a Synapse kiszolgáló nélküli SQL-készlet önsegítő oldalát és az Azure Synapse Analytics ismert problémáit.

JSON-nézetek

A nézetek akkor jó választás, ha további feldolgozást kell végeznie a fájlokból lekért eredményhalmazon felül. Ilyen lehet például a JSON-fájlok elemzése, ahol a JSON-függvényeket kell alkalmaznunk a JSON-dokumentumok értékeinek kinyeréséhez:

CREATE OR ALTER VIEW CovidCases
AS 
select
    *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
    cross apply openjson (doc)
        with (  date_rep datetime2,
                cases int,
                fatal int '$.deaths',
                country varchar(100) '$.countries_and_territories')

A OPENJSON függvény soronként egy JSON-dokumentumot tartalmazó JSONL-fájl minden sorát szöveges formátumban elemzi.

Azure Cosmos DB-nézetek tárolókon

A nézetek az Azure Cosmos DB-tárolók tetején hozhatók létre, ha az Azure Cosmos DB elemzési tároló engedélyezve van a tárolón. A nézet részeként hozzá kell adni az Azure Cosmos DB-fiók nevét, adatbázisnevét és tárolónevét, és az írásvédett hozzáférési kulcsot az adatbázis hatókörébe kell helyezni, amelyre a nézet hivatkozik.

Ez a példaszkript egy olyan adatbázist és tárolót használ, amelyet az alábbi utasítások követésével állíthat be.

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'access-key';
GO
CREATE OR ALTER VIEW Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=your-cosmosdb;Database=covid',
      OBJECT = 'Ecdc',
      CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

További információ: Azure Cosmos DB-adatok lekérdezése kiszolgáló nélküli SQL-készlettel az Azure Synapse Linkben.

Nézet használata

A lekérdezésekben ugyanúgy használhat nézeteket, mint az SQL Server-lekérdezésekben.

Az alábbi lekérdezés bemutatja a Nézet létrehozása nézetben létrehozott population_csv nézet használatát. 2019-ben csökkenő sorrendben adja vissza az ország-/régióneveket.

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationView
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

A nézet lekérdezése során hibák vagy váratlan eredmények léphetnek fel. Ez valószínűleg azt jelenti, hogy a nézet a módosított vagy már nem létező oszlopokra vagy objektumokra hivatkozik. Manuálisan kell módosítania a nézetdefiníciót, hogy igazodjon az alapul szolgáló sémamódosításokhoz.

Kapcsolódó tartalom

A különböző fájltípusok lekérdezéséről további információt az egyetlen CSV-fájl lekérdezése, a Lekérdezés parquet-fájlok és a JSON-fájlok lekérdezése című cikkben talál.