{"id":47617,"date":"2022-10-05T08:00:00","date_gmt":"2022-10-05T15:00:00","guid":{"rendered":""},"modified":"2024-04-19T10:23:40","modified_gmt":"2024-04-19T17:23:40","slug":"data-virtualization-with-polybase-for-sql-server-2022","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2022\/10\/05\/data-virtualization-with-polybase-for-sql-server-2022\/","title":{"rendered":"Data Virtualization with PolyBase for SQL Server 2022"},"content":{"rendered":"\n

Part of the SQL Server 2022 blog series<\/a><\/em>.<\/p>\n\n\n\n

Microsoft SQL Server 2022<\/a> introduces the newest version of PolyBase, and with it the capability to query data where it lives, virtualize data, and use REST APIs. REST APIs enable SQL Server to be both more flexible and lightweight while expanding its range of supported connectors and file formats.<\/p>\n\n\n\n

SQL Server 2022 now supports CSV, Parquet, and Deltafiles stored on Azure Storage Account v2, Azure Data Lake Storage Gen2, or any simple storage service (S3)\u2013compliant object storage\u2014the last as an on-premises offering or in the cloud. Finally, SQL Server 2022 can now use Create External Table as Select<\/code> (CETAS), together with commands like OPENROWSET<\/code>, Create External Table<\/code> (CET), and all the new T-SQL enhancements. SQL Server 2022 is a powerful data hub.<\/p>\n\n\n\n

How does it work?<\/h2>\n\n\n\n

Data virtualization for SQL Server 2022 is both more flexible and easier to use now that its capabilities are inside the engine itself. The figure below gives a better understanding of the architecture:<\/p>\n\n\n

\"Examples
This figure provides a non-exhaustive list of S3-compliant object storage providers. SQL Server 2022 is compatible with any object storage provider compatible with S3-REST APIs.<\/em><\/figcaption><\/figure>\n\n\n\n

OPENROWSET<\/code>: Lightweight command that allows SQL engine to access data outside SQL Server, either a file or another database. Recommended for loading data or data exploration.<\/p>\n\n\n\n

CREATE EXTERNAL TABLE<\/code> (CET): Creates a table where the data stays in its original location outside of SQL Server, and when selected, the SQL engine will provide the requested data to the user. External table benefits from reusability and can leverage the use of statistics for better performance.<\/p>\n\n\n\n

CREATE EXTERNAL TABLE as SELECT<\/code> (CETAS): <\/strong>It performs a combination of operations in a single command. First, it allows SQL Server to transform and convert a given data stored inside or outside the database. Second, it then exports the data to a different location, either a network location or Azure. Finally, it creates an external table targeting the newly exported data.<\/p>\n\n\n\n

These operations are secured by a combination of database master key and external credentials for simplified management.<\/p>\n\n\n\n

If the data is stored on Azure Storage Account v2 (abs), Azure Data Lake Gen2 (ADLs), or an S3-compliant Object Storage, SQL Server 2022 will use the REST API implementation. If not, SQL Server 2022 will use PolyBase services\u2014PolyBase services installation is required for both cases.<\/p>\n\n\n\n

For a complete list of data sources, please refer to our CREATE EXTERNAL DATA SOURCE documentation<\/a>.<\/p>\n\n\n\n

External file support has also increased, and SQL Server 2022 now supports CSV, Parquet, and Delta type. Please find a complete list of supported external file formats in our CREATE EXTERNAL FILE FORMAT (TRANSACT-SQL) documentation<\/a>.<\/p>\n\n\n\n

Benefits<\/h2>\n\n\n\n

Major benefits of Data Virtualization with PolyBase on SQL Server 2022:<\/p>\n\n\n\n