Tutorial: Carga de dados com o SQL Loader

Neste post, irei demonstrar o uso do SQL Loader para carga de uma grande quantidade de informações de um arquivo de texto para um Banco de Dados Oracle.

 

Constantemente nos encontramos com a tarefa de importar arquivos “.csv” para um banco de dados e a maioria dos SGBDs fornece uma ferramenta para auxiliar esta atividade, por exemplo, no MySQL existe o load data in file, no Postgrees o pgloader e no Oracle Database temos o SQL*Loader.

O SQL*Loader é aquela ferramenta ideal para trabalharmos com um grande volume de dados.

Ele pode ser usado para migrar informações entre banco de dados diferentes e entre estruturas de banco de dados diferentes, por exemplo, ao atualizar um sistema legado para uma nova versão ou mesmo para carregar uma grande quantidade de informações e para execução de testes de performance.

Qual é o conceito do SQL* Loader?

A ideia é centralizar as informações um arquivo de texto e passar esse arquivo como entrada para o SQL*Loader que irá descarregar de uma só vez esse conteúdo no banco de dados, bastando para isso, que as informações tenham consistência com os requisitos da ferramenta, que comentaremos no decorrer do texto, e com a infraestrutura do banco.

Com base em um arquivo de controle com as regras de inserção, o SQL*Loader faz uma cópia das informações do arquivo de dados para uma das três saídas possíveis gerando um log no processo.

A figura 01 apresenta os itens envolvidos no processo de execução do  SQL*Loader:

Carga de dados com o SQL Loader

Figura 01 – Arquivos de entrada e saída do SQL*Loader

 

  1. Data File – Arquivo com os dados que realmente serão inseridos na tabela do banco de dados.  É facilmente criado com qualquer editor de texto ou simplesmente copiando e colando algumas vezes um conjunto de linhas.
  2. Control File – Arquivo com as configurações do SQL*Loader e de como ele deverá tratar os dados do “Data File”.
  3. Database – Se tudo correu bem, os dados estarão no banco de dados ao final do processo;
  4. Bad Files – Registros com erro rejeitados pelo SQL*Loader ou pelo Oracle Database, esse arquivo é gerado sempre que um erro ocorrer. Registros com chaves duplicadas ou tipos de dados inconsistentes entram nesse arquivo;
  5. Discard Files – Registros que estão corretos mas não se encaixam em algum critério de inserção. Esse arquivo é gerado somente se for especificado no Control File. Em configurações mais avançadas é possível estabelecer critérios de inserção como apenas valores maiores que 100 em uma coluna específica.
  6. Log File – Além dos arquivos de saída de dados o SQL*Loader sempre gera um log da operação.

Sintaxe da ferramenta

Comparando com uma a execução de uma inserção manual no banco de dados, o “Arquivo de controle” teria os parâmetros da cláusula insert into e o “arquivo de dados” teria os valores da cláusula values, desse modo o comando seria:

 

INSERT INTO Empresa(id, nome) VALUES(1, “MATERA Systems”);

 

poderia ser interpretado como:

 

INSERT INTO Control_file VALUES(Data_file);

 

Agora é só preencher o arquivo de controle e o arquivo de dados para satisfazer as condições do insert.

Veja que a única parte inalterada no comando foram as cláusulas “INSERT INTO” e “VALUES”  e é justamente aí que reside a mágica do SQL*Loader.

Em vez de inserir os dados pelo caminho convencional (conventional path), usado no comando insert, ele faz uma chamada direta a API de inserção (direct path)  passando os dados formatados no padrão do banco (data blocks) e prontos para a inserção. Como esse processo é muito rápido e assíncrono são utilizados buffers internos para tratar um eventual excesso de informação.

O resultado final é que ele despeja no banco em uma velocidade descomunal e de uma vez, todo o conteúdo do “arquivo de dados”.

Mãos à obra!

Agora que os conceitos principais no SQL*Loader estão estabelecidos, nada melhor que um exemplo para deixar a teoria mais clara.

A aplicação não tem um instalador próprio, ela vem junto com o pacote “Oracle Client” se você já tem o “Oracle Client” instalado pode testar se está funcionando digitando no Terminal “sqlldr”, esse é o comando que executa o SQL*Loader e deve mostrar a versão seguido de informações de ajuda.

Vamos usar o Loader para inserir um bilhão de registros em uma tabela e ver ele funcionando, neste exemplo temos um banco de dados Oracle instalado e funcionando, nesse ambiente vamos criar a tabela abaixo:

 

CREATE TABLE board(
  BOARD_ID      int,
  SENTENCE      varchar2(100),
  START_DATE   date
);

 

 A intenção é encher a tabela board realizando o insert abaixo, porém utilizando o SQL*Loader.

 

INSERT INTO board(BOARD_ID, SENTENCE, START_DATE)
VALUES(sequenceBoard.nextval, 'Let´s Create the Future', '01/01/2018');

 

Vamos começar a preparar o arquivo de controle e o de dados. Inicialmente vamos separar os dados do insert.

As informações relativas a estrutura do banco vão para o arquivo de controle (board.ctl) e os dados que queremos consistir no banco ficam no arquivo de dados (board.txt). Veja que o arquivo de controle tem algumas informações extras que vamos comentar abaixo.

O conteúdo do arquivo de controle board.ctl ficaria da seguinte forma:

 

