Partilhar via

Transformação de Pesquisa Fuzzy

Aplica-se a:SQL Server SSIS Integration Runtime em Azure Data Factory

A transformação Fuzzy Lookup realiza tarefas de limpeza de dados, como padronizar dados, corrigir dados e fornecer valores em falta.

Observação

Para informações mais detalhadas sobre a transformação Fuzzy Lookup, incluindo limitações de desempenho e memória, consulte o white paper, Fuzzy Lookup and Fuzzy Grouping in SQL Server Integration Services 2005.

A transformação Fuzzy Lookup diferencia-se da transformação Lookup pelo uso de correspondência difusa. A transformação de Pesquisa utiliza uma união equi para localizar registos correspondentes na tabela de referência. Devolve registos com pelo menos um registo correspondente e devolve registos sem registos correspondentes. Em contraste, a transformação Fuzzy Lookup usa correspondência difusa para devolver uma ou mais correspondências próximas na tabela de referência.

Uma transformação Fuzzy Lookup segue frequentemente uma transformação de Pesquisa num fluxo de dados de pacotes. Primeiro, a transformação Pesquisa tenta encontrar uma correspondência exata. Se falhar, a transformação Fuzzy Lookup fornece correspondências próximas da tabela de referência.

A transformação necessita de acesso a uma fonte de dados de referência que contenha os valores usados para limpar e estender os dados de entrada. A fonte de dados de referência deve ser uma tabela numa base de dados SQL Server. A correspondência entre o valor numa coluna de entrada e o valor na tabela de referência pode ser uma correspondência exata ou uma correspondência difusa. No entanto, o processo de transformação requer que pelo menos uma correspondência de coluna seja configurada para correspondência difusa. Se quiseres usar apenas correspondência exata, usa a transformação Procurar em vez disso.

Esta transformação tem uma entrada e uma saída.

Apenas colunas de entrada com os tipos de dados DT_WSTR e DT_STR podem ser usadas no fuzzy matching. A correspondência exata pode usar qualquer tipo de dado DTS, exceto DT_TEXT, DT_NTEXT e DT_IMAGE. Para obter mais informações, consulte Tipos de dados do Integration Services. As colunas que participam na junção entre a entrada e a tabela de referência devem ter tipos de dados compatíveis. Por exemplo, é válido juntar uma coluna com o tipo de dado DTS DT_WSTR a uma coluna com o tipo de dado nvarchar do SQL Server, mas é inválido juntar uma coluna com o tipo de dado DT_WSTR a uma coluna com o tipo de dados int .

Pode personalizar esta transformação especificando a quantidade máxima de memória, o algoritmo de comparação de linhas e a cache dos índices e tabelas de referência que a transformação utiliza.

A quantidade de memória que a transformação Fuzzy Lookup utiliza pode ser configurada definindo a propriedade personalizada MaxMemoryUsage. Pode especificar o número de megabytes (MB), ou usar o valor 0, o que permite à transformação usar uma quantidade dinâmica de memória com base nas suas necessidades e na memória física disponível. A propriedade personalizada MaxMemoryUsage pode ser atualizada através de uma expressão de propriedade quando o pacote é carregado. Para mais informações, consulte Expressões de Serviços de Integração (SSIS), Expressões de Propriedades de Utilização em Pacotes e Propriedades Personalizadas de Transformação.

Controlo do Comportamento de Fuzzy Matching

A transformação Fuzzy Lookup inclui três funcionalidades para personalizar a pesquisa que realiza: número máximo de correspondências a devolver por linha de entrada, delimitadores de tokens e limiares de similaridade.

A transformação devolve zero ou mais correspondências, até ao número de correspondências especificado. Especificar um número máximo de correspondências não garante que a transformação devolva o número máximo de correspondências; só garante que a transformação retorna no máximo esse número de correspondências. Se definir o número máximo de correspondências para um valor superior a 1, a saída da transformação pode incluir mais do que uma linha por consulta e algumas das linhas podem ser duplicadas.

A transformação fornece um conjunto padrão de delimitadores usados para tokenizar os dados, mas pode adicionar delimitadores de token para se adequar às necessidades dos seus dados. A propriedade Delimitadores contém os delimitadores padrão. A tokenização é importante porque define as unidades dentro dos dados que são comparadas entre si.

