Transformação de consulta

Aplica-se a:Servidor SQL Tempo de execução da integração do SSIS no Azure Data Factory

A transformação Lookup efetua pesquisas ao associar os dados das colunas de entrada às colunas de um conjunto de dados de referência. Use a pesquisa para acessar informações adicionais em uma tabela relacionada baseada em valores em colunas comuns.

O conjunto de dados de referência pode ser um arquivo de cache, uma tabela ou exibição existente, uma nova tabela ou o resultado de uma consulta SQL. A transformação Lookup utiliza um gestor de ligações OLE DB ou um gestor de ligações Cache para estabelecer ligação ao conjunto de dados de referência. Para obter mais informações, consulte Gerenciador de conexões OLE DB e Gerenciador de conexões de cache

Pode configurar a transformação Lookup das seguintes formas:

  • Selecione o gerenciador de conexões que você deseja usar. Se você quiser se conectar a um banco de dados, selecione um gerenciador de conexões OLE DB. Se você quiser se conectar a um arquivo de cache, selecione um gerenciador de conexões de cache.

  • Especifique a tabela ou exibição que contém o conjunto de dados de referência.

  • Gere um conjunto de dados de referência especificando uma instrução SQL.

  • Especifique as junções entre a entrada e o conjunto de dados de referência.

  • Adicione colunas do conjunto de dados de referência ao resultado da transformação Consulta.

  • Configure as opções de memória cache.

A transformação Lookup suporta os seguintes fornecedores de bases de dados para o gestor de ligações OLE DB:

  • SQL Server

  • Oracle

  • DB2

A transformação de Pesquisa tenta efetuar uma junção por igualdade entre os valores na entrada da transformação e os valores no conjunto de dados de referência. (Uma junção de igualdade significa que cada linha na entrada da transformação tem de corresponder a, pelo menos, uma linha do conjunto de dados de referência.) Se uma junção de igualdade não for possível, a transformação de Pesquisa executa uma das seguintes ações:

  • Se não houver nenhuma entrada correspondente no conjunto de dados de referência, nenhuma associação ocorrerá. Por padrão, a transformação Pesquisa trata linhas sem entradas correspondentes como erros. No entanto, você pode configurar a transformação Pesquisa para redirecionar essas linhas para uma saída sem correspondência.

  • Se houver várias correspondências na tabela de referência, a transformação Pesquisa retornará somente a primeira correspondência retornada pela consulta de pesquisa. Se várias correspondências forem encontradas, a transformação Pesquisa gerará um erro ou aviso somente quando a transformação tiver sido configurada para carregar todo o conjunto de dados de referência no cache. Nesse caso, a transformação Pesquisa gera um aviso quando a transformação deteta várias correspondências à medida que a transformação preenche o cache.

A junção pode ser uma junção composta, o que significa que é possível associar várias colunas na entrada da transformação às colunas no conjunto de dados de referência. A transformação suporta unir colunas com qualquer tipo de dados, exceto DT_R4, DT_R8, DT_TEXT, DT_NTEXT ou DT_IMAGE. Para obter mais informações, consulte Tipos de dados do Integration Services.

Normalmente, os valores do conjunto de dados de referência são adicionados à saída da transformação. Por exemplo, a transformação Pesquisa pode extrair um nome de produto de uma tabela usando um valor de uma coluna de entrada e, em seguida, adicionar o nome do produto à saída da transformação. Os valores da tabela de referência podem substituir valores de coluna ou podem ser adicionados a novas colunas.

As operações de procura realizadas pela transformação Lookup são sensíveis a maiúsculas e minúsculas. Para evitar falhas na procura causadas por diferenças entre maiúsculas e minúsculas nos dados, utilize primeiro a transformação Mapa de Caracteres para converter os dados em maiúsculas ou minúsculas. Em seguida, inclua as funções UPPER ou LOWER na instrução SQL que gera a tabela de referência. Para obter mais informações, consulte Transformação do mapa de caracteres, UPPER (Transact-SQL) e LOWER (Transact-SQL).

