topic Re: Target Lookup in Qlik Replicate

Target Lookup

srikar22 — Tue, 02 Jul 2024 19:46:08 GMT

We are unable to load the data using the target lookup getting an error

target_lookup('NO_CACHING','TargetSchema','TargetTable','max(load_date)','','') < $LOAD_DATE

We are using the following Target lookup to load the table only when the Load date from the source table is greater than Load date in Target table.

We are using this target_lookup in FullLoad Filter

Here is the error.

Stream component 'st_1_ORA_FINCAPRD' terminated
Stream component failed at subtask 1, component st_1_ORA_FINCAPRD
Error executing source loop
Endpoint is disconnected
Failed to init unloading table 'SNAPLOGIC'.'WEEKLY_CCS_EXPEND'
ORA-00911: invalid character

Re: Target Lookup

SwathiPulagam — Tue, 02 Jul 2024 20:43:01 GMT

Hi @srikar22 ,

Below is the article regarding Target_lookup for your reference:

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/ta-p/1805653

If you still need assistance, I recommend contacting Professional Service.

Because Target_lookup impacts the performance of your data load so implement it with the help of our expertise.

Thanks,
Swathi

Re: Target Lookup

srikar22 — Tue, 02 Jul 2024 20:46:03 GMT

Hello Swathi,

I already looked up that post.Please let us know how to form a Target lookup for my use case.

Thanks

Re: Target Lookup

Heinvandenheuvel — Wed, 03 Jul 2024 16:59:22 GMT

>>> target_lookup('NO_CACHING','TargetSchema','TargetTable','max(load_date)','','') < $LOAD_DATE

Please provide the EXACT syntax uses. Cleary this is just a made up example with at least faked target names and perhaps more was wrong, but i think it was fine here.

>>> Failed to init unloading table 'SNAPLOGIC'.'WEEKLY_CCS_EXPEND'

>>> ORA-00911: invalid character

The (TARGET|SOURCE)_LOOKUP function perform a string manipulation to generate SQL code:

select EXP from SCHM.TBL where EXP

You did not provide an EXP string, so the result became:

select max(load_date) from TargetSchema,TargetTable where

That's not valid SQL and Oracle complained.

I suggest you retry using for example 1=1 as EXP.

Hein.

Re: Target Lookup

srikar22 — Wed, 03 Jul 2024 17:13:19 GMT

Thank you.

I am trying to avoid duplicates in the target table

I am trying to load target data using a filter on load_date. if the source table load_date is greater than Target table load_date then only insert the data if not ignore the replication to target

Here is the syntax I am using

$LOAD_DATE > target_lookup('NO_CACHING','qlik_replicate','weekly_ccs_expend_old','max(load_date)','load_date=:1','$LOAD_DATE')

Here is the Log Message

Target endpoint does not support data enrichment feature

Source is Oracle

Target is Redshift

Thanks

Re: Target Lookup

Heinvandenheuvel — Wed, 03 Jul 2024 17:27:00 GMT

You first post reported "ORA-00911: invalid character" strongly suggesting an Oracle endpoint, and nothing to the contrary was indicated.

Now you write "Target is Redshift". That's a whole other animal.

And indeed "Target endpoint does not support data enrichment feature"

Replicate R&D apparently believes that it cannot provide a useable solution (performance wise) for a Redshift target. It would be too slow, and soo expensive queries to perform against a datawarehouse style target. A similar restriction exists for Snowflake.

You may want to look for a better design. Perhaps just using error handling "update row if exists" which will pre-delete (if exists) and re-insert. That's cheaper than testing to avoid dups. And check out (search) "Full Load Passthru" filtering to avoid bringing in too much data. For example: https://community.qlik.com/t5/Official-Support-Articles/Filter-for-last-90-days-of-data-in-Qlik-Replicate/ta-p/1880761

Hein.

Re: Target Lookup

Heinvandenheuvel — Thu, 04 Jul 2024 03:39:26 GMT

@srikar22 - I tried to reproduce "ORA-00911: invalid character" but did not get it so far. What replicate version? What was the exact lookup statement you used? I only got "[SOURCE_CAPTURE ]E: Failed to rebind parameter for data lookup".

I got that when using source_lookup(.... TBL,'',''). as well as source_lookup(.... TBL,'1=1','').

I could not use source_lookup(.... TBL) omitting COND,COND_PARAMS completely. That gives a parse error in the designer gui.

What did work for me is : source_lookup(.... TBL,'1=1') - omitting COND_PARAMS.

@Dana_Baldwin , @john_wang - maybe someone can submit a minor code/documentation enhancement to allow lookups wit not condition, essentially leaving out the WHERE clause. And we may want to document the '1=1' style workaround and being able to omit the COND_PARAMS maybe writing ....,COND [,COND_PARAMS] )

Hein.

Re: Target Lookup

john_wang — Thu, 04 Jul 2024 05:18:29 GMT

Hello @Heinvandenheuvel ,

I can open an internal ticket for this issue. It appears that the WHERE clause is essential to ensure that the primary key or unique index is utilized, rather than performing a full table scan. This will help prevent the lookup() query from impacting the database performance.

Additionally, the lookup() query should return exactly one row if the row exists. Without a WHERE clause, it is challenging to guarantee the uniqueness of the result set.

Regards,

John.

Re: Target Lookup

Heinvandenheuvel — Thu, 04 Jul 2024 12:09:47 GMT

>> It appears that the WHERE clause is essential to ensure that the primary key or unique index is utilized, rather than performing a full table scan.

I get that, but that's not up to Replicate to decide. And Replicate should never knowingly send bad SQL to a database IMHO specially here where it is probably easy to fix.

The intended/expected (target|source)_lookup is a possibly very large table of values with a unique key.

But that's not what customers come up with. They use "exists" to give a singleton yeah/nay. They use a min or max with a 1 hour or 1 day cache. They can design a control table with a single row and a cut-off date, or enable flag. In Oracle they can use a SELECT X FROM DUAL perhaps just to see if they are in development or prod ?!

In short, I believe there are many circumstance where no selection expression may be needed and it would be nice if that was simply allowed. If it is not allowed then report during task edit/load/activate - do not send a broken statement to a DB to find out.

fwiw,

Hein.

Re: Target Lookup

john_wang — Thu, 04 Jul 2024 13:59:36 GMT

Thanks for the detailed explanation, @Heinvandenheuvel , I will open an internal ticket.