--- title: Azure Data Factory Tutorial slug: azure-data-factory-tutorial docTags: createdAt: 2023-03-22T17:18:02.000Z --- *Author(s): Kyle Vessey, September 2022* :::hint{type="warning"} **Developer Prerequisites:** - mTLS Certificate, Client ID and Client Secret Key [(tutorial)](docId\:HRo4LnNgI79gn1Ww2Y-Ma) - Active Azure Subscription - Internet connection ::: :::hint{type="info"} **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) ::: :::hint{type="success"} **Visualizing Data:** Data inserted or modified using this tutorial can be visualized using the web portal application(s) at [https://\app.onxecta.com](https://\.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 ```shell openssl pkcs12 -inkey xecta-data-api.key -in xecta-data-api.pem -export -out xecta-data-api.pfx ``` :::hint{type="info"} **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 [Microsoft Azure Portal](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 ::Image[]{src="https://files.readme.io/dfe8cf4-select_key_vault.png" size="70" width="1868" height="682" position="center" showCaption="false"} Navigate to the "Certificates" page ::Image[]{src="https://files.readme.io/435b954-navigate_to_certificates.png" size="70" width="3060" height="1772" position="center" showCaption="false"} ### Import your Certificate Click the "Generate/Import" button to import your PFX certificate ::Image[]{src="https://files.readme.io/fea0996-import_certificate.png" size="70" width="3062" height="1074" position="center" showCaption="false"} Select the Import option ::Image[]{src="https://files.readme.io/a7d922a-select_import_option.png" size="70" width="2550" height="674" position="center" showCaption="false"} 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 ::Image[]{src="https://files.readme.io/14460dd-Azure_Landing_Page.png" size="70" width="1910" height="866" position="center" showCaption="false"} ### Create a Resource Click "Create a resource" to begin building a new Data Factory resource ::Image[]{src="https://files.readme.io/6e332c4-DF_Create_a_Resource.png" size="70" width="1910" height="866" position="center" showCaption="false"} ### Select "Data Factory" as the Resource Search for Data Factory in the search bar, and select the Data Factory option ::Image[]{src="https://files.readme.io/076709a-DF_-_Search_for_Data_Factory.png" size="70" width="1714" height="875" position="center" showCaption="false"} Click the "Create" button ::Image[]{src="https://files.readme.io/d598b5a-DF_-_Create_DF.png" size="74" width="1720" height="560" position="center" showCaption="false"} You will now see the Data Factory landing page ::Image[]{src="https://files.readme.io/92e6485-DF_-_Landing_Page.png" size="72" width="1712" height="867" position="center" showCaption="false"} *** # Create New Pipeline ### Open Azure Data Factory Studio To create a new Pipeline, open the Data Factory Studio ::Image[]{src="https://files.readme.io/39925ed-DF_-_Open_DF_Studio.png" size="70" width="1712" height="867" position="center" showCaption="false"} ### Create a New Pipeline Open the "Author" tab ::Image[]{src="https://files.readme.io/4605f99-author_tab.png" size="70" width="3070" height="526" position="center" showCaption="false"} Click the Pipelines tab and select the menu. ::Image[]{src="https://files.readme.io/9152e4e-create_new_pipeline_menu.png" size="70" width="1352" height="636" position="center" showCaption="false"} Click "New Pipeline" ::Image[]{src="https://files.readme.io/1d15e67-Create_new_pipeline.png" size="70" width="1384" height="652" position="center" showCaption="false"} ### 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 ::Image[]{src="https://files.readme.io/566039c-web_activity.png" size="70" width="2176" height="1602" position="center" showCaption="false"} Give the Web activity the name of "PostRequestAccessToken" ::Image[]{src="https://files.readme.io/f02f053-postrequestaccesstoken.png" size="72" width="1680" height="1010" position="center" showCaption="false"} :::hint{type="info"} **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 :::hint{type="warning"} **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 ![](https://api.archbee.com/api/optimize/-kIqrMCOJgmgBmRpzgKo2/b4P1LOJE5tnw714qUs4kf_image.png) ::: ### 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} | :::hint{type="info"} **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 [given to you in this step](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') ![](https://api.archbee.com/api/optimize/-kIqrMCOJgmgBmRpzgKo2/zuRvUXbi_5kAA_zj2XpiA_image.png) Another option is to use a Base64 utility such as [https://www.base64decode.org/](https://www.base64decode.org/), then enter your ClientId\:ClientSecret Ex: ![](https://api.archbee.com/api/optimize/-kIqrMCOJgmgBmRpzgKo2/5SuurZE4ZP82TAobpab4M_image.png) Copy the Decoded String at the bottom and place into your Authorization Header value ![](https://api.archbee.com/api/optimize/-kIqrMCOJgmgBmRpzgKo2/-zVqXcY_wUjveNj5KFgN8_image.png) ::: ### Authentication Activity Complete Once you configure all of the above, the final result should look like this example: ::Image[]{src="https://files.readme.io/e9b9543-auth_activity_settings.png" size="70" width="2216" height="1616" position="center" showCaption="false"} ### 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 ::Image[]{src="https://files.readme.io/c50b7a0-addwellactivity.png" size="70" width="1648" height="1098" position="center" showCaption="false"} ### 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:* ```text ``` ### 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