A transformação de Pesquisa tem as seguintes entradas e saídas:

  • Entrada.

  • Fazer corresponder a saída. A saída de correspondência processa as linhas na entrada da transformação que correspondem a pelo menos uma entrada no conjunto de dados de referência.

  • Nenhuma saída de correspondência. A saída sem correspondência manipula linhas na entrada que não correspondem a pelo menos uma entrada no conjunto de dados de referência. Se configurar a transformação Lookup para tratar como erros as linhas sem entradas correspondentes, as linhas serão redirecionadas para a saída de erro. Caso contrário, a transformação redirecionaria essas linhas para a saída sem correspondência.

  • Saída de erro.

Armazenando em cache o conjunto de dados de referência

Um cache na memória armazena o conjunto de dados de referência e armazena uma tabela de hash que indexa os dados. O cache permanece na memória até que a execução do pacote seja concluída. Você pode manter o cache em um arquivo de cache (.caw).

Quando você persiste o cache em um arquivo, o sistema carrega o cache mais rapidamente. Isto melhora o desempenho da transformação de Pesquisa e do pacote. Lembre-se de que, ao usar um arquivo de cache, você está trabalhando com dados que não são tão atuais quanto os dados no banco de dados.

A seguir estão os benefícios adicionais de persistir o cache em um arquivo:

A seguir estão as opções de cache:

  • O conjunto de dados de referência é gerado usando uma tabela, exibição ou consulta SQL e carregado em cache, antes que a transformação Pesquisa seja executada. Use o gerenciador de conexões OLE DB para acessar o conjunto de dados.

    Esta opção de cache é compatível com a opção de cache completo que está disponível para a transformação Lookup no SQL Server 2005 Integration Services (SSIS).

  • O conjunto de dados de referência é gerado a partir de uma fonte de dados conectada no fluxo de dados ou de um arquivo de cache e é carregado no cache antes que a transformação Pesquisa seja executada. Use o gerenciador de conexões de cache e, opcionalmente, a transformação de cache, para acessar o conjunto de dados. Para obter mais informações, consulte Gerenciador de conexões de cache e Transformação de cache.

  • O conjunto de dados de referência é gerado usando uma tabela, exibição ou consulta SQL durante a execução da transformação Pesquisa. As linhas com entradas correspondentes no conjunto de dados de referência e as linhas sem entradas correspondentes no conjunto de dados são carregadas em cache.

    Quando o tamanho da memória do cache é excedido, a transformação Pesquisa remove automaticamente as linhas menos usadas do cache.

    Esta opção de armazenamento em cache é compatível com a opção de armazenamento em cache parcial que está disponível para a transformação Lookup no SQL Server 2005 Integration Services (SSIS).

  • O conjunto de dados de referência é gerado usando uma tabela, exibição ou consulta SQL durante a execução da transformação Pesquisa. Nenhum dado é armazenado em cache.

    Esta opção de cache é compatível com a opção sem colocação em cache que está disponível para a transformação Lookup no SQL Server 2005 Integration Services (SSIS).

O Integration Services e o SQL Server diferem na maneira como comparam cadeias de caracteres. Se a transformação de pesquisa estiver configurada para carregar o conjunto de dados de referência para a cache antes de a transformação de pesquisa ser executada, o Integration Services efetua a comparação na cache. Caso contrário, a operação de pesquisa usa uma instrução SQL parametrizada e o SQL Server faz a comparação de pesquisa. Isso significa que a transformação Pesquisa pode retornar um número diferente de correspondências da mesma tabela de pesquisa, dependendo do tipo de cache.

Você pode definir propriedades por meio do SSIS Designer ou programaticamente. Para obter mais detalhes, consulte os tópicos a seguir.

Editor de Transformação de Procura (Página Geral)

Use a página Geral da caixa de diálogo Editor de Transformação de Pesquisa para selecionar o modo de cache, selecionar o tipo de conexão e especificar como lidar com linhas sem entradas correspondentes.

