Udostępnij przez

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

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 odczytu plików Parquet i bazy danych 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 to parametry połączenia, które określają, gdzie są umieszczone dane i jak uwierzytelniać się do źródła 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/'
);

A caller may access data source without credential if an owner of data source allowed anonymous access or give explicit access to Microsoft Entra identity of the caller.

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.

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

In the following external data source, Synapse SQL pool should use a managed identity of the workspace to access data in the storage.

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 = '<yourcosmosdbaccountkey>';

Każdy użytkownik mający rolę administratora usługi Synapse może użyć tych poświadczeń, aby uzyskać dostęp do magazynu 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 administratora usługi Synapse, musisz nadać im jawne uprawnienia 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

Find more details in grant DATABASE SCOPED CREDENTIAL permissions page.

Definiowanie formatów plików zewnętrznych

Formaty plików zewnętrznych definiują strukturę plików przechowywanych w zewnętrznym źródle danych. You can define Parquet and CSV external file formats:

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

For more information, see Use external tables with Synapse SQL and CREATE EXTERNAL FILE FORMAT to describe format of CSV or Parquet files.

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=yourcosmosdbaccount;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

You should place your Azure Cosmos DB account key in a separate credential and reference this credential from the OPENROWSET function. 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

Instead of Microsoft Entra principals, you can create SQL principals that authenticate with the login name and password.

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

The security rules depend on your security policies. 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ć.
  • If you are providing access to data using the views, you should grant REFERENCES permission to the credential that will be used to access external data source.

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

  • Last updated on