Information Virtualization from Azure Blob Storage in Azure SQL Database

Drawback Assertion

In contrast to SQL server 2022 and Azure Synapse devoted pool, which helps Polybase performance to virtualize the exterior knowledge inside the corresponding database, one has to bodily load the info from the Azure Blob storage file inside the Azure SQL database.

So, is it potential to learn the Azure blob storage file instantly inside the Azure SQL database with out the necessity to bodily load the info?

Stipulations

1. Azure Storage Account
2. Azure SQL Database

Answer

Be aware. We’d be leveraging Azure SQL Database Exterior REST Endpoints Integration.

Log in to the Azure SQL database, the place we have to learn the Azure blob storage file and execute the queries within the sequence offered under.

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 learn the contents of the file in Azure Blob storage by way of GET Blob REST Endpoint.

declare @response nvarchar(max);
declare @url nvarchar(max) = 'https://<<StorageAccountName>>.blob.core.home windows.internet/<<ContainerName>>/<<FileName>>'
exec sp_invoke_external_rest_endpoint
@url = @url,
@headers="{"Settle for":"utility/xml"}",
@credential = [<<CredName>>],
@technique = 'GET',
@response = @response output
choose solid(@response as xml);

Consequence

Output