Preprocessing External Tables

Consider we are receiving a compressed file, storedata.gz, on a daily basis and storing it in Hadoop HFDS, we need to first unzip it to storedata.csv then adjust the raw data into a standard format before reading from an external table.

Here is how the data might look in storedata.csv:

Notice that the first row has two store number ‘STORE01/09S’ in one line. We want to split it into two records in order to easily aggregate to the location column and perform analysis.

Ideally, we want to get the desired output directly from the compressed data file received daily without having to run any other process each time there is a read from the external table.

Let’s begin with creating the necessary directory to be used by the external table. Note for testing purposes I’m only using one directory. For security, it’s recommended to keep executables and data in different directories, and group all required preprocessor tools into a dedicated directory then grant EXECUTE privileges to the users who need them.

CREATE OR REPLACE DIRECTORY hdfs_dir AS '/data/hdfs/data/';

CREATE OR REPLACE DIRECTORY exter_dir AS '/data/ora_exter_dir/'

We will then create a quick script storedata.ksh to decompress the data file and adjust the format.

#!/bin/ksh

Tempfile=temp_storedata.csv
L=1

/bin/gunzip -c $1 | while read line
do
    if [ ${L} -eq 1 ]
    then
            echo $line > $tempfile
            let L=L+1
            continue
    fi
    
    array=(${line//\,/ })
    total=${#array[@]}
    lp=""
    rp=""
    Col=1
    for i in "${!array[@]}"; do
        each=${array[i]}
        if [ ${Col} -eq 1 ]
        then
            newarray=(${each//\// })
            newtotal=${#newarray[@]}
            if [ $newtotal -eq 2 ]
            then
                lpp=${newarray[0]}
                rpp=${newarray[1]}
                lp=${lpp}S
                rp=${lpp: 0 :${#lpp}-2}${rpp}
            else
                echo $line >> $tempfile
                let Col=Col+1
                continue 2
            fi
        else
            lp=$lp","${array[i]}
            rp=$rp","${array[i]}
        fi
        let Col=Col+1
    done
        
    echo $lp >> $tempfile
    echo $rp >> $tempfile
    
    let L=L+1
    
done
/bin/cat $tempfile
/bin/rm -f $tempfile

The final remaining step is to create the external table, you can see we used PREPROCESSOR script storedata.ksh at here.

CREATE TABLE TEST.STORE_DATA
(
    LOCATION     VARCHAR2 (100 BYTE),
    ITEM         VARCHAR2 (100 BYTE),
    MONTH        VARCHAR2 (100 BYTE),
    PRICE        VARCHAR2 (100 BYTE),
    UNITS        VARCHAR2 (100 BYTE)
)
ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
                DEFAULT DIRECTORY exter_dir
                    ACCESS PARAMETERS (
                        RECORDS DELIMITED BY NEWLINE
                        PREPROCESSOR 'storedata.ksh'
                        SKIP 1
                        FIELDS
                            TERMINATED BY ','
                        MISSING FIELD VALUES ARE NULL
                    )
                LOCATION (nfs_dir:'storedata.gz'))
    REJECT LIMIT 0;

Let’s check the content of this table.

This is only one small demonstration of the power of PREPROCESSOR in external tables. You can apply it to almost any type of prior processing required to fit your needs, without introducing additional dependencies while saving storage space.