Настройка базы данных SQL в операции копирования (предварительный просмотр)

В этой статье описывается, как использовать действие копирования в конвейере данных для копирования данных из базы данных SQL и из нее.

Поддерживаемая конфигурация

Для настройки каждой вкладки в действии копирования перейдите к следующим разделам соответственно.

• Общие
• источник
• Назначение
• Сопоставление
• Настройки

Общее

Сведения о настройке вкладки "Общие" "Общие" см. в руководстве по настройке вкладки параметров "Общие".

Источник

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

Следующие свойства обязательны:

• подключение. Выберите существующую базу данных SQL , ссылающуюся на этот шаг статьи.

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

  • таблица. Укажите имя базы данных SQL для чтения данных. Выберите существующую таблицу из раскрывающегося списка или выберите Введите вручную, чтобы ввести имя схемы и таблицы.

  • Запроса: определите настраиваемый SQL-запрос для чтения данных. Примером является select * from MyTable. Или щелкните значок карандаша для редактирования в редакторе кода.

    

  • хранимая процедура: выберите хранимую процедуру из раскрывающегося списка.

В разделе Advancedможно указать следующие поля:

• время ожидания запроса (минуты): укажите время ожидания для выполнения команды запроса, значение по умолчанию — 120 минут. Если для этого свойства задан параметр, допустимые значения имеют интервал времени, например "02:00:00" (120 минут).

  

• уровень изоляции: указывает поведение блокировки транзакций для источника SQL. Допустимые значения: чтение с фиксацией, чтение без фиксации, повторяемое чтение, сериализуемоеили снимок. В разделе IsolationLevel Enum см. дополнительные сведения.

  

• Параметр раздела: Укажите параметры разделения данных, используемые для загрузки данных из базы данных SQL. Допустимые значения: Нет (по умолчанию), Физические секции таблицыи Динамический диапазон. Если опция секции включена (то есть не Нет), степень параллелизма для параллельной загрузки данных из базы данных SQL управляется Степень параллелизма копирования на вкладке настроек действия копирования.

  • Нет: выберите этот параметр, чтобы не использовать секцию.

  • Физические разделы таблицы: при использовании физического раздела столбец раздела и механизм автоматически определяются на основе определения физической таблицы.

  • динамический диапазон: при использовании запроса с включенной параллельной обработкой требуется параметр разбиения диапазона (?DfDynamicRangePartitionCondition). Пример запроса: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition.

    • имя столбца раздела: укажите имя исходного столбца в целочисленном или типа date/datetime (int, smallint, bigint, date, smalldatetime, datetime, datetime2или datetimeoffset), используемого секционированием диапазона для параллельного копирования. Если он не указан, индекс или первичный ключ таблицы обнаруживаются автоматически и используются в качестве столбца секционирования.

      Если вы используете запрос для получения исходных данных, вставьте ?DfDynamicRangePartitionCondition в предложение WHERE. Пример см. в разделе Параллельное копирование из базы данных SQL.

    • верхние границы секции: укажите максимальное значение столбца секционирования для разделения диапазона секций. Это значение используется для определения шага раздела, а не для фильтрации строк в таблице. Все строки в таблице или результатах запроса будут секционированы и скопированы. Если значение не указано, действие копирования автоматически обнаруживает значение. Пример см. в разделе Параллельное копирование из базы данных SQL.

    • Нижняя граница секции: укажите минимальное значение столбца секционирования для деления диапазона секций. Это значение используется для определения шага раздела, а не для фильтрации строк в таблице. Все строки в таблице или результатах запроса будут секционированы и скопированы. Если значение не указано, действие копирования автоматически обнаруживает значение. Пример см. в разделе Параллельное копирование из базы данных SQL.

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

Назначение

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

Следующие свойства обязательны:

• Подключение: выберите существующую базу данных SQL , ссылаясь на шаг в этой статье .

• опция таблицы: выберите использовать существующую или автоматически создать таблицу.

  • Если выбрать Использовать существующие:

    • таблица. Укажите имя базы данных SQL для записи данных. Выберите существующую таблицу из раскрывающегося списка или выберите Введите вручную, чтобы ввести имя схемы и таблицы.

  • Если выбрать автоматическое создание таблицы:

    • таблица: она автоматически создает таблицу (если она отсутствует) в исходной схеме, которая не поддерживается при использовании хранимой процедуры в качестве поведения записи.

