import json payload from a rest api and save as json documents in adls gen2

Question

Hi, I am trying to import json payload from a REST api GET method and save json documents into ADLS Gen2 using azure databricks.
GET: https://myapi.com/api/v1/city

GET method Output:

    [
    {"id":2643743,
     "name":"London"},
    {"id":2643744,
     "name":"Manchester"}
    ]

Powershell:

    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
   
    $username = "user"
    $password = "password"
   
    $params = @{uri = 'https://myapi.com/api/v1/city';
                       Method = 'Get';
                       Headers = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("$($username):$($password)"));
               } #end headers hash table
       } #end $params hash table
   
    $var = invoke-restmethod @params -ContentType "application/json".Content | ConvertTo-Json

Now, I'm stuck with how to save json document in Azure Data Lake Storage Gen2. Please guide me.

Thank you.

Answer 1

Hello,

Welcome to Microsoft Q&A platform.

You can use df.write.json API to write to any specific location as per your need.

Syntax:df.write.json('location where you want to save the json file')

Example:df.write.json("abfss://<file_system>@<storage-account-name>.dfs.core.windows.net/iot_devices.json")

Here are the steps to save the JSON documents to Azure Data Lake Gen2 using Azure Databricks.

Step1: You can use spark.read.json API to read the json file and create a dataframe.

Step2: The blob storage location can be mounted to a databricks dbfs directory, using the instructions in below doc

https://learn.microsoft.com/en-us/azure/databricks/data/data-sources/azure/azure-datalake-gen2

Step3: Then use the df.write.json API to write to the mount point, which will write to the blob storage

For more details, refer the below articles:

Azure Databricks – JSON files

Sample notebook: https://learn.microsoft.com/en-us/azure/databricks/_static/notebooks/adls-passthrough-gen2.html

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

----------------------------------------------------------------------------------------

Do click on "Accept Answer" and Upvote on the post that helps you, this can be beneficial to other community members.

Answer 2

Is this what you're looking for?

data-lake-storage-directory-file-acl-powershell