Sink PostgreSQL in DataFlow in Azure Data Factory // Format error

Question

Sink PostgreSQL in DataFlow in Azure Data Factory // Format error

Andres Esteban 156

Hello

From an AVRO data source I want to make a DataFlow to keep some of the AVRO data in a PostgreSQL database.

I have already managed to undo the JSON from the AVRO and put each element in a column.

However, this data seems to be in array format because when I try to connect to the sink (PostgreSQL with its table already created and with all the columns matching the JSON elements) I get the following error.

Does anyone know how to solve it, I understand that the array would have to be removed, if so, how could I do it?

Thank you in advance

Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2022-04-08T21:53:21.143+00:00

Hi @Andres Esteban ,

Thanks for using Microsoft Q&A!!
As per your description, it looks like you are having trouble loading data in AVRO format to a PostgreSQL database using Dataflow.

Could you please help sharing some sample AVRO data which you are trying to load into a PostgreSQL database? What all data transformations you are using to map this AVRO and what is destination schema of the table?

Thanks
Saurabh
MarkKromer-MSFT 5,226 Reputation points Microsoft Employee Moderator

2022-04-11T06:13:43.17+00:00

The [] indicators on each column header are telling you that those array data types, not scalar. If you need to convert them to scalar for your Postgres table, then you'll need to unroll them using Flatten first or convert them to another scalar data type.
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2022-04-22T21:37:31.73+00:00

Hi @Andres Esteban ,

We haven't heard back from you. Just wanted to check if you are you still facing the issue? In case If you already found a solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue.

Thanks
Saurabh
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2022-04-27T00:38:53.77+00:00

Hi @Andres Esteban ,

Please let me know if you need any help on this issue.

Thanks
Saurabh

1 answer

Your answer

Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2022-04-08T21:53:21.143+00:00

Hi @Andres Esteban ,

Thanks for using Microsoft Q&A!!
As per your description, it looks like you are having trouble loading data in AVRO format to a PostgreSQL database using Dataflow.

Could you please help sharing some sample AVRO data which you are trying to load into a PostgreSQL database? What all data transformations you are using to map this AVRO and what is destination schema of the table?

Thanks
Saurabh
MarkKromer-MSFT 5,226 Reputation points Microsoft Employee Moderator

2022-04-11T06:13:43.17+00:00

The [] indicators on each column header are telling you that those array data types, not scalar. If you need to convert them to scalar for your Postgres table, then you'll need to unroll them using Flatten first or convert them to another scalar data type.
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2022-04-22T21:37:31.73+00:00

Hi @Andres Esteban ,

We haven't heard back from you. Just wanted to check if you are you still facing the issue? In case If you already found a solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue.

Thanks
Saurabh
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2022-04-27T00:38:53.77+00:00

Hi @Andres Esteban ,

Please let me know if you need any help on this issue.

Thanks
Saurabh

Answer 1

Andres Esteban 156

First of all, I am sorry for the delay in my reply.

To solve the problem, I decoupled the data from the arrays using Flatten and it worked for me. Thank you very much for your help

Share via

Sink PostgreSQL in DataFlow in Azure Data Factory // Format error

1 answer

Your answer