Where Not Exists Loading only one row

[elijahabel]

Our team needs to implement incremental loading, and our loading is currently done in batches. The idea is that each group of rows from one batch is given a load_id, and we should be able to use that to load only new rows in.

[Table1]:

Load Value,

          Department,

          load_id as LoadID

          from [lib://....qvd](qvd);

Concatenate(Table1)

Load Value,

          Department,

          load_id as LoadID

          from IMPALA.Database

          where not exists(LoadID, load_id);

This issue is that this only loads the first row of each new load_id and then it stops. Seems that after the first row is loaded, it no longer passes the "not exists" because, well, it exists now.

Are there any other methods for loading using an ID like this that is not unique to each row, but still unique to each load?

Thank you for any help.

[kenphamvn]

Hi

Please try this

[Table1]:
Load Value,
          Department,
          load_id as LoadID
          from [lib://....qvd](qvd);

noConcatenate
TmpTbl:
Load Value,
          Department,
          load_id as LoadID
          from IMPALA.Database
          where not exists(LoadID, load_id);

Concatenate(Table1)
Load Value,
          Department,
          LoadID
          resident TmpTbl;
drop table TmpTbl;

Regards

An Pham

[elijahabel · Accepted solution]

An Pham wrote:

Hi
Please try this

1. [Table1]: 
2. Load Value, 
3.           Department, 
4.           load_id as LoadID 
5.           from [lib://....qvd](qvd); 
6.  
7. noConcatenate 
8. TmpTbl: 
9. Load Value, 
10.           Department, 
11.           load_id as LoadID 
12.           from IMPALA.Database 
13.           where not exists(LoadID, load_id); 
14.  
15. Concatenate(Table1) 
16. Load Value, 
17.           Department, 
18.           LoadID 
19.           resident TmpTbl; 
20. drop table TmpTbl; 

[Table1]:
Load Value,
          Department,
          load_id as LoadID
          from [lib://....qvd](qvd);

noConcatenate
TmpTbl:
Load Value,
          Department,
          load_id as LoadID
          from IMPALA.Database
          where not exists(LoadID, load_id);

Concatenate(Table1)
Load Value,
          Department,
          LoadID
          resident TmpTbl;
drop table TmpTbl;

Regards

An Pham

This also only concatenated one row for load_id, but it got me on the right line of thinking. Here's what worked:

1. [Table1]: 
2. Load Value, 
3.           Department, 
4.           load_id as LoadID 
5.           from [lib://....qvd](qvd); 
6.  
7. noConcatenate 
8. TmpTbl: 
9. Load Value, 
10.           Department, 
11.           load_id,
12.           from IMPALA.Database 
13.           where not exists(LoadID, load_id); 
14.  
15. Concatenate(Table1) 
16. Load Value, 
17.           Department, 
18.           load_id as LoadID,
19.           resident TmpTbl; 
20. drop table TmpTbl;

It turns out that if you rename load_id to LoadID on the initial load of the TmpTbl, it stops passing the "where not exists" clause after the first row is concatenated. So if you load all of it and change the name when you concatenate it, then it works perfectly.

Thank you for the help!