Create External Table on Azure SQL Database for Azure Blob Storage

Question

Is it possible to create external tables for a given Azure Blob Storage? Do I need Synapse for that as indicated here: Creating External Table in Azure SQL DW from Azure BLOB Storage ?

Answer 1

Create external tables on Azure SQL(also called elastic queries) is only supported to work between Azure SQL databases.

If you are looking to import data from Azure Storage Account you can use OPENROWSET OR BULK INSERT as shown below:

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (  TYPE = BLOB_STORAGE,
        LOCATION = 'https://sqlchoice.blob.core.windows.net/sqlchoice/samples/load-from-azure-blob-storage',
--      CREDENTIAL= MyAzureBlobStorageCredential    --> CREDENTIAL is not required if a blob storage is public!
);

DROP TABLE IF EXISTS Product;
GO
--Create a permanent table.  A temp table currently is not supported for BULK INSERT,
--although it will will work with OPENROWSET
CREATE TABLE dbo.Product(
    Name nvarchar(50) NOT NULL,
    Color nvarchar(15) NULL,
    Price money NOT NULL,
    Size nvarchar(5) NULL,
    Quantity int NULL,
    Data nvarchar(4000) NULL,
    Tags nvarchar(4000) NULL
    --,INDEX cci CLUSTERED COLUMNSTORE
)
GO

BULK INSERT Product
FROM 'product.csv'
WITH (  DATA_SOURCE = 'MyAzureBlobStorage',
        FORMAT='CSV', CODEPAGE = 65001, --UTF-8 encoding
        FIRSTROW=2,
                ROWTERMINATOR = '0x0a',
        TABLOCK);

-- INSERT file exported using bcp.exe into Product table
BULK INSERT Product
FROM 'product.bcp'
WITH (  DATA_SOURCE = 'MyAzureBlobStorage',
        FORMATFILE='product.fmt',
        FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage',
        TABLOCK);

-- Read rows from product.dat file using format file and insert it into Product table
INSERT INTO Product WITH (TABLOCK) (Name, Color, Price, Size, Quantity, Data, Tags)
SELECT Name, Color, Price, Size, Quantity, Data, Tags
FROM OPENROWSET(BULK 'product.bcp',
                DATA_SOURCE = 'MyAzureBlobStorage',
                FORMATFILE='product.fmt',
                FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage') as products;

-- Query remote file
SELECT Color, count(*)
FROM OPENROWSET(BULK 'product.bcp',
                DATA_SOURCE = 'MyAzureBlobStorage',
                FORMATFILE='data/product.fmt',
                FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage') as data
GROUP BY Color;