Use json column in Database for postgreSQL as sink

Question

Use json column in Database for postgreSQL as sink

Javier Nieto 26

When trying to use a derived json field to fill a json column in Azure Database for postgreSQL I'm getting the error below at validation, is there a way to feed a json column using Data factory activities or do I need to create a service or FaaS to do the job?

svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-11-10T13:45:42.837+00:00

Hello @Javier Nieto ,

Thanks for the question and using MS Q&A platform.

I did refer this document about the connector-postgresql and as per the error -looks like Complex Data type is currently supported REST,SNOWFLAKE,ORC,JSON,AVRO or Azure Cosmos DB. I'll also have check internally.

Meanwhile, just wanted to understand - whether you could push the data as a json formatted string to PostgreSQL (rather than complex type) and consume it at the PostgreSQL end? I did find community threads was achieving this requirement.
Javier Nieto 26 Reputation points

2021-11-10T15:35:43.867+00:00

Hello @svijay-MSFT ,

thanks for the suggestion but I'm planing to use one of the json keys as an index at postgreSQL, so json as string it's not a valid scenario

BR
Javier Nieto 26 Reputation points

2021-11-17T12:12:56.233+00:00

Hello @svijay-MSFT

thanks for the suggestion but still getting an error at writing the toString(Complex) to the postgreSQL json column, even the preview from derived column is looking good. I've tried also by escaping the string just in case but same result
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-11-22T10:14:56.05+00:00

I tried the similar at my end and was not able to encounter the issue. Just wanted confirm the configuration at your end.

I created a table in postgreSQL as below with two columns

Id and info (json column)

At the dataflow end :

This was the structure of the complex field.

I was able to run the data flow without any issues

Now when I ran the query select info->>'SubField1' from orders against the db. I was able to query the json field
Javier Nieto 26 Reputation points

2021-11-22T17:18:02.473+00:00

Hi @svijay-MSFT

I'm curious about the postgreSQL service you're using,
I've tested first with Azure Database for PostgreSQL Single Server (version 11) and got the conversion error and now I'm testing with Flexible server (version 13) and the linked serviced has SSL "on" and linked service and dataset "test connection"'s are working ok and previewing data but not the DataFlow sink test connection and when I run the pipeline I got this error (no firewall at postgres)

BR
Javier
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-11-23T16:02:30.073+00:00

Hello @Javier Nieto ,

I was also using the Single Server.

I have not gotten chance to test it in the Flexible server but looking at the error - just a hunch -Have your configured the below setting in the connection security ?
Javier Nieto 26 Reputation points

2021-11-23T17:48:22.21+00:00

Hi @svijay-MSFT

Flexible server will not work yet

But I was able to make it work at Simple Server, I would say "allow access to AZ services" did the trick.
Thank you!!

BR

Answer accepted by question author

0 additional answers

Your answer

svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-11-10T13:45:42.837+00:00

Hello @Javier Nieto ,

Thanks for the question and using MS Q&A platform.

I did refer this document about the connector-postgresql and as per the error -looks like Complex Data type is currently supported REST,SNOWFLAKE,ORC,JSON,AVRO or Azure Cosmos DB. I'll also have check internally.

Meanwhile, just wanted to understand - whether you could push the data as a json formatted string to PostgreSQL (rather than complex type) and consume it at the PostgreSQL end? I did find community threads was achieving this requirement.
Javier Nieto 26 Reputation points

2021-11-10T15:35:43.867+00:00

Hello @svijay-MSFT ,

thanks for the suggestion but I'm planing to use one of the json keys as an index at postgreSQL, so json as string it's not a valid scenario

BR
Javier Nieto 26 Reputation points

2021-11-17T12:12:56.233+00:00

Hello @svijay-MSFT

thanks for the suggestion but still getting an error at writing the toString(Complex) to the postgreSQL json column, even the preview from derived column is looking good. I've tried also by escaping the string just in case but same result
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-11-22T10:14:56.05+00:00

I tried the similar at my end and was not able to encounter the issue. Just wanted confirm the configuration at your end.

I created a table in postgreSQL as below with two columns

Id and info (json column)

At the dataflow end :

This was the structure of the complex field.

I was able to run the data flow without any issues

Now when I ran the query select info->>'SubField1' from orders against the db. I was able to query the json field
Javier Nieto 26 Reputation points

2021-11-22T17:18:02.473+00:00

Hi @svijay-MSFT

I'm curious about the postgreSQL service you're using,
I've tested first with Azure Database for PostgreSQL Single Server (version 11) and got the conversion error and now I'm testing with Flexible server (version 13) and the linked serviced has SSL "on" and linked service and dataset "test connection"'s are working ok and previewing data but not the DataFlow sink test connection and when I run the pipeline I got this error (no firewall at postgres)

BR
Javier
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-11-23T16:02:30.073+00:00

Hello @Javier Nieto ,

I was also using the Single Server.

I have not gotten chance to test it in the Flexible server but looking at the error - just a hunch -Have your configured the below setting in the connection security ?
Javier Nieto 26 Reputation points

2021-11-23T17:48:22.21+00:00

Hi @svijay-MSFT

Flexible server will not work yet

But I was able to make it work at Simple Server, I would say "allow access to AZ services" did the trick.
Thank you!!

BR

Answer 1

Hello @Javier Nieto ,

I have further checked with my engineering team on your request and got the response which I have explained below .

The complex datatype in Azure Data flow is of type - Object/Array?Map. PostgreSQL supports array - but not Object/Map. It can’t be supported directly.

Having said that PostgreSQL, supports JSON columns.

To that end, you could convert/transform this complex datatype(derived column) to a JSON String at the ADF end and write it as a JSON column PostgreSQL.

**Note : ** This is slightly different from what I suggested earlier. In this scenario, you are writing the data as a string directly to JSON Column at the PostgreSQL.

You could converted the complex datatype to a json a string using a toString function.

Hope this will help. Please let us know if any further queries.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Share via

Use json column in Database for postgreSQL as sink

0 additional answers

Your answer