No artigo de hoje iremos falar sobre o SQL Loader, um utilitário que permite ler dados de arquivos externos e carregar estes dados em tabelas de um Banco de Dados Oracle, com a vantagem de efetuar cargas de dados mais rápidas que os caminhos de atualização de dados convencionais, tais como scripts de INSERT.
O SQL Loader pode ser instalado através do instalador do Oracle Client e seu arquivo utilitário, sqlldr.exe, encontra-se na pasta "ORACLE_HOME\<versão do cliente>\bin".
Ex.: "C:\Oracle\Produto\Cliente\10gR2\BIN\sqlldr.exe".
Para executar uma carga de dados com o utilitário do SQL Loader , deve-se utilizar obrigatoriamente, como parâmetros, um arquivo de controle e um arquivo de dados. Opcionalmente, a carga de dados pode gerar log das operações executadas, dos dados rejeitados e dos dados descartados.
O arquivo de controle é um arquivo texto com uma extensão .ctl (preferencial, mas não obrigatória), que deve conter as seguintes informações: caminho e nome do arquivo de dados, formato dos dados, detalhes de configurações e como manipular os dados.
O arquivo de dados é um arquivo texto com uma extensão qualquer (.csv, .dat, .txt etc.), que poderá conter registros (linhas) em um dos seguintes formatos: registro fixo, registro variável e registro de fluxo.
O arquivo de log é um arquivo texto que contém um resumo detalhado do processo de carga.
O arquivo de dados rejeitados ou arquivo de erros (bad file), contém registros que são rejeitados pelo processo de carga por conterem dados em formato de entrada inválido. Ele permite verificar quais linhas do arquivo de dados foram rejeitadas, para que você possa posteriormente analisar a causa da rejeição.
O arquivo de dados descartados é criado somente quando for especificado no arquivo de controle e contém registros que são excluídos do processo de carga por não corresponderem aos critérios de seleção de carga, especificados no arquivo de controle.
Na Imagem 01 abaixo, podemos visualizar todos os itens citados citados acima e como eles interagem com o utilitário do SQL Loader:
Na Imagem 01 abaixo, podemos visualizar todos os itens citados citados acima e como eles interagem com o utilitário do SQL Loader:
 |
