Преобразование «Нечеткий поиск»

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

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

Примечание.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Команда SQL TRUNCATE TABLE не вызывает DELETE триггеры. Если команда TRUNCATE TABLE используется для эталонной таблицы, эталонная таблица и индекс совпадений больше не будут синхронизированы, а преобразование «Нечеткий поиск» завершается с ошибкой. Хотя триггеры, поддерживающие таблицу индекса соответствия, устанавливаются в эталонной таблице, вместо DELETE команды следует использовать команду SQLTRUNCATE 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, особенно если рабочий сервер имеет ограниченное место на диске.

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

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

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

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

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

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

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

Примечание.

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

Примечание.

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

Параметры

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

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

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

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

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

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

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

Примечание.

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

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

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

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

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

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

Параметры

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

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

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

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

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

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

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

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

Параметры

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

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

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

Связанные материалы

  • Last updated on