Insert Object in Postgres from Cassandra

Highlighted
Seven Stars

Insert Object in Postgres from Cassandra

Hi,

 

I am trying to insert date in Postgres from a Cassandra's table. In particular, I have two column defined as:

 

typeSmiley Surprisedbject

db_type:set

 

the corresponding column I have created in Postgres are defined as json []. This is an example of the column in Postgres:

map.png

I got the following error:

 

3eaddd5f-653e-4b17-bb1a-bc455614f170|||||||||||{carddatainputcapability:NULL,cardholderauthenticationcapability:NULL,cardcapturecapability:NULL,operatingenvironment:NULL,cardholderpresent:NULL,cardpresent:NULL,carddatainputmode:NULL,cardholderauthenticationmethod:NULL,cardholderauthenticationentry:NULL,carddataoutputcapability:NULL,terminaloutputcapability:NULL,pincapturecapability:NULL}|||||||||||690|zero|||Sat Jan 01 01:00:00 CET 2000|||Tue May 29 01:52:08 CEST 2018|02008|698|85528164|1644||690|||Tue May 29 01:52:08 CEST 2018|263873|9|0|0|115152220|5925|42185|3||||||||||501|85538348|1520|180529|180529|978|2|00068|||681|Tue May 29 01:52:08 CEST 2018|00068||LUF|02008|24945|697|85528163|1644|80199|434|PSB||||02008|695||1644|00068||3|MCC|MAST|1|N||380|00000009086|D||SUR|P|MPR|D||[{generaladdendumlist:{},microchip:NULL,freetext:NULL,additionalpermerchant:NULL,telephonecompany:NULL,passengertransportaddendumlist:{},merchantinformation:{messagetypeidentifier:'1644',functioncode:'696',messagenumber:'53331800',formatcode:'A2',forwardingreferencedata:'705023 000000',cardacceptorbusinesscode:5411,name:'PROVE POS',street:'VIA ROSSI 2',city:'VERONA',postalcode:'37132',region:'VR',countrycode:'ITA'},lodgingaddendumlist:{},carrentaladdendumlist:{},moneytransfer:NULL,fleetlist:{},purchasinglist:{},dccaddendum:NULL}]|||0|0|0|649318||0|||||||0||3027305|||30309909|0||1||6||0||Tue Aug 01 00:00:00 CEST 2023|0||0||0|2||0||000|||||R|||||||||||181490000000515|200||||0|||85528690|1240||||||9||00000|000||1||||A|5333179998705023|||0|||180516092507157|00|148000584007|P|||0||ZERO|0|||6990|||000000000000000|Mon May 28 11:00:46 CEST 2018|0|0|0||01||
Exception in component tDBOutput_9 (subOpenConn)
org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of com.datastax.driver.core.UDTValue. Use setObject() with an explicit Types value to specify the type to use.
at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1740)
at cre.subopenconn_0_1.subOpenConn.tCassandraInput_1Process(subOpenConn.java:13580)
at cre.subopenconn_0_1.subOpenConn.runJobInTOS(subOpenConn.java:15656)
at cre.subopenconn_0_1.subOpenConn.main(subOpenConn.java:15505)
[statistics] disconnected

 

How can I insert the two object columns in Postgres?

Does anyone could help me?

Moderator

Re: Insert Object in Postgres from Cassandra

Hello,

About inserting an Array type on tPostgresqlOutput component, could you please let us know if this jira issue helps?

https://jira.talendforge.org/browse/TUP-10233

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seven Stars

Re: Insert Object in Postgres from Cassandra

Hi,

 

no, I mapped the column as string in Talend and db_type varchar.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog