topic Re: Qlik Catalog OPENCONNECTOR to write to PostgreSQL as target in Catalog and Lineage

Qlik Catalog OPENCONNECTOR to write to PostgreSQL as target

LucaMCap — Wed, 14 Sep 2022 10:34:55 GMT

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.

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

Re: Qlik Catalog OPENCONNECTOR to write to PostgreSQL as target

Gerald_U — Wed, 14 Sep 2022 13:30:22 GMT

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

Re: Qlik Catalog OPENCONNECTOR to write to PostgreSQL as target

LucaMCap — Wed, 14 Sep 2022 13:03:00 GMT

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

Re: Qlik Catalog OPENCONNECTOR to write to PostgreSQL as target

Gerald_U — Wed, 14 Sep 2022 13:29:46 GMT

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

Re: Qlik Catalog OPENCONNECTOR to write to PostgreSQL as target

LucaMCap — Wed, 14 Sep 2022 13:40:13 GMT

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.

Re: Qlik Catalog OPENCONNECTOR to write to PostgreSQL as target

Gerald_U — Wed, 14 Sep 2022 16:06:52 GMT

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

Re: Qlik Catalog OPENCONNECTOR to write to PostgreSQL as target

LucaMCap — Thu, 15 Sep 2022 09:01:14 GMT

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.

Re: Qlik Catalog OPENCONNECTOR to write to PostgreSQL as target

LucaMCap — Thu, 15 Sep 2022 10:14:44 GMT

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.

Re: Qlik Catalog OPENCONNECTOR to write to PostgreSQL as target

Gerald_U — Wed, 21 Sep 2022 20:48:48 GMT

Hello,

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

Thanks