Рекомендации по использованию бессерверного пула SQL в Azure Synapse Analytics

  • Статья

В этой статье можно найти набор рекомендаций по использованию бессерверного пула SQL. Бессерверный пул SQL – это ресурс в Azure Synapse Analytics. При работе с выделенным пулом SQL см. конкретные рекомендации в статье Рекомендации по работе с выделенными пулами SQL.

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

Ниже приведены некоторые общие рекомендации.

  • Убедитесь, что клиентские приложения размещены в бессерверном пуле SQL.
    • Если вы используете клиентские приложения за пределами Azure, убедитесь, что вы используете бессерверный пул SQL в регионе, близком к клиентскому компьютеру. Примеры клиентских приложений включают Power BI Desktop, SQL Server Management Studio и Azure Data Studio.
  • Убедитесь, что хранилище и бессерверный пул SQL находятся в одном регионе. Примеры хранилищ включают Azure Data Lake Storage и Azure Cosmos DB.
  • Попробуйте оптимизировать структуру хранилища, используя секционирование и сохраняя файлы размером от 100 Мб до 10 Гб.
  • Если возвращается большое количество результатов, убедитесь, что вы используете SQL Server Management Studio или Azure Data Studio, а не Azure Synapse Studio. Azure Synapse Studio — это веб-инструмент, который не предназначен для обработки больших наборов результатов.
  • Если выполняется фильтрация результатов по столбцу со строками, попробуйте использовать параметры сортировки BIN2_UTF8. Дополнительные сведения об изменении параметров сортировки см. в разделе "Типы сортировки", поддерживаемые для Synapse SQL.
  • Попробуйте выполнить кэширование результатов на стороне клиента с помощью режима импорта Power BI или служб Azure Analysis Services и периодически выполнять их обновление. Бессерверные пулы SQL не могут обеспечить интерактивность в режиме прямых запросов Power BI, если используются сложные запросы или обрабатывается большой объем данных.
  • Максимальная параллелизм не ограничена и зависит от сложности запроса и объема отсканированных данных. Один бессерверный пул SQL может одновременно обрабатывать 1000 активных сеансов, которые выполняют простые запросы. Но этот показатель падает, если запросы усложняются или сканируют больший объем данных. В таком случае рекомендуется сократить параллелизм и выполнить запросы в течение более длительного периода времени (если это возможно).

Клиентские приложения и сетевые подключения

Убедитесь, что клиентское приложение подключено к ближайшей возможной рабочей области Azure Synapse с применением оптимальных параметров.

  • Расположите клиентское приложение вместе с рабочей областью Azure Synapse. Если используются такие приложения, как Power BI или Azure Analysis Service, убедитесь, что они находятся в том же регионе, в котором располагается используемая рабочая область Azure Synapse. При необходимости можно создать отдельные рабочие области, связанные с клиентскими приложениями. Расположение клиентского приложения и рабочей области Azure Synapse в другом регионе может привести к увеличению задержки и замедлению потоковой передачи результатов.
  • При чтении данных из локального приложения убедитесь, что рабочая область Azure Synapse находится в регионе, близком к текущему расположению пользователя.
  • Убедитесь, что нет проблем с пропускной способностью сети при чтении большого объема данных.
  • Не используйте Azure Synapse Studio для возврата большого объема данных. Azure Synapse Studio – это веб-инструмент, использующий для передачи данных протокол HTTPS. Используйте Azure Data Studio или SQL Server Management Studio для чтения большого объема данных.

Хранилище и компоновка содержимого

Ниже приведены рекомендации по использованию хранилища и компоновки содержимого в бессерверном пуле SQL.

Расположите используемое хранилище вместе с бессерверным пулом SQL

Чтобы минимизировать задержку, расположите учетную запись службы хранилища Azure или аналитическое хранилище Azure Cosmos DB вместе с конечной точкой бессерверного пула SQL. Учетные записи хранения и конечные точки, подготовленные во время создания рабочей области, расположены в одном регионе.

