Solved: Qlik Replicate | Custom SQL - Qlik Community

Sauree_10 · ‎2021-06-22

All,

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

If, yes how to do so?

Thanks,

Saurabh

john_wang · ‎2023-02-10

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

Alan_Wang · ‎2021-06-29

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

If the issue is solved please mark the answer with Accept as Solution.

Clever_Anjos · ‎2021-08-02

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

zparveen · ‎2023-02-09

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

zparveen · ‎2023-02-09

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

shashi_holla · ‎2023-02-09

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

zparveen · ‎2023-02-09

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?

Michael_Litz · ‎2023-02-09

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

Let me know if this helps out.

Thanks,
Michael

narendersarva · ‎2023-02-09

Hi @zparveen

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

Thanks
Naren

john_wang · ‎2023-02-10

Hello @zparveen , @Sauree_10 ,

For PostgreSQL source database, the LIMIT rows sample is:

1=1) LIMIT 1000 ;--

Hope this helps.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

Qlik Replicate | Custom SQL

Connectivity - Sources or Targets