Incremental load

[#1]

Hi,

Anybody can help me on setting Incremental load on the below query.

I dont want to reload the old data every time I clcik on reload..

I want just the new data to be added.

Thanks

LOAD  Distinct

     CallID,

     OriginalParty,

FROM

The X directory receives new log files every 15 minutes and I want just to load the new files and add them.

[#2]

LOAD  Distinct

CallID,

OriginalParty,

FROM

////////////////////////////////////////////Make it QVD File

CDR_Temp:
LOAD  Distinct

CallID,

OriginalParty,

FROM

Store CDR into CDR_Temp.QVD;
Drop Table CDR_Temp;

//////////////////////////////////////then it will read first the QVD File, next concatenate it
////////////////////////////////////// to the new data

CDR_updated:
LOAD  Distinct

CallID,

OriginalParty,

FROM ..\\cdr.Qvd;

Concatenate

LOAD  Distinct

CallID,

OriginalParty,

Store CDR_updated into CDR_Temp.qvd;        ////////////Then Rename the new updated file like
Drop Table CDR_updated;                     ///////////CDR_Temp.Qvd   this is the updated file                                                 ///////////that your final application will read
///////////every time you want to update, reload the                                             ///////////processs

I hope it helps!!

[rwunderlich]

Assuming these are text files, just add a BUFFER prefix.

CdrTab:

BUFFER LOAD  Distinct

     CallID,

     OriginalParty,

FROM

;

New files will be added automatically. And new rows from old files will be added as well.

-Rob

http://robwunderlich.com

[#4]

Should I have all the first and the second loads?

it seems to me that I dont need the first load if I weant to store it in QVD.right?

[#5]

Rob,

this didn't work ..It still loading all the files again..

[#6]

Yes , you are right we don´t need the first table!!

[#7]

So my script should look like this:

CDR_Temp:
LOAD  Distinct

           CallID,

           OriginalParty,

FROM

Store CDR into CDR_Temp.QVD;
Drop Table CDR_Temp;

//////////////////////////////////////then it will read first the QVD File, next concatenate it
////////////////////////////////////// to the new data

CDR_updated:
LOAD  Distinct

           CallID,

           OriginalParty

FROM ..\\cdr.Qvd;

Concatenate

LOAD  Distinct

           CallID,

           OriginalParty

Store CDR_updated into CDR_Temp.qvd;        ////////////Then Rename the new updated file like
Drop Table CDR_updated;                  ///////////CDR_Temp.Qvd   this is the updated file                                                                                                            ///////////that your final application will read
                                                                  ///////////every time you want to update, reload the processs

right?

I am just trying to make sure before I reload beause it takes about 10 hours to finish the load.

[#8]

Also what if I have a where statement after the load?

how it can be added after the second load from QVD?

Thanks

[rwunderlich]

It will load all the files the first time the BUFFER is run. Then after that it will appear to read the files -- but it really just checking filetime & size, which is very fast -- and then gets the data from the local BUFFER. It will reread all the physical files when:

1. The LOAD statement changes.

2. The script is moved to a new machine where the BUFFERed QVD doesn't exist.It will thenget created on first load.

-Rob

[#10]

CDR_Temp_History:
LOAD  Distinct

CallID,

OriginalParty,

FROM

                                  ////// this is the history table

Store CDR into CDR_Temp.QVD;
Drop Table CDR_Temp;

CDR_Temp:
LOAD  Distinct

CallID,

OriginalParty,

FROM ..\\cdr.Qvd;                                                               ////// this is the history table in QVD

Concatenate

LOAD  Distinct

CallID,                                                    ////// this is the updated table in excell or whatever is your case

OriginalParty

FROM CDR_Update.XLS;

Store  CDR_Temp into CDR_Temp_History;        ///// Now this new file is the one your final application will read.

Drop Tble CDR_Temp;