Invalid column type [`STRUCT`] specified for column

1

Hi,
i would like to import dataset from PostgreSQL into Dremio that contains JSON fields. I thought that the best approach would be to import JSON fields into STRUCT data type in Dremio, but I can’t even create a table with STRUCT data type. Can you please help me with advice what is the best approach to this.

image

Thanks
Jaro

2

@jaroslav_marko You should be able to add the Postgres source and query the STRUCT field. Are you trying to create a table with struct column and then insert from Postgres table? Instead add the Postgres source and do a CTAS from Postgres which will automatically be stored in Iceberg

Also the STRUCT you have used needs columns and datatypes, below is an example

CREATE table localhdfs.ctas.test_struct (c1 INT, c2 VARCHAR, c3 TIMESTAMP, c4 STRUCT(a1:INT, b1:VARCHAR))
3

Hi @balaji.ramaswamy we have oftentimes JSON fields with variable structure so I won’t be able to define it upfront.
I may connect PostgreSQL directly for smaller table, but bigger tables we process via Parquet exports, so i’m importing it from Parquet directories.
What is best approach to process and use JSON fields?
On top it can be quite complex structures of several MB in size.
Thank you for your advice.
Jaro

4

@jaroslav_marko Have you considered moving JSON to Iceberg using Dremio COPY INTO?

5

hi @balaji.ramaswamy, yes copy into is certainly an option, but the underlaying table need to be created upfront. so what field type am I supposed to use for JSON fields? Varchar?

Thanks for letting me know.
Jaro

6

@jaroslav_marko Can you please try

  • Create table <iceberg_table_name> as select * from <json_table>
  • New rows, you can use insert