So, I thought I knew external tables well in azure but I was reading about external tables with Sql Server: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-ver16&tabs=dedicated and it seems it uses polybase to actually transfer the data. I am wondering if the whole concept of external tables is around using Polybase to actually transfer the data? So basically querying from an external table with an external datasource and scoped credentials use polybase on the backend with sql server, synapse serverless and dedicated sql pool?

Hello Karl Gardner , Welcome to the Microsoft Q&A forum. The concept of external tables in SQL Server is closely tied to PolyBase, which is a technology that allows you to query non-relational data stored in Hadoop or Azure Blob Storage using T-SQL. The external table defines the structure of the data and the location in the external data source. When you query an external table, SQL Server uses PolyBase to execute the query against the external data source. PolyBase does not transfer the data into SQL Server; instead, it processes the query and returns only the results. This means that the data remains in its original location, and only the data is retrieved by the query. This approach allows for a form of data virtualization, where you can integrate and query data across different data stores. In the context of Azure Synapse Analytics, Serverless SQL pools in Azure Synapse Analytics support a native data virtualization technology, while dedicated SQL pools support both native and PolyBase data virtualization. When you query the external table, PolyBase is used to retrieve the data from the external data source and return it to the database. You can use standard SQL queries to query the external table, and the results are returned as if they were stored in the database. also you can use scoped credentials to securely access the external data source. Scoped credentials are used to authenticate the connection to the external data source and are stored in the database. I hope this answers your question. Please let me know if you have any further questions

External Tables and Polybase in Synapse and Sql Server

Accepted answer

BhargavaGunnam-MSFT 27,976 Reputation points Microsoft Employee

2024-05-17T21:39:10.08+00:00

Hello Karl Gardner,

Welcome to the Microsoft Q&A forum.

The concept of external tables in SQL Server is closely tied to PolyBase, which is a technology that allows you to query non-relational data stored in Hadoop or Azure Blob Storage using T-SQL. The external table defines the structure of the data and the location in the external data source. When you query an external table, SQL Server uses PolyBase to execute the query against the external data source.

PolyBase does not transfer the data into SQL Server; instead, it processes the query and returns only the results. This means that the data remains in its original location, and only the data is retrieved by the query. This approach allows for a form of data virtualization, where you can integrate and query data across different data stores.

In the context of Azure Synapse Analytics, Serverless SQL pools in Azure Synapse Analytics support a native data virtualization technology, while dedicated SQL pools support both native and PolyBase data virtualization.

When you query the external table, PolyBase is used to retrieve the data from the external data source and return it to the database. You can use standard SQL queries to query the external table, and the results are returned as if they were stored in the database.

also you can use scoped credentials to securely access the external data source. Scoped credentials are used to authenticate the connection to the external data source and are stored in the database.

I hope this answers your question. Please let me know if you have any further questions
Please sign in to rate this answer.

1 person found this answer helpful.
Karl Gardner 65 Reputation points

2024-05-20T15:20:50.07+00:00

Hello BhargavaGunnam-MSFT ,

It seems like the documentation is contradicting this. In the following page https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=azure-sqldw-latest&tabs=dedicated#arguments-5

it says: "Serverless and dedicated SQL pools in Azure Synapse Analytics use different code bases for data virtualization. Serverless SQL pools support a native data virtualization technology. Dedicated SQL pools support both native and PolyBase data virtualization. PolyBase data virtualization is used when the EXTERNAL DATA SOURCE is created with TYPE=HADOOP."

In the following page:

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=azure-sqldw-latest&tabs=dedicated#location--folder_or_filepath-1

it also says:

If you specify LOCATION to be a folder, a PolyBase query that selects from the external table will retrieve files from the folder and all of its subfolders. Just like Hadoop, PolyBase doesn't return hidden folders. It also doesn't return files for which the file name begins with an underline (_) or a period (.).

In the following image example, if LOCATION='/webdata/', a PolyBase query will return rows from mydata.txt and mydata2.txt. It won't return mydata3.txt because it's in a subfolder of a hidden folder. And it won't return _hidden.txt because it's a hidden file.

So it seems like Polybase are the Hadoop external tables and I guess native external tables use something else?

Thanks,

Karl Gardner

BhargavaGunnam-MSFT 27,976 Reputation points Microsoft Employee

2024-05-20T20:57:46.7066667+00:00

Hello Karl Gardner,

My apologies, and thank you for correcting this.

Native External Tables:

When creating a data source without using type = hadoop, behind the scenes, it uses native external tables. This feature is currently in preview.

The technology behind native external tables is not publicly disclosed in details, but this is built to provide a more efficient and performant querying experience.

You can find side by side comprisions between these two from the below document.

Reference document: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=native#folder-partition-elimination

I have updated my answer with these details. Sorry again for providing the wrong information earlier.

I hope this helps. Please let me know if you have any further questions
Sign in to comment

Share via

External Tables and Polybase in Synapse and Sql Server

0 additional answers