Automatizando Bulk Data Loads num Oracle Database com Shell Script e SQL*Loader

Olá pessoal!

Espero que estejam bem!

Recentemente, um cliente me procurou com um problema urgente: eles precisavam carregar 456 arquivos em uma tabela do Oracle Database para resolver um problema de integração. Esses arquivos estavam comprimidos em formato ZIP e continham dados em registros de posição fixa com 201 colunas. A tarefa era descompactar dinamicamente cada arquivo e carregar os dados em uma única tabela, fazendo o append dos registros e incluindo o nome de cada arquivo na tabela para rastreabilidade.

Neste blog post vou mostrar como realizei essa tarefa através de Shell Script e SQL*Loader.

On this blog post I’ll show how I accomplished this using a combination of Shell Script and SQL*Loader.

A exigência do cliente era clara:

• Descompactar cada arquivo;
• Carregar os dados de cada arquivo na mesma tabela do banco de dados;
• Realizar o append dos registros ao realizar a carga;
• Incluir uma coluna adicional na tabela para armazenar o nome de cada arquivo sendo processado;
• Dado o número de arquivos e o volume de dados, precisávamos de uma solução eficiente e automatizada.

Criei um shell script para lidar com o processo de descompactação e carga. O script atualiza dinamicamente o arquivo de controle do SQL*Loader para incluir o nome de cada arquivo durante o processo de carga

Vamos fazer um break down da solução:

Shell Script para Automação

• Descompacta cada arquivo;
• Itera através de loop com os arquivos de dados descompactados;
• Atualiza dinamicamente o arquivo de controle;
• Carrega os dados usando SQL*Loader;
• Move os arquivos processados para um diretório de arquivamento.

SQL*Loader Control File

• Mapeia todas as 201 colunas;
• Inclui uma coluna adicional para o nome do arquivo.

Vamos criar nossa estrutura de pastas em nosso servidor Oracle, vamos imaginar que temos um ponto de montagem NFS/oracle_nfs.

Com o usuário owner do BD Oracle no Sistema Operacional:

Agora, vamos criar nosso Arquivo de Controle do SQL*Loader:

OPTIONS (DIRECT=true,SKIP=1)
LOAD DATA
INFILE 'dummy.dat'
BADFILE 'dummy.bad'
DISCARDFILE 'dummy.dsc'
APPEND INTO TABLE OWNER.TABLE_TO_BE_LOADED
WHEN (COL01 != '                    ')  
TRAILING NULLCOLS
(
    COL01               POSITION(1:20)   CHAR "RTRIM(:COL01)",
    COL02               POSITION(21:28)  CHAR,
    COL03               POSITION(29:32)  CHAR,
    COL04               POSITION(33:33)  CHAR,
    COL05               POSITION(34:58)  CHAR,
    COL06               POSITION(59:73)  CHAR,
    COL07               POSITION(74:76)  CHAR,
    COL08               POSITION(77:80)  CHAR,
    COL09               POSITION(81:90)  CHAR,
    COL10               POSITION(91:92)  CHAR,
    COL11               POSITION(93:94)  CHAR,
    COL12               POSITION(95:95)  CHAR,
    COL13               POSITION(96:96)  CHAR,
    COL14               POSITION(97:97)  CHAR,
    COL15               POSITION(98:99)  CHAR,
    COL16               POSITION(100:109) CHAR,
    COL17               POSITION(110:139) CHAR,
    COL18               POSITION(140:142) CHAR,
    COL19               POSITION(143:144) CHAR,
    COL20               POSITION(145:147) CHAR,
    COL21               POSITION(148:148) CHAR,
    COL22               POSITION(149:173) CHAR,
    COL23               POSITION(174:188) CHAR,
    COL24               POSITION(189:191) CHAR,
    COL25               POSITION(192:192) CHAR,
    COL26               POSITION(193:201) CHAR,
    COL27               CONSTANT 'DUMMY.DAT'
)

Explicação dos parâmetros do Control File:

