Incremental Loading in SQL Database

[Badzreyes00]

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;

[mfchmielowski]

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? 

[Badzreyes00]

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 😞 

[mfchmielowski]

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. 

[Badzreyes00]

Hello @mfchmielowski ,

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

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)';

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

[Badzreyes00 · Accepted solution]

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.

[mfchmielowski]

Hi.

Good to hear 🙂