Output of lookup activity in blob

Question

Hi,

I want to run a sql query(snowflake db) using lookup activity and use that output to copy to a csv file in azure storage account. I cannt use copy activity or data flow, because snowflake,adf, storage account has some limitations while using managed identity linked services. our linked services are configured to use system assigned managed ids.

To overcome this problem, I am trying to find a solution where I can read the output of lookup and use that for writing to CSV file. If I run lookup activity, it works fine, the query executes fine and output contains the rows.

Please help on this.

Answer 1

Start with adding a Lookup activity to your pipeline where you configure it to run your SQL query against Snowflake.

Then add a Set Variable activity, set the type to Array and configure the value to be the output of the Lookup activity.

Example of your Expression:

@activity('LookupActivityName').output.value

Then you can create Azure Function or Logic App:

• Write a function that accepts an HTTP POST request with the data payload.
• Inside the function, process the payload and write it to a CSV file in the Azure Storage Account.

import azure.functions as func

import csv

from azure.storage.blob import BlobServiceClient

def main(req: func.HttpRequest) -> func.HttpResponse:


logging.info('Python HTTP trigger function processed a request.')

data = req.get_json()

# Process data and write to CSV

csv_content = "column1,column2\n"

for item in data:

    csv_content += f"{item['column1']},{item['column2']}\n"

# Save CSV to Azure Blob Storage

connection_string = "YourAzureStorageConnectionString"

blob_service_client = BlobServiceClient.from_connection_string(connection_string)

container_name = "your-container-name"

blob_name = "output.csv"

blob_client = blob_service_client.get_blob_client(container=container_name, blob=blob_name)

blob_client.upload_blob(csv_content, overwrite=True)

return func.HttpResponse(f"CSV file created successfully.", status_code=200)
```Or  you can create a Logic App with an HTTP request trigger :

- Add actions to process the input data and create a CSV file.

- Save the CSV file to your Azure Storage Account.

Now comes the part to configure Web Activity in ADF:

- Add a Web Activity to your pipeline.

- Set the URL to the endpoint of your Azure Function or Logic App.

- Configure the method to POST.

- Set the body to pass the data stored in the variable.

```json

{

    "data": @variables('YourVariableName')

}