В разделе Advancedможно указать следующие поля:

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

  

  • Вставка: выберите этот параметр, если в исходных данных есть вставки.

  • Upsert: выберите этот параметр, если исходные данные имеют как вставки, так и обновления.

    • Использовать TempDB. Укажите, следует ли использовать глобальную временную таблицу или физическую таблицу в качестве промежуточной таблицы для операции upsert. По умолчанию служба использует глобальную временную таблицу в качестве промежуточной таблицы, а этот флажок установлен.
      При записи большого объема данных в базу данных SQL снимите этот флажок и укажите имя схемы, в котором фабрика данных создаст промежуточную таблицу для загрузки данных вверх и автоматической очистки после завершения. Убедитесь, что у пользователя есть разрешение на создание таблицы в базе данных и разрешение на изменение схемы. Если это не указано, глобальная временная таблица используется в качестве промежуточной.

      

    • Выбор схемы базы данных пользователя. Если Use TempDB не выбран, укажите имя схемы, под которым Data Factory создаст промежуточную таблицу для загрузки входящих данных и автоматически удалит их после завершения. Убедитесь, что у вас есть разрешение на таблицу в базе данных и измените разрешение на схему.

      Заметка

      Необходимо иметь разрешение на создание и удаление таблиц. По умолчанию промежуточная таблица будет иметь ту же схему, что и целевая таблица.

      

    • ключевые столбцы: выберите, какой столбец используется для определения того, соответствует ли строка из источника строке из назначения.

  • имя хранимой процедуры: выберите хранимую процедуру из раскрывающегося списка.

• блокировка таблицы при массовой вставке: выберите Да или Нет. Используйте эту настройку, чтобы улучшить производительность копирования во время массовой вставки данных в таблицу без индекса из нескольких клиентов. Дополнительные сведения см. в BULK INSERT (Transact-SQL)

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

• время ожидания пакетной записи: укажите время ожидания завершения операции пакетной вставки перед истечением тайм-аута. Допустимое значение — интервал времени. Значение по умолчанию — "00:30:00" (30 минут).

• размер пакета записи данных: укажите количество строк для вставки в таблицу SQL в каждом пакете. Допустимое значение — целое число (число строк). По умолчанию служба динамически определяет соответствующий размер пакета на основе размера строки.

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

Отображение

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

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

Например, тип идентификатора столбца в источнике — int, и его можно изменить на тип float при сопоставлении с целевым столбцом.

Параметры

Для настройки вкладки перейдите к Настройка других параметров на вкладке "Параметры".

Параллельная копия из базы данных SQL

Коннектор базы данных SQL в операции копирования обеспечивает встроенное секционирование данных для параллельного копирования. Параметры секционирования данных можно найти на вкладке Источник действия копирования.

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

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

Сценарий	Рекомендуемые параметры
Полная загрузка из большой таблицы с физическими разделами.	параметр раздела: физические разделы таблицы. Во время выполнения служба автоматически обнаруживает физические секции и копирует данные по секциям. Чтобы проверить, имеет ли таблица физическое разделение или нет, можно обратиться к этому запросу .
Полная загрузка из большой таблицы без физических секций, при этом используются столбцы с целыми числами или значениями datetime для секционирования данных.	Варианты разделов: Динамическое разбиение диапазона. Столбец секционирования (необязательно): Укажите столбец, используемый для секционирования данных. Если он не указан, используется индекс или столбец первичного ключа. верхняя граница раздела и нижняя граница раздела (необязательно): укажите, нужно ли определить шаг раздела. Это не для фильтрации строк в таблице, все строки в таблице будут секционированы и скопированы. Если не указано, действие копирования автоматически обнаруживает значения и может занять много времени в зависимости от значений MIN и MAX. Рекомендуется указать верхнюю и нижнюю границу. Например, если столбец разбиения "ID" имеет значения от 1 до 100, и вы установили нижнюю границу на 20, а верхнюю на 80, при параллельной копии равной 4, служба извлекает данные по 4 разделам: <= 20, [21, 50], [51, 80], и >= 81 соответственно.
Загрузка большого объема данных с помощью настраиваемого запроса без физических секций в то время как с целым числом или столбцом date/datetime для секционирования данных.	Опции раздела: Опции динамического диапазона. Запрос: . столбце секционирования: укажите столбец, используемый для секционирования данных. верхняя граница разбиения и нижняя граница разбиения (необязательно): укажите, нужно ли определить шаг разбиения. Это не для фильтрации строк в таблице, все строки в результатах запроса будут секционированы и скопированы. Если значение не указано, действие копирования автоматически обнаруживает значение. Например, если ваш столбец секционирования 'ID' имеет значения от 1 до 100, и вы установили нижнюю границу как 20, а верхнюю границу как 80, с параллельной обработкой в 4, то служба извлекает данные по 4 разделам — идентификаторы в диапазоне <= 20, [21, 50], [51, 80], и >= 81 соответственно. Ниже приведены дополнительные примеры запросов для различных сценариев: • Запросите всю таблицу: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition • Запрос из таблицы с выбором столбцов и дополнительными условиями where: SELECT <column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause> • Запрос с вложенными запросами: SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause> • Запрос с разделом в подзапросе: SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition) AS T