Для обеспечения оптимальной производительности, если пользователь имеет доступ к другим учетным записям хранения с использованием бессерверного пула SQL, убедитесь, что они находятся в одном регионе. Если они не расположены в одном регионе, будет увеличена задержка сетевой передачи данных между удаленным регионом и регионом конечной точки.

Регулирование службы хранилища Azure

Несколько приложений и служб могут получить доступ к вашей учетной записи хранения. Регулирование хранилища происходит, когда совокупное количество операций ввода-вывода в секунду или пропускная способность, создаваемая приложениями, службами и рабочими нагрузками бессерверного пула SQL, выходит за пределы ограничений учетной записи хранения. В результате вы получаете значительное негативное воздействие на производительность запросов.

При обнаружении регулирования бессерверный пул SQL имеет встроенную функцию обработки для решения этой проблемы. Бессерверный пул SQL отправляет запросы в хранилище медленнее до тех пор, пока не решится проблема регулирования.

Совет

Для оптимального выполнения запросов не следует усложнять работу учетной записи хранения другими рабочими нагрузками во время выполнения запроса.

Подготовка файлов к запросам

По возможности вы можете подготовить файлы для повышения производительности:

  • Преобразуйте большие CSV- и JSON-файлы в формат Parquet. Parquet — это формат столбцов. Благодаря сжатию файлы в этом формате меньше по размеру, чем CSV- или JSON-файлы с теми же данными. При чтении файлов Parquet бессерверный пул SQL пропускает столбцы и строки, которые не нужны в запросе. Бессерверному пулу SQL требуется меньше времени и меньше запросов к хранилищу для чтения.
  • Если запрос предназначен для одного большого файла, рекомендуется разделить его на несколько файлов меньшего размера.
  • Постарайтесь, чтобы размер файла CSV составлял от 100 МБ до 10 ГБ.
  • Лучше иметь одинаковый размер файлов для одного пути OPENROWSET или для РАСПОЛОЖЕНИЯ внешней таблицы.
  • Разделите данные, сохранив разделы в разных папках или файлах. См. раздел Использование функций fileinfo и filepath для назначения конкретных разделов.

Расположение аналитического хранилища Azure Cosmos DB вместе с бессерверным пулом SQL

Убедитесь, что аналитическое хранилище Azure Cosmos DB располагается в том же регионе, что и рабочая область Azure Synapse. Межрегиональные запросы могут приводить к большим задержкам. Используйте свойство региона в строке подключения, чтобы явно указать регион, в котором располагается аналитическое хранилище (см. статью Запрос Azure Cosmos DB с использованием бессерверного пула SQL): account=<database account name>;database=<database name>;region=<region name>'

Оптимизация CSV

Ниже приведены рекомендации по использованию CSV-файлов в бессерверном пуле SQL.

Использование PARSER_VERSION 2.0 для запросов к CSV-файлам

При запросах к CSV-файлам можно использовать оптимизированное для высокой производительности средство синтаксического анализа. Дополнительные сведения см. в статье о PARSER_VERSION.

Создание статистики для файлов CSV вручную

Бессерверный пул SQL использует статистику для создания оптимальных планов выполнения запросов. Статистика автоматически создается для столбцов с помощью выборки, и в большинстве случаев процент выборки будет меньше 100 %. Этот поток одинаков для каждого формата файла. Помните, что при чтении CSV-файла с синтаксического анализатора версии 1.0 выборка не поддерживается, а автоматическое создание статистики не произойдет с процентом выборки менее 100 %. Для небольших таблиц с предполагаемой низкой кратностью (число строк) автоматическое создание статистики будет активировано с процентом выборки 100 %. Это означает, что функция fullscan активируется и автоматически создаются статистические данные даже для CSV-файла с синтаксического анализатора версии 1.0. Если статистика не создается автоматически, создайте статистику вручную для столбцов, используемых в запросах, особенно тех, которые используются в DISTINCT, JOIN, WHERE, ORDER BY и GROUP BY. Дополнительные сведения см. статистику в бессерверном пуле SQL.

