Partitioning Data In Sql On-demand With Blob Storage As Data Source

In Amazon Redshift there is a way to create a partition key when using your S3 bucket as a data source. Link. I am attempting to do something similar in Azure Synapse using the SQL

Solution 1:

Serverless SQL can parse partitioned folder structure's using the filename (where you wish to load a specific file or files) and filepath (where you wish to load all files in this said path). More information on syntax and usage is available on documentation online.

In your case, you can parse all files from '2020-10-01' and beyond using the filepath syntax such as filepath(1) > '2020-10-01'

Solution 2:

To expand on the answer from Raunak I ended up with the following syntax for my query.

DROPVIEW IF EXISTS testview6
GO

CREATEVIEW testview6 ASSELECT*,
    r.filepath(1) AS [date]
FROM OPENROWSET (
        BULK 'Sales/*/*.csv',
        FORMAT ='CSV', PARSER_VERSION ='2.0',
        DATA_SOURCE ='AzureBlob',
        FIELDTERMINATOR =',',
        FIRSTROW =2
        ) AS [r]
WHERE r.filepath(1) IN ('2020-10-02');

You can adjust the granularity of your partitioning by the addition of extra wildcards (*) and r.filepath(x) statements.

For instance you can create your query such as:

DROPVIEW IF EXISTS testview6
GO

CREATEVIEW testview6 ASSELECT*,
    r.filepath(1) AS [year],
    r.filepath(2) as [month]
FROM OPENROWSET (
        BULK 'Sales/*-*-01/*.csv',
        FORMAT ='CSV', PARSER_VERSION ='2.0',
        DATA_SOURCE ='AzureBlob',
        FIELDTERMINATOR =',',
        FIRSTROW =2
        ) AS [r]
WHERE r.filepath(1) IN ('2020')
AND r.filepath(2) IN ('10');