Os limiares de similaridade podem ser definidos ao nível do componente e da junção. O limiar de similaridade ao nível de junção só está disponível quando a transformação realiza uma correspondência difusa entre colunas na entrada e na tabela de referência. O intervalo de semelhança é de 0 a 1. Quanto mais próximo de 1 for o limiar, mais semelhantes devem ser as linhas e colunas para serem considerados duplicados. Especifica o limiar de similaridade definindo a propriedade MinSimilaridade nos níveis de componente e junção. Para satisfazer a semelhança especificada ao nível do componente, todas as linhas devem ter uma semelhança em todas as correspondências que seja maior ou igual ao limiar de similaridade especificado ao nível do componente. Ou seja, não pode especificar uma correspondência muito próxima ao nível do componente a menos que as correspondências ao nível da linha ou da junção sejam igualmente próximas.

Cada correspondência inclui uma pontuação de semelhança e uma pontuação de confiança. A pontuação de similaridade é uma medida matemática da similaridade textural entre o registo de entrada e o registo que a transformação Fuzzy Lookup devolve da tabela de referência. A pontuação de confiança é uma medida de quão provável é que um determinado valor seja a melhor correspondência entre as correspondências encontradas na tabela de referência. A pontuação de confiança atribuída a um registo depende dos outros registos correspondentes que são devolvidos. Por exemplo, combinar St. e Saint retorna uma pontuação de semelhança baixa independentemente dos outros jogos. Se Saint for o único resultado devolvido, a pontuação de confiança é elevada. Se tanto Saint como St. aparecerem na tabela de referência, a confiança em St. é elevada e a confiança em Saint é baixa. No entanto, uma alta semelhança pode não significar alta confiança. Por exemplo, se estiver a pesquisar o valor do Capítulo 4, os resultados retornados Capítulo 1, Capítulo 2 e Capítulo 3 têm uma pontuação de similaridade elevada mas uma pontuação de confiança baixa porque não é claro qual dos resultados é a melhor correspondência.

A pontuação de similaridade é representada por um valor decimal entre 0 e 1, onde uma pontuação de similaridade 1 significa uma correspondência exata entre o valor na coluna de entrada e o valor na tabela de referência. A pontuação de confiança, também um valor decimal entre 0 e 1, indica a confiança no jogo. Se não for encontrada correspondência utilizável, são atribuídas pontuações de similaridade e confiança de 0 à linha, e as colunas de saída copiadas da tabela de referência conterão valores nulos.

Por vezes, o Fuzzy Lookup pode não encontrar correspondências apropriadas na tabela de referência. Isto pode ocorrer se o valor de entrada usado numa consulta for uma palavra única e curta. Por exemplo, helo não corresponde ao valor hello numa tabela de referência quando não há outros tokens presentes nessa coluna ou em qualquer outra coluna da linha.

As colunas de saída da transformação incluem as colunas de entrada marcadas como colunas de passagem, as colunas selecionadas na tabela de consulta e as seguintes colunas adicionais:

  • _Similarity, uma coluna que descreve a semelhança entre valores nas colunas de entrada e referência.

  • _Confidence, uma coluna que descreve a qualidade do combate.

A transformação utiliza a ligação à base de dados SQL Server para criar as tabelas temporárias que o algoritmo de correspondência fuzzy utiliza.

Executar a Transformação de Pesquisa Fuzzy

Quando o pacote executa a transformação pela primeira vez, a transformação copia a tabela de referência, adiciona uma chave com um tipo de dado inteiro à nova tabela e constrói um índice na coluna chave. De seguida, a transformação constrói um índice, chamado índice de correspondência, sobre a cópia da tabela de referência. O índice de correspondência armazena os resultados da tokenização dos valores nas colunas de entrada da transformação, e a transformação utiliza então os tokens na operação de pesquisa. O índice de correspondência é uma tabela numa base de dados SQL Server.

