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

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

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

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

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

Предпосылки

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

• ВНЕШНИЙ ИСТОЧНИК ДАННЫХ sqlondemanddemo, ссылающийся на общедоступную демонстрационную учетную запись хранения, и ВНЕШНИЙ ИСТОЧНИК ДАННЫХ nyctlc, ссылающийся на общедоступную учетную запись хранения Azure в расположении https://azureopendatastorage.blob.core.windows.net/nyctlc/.

  CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (LOCATION = 'https://fabrictutorialdata.blob.core.windows.net/sampledata/Synapse');
  GO
  CREATE EXTERNAL DATA SOURCE nyctlc
  WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/')
  GO
  CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
  WITH ( location = 'https://fabrictutorialdata.blob.core.windows.net/sampledata/Synapse/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 из демонстрационной учетной записи хранения SynapseSQL на платформе Azure, ссылаясь на sqlondemanddemo источник данных.

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

Вы можете указать шаблон, которому должны соответствовать файлы, чтобы их можно было подключить к внешней таблице. Рисунок требуется только для паркетных и 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 serverless SQL.

Дельта-таблицы в разделенных каталогах

Внешние таблицы в безсерверных пулах SQL не поддерживают разбиение на разделы в формате Delta Lake. Используйте Delta partitioned views вместо таблиц, если у вас есть разбиенные на разделы наборы данных 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 в одном регионе.

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