OPTIONS (SILENT=ALL)
LOAD DATA
CHARACTERSET WE8ISO8859P1
INFILE ‘frases.txt’
INTO TABLE board
APPEND
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(
BOARD_ID          “sequenceBoard.nextval”,
SENTENCE,
START_DATE   "TO_DATE (:START_DATE, 'dd/mm/yyyy')" 
)

 

O arquivo de dados board.txt  terá um bilhão de linhas como as que seguem abaixo:

 

-1,”Let´s Create the Future”,”01/01/2018”
-1,”Let´s Create the Future”,”01/01/2018”
-1,”Let´s Create the Future”,”01/01/2018”

 

A maioria dos campos é auto explicativo mas alguns merecem algum comentário para evitar pequenos erros:

  • INFILE – Nome do arquivo de dados que deve estar na mesma pasta ou o caminho completo deve ser fornecido;
  • APPEND – Novos dados serão adicionados na tabela mantendo os pré existentes intactos, outra opção seria usar o TRUNCATE, porém, desse modo os dados antigos serão perdidos.
  • FIELDS TERMINATED BY – Delimitador usado no arquivo de dados, no exemplo,  foi utilizado uma vírgula, mas poderia ser definido qualquer um.
  • OPTIONALLY ENCLOSED BY – Carácter opcionalmente utilizado.

 

O “-1” no arquivo de dados guarda lugar para a sequence chamada no arquivo de controle, uma alternativa ao uso da sequence do próprio banco seria usar a função SEQUENCE(MAX, 1) do próprio Loader que automaticamente gera valores numéricos incrementais começando do valor máximo já salvo na tabela e incrementando um a cada registro. Nesse caso a linha do arquivo de controle seria:

BOARD_ID          SEQUENCE(MAX, 1),

e no arquivo de dados não seria necessário o -1 sendo que a linha desse arquivo ficaria assim:

”Let´s Create the Future”,”01/01/2018”

 

Ok, estamos prontos para testar!

 Mas antes alguns detalhes que podem gerar alguma dor de cabeça se forem ignorados.

  • No arquivo de dados (board.txt) todos os dados devem estar entre aspas duplas;
  • No arquivo de controle (board.ctl) todos os tipos de dados devem estar entre aspas duplas “TO_DATE” por exemplo;
  • No arquivo de controle (board.ctl) depois do nome da coluna ou tipo de dado necessariamente deve haver uma vírgula com exceção da última linha;
  • Números e textos são automaticamente convertidos para o seu valor tipo de valor no banco de dados mesmo que estejam entre aspas duplas no arquivo de dados;
  • No exemplo acima BOARD_ID foi usado uma sequence definida no próprio banco de dados, essa sequence deve estar em aspas duplas;
  • Quando uma sequence for utilizada no arquivo de dados (board.txt) a coluna dela deve ter uma valor qualquer apenas para marcar a posições, no exemplo foi usado o valor -1 sem aspas;
  • A sequence do banco de dados poderia ser substituída por uma função do próprio SQL*Loader que se chama SEQUENCE funções do próprio Loader são chamadas sem aspas como no exemplo abaixo.

Iniciando os testes

Para executar o Loader será necessário saber o nome da base no arquivo “tnsnames.ora” dentro da pasta de instalação de configurações do Oracle.

 

MATERA.world =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)
    (Host = 127.0.0.1)
    (Port = 1432)))
(CONNECT_DATA = (SID = Systems)))

 

Nas configurações do BD vemos que o nome é “MATERA” agora é só chamar o loader com os dados da minha base:

 

usuário do banco: usuario
password: 123456
base: MATERA

 

Vamos abrir o terminal e navegar até a pasta com os arquivos do SQL*Loader e digitar o comando abaixo:

 

sqlldr userid=usuario/123456@MATERA control=board.ctl

 

Para verificar se deu tudo certo basta ver o arquivo de log na mesma pasta dos nossos arquivos ou fazer um select na tabela “board” e verificar o número de dados que foram inseridos.

Para este exemplo foram inseridos mais de um bilhão de registros na tabela levando em média trinta segundos e impressiona a eficiência do SQL*Loader sendo ele uma solução bem efetiva para uma carga rápida de dados para os mais diversos propósitos.

Esse post teve o objetivo de passar pelos conceitos básicos do SQL*Loader abordando o básico para a criação de scripts na inserção de dados, evitando assim, alguns erros bem comuns que no início são difíceis de identificar.

O SQL*Loader no entanto é bem completo e extrapola em muito o que foi abordado aqui, se necessário, vale a pena verificar a documentação oficial e aproveitar o máximo que essa ferramenta pode oferecer.

 Até a próxima!

 

Por RICARDO SILVEIRA

Programador backend, Materano, Cientista da Computação, graduando de Matemática e jogador de Magic de vez em quando.

Postado em: 17 de abril de 2018

Confira outros artigos do nosso blog

Inteligência Artificial: uma introdução ao Deep Learning

11 de setembro de 2018

Guilherme Moraes Armigliatto

Implementando Circuit Breaker com Spring Cloud Netflix

25 de julho de 2018

Jamila Peripolli Souza

Balanceamento de carga em microsserviços com Spring Cloud Netflix

13 de julho de 2018

Jamila Peripolli Souza

Quais os benefícios da arquitetura REST?

26 de junho de 2018

Henrique Lacerda

Deixe seu comentário