Samouczek: tworzenie magazynu danych logicznych z bezserwerową pulą SQL

Artykuł
10/18/2023

Z tego samouczka dowiesz się, jak utworzyć magazyn danych logicznych (LDW) na podstawie usługi Azure Storage i usługi Azure Cosmos DB.

LDW to warstwa relacyjna oparta na źródłach danych platformy Azure, takich jak usługa Azure Data Lake Storage (ADLS), magazyn analityczny usługi Azure Cosmos DB lub usługa Azure Blob Storage.

Tworzenie bazy danych LDW

Musisz utworzyć niestandardową bazę danych, w której będą przechowywane zewnętrzne tabele i widoki odwołujące się do zewnętrznych źródeł danych.

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

To sortowanie zapewni optymalną wydajność podczas odczytywania plików Parquet i Azure Cosmos DB. Jeśli nie chcesz określać sortowania bazy danych, upewnij się, że to sortowanie określono w definicji kolumny.

Konfigurowanie źródeł danych i formatów

Pierwszym krokiem jest skonfigurowanie źródła danych i określenie formatu plików przechowywanych zdalnie danych.

Tworzenie źródła danych

Źródła danych reprezentują parametry połączenia informacji opisujących miejsce umieszczenia danych oraz sposób uwierzytelniania w źródle danych.

W poniższym przykładzie pokazano przykład definicji źródła danych, która odwołuje się do publicznego zestawu danych ECDC COVID 19 Azure Open:

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

Obiekt wywołujący może uzyskać dostęp do źródła danych bez poświadczeń, jeśli właściciel źródła danych zezwolił na dostęp anonimowy lub przyznać jawny dostęp do tożsamości osoby wywołującej firmy Microsoft.

Można jawnie zdefiniować poświadczenia niestandardowe, które będą używane podczas uzyskiwania dostępu do danych w zewnętrznym źródle danych.

Tożsamość zarządzana obszaru roboczego usługi Synapse
Sygnatura dostępu współdzielonego usługi Azure Storage
Niestandardowa nazwa główna usługi lub tożsamość aplikacja systemu Azure.
Klucz konta usługi Azure Cosmos DB tylko do odczytu, który umożliwia odczytywanie magazynu analitycznego usługi Azure Cosmos DB.

W ramach wymagań wstępnych należy utworzyć klucz główny w bazie danych:

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

W poniższym zewnętrznym źródle danych pula SQL usługi Synapse powinna używać tożsamości zarządzanej obszaru roboczego do uzyskiwania dostępu do danych w magazynie.

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

Aby uzyskać dostęp do magazynu analitycznego usługi Azure Cosmos DB, należy zdefiniować poświadczenia zawierające klucz konta usługi Azure Cosmos DB tylko do odczytu.

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

Każdy użytkownik z rolą usługi Synapse Administracja istrator może użyć tych poświadczeń w celu uzyskania dostępu do magazynu analitycznego usługi Azure Data Lake lub magazynu analitycznego usługi Azure Cosmos DB. Jeśli masz użytkowników z niskimi uprawnieniami, którzy nie mają roli usługi Synapse Administracja istrator, musisz nadać im jawne uprawnienie do odwołowywania się do tych poświadczeń o zakresie bazy danych:

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

Więcej szczegółów można znaleźć na stronie udzielania uprawnień POŚWIADCZEŃ W ZAKRESIE BAZY DANYCH.

Definiowanie formatów plików zewnętrznych

Formaty plików zewnętrznych definiują strukturę plików przechowywanych w zewnętrznym źródle danych. Można zdefiniować formaty plików zewnętrznych Parquet i CSV:

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

Aby uzyskać więcej informacji, zobacz Używanie tabel zewnętrznych z usługą Synapse SQL i CREATE EXTERNAL FILE FORMAT w celu opisania formatu plików CSV lub Parquet.

Eksplorowanie danych

Po skonfigurowaniu źródeł danych możesz użyć OPENROWSET funkcji do eksplorowania danych. Funkcja OPENROWSET odczytuje zawartość zdalnego źródła danych (na przykład pliku) i zwraca zawartość jako zestaw wierszy.

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

Funkcja OPENROWSET udostępnia informacje o kolumnach w plikach zewnętrznych lub kontenerach i umożliwia zdefiniowanie schematu tabel i widoków zewnętrznych.

Tworzenie tabel zewnętrznych w usłudze Azure Storage

