Transformación Búsqueda aproximada

Artículo
05/08/2013

La Transformación Búsqueda aproximada realiza tareas de limpieza de datos como normalizar datos, corregir datos y proporcionar valores que faltan.

[!NOTA]

Para obtener información más detallada acerca de la transformación Búsqueda aproximada, incluidas las limitaciones de rendimiento y memoria, vea las notas del producto Fuzzy Lookup and Fuzzy Grouping in SQL Server Integration Services 2005.

La transformación Búsqueda aproximada difiere de la Búsqueda aproximada en su uso de coincidencia aproximada. La transformación Búsqueda utiliza una combinación de igualdad para localizar los registros que coinciden en la tabla de referencia. Devuelve los registros que tienen al menos un registro coincidente y devuelve registros que no tienen registros coincidentes. En cambio, la transformación Búsqueda aproximada emplea la coincidencia aproximada para devolver una o más coincidencias similares en la tabla de referencia.

La transformación Búsqueda aproximada suele ir después de una transformación Búsqueda en un flujo de datos de paquete. Primero, la transformación Búsqueda intenta encontrar una coincidencia exacta. Si no lo consigue, la transformación Búsqueda aproximada proporciona coincidencias similares de la tabla de referencia.

La transformación necesita acceso a un origen de datos de referencia que contenga los valores que se utilizan para limpiar y ampliar los datos de entrada. El origen de los datos de referencia debe ser una tabla de una base de datos de SQL Server. La coincidencia entre el valor en una columna de entrada y el valor en la tabla de referencia puede ser exacta o aproximada. Sin embargo, la transformación requiere que se configure al menos una coincidencia de columna para la coincidencia aproximada. Si desea buscar únicamente coincidencias exactas, utilice la transformación Búsqueda.

Esta transformación tiene una entrada y una salida.

Para la coincidencia aproximada, solo se pueden utilizar columnas de entrada con tipos de datos DT_WSTR y DT_STR. Para la búsqueda exacta se puede utilizar cualquier tipo de datos DTS, excepto DT_TEXT, DT_NTEXTy DT_IMAGE. Para obtener más información, vea Tipos de datos de Integration Services. Las columnas que participan en la combinación entre la entrada y la tabla de referencia deben tener tipos de datos compatibles. Por ejemplo, es válido combinar una columna con el tipo de datos DTS DT_WSTR con una columna con el tipo de datos nvarchar de SQL Server, pero no se puede combinar una columna con el tipo de datos DT_WSTR con otra que tenga el tipo de datos int.

Puede personalizar esta transformación, especificando la cantidad máxima de memoria, el algoritmo de comparación de filas y el almacenamiento en caché de índices y tablas de referencia que utiliza la transformación.

La cantidad de memoria que utiliza la transformación Búsqueda aproximada se puede configurar al establecer la propiedad personalizada MaxMemoryUsage. Puede especificar el número de megabytes (MB) o utilizar el valor 0, lo que permite a la transformación utilizar una cantidad de memoria dinámica en función de sus necesidades y de la memoria física disponible. La propiedad personalizada MaxMemoryUsage se puede actualizar a través de una expresión de propiedad, al cargar el paquete. Para obtener más información, vea Expresiones de Integration Services (SSIS), Usar expresiones de propiedad en paquetes y Propiedades personalizadas de transformación.

Controlar el comportamiento de la coincidencia aproximada

La transformación Búsqueda aproximada incluye tres características para personalizar la búsqueda realiza: el número máximo de coincidencias que se van a devolver por fila de entrada, delimitadores de token y umbrales de similitud.

La transformación devuelve cero o más coincidencias, hasta alcanzar el número especificado. Especificar un número máximo de coincidencias no garantiza que la transformación devuelva ese número de coincidencias; solo garantiza que la transformación devolverá, como máximo, ese número de coincidencias. Si establece el número máximo de coincidencias en un valor mayor que 1, la salida de transformación puede incluir más de una fila por búsqueda y algunas de las filas pueden ser duplicados.

