none
Using External tables to query data in blob RRS feed

  • Question

  • Hello Experts,

    Please let me know whether Azure SQL Database allows creating external tables on Azure blob storage. I went through the external tables page in docs.microsoft.com but it does not explicitly talk about the feasibility of setting up a connection form Azure SQL DB to blob storage. My requirement is to be able to query a delimited text file lying in blob from azure sql db by defining an external table schema on it.

    Thanks in advance.

    Tuesday, October 23, 2018 10:41 AM

Answers

  • Hello,

    At this time Azure SQL Database allows to create external data sources and external tables only to other SQL Azure databases or Azure SQL Data Warehouse Databases.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com



    Tuesday, October 23, 2018 12:11 PM
  • Thanks Alberto, Ronen!

    Ronen, as creation of external data source is allowed on Blob Storage, could you please throw some light on what this can be used for? If I have data lying in Blob, is there some way I can make use of it in Azure SQL Database?

    Also, I'm digressing slightly from my original question. As external tables can only be created on other Azure SQL DBs and on Azure SQL DWs, I'm assuming external tables are not supported on Hadoop as well.

    Thanks in advance!

    Hi <?!?>,

    Why do you think that EXTERNAL TABLES is the only use for DATA SOURCE?

    You can use this data source for example to put your CSV files there and read them using OPENROWSET directly from your server. You can use for example JSON files and load them to the Azure SQL Database using simple BULK INSERT or OPENROWSET. For example something like this:

    SELECT * FROM OPENROWSET(
    	BULK  'ronen-ariely/text.txt',-- this is a file inside a folder inside my Data SOURCE
    	DATA_SOURCE = 'MyAzureBlobStorage',-- this is my external DATA SOURCE
    	SINGLE_CLOB) AS DataFile;

    In fact, this might fit your case.

    This is not an external table which you add/remove data but it is an option to read the file data directly in your Azure SQL Database :-)

    More interesting :-)
    Check this: We cannot create external file format, but we can execute the query bellow in order to get all the File Format which we cannot create

    -- this is supported in Azure Database
    SELECT * FROM sys.external_file_formats;

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Thursday, October 25, 2018 11:45 AM
    Moderator

All replies

  • Hello,

    At this time Azure SQL Database allows to create external data sources and external tables only to other SQL Azure databases or Azure SQL Data Warehouse Databases.


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com



    Tuesday, October 23, 2018 12:11 PM
  • Hi,

    I intended to write a full explanation and workaround. I started to write the explanation about EXTERNAL DATA and I had phone so I temporarily delete my answer since it was confusing and partially missing (I started with what is supported and not what is not). I forgot to come back to the answer, and now I see that Alberto already gave the short exact answer

    With that being said, I do want to add some insights which I think should be here

    Let's start from the start...

    As you can see in the documentation,  CREATE EXTERNAL TABLE is supported in the Azure Database, and also CREATE EXTERNAL DATA SOURCE.

    >> whether Azure SQL Database allows creating external tables on Azure blob storage.

    It does allows creating external tables, and it does allows crating external data source on the blob, but not external tables on the BLOB

    Basically you just need to follow the procedure in the doc if you want external data source

    -- connect to the master
    
    create database MyAzureDB;
    GO
    
    -- connet to the database: MyAzureDB 
    
    -- Creates a database master key.
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My!Simple@Pass#for$Lecture'  
    GO
    
    CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential 
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '<enter you blob key here>';
    GO
    
    -- just confirm that we don't have anything before
    SELECT * FROM sys.external_data_sources
    GO
    
    CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
    WITH (
        TYPE = BLOB_STORAGE, 
        LOCATION = 'https://EnterYourBlobStorageNameHere.blob.core.windows.net/invoices', 
        CREDENTIAL= MyAzureBlobStorageCredential
    );
    GO

    PolyBase is what enable us to access external data in Azure Blob Storage from local SQL Server. The problem is that in order to create EXTERNAL TABLE on AZURE BLOB you need that PolyBase will be supported which is not supported on Azure SQL Database.

    You can check the link bellow for details about what is supported in each version. 
    https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-versioned-feature-summary?view=sql-server-2017#feature-summary-for-product-releases

    * Moreover, CREATE EXTERNAL FILE FORMAT is not supported in the Azure Database, which mean that you cannot map your flat file to the table. You can read more information regarding Local SQL Server and FILE FORMAT issues here.

    What can we do in the meantime?

    Option 1: You can use Azure Data warehouse instead of Azure SQL Database

    Option 2: You can use On-Prem SQL Server on Azure Virtual Machine

    I hope this give some more value...

    * Don't forget top mark Alberto answer :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Wednesday, October 24, 2018 4:40 AM
    Moderator
  • Thanks Alberto, Ronen!

    Ronen, as creation of external data source is allowed on Blob Storage, could you please throw some light on what this can be used for? If I have data lying in Blob, is there some way I can make use of it in Azure SQL Database?

    Also, I'm digressing slightly from my original question. As external tables can only be created on other Azure SQL DBs and on Azure SQL DWs, I'm assuming external tables are not supported on Hadoop as well.

    Thanks in advance!


    Wednesday, October 24, 2018 9:08 AM
  • Thanks Alberto, Ronen!

    Ronen, as creation of external data source is allowed on Blob Storage, could you please throw some light on what this can be used for? If I have data lying in Blob, is there some way I can make use of it in Azure SQL Database?

    Also, I'm digressing slightly from my original question. As external tables can only be created on other Azure SQL DBs and on Azure SQL DWs, I'm assuming external tables are not supported on Hadoop as well.

    Thanks in advance!

    Hi <?!?>,

    Why do you think that EXTERNAL TABLES is the only use for DATA SOURCE?

    You can use this data source for example to put your CSV files there and read them using OPENROWSET directly from your server. You can use for example JSON files and load them to the Azure SQL Database using simple BULK INSERT or OPENROWSET. For example something like this:

    SELECT * FROM OPENROWSET(
    	BULK  'ronen-ariely/text.txt',-- this is a file inside a folder inside my Data SOURCE
    	DATA_SOURCE = 'MyAzureBlobStorage',-- this is my external DATA SOURCE
    	SINGLE_CLOB) AS DataFile;

    In fact, this might fit your case.

    This is not an external table which you add/remove data but it is an option to read the file data directly in your Azure SQL Database :-)

    More interesting :-)
    Check this: We cannot create external file format, but we can execute the query bellow in order to get all the File Format which we cannot create

    -- this is supported in Azure Database
    SELECT * FROM sys.external_file_formats;

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Thursday, October 25, 2018 11:45 AM
    Moderator
  • here you have new Wiki article which explain the use of BULK INSERT from Data Source:

    T-SQL: Bulk Insert Azure CSV Blob into Azure SQL Database


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, October 25, 2018 1:04 PM
    Moderator
  • Thanks Ronen! This is extremely helpful!
    Friday, October 26, 2018 5:15 AM
  • I am glad I could help a bit :-)

    Thank you for the nice words!
    There is no greater reward than "thanks" :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, October 26, 2018 8:01 AM
    Moderator
  • Hi, do you know if Azure SQL Database allows to create an external table using Blog Storage and/or ADLS gen2 now?

    Many thanks,
    JS

    Thursday, April 4, 2019 3:53 PM