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

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

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

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

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

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

Для начала создайте базу данных, в которой будут созданы таблицы. Перед созданием учетных данных для базы данных в базе данных должен находиться главный ключ для защиты учетных данных. Дополнительные сведения об этом см. в статье 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.

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

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.

Пример определения таблицы, созданной в папке 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.

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

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

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

USE [mydbname];
GO

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

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

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

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