Um pouco do SQL*Loader

Olá,

Neste artigo irei descrever um pouco sobre o SQL*Loader, também conhecido como Oracle Loader, e demonstrar um exemplo prático de como poderemos efetuar uma carga de dados proveniente de um arquivo texto no formato CSV para uma tabela no banco de dados. Basicamente, o SQL*Loader lê arquivos de texto e insere os dados no banco de dados. A vantagem de utilizá-lo é que o mesmo permite realizar uma carga de dados de forma bastante rápida. Ele é capaz de inserir milhões de linhas em questão de segundos. Ele realiza essa carga de dados com base nas instruções de um arquivo de controle (control file) previamente configurado. Atenção! Não confunda o control file de banco de dados com o control file do SQL*Loader. Este arquivo de controle diz ao Loader não só onde inserir os dados, mas também de que forma estes dados serão lidos do arquivo texto (data file). Para ilustrar, a arquitetura do SQL*Loader é demonstrada na figura abaixo. Em relação ao arquivo de descarte (discard file), por ser opcional, não irei mencioná-lo nesse artigo.

Para executar o SQL*Loader, é necessário executar o comando sqlldr que está localizado em ORACLE_HOME/bin seja na instalação do Oracle Server ou do Oracle Client. Se não for incluído nenhum parâmetro na chamada do SQL*Loader, será exibido uma ajuda online como a que está apresentada na listagem a seguir:

C:\>sqlldr

SQL*Loader: Release 11.2.0.1.0 - Production on Dom Mar 3 10:54:13 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Uso: SQLLDR palavra-chave=valor [,palavra-chave=valor,...]

Palavras-Chave Válidas:

    userid -- ORACLE nome do usuário/senha
   control -- nome do arquivo de controle
       log -- nome do arquivo de log
       bad -- nome do arquivo de incorreções
      data -- nome do arquivo de dados
   discard -- descartar nome de arquivo
discardmax -- número de descartes a serem permitidos          (Usar todos como default)
      skip -- número de registros lógicos a serem ignorados   (Usar 0 como default)
      load -- número de registros lógicos a serem carregados  (Usar todos como default)
    errors -- número de erros a serem permitidos              (Usar 50 como default)
      rows -- número de linhas do array de bind do caminho convencional ou entre o salvamento de dados de caminho direto
               (Default: Caminho convencional 64, Caminho direto para todos)
  bindsize -- tamanho do array de bind do caminho convencional em bytes  (Usar 256000 como default)
    silent -- suprime mensagens durante execução (cabeçalho,feedback,erros,descartes,partições)
    direct -- usar caminho direto                      (Usar FALSE como default)
   parfile -- arquivo de parâmetros: nome do arquivo que contém especificações de parâmetros
  parallel -- fazer carga paralela                     (Usar FALSE como default)
      file -- arquivo para alocar extensões
skip_unusable_indexes -- desativar/ativar índices não utilizáveis em partições de índice  (Usar FALSE como default)
skip_index_maintenance -- não manter índices, marcar índices afetados como não utilizáveis  (Usar FALSE como default)
commit_discontinued -- commit das linhas carregadas quando a carga é descontinuada  (Usar FALSE como default)
readsize -- tamanho do buffer de leitura  (Usar 1048576 como default)
external_table -- usar tabela externa para carga; NOT_USED, GENERATE_ONLY, EXECUTE  (Usar NOT_USED como default)
columnarrayrows -- número de linhas para array de coluna de caminho direto  (Usar 5000 como default)
streamsize -- tamanho do buffer de fluxo de caminho direto em bytes  (Usar 256000 como default)
multithreading -- utilizar multithread no caminho direto
resumable -- ativar ou desativar retomável para a sessão atual  (Usar FALSE como default)
resumable_name -- string de texto para ajudar a identificar a instrução retomável
resumable_timeout -- tempo de espera (em segundos) para RESUMABLE  (Usar 7200 como default)
date_cache -- tamanho (em entradas) do cache de conversão de data  (Usar 1000 como default)
no_index_errors -- abortar carga em qualquer erro de índice  (Usar FALSE como default)