• DIRECT=true: Este parâmetro instrui o SQL*Loader a usar direct path load, o que pode aumentar significativamente a performance do carregamento ao ignorar alguns dos processos durante a carga regular (como logging e rollback);
• SKIP=1: Este parâmetro informa ao SQL*Loader para pular o primeiro registro no arquivo de dados. Isso é frequentemente usado quando a primeira linha do arquivo contém cabeçalhos ou metadados que não devem ser carregados na tabela. Este é o nosso caso aqui;
• INFILE ‘dummy.dat’: Especifica o nome do arquivo de dados a ser carregado. Isso será substituído dinamicamente em nosso script;
• BADFILE ‘dummy.bad’: Especifica o nome do arquivo onde os registros que sofrerem quaisquer tipos de erros durante a carga serão escritos. Isso será substituído dinamicamente em nosso script;
• DISCARDFILE ‘dummy.dsc’: Especifica o nome do arquivo onde os registros que não atendem a nenhuma condição WHEN serão escritos. Isso será substituído dinamicamente em nosso script;
• APPEND INTO TABLE OWNER.TABLE_TO_BE_LOADED: Especifica a tabela na qual os dados serão carregados. A opção APPEND diz ao SQL*Loader para adicionar os novos dados aos dados existentes na tabela (de forma contrária ao INSERT, que exigiria que a tabela estivesse vazia);
• WHEN (COL01 != ‘ ‘): Esta condição especifica que apenas registros onde COL01 não possui 20 espaços em branco serão carregados. Isso pode ser usado para filtrar registros indesejados;
• TRAILING NULLCOLS: Esta diretiva permite que o SQL*Loader trate quaisquer colunas que não possuírem valores no arquivo de dados como nulas. Isso pode ajudar a evitar erros se alguns registros não contiverem valores para todas as colunas;
• Depois dessas definições, temos as definições de COLUNAS, que não explicarei porque são autoexplicativas :-), mas falarei sobre a última coluna:
  • CONSTANT ‘DUMMY.DAT’: Em vez de ler do arquivo de entrada, isso especifica que a coluna será sempre preenchida com o valor constante ‘DUMMY.DAT’. Em nosso script, isso é substituído dinamicamente pelo nome real do arquivo sendo processado.

Então, em resumo, nossa estrutura de arquivo de controle do SQL*Loader nos permite:

• Carregar dados diretamente na tabela, pulando o primeiro registro;
• Filtrar registros com base em uma condição;
• Tratar adequadamente colunas nulas;
• Aplicar transformações específicas para cada campo;
• Realizar o append dos dados a uma tabela existente sem remover os dados antigos;
• Capturar o nome do arquivo em uma coluna dedicada para rastreabilidade.

Agora, vamos criar nosso Shell Script para carregar os arquivos. Nosso script será chamado APP_Loader.sh:

#!/bin/bash
####################################################################################
# Script Name : Data_Loader.sh
# Author      : Marcus Vinicius Miguel Pedro
# Date        : 2024-06-07
# Version     : 1.0
# Description : This script processes ZIP files containing integration data files.
#               It unzips each ZIP file, loads the data from each .APP file 
#               into the Oracle database using SQL*Loader, and then moves the
#               processed files to an archive directory.
#
# Usage       : ./APP_Loader.sh
#
# Parameters  :
#               None
#
# Prerequisites:
#               - Update the script with the credentials of a user who has
#                 permissions to insert data into the target table.
#               - Adjust directory paths if necessary.
#
# Notes       :
#               - The script assumes the .APP files are named appropriately.
#               - Ensure there is sufficient disk space in the directories.
####################################################################################

ZIP_DIR="/oracle_nfs/APP_Loader"
UNZIP_DIR="/oracle_nfs/APP_Loader"
LOADED_DIR="/oracle_nfs/APP_Loader/APP_Loaded"

