Data Virtualization from Azure Blob Storage in Azure SQL Database

Problem Statement

Unlike SQL server 2022 and Azure Synapse dedicated pool, which supports Polybase functionality to virtualize the external data within the corresponding database, one has to physically load the data from the Azure Blob storage file within the Azure SQL database.

So, is it possible to read the Azure blob storage file directly within the Azure SQL database without the need to physically load the data?

Prerequisites

1. Azure Storage Account
2. Azure SQL Database

Solution

Note. We would be leveraging Azure SQL Database External REST Endpoints Integration.

Log in to the Azure SQL database, where we need to read the Azure blob storage file and execute the queries in the sequence provided below.

a)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<<>>'
b)
CREATE DATABASE SCOPED CREDENTIAL [<<Cred Name>>]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=<<SAS Token>>'

One can now read the contents of the file in Azure Blob storage via GET Blob REST Endpoint.

declare @response nvarchar(max);
declare @url nvarchar(max) = 'https://<<StorageAccountName>>.blob.core.windows.net/<<ContainerName>>/<<FileName>>'
exec sp_invoke_external_rest_endpoint
@url = @url,
@headers = '{"Accept":"application/xml"}',
@credential = [<<CredName>>],
@method = 'GET',
@response = @response output
select cast(@response as xml);

Result

Output