Opções

Observação

A saída correspondente pode diferir dependendo se o modo de cache completo, sem modo de cache ou modo de cache parcial é usado:

  • No modo de cache completo, o banco de dados é consultado uma vez durante a fase de pré-execução do fluxo de dados e todo o conjunto de referência é puxado para a memória. Isto significa que recorreremos à regra de correspondência do motor SSIS, que diferencia maiúsculas de minúsculas e é sensível a espaços finais e à precisão e à escala dos valores numéricos.
  • No entanto, quando é utilizado o modo Sem cache ou o modo de cache parcial, não é efetuado qualquer armazenamento em cache durante a fase de pré-execução. Isso significa que usaremos a regra de correspondência do banco de dados de origem. Por exemplo, se a origem for um banco de dados do SQL Server e se o agrupamento de colunas não diferenciar maiúsculas de minúsculas, a regra de correspondência também deverá diferenciar maiúsculas de minúsculas.

Cache completo
Gere e carregue o conjunto de dados de referência em cache antes que a transformação Pesquisa seja executada.

Cache parcial
Gere o conjunto de dados de referência durante a execução da transformação Lookup. Carregue as linhas com entradas correspondentes no conjunto de dados de referência e as linhas sem entradas correspondentes no conjunto de dados no cache.

Sem cache
Gere o conjunto de dados de referência durante a execução da transformação Lookup. Nenhum dado é carregado no cache.

Gerenciador de conexões de cache
Configure a transformação Lookup para utilizar um gestor de ligações Cache. Esta opção só estará disponível se a opção Cache completo estiver selecionada.

Gerenciador de conexões OLE DB
Configure a transformação de Pesquisa para utilizar um gestor de ligações OLE DB.

Especificar como lidar com linhas sem entradas correspondentes
Selecione uma opção para manipular linhas que não correspondam a pelo menos uma entrada no conjunto de dados de referência.

Quando você seleciona Redirecionar linhas para saída sem correspondência, as linhas são redirecionadas para uma saída sem correspondência e não são tratadas como erros. A opção Erro na página Saída de Erro da caixa de diálogo Editor de Transformação de Pesquisa não está disponível.

Quando você seleciona qualquer outra opção na caixa de listagem Especificar como lidar com linhas sem entradas correspondentes , as linhas são tratadas como erros. A opção Erro na página Saída de erro está disponível.

Recursos Externos

Entrada de blogue, modos de consulta da cache em blogs.msdn.com

Editor de Transformação de Pesquisa (Página de Conexão)

Utilize a página Ligação da caixa de diálogo Editor de Transformação de Procura para selecionar um gestor de ligações. Se você selecionar um gerenciador de conexões OLE DB, também selecionará uma consulta, tabela ou exibição para gerar o conjunto de dados de referência.

Opções

As opções seguintes estão disponíveis quando seleciona Cache completo e Gestor de ligações de cache na página Geral da caixa de diálogo Editor de Transformação de Pesquisa.

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

Novo
Crie uma nova conexão usando a caixa de diálogo Editor do Gerenciador de Conexões de Cache .

As opções a seguir estão disponíveis quando você seleciona Cache completo, Cache parcial ou Sem cache e Gerenciador de conexões OLE DB na página Geral da caixa de diálogo Editor de transformação de pesquisa .

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 .

Utilizar uma tabela ou vista
Selecione uma tabela ou vista existente na lista ou crie uma nova tabela clicando em Novo.

Observação

Se especificar uma instrução SQL na página Avançadas do Editor de Transformação Pesquisa, essa instrução SQL sobrepõe-se ao nome da tabela selecionado aqui e substitui-o.

Novo
Crie uma nova tabela usando a caixa de diálogo Criar tabela .

Usar resultados de uma consulta SQL
Escolha esta opção para navegar até uma consulta pré-existente, criar uma nova consulta, verificar a sintaxe da consulta e visualizar os resultados da consulta.

