Självstudie: Skapa logiskt informationslager med en serverlös SQL-pool

Artikel
10/18/2023

I den här självstudien får du lära dig hur du skapar ett LDW (Logical Data Warehouse) ovanpå Azure Storage och Azure Cosmos DB.

LDW är ett relationslager som bygger på Azure-datakällor som Azure Data Lake Storage (ADLS), Azure Cosmos DB-analyslagring eller Azure Blob Storage.

Skapa en LDW-databas

Du måste skapa en anpassad databas där du lagrar dina externa tabeller och vyer som refererar till externa datakällor.

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

Den här sorteringen ger optimala prestanda vid läsning av Parquet och Azure Cosmos DB. Om du inte vill ange databassortering kontrollerar du att du anger den här sorteringen i kolumndefinitionen.

Konfigurera datakällor och format

Som ett första steg måste du konfigurera datakällan och ange filformatet för fjärranslutna data.

Skapa datakälla

Datakällor representerar anslutningssträng information som beskriver var dina data placeras och hur du autentiserar till din datakälla.

Ett exempel på datakälldefinition som refererar till offentlig ECDC COVID 19 Azure Open Data Set visas i följande exempel:

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

En uppringare kan komma åt datakällan utan autentiseringsuppgifter om en ägare av datakällan tillät anonym åtkomst eller ger explicit åtkomst till Anroparens Microsoft Entra-identitet.

Du kan uttryckligen definiera en anpassad autentiseringsuppgift som ska användas vid åtkomst till data på en extern datakälla.

Hanterad identitet för Synapse-arbetsytan
Signatur för delad åtkomst för Azure Storage
Custom Service Principal Name eller Azure Application Identity.
Skrivskyddad Azure Cosmos DB-kontonyckel som gör att du kan läsa Azure Cosmos DB-analyslagring.

Som en förutsättning måste du skapa en huvudnyckel i databasen:

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

I följande externa datakälla bör Synapse SQL-poolen använda en hanterad identitet för arbetsytan för att komma åt data i lagringen.

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

För att få åtkomst till Azure Cosmos DB-analyslagring måste du definiera en autentiseringsuppgift som innehåller en skrivskyddad Azure Cosmos DB-kontonyckel.

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

Alla användare med rollen Synapse-administratör kan använda dessa autentiseringsuppgifter för att få åtkomst till Azure Data Lake Storage eller Azure Cosmos DB-analyslagring. Om du har lågprivilegierade användare som inte har rollen Synapse-administratör måste du ge dem en uttrycklig behörighet att referera till dessa databasomfattningsautentiseringsuppgifter:

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

Mer information finns på sidan bevilja BEHÖRIGHETER FÖR DATABASOMFATTNING FÖR AUTENTISERINGSUPPGIFTER.

Definiera externa filformat

Externa filformat definierar strukturen för de filer som lagras på den externa datakällan. Du kan definiera externa filformat för Parquet och CSV:

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

Mer information finns i Använda externa tabeller med Synapse SQL och SKAPA EXTERNT FILFORMAT för att beskriva formatet för CSV- eller Parquet-filer.

Utforska dina data

När du har konfigurerat dina datakällor kan du använda OPENROWSET funktionen för att utforska dina data. Funktionen OPENROWSET läser innehållet i en fjärrdatakälla (till exempel fil) och returnerar innehållet som en uppsättning rader.

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

Funktionen OPENROWSET ger dig information om kolumnerna i de externa filerna eller containrarna och gör att du kan definiera ett schema för dina externa tabeller och vyer.

Skapa externa tabeller i Azure Storage

När du har identifierat schemat kan du skapa externa tabeller och vyer ovanpå dina externa datakällor. Det är bra att ordna tabeller och vyer i databasscheman. I följande fråga kan du skapa ett schema där du placerar alla objekt som har åtkomst till ECDC COVID-datauppsättningen i Azure Data Lake Storage:

create schema ecdc_adls;

Databasscheman är användbara för att gruppera objekten och definiera behörigheter per schema.

När du har definierat scheman kan du skapa externa tabeller som refererar till filerna. Följande externa tabell refererar till ECDC COVID-parquet-filen som finns i Azure Storage:

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

Se till att du använder de minsta möjliga typerna för sträng- och talkolumner för att optimera dina frågors prestanda.

Skapa vyer i Azure Cosmos DB

Som ett alternativ till externa tabeller kan du skapa vyer ovanpå dina externa data.

På samma sätt som tabellerna som visas i föregående exempel bör du placera vyerna i separata scheman:

create schema ecdc_cosmosdb;

Nu kan du skapa en vy i schemat som refererar till en 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

För att optimera prestanda bör du använda de minsta möjliga typerna i schemadefinitionen WITH .

Kommentar

Du bör placera din Azure Cosmos DB-kontonyckel i en separat autentiseringsuppgift och referera till den här autentiseringsuppgiften OPENROWSET från funktionen. Behåll inte kontonyckeln i vydefinitionen.

Åtkomst och behörigheter

Som ett sista steg bör du skapa databasanvändare som ska kunna komma åt din LDW och ge dem behörighet att välja data från de externa tabellerna och vyerna. I följande skript kan du se hur du lägger till en ny användare som ska autentiseras med hjälp av Microsoft Entra-identitet:

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

I stället för Microsoft Entra-huvudkonton kan du skapa SQL-huvudnamn som autentiserar med inloggningsnamnet och lösenordet.

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

I båda fallen kan du tilldela behörigheter till användarna.

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

Säkerhetsreglerna beror på dina säkerhetsprinciper. Några allmänna riktlinjer är:

Du bör neka ADMINISTER DATABASE BULK OPERATIONS behörighet till de nya användarna eftersom de endast ska kunna läsa data med hjälp av de externa tabeller och vyer som du har förberett.
Du bör endast ge SELECT behörighet till de tabeller som vissa användare ska kunna använda.
Om du ger åtkomst till data med hjälp av vyerna bör du ge REFERENCES behörighet till de autentiseringsuppgifter som ska användas för att komma åt den externa datakällan.

Den här användaren har minimal behörighet för att köra frågor mot externa data. Om du vill skapa en power-användare som kan konfigurera behörigheter, externa tabeller och vyer kan du ge CONTROL användaren behörighet:

GRANT CONTROL TO [jovan@contoso.com]

Rollbaserad säkerhet

I stället för att tilldela behörigheter till enskilda användare är det bra att ordna användarna i roller och hantera behörigheter på rollnivå. Följande kodexempel skapar en ny roll som representerar de personer som kan analysera COVID-19-fall och lägger till tre användare i den här rollen:

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

Du kan tilldela behörigheter till alla användare som tillhör gruppen:

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

Den här rollbaserade säkerhetsåtkomstkontrollen kan förenkla hanteringen av dina säkerhetsregler.

Nästa steg

Information om hur du ansluter en serverlös SQL-pool till Power BI Desktop och skapar rapporter finns i Ansluta serverlös SQL-pool till Power BI Desktop och skapa rapporter.
Information om hur du använder externa tabeller i en serverlös SQL-pool finns i Använda externa tabeller med Synapse SQL

Dela via