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

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

  • Статья

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

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

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

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

PolyBase не может загружать строки, содержащие более 1 000 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. Имейте в виду, что такие параметры следует использовать только для начальной загрузки, где необходимо создать дополнительные группы рабочей нагрузки с собственными конфигурациями, чтобы сбалансировано распределить ресурсы между ними.

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

Разрешение выполнения загрузки нескольким пользователям

Зачастую появляется необходимость в том, чтобы несколько пользователей могли загружать данные в хранилище данных. Для загрузки с помощью инструкции 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 требуют огромные объемы памяти для сжатия данных в группы строк высокого качества. Чтобы обеспечить лучшее сжатие и эффективность индексов, индекс columnstore должен сжать в каждую группу строк не более 1 048 576 строк. При нехватке памяти индекс columnstore не сможет добиться максимального сжатия. Это влияет на производительность запросов. Дополнительные сведения см. в статье Максимальное повышение качества группы строк для индекса columnstore.

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

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

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

Совет

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

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

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

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

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

Одноразовую загрузку в небольшую таблицу или даже периодическую перезагрузку результатов поиска рекомендуется выполнять, используя такой синтаксис инструкции INSERT: INSERT INTO MyLookup VALUES (1, 'Type 1'). Но множественные одноэлементные вставки не так эффективны, как массовая загрузка.

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

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

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

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

Для смены ключей учетной записи службы хранилища 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'

Вносить изменения в базовые внешние источники данных больше не нужно.

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