Azure Pipeline - Lookup and CopyData

Hi Tom_CA ,

While I can't get the Json output of the Lookup activity directly through the data pipeline, I do have a workaround to store the JSON output of the lookup activity in a file in Lakehouse.

Please follow my steps to do this:

First, run the Lookup activity successfully in the pipeline.

Then, copy the output json result.

Use the following code to save the output json result as a temporary file. Please replace the parts I marked with your own json output.

import json
import os
import shutil

# Define example schema with more columns
test = {
    "firstRow": {
        "SalesOrderNumber": "SO51555",
        "SalesOrderLineNumber": 7,
        "OrderDate": "2021-06-23T00:00:00Z",
        "CustomerName": "Chloe Garcia",
        "Email": "[email protected]",
        "Item": "Patch Kit/8 Patches",
        "Quantity": 1,
        "UnitPrice": 2.29,
        "Tax": 0.1832
    }
}

# Write to temporary file
temp_path = "/tmp/test.json"
with open(temp_path, 'w') as f:
    json.dump(test, f)

print(f"Schema temporarily saved to {temp_path}")

Use the following code to save it to the file section of lakehouse, where the path uses the File API Path.

# File API path
lakehouse_dir = "/lakehouse/default/Files/"
lakehouse_path = lakehouse_dir + "test.json"

if not os.path.exists(lakehouse_dir):
    os.makedirs(lakehouse_dir)

# Use shutil.copy to copy files to the target location
shutil.copy(temp_path, lakehouse_path)

print(f"Schema saved to {lakehouse_path}")

Finally you can use this code to see if your stored json output is correct.

import json
import pandas as pd

# set Lakehouse path
lakehouse_path = "/lakehouse/default/Files/test.json"

# use pandas to read JSON file
df = pd.read_json(lakehouse_path, orient='records', lines=True)

schema_loaded = json.loads(df.to_json(orient='records'))

print("Loaded schema:")
print(json.dumps(schema_loaded, indent=4))

By using notebook, you'll be able to save the json file in lakehouse's file.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!