3

I am trying to setup a PolyBase (external) table with data in

  • Azure SQL Server database hosted on a Azure SQL Server (setup through the portal)
  • Azure Blob storage (CSV data)

SQL Server version:

Microsoft SQL Azure (RTM) - 12.0.2000.8   Nov  2 2018 21:17:06

The motive is to run some queries joining the two datasources.

Does Azure SQL Servers come with PolyBase setup? I have no idea how to enable "polybase query service for external data" from the azure console referred in these docs

when I try to run these config steps through the SQL Server Management Studio to enable polybase and setup connectivity:

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

I get an error

Could not find stored procedure 'sp_configure'

Also running this query

 SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled;

Returns - 0

However, I am able to run these queries and create a External Data Source

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  

CREATE DATABASE SCOPED CREDENTIAL AzureStorage
WITH IDENTITY = 'user', Secret = 'SecretKey';

CREATE EXTERNAL DATA SOURCE AzureStorage with (  
  TYPE = BLOB_STORAGE,
  LOCATION ='wasbs://blob@container.blob.core.windows.net',  
  CREDENTIAL = AzureStorage  
);

And when I try to create a new external file

CREATE EXTERNAL FILE FORMAT taxifileformat  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,   
    FORMAT_OPTIONS (FIELD_TERMINATOR =',')  
);

I get error

Incorrect syntax near 'EXTERNAL'

My questions are:

  1. Does Azure SQL Server have polybase enabled? If not, how do I enable them?

  2. What might be the issue with creating an external format which I plan to use to create the external table?

Thank you !

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
kpython
  • 363
  • 2
  • 8
  • 21
  • Nope, not an Azure SQL Database. I tried as well, ended up using an Azure SQL Data Warehouse. https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-2017 You can use polybase in SQL Server 2016 and later(windows only), Analytics Platform System (Formerly Parallel Data Warehouse) or Azure SQL Data Warehouse. – Tim Mylott Nov 20 '18 at 21:49

1 Answers1

4

Polybase is not supported on Azure SQL Database. It is one of the most voted features users want to see on Azure SQL Database in the future. If you want to vote for this feature please vote here.

sp_configure is not available on Azure SQL Database also. Use ALTER DATABASE SCOPED CCONFIGURATION instead to configure Azure SQL Database.

Azure SQL Data Warehouse seems to be a better option for you if you need Polybase, as mentioned on the comments of your question.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • You _can_ load from Azure Blob Storage using BULK INSERT or OPENROWSET. https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/02/23/loading-files-from-azure-blob-storage-into-azure-sql-database/ – David Browne - Microsoft Nov 22 '18 at 14:38
  • Yes, you can use bulk load data via BULK INSERT or OPENROWSET, but both of these are more limited in Azure SQL than the same feature offerings in Azure DW or Synapse Analtics (which are PolyBase enabled); they support file types DAT, BCP, CSV for example, but there is no support for Parquet files (which is my use case currently). – CajunCoding Nov 06 '20 at 18:57