| Imagem 01 - Diagrama da Estrutura do SQL Loader Fonte: Oracle Corporation |
Neste artigo não explicarei todos os detalhes do SQL Loader. Meu objetivo é apenas apresentar uma visão geral e demonstrar seu desempenho (através de um exemplo).
Para verificar o desempenho do SQL Loader, criaremos os seguintes objetos e arquivos:
1- Uma tabela CLIENTES em um Banco de Dados Oracle com um usuário que tenha privilégios para criar tabelas em seu próprio schema:
CREATE TABLE cliente
(
cod_cliente NUMBER,
nom_cliente VARCHAR2(50),
dat_nascimento DATE,
dat_inclusao DATE
)
2- Um arquivo de controle com o nome clientes.ctl, que pode ser baixado a partir do Meu Sky Drive (ver painel inferior direito), pasta Oracle -> Scripts, arquivo clientes.zip.
Neste arquivo de controle foram parametrizados os seguintes valores:
- OPTIONS (SILENT=ALL, DIRECT=TRUE):
Suprime todas as mensagens de feedback durante a execução da carga e faz inserção em modo DIRECT PATH (mais rápido).
- UNRECOVERABLE LOAD DATA:
Não gera redo log.
- CHARACTERSET WE8ISO8859P1:
Utiliza character set WE8ISO8859P1.
- TRUNCATE:
Trunca dados da tabelas antes de iniciar a inserção dos dados.
- FIELDS TERMINATED BY '|':
Utiliza o caractere "|" como delimitador de colunas.
- OPTIONALLY ENCLOSED BY '"'
Indica que o caractere " (aspas duplas) pode ser utilizado opcionalmente como delimitador de valores.
- cod_eleitor SEQUENCE(Max):
Insere código sequencial na coluna "cod_eleitor".
- dat_nascimento "TO_DATE (:dat_nascimento, 'dd/mm/yyyy')":
Converte data de nascimento (coluna dat_nascimento), recuperada do arquivo de dados, no formato 'dd/mm/yyyy'.
- dat_inclusao SYSDATE:
Insere data/hora atual na coluna inclusão.
3- Um arquivo de dados no formato de registro variável, com o nome clientes.txt. Crie o arquivo com 254.000 linhas repetindo o conteúdo das 2 linhas abaixo:
"Homer Simpson"|"06/08/1910"
"Marge Simpson"|"23/10/1914"
Obs.: Crie os arquivos clientes.ctl e clientes.txt na mesma pasta.
4- Após criar os objetos e arquivos dos passos anteriores, abra uma janela de comandos do Sistema Operacional, entre na pasta em que você criou os arquivos e digite o comando abaixo, substituindo XXX e ZZZ pelo nome e senha do usuário que criou a tabela e YYY pelo nome da instância de Banco de Dados em que a tabela foi criada:
sqlldr userid=XXX@YYY/ZZZ control=clientes.ctl log=clientes.logO comando acima irá ler o arquivo de controle clientes.ctl e se tudo estiver OK, irá iniciar o processo de carga e gerar um arquivo de log com o nome clientes.log.
Para efeitos de comparação de performance entre um processo de carga executado pelo SQL Loader e um processo de carga executado por um script de comandos INSERT, seguem abaixo os resultados de um teste que eu fiz:
TESTE 1- Carga de dados de 254.000 registros via SQL Loader utilizando o exemplo deste artigo:
Tempo de execução: 04 segundos.
TESTE 2- Carga de dados de 254.000 registros via SQL Plus executando um script de comandos INSERT:
Tempo de execução: 12 minutos e 25 segundos.
Neste teste (conforme os parâmetros configurados) tivemos um mega ganho (18.625% superior) de desempenho no tempo de execução da carga utilizando o SQL Loader.
COMENTÁRIOS FINAIS:
Através dos resultados dos testes que eu fiz utilizando o exemplo deste artigo, conseguimos verificar que o SQL Loader é uma ótima ferramenta para ser utilizada para efetuar cargas de dados quando a origem dos dados é um arquivo externo, em formato texto.
Para mais informações, leia o FAQ oficial da Oracle e as referências deste artigo.
Para mais informações, leia o FAQ oficial da Oracle e as referências deste artigo.
Referências:
- SQL*Loader (sqlldr) Utility tips: http://www.dba-oracle.com/tips_sqlldr_loader.htm
- Oracle SQL*Loader: http://www.psoug.org/reference/sqlloader.html
- Oracle SQL*Loader Overview: http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html
- SQL*Loader Command-Line Reference: http://www.csee.umbc.edu/help/oracle8/server.815/a67792/ch06.htm
- SQL*Loader Control File Reference: http://www.cs.umbc.edu/help/oracle8/server.815/a67792/ch05.htm
- Treinamento oficial da Oracle "Oracle Database 10G Administration Workshop I".
Matéria simples, mas direta, o melhor são os links de referência que permitem entender os detalhes do SQL Loader.
ResponderExcluirObrigado pelo comentário Flávio. A idéia do artigo é realmente passar uma visão geral do SQL Loader de uma forma simples e rápida.
ResponderExcluir[]s
Fala Fábio;
ResponderExcluirCara estou com dúvidas sobre o Loader, porém não tem nada a ver com seu artigo, se puder me ajudar ficarei grato.
A dúvida é:
Tenho um arquivo .txt que é classificado em 0(Header), 1(Dados) e 9(Trailler). Dentro do Header(0) eu possuo um ID que não está contido nas demais classificações(1 e 9) do .txt e gostaria de saber como pegar esse ID do Header e adicionar em um determinado campo da tabela no banco de dados para todos os tipos de registros (0 - Header/1 - Detalhe/9 - Trailler)
Segue trecho de código do .txt:
posição 1 = classificação (0 - Header/1 - Detalhe/9 - Trailler)
08251234
1260612
9000000096
trecho do loader :
NU_SEQUENCIA SEQUENCE(MAX,1),
NU_MSG "825",
CD_TIPO_REGISTRO POSITION(1:1) INTEGER EXTERNAL,
CD_SOLICITACAO_DOWNLOAD POSITION(5:34) "DECODE(:CD_TIPO_REGISTRO,0,:CD_SOLICITACAO_DOWNLOAD,NULL)"
Obs: Neste caso ele adiciona NULL no ID para os registros de classificação 1 e 9.
Obrigado desde já.
Anonimo, infelizmente não tenho o tempo que eu gostaria de ter para ajudar a todos que me pedem uma ajuda mais avançada pelo blog ou e-mail. Sugiro que pesquise as referências que estão no final do artigo!
Excluir[]s
Como eu faço para cronometrar o tempo de carga de dados com o Linux, se eu faço a carga de dados no sqlplus eu faço o comando "set timing on" e tenho o tempo de duração da consulta, insert, update, etc.. Tem como calcular isso com o SQL*Loader?
ExcluirDentro do log do SQL Loader é gravado o tempo de execução da carga.
ExcluirQuando a tabela possui muitas constrainst, a carga de dados pelo SQL*Loader funciona do mesmo jeito???
ResponderExcluirExistem 2 tipos de cargas: convencional e direta. Na carga convencional funciona do mesmo jeito. Na carga direta, as constraints de FK, check constraints e triggers são desabilitadas durante o processo de carga.
Excluir[]s
Mas as constraints são desabilitadas pelo SQL*Loader ou eu tenho que entrar no banco, desabilitar as constraints e fazer a carga?
ExcluirAs constraints sao desabilitadas e reabilitadas automaticamente pelo SQL Loader.
ExcluirOlá Fábio.
ResponderExcluirTenho uma tabela com um campo texto de 400 posições. Na aplicação o pessoal populou esse campo e usou a tecla enter.
O resultado disso é que o Sql Loader, ao encontrar o ^M, entende que é uma nova linha e se perde.
Tens alguma sugestão para esse problema ?
Sds.
Pedro.
Pedro, eu não sei como resolver isso pelo SQL Loader, mas já eliminei problemas parecidos utilizando macros em editores de textos mais avançados, como por exemplo, o Notepad++.
ExcluirOutro problema que acontece é quando um determinado campo tem somente um espaço em branco, o Sql Loader interpreta como NULL. Tem como forçá-lo a interpretar como espaço ?
ResponderExcluirPedro, se o campo tiver espaço em branco o Oracle sempre vai interpretar como NULL, esse é um comportamento padrão do Oracle. Pq vc acha melhor ter um espaço em branco do que deixar o campo nulo?
ExcluirPesquisando nas referências deste artigo, olha só o que eu achei e que talvez possa te ajudar:
Excluir-------------------------------
Comparing Fields to BLANKS
The BLANKS keyword makes it possible to determine easily if a field of unknown length is blank.
For example, use the following clause to load a blank field as null:
full_field_name ... NULLIF column_name=BLANKS
The BLANKS keyword only recognizes blanks, not tabs. It can be used in place of a literal string in any field comparison. The condition is TRUE whenever the column is entirely blank.
The BLANKS keyword also works for fixed-length fields. Using it is the same as specifying an appropriately-sized literal string of blanks. For example, the following specifications are equivalent:
fixed_field CHAR(2) NULLIF (fixed_field)=BLANKS
fixed_field CHAR(2) NULLIF (fixed_field)=" "
-------------------------------
Eu nunca usei, mas encontrei isso no link: http://www.cs.umbc.edu/help/oracle8/server.815/a67792/ch05.htm
Fabio, não encontrei o sqlldr.exe na pasta bin do meu cliente. Sabe como faço para instalar? Existe uma versão especifica de cliente?
ResponderExcluirLuis, o "Oracle Client" não foi instalado no modo completo. Eu acho que o item que é necessário selecionar durante a instalação para ter o sqlldr.exe é o "Oracle Database Utilities...". Se não for, faça a instalação completa que aí eu tenho certeza de que você encontrará o sqlldr.exe, oK?
Excluir[]s
Bom dia Fábio!
ResponderExcluirCara, montei o arquivo .ctl certinho, ele insere os dados com sucesso na tabela, mas tem um porém, neste txt que tenho os dados, tem além de novos dados, alterações de campos que já existem nas tabelas. Para importar tudo, preciso desabilitar a constraint, só que aí fico com ID's repetidos na tabela. Sabe dizer se há alguma forma de tratar isso dentro do ctl? Criar um tratamento para ele entender que se o registro já existir, para fazer update, caso contrário insere.
Se puder ajudar ficarei muito grato!
Eric
Eric, nunca fiz nada deste tipo com o SQL Loader, mas pelo que eu já estudei sobre o assunto, tenho quase certeza de que não dá para incluir essa condição de fazer insert ou update.
ExcluirOutra técnica que dá fazer isso e qq tratamento condicional, que é mais flexível e mais performática que o SQL Loader (e que já estou ensinando no nos treinamentos de SQL Tuning), é criar uma tabela externa para ler o arquivo de dados e implementar uma stored procedure para processar os dados dessa tabela externa, inserindo-as em outras tabelas do BD. Para aprender a criar tabelas externas, leia o artigo: http://www.fabioprado.net/2010/09/tabelas-externas.html
[]s
[]s
Bom dia Fábio.
ResponderExcluirEm minhas interfaces atualmente, solicito aos clientes inserirem um asterisco na última linha, denotando que não há mais nenhuma informação a ser inserida. Via Sql Loader há como fazer algo parecido?
Obrigado
Carlos, eu acho desnecessário inserir esse asterisco, de qq forma, se ele for inserido vc pode incluir no arquivo de controle uma validação para quando encontrar o asterisco fazer algo diferente, gravando-o no arquivo de dados descartados.
Excluir[]s
Boa tarde Fábio, tudo bom?
ResponderExcluirNo Sql Loader que estou montando o cliente me pediu para desconsiderar a linha ser importada caso uma determinada palavra esteja presente, tem como fazer isso?
Tentei sem sucesso:
options (silent=(header,feedback,discards))
load data
infile *
append
into table ap_credit_card_trxns_all
when ((1:2) = '2A') and ((134:178) != '%SAQUE%')
( ... )
Se puder ajudar agradeço muito!
Rodrigo
Rodrigo, para te dar a solução eu teria que analisar seu arquivo de entrada, mas como nao vou ter tempo para fazer isso, sugiro que consulte as referencias ao final do artigo. O que eu posso te ajudar de forma rapida, é que vc está no caminho certo. Para ignorar determinadas linhas vc tem que incluir a cláusula when no arquivo de controle e isso vc já está fazendo. O erro deve estar na linha da sua cláusula when, ok?
Excluir[]s
Fabio,
ResponderExcluirEu tenho um arquivo.txt com apenas registros tam. 10 separador TAB. Ao executar o SQLLOADER não carrega na TABELA do BANCO. A estrutura da TABELA tem apenas um CAMPo tam 10. No arquivo de controle caminha está correto. Em uma maquina o processo se completa OK e em outra o processo os dados não são carregados e não dá nenhuma msg. de erro. Poderia me orientar qto. ao que avaliar. Variaveis de ambiente ? PATH ? Versão do SQLLOADER ? etc ....
O SQL Loader sempre gera um arquivo de log quando ele é executado. Qq falha no processo de carga será registrada lá. Se este arquivo não está sendo gerado é bem provável que o Sql Loader nem está sendo executado. Será que na máquina cliente que está falhando vc tem o executável do SQL Loader? Vc está informando o caminho completo do SQL loader (pasta + executável)? Se não está, o caminho da pasta do executável está inclusa no PATH do SO?
Excluir[]s
Olá,
ResponderExcluirTem como executar isso via java(.xhtml) chamando na pagina? Preciso fazer isso mais que seja via web.
Não sou programador Java, então não posso te afirmar isso com certeza, só sei que para funcionar a máquina do servidor web (no seu caso) tem que ter o utilitário do sql loader instalado (junto com o Oracle Client).
Excluir[]s
Olá. Como posso colocar condição em meu arquivo para realizar importação em tabela? Ex: Em minha se position 1:1 for igual 0 então insere os valores dessa linha em tabela0 , se position 1:1 for igual a 1 insere em tabela1.
ResponderExcluirRubens, pode sim. Eu nunca precisei usar, mas veja no link https://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_control_file.htm#i1005798 na seção "Relative Positioning Based on the POSITION Parameter" como fazer, ok
ExcluirObrigado Fabio, deu certo. Só usar o 'When' após a linha do into.
ExcluirWHEN (CODLINHA = '0')
Fabio. Sabe alguma maneira simples de executar ou fazer a chamada do meu arquivo.ctl por uma procedure ou usa-la em um Job?
ResponderExcluirMe explique melhor o que você precisa que aí tento te ajudar.
ExcluirEntão criei o meu arquivo.ctl, e quero que ele seja executado todo dia as 7 horas. Gostaria que esse processo fosse automatico em vez de entrar no sql plus e escrever uma linha para executar a importação. Ou se não, gostaria de ter alguma maneira de executar a importação pelo sqlloader por meio de uma procedure no banco.
ExcluirQuando tento usar no loader o SEQUENCE (MAX,1), ele carrega o primeiro arquivo, mas quando vai carregar o segundo, ele insere o ID do último registro incluído na tabela, duplicando o campo.
ResponderExcluirNunca fiz carga com mais de 1 arquivo, como está configurado o seu arquivo de controle? Talvez este erro esteja ocorrendo pq esse comando lê o maior valor da coluna na tabela, se o valor já foi utilizado, mas a transação nao foi commitada, é possível ocorrer a repetição!
Excluir