How to store json data in csv of API which is authenticated using grant_type = password in ADF

Question

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.

Answer 1

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:

1. 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_type as password. Provide the necessary credentials (username, password, client ID, and client secret) in the linked service configuration.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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

Mapping data flow properties

Hope this helps. Do let us know if you have any further queries.