La transformación proporciona un conjunto predeterminado de delimitadores que se utilizan para dividir los datos en tokens, pero puede agregar delimitadores de token que se adapten a las necesidades de sus datos. La propiedad Delimiters contiene los delimitadores predeterminados. La división de los datos en tokens es importante, ya que define las unidades de los datos que se compararán con otras.

Los umbrales de similitud se pueden establecer en el nivel de componente y de combinación. El umbral de similitud en el nivel de combinación solo está disponible cuando la transformación realiza una búsqueda de coincidencias aproximadas entre las columnas de la entrada y la tabla de referencia. El intervalo de similitud es de 0 a 1. Cuanto más se acerque el umbral a 1, más similares deberán ser las filas y las columnas para ser calificadas como duplicados. El umbral de similitud se especifica estableciendo la propiedad MinSimilarity en los niveles de componente y de combinación. Para satisfacer la similitud especificada en el nivel de componente, todas las filas deben tener una similitud en todas las coincidencias igual o superior al umbral de similitud especificado en el nivel de componente. Es decir, no puede especificar una coincidencia muy próxima en el nivel de componente, a menos que las coincidencias en el nivel de fila o de combinación sean igual de próximas.

Cada coincidencia incluye una puntuación de similitud y una puntuación de confianza. La puntuación de similitud es una medida matemática de la similitud del texto entre el registro de entrada y el registro que la transformación Búsqueda aproximada devuelve de la tabla de referencia. La puntuación de confianza es una medida de la probabilidad de que un valor determinado sea la mejor coincidencia de todas las que se encuentran en la tabla de referencia. La puntuación de confianza asignada a un registro depende de los demás registros coincidentes devueltos. Por ejemplo, una coincidencia entre St. y Saint devuelve una puntuación de similitud baja, independientemente de las demás coincidencias. Si Saint es la única coincidencia devuelta, la puntuación de confianza será alta. Si aparecen Saint y St. en la tabla de referencia, la confianza para St. será alta y para Saint será baja. Sin embargo, una similitud alta no necesariamente significa una confianza alta. Por ejemplo, si está buscando el valor Chapter 4, los valores devueltos Chapter 1, Chapter 2 y Chapter 3 tendrán una puntuación de similitud alta, pero una puntuación de confianza baja, ya que no está claro cuál de los resultados es la mejor coincidencia.

La puntuación de similitud se representa mediante un valor decimal entre 0 y 1; una puntuación de similitud 1 significa una coincidencia exacta entre el valor de la columna de entrada y el valor de la tabla de referencia. La puntuación de confianza, que también es un valor decimal entre 0 y 1, indica la confianza de la coincidencia. Si no se encuentra ninguna coincidencia útil, se asignan las puntuaciones de similitud y confianza 0 a la fila, y las columnas de salida copiadas de la tabla de referencia contienen valores NULL.

En ocasiones, la búsqueda aproximada no localiza coincidencias apropiadas en la tabla de referencia. Esto puede ocurrir si el valor de entrada que se utiliza en una búsqueda es una sola palabra corta. Por ejemplo, helo no se corresponde con el valor hello de una tabla de referencia si no hay otros tokens presentes en esa columna o en cualquier otra columna de la fila.

Las columnas de salida de transformación incluyen las columnas de entrada marcadas como columnas de paso, las columnas seleccionadas en la tabla de búsqueda y las siguientes columnas adicionales:

_Similarity, una columna que describe la similitud entre los valores de las columnas de entrada y de referencia.
_Confidence, una columna que describe la calidad de la coincidencia.

La transformación utiliza la conexión a la base de datos de SQL Server para crear las tablas temporales que utiliza el algoritmo de coincidencia aproximada.

Ejecutar la transformación Búsqueda aproximada

Cuando el paquete ejecuta la transformación por primera vez, ésta copia la tabla de referencia, agrega una clave con un tipo de datos entero a la tabla nueva y crea un índice en la columna de clave. Después, la transformación crea un índice, llamado índice de coincidencias, en la copia de la tabla de referencia. El índice de coincidencias almacena los resultados de la división de los valores en tokens en las columnas de entrada de la transformación y posteriormente, la transformación utiliza los tokens en la operación de búsqueda. El índice de coincidencias es una tabla en una base de datos de SQL Server.

