0

I have a SQL Server Managed Instance in Azure and have a lot of parquet files on my Blob Storage. I tried to create an external table linked to these files, but I can't. After followed a tutorial on how-to create an external table, I had problems creating a file format on SQL. I used the sintax:

CREATE EXTERNAL FILE FORMAT MyParquetFormat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS(
    FIELD_TERMINATOR = '|',
    STRING_DELIMITER = '"',
    ENCODING = 'UTF8'
    )
);

When running this command received a msg:

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

I tried to find some Azure documentation about the connection between SQL Server Managed Instance and Blob Storage. I would like to know if this is possible.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Fernando Delago
  • 105
  • 1
  • 2
  • 8
  • According to this https://stackoverflow.com/questions/66123032/error-while-creating-external-file-format-in-azure-sql-database you should try this instead. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15&tabs=dedicated Then perhaps raise a document issue on the page since it doesn't see to be supported in managed instance. – Nick.Mc Feb 21 '23 at 12:34
  • You might also want to check your compatability level in case you upgrade from on prem and you are stuck in an old version – Nick.Mc Feb 21 '23 at 12:35
  • Are you sure you're using an Azure SQL Managed Instance and not, perhaps, an Azure SQL Database? – Thom A Feb 21 '23 at 12:35
  • 1
    as per other learned comments use `SELECT @@VERSION` to confirm exactly what you are running in – Nick.Mc Feb 21 '23 at 12:35
  • The result of @@version is : "Microsoft SQL Azure (RTM) - 12.0.2000.8 Jan 12 2023 05:25:39 Copyright (C) 2022 Microsoft Corporation " – Fernando Delago Feb 21 '23 at 14:11
  • That's Azure SQL Database, @FernandoDelago , not a managed instance. Azure SQL Database doesn't support the syntax you have tried to use, as per the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-ver16&tabs=delimited). – Thom A Feb 21 '23 at 19:08
  • Could you share the result of this query `SELECT SERVERPROPERTY('EngineEdition')` . – Aswin Feb 22 '23 at 04:14
  • The result of SELECT SERVERPROPERTY('EngineEdition') is 5. – Fernando Delago Feb 23 '23 at 12:42

1 Answers1

2

Version can be similar for both Azure SQL database and Azure SQL managed Instance.

The result of SELECT SERVERPROPERTY('EngineEdition') is 5.

This confirms that you use Azure SQL database and not a managed instance.

enter image description here

Image Reference: SERVERPROPERTY (Transact-SQL) | Microsoft Learn

  • External file format is not supported in Azure SQL database.
  • To copy data from blob storage to Azure SQL database, you use either bulk insert T-SQL command or Open rowset.

Step:1 Create Database scoped credentials

If Azure blob storage is not public, create database scoped credentials. Otherwise, skip to next step.

CREATE DATABASE SCOPED CREDENTIAL <credential-name>
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<sas-value>';

Step:2 Create External data source

CREATE EXTERNAL DATA SOURCE <datasource-name>
WITH ( TYPE = BLOB_STORAGE,
       LOCATION = 'https://<storage-account>.blob.core.windows.net',
       CREDENTIAL= <credential-name>);

If your blob is public, you can remove credential option in the above query.

Step:3

BULK INSERT product FROM '<path>' WITH 
( DATA_SOURCE = '<datasource-name>');

or

SELECT *
FROM OPENROWSET(BULK '<path>', DATA_SOURCE = '<datasource-name>'
) as products;

enter image description here

Reference: Loading files from Azure Blob storage into Azure SQL Database | Microsoft Azure

Aswin
  • 4,090
  • 2
  • 4
  • 16