OPENROWSET(FORMAT='PARQUET') syntax error in Azure SQL Database

Question

Documentation suggests that OPENROWSET should support retrieving data in Parquet files from Azure BLOB Storage in Azure SQL Database [1], but for me it is returning "[Code: 102, SQL State: S0001] Incorrect syntax near 'FORMAT'."

This is a new Azure SQL Database Serverless instance, with a database set to compatibility level 160.

Is SQL Database capable of reading formats other than CSV from BLOB Storage?

[1] https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver16

Answer 1

@Matt Keranen Thank you for reaching out.

Sorry to hear about the issue you are facing using OPENROWSET(FORMAT='PARQUET) error with Azure SQL Database.

OPENROWSET(FORMAT='PARQUET) is currently in private review for Azure SQL Database but it is supported in Azure SQL Managed Instance and Azure synapse SQL pool.

Currently ETA for Public preview is November, GA date is still TBD.

Regards,

Oury

Answer 2

Hello Matt Keranen,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are having OPENROWSET(FORMAT='PARQUET') syntax error in Azure SQL Database which against the document.

Regarding your question:

Is SQL Database capable of reading formats other than CSV from BLOB Storage?

Azure SQL Database supports reading data from Azure Blob Storage in formats such as CSV, Parquet, and Delta. https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver16.

About the syntax error with OPENROWSET(FORMAT='PARQUET') in Azure SQL Database. can be solved with these steps:

-- Confirm that your database compatibility level is set to 160
     ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 160;
   
/* It is crucial to make sure your database collation is set to a UTF-8 compatible collation, such as `Latin1_General_100_BIN2_UTF8` */

   ALTER DATABASE [YourDatabaseName] COLLATE Latin1_General_100_BIN2_UTF8;
  
-- Create an external data source and scoped credential to access your Azure Blob Storage:

   CREATE DATABASE SCOPED CREDENTIAL [YourCredentialName]
   WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
   SECRET = 'YourSASKey';
   CREATE EXTERNAL DATA SOURCE [YourDataSourceName]
   WITH (TYPE = BLOB_STORAGE, LOCATION = 'https://yourstorageaccount.blob.core.windows.net/yourcontainer', CREDENTIAL = [YourCredentialName]);

-- Use the `OPENROWSET` function to query the Parquet file:

   SELECT *
   FROM OPENROWSET(
       BULK 'yourfile.parquet',
       DATA_SOURCE = 'YourDataSourceName',
       FORMAT = 'PARQUET'
   ) AS [result];

For more reading and steps:

• https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files
• https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver16
• https://techcommunity.microsoft.com/t5/azure-sql-blog/troubleshooting-bulk-insert-and-openrowset-issues-on-azure-sql/ba-p/664734

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Answer 3

The only way I know to upload parquet files to Azure SQL is using Copy Activity on Azure Synapse or Azure Data Factory. You can use also write pyspark code in synapse notebook to do the job specially if the parquet data needs to be loaded incrementally.

What you are trying to do is supported with SQL Server 2017 and later versions but is not supported on Azure SQL Database.