Question 321311 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to create a Json file and save it to a storage account using Azure Data Factory

I’m new to Azure Data Factory and have a requirement that sounds pretty simple but I not able to figure it out.

I have a Json file saved in a storage acount container, from which i need to read the value of "LastRunDate" (pretty easy).

{
    "LastRunDate": "2024-01-12"
}

The problem is, there is a senarion where this file might not exist. If the file does not exist while trying to read it, i need to create the file on the fly and write the below default json to it.

{
    "LastRunDate": "2000-01-01"
}

My approach was to use the "Get MetaData" Activity and use the metadata "exist" to find out if the file exist or not – which was fine. My thought then was to assign a variable (with the default Json string) and write the value of the variable to the new file. This is the point where i hit a brick wall. Im not able to create the json file on the fly with a default value.

Tags:

2

Answers


  1. 0

    The simplest solution:

    Create the default file manually in a different folder on the storage account (once)

    In your pipeline:

    1. use metadata activity to check the content of the main folder
    2. use if activity to check if the main file is missing
    3. use a copy data activity to copy the default file to the main location
    Login or Signup to reply.
  2. 0

    You can also try below approach to create JSON file from ADF variable.

    First take a dummy csv file in a temporary storage location with only one row data like below.

    enter image description here

    Create a dataset for this and uncheck the First row as header in the dataset settings.

    enter image description here

    Now, create another csv dataset with the storage location to the required JSON file.

    Here, we are creating a new JSON file myfile.json using csv dataset, so give the below configurations in the csv dataset.

    enter image description here

    Now, after checking whether the file exists or not with Get meta data activity, take an if activity and give the output from Get meta data exists to this if activity expression.

    Here, I have given the below expression.

    @not(activity('Get Metadata1').output.exists)

    In the True activities of if, we are going to create new JSON file with defaut JSON. In the False activities of if activity, you can go ahead with your activities.

    First give your JSON as a string in a string variable using set variable activity.

    @string('{
"LastRunDate": "2000-01-01"
    }')

    enter image description here

    In copy activity, take the dummy dataset as source and the another csv dataset as sink.

    In the source, create an additional column by clicking on new and give the column name (Here column name is new) and give the above string variable in the dynamic content like below.

    enter image description here

    Give the below expression as dynamic content for the mapping of the copy activity.

    @json('{
                                    "type": "TabularTranslator",
                                    "mappings": [
                                        {
                                            "source": {
                                                "name": "new",
                                                "type": "String"
                                            },
                                            "sink": {
                                                "type": "String",
                                                "physicalType": "String",
                                                "ordinal": 1
                                            }
                                        }
                                    ],
                                    "typeConversion": true,
                                    "typeConversionSettings": {
                                        "allowDataTruncation": true,
                                        "treatBooleanAsNumber": false
                                    }
                                }')

    This mapping ensures to copy only the additional column that we created in the source.

    enter image description here

    Debug the pipeline, and if there is no JSON file in your storage location, your JSON file with required JSON will be created like below.

    enter image description here

    If your requirement is only to create a default JSON, then the above answer by @Chen Hirsh is a better option.

    But if you want to create a dynamic JSON from ADF, then you can follow this approach because in this approach you can build whatever JSON that you want dynamically in the set variable and give it to copy activity.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search