Share via

Kurz: Vytvoření logického datového skladu s bezserverovým fondem SQL

  • Článek

V tomto kurzu se dozvíte, jak vytvořit logický datový sklad (LDW) nad úložištěm Azure a službou Azure Cosmos DB.

LDW je relační vrstva založená na zdrojích dat Azure, jako je Azure Data Lake Storage (ADLS), analytické úložiště Azure Cosmos DB nebo Azure Blob Storage.

Vytvoření databáze LDW

Potřebujete vytvořit vlastní databázi, do které budete ukládat externí tabulky a zobrazení odkazující na externí zdroje dat.

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

Tato kolace zajistí optimální výkon při čtení Parquet a Azure Cosmos DB. Pokud nechcete zadávat kolaci databáze, ujistěte se, že tuto kolaci zadáte v definici sloupce.

Konfigurace zdrojů a formátů dat

Jako první krok je potřeba nakonfigurovat zdroj dat a zadat formát souboru vzdáleně uložených dat.

Vytvoření zdroje dat

Zdroje dat představují připojovací řetězec informace, které popisují umístění dat a způsob ověření ve zdroji dat.

Příklad definice zdroje dat, která odkazuje na veřejnou sadu dat ECDC COVID 19 Azure Open Data Set , je znázorněna v následujícím příkladu:

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

Volající může přistupovat ke zdroji dat bez přihlašovacích údajů, pokud vlastník zdroje dat povolil anonymní přístup nebo dal explicitní přístup k identitě Microsoft Entra volajícího.

Můžete explicitně definovat vlastní přihlašovací údaje, které se použijí při přístupu k datům externího zdroje dat.

Předpokladem je vytvoření hlavního klíče v databázi:

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

V následujícím externím zdroji dat by fond Synapse SQL měl pro přístup k datům v úložišti používat spravovanou identitu pracovního prostoru.

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

Pokud chcete získat přístup k analytickému úložišti Azure Cosmos DB, musíte definovat přihlašovací údaje obsahující klíč účtu služby Azure Cosmos DB jen pro čtení.

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';

Každý uživatel s rolí Synapse Správa istrator může tyto přihlašovací údaje použít pro přístup ke službě Azure Data Lake Storage nebo analytickému úložišti Azure Cosmos DB. Pokud máte uživatele s nízkou úrovní oprávnění, kteří nemají roli Synapse Správa istrator, budete jim muset udělit explicitní oprávnění pro odkaz na tyto přihlašovací údaje s vymezeným oborem databáze:

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

Další podrobnosti najdete na stránce oprávnění K UDĚLENÍ OPRÁVNĚNÍ CREDENTIAL OBORU DATABÁZE.

Definování formátů externích souborů

Formáty externích souborů definují strukturu souborů uložených v externím zdroji dat. Můžete definovat formáty externích souborů Parquet a CSV:

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

Další informace najdete v tématu Použití externích tabulek se službou Synapse SQL a CREATE EXTERNAL FILE FORMAT k popisu formátu SOUBORŮ CSV nebo Parquet.

Prozkoumání dat

Jakmile nastavíte zdroje dat, můžete pomocí OPENROWSET funkce prozkoumat data. Funkce OPENROWSET čte obsah vzdáleného zdroje dat (například souboru) a vrací obsah jako sadu řádků.

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

Funkce OPENROWSET vám poskytne informace o sloupcích v externích souborech nebo kontejnerech a umožní vám definovat schéma externích tabulek a zobrazení.

Vytvoření externích tabulek v úložišti Azure

Jakmile zjistíte schéma, můžete vytvořit externí tabulky a zobrazení nad externími zdroji dat. Osvědčeným postupem je uspořádat tabulky a zobrazení ve schématech databází. V následujícím dotazu můžete vytvořit schéma, ve kterém umístíte všechny objekty, které přistupují k datové sadě ECDC COVID ve službě Azure Data Lake Storage:

create schema ecdc_adls;

Schémata databáze jsou užitečná pro seskupení objektů a definování oprávnění na schéma.

Jakmile definujete schémata, můžete vytvořit externí tabulky, které odkazují na soubory. Následující externí tabulka odkazuje na soubor ECDC COVID parquet umístěný v úložišti Azure:

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

Ujistěte se, že používáte nejmenší možné typy pro řetězcové a číselné sloupce k optimalizaci výkonu dotazů.

Vytváření zobrazení ve službě Azure Cosmos DB

Jako alternativu k externím tabulkám můžete vytvářet zobrazení nad externími daty.

Podobně jako v tabulkách zobrazených v předchozím příkladu byste měli umístit zobrazení do samostatných schémat:

create schema ecdc_cosmosdb;

Teď můžete vytvořit zobrazení ve schématu, které odkazuje na kontejner Azure Cosmos DB:

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

K optimalizaci výkonu byste měli použít nejmenší možné typy v definici schématu WITH .

Poznámka:

Klíč účtu služby Azure Cosmos DB byste měli umístit do samostatných přihlašovacích údajů a odkazovat na OPENROWSET tyto přihlašovací údaje od funkce. Neuchovávejte klíč účtu v definici zobrazení.

Přístup a oprávnění

V posledním kroku byste měli vytvořit uživatele databáze, kteří by měli mít přístup k vašemu LDW, a udělit jim oprávnění k výběru dat z externích tabulek a zobrazení. V následujícím skriptu zjistíte, jak přidat nového uživatele, který se ověří pomocí identity Microsoft Entra:

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

Místo objektů zabezpečení Microsoft Entra můžete vytvořit objekty zabezpečení SQL, které se ověřují pomocí přihlašovacího jména a hesla.

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

V obou případech můžete uživatelům přiřadit oprávnění.

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

Pravidla zabezpečení závisí na vašich zásadách zabezpečení. Tady jsou některé obecné pokyny:

  • Novým uživatelům byste měli odepřít ADMINISTER DATABASE BULK OPERATIONS oprávnění, protože by měli mít možnost číst data jenom pomocí externích tabulek a zobrazení, která jste připravili.
  • Měli byste poskytnout SELECT oprávnění pouze k tabulkám, které by měl některý uživatel používat.
  • Pokud poskytujete přístup k datům pomocí zobrazení, měli byste udělit REFERENCES oprávnění k přihlašovacím údajům, které se použijí pro přístup k externímu zdroji dat.

Tento uživatel má minimální oprávnění potřebná k dotazování externích dat. Pokud chcete vytvořit uživatele s oprávněními, externími tabulkami a zobrazeními, můžete uživateli udělit CONTROL oprávnění:

GRANT CONTROL TO [jovan@contoso.com]

Zabezpečení na základě rolí

Místo přiřazování oprávnění jednotlivým uživatelům je vhodné uspořádat uživatele do rolí a spravovat oprávnění na úrovni role. Následující ukázka kódu vytvoří novou roli představující lidi, kteří můžou analyzovat případy COVID-19, a přidá do této role tři uživatele:

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

Oprávnění můžete přiřadit všem uživatelům, kteří patří do skupiny:

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

Toto řízení přístupu na základě role může zjednodušit správu pravidel zabezpečení.

Další kroky