Target Lookup

[srikar22]

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

[SwathiPulagam]

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-...

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

[srikar22]

Hello Swathi,

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

Thanks

[Heinvandenheuvel]

>>> 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.

[srikar22]

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

[Heinvandenheuvel]

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-Repl...

Hein.

[Heinvandenheuvel]

@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.

[john_wang]

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.

[Heinvandenheuvel]

>>  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.

[john_wang]

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