Hi Jonathan,
If I read your screenshot correctly, you have a dimension table
"dim_account", and you mentioned that the "email_key" field in that
table is not unique. In that case, the lookup transform will not work,
it will always just return one record. In your case, that's the first
record it happens to find.
(In other words, for every record in your main data stream it can only
find one record in the lookup table. By the way, if the lookup field
(email_key in your case) is the primary key in that table, then lookup
happens exponentially faster. But since you have email duplicates, that
doesn't work in your case.)
This is exactly as it should be. Let's say you have three accounts with
the same email address. If the lookup step would return multiple
records, then it would triplicate each record from your incoming stream
with that email address.
As others have mentioned, you may want to consider a join instead.
I hope that's helpful.
Gert
On 2023-03-15 5:05 a.m., Jonathan Haehnel wrote:
Dear Hop Team,
I tested the Database lookup step with two different Databases
(mysql&redshift).
Let’s say there is one input row. The lookup column value from input
relates to several rows in the lookup table. I would expect to get
several rows out as a result but only get one. Am I doing something
wrong or is this a bug (didn’t fine a ticket for it in githup)?
I can work around this issue using Database join but its not so nice,
having to write a query and I assume also not as effective.
select account_key
from dwh.dim_account
where email_key = ?
Kind Regards
Jonathan
Jonathan Haehnel
Business Intelligence Engineer
Travian Games GmbH | Wilhelm-Wagenfeld-Str. 22 | 80807 Munich | Germany
Tel.: +49 / (0)89 / 324 915 144
[email protected]
http://www.traviangames.com
<http://atpscan.global.hornetsecurity.com/index.php?atp_str=Oyvn9fZ6t_uhls14PQ1YE4Rgk3-XXrn-XGZqpjy0WfPCi2pdXoJAZe5aUUACYzJhp-HhMq0mS_6qp9fL9UjipNZdx1BCNtknARdaPf2SZkry4uPLLNEpEuWnhks8OA8D8bIi1CqWvpC-rjiibIY8krxEtK2eO_Ar3ZaeeEbRkPWNykOHKWLEZCq8QAHFCkteMA2iftHEMjA2M7o0AYpdaTk6AKxquuaZP7_g0rdaoyVhJea86bW9cMjGPVX7BJsvdGT2Kp2o_zX0uOFDY_fvKZjO21QGMGudxZDkm5l0z8OpKKo5CwIq0jpdAHlUyG-mCy2oqrGWIzo6IwvudY5yNluzP2h5ziM6OiMTC6x8QJ2Knq3krqE_Jznf>
Registered office: Munich
Commercial register number: HRB 173511
Managing Directors: Jörg Strathaus, Frederik Hammes
VAT ID: DE246258085
This email and its attachments are strictly confidential and are
intended solely for the attention of the person to whom it is
addressed. If you are not the intended recipient of this email, please
delete it including its attachments immediately and inform us accordingly.
