• Anderson Graf

Importando dados com o Oracle SQL*Loader

O Oracle SQL*Loader é um utilitário presente tanto na instalação do Oracle Server quanto na instalação do Oracle client e sua função é basicamente ler arquivos textos e inserir os dados no banco de dados.


Sua vantagem é de ser extremamente flexível e rápido em relação aos tradicionais scripts de INSERT o que permite a inserção de milhões de linhas em poucos segundos.


Características do SQL*Loader:

  • Permite carregar dados via rede, ou seja, o arquivo de dados pode estar em um sistema diferente do banco de dados (userid=username/password@instance);

  • Permite carregar dados de vários arquivos de dados durante a mesma sessão de carga;

  • Permite Carregar dados em várias tabelas durante a mesma sessão de carga;

  • Permite especificar o conjunto de caracteres (CHARACTERSET) dos dados;

  • Permite carregar dados de forma seletiva (pode carregar registos com base nos valores dos registos) - "when" categoria='Bebida';

  • Permite a manipulação dos dados antes de carregá-los, usando funções SQL;

  • Permite gerar valores sequencias para uma determinada coluna, entre outras.

A inserção dos dados é realizada com base em um arquivo de controle (control file) onde estão previamente configuradas informações como a localização do arquivo de dados (data file), como analisar e interpretar os dados, onde inserir os dados, entre outras configurações possíveis.


Além do arquivo de controle (control file) e arquivo de dados (data file), a estrutura do SQL*Loader é composta ainda pelo arquivo de log (log file) que contém um detalhamento do processo de carga, arquivo de dados rejeitados (bad file) contendo os registros rejeitados durante o processo de carga de dados e o arquivo de dados descartados (discard file) que é criado apenas se for especificado no arquivo de controle e que registra os dados descartados do processo de carga por não atenderem os critérios de seleção dos dados.








O SQL*Loader, assim como diversos outros utilitários Oracle, está abaixo do $ORACLE_HOME/bin sendo identificado pelo nome sqlldr.


Fazendo uma simples chamada, sem nenhum parâmetro, podemos ver diversas opções/comandos de utilização.



Para Exemplificar alguns casos de uso do SQL*Loader foi criado a seguinte tabela:



Arquivo de dados que será utilizado em alguns exemplos:


1.Carregando os dados ("normal"):


Arquivo de controle:



ERRORS: Independente da quantidade de erros que ocorram, o processo de carga deve continuar, por isso o número alto. Caso nenhum erro seja permitido ajuste o valor para 0. O valor padrão deste parâmetro é 50;


ROWS: Frequência de commits, ou seja, no exemplo a cada 5 linhas inseridas irá ocorrer um commit;

CHARACTERSET: Character set utilizado;


INFILE: Nome do arquivo texto que contém os dados que serão importados baseado nas configurações do control file;

BADFILE: Arquivo texto que será criado dos os registros rejeitados;


DISCARDFILE: Arquivo texto que será criado com os registros descartados mediante as condições de carga;


FIELDS TERMINATED BY: É o delimitador das colunas, ou seja, sempre que o caractere definido for encontrado significa que uma nova coluna se inicia.


Executando o SQL*Loader para carregar os registros contidos no arquivo dados1.txt baseado nas configurações do arquivo de controle (control file).


Finalizada a execução, a saída do comando irá informar o nome do log gerado (Check the log file: exemplo1.log ), nele existirá detalhes adicionais da carga.



Registros na tabela PRODUTOS do banco de dados:


2.Inserindo dados adicionais:


Após a inserção dos registros do arquivo dados1.txt se tornou necessário inserir alguns registros adicionais, desta forma, o SQL*Loader foi novamente executado informando um segundo arquivo de dados chamado dados2.txt, contudo ocorreu ocorre um erro informando que a tabela PROTUDOS precisa estar vazia.



Como o arquivo dados2.txt contém apenas registros adicionais, será utilizado o comando APPEND que instrui o SQL*Loader a "acrescentar" os registros do arquivo dados2 na tabela PRODUTOS.


Arquivo de controle contendo o comando APPEND:


Execução do SQL*Loader para carregar os dados adicionais:


Verificando se os registros foram acrescentados a tabela com sucesso:


Caso os novos registros tenham sido adicionados no mesmo arquivo de dados anterior (dados1.txt), utilize o parâmetro TRUNCATE em vez de APPEND, desta forma, a tabela será primeiramente truncada e posteriormente os dados inseridos.


Execução do SQL*Loader com o novo arquivo de controle (control file):


3.Diferentes delimitadores:


Em alguns cenários os delimitadores dos registros poderão ser diferentes, como no arquivo de dados abaixo:


Para carregar estes dados, além do já configurado terminated by "," será adicionado após cada coluna no arquivo de controle um novo terminated by especificando o caractere necessário para quebrar cada coluna.


Execução:


Verificando os registros:


4.Formatando datatype DATE e definindo "tamanho fixo" para os dados:


Da mesma forma que um arquivo de dados pode ter diferentes delimitadores, também é possível que ele não tenha qualquer delimitador. Utilizando o comando "position(start:end)" é possível delimitar onde começa e onde termina cada coluna/registro.

Na coluna DATA_INCLUSAO, datatype DATE, também é permitido aplicar mascaras, transformando-a conforme necessidade.


Arquivo de dados:


Arquivo de controle contendo a máscara para a data_inclusao e com as posições de início:fim de cada coluna/registro:


Registros inseridos:


5.Alterando os dados durante a carga:


Nem sempre os dados de entrada representam o formato ou nome que é necessário que eles tenham no banco de dados. Para tanto podemos usar funções SQL para “transformar” estes dados durante o processo de carga.


