External Table in Dedicated Pool using Parquet

Question

External Table in Dedicated Pool using Parquet

Satwik Kashyap 11

Scenario

Copy Data Pipeline to get data from SAP to Azure Data Lake as parquet files (Files are partitioned)
Reading that file as an external table using Synapse, I am getting 'Failed to detect Schema'

Error details
New external table
Previewing the file data failed. Details: Error: Conversion overflows.
If the issue persists, contact support and provide the following id : 39afb7bf-6726-4bbd-8a49-303a07cb5e40

AnnuKumari-MSFT 28,076 Reputation points Microsoft Employee

2022-04-20T17:41:00.617+00:00

Hi @Satwik Kashyap ,
If the suggested response helped you, please click Accept Answer and kindly upvote the same.

AnnuKumari-MSFT 28,076 Reputation points Microsoft Employee

2022-04-20T17:41:00.617+00:00

Hi @Satwik Kashyap ,
If the suggested response helped you, please click Accept Answer and kindly upvote the same.

Answer 1

Pratik Somaiya 4,126

Hello @Satwik Kashyap

Can you try creating external table from SSMS using the following link: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop

You will have to create following:

1) Database Scoped Credential
2) External Data Source
3) External File Format
4) External Table Query

In your case, I think there is a data type issue between data present in file and column data type in external table

Satwik Kashyap 11 Reputation points

2022-03-30T20:55:48.757+00:00

@Pratik Somaiya Thank you for looking into my question. yes, I am able to create the external tables through SSMS/Synapse.

But was wondering why is the default option in UI where we can go in the external storage - data lake in synapse and right-click on the dataset to create an external table not working.

I wanted to understand where is the schema mismatch or if you can explain what is a conversion overflow as shown in the error message? This is issue only when I am using partitioned files
Pratik Somaiya 4,126 Reputation points

2022-03-31T04:09:33.223+00:00

@Satwik Kashyap : Can you share the screenshot from where you are trying to create the external table

Answer 2

Satwik Kashyap 11

@Pratik Somaiya

The folder has parquet partitioned files. I am using the UI option where I am right-clicking on the folder and the below options appear to create an external table. When I click on it then it failes with the above error.

AnnuKumari-MSFT 28,076 Reputation points Microsoft Employee

2022-04-12T15:19:12.94+00:00

Hi @Satwik Kashyap ,
Just checking if the issue still persists. I tried to reproduce your scenario. I copied a table data into partitioned parquet files and created external table on top of one of the partitioned parquet files using sql script generated in the UI. It worked fine for me.

Please share the updates on this issue.
AnnuKumari-MSFT 28,076 Reputation points Microsoft Employee

2022-04-18T07:04:09.293+00:00

Hi @Satwik Kashyap ,
Just checking to see if you got a chance to visit the previous response. In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help

External Table in Dedicated Pool using Parquet

2 answers

Activity