topic Re: Can SQL task pass its outputs to ForEach task? in Data Engineering

Can SQL task pass its outputs to ForEach task?

Fikrat — Fri, 20 Dec 2024 22:24:43 GMT

Hi there,
If I understood correctly, Roland said output SQL task can be used as input to ForEach task in Workflows. I tried that and used the expression sqlTaskName.output.rows, but Databricks rejected that expression. Anyone know how to do that?

Re: Can SQL task pass its outputs to ForEach task?

Walter_C — Sat, 21 Dec 2024 02:27:10 GMT

Can you confirm if this are the steps being followed:

Create the SQL Task: Ensure your SQL task is correctly set up and produces the desired output. For example:
```
SELECT customer_name, market FROM example_customers;
```
Reference the SQL Task Output in ForEach Task:
- Use the correct syntax to reference the rows output. The expression should be in the format {{ tasks.sqlTaskName.output.rows }}.
- Ensure that the SQL task name (sqlTaskName) matches the task key you have defined in your workflow.

Configure the ForEach Task:

Set the inputs field of the ForEach task to {{ tasks.sqlTaskName.output.rows }}.

Define the nested task within the ForEach task to process each row. For example:

{
  "task_key": "process_customers_iteration",
  "parameters": {
    "customer_name": "{{ input.customer_name }}",
    "market": "{{ input.market }}"
  },
  "sql_task": {
    "file": {
      "path": "/path/to/your/sql/file.sql"
    }
  }
}

Re: Can SQL task pass its outputs to ForEach task?

Fikrat — Sat, 21 Dec 2024 21:05:39 GMT

Hi there,

Thanks for looking at this. I followed your instructions to the letter, but Databricks refused to accept that dynamic expression for the input of foreach task, see the screenshot.And output.rows expression is not among dynamic expressions offered by 'Inputs' box.Also, I can't even save that setting for ForEach task, let alone to execute it. Please advise.

Re: Can SQL task pass its outputs to ForEach task?

Walter_C — Mon, 23 Dec 2024 15:24:06 GMT

May I know from which blog or webinar did you get the information provided in the original post?

Re: Can SQL task pass its outputs to ForEach task?

Fikrat — Mon, 23 Dec 2024 16:35:49 GMT

I got it from this:
What's New in Databricks Workflows - with Live Demos!
He's demoing haw sql task output can be consumed at around 9-th min

Re: Can SQL task pass its outputs to ForEach task?

Walter_C — Mon, 23 Dec 2024 18:29:33 GMT

I am asking internally on this, will get back to you once I have more details

Re: Can SQL task pass its outputs to ForEach task?

Walter_C — Tue, 24 Dec 2024 17:59:48 GMT

Our internal teams has confirmed that this is currently not working on your side as this feature is currently in Private preview we will need to wait for some time until it is fully released.