No exemplo será utilizado o arquivo de dados dados1.txt, onde cada código será incrementado em +10, o nome convertido para UPPERCASE (Maiúsculo) e a categoria além do UPPERCASE será substituída por DESCONHECIDA quando a categoria de entrada (arquivo de dados) for Limpeza.


Verificando se os registros foram alterados durante a carga de dados conforme especificado:


6.Carregando dados de forma seletiva:


Se nem todos os dados precisam ser carregados, basta inserir a clausula WHEN logo após o "into table" onde apenas os registros que satisfazerem a condição serão carregados para a tabela no banco de dados.


Dados que foram inseridos na tabela PRODUTOS:


Como existem dados descartados em função da condição WHEN, o arquivo discardfile foi criado contendo os registros que foram descartados no processo de carga por não atenderem a condição imposta.


7.Utilizando valores sequenciais:


Nos exemplos até aqui utilizados, sempre existiu no arquivo de dados o código do produto, mas e se não existe? Pois bem, lembra da flexibilidade do SQL*Loader supracitada?


SEQUENCE(MAX): Especificado após o nome da coluna no arquivo de controle (control file) faz com que o SQL*Loader gere de forma sequencial os valores para a determinada coluna.


Arquivo de dados criado sem os códigos:


Arquivo de controle com o sequence(max) na coluna código:

Após a execução da carga, foram inseridos os seguintes registros na tabela PRODUTOS:


Caso seja necessário carregar mais dados, não tem problema. Altere o arquivo de controle substituindo o comando TRUNCATE por APPEND. O sequence(max) irá continuar a sequência sem problemas.


Registros na tabela PRODUTOS após a carga de dados adicional:


Além da opção nativa do SQL*Loader ainda é possível utilizar sequences criadas na própria base de dados, contudo a antiga coluna de código ainda deve existir no arquivo de dados, caso contrário será gerado erro.


Será utilizado o arquivo de dados - dados1.txt para a simulação.

Arquivo de controle especificando a sequência criada no banco de dados:


Realizada a carga, seguem os dados carregados:


Outra opção para trabalhar com uma sequence da base de dados é via trigger, ou seja, a coluna código deve ser removida do arquivo de controle da mesma forma que no arquivo de dados também não é mais necessário.


Será utilizado o arquivo de dados - dados5.txt para a simulação, onde não existem valores "codigo".


Trigger criada na base de dados:


Arquivo de controle onde foi removido a coluna "codigo":

Registros após a nova carga dos dados utilizando o arquivo de controle exemplo11.ctl:


8.Carregando dados de múltiplos arquivos de dados:

Para carregar dados de múltiplos arquivos, insira múltiplos infile dentro do arquivo de controle.


Como no meu arquivo de dados dados1.txt atualmente existem registros duplicados com o dados2.txt, será criado uma primary key (PK) na tabela PRODUTOS sobre a coluna codigo de forma a não importar dados duplicados e gerar o arquivo badfile com os registros rejeitados.

Execução da carga:


Arquivos rejeitados (badfile) – O nome do caminho/arquivo é o configurado no arquivo de controle, neste caso /home/oracle/exemplo12.bad:


Erro gerado no arquivo de log que justificam os registros rejeitados:

Registros que foram inseridos:



9.Carregando dados em múltiplas tabelas:


Assim como para carregar múltiplos arquivos basta inserir outros infile no arquivo de controle, para carregar os dados em múltiplas tabelas basta inserir outros into table.


Neste exemplo será criado uma nova tabela chamada CATEGORIA onde teremos o código da categoria e seu respectivo nome.


Na tabela PRODUTOS não existirá mais o nome da categoria, mas o código da categoria que fara referência a tabela CATEGORIA. Desta forma, o arquivo de controle foi ajustado para:


O decode na categoria foi inserido para transformar o "nome da categoria" em "códigos" para os mesmos grupos.


No arquivo de dados existem 4 colunas/registros para cada linha, diferentemente da tabela PRODUTOS a tabela CATEGORIA possui apenas 2 colunas e que não seguem a mesma sequência das colunas no arquivo de dados.



Para ler apenas a 3ª coluna de cada linha e popular corretamente a tabela CATEGORIA foi inserido o comando TRAILING NULLCOLS para que quando não houverem valores eles sejam nulos e foi criado colunas fictícias com o comando FILLER para simular as colunas do arquivo de dados.


Posterior a coluna código da CATEGORIA existe o FILLER x e y além da coluna nome. No arquivo de dados a coluna que contém os dados do nome da categoria é a 3ª e não a 4ª coluna, isto é necessário pois quando se trabalha com múltiplas tabelas e diferentes colunas de inserção o delimitador acaba se perdendo e para voltar ao início da linha foi atribuído junto ao comando FILLER da coluna x o comando position(1), por isso existem mais 3 colunas após a coluna categoria, justamente para simular as colunas fictícias desde o início de cada linha (null,null,CATEGORIA).


Executando a carga dos dados:

Registros carregados na tabela PRODUTOS:

Pelo output (saída) da execução do SQL*Loader já é perceptível que ocorreu a inserção de 12 linhas também na tabela CATEGORIA, mas temos apenas 4 distintas categorias. Como não é possível trabalhar com distinct no SQL*Loader, basta agora remover os valores duplicados da tabela CATEGORIA.

Além das opções e comandos citados que já tornam o SQL*Loader uma poderosa ferramenta, ainda existem diversas outras parametrizações e comandos que podem ser empregados na sua configuração deixando-o ainda mais rápido (direct, parallel, etc) e versátil (skip, begindata, dnfs_enable, etc).



Referências:


https://docs.oracle.com/database/122/SUTIL/oracle-sql-loader-concepts.htm

https://docs.oracle.com/database/122/SUTIL/oracle-sql-loader-commands.htm

13 visualizações0 comentário