Cuando se vuelve a ejecutar el paquete, la transformación puede utilizar un índice de coincidencias existente o crear uno. Si la tabla de referencia es estática, el paquete puede evitar el proceso potencialmente costoso de volver a crear el índice para sesiones reiteradas de limpieza de datos. Si elige utilizar un índice existente, el índice se creará la primera vez que se ejecute el paquete. Si varias transformaciones Búsqueda aproximada utilizan la misma tabla de referencia, todas pueden utilizar el mismo índice. Para volver a usar el índice, las operaciones de búsqueda deben ser idénticas; la búsqueda debe utilizar las mismas columnas. Puede asignar un nombre al índice y seleccionar la conexión a la base de datos de SQL Server en la que se guarda el índice.

Si la transformación guarda el índice de coincidencias, éste se puede mantener de manera automática. Esto significa que cada vez que se actualiza un registro en la tabla de referencia, el índice de coincidencias también se actualiza. Mantener el índice de coincidencias puede ahorrar tiempo de procesamiento, ya que no es necesario volver a crear el índice cuando se ejecuta el paquete. Puede especificar cómo la transformación administra el índice de coincidencias.

Las opciones del índice de coincidencias se describen en la siguiente tabla.

Opción	Descripción
GenerateAndMaintainNewIndex	Crea un índice, lo guarda y lo mantiene. La transformación instala desencadenadores en la tabla de referencia para mantener la tabla de referencia sincronizada con la tabla del índice.
GenerateAndPersistNewIndex	Crea un índice y lo guarda, pero no lo mantiene.
GenerateNewIndex	Crea un índice, pero no lo guarda.
ReuseExistingIndex	Vuelve a utilizar un índice existente.

Mantenimiento de la tabla del índice de coincidencias

La opción GenerateAndMaintainNewIndex instala desencadenadores en la tabla de referencia para mantener la tabla del índice de coincidencias sincronizada con la tabla de referencia. Si tiene que quitar el desencadenador instalado, debe ejecutar el procedimiento almacenado sp_FuzzyLookupTableMaintenanceUnInstall y proporcionar el nombre especificado en la propiedad MatchIndexName como valor del parámetro de entrada.

No debe eliminar la tabla del índice de coincidencias mantenida antes de ejecutar el procedimiento almacenado sp_FuzzyLookupTableMaintenanceUnInstall. Si elimina la tabla del índice de coincidencias, los desencadenadores de la tabla de referencia no se ejecutarán correctamente. Todas las actualizaciones posteriores de la tabla de referencia generarán errores hasta que quite manualmente los desencadenadores de la tabla de referencia.

El comando SQL TRUNCATE TABLE no invoca desencadenadores DELETE. Si se utiliza el comando TRUNCATE TABLE en la tabla de referencia, la tabla de referencia y el índice de coincidencias dejarán de estar sincronizados y la Transformación Búsqueda aproximada generará un error. Mientras estén instalados en la tabla de referencia los desencadenadores que mantienen la tabla del índice de coincidencias, debe utilizar el comando SQL DELETE en lugar del comando TRUNCATE TABLE.

[!NOTA]

Al seleccionar Mantener el índice almacenado en la pestaña Tabla de referencia del Editor de transformación Búsqueda aproximada, la transformación utiliza los procedimientos almacenados administrados para mantener el índice. Estos procedimientos almacenados administrados utilizan la característica de integración de Common Language Runtime (CLR) en SQL Server. De forma predeterminada, la integración de CLR en SQL Server no está habilitada. Para utilizar la funcionalidad Mantener el índice almacenado, debe habilitar la integración de CLR. Para obtener más información, vea Habilitar la integración CLR.

Dado que la opción Mantener el índice almacenado requiere la integración con CLR, esta característica solo funciona al seleccionar una tabla de referencia en una instancia de SQL Server donde se habilite la integración con CLR.

Comparación de filas