Criar consulta
Crie a instrução Transact-SQL a ser executada usando o Construtor de Consultas, uma ferramenta gráfica usada para criar consultas navegando pelos dados.

Navegar
Use esta opção para navegar até uma consulta pré-existente salva como um arquivo.

Analisar consulta
Verifique a sintaxe da consulta.

Preview
Visualize os resultados usando a caixa de diálogo Visualizar Resultados da Consulta . Esta opção exibe até 200 linhas.

Recursos Externos

Entrada de blog, modos de cache de pesquisa no blogs.msdn.com

Editor de transformação de pesquisa (página Colunas)

Use a página Colunas da caixa de diálogo Editor de Transformação de Pesquisa para especificar a junção entre a tabela de origem e a tabela de referência e para selecionar colunas de pesquisa na tabela de referência.

Opções

Colunas de entrada disponíveis
Veja a lista de colunas de entrada disponíveis. As colunas de entrada são as colunas no fluxo de dados proveniente de uma fonte conectada. As colunas de entrada e a coluna de pesquisa devem ter tipos de dados correspondentes.

Use uma operação de arrastar e soltar para mapear colunas de entrada disponíveis para colunas de pesquisa.

Você também pode mapear colunas de entrada para pesquisar colunas usando o teclado, destacando uma coluna na tabela Colunas de entrada disponíveis , pressionando a tecla Aplicativo e clicando em Editar mapeamentos.

Colunas de pesquisa disponíveis
Exiba a lista de colunas de pesquisa. As colunas de pesquisa são colunas na tabela de referência nas quais você deseja procurar valores que correspondam às colunas de entrada.

Utilize uma operação de arrastar e largar para associar as colunas de consulta disponíveis às colunas de entrada.

Use as caixas de seleção para selecionar colunas de pesquisa na tabela de referência nas quais executar operações de pesquisa.

Você também pode mapear colunas de pesquisa para colunas de entrada usando o teclado, destacando uma coluna na tabela Colunas de Pesquisa Disponíveis , pressionando a tecla Aplicativo e clicando em Editar Mapeamentos.

Coluna de pesquisa
Exiba as colunas de pesquisa selecionadas. As seleções refletem-se nas caixas de verificação selecionadas na tabela Colunas de Consulta Disponíveis.

Operação de pesquisa
Selecione uma operação de pesquisa na lista a ser executada na coluna de pesquisa.

Alias de saída
Introduza um alias para o resultado de cada coluna de procura. O padrão é o nome da coluna de pesquisa; no entanto, você pode selecionar qualquer nome exclusivo e descritivo.

Editor de transformação de pesquisa (página avançada)

Utilize a página Avançado da caixa de diálogo Editor de Transformação Lookup para configurar o armazenamento em cache parcial e modificar a instrução SQL da transformação Lookup.

Opções

Tamanho do cache (32 bits)
Ajuste o tamanho do cache (em megabytes) para computadores de 32 bits. O valor padrão é 5 megabytes.

Tamanho do cache (64 bits)
Ajuste o tamanho do cache (em megabytes) para computadores de 64 bits. O valor padrão é 5 megabytes.

Habilitar cache para linhas sem entradas correspondentes
Armazenar em cache linhas sem entradas correspondentes no conjunto de dados de referência.

Alocação a partir do cache
Especifique a porcentagem do cache a ser alocada para linhas sem entradas correspondentes no conjunto de dados de referência.

Modificar a instrução SQL
Modifique a instrução SQL usada para gerar o conjunto de dados de referência.

Observação

A instrução SQL opcional que especificou nesta página sobrepõe-se e substitui o nome de tabela que especificou na página Ligação do Editor da Transformação Pesquisa. .

Definir parâmetros
Mapeie colunas de entrada para parâmetros usando a caixa de diálogo Definir parâmetros de consulta .

Recursos Externos

Entrada de blog, modos de cache de pesquisa no blogs.msdn.com

Conteúdo relacionado

  • Last updated on