lookup activity output ,read custom activity and generate output .txt file

N2120 81 Reputation points
2023-04-03T04:04:54.1333333+00:00

Hello,

I have a lookup activity which reads mysql table, Its output is in this format.

"firstRow": {
         "FirstName": "John",
        "LastName": "Doe",
       "LocationName": "MA"
    },

I need a python script inside custom activity which would read the firstrow of lookup activity and generate an output .txt file.

Could you please advice. small correction: Move all rows in lookup activity not just first row.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,751 questions
0 comments No comments
{count} votes

Accepted answer
  1. AnnuKumari-MSFT 31,726 Reputation points Microsoft Employee
    2023-04-04T10:50:23.9433333+00:00

    Hi @N2120 , Thankyou for using Microsoft Q&A platform and thanks for posting your query here. As I understand your ask , you are trying to fetch the firstrow json out of the lookup activity and store it in the .txt file using python script via custom activity in ADF. Please let me know if that is not the ask here. To repro your case, I created a pool under the batch account . Also, stored the below python script in my storage account which is supposed to read the input argument and store the same in output.txt file in document form:

    from sys import argv
    print(argv[1])
    
    output=argv[1]
    outdic={}
    outdic["firstrow"]=output
    
    
    # Write output text to file
    with open('output.txt', 'w') as f:
        f.write(str(outdic))
    
    
    1. Use look up activity to read the sql table data.
    2. Use Foreach activity to iterate through the lookup output, since you need only firstrow in the output file, you can use below expression in foreach , or you can directly use custom activity .
       @array(activity('Lookup1').output.value[0])
    

    3.Use custom activity inside foreach, and use this expression in command :

       @concat('python firstrow.py ', item())
    

    Below are the videos of implementation:firstrow1

    You can see the output.txt file in the batch account job under the root folder . firstrow2

    For more details on creation of batch account and pool, you can go through the following videos: Azure Data Factory - Execute Python script from ADF Azure Data Factory - Access Custom Activity Output from subsequent activities in ADF

    Hope it helps. Kindly accept the answer as accepted answer helps community. Thankyou


1 additional answer

Sort by: Most helpful
  1. Susheel Bhatt 351 Reputation points
    2023-04-03T08:04:59.4666667+00:00

    Here's a Python script that reads the first row of the Lookup activity's output and generates an output file. Replace <lookup_output_file> with the path to your Lookup activity's output file, and <output_file> with the desired path for the output file. The script reads the Lookup output file as JSON, extracts the first row data, generates the desired output text, and writes it to the output file.

    import json
    
    # Read Lookup activity output file
    with open('<lookup_output_file>.json') as f:
        lookup_output = json.load(f)
    
    # Extract first row data
    first_row = lookup_output['firstRow']
    
    # Generate output text
    output_text = f"Name: {first_row['FirstName']} {first_row['LastName']}\nLocation: {first_row['LocationName']}"
    
    # Write output text to file
    with open('<output_file>.txt', 'w') as f:
        f.write(output_text)