Delen via

Zelfstudie: Logische datawarehouse maken met een serverloze SQL-pool

  • Artikel

In deze zelfstudie leert u hoe u een Logical Data Warehouse (LDW) maakt boven op Azure Storage en Azure Cosmos DB.

LDW is een relationele laag die is gebouwd op Basis van Azure-gegevensbronnen, zoals Azure Data Lake Storage (ADLS), analytische opslag van Azure Cosmos DB of Azure Blob Storage.

Een LDW-database maken

U moet een aangepaste database maken waarin u uw externe tabellen en weergaven opslaat die verwijzen naar externe gegevensbronnen.

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

Deze sortering biedt de optimale prestaties tijdens het lezen van Parquet en Azure Cosmos DB. Als u de databasesortering niet wilt opgeven, moet u deze sortering opgeven in de kolomdefinitie.

Gegevensbronnen en -indelingen configureren

Als eerste stap moet u de gegevensbron configureren en de bestandsindeling van extern opgeslagen gegevens opgeven.

Gegevensbron maken

Gegevensbronnen vertegenwoordigen verbindingsreeks informatie die beschrijft waar uw gegevens worden geplaatst en hoe u zich kunt verifiëren bij uw gegevensbron.

Een voorbeeld van de definitie van de gegevensbron die verwijst naar openbare ECDC COVID 19 Azure Open Data Set , wordt weergegeven in het volgende voorbeeld:

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

Een beller heeft toegang tot de gegevensbron zonder referenties als een eigenaar van de gegevensbron anonieme toegang heeft toegestaan of expliciete toegang geeft tot de Microsoft Entra-identiteit van de beller.

U kunt expliciet een aangepaste referentie definiëren die wordt gebruikt bij het openen van gegevens in een externe gegevensbron.

Als vereiste moet u een hoofdsleutel maken in de database:

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

In de volgende externe gegevensbron moet synapse SQL-pool een beheerde identiteit van de werkruimte gebruiken om toegang te krijgen tot gegevens in de opslag.

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

Als u toegang wilt krijgen tot analytische opslag van Azure Cosmos DB, moet u een referentie definiëren die een alleen-lezen Azure Cosmos DB-accountsleutel bevat.

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

Elke gebruiker met de rol Synapse-beheerder kan deze referenties gebruiken voor toegang tot Azure Data Lake-opslag of analytische opslag van Azure Cosmos DB. Als u gebruikers met beperkte bevoegdheden hebt die geen synapse-beheerdersrol hebben, moet u hen een expliciete machtiging geven om te verwijzen naar deze databasereferenties:

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

Meer informatie vindt u op de pagina DATABASE SCOPED CREDENTIAL-machtigingen verlenen.

Externe bestandsindelingen definiëren

Externe bestandsindelingen definiëren de structuur van de bestanden die zijn opgeslagen op externe gegevensbron. U kunt externe Parquet- en CSV-bestandsindelingen definiëren:

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

Zie Externe tabellen gebruiken met Synapse SQL en CREATE EXTERNAL FILE FORMAT om de indeling van CSV- of Parquet-bestanden te beschrijven voor meer informatie.

Uw gegevens verkennen

Zodra u uw gegevensbronnen hebt ingesteld, kunt u de OPENROWSET functie gebruiken om uw gegevens te verkennen. De functie OPENROWSET leest inhoud van een externe gegevensbron (bijvoorbeeld bestand) en retourneert de inhoud als een set rijen.

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

Met OPENROWSET de functie krijgt u informatie over de kolommen in de externe bestanden of containers en kunt u een schema van uw externe tabellen en weergaven definiëren.

Externe tabellen maken in Azure Storage

Zodra u het schema hebt gedetecteerd, kunt u externe tabellen en weergaven maken boven op uw externe gegevensbronnen. Het is raadzaam om uw tabellen en weergaven in databasesschema's te ordenen. In de volgende query kunt u een schema maken waarin u alle objecten plaatst die toegang hebben tot ECDC COVID-gegevensset in Azure Data Lake Storage:

create schema ecdc_adls;

De databaseschema's zijn handig voor het groeperen van de objecten en het definiëren van machtigingen per schema.

Zodra u de schema's hebt gedefinieerd, kunt u externe tabellen maken die verwijzen naar de bestanden. De volgende externe tabel verwijst naar het ECDC COVID Parquet-bestand dat in de Azure-opslag is geplaatst:

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

Zorg ervoor dat u de kleinste mogelijke typen voor tekenreeks- en getalkolommen gebruikt om de prestaties van uw query's te optimaliseren.

Weergaven maken in Azure Cosmos DB

Als alternatief voor externe tabellen kunt u weergaven maken boven op uw externe gegevens.

Net als bij de tabellen die in het vorige voorbeeld worden weergegeven, moet u de weergaven in afzonderlijke schema's plaatsen:

create schema ecdc_cosmosdb;

U kunt nu een weergave maken in het schema dat verwijst naar een Azure Cosmos DB-container:

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

Als u de prestaties wilt optimaliseren, moet u de kleinste mogelijke typen in de WITH schemadefinitie gebruiken.

Notitie

U moet uw Azure Cosmos DB-accountsleutel in een afzonderlijke referentie plaatsen en naar deze referentie verwijzen vanuit de OPENROWSET functie. Bewaar uw accountsleutel niet in de weergavedefinitie.

Toegang en machtigingen

Als laatste stap moet u databasegebruikers maken die toegang moeten hebben tot uw LDW en hen machtigingen geven om gegevens uit de externe tabellen en weergaven te selecteren. In het volgende script ziet u hoe u een nieuwe gebruiker toevoegt die wordt geverifieerd met behulp van Microsoft Entra-identiteit:

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

In plaats van Microsoft Entra-principals kunt u SQL-principals maken die worden geverifieerd met de aanmeldingsnaam en het wachtwoord.

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

In beide gevallen kunt u machtigingen toewijzen aan de gebruikers.

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

De beveiligingsregels zijn afhankelijk van uw beveiligingsbeleid. Enkele algemene richtlijnen zijn:

  • U moet machtigingen voor de nieuwe gebruikers weigeren ADMINISTER DATABASE BULK OPERATIONS , omdat ze alleen gegevens moeten kunnen lezen met behulp van de externe tabellen en weergaven die u hebt voorbereid.
  • U moet alleen machtigingen opgeven SELECT voor de tabellen die sommige gebruikers moeten kunnen gebruiken.
  • Als u toegang geeft tot gegevens met behulp van de weergaven, moet u toestemming verlenen REFERENCES aan de referentie die wordt gebruikt voor toegang tot externe gegevensbron.

Deze gebruiker heeft minimale machtigingen die nodig zijn om een query uit te voeren op externe gegevens. Als u een power-gebruiker wilt maken die machtigingen, externe tabellen en weergaven kan instellen, kunt u de gebruiker toestemming geven CONTROL :

GRANT CONTROL TO [jovan@contoso.com]

Op rollen gebaseerde beveiliging

In plaats van machtigingen toe te wijzen aan het afzonderlijke gebruik, is het een goede gewoonte om de gebruikers in rollen te ordenen en machtigingen op rolniveau te beheren. In het volgende codevoorbeeld wordt een nieuwe rol gemaakt die de personen vertegenwoordigt die COVID-19-gevallen kunnen analyseren en drie gebruikers aan deze rol kunnen toevoegen:

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

U kunt de machtigingen toewijzen aan alle gebruikers die deel uitmaken van de groep:

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

Met dit op rollen gebaseerde beveiligingstoegangsbeheer kan het beheer van uw beveiligingsregels worden vereenvoudigd.

Volgende stappen