How to make a file format in SQL Server

Question

I'm trying to experiment with external files in SQL Server 2017, and am stumped at step one.

The data is pipe delimited, and I'm trying to follow the syntax in the documentation, which requires a FILE_FORMAT.

Here is the syntax per Microsoft:

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 ] ]  
)  
[;]  

That needs a file_format.

Here is the syntax for that, per another MS page:

CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
FORMAT_TYPE = DELIMITEDTEXT  
[ , FORMAT_OPTIONS ( <format_options> [ ,...n  ] ) ]  
[ , DATA_COMPRESSION = {  
       'org.apache.hadoop.io.compress.GzipCodec'  
     | 'org.apache.hadoop.io.compress.DefaultCodec'  
    }  
 ]);  

So here is what I wrote to create a pipe delimited file:

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

Here is the error message:

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

Answer 1

Good day,

CREATE EXTERNAL FILE FORMAT is based on PolyBase, which mean that you will need to install PolyBase and enable it. Until you do so, you will get this error.

In the following procedure you can see that part of the related elements which ca be used regardless PolyBase, while others need more steps... let go over this example:

create database MyLocalDB;
GO

use MyLocalDB
GO

-- 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

let's create external data source in SSMS:

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE, 
    LOCATION = 'https://EnterYourBlobNameHere.blob.core.windows.net/invoices', 
    CREDENTIAL= MyAzureBlobStorageCredential
);
GO

You will probably get an error: "Incorrect syntax near BLOB_STORAGE" This is an issue with SSMS parser and not with the query. You can execute the same query from SOS instead of SSMS. For some reasons when we use Azure SQL Database this executed well in SSMS.

SELECT * FROM sys.external_data_sources
GO

Now we are coming to your issue

CREATE EXTERNAL FILE FORMAT myfileformat  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,   
    FORMAT_OPTIONS (FIELD_TERMINATOR ='|')  
);
-- ERROR: Incorrect syntax near 'EXTERNAL'.

More work is needed...

1. Install "java runtime environment" (need version 7 and above) https://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html

2. Run SQL Server installation and add the feature: "PolyBase Query Service for External Data"

3. Enable PolyBase source configuration using sp_configure according to the source you use

This last point would, e.g., imply that for hadoop connectivity you need:

USE master;  
GO  
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
GO
EXEC sp_configure 'hadoop connectivity', 1;
RECONFIGURE;
GO

More information on this point you can get in the official doc: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/polybase-connectivity-configuration-transact-sql

Confirm that the PolyBase is installed:

SELECT SERVERPROPERTY ('IsPolybaseInstalled') AS IsPolybaseInstalled;
GO

Try to execute your query

CREATE EXTERNAL FILE FORMAT myfileformat  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,   
    FORMAT_OPTIONS (FIELD_TERMINATOR ='|')  
);
-- ERROR: TCP Provider: No connection could be made because the target machine actively refused it.

If you get the above error then: (1) enable TCP using the SQL Server Configuration Manager. (2) Make sure the PolyBase services are running

CREATE EXTERNAL FILE FORMAT myfileformat  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,   
    FORMAT_OPTIONS (FIELD_TERMINATOR ='|')  
);
-- OK

That's it :-)

You can now work with external file format and create external tables

I hope that this was useful :-)