Cannot create EXTERNAL TABLE on Azure SQL Databse

Question

Can you not create an External table on an Azure SQL Database with a format file? I'm trying to create an external table to a table I dumped into blob storage.

From this page: https://msdn.microsoft.com/en-us/library/dn935021.aspx

-- Create a new external table  
CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name   
    ( <column_definition> [ ,...n ] )  
    WITH (   
        LOCATION = 'folder_or_filepath',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name  
        [ , <reject_options> [ ,...n ] ]  
    )  
[;]

Is the documentation incorrect or am I missing something? I can't seem to create a FORMAT FILE and keep receiving

"Incorrect syntax near 'EXTERNAL'." error.

CREATE EXTERNAL FILE FORMAT [DelimitedText] 
WITH (
    FORMAT_TYPE = DELIMITEDTEXT, 
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = N'~¶~', 
        USE_TYPE_DEFAULT = False
    ), 
    DATA_COMPRESSION = N'org.apache.hadoop.io.compress.GzipCodec')
GO

Did you try it on an Azure SQL Data Warehouse instead as my answer suggests? Please consider marking it the answer if it solves your problem. — yoape

yoape yoape · Accepted Answer · 2017-01-25T16:22:47

The problem is (probably) that you are trying to use PolyBase on an Azure SQL Database, but PolyBase is only supported on on SQL Service 2016 on-premise. It is, however, supported on Azure SQL Datawarehouse: PolyBase Versioned Feature Summary

If you instead of an Azure SQL Database create an Azure SQL Datawarehouse you should have the PolyBase features available, including creating an external file format.

Running this:

CREATE EXTERNAL FILE FORMAT TextFormat 
WITH (
    FORMAT_TYPE = DELIMITEDTEXT, 
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = N'~¶~', 
        USE_TYPE_DEFAULT = False
    ), 
    DATA_COMPRESSION = N'org.apache.hadoop.io.compress.GzipCodec')
GO

On an Azure SQL Database will give you an error like:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'EXTERNAL'.

Running the same thing on Azure SQL Data warehouse will work

Command(s) completed successfully.

You will not be able to work with Hadoop databases using Azure SQL data warehouse, but working with Azure blob Storage is supported.

Cannot create EXTERNAL TABLE on Azure SQL Databse

1 Answers