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

Рекомендации по загрузке данных в выделенный пул SQL в Azure Synapse Analytics

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

Подготовка данных в службе хранилища Azure

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

При экспорте данных в формат файла ORC могут возникать ошибки нехватки памяти Java, если присутствуют большие текстовые столбцы. Чтобы обойти это ограничение, экспортируйте только подмножество столбцов.

PolyBase не может загружать строки, имеющие более 1000 000 байт данных. При добавлении данных в текстовые файлы в хранилище BLOB-объектов Azure или Azure Data Lake Store, объём данных должен быть не больше 1 000 000 байт. Ограничение на количество байтов действительно независимо от схемы таблицы.

Все форматы файлов имеют разные характеристики производительности. Для самой быстрой загрузки используйте сжатые текстовые файлы с разделителями. Разница между производительностью UTF-8 и UTF-16 минимальна.

Разделение больших сжатых файлов на небольшие сжатые файлы.

Запуск задач с достаточной вычислительной мощностью

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

Чтобы запустить нагрузки с соответствующими вычислительными ресурсами, создайте пользователей, предназначенных для выполнения нагрузок. Назначьте каждого пользователя загрузки в определенный класс ресурсов или группу рабочей нагрузки. Чтобы запустить загрузку, войдите в качестве одного из пользователей загрузки, а затем выполните загрузку. Загрузка выполняется с классом ресурсов пользователя. Этот метод проще, чем пытаться изменить класс ресурсов пользователя в соответствии с текущими требованиями к классу ресурсов.

Создать пользователя для загрузки системы

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

   -- Connect to master
   CREATE LOGIN loader WITH PASSWORD = 'a123STRONGpassword!';

Подключитесь к выделенному пулу SQL и создайте пользователя. В следующем коде предполагается, что вы подключены к базе данных с именем mySampleDataWarehouse. В нем показано, как создать пользователя с именем loader и предоставить пользователю разрешения на создание таблиц и загрузку с помощью инструкции COPY. Затем он классифицирует пользователя в группу рабочих нагрузок DataLoads с максимальными ресурсами.

   -- Connect to the dedicated SQL pool
   CREATE USER loader FOR LOGIN loader;
   GRANT ADMINISTER DATABASE BULK OPERATIONS TO loader;
   GRANT INSERT ON <yourtablename> TO loader;
   GRANT SELECT ON <yourtablename> TO loader;
   GRANT CREATE TABLE TO loader;
   GRANT ALTER ON SCHEMA::dbo TO loader;
   
   CREATE WORKLOAD GROUP DataLoads
   WITH ( 
       MIN_PERCENTAGE_RESOURCE = 0
       ,CAP_PERCENTAGE_RESOURCE = 100
       ,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
	);

   CREATE WORKLOAD CLASSIFIER [wgcELTLogin]
   WITH (
	     WORKLOAD_GROUP = 'DataLoads'
       ,MEMBERNAME = 'loader'
   );



Это важно

Это крайний пример выделения 100% ресурсов пула SQL для одной нагрузки. Это обеспечит максимальную параллельность из 1. Имейте в виду, что это следует использовать только для начальной нагрузки, в которой необходимо создать другие группы рабочих нагрузок с собственными конфигурациями для балансировки ресурсов между рабочими нагрузками.

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

Разрешить загрузку нескольких пользователей

Часто требуется загрузить данные в хранилище данных несколькими пользователями. Для загрузки с помощью инструкции CREATE TABLE AS SELECT (Transact-SQL) требуются разрешения CONTROL базы данных. Разрешение CONTROL обеспечивает контроль доступа ко всем схемам. Возможно, вы не хотите, чтобы все загружаемые пользователи имели контрольный доступ ко всем схемам. Чтобы ограничить разрешения, используйте инструкцию DENY CONTROL.

Например, рассмотрим схемы базы данных: schema_A для отдела A и schema_B для отдела B. Пусть пользователи базы данных user_A и user_B являются пользователями для загрузки PolyBase в отделах A и B соответственно. Оба они были предоставлены разрешения на базу данных CONTROL. Создатели схемы A и B теперь блокируют свои схемы с помощью DENY:

   DENY CONTROL ON SCHEMA :: schema_A TO user_B;
   DENY CONTROL ON SCHEMA :: schema_B TO user_A;

User_A и user_B теперь заблокированы для доступа к схеме другого отдела.

Загрузка в промежуточную таблицу

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

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

Загрузка в индекс с использованием колоночного хранилища

Индексы Columnstore требуют больших объемов памяти для сжатия данных в высококачественные группы строк. Для наилучшего сжатия и эффективности columnstore индекса необходимо сжать максимально 1 048 576 строк в каждой группе строк. При нехватке памяти индекс columnstore может не достичь максимальной скорости сжатия. Это влияет на производительность запросов. Подробные сведения см. в статье об оптимизации памяти Columnstore.

  • Чтобы гарантировать, что у пользователей загрузки достаточно памяти для достижения максимального уровня сжатия, используйте пользователей, которые являются членами среднего или большого класса ресурсов.
  • Загрузите достаточно строк, чтобы полностью заполнить новые группы строк. При массовой загрузке каждые 1048 576 строк сжимаются непосредственно в columnstore в виде полной группы строк. Загрузки, содержащие менее 102 400 строк, отправляют строки в deltastore, где они хранятся в индексе b-дерева. Если вы загружаете слишком мало строк, они могут все перейти в deltastore и не будут сразу сжаты в формат columnstore.

Увеличение размера пакета при использовании API SQLBulkCopy или BCP

Загрузка с помощью инструкции COPY обеспечивает максимальную пропускную способность с выделенными SQL-пулами. Если вы не можете использовать COPY для загрузки и должны использовать API SqLBulkCopy или bcp, рекомендуется увеличивать размер пакета для улучшения производительности.

Подсказка

Размер пакета от 100 К до 1 МЛН строк является рекомендуемой базовой базой для определения оптимальной емкости пакета.

Управление сбоями загрузки

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

Чтобы исправить грязные записи, убедитесь, что определения внешнего таблицы и внешнего формата файлов правильны, а внешние данные соответствуют этим определениям. Если подмножество внешних записей данных испорчены, вы можете отклонить эти записи в ваших запросах, используя параметры отклонения в CREATE EXTERNAL TABLE.

Вставка данных в рабочую таблицу

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

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

Создание статистики после загрузки

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

Подробное описание статистики см. в разделе "Статистика". В следующем примере показано, как вручную создать статистику по пяти столбцам таблицы Customer_Speed.

create statistics [SensorKey] on [Customer_Speed] ([SensorKey]);
create statistics [CustomerKey] on [Customer_Speed] ([CustomerKey]);
create statistics [GeographyKey] on [Customer_Speed] ([GeographyKey]);
create statistics [Speed] on [Customer_Speed] ([Speed]);
create statistics [YearMeasured] on [Customer_Speed] ([YearMeasured]);

Поворот ключей хранилища

Рекомендуется регулярно изменять ключ доступа к хранилищу BLOB. У вас есть два ключа для учетной записи Blob-хранилища, что позволяет сменить ключи.

Чтобы повернуть ключи учетной записи хранения Azure, выполните приведенные действия.

Для каждой учетной записи хранения, ключ которой изменился, выполните команду ALTER DATABASE SCOPED CREDENTIAL.

Пример:

Исходный ключ создается

CREATE DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key1'

Поворот ключа от клавиши 1 до клавиши 2

ALTER DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key2'

Никаких других изменений в базовых внешних источниках данных не требуется.

Связанный контент