Dedicated SQL Pool: "Parse error at line: 4, column: 5: Incorrect syntax near 'OPENROWSET' "

Question

I am using Azure Synapse. I have created a dedicated SQL Pool. I am trying to use the below T-SQL script to query data from my Azure Gen 2 account:

SELECT
    TOP 100 *, result.filename()
FROM
    OPENROWSET(
        BULK 'https://bidi65.dfs.core.windows.net/taxi/raw/trip_data_green_parquet/year=*/month=*/*.parquet',
        FORMAT = 'PARQUET'
    )  
    WITH (
        VendorID INT, 
        lpep_pickup_datetime datetime2(7)
        )   AS [result]

I get the following error:

Parse error at line: 4, column: 5: Incorrect syntax near 'OPENROWSET'.

When I used the same script to query data from my Serverless SQL Pool it worked.

Answer 1

As per this MS Document

The OPENROWSET function is not supported in dedicated SQL pool.

To access external files, you need to build an external data source, external file format, and external table on the dedicated SQL Pool.

Sample Code:

IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseParquetFormat') 
    CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat] 
    WITH ( FORMAT_TYPE = PARQUET)
GO

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'fsnp2_dlsgp2_dfs_core_windows_net') 
    CREATE EXTERNAL DATA SOURCE [fsnp2_dlsgp2_dfs_core_windows_net] 
    WITH (
        LOCATION = 'abfss://<container_name>@<storageaccount_name>.dfs.core.windows.net' 
    )
GO

CREATE EXTERNAL TABLE [dbo].[sample] (
    [Col1] int,
    [Col2] nvarchar(4000),
    [Col3] nvarchar(4000),
    .......
    [ColN] nvarchar(4000)
    )
    WITH (
    LOCATION = 'parquetefilename.parquet',
    DATA_SOURCE = [fsnp2_dlsgp2_dfs_core_windows_net],
    FILE_FORMAT = [SynapseParquetFormat]
    )
GO


SELECT TOP 100 * FROM [dbo].[sample]
GO

Output: