Carga de dados com Integration Services 2008 - Revista SQL Magazine 110

A sigla ETL é originária do idioma inglês, Extract, Transform and Load, sendo um processo que pode ser feito via scripts SQL ou usando ferramentas de software que se destinam a extração, transformação e carga de dados. Estes dados podem ser originados de uma ou mais bases de dados, bem como o destino destes dados podem ser para um ou mais bancos de dados de sistemas de informação ou datamarts ou data warehouse.

A metodologia de ETL como já dito é extrair, transformar e carregar os dados. Em alguns casos pode não haver a necessidade de aplicar a transformação dos dados a serem carregados, pois os mesmos já podem estar de acordo com as regras do banco de dados de destino, mas são raros os casos de se pular a parte de transformação para um datamart ou data warehouse, visto que os mesmos foram criados para suprir uma necessidade que uma base de dados relacional não tem capacidade de fornecer. Esse tipo de processo pode acontecer mais quando se transfere dados de um banco relacional para outro, sendo neste caso um processo de migração de dados.

Atualmente a produção de informação é incessante e cresce vertiginosamente, inúmeras empresas possuem diversas fontes de dados em vários formatos. Para obter informações desses dados armazenados em fontes diversas, surge a necessidade de integrá-los de forma prática, ágil e produtiva.

Para transformar várias bases de dados em uma única fonte de consulta e processamento para obtenção de informação, devem ser aplicadas diversas técnicas de ordenação, agrupamento, padronização e limpeza dos dados.

O processo de automatização é de vital importância para quem deseja obter ganho de desempenho e não pode demandar maior parcela de tempo na captação de informações que são imprescindíveis nas tomadas de decisões gerenciais.

Para fins de exemplificação, foram utilizadas bases de dados de um concurso público da UFJF (Universidade Federal de Juiz de Fora). Tal concurso foi realizado pela Comissão Permanente de Seleção - COPESE e seus resultados foram divulgados em formato PDF, conforme cargo e localidade. As bases de dados são públicas e podem ser encontradas no site da Universidade Federal de Juiz de Fora.

Em artigos anteriores publicados na SQL Magazine, foram demonstrados os processos de extração e de transformação dos dados. Os dados foram extraídos de arquivos no formato PDF e convertidos para dados em formato texto. Também foram ordenados, ajustados, transformados e carregados para dentro de tabelas num banco de dados relacional.

Neste artigo será demonstrada a terceira fase do processo de ETL, a carga dos dados ou Load, que será automatizada pela ferramenta SSIS. Depois de todas as transformações sofridas pelos dados, os mesmo serão carregados em um banco de dados multidimensional no formato estrela, fazendo uso do SSIS. Serão revistos diversos componentes e suas respectivas aplicabilidades. Essa última fase se caracteriza por ser o fechamento de todo o trabalho feito pela extração e transformação.

Validação dos dados

Antes de iniciar o processo de carga dos dados será feita uma validação dos dados que foram manipulados na fase de transformação dos dados vista no artigo publicado na edição 108 da SQL Magazine. Esse tipo de verificação é vital para a continuação do processo, pois é mais fácil corrigir imperfeições na parte de tratamento dos dados, do que depois que os dados já foram carregados no datamart ou data warehouse.

Assim, a tabela tab_tratada passará por algumas verificações antes de ser carregada dentro do banco multidimensional. Essa verificação será feita por meio de scripts SQL dentro da ferramenta de banco de dados do SQL Server.

Todo o processo de validação será feito por meio de scripts T-SQL na ferramenta de banco de dados do SQL Server.

Para aqueles que estão começando a acompanhar o artigo agora, aconselhamos a leitura dos artigos anteriores publicados nas revistas SQL Magazine 107 e 108. Caso não seja possível, estaremos disponibilizando o script de criação do banco juntamente com os dados utilizados na seção Links no final do artigo.

O script da Listagem 1 seleciona todos os dados inseridos na tabela tab_tratada. Neste ponto deverá ser feita uma verificação visual para identificar algum erro grosseiro nos dados. A partir dos mesmos deve-se buscar por falhas mais específicas.

Listagem 1. Script que verifica todos os dados inseridos na tabela tab_tratada.

  Use importa
  SELECT 
  classificacao, nome, total, ling_port, rac_loc_quant, legislacao, con_esp,
 pr_pratica, cidade, cargo, insc, data_nasc
  FROM
  #TAB_TRATADA
  ORDER BY CARGO,INSC,NOME