Quando o pacote é executado novamente, a transformação pode usar um índice de correspondência existente ou criar um novo índice. Se a tabela de referência for estática, o pacote pode evitar o processo potencialmente dispendioso de reconstruir o índice para sessões repetidas de limpeza de dados. Se optar por usar um índice existente, o índice é criado na primeira execução do pacote. Se múltiplas transformações Fuzzy Lookup usarem a mesma tabela de referência, todas podem usar o mesmo índice. Para reutilizar o índice, as operações de consulta devem ser idênticas; A pesquisa deve usar as mesmas colunas. Pode nomear o índice e selecionar a ligação à base de dados SQL Server que guarda o índice.

Se a transformação guardar o índice de correspondência, este pode ser mantido automaticamente. Isto significa que sempre que um registo na tabela de referência é atualizado, o índice de correspondência também é atualizado. Manter o índice de correspondência pode poupar tempo de processamento, porque o índice não precisa de ser reconstruído quando o pacote é executado. Pode especificar como a transformação gere o índice de correspondência.

A tabela seguinte descreve as opções do índice de correspondência.

Opção Description
GerarEManterNovoÍndice Crie um novo índice, guarde-o e mantenha-o. A transformação instala gatilhos na tabela de referência para manter a tabela de referência e a tabela de índice sincronizadas.
GerarEPersistirNovoÍndice Crie um novo índice e guarde-o, mas não o mantenha.
GenerateNewIndex Crie um novo índice, mas não o guarde.
ReuseExistingIndex Reutilize um índice existente.

Manutenção da Tabela de Índice de Partidas

A opção GenerateAndMaintainNewIndex instala gatilhos na tabela de referência para manter a tabela de índice de correspondência e a tabela de referência sincronizadas. Se tiver de remover o trigger instalado, deve executar o procedimento sp_FuzzyLookupTableMaintenanceUnInstall armazenado e fornecer o nome especificado na propriedade MatchIndexName como valor do parâmetro de entrada.

Não deve eliminar a tabela de índice de correspondências mantida antes de executar o procedimento sp_FuzzyLookupTableMaintenanceUnInstall armazenado. Se a tabela de índice de correspondência for eliminada, os gatilhos na tabela de referência não serão executados corretamente. Todas as atualizações subsequentes da tabela de referência falharão até eliminares manualmente os triggers na tabela de referência.

O comando SQL TRUNCATE TABLE não invoca os gatilhos DELETE. Se o comando TRUNCATE TABLE for usado na tabela de referência, a tabela de referência e o índice de correspondência deixarão de estar sincronizados e a transformação Fuzzy Lookup falha. Embora os gatilhos que mantêm a tabela de índice de correspondência estejam instalados na tabela de referência, deve usar o comando SQL DELETE em vez do comando TRUNCATE TABLE.

Observação

Quando seleciona Manter índice armazenado na aba Tabela de Referência do Editor de Transformação de Pesquisa Fuzzy, a transformação utiliza procedimentos armazenados geridos para manter o índice. Estes procedimentos armazenados geridos utilizam a funcionalidade de integração em tempo de execução de linguagem comum (CLR) no SQL Server. Por defeito, a integração CLR no SQL Server não está ativada. Para usar a funcionalidade Manter o índice armazenado , deve ativar a integração com CLR. Para obter mais informações, consulte Habilitando a integração CLR.

Como a opção Manter o índice armazenado requer integração com CLR, esta funcionalidade só funciona quando seleciona uma tabela de referência numa instância do SQL Server onde a integração com CLR está ativada.

Comparação de Filas

Quando configura a transformação Fuzzy Lookup, pode especificar o algoritmo de comparação que a transformação utiliza para localizar registos correspondentes na tabela de referência. Se definires a propriedade Exaustiva para Verdadeiro, a transformação compara cada linha da entrada com cada linha da tabela de referência. Este algoritmo de comparação pode produzir resultados mais precisos, mas é provável que faça a transformação ser mais lenta, a menos que o número de linhas da tabela de referência seja pequeno. Se a propriedade Exaustiva for definida como Verdadeira, toda a tabela de referência é carregada na memória. Para evitar problemas de desempenho, é aconselhável definir a propriedade Exaustiva para Verdadeiro apenas durante o desenvolvimento do pacote.

