Add Azure Blob Partitions To Azure Sql Table

I have partitioned parquet files in Azure Blob that I am copying to Azure SQL. How do I get the partition name into the SQL table? I've figured out how to get the full file path in

Solution 1:

As explained here in MS doc, you can utilize enablePartitionDiscovery feature.

Source: partitioned files:

Source Dataset:

Just mentioned the container name and leave the directory and file fields empty. We shall filter them using WildCard paths in Copy Activity.

Configure source in Copy Activity with respect to your files path:

Note: you can skip the step 4 i.e. additional column with $$FILEPATH, just shown for reference. You can drop this bit as you already get the ready column using enablePartitionDiscovery.

For a single folder to be picked, you will set as below.

Wildcard paths:sink / columnparts / time_period=202105 / *.parquet

For multiple folders time_period=202105 , time_period=202106 ..... as seen in previous sinp, set as below.

** will take the place of any folder in the parent folder columnparts

Wildcard paths:sink / columnparts / ** / *.parquet

Partition root Path: This should point to the parent folder where all the partitioned folders rest.

In my example: sink/columnparts

partition root path must be provided when you enable partition discovery.

Sink: Optional update existing table or just create a new one.

View from SQL DB:time_period column holds the value 202105

time_period=202105/part-00004-fcbe0bf5-2c93-45f5-9bb2-2f9089a3e83a-c000.snappy.parquet

If you see this error:

You have a mapping that is not updated! In the mapping section, you can clear or reset schema and Import schema again just to be sure. 😊

In my case it was additional column file_path

--OR--

$$FILEPATH is a reserved variable, you cannot use it in expression builder or in functions to manipulate.

Instead if you can incorporate a step after you copy to SQL DB i.e. use a stored procedure as below.

Where column path holds the full file path received from $$FILEPATH as you have managed already. StoreParquetTest is the table created in SQL sink

CREATE PROCEDURE trimpath
AS
    UPDATE StoreParquetTest
    SET path = SUBSTRING(path,(CHARINDEX('=',path) + 1), ((CHARINDEX('/',path) - CHARINDEX('=',path) -1)))
GO

Now you can use the stored procedure activity in Pipeline after Copy Activity.