Лучшие практики загрузки данных с опцией разделения:

• Выберите отличительный столбец в качестве столбца секционирования (например, первичный ключ или уникальный ключ), чтобы избежать отклонения данных.
• Если у таблицы есть встроенный раздел, используйте параметр раздела физические разделы таблицы для повышения производительности.

Пример запроса для проверки физического раздела

SELECT DISTINCT s.name AS SchemaName, t.name AS TableName, pf.name AS PartitionFunctionName, c.name AS ColumnName, iif(pf.name is null, 'no', 'yes') AS HasPartition
FROM sys.tables AS t
LEFT JOIN sys.objects AS o ON t.object_id = o.object_id
LEFT JOIN sys.schemas AS s ON o.schema_id = s.schema_id
LEFT JOIN sys.indexes AS i ON t.object_id = i.object_id 
LEFT JOIN sys.index_columns AS ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id 
LEFT JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id 
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id 
LEFT JOIN sys.partition_functions pf ON pf.function_id = ps.function_id 
WHERE s.name='[your schema]' AND t.name = '[your table name]'

Если в таблице есть физическая секция, вы увидите HasPartition как "да" как показано ниже.

Сводка таблицы

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

Источник

Имя	Описание	Ценность	Обязательно	Свойство скрипта JSON
подключение	Подключение к исходному хранилищу данных.	<ваше подключение>	Да	связь
Использовать запрос	Способ чтения данных. Примените таблицу для чтения данных из указанной таблицы или применения запроса для чтения данных с помощью запросов SQL.	• таблица • запроса • хранимая процедура	Да	/
для таблицы
имя схемы	Имя схемы.	< имя схемы >	Нет	схема
название таблицы	Имя таблицы.	< имя вашей таблицы >	Нет	стол
для запроса
запроса	Укажите настраиваемый SQL-запрос для чтения данных. Например, SELECT * FROM MyTable.	< sql-запросы >	Нет	sqlReaderQuery
Для хранимой процедуры
имя хранимой процедуры	Имя хранимой процедуры.	< имя вашей хранимой процедуры >	Нет	sqlReaderStoredProcedureName
время ожидания запроса (минуты)	Время ожидания выполнения команды запроса по умолчанию — 120 минут. Если для этого свойства задан параметр, допустимые значения имеют интервал времени, например "02:00:00" (120 минут).	интервал времени	Нет	время ожидания запроса
уровне изоляции	Указывает поведение блокировки транзакций для источника SQL.	• Чтение с фиксацией • Чтение незакоммиченных данных • Повторяемое чтение •Сериализуемый •Снимок	Нет	уровень изоляции • Read Committed (чтение с фиксацией) • ReadUncommitted (чтение без фиксации) • RepeatableRead •Сериализуемый •Снимок
параметра секции	Параметры секционирования данных, используемые для загрузки данных из базы данных SQL.	• Нет • Физические разделы таблицы •Динамический диапазон	Нет	опция разделения • ФизическиеРазделыТаблицы • DynamicRange
Для динамического диапазона
имени столбца секции	Имя исходного столбца типа целое число или дата/дата и время (int, smallint, bigint, date, smalldatetime, datetime, datetime2или datetimeoffset), которое используется для диапазонного секционирования при параллельном копировании. Если он не указан, индекс или первичный ключ таблицы обнаруживаются автоматически и используются в качестве столбца секционирования. Если вы используете запрос для получения исходных данных, подключите ?DfDynamicRangePartitionCondition в условии WHERE.	< имена столбцов секционирования >	Нет	partitionColumnName
верхняя граница разбиения	Максимальное значение столбца разделения для разбиения диапазона. Это значение используется для определения шага разбиения, а не для фильтрации строк в таблице. Все строки в таблице или результатах запроса будут секционированы и скопированы. Если значение не указано, действие копирования автоматически обнаруживает значение.	< верхняя граница раздела >	Нет	верхняя граница раздела
нижней границы раздела	Минимальное значение столбца секционирования для разделения диапазона секционирования. Это значение используется для определения шага разбиения, а не для фильтрации строк в таблице. Все строки в таблице или результатах запроса будут секционированы и скопированы. Если значение не указано, действие копирования автоматически обнаруживает значение.	< нижняя граница раздела >	Нет	partitionLowerBound
Дополнительные столбцы	Добавьте дополнительные столбцы данных для хранения относительного пути или статического значения исходных файлов. Выражение поддерживается для последнего.	•Имя •Ценность	Нет	дополнительныеСтолбцы •имя •ценность

