Поделиться через

Учебник: создание логического хранилища данных с использованием бессерверного пула SQL

  • Статья

Из этого учебника вы узнаете, как создать логическое хранилище данных (ЛХД) на основе службы хранилища Azure и Azure Cosmos DB.

ЛХД — это структура реляционного уровня, создаваемая на основе источников данных Azure, таких как хранилище Azure Data Lake Storage (ADLS), аналитическое хранилище Azure Cosmos DB или хранилище BLOB-объектов Azure.

Создание базы данных ЛХД

Необходимо создать пользовательскую базу данных, в которой будут храниться внешние таблицы и представления, ссылающиеся на внешние источники данных.

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

Эта сортировка обеспечит оптимальную производительность при чтении Parquet и Azure Cosmos DB. Если параметры сортировки базы данных не указаны, они должны быть заданы в определениях столбцов.

Настройка источников данных и форматов

На первом этапе необходимо настроить источник данных и указать формат файла для данных, хранимых дистанционно.

Создание источника данных

Источники данных представляют собой строки подключения, которые описывают место размещения данных и способ проверки подлинности в источнике.

В следующем примере показано определение источника данных, ссылающегося на общедоступный набор данных Azure ECDC COVID 19:

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

Вызывающий объект может получить доступ к источнику данных без учетных данных, если владелец источника данных разрешил анонимный доступ или предоставить явный доступ к удостоверению Microsoft Entra вызывающего объекта.

Вы можете явным образом задать пользовательские учетные данные, которые будут использоваться при доступе к данным во внешнем источнике.

В качестве необходимого условия в базе данных должен быть создан главный ключ:

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

В следующем внешнем источнике данных пул Synapse SQL должен использовать для доступа к данным в хранилище управляемое удостоверение рабочей области.

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

Чтобы получить доступ к аналитическому хранилищу Azure Cosmos DB, необходимо определить учетные данные, содержащие ключ учетной записи Azure Cosmos DB только для чтения.

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

Любой пользователь с ролью администратора Synapse может использовать эти учетные данные для доступа к хранилищу Azure Data Lake или аналитическому хранилищу Azure Cosmos DB. Если у вас недостаточно привилегированных пользователей, у которых нет роли администратора Synapse, необходимо явным образом предоставить им разрешения ссылаться на эти учетные данные для базы данных.

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

Дополнительные сведения см. на странице Предоставление разрешений на использование учетных данных для базы данных.

Определение форматов внешних файлов

Форматы внешних файлов определяют структуру файлов, хранящихся во внешнем источнике данных. Вы можете определить форматы внешних файлов Parquet и CSV:

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

Дополнительные сведения см. в разделе "Использование внешних таблиц с Synapse SQL " и CREATE EXTERNAL FILE FORMAT для описания формата CSV-файлов или Parquet.

Изучение данных

После настройки источников для просмотра данных можно использовать функцию OPENROWSET. Функция OPENROWSET считывает содержимое удаленного источника данных (например, файла) и возвращает содержимое в виде набора строк.

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

Функция OPENROWSET предоставляет сведения о столбцах во внешних файлах или контейнерах и позволяет задать схему внешних таблиц и представлений.

Создание внешних таблиц для хранилища BLOB-объектов Azure

После обнаружения схемы можно создать внешние таблицы и представления на основе внешних источников данных. Рекомендуется упорядочивать таблицы и представления в схемах баз данных. В следующем запросе можно создать схему, в которой будут размещены все объекты, обращающиеся к набору данных ECDC COVID в хранилище Azure Data Lake:

create schema ecdc_adls;

Схемы базы данных полезны для группирования объектов и определения разрешений для каждой схемы.

Определив схемы, можно создать внешние таблицы, ссылающиеся на эти файлы. Следующая внешняя таблица ссылается на файл Parquet ECDC COVID, размещенный в службе хранилища 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
);

Чтобы добиться от запросов оптимальной производительности, используйте для строковых и числовых столбцов наименьшие возможные типы.

Создание представлений в Azure Cosmos DB

В качестве альтернативы внешним таблицам можно создавать представления на основе внешних данных.

Аналогично таблицам, приведенным в предыдущем примере, представления должны размещаться в отдельных схемах:

create schema ecdc_cosmosdb;

Теперь вы можете создать представление в схеме, которая ссылается на контейнер 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

Для оптимизации производительности в определении схемы WITH следует использовать наименьшие возможные типы.

Примечание.

Следует разместить ключ учетной записи Azure Cosmos DB в отдельном наборе учетных данных и ссылаться на эти учетные данные из функции OPENROWSET. Не храните ключ учетной записи в определении представления.

Доступ и разрешения

В качестве последнего шага следует создать пользователей базы данных, у которых должен быть доступ к ЛХД, и предоставить им разрешения на выбор данных из внешних таблиц и представлений. В следующем скрипте показано, как добавить нового пользователя, который будет проходить проверку подлинности с помощью удостоверения Microsoft Entra:

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

Вместо субъектов Microsoft Entra можно создать субъекты SQL, прошедшие проверку подлинности с именем входа и паролем.

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

В обоих случаях пользователям можно назначать разрешения.

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

Правила безопасности зависят от политик безопасности. Ниже приведены некоторые общие рекомендации.

  • Следует отклонять разрешение ADMINISTER DATABASE BULK OPERATIONS для новых пользователей, так как у них должна быть возможность считывать данные только с помощью подготовленных вами внешних таблиц и представлений.
  • Следует предоставлять разрешение SELECT только тем таблицам, к которым могут обращаться те или иные пользователи.
  • Если вы организуете доступ к данным с помощью представлений, необходимо предоставить разрешение REFERENCES учетным данным, которые будут использоваться для доступа к внешнему источнику.

Этот пользователь обладает минимальными разрешениями, необходимыми для запроса внешних данных. Если вы хотите создать опытного пользователя, который может настраивать разрешения, внешние таблицы и представления, можно предоставить пользователю разрешение CONTROL.

GRANT CONTROL TO [jovan@contoso.com]

Безопасность на основе ролей

Вместо того чтобы назначать разрешения отдельным пользователям, рекомендуется упорядочить пользователей по ролям и управлять разрешениями на уровне ролей. В следующем примере кода создают новую роль, представляющую пользователей, которые могут анализировать случаи COVID-19, после чего в эту роль добавляют трех пользователей.

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

Разрешения можно назначать всем пользователям, принадлежащим к группе.

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

Такое управление доступом на основе ролей позволяет упростить управление правилами безопасности.

Следующие шаги