question

SatwikKashyap-4302 avatar image
1 Vote"
SatwikKashyap-4302 asked AnnuKumari-MSFT commented

External Table in Dedicated Pool using Parquet

Scenario

  1. Copy Data Pipeline to get data from SAP to Azure Data Lake as parquet files (Files are partitioned)


  2. Reading that file as an external table using Synapse, I am getting 'Failed to detect Schema'



187988-image.png




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




azure-data-factoryazure-synapse-analyticsazure-data-lake-storage
image.png (27.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

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

Hi @SatwikKashyap-4302 ,
If the suggested response helped you, please click Accept Answer and kindly upvote the same.

0 Votes 0 ·
PratikSomaiya avatar image
2 Votes"
PratikSomaiya answered PratikSomaiya commented

Hello @SatwikKashyap-4302

Can you try creating external table from SSMS using the following link: https://docs.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

· 2
5 |1600 characters needed characters left characters exceeded

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

@pratiksomaiya 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

0 Votes 0 ·
PratikSomaiya avatar image PratikSomaiya SatwikKashyap-4302 ·

@SatwikKashyap-4302 : Can you share the screenshot from where you are trying to create the external table

0 Votes 0 ·
SatwikKashyap-4302 avatar image
0 Votes"
SatwikKashyap-4302 answered AnnuKumari-MSFT commented

@PratikSomaiya

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.

189754-image.png



image.png (29.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

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

Hi @SatwikKashyap-4302 ,
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.

192403-image.png
Please share the updates on this issue.


0 Votes 0 ·
image.png (70.7 KiB)

Hi @SatwikKashyap-4302 ,
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

0 Votes 0 ·