topic Incremental Loading in SQL Database in App Development

Incremental Loading in SQL Database

Badzreyes00 — Tue, 01 Jun 2021 14:45:47 GMT

Hi Everyone! Hope you can help me with my dilemma.

We are extracting large tables from a SQL database and we are really experiencing problems since it is now taking almost 24hrs just to load all the tables. So I am exploring incremental loading but I am still building the incremental load script and I upon testing my SQL where clause using a variable doesn't work (I am still extracting the whole table from SQL)

Here is my script:

//Get the max date from current table//

CurrentQVD_Table:
LOAD
max(tmp_date) as max_date
FROM [$(vRawQVDPath)/CurrentQVD_Table.qvd]
(qvd);

//Store in a variable

Let vMaxQvdDate = Date(Peek(max_date,0,WAT),'YYYYMMDD');

//Connect to database and extract only where tmp_date > max_date

LIB CONNECT TO 'WAT DB (cimbph_phcc21bj)';
DB_TableA:
LOAD id,
`cust_number`,
`trxn_number`,
`tmp_date `;
SQL SELECT id,
`cust_number`,
`trxn_number`,
`tmp_date `
FROM DB.`DB_TableA`
where `tmp_date `>'$(vMaxQvdDate)';

Drop Table CurrentQVD_Table;
exit script;

BTW: I have tried the below variable formula as well:

--Let vMaxQvdDate = max_date;

Re: Incremental Loading in SQL Database

mfchmielowski — Tue, 01 Jun 2021 17:30:03 GMT

Hi.

First of all check the syntax of peek function.

Let vMaxQvdDate = Date(Peek(max_date,0,WAT),'YYYYMMDD');

Replace this with:

Let vMaxQvdDate = Date(Peek('max_date' ,0,'CurrentQVD_Table') ,'YYYYMMDD');

This date format is also confusing. Check the date format in db. This is not a standard one.

Another step is to check scriptlog. What sql query is Send to db?

Re: Incremental Loading in SQL Database

Badzreyes00 — Wed, 02 Jun 2021 06:17:48 GMT

Thank you so much @mfchmielowski !

Can you give me the path of scriptlog?

the date format is the current date format of the tmp_date in db ex. 20210602

This script below gives me the correct max date = 20210530 as value when I tried it, I just applied peek since I saw it in a different post.

CurrentQVD_Table:
LOAD
max(tmp_date) as max_date
FROM [$(vRawQVDPath)/CurrentQVD_Table.qvd]
(qvd);

I tried updating my Peek formula but It did not fetch any rows at all 😞

Re: Incremental Loading in SQL Database

mfchmielowski — Wed, 02 Jun 2021 06:29:22 GMT

Hi.

I dont know your enviroment so this community post should be helpfull.

So if this gives you the correct result:
CurrentQVD_Table:
LOAD
max(tmp_date) as max_date
FROM [$(vRawQVDPath)/CurrentQVD_Table.qvd]
(qvd);

Let vMaxQvdDate = Date(Peek('max_date' ,0,'CurrentQVD_Table') ,'YYYYMMDD');
exit script; // do not load data from sql.

Check the value of variable vMaxQvdDate.

Re: Incremental Loading in SQL Database

Badzreyes00 — Wed, 02 Jun 2021 07:14:54 GMT

Hello @mfchmielowski ,

yes it gives correct date result, but when I apply it in the where clause here:

It still extracts the whole table and not just rows with tmp_date > max_date

Re: Incremental Loading in SQL Database

Badzreyes00 — Wed, 02 Jun 2021 07:41:57 GMT

Hi @mfchmielowski ,

I tried only using this

Let vMaxQvdDate = Peek('max_date' ,0,'CurrentQVD_Table') ;

and it works perfectly. Thank you so much! I appreciate your quick response to my concerns.

Re: Incremental Loading in SQL Database

mfchmielowski — Wed, 02 Jun 2021 08:19:25 GMT

Hi.

Good to hear 🙂