Re: Dimension lookup/update

[podunk]

That's a good idea - thank you!

 

 

Sent: Tuesday, August 30, 2022 at 1:53 PM
From: "Hans Van Akelyen" <hans.van.akel...@gmail.com>
To: users@hop.apache.org
Subject: Re: Dimension lookup/update

Another solution to avoid all the lookups is to use a table input and load the source table.

Then you can use "Merge Rows (diff)" to do a comparison between the old set and the new set.

It will mark the rows as new,identical,changed,deleted after that you can use "Synchronize after merge" and it will do the insert or update depending on the flag field.

 

This way you do not bash your database with all the lookup queries, you do have to load and sort the entire source table to do the comparison against.

 

Cheers,

Hans

 

On Tue, 30 Aug 2022 at 13:47, <pod...@gmx.com> wrote:

 

Thank you both for quick reaction.

 

I'm searching for some way to quickly update table with millions of records.

 

I know 'Insert / update' but this is terrible slow - so may queries.

 

I know I can do some workaround like instert into temp table and execute SQL query (update table taking data from another table).

I was thinking that dimension table is something that could work here.

 

 

 

Sent: Tuesday, August 30, 2022 at 1:09 PM
From: "Bart Maertens" <bartm...@apache.org>
To: users@hop.apache.org
Subject: Re: Dimension lookup/update

Hi Mike, 
 

The Dimension Lookup/update transform populates Slowly Changing Dimensions, typically type 2 [1] with some additional functionality. 

 

The "date range start", "date range end" and "version" fields are technical fields that are used to determine the validity period and version number for a dimension record. 

If you don't need any of the versioning information, you're probably not maintaining a slowly changing dimension. 

Hop comes with other transforms like "Table output", "Insert/update", "Database lookup" etc that are better suited to maintain regular tables or type 1 dimensions. 

 

[1] https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row

 

Regards,

Bart 

 

On Tue, Aug 30, 2022 at 1:01 PM <pod...@gmx.com> wrote:

 

Hello!

 

I try to use 'Dimension lookup/update' hop and I do not understand the meaning of two fields in properties:

 

"Date range start field / Table date range end"

 

My data has no date column. I do not need it. So I cannot specify any date. What this field is for and how to ignore it?

 

 

What is exactly 'Version field'? This is to keep several datasets in one table? I.e same record can have multiple copies with different 'Version'?

What if I do no not need (most of scenarios I think) any 'versions', I just need single copy of data?

If I have 'version number' I can leave 'key fields' empty?

 

Regards,

 

Mike