for zip_file in "$ZIP_DIR"/*.ZIP; do
    unzip -o "$zip_file" -d "$UNZIP_DIR"
    
    for data_file in "$UNZIP_DIR"/*.APP; do
        filename=$(basename "$data_file" .APP)
        
        sed "s/CONSTANT 'DUMMY.DAT'/CONSTANT '$filename.APP'/" APP.ctl > temp_APP.ctl
        
        sqlldr userid=myuser/mypassword control=temp_APP.ctl bad="$filename.bad" log="$filename.log" data="$data_file"
    done
    
    rm -f "$data_file"
    
    mv "$zip_file" "$LOADED_DIR"
    mv *.log "$LOADED_DIR"
    mv *.bad "$LOADED_DIR"
done

Vamos fazer o breakdown do nosso script:

• ZIP_DIR: Esta variável contém o caminho do diretório onde os arquivos ZIP estão armazenados;
• UNZIP_DIR: Esta variável contém o caminho do diretório onde os arquivos ZIP serão descompactados. Neste caso, é o mesmo que o ZIP_DIR;
• LOADED_DIR: Esta variável contém o caminho do diretório onde os arquivos processados (tanto os arquivos ZIP quanto os arquivos de log e bad correspondentes) serão movidos após o processamento;
• Então temos dois loops:
  • O principal itera basicamente descompactando os arquivos ZIP, chamando o loop interno e depois movendo os arquivos ZIP, arquivos de log e arquivos bad para LOADED_DIR;
  • O loop interno itera sobre os arquivos APP (dados) a serem carregados, extraindo o nome base do arquivo de dados, removendo a extensão .APP, armazenando-o na variável filename; em seguida, substituirá o ‘DUMMY.DAT’ pelo arquivo de dados sendo processado; como último passo, executará o SQL*Loader.

Benefícios ao usar essa abordagem:

• Automação: Todo o processo é automatizado, reduzindo a intervenção manual e o risco de erros;
• Eficiência: O direct path load com o SQL*Loader acelera significativamente o processo de carga de dados;
• Escalabilidade: : O script pode lidar com qualquer número de arquivos, facilitando a escalabilidade para grandes volumes de dados;
• Rastreabilidade: Incluir o nome do arquivo na tabela garante que cada registro possa ser rastreado até o arquivo de origem;
• Performance: O direct path load otimiza o desempenho ao ignorar certas etapas de processamento, como logging e rollback.

Vamos agora mostrar um exemplo do script funcionando. Com o usuário que é proprietário do DB no Sistema Operacional:

Apenas uma parte do output está exibido abaixo:

Archive:  /oracle_nfs/APP_Loader/0015_0006_20240406060029_APP20240406-063847-568.ZIP
  inflating: /oracle_nfs/APP_Loader/0015_0006_20240406060029.APP  

SQL*Loader: Release 19.0.0.0.0 - Production on Fri Jun 7 21:19:32 2024
Version 19.21.0.0.0

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

Path used:      Direct

Load completed - logical record count 7133.

Table OWNER.TABLE_TO_BE_LOADED:
  7133 Rows successfully loaded.

Check the log file:
  0015_0006_20240406060029.log
for more information about the load.
Archive:  /oracle_nfs/APP_Loader/0015_0006_20240408060025_APP20240408-061329-963.ZIP
  inflating: /oracle_nfs/APP_Loader/0015_0006_20240408060025.APP  

SQL*Loader: Release 19.0.0.0.0 - Production on Fri Jun 7 21:19:34 2024
Version 19.21.0.0.0

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

Path used:      Direct

Load completed - logical record count 12198.

Table OWNER.TABLE_TO_BE_LOADED:
  12198 Rows successfully loaded.

Check the log file:
  0015_0006_20240408060025.log

Perfeito! O script rodou bem rápido. Agora podemos fazer algumas verificações. Vamos para o diretório onde os arquivos foram movidos após o processamento:

Agora, vamos verificar se tivemos algum erro:

Como podemos ver acima, não tivemos nenhum erro. O que é ótimo!

Agora, vamos verificar os registros carregados:

De acordo com a saída acima, carregamos aproximadamente 2.6 milhões de registros na tabela. Vamos agora verificar a tabela:

Perfeito, o count na tabela bate/corresponde com a informação do log!

Combinando Shell Script e SQL*Loader, podemos criar uma solução robusta e simples para carregar centenas de arquivos (ou talvez milhares, se necessário) no Oracle Database de forma eficiente. Esta abordagem não só atende aos requisitos do cliente, mas também proporciona um método escalável e rastreável para lidar com grandes volumes de dados. Seja lidando com poucos arquivos ou milhares, este script pode simplificar seu processo de integração de dados e garantir um bom desempenho geral.

Espero que ajude!

Abraços!

Vinicius