CODEWITHSUNDEEP
azure-data-factoryazure-databricks
Developer Rajinikanth
Developer Rajinikanth

Reputation: 354

Azure DataBricks output is not able to loop in ADF foreach Loop

I am trying to send the array list of Json value to azure data factory ForEach loop from my databricks notebook. there is error and failed my ForEach loop as below message,

enter image description here

The execution of template action 'MainForEach1' failed: the result of the evaluation of 'foreach' expression '@activity('test_notebook').output.runOutput' is of type 'String'. The result must be a valid array.

my databricks code as below like,

final_df = spark.sql("select * from table1")

def call(row):
    row = row.asDict()
    payload = dict()
    payload["id"] = row.get("t1")
    payload["name"] = "test"
    payload["name2"] = row.get("cname")
    payload["add"] = row.get("add1")
    payload["add2"] = row.get("add2")
    payload["c1"] = row.get("c1")
    payload["s1"] = row.get("s1")
    payload["country"] = row.get("Country")
    payload["code"] = row.get("code")
    response = requests.post(api_endpoint,json=payload,headers=headers)
    return response

list_data=list()
# testing purpose
x = final_df.collect()[1:5]
for row in x:
    api_response =call(row)
    temp = {"details":{},"reason":""}
    temp["edetails"]["input"] = row.asDict()
    temp["edetail"]["output"] = api.text
    if api.status_code != 200:
        temp["s"] = "f"
        temp["reason"] = api.text
    else:
        temp["s"] = "s"
    list_data.append(temp)

dbutils.notebook.exit(list_data)

below output for trying builded array from current notebook but adf not accepting this array list.

[{'EDetails': {'Input': {'test': '3231232', 'cname': 'sdasa', 'add1': None, 'add2': None, 'c1': None, 's1': None, 'c2': None, 'code': None}, 'Output': '{"Message":"An error has occurred."}'}, 'Reason': '{"Message":"An error has occurred."}', 's': 'f'}, {'EDetails': {'Input': {'test': '24431212', 'cname': 'somename', 'add1': 'sfasasd', 'add2': 'dsadasdasd', 'c1': 'swqwewqe', 's1': ' ', 'c1': 'asddadasd', 'code': 'sads2323'}, 'Output': '{"test":"sad12321312312"}'}, 'Reason': '', 's': 's'}, {'EDetails': {'Input': {'test': '23232323', 'cname': "ffffff", 'add1': '43ddsdsd', 'add2': 'NA', 'c1': 'sdfdsfsdf', 's': ' ', 'c1': 'Us', 'code': '323dsds'}, 'Output': '{"test":"0232323"}'}, 'Failure': '', 's1': 's'}, {'EDetails': {'Input': {'test': '33sdsd', 'cname': "sdasdasdas", 'add1': 'sdadsad', 'add2': 'NA', 'c1': 'sadsd', 's1': ' ', 'c1': 'km', 'code': '34eerer'}, 'Output': '{"test":"sadsadsads"}'}, 'Failure': '', 's': 's'}]

Upvotes: 0

Views: 297

Answers (1)

Rakesh Govindula
Rakesh Govindula

Reputation: 11454

I got same error when I tried to return your list of dictionaries from Notebook to ADF.

enter image description here

Due to the None values, it is converting the list of dictionaries to the string when returning the value from Notebook to ADF.

enter image description here

And that's why you are getting the above error.

To resolve the error, use the below code before returning the value from Notebook.

import json

list_data=[{'EDetails': {'Input': {'test': '3231232', 'cname': 'sdasa', 'add1': None, 'add2': None, 'c1': None, 's1': None, 'c2': None, 'code': None}, 'Output': '{"Message":"An error has occurred."}'}, 'Reason': '{"Message":"An error has occurred."}', 's': 'f'}, {'EDetails': {'Input': {'test': '24431212', 'cname': 'somename', 'add1': 'sfasasd', 'add2': 'dsadasdasd', 'c1': 'swqwewqe', 's1': ' ', 'c1': 'asddadasd', 'code': 'sads2323'}, 'Output': '{"test":"sad12321312312"}'}, 'Reason': '', 's': 's'}, {'EDetails': {'Input': {'test': '23232323', 'cname': "ffffff", 'add1': '43ddsdsd', 'add2': 'NA', 'c1': 'sdfdsfsdf', 's': ' ', 'c1': 'Us', 'code': '323dsds'}, 'Output': '{"test":"0232323"}'}, 'Failure': '', 's1': 's'}, {'EDetails': {'Input': {'test': '33sdsd', 'cname': "sdasdasdas", 'add1': 'sdadsad', 'add2': 'NA', 'c1': 'sadsd', 's1': ' ', 'c1': 'km', 'code': '34eerer'}, 'Output': '{"test":"sadsadsads"}'}, 'Failure': '', 's': 's'}]

myjson= json.dumps(list_data)
dbutils.notebook.exit(myjson)

It will give the JSON with null values instead of None values and the Notebook activity runOutput also will be a JSON array which you can use in ForEach.

enter image description here

Upvotes: 0

Related Questions