Azure SQL Server External Table

Category: azure sql data warehouse

Question

M Dadhich on Wed, 10 Oct 2018 16:17:58


Hello ,

I have couple of question regarding creating External table in Azure SQL Server database to access blob file.

1) Can we access CSV file in Azure blob from SQL Server External table through Polybase

2) If yes then can we use below query to create External File format

Create EXTERNAL FILE FORMAT TextfileFormat WITH (FORMAT_TYPE = DelimitedText,

FORMAT_OPTIONS (FIELD_TERMINATOR = N',', USE_TYPE_DEFAULT = True))

3) Do we have to enable Polybase feature in SQL Server instance to create External table in SQL Server database 2017/2016

4) Can External table be created on Azure SQL Server database or only in Azure SQL Server Data warehouse

5) I am using Azure SQL Server Database where Polybase is not enabled/Installed but I am able to execute below query successfully 

CREATE EXTERNAL DATA SOURCE AzureBlob  
WITH (  
TYPE = BLOB_STORAGE,  
LOCATION = 'wasbs://abc@vbh.blob.core.windows.net', 
CREDENTIAL = AzureStorageCredential

); 

However I get error when I use Type HADOOP instead of BLOB_STORAGE, Not sure why Can you please let me know.

6) I am getting below error while creating File Format

Incorrect syntax near 'EXTERNAL'.

Query is : 

Create EXTERNAL FILE FORMAT TextfileFormat WITH (FORMAT_TYPE = DelimitedText,
FORMAT_OPTIONS (FIELD_TERMINATOR = N',', USE_TYPE_DEFAULT = True))

How to fix it ?

Can you please help me what to do before creating External table and how to fix above Issues.

Many Thanks in Advance !

Regards,

Mahesh

Replies

Vitor Pombeiro on Wed, 10 Oct 2018 17:41:53


Hi,

From the documentation https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-2017
An external table created for PolyBase cannot be used for Elastic Database queries. Similarly, an external table created for Elastic Database queries cannot be used for PolyBase, etc.
PolyBase is supported only on SQL Server 2016 (or higher), Azure SQL Data Warehouse, and Parallel Data Warehouse. Elastic Database queries are supported only on Azure SQL Database v12 or later.

Regarding your questions:
1) No, Azure SQL Database don't support Polybase. You can vote to add support here:
https://feedback.azure.com/forums/217321-sql-database/suggestions/17376250-polybase-support-for-azure-sql-database
2) Create External File Format is not supported on Azure SQL Database as you can confirm in the documentation, https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-2017

3) In SQL Server the instance has to have this feature installed.

4) On both, but on Azure SQL Database it's only for Elastic Database queries use meaning cross Azure SQL Databases queries. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview#vertical-partitioning---cross-database-queries

5) Azure SQL Database don't support HADOOP has it doesn't support Polybase.

6) As in answer 2) Create External File Format is not supported in Azure SQL Database

If the main goal is to load data from Azure Storage into Azure SQL Database you can try Bulk Insert or OpenRowSet.
More information on this here: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/02/23/loading-files-from-azure-blob-storage-into-azure-sql-database/
https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017
https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/03/24/lesson-learned-34-does-azure-sql-database-support-openjson/

M Dadhich on Thu, 11 Oct 2018 09:18:43


Thanks Vitor for your quick help 

It was so helpful. But I may come up with more doubt while doing the development :)

Thanks again !

Regards,

Mahesh