How to parameter the folder directory as today date in openrowset for parque file

Question

Hi, I need to parameter the Filedirectory value as todaydate in openrowset. I will be getting the parque file daily in datalake at Folder directory with today date like 20230426, so each day need to query the parquet file from the current folder and take the output. I have tried using different way but its not working. Could you please help on how to add below value as parameter in sql script

-- This is auto-generated code
SELECT    
 *
FROM
    OPENROWSET(
        BULK 'https://synapsestoragetest.dfs.core.windows.net/test/Index/@parameter as todaydate like(20230426)/*.*',
        FORMAT = 'PARQUET'
    ) AS [r]

Answer 1

Hi Prabhuram Kumar ,

Welcome to Microsoft Q&A platform and thankyou for posting your question here.

As per my understanding, you are trying to parameterize the folder path in openrowset function in synapse SQL script. Please let me know if that is not the ask.

To parameterize the file/folder directory value in your SQL script, You can use the wildcards in the OPENROWSET part of the query and filter the files in the WHERE clause.

For example:

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',        
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Note that you may need to adjust the file path and file name to match your specific scenario. Also, make sure that the SQL Server instance has the necessary permissions to access the data lake storage account

For more details, kindly check the following documentation: Use file metadata in serverless SQL pool queries

Hope it helps. Kindly accept the answer if it's helpful. Thankyou.