topic Re: Qlik Replicate | Custom SQL in Qlik Replicate

Qlik Replicate | Custom SQL

Sauree_10 — Tue, 22 Jun 2021 11:38:52 GMT

All,

Can I write the customized sql query while selecting the data in Qlik replicate?

If, yes how to do so?

Thanks,

Saurabh

Re: Qlik Replicate | Custom SQL

Alan_Wang — Tue, 29 Jun 2021 18:01:43 GMT

Hi Saurabh,

You do not write customized SQL queries for selecting data in Qlik Replicate. You can use table settings to enable filters on the columns that will filter data for Replication.

Thanks,

Alan

Re: Qlik Replicate | Custom SQL

Clever_Anjos — Mon, 02 Aug 2021 16:43:55 GMT

If you plan to use a Full Load (not CDC) you could create a view with your SQL and use it as a source

Re: Qlik Replicate | Custom SQL

zparveen — Thu, 09 Feb 2023 21:27:53 GMT

Do you have sample query filters for eg adding Limit condition in the Pass through filters.

Only selecting 1000 rows. Can you provide some samples please

Re: Qlik Replicate | Custom SQL

zparveen — Thu, 09 Feb 2023 21:29:04 GMT

I'm working on the POC , Postgres SQL as the source and AWS S3 as the target endpoints

Re: Qlik Replicate | Custom SQL

shashi_holla — Thu, 09 Feb 2023 21:51:02 GMT

@zparveen You have to add following text in the passthru filter field for the table

1=1 LIMIT 1000

Internally Replicate will convert it as follows:

select a,b from table where 1=1 LIMIT 1000

Thank you,

Re: Qlik Replicate | Custom SQL

zparveen — Thu, 09 Feb 2023 22:08:11 GMT

Thanks Shashi for responding, I tried your filter but still shows it is picking 10 million records when the task is running. Do we have any Qlik guide on this queries pass through?

Re: Qlik Replicate | Custom SQL

Michael_Litz — Thu, 09 Feb 2023 22:18:39 GMT

Hi @zparveen ,

Please have a look at this Techspert where there is a section on doing passthrough filters.

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Loading-Best-Practices/ta-p/2028269

Let me know if this helps out.

Thanks,
Michael

Re: Qlik Replicate | Custom SQL

narendersarva — Thu, 09 Feb 2023 23:09:12 GMT

Hi @zparveen

You can try to create a view in the same database which will select the 1000 rows.

Thanks
Naren

Re: Qlik Replicate | Custom SQL

john_wang — Fri, 10 Feb 2023 05:38:44 GMT

Hello @zparveen , @Sauree_10 ,

For PostgreSQL source database, the LIMIT rows sample is:

1=1) LIMIT 1000 ;--

Hope this helps.

Regards,

John.

Re: Qlik Replicate | Custom SQL

zparveen — Fri, 10 Feb 2023 15:06:29 GMT

Thanks a lot John, the above query did help me. Can you provide me where I can get this query format or what language is this? So I can come up for my other scenarios that I'm trying to test. Thanks for the help again!

Re: Qlik Replicate | Custom SQL

john_wang — Sat, 11 Feb 2023 03:37:06 GMT

Hello @zparveen ,

Glad to hear you are interesting how Replicate works 🙂 Well, it's not a special language but only a few 'tricks':

1- The whole passthrough filter expression in the GUI will be executed in source side database so its syntax relies on source database; that means you may use 3rd party JDBC/ODBC query tools to test your SQL, eg SQL Workbench/J, RazorSQL, DBeaver etc, it's much easier to debug SQL than using Replicate task running.

2- The whole passthrough filter expression (eg "xxxx" ) will be sent to source database combined with "WHERE ( xxxx )" sub-clause. No way to repress the WHERE sub-clause as it's hard coded, however we may use single right parenthesis ")" to end up the WHERE sub-clause prior to embedded ")".

3- "LIMIT nnnn" cannot be included in WHERE clause, we have to move it out of "WHERE ( xxxx )" string

4- To make sure no empty parenthesis "()" generated (it may lead syntax error), we add "1=1" (it's TRUE forever). Certainly you may add more clauses eg "1=1 and ID >= 20230101" if necessary (ID is your table column) then you may keep or remove "1=1".

5- Now we end up the expression by semicolon, it looks like "1=1) LIMIT 1000 ;". The last step is removing the latest useless embedded ")": We add comment string "--" before it, PostgreSQL will ignore the comment string "--)". So the whole expression in PostgreSQL is:

1=1) LIMIT 1000 ;--

With above expression, Replicate will execute the query as:

2023-02-10T17:00:46 [SOURCE_UNLOAD ]T: SELECT ... FROM ... WHERE (1=1) LIMIT 1000 ;--), postgres_construct_select_statement

Hope it helps,

Regards,

John.

Re: Qlik Replicate | Custom SQL

zparveen — Sat, 11 Feb 2023 15:06:32 GMT

Thanks a lot John for the explanation!!