Создание и использование представлений с помощью бессерверного пула SQL в Azure Synapse Analytics

Из этой статьи вы узнаете, как создавать и использовать представления для "обертывания" запросов бессерверного пула SQL. Представления позволяют повторно использовать эти запросы. Представления также необходимы, если вы хотите использовать средства, такие как Power BI, в сочетании с бессерверным пулом SQL.

Предварительные требования

Первый этап — создание базы данных. В ней создается представление и инициализируются объекты, необходимые для проверки подлинности в службе хранилища Azure. Для этого в базе данных выполняется скрипт установки. Все запросы в этой статье будут выполняться в примере базы данных.

Представления внешних данных

Представления можно создавать так же, как и обычные представления SQL Server. Приведенный ниже запрос позволяет создать представление, которое считывает файл population.csv.

Примечание

Измените первую строку в запросе ([mydbname]), чтобы использовать созданную вами базу данных.

USE [mydbname];
GO

DROP VIEW IF EXISTS populationView;
GO

CREATE VIEW populationView AS
SELECT * 
FROM OPENROWSET(
        BULK 'csv/population/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', 
        FIELDTERMINATOR =',', 
        ROWTERMINATOR = '\n'
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
) AS [r];

В представлении используется EXTERNAL DATA SOURCE с корневым URL-адресом вашего хранилища в качестве DATA_SOURCE и добавляется относительный путь к файлам.

Представления Delta Lake

Если вы создаете представления поверх папки Delta Lake, вам необходимо указать расположение корневой папки после параметра BULK вместо указания пути к файлу.

Функция OPENROWSET, которая считывает данные из папки Delta Lake, проверяет структуру папок и автоматически определяет расположение файлов.

create or alter view CovidDeltaLake
as
select *
from openrowset(
           bulk 'covid',
           data_source = 'DeltaLakeStorage',
           format = 'delta'
    ) with (
           date_rep date,
           cases int,
           geo_id varchar(6)
           ) as rows

Дополнительные сведения см. на странице самостоятельного решения проблем бессерверного пула SQL Synapse и Azure Synapse Analytics.

Секционированные представления

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

CREATE VIEW TaxiView
AS SELECT *, nyc.filepath(1) AS [year], nyc.filepath(2) AS [month]
FROM
    OPENROWSET(
        BULK 'parquet/taxi/year=*/month=*/*.parquet',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT='PARQUET'
    ) AS nyc

Секционированные представления могут повысить производительность запросов, выполняя исключение секций при запросе с фильтрами по столбцам секционирования. Однако не все запросы поддерживают исключение секций, поэтому важно следовать некоторым рекомендациям.

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

При использовании JOIN в SQL-запросах объявите предикат фильтра как NVARCHAR, чтобы снизить сложность плана запроса и увеличить вероятность правильного исключения секций. Столбцы секционирования обычно выводятся как NVARCHAR(1024), поэтому использование одного и того же типа для предиката позволяет избежать необходимости неявного приведения, что может повысить сложность плана запроса.

Разделенные представления Delta Lake

Если вы создаете секционированные представления поверх хранилища Delta Lake, вы можете указать только корневую папку Delta Lake и не нужно явно открывать столбцы секционирования с помощью функции FILEPATH:

CREATE OR ALTER VIEW YellowTaxiView
AS SELECT *
FROM  
    OPENROWSET(
        BULK 'yellow',
        DATA_SOURCE = 'DeltaLakeStorage',
        FORMAT='DELTA'
    ) nyc

Функция OPENROWSET выполняет проверку структуры базовой папки Delta Lake и автоматически определяет и отображает столбцы разделения. Исключение секционирования будет выполнено автоматически, если вы поместите столбец секционирования в предложение WHERE запроса.

Имя папки в функции OPENROWSET (yellow в этом примере), сцепленное с кодом URI LOCATION, который определен в источнике данных DeltaLakeStorage, должно ссылаться на корневую папку Delta Lake с вложенной папкой _delta_log.

Дополнительные сведения см. на странице самостоятельного решения проблем бессерверного пула SQL Synapse и Azure Synapse Analytics.

Представления JSON

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

CREATE OR ALTER VIEW CovidCases
AS 
select
    *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
    cross apply openjson (doc)
        with (  date_rep datetime2,
                cases int,
                fatal int '$.deaths',
                country varchar(100) '$.countries_and_territories')

Функция OPENJSON анализирует каждую строку из файла JSON, содержащего один документ JSON на строку, в текстовом формате.

Представления Azure Cosmos DB в контейнерах

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

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
GO
CREATE OR ALTER VIEW 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

Дополнительные сведения см. в статье Запрос данных Azure Cosmos DB с помощью бессерверого пула SQL в Azure Synapse Link.

Использование представления

Представления в запросах можно использовать так же, как и в запросах SQL Server.

Следующий запрос демонстрирует использование представления population_csv, созданного в разделе Создание представления. Он возвращает названия стран или регионов с численностью населения по состоянию на 2019 г. в порядке убывания.

Примечание

Измените первую строку в запросе ([mydbname]), чтобы использовать созданную вами базу данных.

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationView
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

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

Дальнейшие действия

Сведения о том, как запрашивать различные типы файлов, см. в статьях Запрашивание одного CSV-файла, Запрашивание файлов Parquet и Запрашивание файлов JSON.

• Новые возможности Azure Synapse Analytics?
• Рекомендации по использованию бессерверного пула SQL в Azure Synapse Analytics
• Устранение неполадок с бессерверным пулом SQL в Azure Synapse Analytics
• Устранение неполадок с медленным запросом в выделенном пуле SQL
• Устранение неполадок с Synapse Studio