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

преобразование «Нечеткий уточняющий запрос»

  • Статья

Область применения: среда выполнения интеграции SSIS SQL Server в Фабрика данных Azure

Преобразование «Нечеткий уточняющий запрос» выполняет задачи по очистке данных, такие как стандартизация данных, исправление данных и предоставление отсутствующих значений.

Примечание.

Дополнительные сведения о преобразовании "Нечеткий уточняющий запрос", в том числе сведения об ограничениях производительности и памяти, см. в техническом документе Преобразования "Нечеткий уточняющий запрос" и "Нечеткое группирование" в службах SQL Server Integration Services 2005.

Преобразование «Нечеткий уточняющий запрос» отличается от преобразования «Уточняющий запрос» использованием нечеткого соответствия. Преобразование «Уточняющий запрос» использует эквивалентное соединение для обнаружения совпадающих записей в ссылочной таблице. Возвращает записи хотя бы с одной совпадающей записью и возвращает записи без совпадающих записей. В то же время преобразование «Нечеткий уточняющий запрос» замечает и нечеткие соответствия, возвращая одну или несколько похожих записей из ссылочной таблицы.

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

Для данного преобразования необходим доступ к эталонному источнику данных, в котором содержатся значения, используемые для очистки и расширения входных данных. Источник ссылочных данных должен быть таблицей в базе данных SQL Server. Соответствие между значением во входном столбце и значением в ссылочной таблице может быть точным или нечетким. Однако для настройки преобразования необходимо хотя бы одно нечеткое соответствие столбца. Если необходимы только четкие соответствия, используйте преобразование «Уточняющий запрос».

Это преобразование имеет один вход и один выход.

В нечетком соответствии могут использоваться только входные столбцы с типами данных DT_WSTR и DT_STR . Точное соответствие может быть применено к столбцам любых типов данных служб DTS, кроме DT_TEXT, DT_NTEXTи DT_IMAGE. Дополнительные сведения см. в разделе Integration Services Data Types. Столбцы, которые участвуют в соединении входной и ссылочной таблиц, должны иметь совместимые типы данных. Например, допустимо присоединить столбец с типом данных DTS DT_WSTR к столбцу с типом данных nvarchar SQL Server, но недопустимо присоединить столбец с типом данных DT_WSTR к столбцу с типом данных int.

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

Объем памяти, используемой преобразованием "Нечеткий уточняющий запрос", можно задать в пользовательском свойстве MaxMemoryUsage. Можно указать число мегабайтов (МБ) или значение 0, которое позволяет преобразованию использовать динамический объем памяти в зависимости от своих потребностей и наличия доступной физической памяти. Пользовательское свойство MaxMemoryUsage можно обновить с помощью выражения свойства при загрузке пакета. Дополнительные сведения см. в разделах Выражения служб Integration Services (SSIS), Использование выражений свойств в пакетах и Пользовательские свойства преобразований.

Управление режимом нечеткого соответствия

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

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

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

Пороги подобия могут быть установлены на уровне компонентов и соединений. Порог подобия на уровне соединений доступен только тогда, когда преобразование «Нечеткий уточняющий запрос» производится между столбцами входной и ссылочной таблиц. Диапазон подобия охватывает значения от 0 до 1. Чем ближе порог к 1, тем более сходными должны быть строки и столбцы, чтобы называться дубликатами. Порог подобия определяется при помощи установки свойства MinSimilarity на уровне компонентов и соединений. Чтобы удовлетворять условиям подобия, определенным на уровне компонентов, все строки должны иметь во всех результатах поиска степень сходства, большую, чем порог подобия, определенный на уровне компонентов, или равную ему. Таким образом, невозможно определить очень близкие совпадения на уровне компонентов до тех пор, пока совпадения на уровне строк или соединений не будут столь же близки.

Каждое совпадение включает показатель сходства и показатель достоверности. Показатель сходства — это математическая мера структурного сходства между входной записью и записью, найденной преобразованием «Нечеткий уточняющий запрос» в ссылочной таблице. Показатель достоверности — это вероятность того, что полученная величина является наиболее точным совпадением с искомой величиной среди всех остальных результатов поиска в ссылочной таблице. Присвоенный записи показатель достоверности зависит от остальных найденных совпадающих записей. Например, соответствие между СПб и Санкт-Петербург обладает низкой степенью сходства независимо от других результатов поиска. Но если Санкт-Петербург является единственным результатом поиска, то показатель достоверности будет высоким. Если же и Санкт-Петербург , и СПб найдены в ссылочной таблице, то достоверность СПб будет высокой, а достоверность Санкт-Петербург будет низкой. Однако из того, что у результата поиска высокий показатель сходства может совсем не следовать, что у него высокий показатель достоверности. Например, если ищется значение Раздел 4, то результаты поиска Раздел 1, Раздел 2и Раздел 3 будут иметь высокий показатель сходства, но низкий показатель достоверности, так как остается неясным, какой из результатов является наиболее близким к искомому.

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

