ADF read files at 3rd level in the storage

Question

I have storage account which has three sub folders and than csv files. Below is the folder structure.

Inbound Container-Market 1 - Category 1,2 - files.csv

             -Market 2 - Category 3,4 - Files.csv

             -Market N - Category N   - Files.csv

Files are always in the Category folders. I need to loop through process these files one by one. And to do so, I need the file name and path. How can I achieve this in data factory. I also tried to find if I can configure data set to read from third level but no luck.

I can do the method explained here, but performance of that would be bad and I don't want to use the any other resource like azure function.

I can not use copy because I need to process files differently based on their name and location.

Rakesh Govindula · Answer

I reproduced the above scenario and got the file names in ADF.

AFAIK, in ADF we can get the file location from the dataset if we give the folder manually in the dataset. In this case we are not giving the folder name manually, so ADF pipeline cannot get the location of the file. You can get the file names from the 3rd level from Get Meta data activity.

Please follow the below process to get the file names in the 3rd level.

These are files storage structure:

inputroot(container)
    market1
        category1,2
            csvin1.csv
    market2
        category3,4
            csvin2.csv
    market3
        category5,6
            csvin3.csv

Get Meta data activity doesn't have any wild card path option. So, create a dataset parameter and use that as wild card placeholder in the dataset.

In the Get Meta data activity, give the below value /*/*/ in the Dataset parameter and you can get the result like below.

enter image description here

Result filenames:

enter image description here

For both file name and path, the method that you have given above will be the solution for this if you want to do this only in ADF.

If you want to use other resource, you can try databricks. You can pass the result into ADF array variable.

Create a databricks Notebook and give the following code:

#Mount the Storage account 
dbutils.fs.mount(
  source = "wasbs://container@storageaccount.blob.core.windows.net",
  mount_point = "/mnt/mountpoint",
  extra_configs = {"fs.azure.account.key.storageaccount.blob.core.windows.net":"Storage account Account Key"})

#Get all file paths inside the container   
import glob
ok=[]
ok = [{"path":i} for i in glob.iglob('/dbfs/mnt/mount3/**/*.csv',recursive=True)]
#print(ok)

#Pass this as JSON to ADF
import json
dbutils.notebook.exit(json.dumps(ok))

Drag a Databricks Notebook activity and create the linked service and give the Notebook.

enter image description here

When you execute the Notebook activity it will run the code like below.

enter image description here

You can see the above Notebook exited with a JSON. So, store that in ADF array variable with the below expression.

@activity('Databricks Notebook for file paths').output.runOutput

enter image description here

Result in Array variable with file locations :

enter image description here

Store the Meta data activity output in another array variable. Now, you can use these two variables further as per your requirement.

ADF read files at 3rd level in the storage

1 Answers