Типы данных

Ниже приведены рекомендации по использованию типов данных в бессерверном пуле SQL.

Использование соответствующих типов данных

Типы данных, используемые в запросе, влияют на производительность и параллелизм. Производительность можно повысить следующим образом:

  • Используйте наименьший размер данных, соответствующий максимально возможному значению.
    • Если максимальная длина символьного значения равна 30 символам, используйте символьный тип данных длиной 30 символов.
    • Если все символьные значения столбцов имеют фиксированный размер, используйте тип char или nchar. В противном случае используйте тип varchar или nvarchar.
    • Если максимальное значение целочисленного столбца равно 500, используйте smallint, так как это минимальный тип данных, который может соответствовать этому значению. Дополнительные сведения см . в диапазонах целых типов данных.
  • По возможности используйте тип varchar и char вместо nvarchar и nchar.
    • При чтении данных из файлов в формате Parquet, Azure Cosmos DB, Delta Lake или CSV с кодировкой UTF-8 используйте тип varchar с некоторыми параметрами сортировки UTF-8.
    • При чтении данных из CSV-файлов, не поддерживающих Юникод (например, ASCII), используйте тип varchar без параметров сортировки UTF-8.
    • При чтении данных из CSV-файла с кодировкой UTF-16 используйте тип nvarchar.
  • По возможности используйте целочисленные типы данных. Операции SORT, JOIN и GROUP BY выполняются быстрее на основе целых чисел, чем на основе символьных данных.
  • Если вы используете вывод схемы, проверьте выводимые типы данных и явно переопределите их с помощью меньших типов, если это возможно.

Проверка выводимых типов данных

Вывод схемы помогает быстро создавать запросы и исследовать данные, не зная схемы файлов. Единственный недостаток заключается в том, что выводимые типы данных могут быть больше фактических. Это происходит, когда в исходных файлах недостаточно сведений, чтобы обеспечить использование соответствующего типа данных. Например, файлы Parquet не содержат метаданных о максимальной длине символьного столбца, Таким образом, бессерверный пул SQL выводит данные как varchar(8000).

Имейте в виду, что ситуация может отличаться в случае общих управляемых и внешних таблиц Spark, предоставляемых в подсистеме SQL как внешние таблицы. Типы данных в таблицах Spark отличаются от типов данных в подсистемах Synapse SQL. Сопоставление типов данных таблицы Spark и типов SQL можно найти здесь.

Для проверки результирующего типа данных запроса можно использовать системную хранимую процедуру sp_describe_first_results_set .

В следующем примере показано, как оптимизировать выводимые типы данных. Эта процедура используется для отображения выводимых типов данных:

EXEC sp_describe_first_result_set N'
    SELECT
        vendor_id, pickup_datetime, passenger_count
    FROM  
        OPENROWSET(
            BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/*/*/*'',
            FORMAT=''PARQUET''
        ) AS nyc';

Вот результирующий набор:

is_hidden column_ordinal name system_type_name max_length
0 1 vendor_id varchar(8000) 8000
0 2 pickup_datetime datetime2(7) 8
0 3 passenger_count INT 4

После получения сведений о выводимых типах данных для запроса можно указать соответствующие типы данных:

