SQL error when querying parquet table added to Lake database via GUI

Andreas Nord 1

I created a Lake database and added a table via the "+Table / From data lake" option in the GUI, using a parquet file residing in my data lake.
When trying to query the added table I get the following error:
Column 'col' of type 'REAL' is not compatible with external data type 'Parquet physical type: DOUBLE', please try with 'FLOAT'. File/External table name: 'testtable'.
However the column type was correctly inferred as float, at least that is what shows up in the GUI

Andreas Nord 1 Reputation point

2022-06-21T10:17:00.243+00:00

Failing query:

Model:

Working query
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-06-21T21:15:00.28+00:00

Hello @Andreas Nord ,

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

From the behavior, it seems like a bug, this issue happens if data type mapping doesn't occur properly as described in this doc: Type mapping for Parquet. To work around this, could you please try changing the the data type in the UI from "float" to "double" for all the columns for which this error is showing up, and then publish the database and see if that helps to resolve the issue.

In the meantime we will escalate this issue to respective product owners and will keep you posted as soon as we have an update from the team.

Let us know how it goes.

Thank you.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-06-23T00:57:24.553+00:00

Hello @Andreas Nord ,

Just checking in to see if the workaround provided was helpful to resolve the issue.

The product team has opened a work item to work on this issue, but we do not have an ETA at this point. Until then, the workaround provided should help unblock. And, if you have any further query do let us know.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-06-30T16:55:01.783+00:00

Hello @Andreas Nord ,

We still have not heard back from you. Just wanted to if your issue was able to resolve with the workaround provided. And, if you have any further query do let us know.

Thank you
Nikolai Kobyzev 1 Reputation point

2022-07-01T14:41:54.407+00:00

Hi @KranthiPakala-MSFT ,

I have the same issue with datatype as @Andreas Nord and the suggested workaround didn't help me solve the problem.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-07-01T21:29:05.62+00:00

Hello @Andreas Nord ,

Thanks for reaching out. As the workaround doesn't work, if you have a support plan, could you please file a support ticket for deeper analysis and do share the Support ticket number with us for tracking. And if you don't have a support plan, please let me know here.

Thanks

1 answer

Grace Blainville 0 Reputation points

2023-02-15T09:38:31.66+00:00

Hi @Nikolai Kobyzev @Andreas Nord ,

I've had the same problem sometimes when I wanted to read a table in synapse. It also happened to me with other data types: Integer, decimal, Boolean and date.

I had the same problem again when loading the data from my table into power BI.

The problem is solved when I add a condition on its columns in my synapse dataflow (I use a derived column with my column as reference).

I replace the null values (perhaps null values are explicitly translated as "null" instead of " ") , I remove the spaces in the numerical values, replace the "," by "." for the decimal values and indeed there are no more problems, it works correctly now.

Perhaps make sure that the columns concerned also have these conditions.

I hope this answer will help someone,
Please sign in to rate this answer.

0 comments No comments
Sign in to comment