Se a propriedade Exaustiva estiver definida como Falso, a transformação Fuzzy Lookup devolve apenas correspondências que tenham pelo menos um token ou substring indexado (a substring chama-se q-grama) em comum com o registo de entrada. Para maximizar a eficiência das consultas, apenas um subconjunto dos tokens em cada linha da tabela é indexado na estrutura de índice invertido que a transformação Fuzzy Lookup utiliza para localizar correspondências. Quando o conjunto de dados de entrada é pequeno, pode definir a opção Exhaustivo como Verdadeiro para evitar falhar correspondências para as quais não existem tokens comuns na tabela de índices.

Cache de índices e tabelas de referência

Quando configuras a transformação Fuzzy Lookup, podes especificar se a transformação armazena parcialmente em cache o índice e a tabela de referência na memória antes de a transformação fazer o seu trabalho. Se definires a propriedade WarmCaches para True, o índice e a tabela de referência são carregados na memória. Quando a entrada tem muitas linhas, definir a propriedade WarmCaches para True pode melhorar o desempenho da transformação. Quando o número de linhas de entrada é pequeno, definir a propriedade WarmCaches para False pode tornar a reutilização de um índice grande mais rápida.

Tabelas e Índices Temporários

Em tempo de execução, a transformação Fuzzy Lookup cria objetos temporários, como tabelas e índices, na base de dados SQL Server à qual a transformação se liga. O tamanho destas tabelas e índices temporários é proporcional ao número de linhas e tokens na tabela de referência e ao número de tokens que a transformação Fuzzy Lookup cria; por isso, poderiam potencialmente consumir uma quantidade significativa de espaço em disco. A transformação também interroga estas tabelas temporárias. Deve, portanto, considerar ligar a transformação Fuzzy Lookup a uma instância não de produção de uma base de dados SQL Server, especialmente se o servidor de produção tiver espaço em disco limitado.

O desempenho desta transformação pode melhorar se as tabelas e índices que utiliza estiverem localizados no computador local. Se a tabela de referência usada pela transformação Fuzzy Lookup estiver no servidor de produção, deve considerar copiar a tabela para um servidor não de produção e configurar a transformação Fuzzy Lookup para aceder à cópia. Ao fazer isto, pode evitar que as consultas de pesquisa consomam recursos no servidor de produção. Além disso, se a transformação Fuzzy Lookup mantiver o índice de correspondência — ou seja, se o MatchIndexOptions estiver definido como GenerateAndMaintainNewIndex — a transformação pode bloquear a tabela de referência durante toda a operação de limpeza de dados e impedir que outros utilizadores e aplicações acedam à tabela.

Configuração da Transformação de Pesquisa Fuzzy

Você pode definir propriedades por meio do SSIS Designer ou programaticamente.

Para obter mais informações sobre as propriedades que você pode definir na caixa de diálogo Advanced Editor ou programaticamente, clique em um dos seguintes tópicos:

Para detalhes sobre como definir propriedades de um componente de fluxo de dados, veja Definir as Propriedades de um Componente de Fluxo de Dados.

Editor de Transformação de Pesquisa Fuzzy (Separador da Tabela de Referências)

Use o separador Tabela de Referência da caixa de diálogo Fuzzy Lookup Transformation Editor para especificar a tabela de origem e o índice a usar para a consulta. A fonte de dados de referência deve ser uma tabela numa base de dados SQL Server.

Observação

A transformação Fuzzy Lookup cria uma cópia funcional da tabela de referência. Os índices descritos abaixo são criados nesta tabela de trabalho utilizando uma tabela especial, e não um índice comum do SQL Server. A transformação não modifica as tabelas de origem existentes, a menos que selecione Manter índice armazenado. Neste caso, cria um gatilho na tabela de referência que atualiza a tabela de trabalho e a tabela de índice de pesquisa com base nas alterações na tabela de referência.

Observação

As propriedades Exaustiva e MaxMemoryUsage da transformação Fuzzy Lookup não estão disponíveis no Fuzzy Lookup Transformation Editor, mas podem ser definidas usando o Editor Avançado. Além disso, um valor superior a 100 para MaxOutputMatchesPerInput só pode ser especificado no Editor Avançado. Para mais informações sobre estas propriedades, consulte a secção Fuzzy Lookup Transformation de Propriedades Personalizadas de Transformação.