SELECT
    vendorID, tpepPickupDateTime, passengerCount
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=2018/puMonth=*/*.snappy.parquet',
        FORMAT='PARQUET'
    )  
    WITH (
        vendorID varchar(4), -- we used length of 4 instead of the inferred 8000
        tpepPickupDateTime datetime2,
        passengerCount int
    ) AS nyc;

Оптимизация фильтрации

Ниже приведены рекомендации по использованию запросов в бессерверном пуле SQL.

Отправка подстановочных знаков на более низкие уровни в пути

В пути можно использовать подстановочные знаки, чтобы запрашивать несколько файлов и папок. Бессерверный пул SQL выводит список файлов в учетной записи хранения начиная с первой звездочки (*), используя API хранилища. При этом файлы, которые не соответствуют указанному пути, исключаются. Сокращение исходного списка файлов может повысить производительность, если есть много файлов, соответствующих указанному пути до первого подстановочного знака.

Использование функций filename и filepath для назначения конкретных разделов

Данные часто организованы в разделы. Можно указать, чтобы бессерверный пул SQL запрашивал определенные папки и файлы. Это сокращает количество файлов и объем данных для чтения и обработки в рамках запроса. Дополнительное преимущество — это увеличение производительности.

См. дополнительные сведения о функциях filename и filepath, а также примеры запросов к конкретным файлам.

Совет

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

Функции, используемые для устранения секций, файлового пути и имени файла, в настоящее время не поддерживаются для внешних таблиц, кроме тех, которые создаются автоматически для каждой таблицы, созданной в Apache Spark для Azure Synapse Analytics.

Если хранимые данные не секционированы, рекомендуем их секционировать. В таком случае вы сможете использовать эти функции для оптимизации запросов, предназначенных для этих файлов. При выполнении запроса к секционированным таблицам Apache Spark для Azure Synapse из бессерверного пула SQL запрос автоматически направляется только на необходимые файлы.

Использование подходящих параметров сортировки, чтобы использовать раскрытие предиката для столбцов с символами

Данные в файле Parquet организованы в группы строк. Бессерверный пул SQL пропускает группы строк на основе указанного предиката в предложении WHERE, что сокращает число операций ввода-вывода. В результате повышается производительность запросов.

Метод pushdown предиката для столбцов с символами в файлах Parquet поддерживается только для параметров сортировки Latin1_General_100_BIN2_UTF8. Можно указать параметры сортировки для определенного столбца с помощью предложения WITH. Если не указать эти параметры сортировки с помощью предложения WITH, будут использоваться параметры сортировки базы данных.

Оптимизация обработки повторяющихся запросов

Ниже приведены рекомендации по использованию CETAS в бессерверном пуле SQL.

Использование CETAS для повышения производительности и улучшения соединений запросов

CETAS — одна из наиболее важных функций, доступных в бессерверном пуле SQL. CETAS — это параллельная операция, которая создает метаданные внешней таблицы и экспортирует результаты запроса SELECT в набор файлов в учетной записи хранения.

Можно использовать CETAS для применения часто используемых частей запросов, например объединенных справочных таблиц, в новом наборе файлов. Далее можно выполнить объединение с этой отдельной внешней таблицей вместо повторного выполнения операций объединения в нескольких запросах.

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

Запрос данных Azure

Бессерверные пулы SQL позволяют запрашивать данные в хранилище Azure или Azure Cosmos DB с помощью внешних таблиц и функции OPENROWSET. Убедитесь, что в хранилище установлены необходимые настройки разрешений.

Запрос данных CSV

Дополнительные сведения о выполнении запроса к одному CSV-файлу или папкам и нескольким CSV-файлам. Вы также можете запрашивать секционированные файлы

Запрос данных Parquet

Дополнительные сведения о выполнении запроса к файлам Parquet с помощью вложенных типов. Также можно выполнять запросы к секционированным файлам.

Запросы Delta Lake

Дополнительные сведения о выполнении запроса к файлам Delta Lake с помощью вложенных типов.

Запрос данных Azure Cosmos DB

Дополнительные сведения о выполнении запроса аналитического хранилища Azure Cosmos DB. Можно использовать подключенный генератор для создания предложения WITH на основе примера документа Azure Cosmos DB. Вы можете создавать представления поверх контейнеров Azure Cosmos DB.

Запрос данных JSON

Дополнительные сведения о выполнении запроса к файлам JSON. Также можно выполнять запросы к секционированным файлам.

Создание представлений, таблиц и других объектов базы данных

Дополнительные сведения о создании и использовании представлений и внешних таблиц, или настройке безопасности на уровне строк. При наличии у вас секционированных файлов убедитесь, что вы используете секционированные представления.

Копирование и преобразование данных (CETAS)

Дополнительные сведения о хранении результатов запроса к хранилищу с помощью команды CETAS.

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