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

Question

question

AndresEsteban-7986 asked Apr 7, '22 AndresEsteban-7986 answered Apr 27, '22

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

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

azure-data-factory azure-database-postgresql

image.png (5.2 KiB)

image.png (3.1 KiB)

· 4

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MarkKromer-MSFT · Apr 11 at 06:13 AM

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.

1 ·

SaurabhSharma-msft · Apr 08 at 09:53 PM

Hi @andresesteban-7986,

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

0 ·

SaurabhSharma-msft SaurabhSharma-msft · Apr 22 at 09:37 PM

Hi @andresesteban-7986,

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

0 ·

SaurabhSharma-msft SaurabhSharma-msft · Apr 27 at 12:38 AM

Hi @andresesteban-7986,

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

Thanks
Saurabh

0 ·

Answer 1 · 2022-04-27T15:00:53.397Z

AndresEsteban-7986 answered Apr 27, '22

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question