CODEWITHSUNDEEP
sqlazureazure-synapseazure-data-lake-gen2
Ganesh
Ganesh

Reputation: 123

Azure Synapse SQL on-demand pagination (OFFSET/FETCH)

We have Azure data lake - where the data is stored in parquet file format. We are trying to fetch the data from the parquet file using Azure synapse SQL-on Demand.

What we are looking for is - pagination while fetching the data. So if there are 10,000 records matching to the criteria, we want to return only 100 rows on UI and then next 100 etc. In SQL query there is OFFSET/FETCH which we can use.

How to do such thing for SQL on-demand query? Here is our sample query of SQL on demand.

  SELECT * FROM 
    OPENROWSET
     ( 
       BULK '*.parquet',  
       FORMAT='PARQUET'
     ) 
     AS rows

Upvotes: 1

Views: 2373

Answers (2)

np_6
np_6

Reputation: 511

Update

OFFSET/FETCH is now officially supported by SQL OD, along with a few other features.

Update

Just tried to execute an openrowset with OFFSET and FETCH - it works now.

As stated in this blog post, OFFSET/FETCH isn't currently supported by SQL OD, however there are some workarounds.

TOP offset+fetch

SELECT *
FROM (
       SELECT TOP (@fetch) *
       FROM
              (SELECT TOP(@offset+@fetch) *
                      FROM dbo.Supplier
                      ORDER BY S_SUPPKEY ASC
              ) AS topFO
       ORDER BY S_SUPPKEY DESC
     ) AS bottomF
ORDER BY S_SUPPKEY ASC

ROW_NUMBER window

SELECT TOP (@fetch) *
FROM
( SELECT ROW_NUMBER() OVER(ORDER BY S_SUPPKEY) AS RowNum, *
       FROM dbo.Supplier AS tr) AS data
WHERE @offset < RowNum
ORDER BY data.S_SUPPKEY

Upvotes: 1

HarithaMaddi-MSFT
HarithaMaddi-MSFT

Reputation: 551

Currently SQL on-demand doesn’t support offset/fetch as in document. Today there is no functionality available in synapse workspaces to do pagination.

Upvotes: 0

Related Questions