Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
LucaMCap
Partner - Contributor II
Partner - Contributor II

Qlik Catalog OPENCONNECTOR to write to PostgreSQL as target

Hi,

i have a single node instance of Qlik Catalog version May 2022. 

I would like to publish data to PostgreSQL as target, but in the target definition i only see the OPENCONNECTOR choice that seems to fit.

LucaMCap_0-1663151365629.png

1) Is possible to write data directly to PostgreSQL from Qlik Catalog?

2) Is OPENCONNECTOR the right choice to achieve this goal?

3) Does someone have done this before? If yes do you have and example of the script and a detailed step by step guide to call it from Catalog?

Thank you

Luca

 

Labels (1)
8 Replies
Gerald_U
Support
Support

Hello,

Please review the documentation link below to see the list of accepted target types:
https://help.qlik.com/en-US/catalog/August2022/Content/QlikCatalog/Publish/Publish_Add_Target.htm

 

Thank you,

Gerald 

LucaMCap
Partner - Contributor II
Partner - Contributor II
Author

Hi Gerald,

in the doc there is written:

  • Open Connector/ RDBMS: File-based targets published to RDBMS via Sqoop transfer

Does your answer mean RDBMS is not supported at all or is not supported on the single node instance of Qlik Catalog?

Thank you

Gerald_U
Support
Support

Hello,

You are correct. 

You could publish to Postgres using the Open Connector/ RDBMS: File-based targets published to RDBMS via Sqoop transfer option.

It looks like it can be done using the Bash SQOOP export script in the documentation. 

You can give it try and see if the option works for you.

Thanks,

Gerald 

LucaMCap
Partner - Contributor II
Partner - Contributor II
Author

Hi Gerald,

Sqoop is not available on single node Qlik Catalog. Since it is a BigData tool i suppose it works only with the HDFS/Hive version of Qlik Catalog.

My question is: Is there a way to use openconnector even in a single server instance? The documentation is really poor about it.

Gerald_U
Support
Support

Yes, I believe you can use open connection in a single instance. 

All you need is the JDBC connection string to connect to postgresql and ensure that you have the JDBC drivers under

[qdc@CENTOS701 jdbcDrivers]$ pwd
/usr/local/qdc/jdbcDrivers

 

Thanks,

Gerald 

LucaMCap
Partner - Contributor II
Partner - Contributor II
Author

I am a bit confused, what command should i write in the "custom script command" input?

You have the psql command under /usr/pgsql/qdc11-15

and you can run : psql "host=127.0.0.1 port=5432 dbname=postgres user=postgres password=postgres" -c "\copy ...." 

maybe first there is the need to export the env var: 

export PSQL_HOME=/usr/pgsql/qdc11-15

export PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PSQL_HOME/bin

The question is how can i run all these command in "custom script command" input? 

You do not need the jdbcDrivers.

LucaMCap
Partner - Contributor II
Partner - Contributor II
Author

i have created an sh file containing the instructions and calling it via sh ... in the "custom script command" worked.

Now the question is how do i reference the data that must be saved on PostgreSQL? Should i do everything manually referencing the csv paths locations? In this case i do not see any advantage adding an entity to the cart and then publishing it via OPENCONNECTOR.

Gerald_U
Support
Support

Hello,

Yes, You need to manually reference the csv file directory location. 

Thanks