OBSERVE: Os parâmetros da linha de comandos podem ser especificados pela
posição ou por palavras-chave.  Um exemplo do primeiro caso é 'sqlload
scott/tiger foo'; um exemplo do segundo caso é 'sqlldr control=foo
userid=scott/tiger'.  Um pode determinar parâmetros pela posição antes,
mas não após os parâmetros especificados por palavras-chave.  Por exemplo,
'sqlldr scott/tiger control=foo logfile=log' é permitido, mas
'sqlldr scott/tiger control=foo log' não é, mesmo que a
posição do 'log' de parâmetro esteja correta.

Como podemos ver acima, existe uma longa lista de parâmetros, entretanto na maior parte das vezes, executaremos comandos semelhantes a sqlldr {user}/{password} CONTROL={arquivo.ctl} DATA={arquivo.csv}. Para exemplificar, irei carregar abaixo um arquivo no formato CSV contendo dados fictícios de pessoas para uma tabela no banco de dados. Segue abaixo a estrutura da tabela:

SQL> desc pessoa
 Nome               Nulo?    Tipo
 ------------------ -------- ------------------
 CODIGO             NOT NULL NUMBER
 NOME               NOT NULL VARCHAR2(100)
 CIDADE                      VARCHAR2(100)
 UF                          VARCHAR2(2)
 DATA_CADASTRO      NOT NULL DATE

Em relação aos dados de origem, segue um trecho do arquivo CSV contendo os dados de pessoas que deverão ser carregadas na tabela PESSOA de propriedade do usuário SCOTT. O arquivo dados.csv contém 1000 linhas.

C:\>type dados.csv
1;Jose Rodrigues;Belo Horizonte;MG;06/01/2001 08:25:23
2;Reinaldo Duarte;Belo Horizonte;MG;04/09/2005 10:22:08
3;Ronaldo Junior;Belo Horizonte;MG;20/07/2006 13:45:34
4;Leonardo Vieira;São Paulo;SP;14/06/2010 14:06:56
5;Joana Santos;Salvador;BA;11/03/2011 17:56:21
6;Laura Fernandes;Rio de janeiro;RJ;25/10/2008 15:12:39
...

Já em relação ao arquivo de controle, segue abaixo a configuração que utilizarei: 

C:\>type pessoa.ctl
OPTIONS (ERRORS=999999999, ROWS=100)
LOAD DATA
INFILE 'dados.csv'
APPEND
INTO TABLE PESSOA
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
 CODIGO,
 NOME,
 CIDADE,
 UF,
 DATA_CADASTRO "to_date(Trim(:DATA_CADASTRO),'DD/MM/YYYY HH24:MI:SS')"
)

Na primeira linha do arquivo de controle eu estou definindo que não quero parar o processo de carga dos dados de forma nenhuma. Independente do número de erros que acontecerem, eu quero continuar com o processo de carga, por isso o parâmetro ERRORS está com um valor alto. O parâmetro ROWS configura a frequência de commits de acordo com os valores de BINDSIZE e READSIZE. Para ambos estou utilizando os valores Default. No mais, segue abaixo a execução da carga de dados.

C:\>sqlldr scott/tiger CONTROL=pessoa.ctl DATA=dados.csv

SQL*Loader: Release 11.2.0.1.0 - Production on Dom Mar 3 10:54:14 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Atingido o ponto de commit - contagem de registros lógicos 100
Atingido o ponto de commit - contagem de registros lógicos 200
Atingido o ponto de commit - contagem de registros lógicos 300
Atingido o ponto de commit - contagem de registros lógicos 400
Atingido o ponto de commit - contagem de registros lógicos 500
Atingido o ponto de commit - contagem de registros lógicos 600
Atingido o ponto de commit - contagem de registros lógicos 700
Atingido o ponto de commit - contagem de registros lógicos 800
Atingido o ponto de commit - contagem de registros lógicos 900
Atingido o ponto de commit - contagem de registros lógicos 999
Atingido o ponto de commit - contagem de registros lógicos 1000

