Запрос файлов Delta Lake с использованием бессерверного пула SQL в Azure Synapse Analytics
Из этой статьи вы узнаете, как написать запрос с использованием бессерверного пула Synapse SQL для чтения файлов Delta Lake. Delta Lake — это уровень хранилища с открытым кодом, который переносит транзакции ACID (атомарность, согласованность, изоляция и устойчивость) для Apache Spark и рабочих нагрузок с большими данными. Дополнительные сведения см. в видео о запросах к разностным таблицам озера.
Бессерверный пул SQL в рабочей области Synapse позволяет вам читать данные, хранящиеся в формате Delta Lake, и передавать их средствам отчетности. Бессерверный пул SQL может читать файлы Delta Lake, созданные с помощью Apache Spark, Azure Databricks или любого другого производителя формата Delta Lake.
Пулы Apache Spark в Azure Synapse позволяют инженерам данных изменять файлы Delta Lake с помощью Scala, PySpark и .NET. Бессерверные пулы SQL помогают аналитикам данных создавать отчеты по файлам Delta Lake, созданным инженерами по данным.
Важно!
Запросы к формату Delta Lake с использованием бессерверного пула SQL являются общедоступными функциональными возможностями. Однако запросы к таблицам Spark Delta по-прежнему доступны в общедоступной предварительной версии и не готовы к рабочей среде. Существуют известные проблемы, которые могут возникнуть при запросе таблиц Delta, созданных с помощью пулов Spark. Ознакомьтесь с известными проблемами, возникающими в бессерверном пуле SQL.
Пример для быстрого начала
Функция OPENROWSET позволяет вам читать содержимое файлов Delta Lake, предоставляя URL-адрес вашей корневой папки.
Прочитать папку Delta Lake
Наиболее простой способ просмотреть содержимое файла DELTA - предоставить URL-адрес файла функции OPENROWSET и указать формат DELTA. Если файл доступен в общедоступном виде или если удостоверение Microsoft Entra может получить доступ к этому файлу, вы сможете просмотреть содержимое файла с помощью запроса, как показано в следующем примере:
SELECT TOP 10 *
FROM OPENROWSET(
BULK 'https://sqlondemandstorage.blob.core.windows.net/delta-lake/covid/',
FORMAT = 'delta') as rows;
Имена столбцов и типы данных автоматически считываются из файлов Delta Lake. Функция OPENROWSET использует типы наилучшего предположения, такие как VARCHAR (1000), для строковых столбцов.
URI в функции OPENROWSET должен ссылаться на корневую папку Delta Lake, которая содержит подпапку с именем _delta_log.
Отсутствие данной подпапки означает, что вы не используете формат Delta Lake. Вы можете преобразовать ваши простые файлы Parquet в папке в формат Delta Lake, используя следующий скрипт Apache Spark Python:
%%pyspark
from delta.tables import DeltaTable
deltaTable = DeltaTable.convertToDelta(spark, "parquet.`abfss://delta-lake@sqlondemandstorage.dfs.core.windows.net/covid`")
Чтобы повысить производительность ваших запросов, рассмотрите возможность указания явных типов в предложении WITH.
Примечание.
Бессерверный пул Synapse SQL использует вывод схемы для автоматического определения столбцов и их типов. Правила вывода схемы такие же, как и для файлов Parquet. Для сопоставления типов Delta Lake с собственным типом SQL проверьте сопоставление типов для Parquet.
Убедитесь, что у вас имеется доступ к своему файлу. Если ваш файл защищен ключом SAS или пользовательским удостоверением Azure, вам потребуется настроить учетные данные на уровне сервера для входа в систему sql.
Важно!
Убедитесь, что вы используете параметры сортировки базы данных UTF-8 (например, Latin1_General_100_BIN2_UTF8), поскольку строковые значения в файлах Delta Lake кодируются с использованием кодировки UTF-8.
Несоответствие между кодировкой текста в файле Delta Lake и параметрами сортировки может вызвать непредвиденные ошибки преобразования.
Параметры сортировки по умолчанию для текущей базы данных можно легко изменить с помощью такой инструкции T-SQL: .ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Дополнительные сведения о параметрах сортировки см. в разделе "Типы сортировки", поддерживаемые для Synapse SQL.
Использование источника данных
В контексте предыдущих примеров использовался полный путь к файлу. В качестве альтернативы вы можете создать внешний источник данных с расположением, указывающим на корневую папку хранилища. После создания внешнего источника данных используйте источник данных и относительный путь к файлу в функции OPENROWSET. Таким образом, нет необходимости использовать полный абсолютный URI для ваших файлов. После этого вы также можете определить пользовательские учетные данные для доступа к месту хранения.
Важно!
Источники данных можно создать только в пользовательских базах данных (не в базе данных master или базах данных, реплицированных из пулов Apache Spark).
Чтобы использовать нижеприведенные образцы, необходимо выполнить следующее:
- Создайте базу данных с источником данных, который ссылается на учетную запись хранения NYC Yellow Taxi.
- Инициализируйте объекты, выполнив сценарий установки в базе данных, созданной на шаге 1. Этот сценарий установки создает источники данных, учетные данные области базы данных и форматы внешних файлов, которые используются в этих примерах.
Если вы создали свою базу данных и переключили контекст на свою базу данных (используя оператор USE database_name или раскрывающийся список для выбора базы данных в каком-либо редакторе запросов), вы можете создать внешний источник данных, содержащий корневой URI для вашего набора данных, и использовать его для запроса Файлы Delta Lake:
CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
WITH ( LOCATION = 'https://sqlondemandstorage.blob.core.windows.net/delta-lake/' );
GO
SELECT TOP 10 *
FROM OPENROWSET(
BULK 'covid',
DATA_SOURCE = 'DeltaLakeStorage',
FORMAT = 'delta'
) as rows;
Если источник данных защищен с помощью ключа SAS или пользовательского удостоверения, можно настроить источник данных с учетными данными для базы данных.
Явное указание схемы
Функция OPENROWSET позволяет явным образом указывать, какие столбцы вы хотите считать из файла, с помощью предложения WITH:
SELECT TOP 10 *
FROM OPENROWSET(
BULK 'covid',
DATA_SOURCE = 'DeltaLakeStorage',
FORMAT = 'delta'
)
WITH ( date_rep date,
cases int,
geo_id varchar(6)
) as rows;
С явной спецификацией схемы набора результатов вы можете минимизировать размеры типов и использовать более точные типы VARCHAR (6) для строковых столбцов вместо пессимистичного VARCHAR (1000). Минимизация типов может значительно повысить степени производительности ваших запросов.
Важно!
Убедитесь, что вы явным образом указываете параметры сортировки UTF-8 (например, Latin1_General_100_BIN2_UTF8) для всех строковых столбцов в предложении WITH, или установите сопоставление UTF-8 на уровне базы данных.
Несоответствие кодировки текста в файле и параметров сортировки для строковых столбцов может привести к непредвиденным ошибкам преобразования текста.
Параметры сортировки по умолчанию для текущей базы данных можно легко изменить с помощью такой инструкции T-SQL: .alter database current collate Latin1_General_100_BIN2_UTF8Вы можете легко задать параметры сортировки для определенных типов столбцов, используя следующее определение: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8
Набор данных
В этом примере используется набор данных NYC Yellow Taxi. Исходный набор данных PARQUET преобразуется в формат DELTA, а в примерах используется версия DELTA.
Запрос по секционированным данным
Набор данных, представленный в этом примере, разделен на отдельные вложенные папки.
В отличие от Parquet, вам не нужно настраивать таргетинг на определенные разделы с помощью функции FILEPATH. OPENROWSET определит столбцы разделения в структуре папок Delta Lake и позволит вам напрямую запрашивать данные, используя данные столбцы. В этом примере показаны суммы по тарифам за год, месяц и payment_type за первые три месяца 2017 года.
SELECT
YEAR(pickup_datetime) AS year,
passenger_count,
COUNT(*) AS cnt
FROM
OPENROWSET(
BULK 'yellow',
DATA_SOURCE = 'DeltaLakeStorage',
FORMAT='DELTA'
) nyc
WHERE
nyc.year = 2017
AND nyc.month IN (1, 2, 3)
AND pickup_datetime BETWEEN CAST('1/1/2017' AS datetime) AND CAST('3/31/2017' AS datetime)
GROUP BY
passenger_count,
YEAR(pickup_datetime)
ORDER BY
YEAR(pickup_datetime),
passenger_count;
Функция OPENROWSET удалит разделы, которые не соответствуют year и month в предложении where. Указанный метод сокращения файлов/разделов значительно сократит ваш набор данных, повысит производительность и снизит стоимость запроса.
Имя папки в функции OPENROWSET (yellow в этом примере), сцепленное с использованием LOCATION в источнике данных DeltaLakeStorage, должно ссылаться на корневую папку Delta Lake с вложенной папкой _delta_log.
Отсутствие данной подпапки означает, что вы не используете формат Delta Lake. Вы можете преобразовать ваши простые файлы Parquet в папке в формат Delta Lake, используя следующий скрипт Apache Spark Python:
%%pyspark
from delta.tables import DeltaTable
deltaTable = DeltaTable.convertToDelta(spark, "parquet.`abfss://delta-lake@sqlondemandstorage.dfs.core.windows.net/yellow`", "year INT, month INT")
Второй аргумент функции DeltaTable.convertToDeltaLake представляет столбцы разделения (год и месяц), которые являются частью шаблона папки (year=*/month=* в данном примере), а также их типы.
Ограничения
- Ознакомьтесь с ограничениями и известными проблемами на странице Самопомощь при использовании бессерверного пула SQL в Synapse.
Следующие шаги
Перейдите к следующей статье, чтобы узнать, как запросить вложенные типы Parquet. Если вы хотите продолжить создание решения Delta Lake, узнайте, как создавать представления или внешние таблицы в папке Delta Lake.