Oktatóanyag: Logikai adattárház létrehozása kiszolgáló nélküli SQL-készlettel

Tip

Microsoft Fabric Data Warehouse egy nagyvállalati szintű relációs raktár egy Data Lake-alaprendszeren, jövőre kész architektúrával, beépített AI-vel és új funkciókkal. Ha még nem ismerkedik adattárházzal, kezdje a Fabric Data Warehouse. A meglévő dedikált SQL-készlet számítási feladatai frissíthetők Fabric az adatelemzés, a valós idejű elemzés és a jelentéskészítés új képességeinek eléréséhez.

Ebben az oktatóanyagban megtudhatja, hogyan hozhat létre logikai adattárházat (LDW) az Azure Storage és az Azure Cosmos DB fölé.

Az LDW egy relációs réteg, amely olyan Azure-adatforrásokra épül, mint az Azure Data Lake Storage (ADLS), az Azure Cosmos DB elemzési tárolója vagy az Azure Blob Storage.

LDW-adatbázis létrehozása

Létre kell hoznia egy egyéni adatbázist, amelyben a külső adatforrásokra hivatkozó külső táblákat és nézeteket fogja tárolni.

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

Ez a rendezés optimális teljesítményt nyújt a Parquet és az Azure Cosmos DB olvasása közben. Ha nem szeretné megadni az adatbázis-rendezést, győződjön meg arról, hogy ezt a rendezést az oszlopdefinícióban adja meg.

Adatforrások és formátumok konfigurálása

Első lépésként konfigurálnia kell az adatforrást, és meg kell adnia a távolról tárolt adatok fájlformátumát.

Adatforrás létrehozása

Az adatforrások kapcsolati sztring adatokat jelölnek, amelyek az adatok elhelyezésének helyét és az adatforráson való hitelesítés módját írják le.

A nyilvános ECDC COVID 19 Azure Open Data Setre hivatkozó adatforrásdefiníció egy példája a következő példában látható:

CREATE EXTERNAL DATA SOURCE ecdc_cases WITH (
    LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/'
);

A hívó hitelesítő adatok nélkül is hozzáférhet az adatforráshoz, ha az adatforrás tulajdonosa engedélyezte a névtelen hozzáférést, vagy explicit hozzáférést ad a hívó Microsoft Entra-identitásához.

Explicit módon definiálhat egy egyéni hitelesítő adatot, amelyet a külső adatforrás adatainak elérésekor fog használni.

Előfeltételként létre kell hoznia egy főkulcsot az adatbázisban:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Setup you password - you need to create master key only once';

A következő külső adatforrásban a Synapse SQL-készletnek a munkaterület felügyelt identitását kell használnia a tárolóban lévő adatok eléréséhez.

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
WITH IDENTITY = 'Managed Identity';
GO
CREATE EXTERNAL DATA SOURCE ecdc_cases WITH (
    LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/',
    CREDENTIAL = WorkspaceIdentity
);

Az Azure Cosmos DB elemzési tár eléréséhez meg kell határoznia egy írásvédett Azure Cosmos DB-fiókkulcsot tartalmazó hitelesítő adatot.

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = '<yourcosmosdbaccountkey>';

A Synapse-rendszergazdai szerepkörrel rendelkező felhasználók ezeket a hitelesítő adatokat használhatják az Azure Data Lake Storage vagy az Azure Cosmos DB elemzési tár eléréséhez. Ha alacsony jogosultsági szintű felhasználókkal rendelkezik, és nem rendelkeznek Synapse-rendszergazdai szerepkörrel, explicit engedélyt kell adnia nekik az adatbázis hatókörébe tartozó hitelesítő adatokra való hivatkozáshoz:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::WorkspaceIdentity TO <user>
GO
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::MyCosmosDbAccountCredential TO <user>
GO

További részletekért tekintse meg a DATABASE SCOPED CREDENTIAL engedélyeinek megadására vonatkozó oldalt.

Külső fájlformátumok definiálása

A külső fájlformátumok határozzák meg a külső adatforráson tárolt fájlok struktúráját. Parquet- és CSV-külső fájlformátumokat definiálhat:

CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT CsvFormat WITH (  FORMAT_TYPE = DELIMITEDTEXT );

További információ: Külső táblák használata Synapse SQL-lel és CREATE EXTERNAL FILE FORMAT a CSV- vagy Parquet-fájlok formátumának leírásához.

Fedezze fel az adatokat

Az adatforrások beállítása után a OPENROWSET függvény segítségével feltárhatja az adatokat. Az OPENROWSET függvény beolvassa egy távoli adatforrás (például fájl) tartalmát, és sorhalmazként adja vissza a tartalmat.

select top 10  *
from openrowset(bulk 'latest/ecdc_cases.parquet',
                data_source = 'ecdc_cases',
                format='parquet') as a

A OPENROWSET függvény információkat ad a külső fájlok vagy tárolók oszlopairól, és lehetővé teszi a külső táblák és nézetek sémájának definiálását.

Külső táblák létrehozása az Azure Storage-ban

A séma felfedezése után külső táblákat és nézeteket hozhat létre a külső adatforrások tetején. Az ajánlott eljárás a táblák és nézetek adatbázis-sémákban való rendszerezése. A következő lekérdezésben létrehozhat egy sémát, amelyben az ECDC COVID-adatkészlethez hozzáférő összes objektumot az Azure Data Lake Storage-ban helyezi el:

create schema ecdc_adls;

Az adatbázissémák hasznosak az objektumok csoportosításához és a sémánkénti engedélyek meghatározásához.

