Azure Data Factory Tutorial

author(s) kyle vessey, september 2022 developer prerequisites mtls certificate, client id and client secret key mtls & api key creation docid\ hro4lnngi79gn1ww2y ma active azure subscription internet connection data schema please read the data schema fundamentals docid\ irzm o cyhcgjw5athv1 document before using the api database crud operations are performed via the api and therefore it is important to think of each http rest operation (post/get/delete) as sql transaction (upsert/select/delete) visualizing data data inserted or modified using this tutorial can be visualized using the web portal application(s) at https //\<tenant>app onxecta com https //\<tenant> app onxecta com/ the portal contains a data quality reporting tool where you can see what data is in the system, the validity of the data and when the data was last modified authenticate to xecta api generate pfx certificate to authenticate with the api through azure data factory, microsoft requires the client mtls certificate to be in a pfx format the pfx certificate is just a combination of the private key and the pem file which was generated as part of the mtls & api key creation docid\ hro4lnngi79gn1ww2y ma tutorial if you have not already generated your csr file and submitted to xecta, please do so before continuing with this tutorial steps to generate pfx certificate to generate the pfx with openssl, run the following command in your local repository where your pem and pfx files are located openssl pkcs12 inkey xecta data api key in xecta data api pem export out xecta data api pfx remember your password make note of the password which you assigned to the pfx certificate, you will need to add that password in the azure portal to authenticate add pfx to azure key vault login to the https //portal azure com/ to begin if you do not have a key vault created in your azure account, follow the microsoft azure key vault tutorial https //learn microsoft com/en us/azure/key vault/general/quick create portal to get your key vault set up in your key vaults, select the key vault which you wish to assign your certificate to navigate to the "certificates" page import your certificate click the "generate/import" button to import your pfx certificate select the import option enter a name for your certificate and upload your certificate file in the password field you should enter the password for the pfx file which was generated in a previous step now that your certificate is uploaded in the azure key vault, navigate back to the data factory to build your pipeline create new azure data factory login to microsoft azure portal login to the microsoft azure portal https //portal azure com/ to begin create a resource click "create a resource" to begin building a new data factory resource select "data factory" as the resource search for data factory in the search bar, and select the data factory option click the "create" button you will now see the data factory landing page create new pipeline open azure data factory studio to create a new pipeline, open the data factory studio create a new pipeline open the "author" tab click the pipelines tab and select the menu click "new pipeline" web activity for authentication the first step needed to interact with the api is to build a web activity which will be used to return an access token we will use a post web request to return this access token and pass it on through the pipeline create a new web activitiy under pipelines, navigate to the "general" section and select a web activity give the web activity the name of "postrequestaccesstoken" web activity name you can use any name you would like for this web activity, however make sure you use the same name later on in the tutorial as it will be referenced in some other fields configure settings for web activity select the postrequestaccesstoken activity in the canvas area, then navigate to the settings tab to configure this activity set url set the url to https //data sandbox onxecta com/authenticate/oauth2/token?grant type=client credentials https //data sandbox onxecta com/authenticate/oauth2/token?grant type=client credentials set method from the dropdown, set the method to post request body enter two double quotes (ie "" ) for the request body empty request body it is important that you pass the empty pair of double quotes in the request body azure data factory requires a valid body payload for all post requests, however there are no body parameters that the xecta api requires authentication from the dropdown, select client certificate pfx and password for the pfx field, click the azure key vault checkbox on the right, then select the key vault which contains the pfx file from the previous key vault step for the password field, enter the password which was previously defined for your pfx file above headers the following headers must be added name value content type application/x www form urlencoded authorization basic {base64 encoded client key\ client secret pair} base64 encoded authorization header a limitation for using mtls with azure data factory is that we require passing both the client pfx certificate and an client id / client secret api keys mtls & api key creation docid\ hro4lnngi79gn1ww2y ma data factory only allows you to select either a client certificate or basic auth, so we have to append our basic auth as a header value you can encode the client id / client secret through data factory using the @base64() function ex @base64('2nkdkdf402dhfdsjlxecta123456 1903872fekdhfsl8457847932dhsfjdshfefj489xecta12345') another option is to use a base64 utility such as https //www base64decode org/ https //www base64decode org/ , then enter your clientid\ clientsecret ex copy the decoded string at the bottom and place into your authorization header value authentication activity complete once you configure all of the above, the final result should look like this example web activity for adding / upserting wells after we have the authentication activity created, we can now push wells to the api create a new web activity create a new web activity and name it "addupsertwells", then connect it via the green "on success" arrow configure settings set url set the url to https //data onxecta com/api/production/wells https //data onxecta com/api/production/wells set method set the method to post request body enter a valid payload for adding a well example payload for demo purposes authentication refer to the same steps above headers the following header needs to be added to this activity name value authorization @concat('bearer ',activity('postrequestaccesstoken') output access token) this authorization header value takes the access token from the postrequestaccesstoken web activity