Incremental load - scripting problems

[#1]

Hi!

I have som problem writing my script that should be used for incremental load of data. As always when using incremental load I just want to load the posts that is new by looking at the field called CREATE_TIMESTAMP.

The problem is that i have to tables that i want to perform incremental load on, lets call it table1 and table2, in an Oracle database. It works fine when i am loading only from table1, only the new posts is loaded in to my qvd-file each time. When this is done i just want to load the data in table2 that is connected to the loaded posts in table1 since the other data already should be loaded. Here's the catch, the CREATE_TIMESTAMP is only in table1 and table2 is associated with table1 with an key (table1.ID = table2.ID2). I ONLY want to load the posts from table2 that is associated via the key on the loaded posts in table1.

Hope you understand my problem.

Thank you.

Hannes

[shantanu73]

Hello Hannesse,

I am not so very clear. If u provide me the script I may understand. And then I may provide you the solution.

Thank

Shantanu

[#3]

shantanu73, heres the script:

/* Setup for incremental load. */

SET vQvdFile='File.qvd';

SET vPKID='ID1';

// Set a variable indicating if the QVD exists or not. -1 is True, 0 is False

LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);

IF $(vQvdExists) THEN                    // QVD exists, we will do an incremental reload

          maxdateTab:

          // Get the max date from this QVD for use in incremental SELECT

          LOAD max(CREATE_TIMESTAMP) as maxdate

          FROM $(vQvdFile) (qvd);

  LET vIncrementalExpression = 'WHERE CREATE_TIMESTAMP >= ' & chr(39) & timestamp(peek('maxdate')) & chr(39);

          DROP table maxdateTab;

ELSE                                                            // QVD does not exist

          LET vIncrementalExpression = '';           // No QVD. Force full reload

END IF

ODBC CONNECT TO [database;DBQ=DATABASE ] (XUserId is XXXXX, XPassword is YYYY);

Table1:

SQL SELECT BIRTHDATE,

    "FIRST_NAME",

    GENDER,

    "CREATE_TIMESTAMP",

    ID as "ID1",                                        //Primary Key

    "LAST_NAME",

    "WORK_TITLE",

FROM PROD.TABLE1; 

//Heres the problem, i only want to load the posts that is associated with the newly loaded posts from table1

//Maybe i need to store old posts from table2 in another qvd-file as well and concatenate the new posts to that?

Table2:

SQL SELECT "DATE",

    "RESULT",

    ID2,

    ID1 as "Table2_ID"    //NOTE THAT ID2 is the field that should be associated with ID1 in table1

FROM PROD.TABLE2;

/*

Update the QVD with changes.

*/

Directory;

IF $(vQvdExists) THEN

          // Use CONCATENATE in case we've added any new fields.

          CONCATENATE (Table1) LOAD * FROM $(vQvdFile) (qvd)

          WHERE NOT exists($(vPKID))          // Load only QVD rows that were not already loaded in the data load.

          ;

END IF

//Overwrite the QVD with the QV datatable. 

STORE Table1 INTO $(vQvdFile);

THANKS!

/Hannes

[shantanu73]

Hello Hannes,

Sorry of Late Answer,

Check the following Script. If you get some idea.

//Start from Here:

Let ThisExecTime = ReloadTime();

QV_Table:
SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
       AND ModificationTime < #$(ThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD
WHERE NOT EXISTS(PrimaryKey);

Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

If ScriptErrorCount = 0 then
STORE QV_Table INTO File.QVD;
Let LastExecTime = ThisExecTime;
End If

//End from Here:

Regards

Shantanu

[#5]

Thank you Shantanu, after some modification it works fine!

Aanother problem: I want to count how many times the PrimaryKey occurs in the table. Problem is that one post in table1 can be connected to several posts in table2, but still, i only want to count it once. If I do count(PrimaryKey) it returns to many!

Thank you in advance!

Hannes

[#6]

Hi Hannes,

Maybe you can do this.

table1:

Load *,

PrimaryKey as pKey

From xxx

Then count the number of pKey in table 1 should give you the correct count.

Regards,

Xue Bin

[#7]

You Chek this One.

Table_Name:

                              LOAD *;

                              SQL  SELECT  * FROM Table_Name

                                        where TO_CHAR(to_DATE($(vModificationTime),'YYYY/MM/DD HH24:MI:SS'),'YYYYMMDDHH24MISS')>='$(T)';

                              Concatenate ([Table_Name])

Table_Name:

                              LOAD * from Table_Name .qvd (qvd)

                              WHERE NOT Exists ($(Y));

                              inner join ([Table_Name])

                              sql select Pk  FROM Table_Name;

          ENDIF

                    store Table_Name into Table_Name.qvd ;

//..............................................................................................//

                    LET T= (DATE(NOW(),'YYYYMMDD')  & ((hour(NOW()))*10000)  + (MINUTE(NOW())*100)  + SECOND(NOW()));

[#8]

adityatiwari, that looks pretty much the same as my script. It still returns to many of PrimaryKey...

Hannes

[#9 · Accepted solution]

          select TABLE_NAME from SYS.ALL_TABLES

           WHERE TABLESPACE_NAME=USERS  AND OWNER=OWNER_NAME;

Left Join

          SELECT A.TABLE_NAME  , B.COLUMN_NAME 

          from all_constraints A ,

          all_cons_columns B

          WHERE A.CONSTRAINT_TYPE ='P' AND  A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

          ORDER BY A.TABLE_NAME;

This quarry Return Pk for all table and that pk is used in script.

[#10]

Thank you, I think i solved it!

/Hannes