Создание и использование собственных внешних таблиц с помощью пулов SQL в Azure Synapse Analytics

Статья
05/12/2023

В данном разделе вы узнаете, как создавать и использовать собственные внешние таблицы в пулах Synapse SQL. Собственные внешние таблицы имеют лучшую производительность по сравнению с внешними таблицами с TYPE=HADOOP в определении внешнего источника данных. Это связано с тем, что собственные внешние таблицы используют собственный код для доступа к внешним данным.

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

общедоступное хранилище, в котором пользователи получают доступ к общедоступным файлам;
Защищенное хранилище, где пользователи получают доступ к файлам хранилища с помощью учетных данных SAS, удостоверения Microsoft Entra или управляемого удостоверения рабочей области Synapse.

Примечание.

В выделенных пулах SQL можно использовать только собственные внешние таблицы с типом файлов Parquet, и эта функция доступна в общедоступной предварительной версии. Если вы хотите воспользоваться общедоступными функциями средства Parquet в выделенных пулах SQL или вам нужен доступ к файлам CSV или ORC, используйте внешние таблицы Hadoop. Как правило, встроенные внешние таблицы доступны в бессерверных пулах SQL. Дополнительные сведения о различиях между собственными внешними таблицами и внешними таблицами Hadoop см. в статье Использование внешних таблиц с Synapse SQL.

В следующей таблице перечислены поддерживаемые форматы данных:

Формат данных (собственные внешние таблицы)	Бессерверный пул SQL	Выделенный пул SQL
Parquet	Да (GA)	Да (предварительная версия)
CSV	Да	Нет (также можно использовать внешние таблицы Hadoop)
delta	Да	No
Spark	Да	No
Dataverse	Да	No
Форматы данных Azure Cosmos DB (JSON, BSON и т. д.)	Нет (также можно создавать представления)	Нет

Необходимые компоненты

Для начала создайте базу данных, в которой будут созданы таблицы. Перед созданием учетных данных для базы данных в базе данных должен находиться главный ключ для защиты учетных данных. Дополнительные сведения об этом см. в статье CREATE MASTER KEY (Transact-SQL). Затем создайте следующие объекты, которые используются в нижеприведенном примере:

DATABASE SCOPED CREDENTIAL sqlondemand — предоставляет доступ к учетной записи хранения Azure https://sqlondemandstorage.blob.core.windows.net, защищенной с использованием SAS.

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'

EXTERNAL DATA SOURCE sqlondemanddemo — ссылается на демонстрационную учетную запись хранения, защищенную ключом SAS. EXTERNAL DATA SOURCE nyctlc — ссылается на общедоступную учетную запись хранения Azure в расположении https://azureopendatastorage.blob.core.windows.net/nyctlc/.

CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);
GO
CREATE EXTERNAL DATA SOURCE nyctlc
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/')
GO
CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
WITH ( location = 'https://sqlondemandstorage.blob.core.windows.net/delta-lake/' );

Форматы файлов QuotedCSVWithHeaderFormat и ParquetFormat — описывают типы файлов CSV и Parquet.

CREATE EXTERNAL FILE FORMAT QuotedCsvWithHeaderFormat
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2   )
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT DeltaLakeFormat WITH (  FORMAT_TYPE = DELTA );
GO

Запросы в этой статье будут выполняться к примеру базы данных и использовать эти объекты.

Внешняя таблица в файле

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

В следующем запросе создается внешняя таблица, считывающая файл population.csv из демонстрационной учетной записи хранения Azure SynapseSQL, на которую ссылается источник данных sqlondemanddemo и которая защищена с помощью учетных данных уровня базы данных sqlondemand.

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

Примечание.

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

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

Встроенные таблицы CSV в настоящее время доступны только в бессерверных пулах SQL.

Внешняя таблица по набору файлов

Вы можете создавать внешние таблицы, которые читают данные из набора файлов, размещенных в хранилище Azure:

CREATE EXTERNAL TABLE Taxi (
     vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
     pickup_datetime DATETIME2, 
     dropoff_datetime DATETIME2,
     passenger_count INT,
     trip_distance FLOAT,
     fare_amount FLOAT,
     tip_amount FLOAT,
     tolls_amount FLOAT,
     total_amount FLOAT
) WITH (
         LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
         DATA_SOURCE = nyctlc,
         FILE_FORMAT = ParquetFormat
);

Вы можете указать шаблон, которому должны удовлетворять файлы, чтобы на них ссылалась внешняя таблица. Шаблон требуется только для таблиц Parquet и CSV. Если вы используете формат Delta Lake, вам необходимо указать только корневую папку, в результате чего внешняя таблица автоматически найдет шаблон.

Примечание.

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

Внешняя таблица для файлов, которые можно добавить

Файлы, на которые указывает ссылка во внешней таблице, не должны изменяться во время выполнения запроса. При длительных запросах пул SQL может повторять попытки чтения, читать части файлов или даже считывать файл несколько раз. Изменения содержимого файла приведут к неверным результатам. Таким образом, запрос пула SQL завершается ошибкой, если он обнаруживает, что время изменения какого-либо файла изменено во время выполнения запроса. В некоторых сценариях может потребоваться создать таблицу для файлов, которые добавляются постоянно. Чтобы избежать сбоев запросов из-за постоянно добавляемых файлов, можно указать с помощью параметра TABLE_OPTIONS, что внешняя таблица должна игнорировать потенциальное несогласованное чтение.

CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat,
    TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
);

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

Этот параметр доступен только во внешних таблицах, созданных в формате CSV-файла.

Примечание.

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

Внешняя таблица Delta Lake

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

ECDC COVID-19 Delta Lake folder

Пример определения таблицы, созданной в папке Delta Lake:

CREATE EXTERNAL TABLE Covid (
     date_rep date,
     cases int,
     geo_id varchar(6)
) WITH (
        LOCATION = 'covid', --> the root folder containing the Delta Lake files
        data_source = DeltaLakeStorage,
        FILE_FORMAT = DeltaLakeFormat
);

Для секционированной папки невозможно создать внешние таблицы. Ознакомьтесь с другими известными проблемами на странице Самопомощь при работе с бессерверным пулом Synapse SQL.

Разностные таблицы в секционированных папках

Внешние таблицы в бессерверных пулах SQL не поддерживают секционирование в формате Delta Lake. Используйте разностные секционированные представления вместо таблиц, если вы секционировали наборы данных Delta Lake.

Важно!

Не создавайте внешние таблицы в секционированных папках Delta Lake, даже если это может работать в некоторых случаях. Использование неподдерживаемых функций, таких как внешние таблицы в секционированных разностных папках, может привести к проблемам или нестабильности бессерверного пула. Служба поддержки Azure не сможет помочь устранить проблему, если используются таблицы в секционированных папках. Перед решением проблемы вам будет предложено перейти на секционированные разностные представления и переписать код для использования только поддерживаемых функций.

Использование внешней таблицы

Внешние таблицы в запросах можно использовать так же, как и в запросах SQL Server.

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

Примечание.

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

USE [mydbname];
GO

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

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

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

Для получения информации о том, как сохранить результаты запроса в хранилище, обратитесь к статье Сохранение результатов запроса в хранилище.

Создание и использование собственных внешних таблиц с помощью пулов SQL в Azure Synapse Analytics

Необходимые компоненты

Внешняя таблица в файле

Внешняя таблица по набору файлов

Внешняя таблица для файлов, которые можно добавить

Внешняя таблица Delta Lake

Разностные таблицы в секционированных папках

Использование внешней таблицы

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

Дополнительные ресурсы