topic Where Not Exists Loading only one row in App Development

Where Not Exists Loading only one row

elijahabel — Fri, 28 Sep 2018 00:29:00 GMT

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.

Re: Where Not Exists Loading only one row

kenphamvn — Fri, 28 Sep 2018 00:57:47 GMT

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

Re: Where Not Exists Loading only one row

elijahabel — Fri, 28 Sep 2018 04:00:52 GMT

An Pham wrote:

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;

[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:

[Table1]:
Load Value,
Department,
load_id as LoadID
from [lib://....qvd](qvd);
noConcatenate
TmpTbl:
Load Value,
Department,
load_id,
from IMPALA.Database
where not exists(LoadID, load_id);
Concatenate(Table1)
Load Value,
Department,
load_id as LoadID,
resident TmpTbl;
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!