Al configurar la transformación Búsqueda aproximada, puede especificar el algoritmo de comparación que utiliza la transformación para localizar los registros que coinciden en la tabla de referencia. Si establece la propiedad Exhaustive en True, la transformación compara cada fila de la entrada con cada fila de la tabla de referencia. Este algoritmo de comparación puede producir resultados más exactos, pero es probable que la transformación sea más lenta, a menos que la tabla de referencia tenga un número de filas reducido. Si establece la propiedad Exhaustive en True, se cargará en la memoria la tabla de referencia completa. Para evitar problemas de rendimiento, es conveniente establecer la propiedad Exhaustive en True solo durante el desarrollo de paquetes.

Si la propiedad Exhaustive se establece en False, la transformación Búsqueda aproximada devuelve solo coincidencias que tienen al menos un token indizado o una subcadena (la subcadena se denomina q-grama) en común con el registro de entrada. Para maximizar la eficacia de las búsquedas, solamente se indiza un subconjunto de los tokens de cada fila de la tabla en la estructura de índice invertido que utiliza la transformación Búsqueda aproximada para localizar coincidencias. Si el conjunto de datos de entrada es pequeño, puede establecer Exhaustive en True para evitar que falten las coincidencias para las que no existen tokens comunes en la tabla del índice.

Almacenar en caché índices y tablas de referencia

Al configurar la transformación Búsqueda aproximada, puede especificar si la transformación almacenará parcialmente en caché el índice y la tabla de referencia antes de que la transformación realice su trabajo. Si establece la propiedad WarmCaches en True, se cargará en la memoria el índice y la tabla de referencia. Si la entrada tiene muchas filas, establecer la propiedad WarmCaches en True puede mejorar el rendimiento de la transformación. Cuando el número de filas de entrada es pequeño, establecer la propiedad WarmCaches en False puede hacer que la reutilización de un índice grande sea más rápida.

Tablas e índices temporales

En tiempo de ejecución, la transformación Búsqueda aproximada crea objetos temporales, como tablas e índices, en la base de datos de SQL Server a la que se conecta la transformación. El tamaño de estas tablas e índices temporales es proporcional al número de filas y tokens de la tabla de referencia y al número de tokens que crea la transformación Búsqueda aproximada; por lo tanto, podría consumir una cantidad importante de espacio en disco. La transformación también consulta estas tablas temporales. Por lo tanto, debe considerar la posibilidad de conectar la transformación Búsqueda aproximada a una instancia de la base de datos de SQL Server que no sea de producción, en especial si el servidor de producción tiene un espacio en disco disponible limitado.

El rendimiento de esta transformación puede mejorar si las tablas e índices que utiliza están ubicados en el equipo local. Si la tabla de referencia que utiliza la transformación Búsqueda aproximada está en el servidor de producción, debe plantearse copiar la tabla a un servidor que no sea de producción y configurar la transformación Búsqueda aproximada para que tenga acceso a la copia. Haciendo esto, puede evitar que las consultas de búsqueda consuman recursos del servidor de producción. Además, si MatchIndexOptionsestá establecida en GenerateAndMaintainNewIndex, la transformación Búsqueda aproximada mantiene el índice de coincidencias y puede bloquear la tabla de referencia durante la operación de limpieza de datos, evitando que otros usuarios y aplicaciones tengan acceso a ella.

Configurar la transformación Búsqueda aproximada

Puede establecer propiedades a través del Diseñador SSIS o mediante programación.

Para obtener más información acerca de las propiedades que puede establecer en el cuadro de diálogo Editor de transformación Búsqueda aproximada, haga clic en uno de los temas siguientes:

Para obtener más información acerca de las propiedades que puede establecer a través del cuadro de diálogo Editor avanzado o mediante programación, haga clic en uno de los temas siguientes:

Tareas relacionadas

Para obtener información detallada sobre cómo establecer propiedades de un componente de flujo de datos, vea Establecer las propiedades de un componente de flujo de datos.

Contenido relacionado

Mantenerse al día con Integration Services

Para obtener las descargas, los artículos, los ejemplos y los vídeos más recientes de Microsoft, así como una selección de soluciones de la comunidad, visite la página de Integration Services en MSDN: