Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,968 questions
How to store json data in csv of API which is authenticated using grant_type = password in ADF
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(233.6, 21%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKA%3C/text%3E%3C/svg%3E)
Kukade, Atharva
0
Reputation points
We have requirement where we need to generate token using grant type as password and use that token to call API to get data.
We need to store this data in CSV file in ADLS.
We are able to get the response but are unable to store it in proper json format in ADLS.
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(201.6, 18%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EGG%3C/text%3E%3C/svg%3E)
Ganesh Gurram 1,825 Reputation points Microsoft Vendor2024-12-04T00:37:38.57+00:00 Hi @Kukade, Atharva,
Thanks for the question and using MS Q&A forum.
To store JSON data in CSV format in Azure Data Factory (ADF) using a token from an API with
grant_type=password, you can follow these general steps:- Set Up the API Connection: Create a linked service in ADF to connect to the REST API. - Configure the linked service to use the OAuth2 authentication method, specifying the
grant_typeaspassword. Provide the necessary credentials (username, password, client ID, and client secret) in the linked service configuration. - Generate the Access Token: Use a Web Activity in your ADF pipeline to call the token endpoint of the API. Store the access token in a pipeline variable for later use.
- Call the API to Retrieve Data: Add another Web Activity to call the API endpoint that returns the JSON data. Use the access token obtained in the previous step by including it in the headers of the request.
- Transform JSON to CSV: Use a Data Flow activity in ADF to transform the JSON response into a CSV format. You may need to use the Flatten transformation to handle nested JSON structures effectively. Ensure that you select the appropriate columns and format them as needed for the CSV output.
- Store the CSV in ADLS: Add a Sink transformation in your Data Flow to write the transformed data to Azure Data Lake Storage (ADLS). Specify the file format as CSV and configure the output path in ADLS.
- Debug and Validate: Run the pipeline in debug mode to ensure that the data is being retrieved, transformed, and stored correctly. Check the output in ADLS to confirm that the CSV file is formatted as expected.
For more details refer to: JSON format in Azure Data Factory and Azure Synapse Analytics
Hope this helps. Do let us know if you have any further queries.
- Set Up the API Connection: Create a linked service in ADF to connect to the REST API. - Configure the linked service to use the OAuth2 authentication method, specifying the
Sign in to comment
Sign in to answer