Filtering Data while Creating views on synapse serverless with FORMAT='DELTA'?

GSMATTA 1 Reputation point
2021-11-15T09:00:06.48+00:00

Hi , Earlier i was creating views on serverless with FORMAT='PARQUET' , there i used filepath() function to filter data while creating view . But filepath() function not working when format is delta , as delta stores metadata , is there a way to filter data while creating views ?

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.
5,178 questions
{count} votes

1 answer

Sort by: Most helpful
  1. PRADEEPCHEEKATLA 90,541 Reputation points
    2021-11-16T05:44:58.683+00:00

    Hello @GSMATTA ,

    Welcome to the MS Q&A platform.

    This is an excepted behaviour when using Delta Lake partitioned views.

    If you are creating the partitioned views on top of Delta Lake storage, you can specify just a root Delta Lake folder and don't need to explicitly expose the partitioning columns using the FILEPATH function:

    CREATE OR ALTER VIEW YellowTaxiView  
    AS SELECT *  
    FROM    
        OPENROWSET(  
            BULK 'yellow',  
            DATA_SOURCE = 'DeltaLakeStorage',  
            FORMAT='DELTA'  
        ) nyc  
    

    You can use the FILEPATH function with Partitioned views.

    If you have a set of files that is partitioned in the hierarchical folder structure, you can describe the partition pattern using the wildcards in the file path. Use the FILEPATH function to expose parts of the folder path as partitioning columns.

    CREATE VIEW TaxiView  
    AS SELECT *, nyc.filepath(1) AS [year], nyc.filepath(2) AS [month]  
    FROM  
        OPENROWSET(  
            BULK 'parquet/taxi/year=*/month=*/*.parquet',  
            DATA_SOURCE = 'sqlondemanddemo',  
            FORMAT='PARQUET'  
        ) AS nyc  
    

    For more details, refer to Create and use views using serverless SQL pool in Azure Synapse Analytics.

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.