Por meio da análise visual feita previamente sobre os dados da Listagem 1 foi possível constatar que alguns dados não passaram com sucesso pelo filtro que separa os registros com cargos que tem prova prática e os que não possuem prova prática, ocasionando irregularidades nos registros.

Percebe-se isso principalmente nos cargos que possuem descrições de Técnicos, onde os campos “con_esp” (conhecimentos específicos) e “pr_pratica” (prova prática) estão duplicados, sendo que tais registros não deveriam existir, pois são os que contêm falhas de inserção de dados.

Para fins de melhor visualização é necessário isolar estes casos. Dessa forma, é possível verificar melhor o tipo de inconsistência, vide Listagem 2.

Listagem 2. Script que filtra apenas os casos com registros inconsistentes.

  Use importa
  SELECT 
  ROW_NUMBER() over(PARTITION BY insc ORDER BY pr_pratica asc)
ordem,classificacao, nome, total, ling_port, rac_loc_quant, legislacao, 
con_esp, pr_pratica, cidade, cargo, insc, data_nasc
  FROM #TAB_TRATADA WHERE CARGO LIKE '%TECNICO%' 
AND CARGO NOT LIKE 'TECNICO-DE-TECNOLOGIA-DA-INFORMACAO' 
  ORDER BY INSC,NOME

Com o script da Listagem 2 é possível verificar somente os casos em que existem as irregularidades. Foi criado somente dentro dessa consulta um campo chamado ordem, ele serve para diferenciar a maioria dos casos em que há irregularidades, ordem 1, e para os demais que não possuem esta classificação, ordem 2. Em alguns pouquíssimos registros os valores do campo ordem se invertem, então foi usado o script da Listagem 3 para uma verificação mais apurada.

Listagem 3. Verificação usando como critério o campo “con_esp” (conhecimentos específicos).

  Use importa
  SELECT 
  ROW_NUMBER() over(PARTITION BY insc ORDER BY pr_pratica asc)
ordem,classificacao, nome, total, ling_port, rac_loc_quant, legislacao, 
con_esp, pr_pratica, cidade, cargo, insc, data_nasc
  FROM #TAB_TRATADA WHERE CARGO LIKE '%TECNICO%' 
AND CARGO NOT LIKE 'TECNICO-DE-TECNOLOGIA-DA-INFORMACAO' 
  AND CHARINDEX(' ',LTRIM(RTRIM(CON_ESP)))>0
  ORDER BY INSC,NOME

Na Listagem 3 é possível visualizar todos os casos que possuem mais de uma nota dentro do campo “con_esp”. Verifica-se que existe um padrão separando a primeira nota da segunda nota. Esse padrão é um espaço em branco. Assim, será necessário remover todos os espaços em branco das extremidades e trazer todos os casos em que se tem um espaço em branco no meio de uma cadeia de caracteres. Para resolver essa situação usa-se o script da Listagem 4.

Listagem 4. Remove as colunas duplicadas e com erro nos dados de notas.

  Use importa
  BEGIN TRAN
  DELETE
  FROM #TAB_TRATADA WHERE CARGO LIKE '%TECNICO%'
 AND CARGO NOT LIKE 'TECNICO-DE-TECNOLOGIA-DA-INFORMACAO'
  AND CHARINDEX(' ',LTRIM(RTRIM(CON_ESP)))>0
  COMMIT TRAN

Através do script da Listagem 4 é possível remover os registros duplicados com erro nos dados de notas, assim mantêm-se apenas os registros corretos.

Executando novamente o script da Listagem 3 percebe-se que os registros duplicados foram removidos e permaneceram apenas os registros com os dados corretos.

Outra verificação que pode ser feita é se existem nomes duplicados. Essa verificação é feita na Listagem 5.

Listagem 5. Verifica se existem nomes duplicados.

Use importa

  SELECT NOME,MAX(INSC)INSC_MAXIMO,MIN(INSC)INSC_MINIMO 
FROM #TAB_TRATADA GROUP BY NOME HAVING COUNT(NOME)>2
   
  SELECT NOME,MAX(INSC)INSC_MAXIMO,MIN(INSC)INSC_MINIMO 
FROM #TAB_TRATADA GROUP BY NOME HAVING COUNT(NOME)>1

A primeira consulta da Listagem 5 verifica se existem casos de mais de dois nomes iguais, porém isso não é verdadeiro. A segunda consulta é onde a condição traz os nomes duplicados e suas respectivas inscrições. Se as inscrições forem diferentes, quer dizer que são apenas pessoas com mesmo nome e não registros duplicados.

Mais uma verificação que pode ser feita é se existem inscrições duplicadas. Essa verificação é feita na ...