PolyBase and External Tables in Synapse

PolyBase is a technology that enables Azure Synapse Analytics (formerly SQL Data Warehouse) to query data from external sources using standard T-SQL statements. It was originally developed by Microsoft for SQL Server 2016 and later integrated into Azure Synapse. The primary purpose is to allow data virtualization—querying data without moving or copying it into the dedicated SQL pool. This is critical for scenarios where data resides in Azure Blob Storage, Azure Data Lake Storage (Gen1 or Gen2), or Hadoop (via Hortonworks Data Platform or Cloudera). PolyBase avoids expensive data movement and enables hybrid architectures.

When you issue a T-SQL query that references an external table, the PolyBase engine in Synapse SQL pool does not pull the entire external dataset into the pool. Instead, it: 1. Parses the query and identifies which parts involve external tables. 2. Contacts the external data source using the connection information defined in the external data source object. 3. If possible, pushes down parts of the query (filtering, projection) to the external source (e.g., Hadoop or Azure Storage) to reduce data movement. 4. Retrieves only the necessary data from the external source, typically in a compressed format (e.g., ORC, Parquet, or delimited text). 5. Processes the data in the SQL pool and returns results.

PolyBase supports two modes for reading external data: - Direct read: For data in Azure Blob Storage or Azure Data Lake Storage, PolyBase reads the files directly. - Pushdown: For Hadoop or Azure HDInsight, PolyBase can push down filter and join operations to the MapReduce or Spark layer.

To use PolyBase, you must create several objects in the Synapse SQL pool:

- External Data Source: Defines the location and connection string for the external data. Supported types: - HADOOP for Hadoop or Azure HDInsight. - SHARD_MAP_MANAGER for Azure SQL Database sharding (not commonly tested). - RDBMS for remote SQL Server (not in Synapse, only in SQL Server 2019+). Example syntax:

CREATE EXTERNAL DATA SOURCE MyDataSource WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://container@storageaccount.blob.core.windows.net',
    CREDENTIAL = MyCredential
);

- External File Format: Defines the format of the external data (delimited text, Parquet, ORC, RCFile, etc.). Example:

CREATE EXTERNAL FILE FORMAT MyFileFormat WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

- External Table: Similar to a regular table but points to data in the external source. It includes the schema and references the external data source and file format. Example:

CREATE EXTERNAL TABLE dbo.ExternalSales (
    SaleID INT,
    SaleDate DATE,
    Amount DECIMAL(10,2)
)
WITH (
    LOCATION = '/sales/',
    DATA_SOURCE = MyDataSource,
    FILE_FORMAT = MyFileFormat
);

PolyBase supports reading from: - Azure Blob Storage: via wasbs:// or abfss:// (for ADLS Gen2). - Azure Data Lake Storage Gen1: via adl://. - Azure Data Lake Storage Gen2: via abfss://. - Hadoop (HDFS): via hdfs://. - Azure HDInsight: as a Hadoop cluster.

File formats:

PolyBase can push down filter predicates and projections to the external source if the source supports it (e.g., Hadoop with ORC/Parquet). For Azure Blob Storage, pushdown is not supported because Blob Storage is a flat file system; all data must be read and processed in the SQL pool. However, using columnar formats like Parquet reduces I/O because only relevant columns are read.

PolyBase uses database-scoped credentials to authenticate to external storage. These credentials can be based on:

Example credential creation:

CREATE DATABASE SCOPED CREDENTIAL MyCredential WITH
    IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = 'sv=2018-03-28&ss=b&...';

Once created, you can query external tables like regular tables:

SELECT * FROM dbo.ExternalSales WHERE SaleDate > '2023-01-01';

You can also join external tables with local tables:

SELECT l.Region, SUM(e.Amount) AS TotalSales
FROM dbo.LocalRegions l
JOIN dbo.ExternalSales e ON l.RegionID = e.RegionID
GROUP BY l.Region;

To check existing external objects:

SELECT * FROM sys.external_data_sources;
SELECT * FROM sys.external_file_formats;
SELECT * FROM sys.external_tables;

To view execution plan for a PolyBase query:

EXPLAIN WITH_RECOMMENDATIONS SELECT * FROM dbo.ExternalSales;

A retail company stores historical sales data in Azure Data Lake Storage Gen2 as Parquet files, partitioned by year and month. The data warehouse team uses Synapse dedicated SQL pool for daily reporting. Rather than copying terabytes of data into the pool, they create external tables pointing to the Parquet files. Analysts can then query sales data from 2020-2023 using standard T-SQL, joining with dimension tables in the pool. Performance is acceptable because Parquet is columnar and filters push down to file-level pruning. The team set up a managed identity for the Synapse workspace to authenticate to ADLS Gen2. One common issue: if the external files are not in a consistent schema, queries fail with 'File format error'. They enforce schema-on-read by ensuring all files in the location have the same columns.

A financial services firm has an on-premises Hadoop cluster with historical trade data. They are migrating to Azure and want to use Synapse for analytics. They set up an Azure HDInsight cluster as a staging environment and create external tables in Synapse pointing to HDFS via the HADOOP data source type. PolyBase pushes down filter operations to the HDInsight cluster, reducing data transfer. The migration team gradually moves data to ADLS Gen2 and updates the external data source URL. They discovered that PolyBase pushdown works only with ORC and Parquet files on HDInsight; CSV files do not support pushdown, causing slower queries. They also had to configure firewall rules to allow Synapse to communicate with HDInsight.

A logistics company ingests IoT device data into Azure Blob Storage as CSV files every 5 minutes. They use PolyBase external tables to query the latest data for dashboards. However, they found that PolyBase does not automatically detect new files; they must recreate or refresh metadata. They solved this by using a stored procedure that drops and recreates the external table daily. They also set up alerts for when PolyBase queries fail due to missing files or authentication issues. A misconfiguration they encountered: using a SAS token that expired, causing all external queries to fail until the credential was updated.