Read Azure Data Lake Gen2 files from Azure SQL Database

PowerBI82 21 Reputation points
2021-08-12T21:32:03.61+00:00

Hi,

Is it possible to read a file that is on Azure Data Lake Gen 2 Storage account from an T-SQL query on Azure SQL Database?

Thanks

Azure SQL Database
Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,483 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Alberto Morillo 34,056 Reputation points MVP
    2021-08-12T23:08:50.473+00:00

    I think all you need can be found on this article.

    Alternatively you can use OPENROWSET.

     CREATE EXTERNAL DATA SOURCE pocBlobStorage
    WITH ( TYPE = BLOB_STORAGE, 
    LOCATION = 'https://eventhubstore.blob.core.windows.net/datasets', 
    CREDENTIAL= sqlblob);
    
    -- Query remote file 
    SELECT *
    FROM OPENROWSET(BULK 'currency.csv',
    DATA_SOURCE = 'pocBlobStorage',
    FORMATFILE='currency.fmt',
    FIRSTROW=2,
    FORMATFILE_DATA_SOURCE = 'pocBlobStorage') as D
    Inner Join [dbo].[State] C
    On C.[id] = D.CurrencyKey
    

    This OPENROWSET example works for classic storage accounts for sure but on Azure Data Lake Storage Gen2 accounts there was this reason that prevented from working with Gen2. I last verified this back on 2019.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.