Назначение

Имя	Описание	Ценность	Обязательно	Свойство скрипта JSON
Подключение	Подключение к целевому хранилищу данных.	<ваше подключение >	Да	связь
параметр таблицы	Таблица данных назначения. Выберите Использовать существующие или автоматическое создание таблицы.	Используйте существующие • Автоматическая создание таблицы	Да	схема стол
Поведение при записи	Определяет поведение записи, когда источник является файлами из файлового хранилища данных.	•Вставка • обновление или добавление записи • Хранимая процедура	Нет	writeBehavior: •вставить • обновление или вставка • sqlWriterStoredProcedureName
блокировка таблицы при массовой вставке	Используйте этот параметр, чтобы повысить производительность копирования во время операции массовой вставки в таблицу без индекса от нескольких клиентов.	Да или нет (по умолчанию)	Нет	sqlWriterUseTableLock: true или false (по умолчанию)
Для вставки или обновления
Использовать TempDB	Следует ли использовать глобальную временную или физическую таблицу в качестве промежуточной таблицы для upsert.	выбрано (по умолчанию) или не выбрано	Нет	useTempDB: true (по умолчанию) или false
ключевые столбцы	Выберите столбец, используемый для определения того, соответствует ли строка из источника строке из назначения.	< вашей ключевой колонки>	Нет	Ключи
Для хранимой процедуры
имя хранимой процедуры	Это свойство — имя хранимой процедуры, которая считывает данные из исходной таблицы. Последняя инструкция SQL должна быть инструкцией SELECT в хранимой процедуре.	< имя хранимой процедуры >	Нет	имя_хранимой_процедуры_sqlWriter
сценарий предварительного копирования	Скрипт для выполнения действия копирования перед записью данных в целевую таблицу каждого запуска. Это свойство можно использовать для очистки предварительно загруженных данных.	<предкопировочный скрипт> (строка)	Нет	PreCopyScript
время ожидания выполнения пакета	Время ожидания завершения операции пакетной вставки до истечения таймаута. Допустимое значение — интервал времени. Значение по умолчанию — "00:30:00" (30 минут).	интервал времени	Нет	writeBatchTimeout
размер пакета записи	Количество строк для вставки в таблицу SQL на пакет. По умолчанию служба динамически определяет соответствующий размер пакета на основе размера строки.	<количество строк> (целое число)	Нет	writeBatchSize
Максимальное число одновременных подключений	Верхний предел одновременных подключений, установленных для хранилища данных во время выполнения действия. Укажите значение только в том случае, если требуется ограничить одновременные подключения.	<верхний предел одновременных подключений> (целое число)	Нет	максимальное количество одновременных подключений

Связанное содержимое

• Обзор базы данных SQL