Append into the LookUp Fileset?

[Thanh Dalton]

Is it possible to append into the LookUp Fileset in a later job after it was created and loaded by a job?
Anyone knows?
Thanks in advance!

[ray.wurlod]

I don't think it's possible. There is no overwrite/append property in the Lookup File Set stage. But you can construct something like the following.

Code: Select all

LookupFileSetprevioussource
     |
     V
  Funnel  ---->  LookupFileSet
     ^
     |
(new data)

[Thanh Dalton]

Thanks for your response, Ray.

I tried but the LookupFileSetprevioussource doesn't support an output link into Funnel or anything. I did the test to put the data into a dataset at the sametime as into the LookupFileSetprevioussource. Later, I could append into the dataset in a different job and reload the LookupFileSet.

I'm just wondering about this kind of performance. Is there any better way?

Thanks a bunch!

[ArndW]

What sort of a problem are you having going from a Lookup Fileset to a join or funnel stage?

[Thanh Dalton]

When I use LookUpFileset as a source and Funnel stage as a destination and I got a message saying :The source stage does not support stream output links!

[PhilHibbs]

When I use LookUpFileset as a source and Funnel stage as a destination and I got a message saying :The source stage does not support stream output links!

That's right, it doesn't. Once your data goes into a LookUpFileset, the only thing you can do is to look up against it. You have to jump through hoops in order to accumulate data from several sources over time, re-creating the file set from another copy of the source data that can be streamed.

I don't think that Lookup File Sets are a good idea anyway. Your stream data has to be partitioned the same way that the Lookup File Set was partitioned when it was created, which is not efficient (you might have a small set of look up data, but have to re-partition millions of rows of stream data just to do the look up on that key). I put all my look up data in regular File Sets.

[ArndW]

Phil has it - I wasn't thinking before I responded! I never use lookup filesets.

[Thanh Dalton]

Thanks to Phil and ArndW!

My experience with the table to do the LookUp for 300M rows take so long. That's why I prefer to use LookUpFileset instead. It has proved to perform very well. In this case, I can append into a dataset and reload to the LookUp Fileset. I tested for 100M rows to load the LookUp Fileset from a dataset and it took me only 9 minutes. So I guess, I go for the dataset then!

Thanks to all for the help!

[ray.wurlod]

By LookupFileSetprevioussource I meant the job that populated the Lookup File Set. The Lookup File Set stage itself does not support stream output.