Após a execução do processo de carga dos dados, poderemos verificar um arquivo de log contendo algumas informações sobre a execução. Neste caso, foi gerado o arquivo pessoa.log. Dentre as informação mais importantes do arquivo de log destaco alguns abaixo:

Arquivo de Controle:    pessoa.ctl
Arquivo de Dados:       dados.csv
Arquivo de Incorreções: dados.bad
Arquivo de Descarte:  nenhum(a) foi especificado(a)
Número a ser carregado: ALL
Número a ser ignorado: 0
Erros permitidos: 999999999
Array de ligação: 100 linhas, máximo de 256000 bytes
Caminho usado: Convencional
Opção de inserção em vigor para esta tabela: APPEND
Opção TRAILING NULLCOLS em efeito

Tabela PESSOA:
  1000 Linhas carregado com êxito.
  0 Linhas não carregado devido a erros de dados.
  0 Linhas não carregado porque todas as cláusulas WHEN falharam.
  0 Linhas não carregado porque todos os campos eram nulos.

Total de registros lógicos ignorados:       0
Total de registros lógicos lidos:        1000
Total de registros lógicos rejeitados:      0
Total de registros lógicos descartados:     0

A execução começou em Dom Mar 03 10:54:14 2013
A execução foi finalizada em Dom Mar 03 10:54:14 2013

O tempo decorrido foi:   00:00:00.23
O tempo de CPU foi:      00:00:00.11

Vale a pena salientar que, se algum registro não for carregado para a tabela, será necessário verificar o motivo do mesmo analisando o arquivo de log e também um arquivo com a extensão .bad. Este arquivo irá conter todos os registros que foram rejeitados pelo processo de carga devido alguma inconformidade ou erro no banco de dados. Para exemplificar, irei alterar o formato de data em uma linha do arquivo de dados (dados.csv) de forma que o mesmo será diferente do formato de data esperado pelo arquivo de controle (pessoa.ctl).

C:\>sqlldr scott/tiger CONTROL=pessoa.ctl DATA=dados.csv

SQL*Loader: Release 11.2.0.1.0 - Production on Dom Mar 3 11:09:42 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Atingido o ponto de commit - contagem de registros lógicos 100
Atingido o ponto de commit - contagem de registros lógicos 200
Atingido o ponto de commit - contagem de registros lógicos 300
Atingido o ponto de commit - contagem de registros lógicos 400
Atingido o ponto de commit - contagem de registros lógicos 500
Atingido o ponto de commit - contagem de registros lógicos 600
Atingido o ponto de commit - contagem de registros lógicos 700
Atingido o ponto de commit - contagem de registros lógicos 800
Atingido o ponto de commit - contagem de registros lógicos 900
Atingido o ponto de commit - contagem de registros lógicos 999
Atingido o ponto de commit - contagem de registros lógicos 1000

Após a execução do processo de carga, poderemos verificar que, além do arquivo pessoa.log, também foi gerado o arquivo dados.bad. No arquivo de log destaco as informações abaixo:

Registro 1000: Rejeitado - Erro na tabela PESSOA, coluna DATA_CADASTRO.
ORA-01861: o literal não corresponde à string de formato

Tabela PESSOA:
  999 Linhas carregado com êxito.
  1 Linha não carregado devido a erros de dados.
  0 Linhas não carregado porque todas as cláusulas WHEN falharam.
  0 Linhas não carregado porque todos os campos eram nulos.

Total de registros lógicos ignorados:       0
Total de registros lógicos lidos:        1000
Total de registros lógicos rejeitados:      1
Total de registros lógicos descartados:     0

Em relação ao arquivo dados.bad, podemos verificar abaixo qual o registro que não foi carregado.

C:\>type dados.bad
4;Leonardo Vieira;São Paulo;SP;2010/04/10 14:06:56

Por fim, o SQL*Loader é uma ferramenta bem versátil que permite a realização de carga massiva de dados para o banco de dados. Com a configuração adequada, o mesmo consegue ler um ou mais arquivos de texto ao mesmo tempo e carregar dados para uma ou mais tabelas. Vale a pena salientar que ele também realiza carga de dados utilizando o caminho direto (direct path), mas com algumas restrições.