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

Prabhuram Kumar 0 Reputation points

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
        BULK ' as todaydate like(20230426)/*.*',
        FORMAT = 'PARQUET'
    ) AS [r]

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
3,797 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
8,455 questions
{count} votes

1 answer

Sort by: Most helpful
  1. AnnuKumari-MSFT 28,001 Reputation points Microsoft Employee

    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:

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

    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.