Po odnalezieniu schematu można tworzyć zewnętrzne tabele i widoki na podstawie zewnętrznych źródeł danych. Dobrym rozwiązaniem jest organizowanie tabel i widoków w schematach baz danych. W poniższym zapytaniu można utworzyć schemat, w którym umieścisz wszystkie obiekty, które uzyskują dostęp do zestawu danych COVID usługi ECDC w usłudze Azure Data Lake Storage:

create schema ecdc_adls;

Schematy bazy danych są przydatne do grupowania obiektów i definiowania uprawnień na schemat.

Po zdefiniowaniu schematów można utworzyć tabele zewnętrzne odwołujące się do plików. Poniższa tabela zewnętrzna odwołuje się do pliku ECDC COVID parquet umieszczonego w usłudze 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
);

Upewnij się, że używasz najmniejszych możliwych typów dla kolumn ciągów i liczb w celu zoptymalizowania wydajności zapytań.

Tworzenie widoków w usłudze Azure Cosmos DB

Alternatywą dla tabel zewnętrznych jest tworzenie widoków na podstawie danych zewnętrznych.

Podobnie jak w tabelach przedstawionych w poprzednim przykładzie, należy umieścić widoki w oddzielnych schematach:

create schema ecdc_cosmosdb;

Teraz możesz utworzyć widok w schemacie odwołującym się do kontenera usługi 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

Aby zoptymalizować wydajność, należy użyć najmniejszych możliwych typów w definicji schematu WITH .

Uwaga

Należy umieścić klucz konta usługi Azure Cosmos DB w osobnym poświadczeniu i odwołać się do tego poświadczenia z OPENROWSET funkcji. Nie należy przechowywać klucza konta w definicji widoku.

Dostęp i uprawnienia

W ostatnim kroku należy utworzyć użytkowników bazy danych, którzy powinni mieć dostęp do pliku LDW i przyznać im uprawnienia do wybierania danych z tabel zewnętrznych i widoków. W poniższym skryscie można zobaczyć, jak dodać nowego użytkownika, który zostanie uwierzytelniony przy użyciu tożsamości firmy Microsoft Entra:

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

Zamiast podmiotów zabezpieczeń firmy Microsoft można utworzyć podmioty zabezpieczeń SQL, które uwierzytelniają się przy użyciu nazwy logowania i hasła.

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

W obu przypadkach można przypisać uprawnienia do użytkowników.

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

Reguły zabezpieczeń zależą od zasad zabezpieczeń. Oto niektóre ogólne wytyczne:

Należy odmówić ADMINISTER DATABASE BULK OPERATIONS uprawnień nowym użytkownikom, ponieważ powinni mieć możliwość odczytu danych tylko przy użyciu przygotowanych tabel zewnętrznych i widoków.
Należy podać SELECT uprawnienia tylko do tabel, których niektórzy użytkownicy powinni używać.
Jeśli zapewniasz dostęp do danych przy użyciu widoków, należy udzielić REFERENCES uprawnień do poświadczeń, które będą używane do uzyskiwania dostępu do zewnętrznego źródła danych.

Ten użytkownik ma minimalne uprawnienia wymagane do wykonywania zapytań dotyczących danych zewnętrznych. Jeśli chcesz utworzyć użytkownika, który może skonfigurować uprawnienia, tabele zewnętrzne i widoki, możesz udzielić CONTROL użytkownikowi uprawnień:

GRANT CONTROL TO [jovan@contoso.com]

Zabezpieczenia oparte na rolach

Zamiast przypisywać uprawnienia do poszczególnych zastosowań, dobrym rozwiązaniem jest organizowanie użytkowników w role i zarządzanie uprawnieniami na poziomie roli. Poniższy przykładowy kod tworzy nową rolę reprezentującą osoby, które mogą analizować przypadki COVID-19, i dodaje trzech użytkowników do tej roli:

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

Uprawnienia można przypisać do wszystkich użytkowników należących do grupy:

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

Ta kontrola dostępu oparta na rolach może uprościć zarządzanie regułami zabezpieczeń.

Następne kroki

Aby dowiedzieć się, jak połączyć bezserwerową pulę SQL z programem Power BI Desktop i tworzyć raporty, zobacz Połączenie bezserwerową pulę SQL z programem Power BI Desktop i tworzenie raportów.
Aby dowiedzieć się, jak używać tabel zewnętrznych w bezserwerowej puli SQL, zobacz Używanie tabel zewnętrznych z usługą Synapse SQL

Share via