Opções

Gerenciador de conexões OLE DB
Selecione um gerenciador de conexões OLE DB existente na lista ou crie uma nova conexão clicando em Novo.

Novo
Crie uma nova conexão usando a caixa de diálogo Configurar Gerenciador de Conexões OLE DB .

Gerar novo índice
Especifique que a transformação deve criar um novo índice para usar na pesquisa.

Nome da tabela de referência
Selecione a tabela existente para usar como tabela de referência (consulta).

Armazenar novo índice
Selecione esta opção se quiser guardar o novo índice de pesquisa.

Novo nome de índice
Se optou por guardar o novo índice de consulta, escreva um nome descritivo para o índice.

Manter o índice armazenado
Se optaste por guardar o novo índice de consulta, especifica se também queres que o SQL Server mantenha o índice.

Observação

Quando seleciona Manter índice armazenado na aba Tabela de Referência do Editor de Transformação de Pesquisa Fuzzy, a transformação utiliza procedimentos armazenados geridos para manter o índice. Estes procedimentos armazenados geridos utilizam a funcionalidade de integração em tempo de execução de linguagem comum (CLR) no SQL Server. Por defeito, a integração CLR no SQL Server não está ativada. Para usar a funcionalidade Manter o índice armazenado , deve ativar a integração com CLR. Para obter mais informações, consulte Habilitando a integração CLR.

Como a opção Manter o índice armazenado requer integração com CLR, esta funcionalidade só funciona quando seleciona uma tabela de referência numa instância do SQL Server onde a integração com CLR está ativada.

Utilizar índice existente
Especifique que a transformação deve usar um índice existente para a consulta.

Nome de um índice existente
Selecione um índice de pesquisa previamente criado da lista.

Editor de Transformação de Pesquisa Difusa (Separador de Colunas)

Use o separador Colunas da caixa de diálogo Fuzzy Lookup Transformation Editor para definir as propriedades das colunas de entrada e saída.

Opções

Colunas de entrada disponíveis
Arraste as colunas de entrada para as ligar às colunas de consulta disponíveis. Estas colunas devem ter tipos de dados correspondentes e suportados. Selecione uma linha de mapeamento e clique com o botão direito para editar os mapeamentos na caixa de diálogo Criar Relacionamentos .

Nome
Consulte os nomes das colunas de entrada disponíveis.

Passagem Direta
Especifique se deve incluir as colunas de entrada na saída da transformação.

Colunas de pesquisa disponíveis
Use as caixas de seleção para selecionar colunas onde realizar operações de consulta difusa.

Coluna de Procura
Selecione colunas de pesquisa da lista de colunas disponíveis na tabela de referência. As suas escolhas estão refletidas nas caixas de seleção na tabela Colunas de Pesquisa Disponíveis. Selecionar uma coluna na tabela de Colunas de Pesquisa Disponíveis cria uma coluna de saída que contém o valor da coluna da tabela de referência para cada linha correspondente devolvida.

Alias de saída
Digite um alias para a saída de cada coluna de pesquisa. O padrão é o nome da coluna de consulta com um valor numérico de índice acrescentado; No entanto, pode escolher qualquer nome único e descritivo.

Editor de Transformação de Pesquisa Difusa (Separador Avançado)

Use a aba Avançado da caixa de diálogo Fuzzy Lookup Transformation Editor para definir parâmetros para a pesquisa fuzzy.

Opções

Número máximo de correspondências a produzir por consulta
Especifique o número máximo de correspondências que a transformação pode devolver para cada linha de entrada. O padrão é 1.

Limiar de similaridade
Defina o limiar de similaridade ao nível do componente usando o slider. Quanto mais próximo for o valor de 1, mais próxima deve ser a semelhança do valor de consulta com o valor de origem para qualificar-se como correspondência. Aumentar o limiar pode melhorar a velocidade de correspondência, tornando desnecessário considerar tantos registos candidatos.

Delimitadores de tokens
Especifique os delimitadores que a transformação usa para tokenizar os valores das colunas.

Ver também

Transformação de pesquisa
Transformação por Agrupamento Fuzzy
Transformações do Integration Services

  • Last updated on