Поделиться через

Рекомендации по бессерверному пулу 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, убедитесь, что они расположены в том же регионе, где размещена рабочая область 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 Cosmos DB и бессерверный пул SQL

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

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

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

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

Подсказка

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

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

Если это возможно, вы можете подготовить файлы к повышению производительности:

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

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

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

Запрос CSV-файлов с помощью PARSER_VERSION 2.0

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

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

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

Оптимизация Delta Lake

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

Оптимизация контрольных точек

Производительность запросов в формате Delta Lake зависит от количества JSON-файлов в каталоге _delta_log. Чтобы обеспечить оптимальную производительность, избегайте накопления слишком большого количества JSON-файлов. В идеале журнал должен содержать только последний файл контрольной точки Parquet без дополнительных JSON-файлов. Однако эта настройка может оказаться не оптимальной для рабочих нагрузок с высокой нагрузкой на запись.

Сбалансированный подход — сохранять около 10 JSON-файлов между контрольными заданиями, что обычно обеспечивает хорошую производительность как для читателей, так и для записывающих устройств. Следите за конфигурациями, которые задерживают создание контрольной точки, так как они могут привести к чрезмерному накоплению JSON-файла и снижению производительности запросов.

Задайте следующее свойство таблицы, чтобы убедиться, что контрольная точка создается после каждых 10 файлов журнала JSON:

ALTER TABLE tableName SET TBLPROPERTIES ('delta.checkpointInterval' = '10')

Типы данных

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

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

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

  • Используйте наименьший размер данных, который может соответствовать максимально возможному значению.
    • Если максимальная длина символа составляет 30 символов, используйте тип данных символов длиной 30.
    • Если все значения столбцов символов имеют фиксированный размер, используйте char или nchar. В противном случае используйте varchar или nvarchar.
    • Если максимальное целочисленное значение столбца равно 500, используйте smallint , так как это самый маленький тип данных, который может соответствовать этому значению. Дополнительные сведения см. в разделе Диапазоны целых типов данных.
  • По возможности используйте varchar и char вместо nvarchar и nchar.
    • Используйте тип varchar с некоторыми параметрами сортировки UTF8, если вы считываете данные из Parquet, Azure Cosmos DB, Delta Lake или CSV с кодировкой UTF-8.
    • Используйте тип varchar без сортировки UTF8, если вы считываете данные из CSV-файлов, отличных от Юникода (например, ASCII).
    • Используйте тип nvarchar , если вы считываете данные из CSV-файла UTF-16.
  • Если это возможно, используйте типы данных на основе целых чисел. Операции 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';

Ниже приведен результирующий набор:

является скрытым порядковый_номер_столбца имя system_type_name максимальная_длина
0 1 идентификатор_поставщика varchar(8000) 8 000
0 2 дата_время_забора datetime2(7) 8
0 3 Количество пассажиров инт 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 см. в примерах запросов к определенным файлам.

Подсказка

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

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

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

Используйте правильную сортировку для применения pushdown предикатов в символьных столбцах.

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

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

Дальнейшие шаги