Azure Data Factory - Extracting API Data into JSON files

Problem Statement

A client has asked me to create a solution to return the Police Forces of the UK into a Storage Account in JSON format. To achieve this, the client asked me to use the following:

• Police Data API - https://data.police.uk/docs/

• Azure Storage Account for the Export of the Forces as JSON files

• Azure Data Factory to coordinate the pipeline

Task:

• Loop through each force to return the Force Info. Using https://data.police.uk/docs/method/force/

• The Sink datasets should be the Force Name + ".json"

Solution

To resolve the client's problem I designed the following pipe as my solution.

The pre-requisites are:

• Have a Azure Storage Account set up with a container for the data.

Additional steps before we start working on the pipe is to set-up the the following:

1. Have 2 linked services

   1. Police Forces API -> https://data.police.uk/api/forces

   2. Azure Storage Account Container

2. Pipeline parameters:

   1. @MasterFileName - String - "Police_Force_Master.json"

3. Pipeline Variables:

   1. PoliceFileName - String

   2. PoliceFileId - String

The logical progression of the pipe is:

• Copy Data Activity -> to copy all the police data from the API into one master file

Source Dataset Configuration

Sink Dataset Configuration

I set up a dataset variable called FileName to pass over the Police Master File parameter that we created at the beginning of the pipe.

• Lookup Activity -> This lookup activity uses the Master File that we created in the first step and loops through all the items to return their values.

• ForEach loop Activity -> For each item - set @PoliceFileName variable and @PoliceFileId variable and create individual files in the blob container

We pass the following expression in the For each loop settings and tick the sequential.

@activity('lookup police forces').output.value

• Set @PoliceFileName variable

• Set @PoliceFileId variable

• Copy Activity -> Police Specific Forces Extract

Source Dataset Configuration

Sink Dataset Configuration

Result

The container is populated with JSON files for each police force with relevant police force data. Each file is named -> police force name + ".json".