適用対象: SQL Server (サポートされているすべてのバージョン)
PolyBase VS. linked servers
The following table highlights the differences between PolyBase and linked server features:
|Database scoped object||Instance scoped object|
|Uses ODBC drivers||Uses OLEDB providers|
|Supports read-only operations for all data sources and insert operation for HADOOP & data pool data source only||Supports both read and write operations|
|Queries to remote data source from a single connection can be scaled-out||Queries to remote data source from a single connection cannot be scaled-out|
|Predicates pushdown is supported||Predicates pushdown is supported|
|No separate configuration needed for availability group||Separate configuration needed for each instance in availability group|
|Basic authentication only||Basic & integrated authentication|
|Suitable for analytic queries processing large number of rows||Suitable for OLTP queries returning single or few rows|
|Queries using external table cannot participate in distributed transaction||Distributed queries can participate in distributed transaction|
What's new in PolyBase 2019?
PolyBase in SQL Server 2019 (15.x) can now read data from a larger variety of data sources. The data from these external data sources can be store as external tables on your SQL Server. PolyBase also supports pushdown computation to these external data sources, excluding ODBC generic types.
Compatible Data Sources
- SQL Server
- Compatible ODBC generic types
PolyBase can allow connection to external data sources using third party ODBC drivers. These drivers are not provided along with PolyBase and may not work as intended. For more information, visit our guide for PolyBase ODBC generic configuration.
PolyBase in Big Data Clusters vs. PolyBase in stand-alone instances
The following table highlights the PolyBase features available in SQL Server 2019 (15.x) stand-alone install and SQL Server 2019 (15.x) big data cluster:
|Feature||Big Data Cluster||Stand alone instance|
|Create external data source for SQL Server, Oracle, Teradata, and Mongo DB||X||X|
|Create external data source using a compatible third-party ODBC Driver||X|
|Create external data source for HADOOP data source||X||X|
|Create external data source for Azure Blob Storage||X||X|
|Create external table on a SQL Server data pool||X|
|Create external table on a SQL Server storage pool||X|
|Scale-out query execution||X||X (Windows only)|
For more information on connections using the ODBC generic connector visit our How to guide for configuring ODBC generic types.
How can I view the remote query execution plan of PolyBase pushdown computation?
This is possible via a couple of methods depending on the version of SQL Server. See PolyBase Troubleshooting - To view the PolyBase query plan.
How can I tell whether PolyBase pushdown computation is happening?
Which ports should I allow through my firewall for PolyBase?
No additional ports are needed to open for PolyBase itself, except in the case of a PolyBase scale-out group. Without a scale-out group, the PolyBase service alone should not require any firewall rule.
On Windows servers, PolyBase will leverage the ports used by SQL Server in the IPAll section of the TCP/IP settings in SQL Server Configuration Manager. For more information, see TCP/IP Properties. On Linux servers, PolyBase will leverage the network.tcpport setting. For more information, see TCP port.
For PolyBase scale-out groups over multiple Windows servers, see port information in the next question.
However, you must allow other ports for communication to the various external data source(s) used by PolyBase. Refer to ODBC configuration, Azure resource configuration, or specific data source documentation. For example:
|Microsoft SQL Server||Ports Used By SQL Server|
|Hortonworks HDP||Administering HDFS|
|Cloudera CDH||Ports Used by CDH Components|
What ports are used for PolyBase scale-out group?
By default, the PolyBase data movement service connects to the head node of a scale-out group over a range of ports from TCP 16450-16460, typically using 16450-16453, plus port 17001. These ports are only used when a PolyBase scale-out group is configured and should be allowed through server firewalls. SQL Server Setup should automatically create a local firewall rule allowing these ports upon feature installation. For more information, see Configure PolyBase scale-out groups on Windows.