You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hop.apache.org by "barbosaadriano (via GitHub)" <gi...@apache.org> on 2025/07/30 00:41:14 UTC
[I] [Bug]: Dimension lookup/update component doesn't looking up data based on date field (hop)
barbosaadriano opened a new issue, #5594:
URL: https://github.com/apache/hop/issues/5594
### Apache Hop version?
2.14.0
### Java version?
18.0.1.1
### Operating system
Windows
### What happened?
I'm working with a MariaDB database (version 10.5.27), which serves as both the target for data insertion and the source for lookups.
However, I'm encountering an issue when using the Dimension/Lookup step in my ETL pipeline. Specifically, when using a Date field as the lookup key, the component fails to return the correct surrogate key. Instead, the result is always 0—which is typically the default for null or unmatched values—even though the date exists in the dimension table and is formatted correctly.
What I’ve observed:
The "Data" field is of type DATE, using the format YYYY-MM-DD, consistent across both the stream and the dimension table.
Before the Dimension/Lookup step, I can confirm that the date is being passed correctly in the stream (see screenshot below).
📷
<img width="538" height="362" alt="Stream Date Field" src="https://github.com/user-attachments/assets/b9e1f29a-e9d4-48cf-9b81-0d20028ba9d2" />
After the Dimension/Lookup step, the output shows the surrogate key (sk_tempo) as 0 for all rows, even though matching dates exist in the dimension table.
📷
<img width="682" height="531" alt="Lookup Result - Skipped Match" src="https://github.com/user-attachments/assets/c49352e6-1c22-4e6a-b2cd-1d550c6eef1a" />
This issue only seems to affect lookups based on Date fields. For all other dimensions where the lookup key is something other than a Date (e.g., String or Integer), everything works as expected.
Below are the screenshots showing how the Dimension/Lookup step is configured:
📷
<img width="953" height="761" alt="Lookup Configuration 1" src="https://github.com/user-attachments/assets/8310dd71-7afe-41c2-93b9-cbf70411a8b3" />
📷
<img width="929" height="761" alt="Lookup Configuration 2" src="https://github.com/user-attachments/assets/264fe41a-c31d-4430-a6e6-dcd845f4a3ae" />
📷
<img width="932" height="754" alt="Lookup Configuration 3" src="https://github.com/user-attachments/assets/4b1c9988-fbfe-4ec2-99a3-eb8c2d782bca" />
Finally, here's a screenshot of a record in the database that should match based on the Date field:
📷
<img width="1127" height="247" alt="Database Record" src="https://github.com/user-attachments/assets/3d8e8e87-a26c-4ad8-90cc-ad2ce24b79c8" />
### Issue Priority
Priority: 2
### Issue Component
Component: Other, Component: Actions
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Dimension lookup/update component doesn't looking up data based on date field (hop)
Posted by "mattcasters (via GitHub)" <gi...@apache.org>.
mattcasters commented on issue #5594:
URL: https://github.com/apache/hop/issues/5594#issuecomment-3280805821
Using the changed date as a natural key doesn't really make any sense to me when building a slowly changed dimension.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Dimension lookup/update component doesn't looking up data based on date field (hop)
Posted by "barbosaadriano (via GitHub)" <gi...@apache.org>.
barbosaadriano commented on issue #5594:
URL: https://github.com/apache/hop/issues/5594#issuecomment-3300600486
Thank you so much!
It's definitely a bug. It was totally my misunderstanding of concepts.
Now I made a new version of my Time Dimension, using an Insert / Update component, just like that
<img width="1623" height="474" alt="Image" src="https://github.com/user-attachments/assets/8112e7e8-bda6-4a50-8128-fcba4dd6357a" />
And changed to the database lookup component, instead using dimension lookup / update.
<img width="452" height="370" alt="Image" src="https://github.com/user-attachments/assets/561edc1c-ef4b-466c-826f-2fbe4568f5b6" />
I apologize for opening this bug issue without really needing to.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Dimension lookup/update component doesn't looking up data based on date field (hop)
Posted by "mattcasters (via GitHub)" <gi...@apache.org>.
mattcasters commented on issue #5594:
URL: https://github.com/apache/hop/issues/5594#issuecomment-3297533069
A date dimension is typically not slowly changing but fixed. Simply generate a sequence for X days and add it to a start date with a calculator. Then calculate all sorts of things like day of month, month, year, ... and store it in the date dimension.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Dimension lookup/update component doesn't looking up data based on date field (hop)
Posted by "barbosaadriano (via GitHub)" <gi...@apache.org>.
barbosaadriano commented on issue #5594:
URL: https://github.com/apache/hop/issues/5594#issuecomment-3297466821
> Using the changed date as a natural key doesn't really make any sense to me when building a slowly changed dimension.
Hi Matt, you're right. Now that I think better on it. I think I'm doing something wrong. My intention is perform a lookup on time dimension using some date to retrieve the surrogate key of the time dimension. I tried to use the dimension lookup update component without the option to update.
I will review my aproach, and report back here again to close this issue.
Thanks.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Dimension lookup/update component doesn't looking up data based on date field (hop)
Posted by "barbosaadriano (via GitHub)" <gi...@apache.org>.
barbosaadriano closed issue #5594: [Bug]: Dimension lookup/update component doesn't looking up data based on date field
URL: https://github.com/apache/hop/issues/5594
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org