Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Sperling
Helper II
Helper II

Pass widget parameter to T-SQL query in Fabric

Lets say my first cell in my Fabric notebook is:

%%pyspark
import ipywidgets as widgets
ADF_PipelineRunId= widgets.Text(description = "ADF_PipelineRunId", value = "00000000-0000-0000-0000-000000000000")
display(ADF_PipelineRunId)
 
I've toggled the cell to be a parameter cell.
How do I then select the input in a T-SQL cell?
 
In Databricks the following works, but I can't find a workaround in Fabric:
SELECT "${ADF_PipelineRunId}" AS ADF_PipelineRunId
1 ACCEPTED SOLUTION
v-jingzhan-msft
Community Support
Community Support

Hi @Sperling 

 

You can try a two-step approach:

  1. Capture the Widget Value in PySpark: First, ensure you capture the value from the widget in a PySpark cell. You've already done this part.

    import ipywidgets as widgets
    ADF_PipelineRunId = widgets.Text(description="ADF_PipelineRunId", value="00000000-0000-0000-0000-000000000000")
    display(ADF_PipelineRunId)
    
  2. Pass the Value to a T-SQL Query: Next, you'll need to use PySpark to dynamically construct your T-SQL query string with the widget's value included. This can be done by creating a query string in PySpark and then executing it using the Spark SQL context.

    Here's how you can do it:

    # Assuming ADF_PipelineRunId.value holds the value you want to pass to your T-SQL query
    query = f"""
    SELECT '{ADF_PipelineRunId.value}' AS ADF_PipelineRunId
    """
    spark.sql(query)
    

    This approach involves constructing the SQL query as a string in PySpark, where you can dynamically insert the widget's value. Then, you execute this query using Spark SQL (spark.sql). This way, you can pass parameters from PySpark widgets to your SQL context.

 

If this doesn't work, you can also seek help at the Synapse forum: Get Help with Synapse

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

View solution in original post

2 REPLIES 2
v-jingzhan-msft
Community Support
Community Support

Hi @Sperling 

 

You can try a two-step approach:

  1. Capture the Widget Value in PySpark: First, ensure you capture the value from the widget in a PySpark cell. You've already done this part.

    import ipywidgets as widgets
    ADF_PipelineRunId = widgets.Text(description="ADF_PipelineRunId", value="00000000-0000-0000-0000-000000000000")
    display(ADF_PipelineRunId)
    
  2. Pass the Value to a T-SQL Query: Next, you'll need to use PySpark to dynamically construct your T-SQL query string with the widget's value included. This can be done by creating a query string in PySpark and then executing it using the Spark SQL context.

    Here's how you can do it:

    # Assuming ADF_PipelineRunId.value holds the value you want to pass to your T-SQL query
    query = f"""
    SELECT '{ADF_PipelineRunId.value}' AS ADF_PipelineRunId
    """
    spark.sql(query)
    

    This approach involves constructing the SQL query as a string in PySpark, where you can dynamically insert the widget's value. Then, you execute this query using Spark SQL (spark.sql). This way, you can pass parameters from PySpark widgets to your SQL context.

 

If this doesn't work, you can also seek help at the Synapse forum: Get Help with Synapse

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Hi @v-jingzhan-msft 

 

I found a solution with your approach. I'm combining it with data from another source, but I just made my initial query in python and added the parameters, and then continued with T-SQL using the spark.sql query.

Thanks for the help!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.