Openrowset syntax issues in Azure Synapse

1
votes

I'm using Azure synapse query editor to run the below query, This is also an example provided in the Azure documentation

SELECT
    nyc.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM  
    OPENROWSET(
        BULK '../userdata1.parquet',
    DATA_SOURCE = AzureStorage,
    FORMAT_TYPE = PARQUET
    ) nyc
GROUP BY nyc.filename();

But it always throws

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

But, I was able to successfully run the Create statement with full schema and perform select queries on the external table

CREATE EXTERNAL TABLE dbo.userdata1 (
    [registration_dttm] nvarchar(100) NULL,
    [id]                decimal(38,0) NULL,
    [first_name]        nvarchar(100) NULL,
    [last_name]         nvarchar(100) NULL,
      |
      |
      |
)
WITH (
    LOCATION='../userdata1.parquet',
    DATA_SOURCE = AzureStorage,
    FILE_FORMAT=parquet_file_format
);

Select @@Version --

Microsoft Azure SQL Data Warehouse - 10.0.15225.0 Sep 8 2020 20:17:38 Copyright (c) Microsoft Corporation

Please correct me if anything wrong...

1
azuretsqlazure-blob-storageazure-sql-data-warehouseazure-synapse

1 Answers

1
votes

You are missing the "AS" when you assign nyc to the rowset:

SELECT ... FROM
OPENROWSET( ... ) AS nyc GROUP BY nyc.filename();