Иногда преобразование «Нечеткий уточняющий запрос» может не найти соответствий в ссылочной таблице. Это может произойти в том случае, если входной строкой для уточняющего запроса является одиночное короткое слово. Например, helo не совпадет с hello в ссылочной таблице, если в том или каком-нибудь другом столбце строки нет других токенов.

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

  • _Similarity— столбец, содержащий показатели сходства между значениями во входном и ссылочном столбце.

  • _Confidence— столбец, содержащий показатели достоверности соответствий.

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

Выполнение преобразования «Нечеткий уточняющий запрос»

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

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

Если преобразование сохраняет индекс соответствия, этот индекс может поддерживаться автоматически. Это означает, что каждый раз при обновлении записей в ссылочной таблице индекс соответствия также будет обновляться. Поддержка индекса соответствия может уменьшить время обработки, так как исчезает необходимость перестройки индекса во время запуска пакета. Можно определить способ, как преобразование управляет индексом соответствия.

В следующей таблице приводятся описания параметров индекса соответствия.

Вариант Описание
GenerateAndMaintainNewIndex Создает новый индекс, сохраняет и поддерживает его. Преобразование устанавливает триггеры на ссылочную таблицу для синхронизации ссылочной таблицы и таблицы индекса.
GenerateAndPersistNewIndex Создает и сохраняет новый индекс, но не поддерживает его.
GenerateNewIndex Создает новый индекс, но не сохраняет его.
ReuseExistingIndex Повторно использует уже существующий индекс.

Поддержка таблицы индексов соответствия

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

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

Команда SQL TRUNCATE TABLE не запускает триггеры DELETE. Если ссылочная таблица была обработана командой TRUNCATE TABLE, то синхронизация между ссылочной таблицей и индексом соответствия нарушается и преобразование «Нечеткий уточняющий запрос» перестает работать. Если триггеры, поддерживающие таблицу индекса соответствия, установлены на ссылочную таблицу, необходимо использовать команду SQL DELETE вместо команды TRUNCATE TABLE.

Примечание.

Если выбрать параметр Поддерживать хранимый индекс в редакторе преобразования «Нечеткий уточняющий запрос» на вкладке Ссылочная таблица, представление будет поддерживать хранимый индекс с помощью управляемых хранимых процедур. Эти управляемые хранимые процедуры используют функцию интеграции среды CLR в SQL Server. По умолчанию интеграция СРЕДЫ CLR в SQL Server не включена. Чтобы использовать функцию Поддерживать хранимый индекс , необходимо включить интеграцию со средой CLR. Дополнительные сведения см. в статье Enabling CLR Integration.

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

Сравнение строк

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

Если свойство Exhaustive имеет значение False, преобразование "Нечеткий уточняющий запрос" возвращает только результаты, в которых есть, по крайней мере, один индексированный токен или подстрока (подстрока называется q-gram), похожая на входную запись. Для повышения эффективности уточняющих запросов в инвертированном индексе, используемом преобразованием «Нечеткий уточняющий запрос» для поиска совпадений, индексируется только подмножество токенов в каждой строке таблицы. Если входных данных мало, свойству Exhaustive можно задать значение True , чтобы среди результирующих совпадений были и те, для которых в таблице индексов нет похожих токенов.

Кэширование индексов и ссылочных таблиц

При настройке преобразования «Нечеткий уточняющий запрос» можно определить, будет ли преобразование производить частичное кэширование индексов и ссылочных таблиц перед началом работы. Если свойству WarmCaches присвоено значение True, то индексы и ссылочная таблица будут загружены в память. Когда на вход подается большое количество строк, присвоением свойству WarmCaches значения True можно добиться увеличения производительности преобразования. Когда на вход подается небольшое количество строк, присвоением свойству WarmCaches значения False можно ускорить процесс повторного использования больших индексов.

Временные таблицы и индексы

Во время выполнения преобразование "Нечеткий поиск" создает временные объекты, такие как таблицы и индексы, в базе данных SQL Server, к которым подключается преобразование. Размер временных таблиц и индексов пропорционален числу строк и токенов в ссылочной таблице и числу токенов, которые создает преобразование «Нечеткий уточняющий запрос», поэтому они потенциально могут занимать существенный объем места на диске. Также преобразование выполняет запросы к этим временным таблицам. Поэтому следует рассмотреть возможность подключения преобразования "Нечеткий поиск" к нерабочему экземпляру базы данных SQL Server, особенно если рабочий сервер имеет ограниченное место на диске.

Производительность этого преобразования может повышаться путем размещения используемых им таблиц и индексов на локальном компьютере. Если используемая преобразованием «Нечеткий уточняющий запрос» ссылочная таблица находится на рабочем сервере, необходимо рассмотреть возможность копирования таблицы на отладочный сервер и настройки доступа преобразования «Нечеткий уточняющий поиск» к этой копии. С помощью этого можно предотвратить использование ресурсов рабочего сервера запросами поиска. Кроме того, если преобразование "Нечеткий уточняющий запрос" поддерживает индекс соответствия, то есть если свойству MatchIndexOptionsis присвоено значение GenerateAndMaintainNewIndex, то преобразование может заблокировать ссылочную таблицу на время проведения операции по очистке данных и предотвратить доступ к ней других пользователей и приложений.

Настройка преобразования «Нечеткий уточняющий запрос»

Свойства могут быть заданы с помощью конструктора SSIS или программным путем.

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

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

Редактор преобразования «Нечеткий уточняющий запрос» (вкладка «Ссылочная таблица»)

Вкладка Ссылочная таблица в диалоговом окне Редактор преобразования «Нечеткий уточняющий запрос» позволяет указать исходную таблицу и индекс поиска. Источник ссылочных данных должен быть таблицей в базе данных SQL Server.

Примечание.

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

Примечание.

Свойства Exhaustive и MaxMemoryUsage преобразования «Нечеткий уточняющий запрос» недоступны в диалоговом окне Редактор преобразования «Нечеткий уточняющий запрос», но могут быть заданы с помощью диалогового окна Расширенный редактор. К тому же значения параметра MaxOutputMatchesPerInput больше 100 могут быть заданы только в окне Расширенный редактор. Дополнительные сведения об этих свойствах см. в подразделе «Преобразование "Нечеткий уточняющий запрос"» раздела Transformation Custom Properties.

Параметры

Диспетчер соединений OLE DB
Выберите существующий диспетчер соединений OLE DB из списка или создайте новое подключение, выбрав пункт Создать.

Новый
Создайте новое соединение с помощью диалогового окна Настройка диспетчера соединений OLE DB .

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

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

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

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

Поддерживать хранимый индекс
Если вы решили сохранить новый индекс подстановки, укажите, следует ли поддерживать индекс SQL Server.

Примечание.

Если выбрать параметр Поддерживать хранимый индекс в редакторе преобразования «Нечеткий уточняющий запрос» на вкладке Ссылочная таблица, представление будет поддерживать хранимый индекс с помощью управляемых хранимых процедур. Эти управляемые хранимые процедуры используют функцию интеграции среды CLR в SQL Server. По умолчанию интеграция СРЕДЫ CLR в SQL Server не включена. Чтобы использовать функцию Поддерживать хранимый индекс , необходимо включить интеграцию со средой CLR. Дополнительные сведения см. в статье Enabling CLR Integration.

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

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

Имя существующего индекса
Выберите из списка ранее созданный индекс уточняющего запроса.

Редактор преобразования «Нечеткий уточняющий запрос» (вкладка «Столбцы»)

Вкладка Столбцы диалогового окна Редактор преобразования «Нечеткий уточняющий запрос» используется для установки свойств входных и выходных столбцов.

Параметры

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

Имя
Просмотрите имена доступных входных столбцов.

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

Доступные уточняющие столбцы
С помощью флажков выберите столбцы, по которым выполняется нечеткий уточняющий запрос.

Столбец подстановки
Выберите уточняющие столбцы из списка доступных столбцов в ссылочной таблице. Выбор отражается установкой флажков в таблице Доступные столбцы подстановок . При выборе столбца в таблице Доступные столбцы подстановок создается выходной столбец, содержащий значение столбца ссылочной таблицы для каждой совпадающей возвращаемой строки.

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

Редактор преобразования «Нечеткий уточняющий запрос» (вкладка «Дополнительно»)

Вкладка Дополнительно диалогового окна Редактор преобразования «Нечеткий уточняющий запрос» позволяет задать параметры нечеткого поиска.

Параметры

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

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

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

См. также

Преобразование «Уточняющий запрос»
Преобразование «Нечеткое группирование»
Преобразования служб Integration Services