select statement on notebook in lookup transformation in ADF

Question

select statement on notebook in lookup transformation in ADF

Shambhu Rai 1,411

Hi Expert.

how to use the select statement on notebook to pass parameter in lookup transformation

Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-09-28T10:49:59.97+00:00

@Shambhu Rai We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Your answer

Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-09-28T10:49:59.97+00:00

@Shambhu Rai We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Smaran Thoomu 24,110 Microsoft External Staff Moderator

@Shambhu Rai Welcome to Microsoft Q&A platform and thanks for posting your question here. As I understand you want to pass the output of a Databricks notebook to a lookup activity in an Azure Data Factory pipeline:

In the notebook, call dbutils.notebook.exit("returnValue"). This will return the specified value to the service.
In your ADF pipeline, utilize the following expression to access the output: @{activity('databricks notebook activity name').output.runOutput}. This expression will provide the output of the Databricks notebook activity with the specified name.

If, however, you need to use a select statement as a lookup parameter, you can create the expression by selecting 'Add Dynamic Content' and specifying the select statement there.

Reference: Run a Databricks notebook

I hope this helps! Let me know if you have any further questions.

Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-09-29T13:20:36.34+00:00

Shambhu Rai Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Shambhu Rai 1,411 Reputation points

2023-09-29T16:05:21.35+00:00

sir question was how we can add this in lookup with adf pipline ..Plese read carefully
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-10-02T18:08:48.1833333+00:00
@Shambhu Rai I apologize for the confusion. To add a select statement on a notebook to a lookup transformation in an Azure Data Factory pipeline, you can follow these steps:

In the lookup activity, set the "source" property to the output of your notebook. Here's an example of how you can do this:

"source": { "type": "Notebook", "notebookPath": "/path/to/your/notebook", "parameters": { "param1": "@pipeline().parameters.param1" } }

In the lookup activity, set the "dataset" property to the dataset that contains the data that you want to look up.

In the lookup activity, set the "lookup" property to the select statement that you want to use to look up the data. Here's an example of how you can do this:
"lookup": "select column1, column2 from @{activity('NotebookActivityName').output.firstRow}"

Finally, you can use the output of your lookup activity in your pipeline by referencing it in subsequent activities.

I hope this helps! Kindly accept the answer by clicking on Accept answer button. Thankyou
Shambhu Rai 1,411 Reputation points

2023-10-02T20:03:07.2633333+00:00

Hi expert,

the 2nd select is coming from azure sql db(1st lookup)

select count(1) As CurrentChangeTrackingVersion

from

CHANGETABLE(CHANGES ['@{activity('Notebook1').output.runOutput}'],

@{activity('LookupLastTrackingVersionActivity').output.runOutput}) a

its giving 2 individual output instead of 1
Shambhu Rai 1,411 Reputation points

2023-10-03T09:10:22.4266667+00:00

suggestion please
Shambhu Rai 1,411 Reputation points

2023-10-03T12:12:52.3133333+00:00

suggestion pls
Shambhu Rai 1,411 Reputation points

2023-10-04T09:16:35.6533333+00:00

suggestion pls
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-10-04T12:54:13.9133333+00:00
Hi @Shambhu Rai
Thank you for providing additional information about your query. Based on your response, it seems that you are trying to combine the results of two SELECT statements that retrieve data from an Azure SQL Database and a notebook, respectively.
To combine the results of these two SELECT statements, you can use the UNION ALL operator. However, you need to ensure that the data types of the two columns match, as the UNION ALL operator requires that the data types of the columns in the two result sets match.
Here's an example query:

SELECT CAST('@{activity('LookupLastChangeTrackingVersionActivity1').output.firstRow.SYS_CHANGE_VERSION}' AS STRING) AS output1 UNION ALL SELECT '@{activity('Notebook2').output.runOutput}' AS output2

This query retrieves data from the two sources and combines them using the UNION ALL operator.
Note that the UNION ALL operator returns all rows from both tables, including duplicates. If you want to remove duplicates from the result set, you can use the UNION operator instead.
I hope this helps!

Share via

select statement on notebook in lookup transformation in ADF

1 answer

Your answer