A sémák definiálása után létrehozhat külső táblákat, amelyek hivatkoznak a fájlokra. Az alábbi külső táblázat az Azure Storage-ban elhelyezett ECDC COVID-parquet-fájlra hivatkozik:

create external table ecdc_adls.cases (
    date_rep                   date,
    day                        smallint,
    month                      smallint,
    year                       smallint,
    cases                      smallint,
    deaths                     smallint,
    countries_and_territories  varchar(256),
    geo_id                     varchar(60),
    country_territory_code     varchar(16),
    pop_data_2018              int,
    continent_exp              varchar(32),
    load_date                  datetime2(7),
    iso_country                varchar(16)
) with (
    data_source= ecdc_cases,
    location = 'latest/ecdc_cases.parquet',
    file_format = ParquetFormat
);

Győződjön meg arról, hogy a lehető legkisebb sztring- és számoszloptípusokat használja a lekérdezések teljesítményének optimalizálásához.

Nézetek létrehozása az Azure Cosmos DB-ben

Alternatívaként a külső táblákkal szemben, létrehozhatsz nézeteket a külső adataidra.

Az előző példában bemutatott táblákhoz hasonlóan a nézeteket külön sémákba kell helyeznie:

create schema ecdc_cosmosdb;

Most létrehozhat egy nézetet a sémában, amely egy Azure Cosmos DB-tárolóra hivatkozik:

CREATE OR ALTER VIEW ecdc_cosmosdb.Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=yourcosmosdbaccount;Database=covid',
      OBJECT = 'Ecdc',
      CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) WITH
     ( date_rep varchar(20), 
       cases bigint,
       geo_id varchar(6) 
     ) as rows

A teljesítmény optimalizálásához a sémadefinícióban a lehető legkisebb típusokat WITH kell használnia.

Feljegyzés

Az Azure Cosmos DB-fiókkulcsot egy külön hitelesítő adatban kell elhelyeznie, majd a OPENROWSET függvényből hivatkoznia kell erre a hitelesítő adatra. Ne tartsa meg a fiókkulcsot a nézetdefinícióban.

Hozzáférés és engedélyek

Utolsó lépésként olyan adatbázis-felhasználókat kell létrehoznia, amelyeknek hozzá kell férnie az LDW-hez, és engedélyeket kell adni számukra a külső táblákból és nézetekből származó adatok kiválasztásához. A következő szkriptben láthatja, hogyan adhat hozzá egy új felhasználót, aki a Microsoft Entra-identitással lesz hitelesítve:

CREATE USER [jovan@contoso.com] FROM EXTERNAL PROVIDER;
GO

A Microsoft Entra-tagok helyett olyan SQL-tagokat hozhat létre, amelyek a bejelentkezési névvel és jelszóval hitelesíthetők.

CREATE LOGIN [jovan] WITH PASSWORD = 'My Very strong Password ! 1234';
CREATE USER [jovan] FROM LOGIN [jovan];

Mindkét esetben engedélyeket rendelhet a felhasználókhoz.

DENY ADMINISTER DATABASE BULK OPERATIONS TO [jovan@contoso.com]
GO
GRANT SELECT ON SCHEMA::ecdc_adls TO [jovan@contoso.com]
GO
GRANT SELECT ON OBJECT::ecdc_cosmosDB.cases TO [jovan@contoso.com]
GO
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::MyCosmosDbAccountCredential TO [jovan@contoso.com]
GO

A biztonsági szabályok a biztonsági szabályzatoktól függenek. Néhány általános irányelv:

  • Tiltsa le ADMINISTER DATABASE BULK OPERATIONS az új felhasználók engedélyét, mert csak az Ön által előkészített külső táblák és nézetek használatával olvashatnak adatokat.
  • Csak azokra a táblákra adjon SELECT engedélyt, amelyeket egy felhasználónak használnia kell.
  • Ha a nézetek segítségével biztosít hozzáférést az adatokhoz, engedélyt kell adnia REFERENCES számára, amely hitelesítő adat a külső adatforrás elérésére szolgál.

Ez a felhasználó minimális engedélyekkel rendelkezik a külső adatok lekérdezéséhez. Ha olyan power-felhasználót szeretne létrehozni, aki beállíthat engedélyeket, külső táblákat és nézeteket, engedélyt adhat CONTROL a felhasználónak:

GRANT CONTROL TO [jovan@contoso.com]

Szerepköralapú biztonság

Ahelyett, hogy engedélyeket rendel az egyes felhasználókhoz, célszerű szerepkörökbe rendezni a felhasználókat, és szerepkörszinten kezelni az engedélyeket. Az alábbi kódminta egy új szerepkört hoz létre, amely a COVID-19-esetek elemzésére jogosult személyeket jelöli, és három felhasználót ad hozzá ehhez a szerepkörhöz:

CREATE ROLE CovidAnalyst;

ALTER ROLE CovidAnalyst ADD MEMBER [jovan@contoso.com];
ALTER ROLE CovidAnalyst ADD MEMBER [milan@contoso.com];
ALTER ROLE CovidAnalyst ADD MEMBER [petar@contoso.com];

Az engedélyeket hozzárendelheti a csoporthoz tartozó összes felhasználóhoz:

GRANT SELECT ON SCHEMA::ecdc_cosmosdb TO [CovidAnalyst];
GO
DENY SELECT ON SCHEMA::ecdc_adls TO [CovidAnalyst];
GO
DENY ADMINISTER DATABASE BULK OPERATIONS TO [CovidAnalyst];

Ez a szerepköralapú biztonsági hozzáférés-vezérlés leegyszerűsítheti a biztonsági szabályok kezelését.

Következő lépések

  • Last updated on