Создание и использование представлений с помощью бессерверного пула 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 storage, можно указать только корневую папку 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.