SPUPad example

This example uses the nzlua table function, which is automatically installed in the database by the nzLua installation script. In addition to the code itself, three small tables are created by this SQL script to properly demonstrate the capabilities of nzlua in combination with the SPUPad features.

drop table elt_source;
drop table elt_good;

drop table elt_bad;

create table elt_source (str varchar(1000)) distribute on random;
create table elt_good (id integer, dt date, text varchar(255)) distribute on random;

create table elt_bad (str varchar(1000), mesg varchar(255)) distribute on random;

insert into elt_source values ('1|20100101|aaaaaa');
insert into elt_source values ('2|2010-01-02|bbbb');
insert into elt_source values ('3|20100103|cccccc');
insert into elt_source values ('4|20100104|ddd');
insert into elt_source values ('x|20100130|eeeeeeeeee');
insert into elt_source values ('6|20100110|ffffff|fffff');
insert into elt_source values ('7|20100132|ggggg');
insert into elt_source values ('9|20101011|hhhhhhhhhhhhhhhhhhhhhhh');

begin;

\echo
***********************************************************************
\echo **** insert good rows into ELT_GOOD, bad rows get saved in SPUPad
\echo
***********************************************************************
insert into elt_good (id,dt,text)
select id, dt, text
from elt_source, table with final(nzlua('
function initialize()
errors={}
end

function saveError(str,mesg)
push(errors, { str, mesg })
end

function processRow(str)

t = split(str,"|")

if #t != 3 then
saveError(str, "Invalid number of columns!" )
return null
end

id = tonumber(t[1])
if id == null then
saveError( str, "column 1: Invalid id = " || t[1])
return null
end

ok,dt = pcall(to_date, t[2], "YYYYMMDD")
if not ok then
saveError( str, "column 2: Invalid date = " || t[2])
return null
end

text = t[3]

return { id, dt, text }

end

function outputFinalRow(rownum)
if rownum > 1 then return null end
saveTable("errors", errors)
return null

end

function getShape()
columns={}
columns[1] = { "id", integer }
columns[2] = { "dt", date }
columns[3] = { "text", varchar(255) }
return columns
end'
,str)) tf;


\echo
***********************************************************************

\echo **** Extract bad rows from SPUPad and insert them into ELT_BAD table
\echo
***********************************************************************
insert into elt_bad (str,mesg)
select str, mesg
from _v_dual_dslice, table(nzlua('
function initialize()
errors=restoreTable("errors")

end

function processRow()
if errors == null or #errors == 0 then return null end return errors

end

function getShape()
columns={}
columns[1] = { "str",	varchar(255) }
columns[2] = { "mesg", varchar(255) }
return columns
end',
dsid)) tf;

commit;

\echo **** GOOD DATA (ELT_GOOD table)

select * from elt_good;

\echo **** BAD DATA (ELT_BAD table)
select * from elt_bad;