Forum Discussion

Ka13's avatar
Ka13
Helper II
7 months ago
Solved

Fabric Notebook ExitValue how to select specific values

Hi,  I had a question in the Fabric Notebook. In Fabric Notebook , From the ExitValue below output in Notebook , how to select specific value , like only schema and table and need to select va...
data engineering
notebook
  • pallavi_r's avatar
    pallavi_r
    6 months ago

    Hi Ka13 ,

    Yes we can read this query in a copy activity. First convert this select query to json 

    import json
    from notebookutils import notebook
    nb_result = {
        "select_query": select_query
    }
    notebook.exit(json.dumps(nb_result))

    Then in pipeline add notebook activity and connect to copy data. In the dynamic content of copy data, add this json expression.

    @json(activity('Notebook1').output.exitValue).select_query

    This works.

    If this post helps, please accept this as a solution. Appreciate your kudos.

    Thanks,

    Pallavi_r

Ka13's avatar
Ka13
Helper II
7 months ago

Thanks Pallavi, in Details if we have one more field of columns like - id,name,city,country. And to extract columns.

can you please suggest.

pallavi_r's avatar
pallavi_r
Super User
7 months ago

Hi Ka13 ,

So here we need frame a dynamic sql. in the previous one we had parsed the string into json. Now we have to retrieve all the column names into string literal in a python list.

details = parsed["parent1"]["Details"]

schema = details["schema"]
table = details["table"]
columns = details["columns"]
getcol = [
    f"CAST({col} AS STRING) AS {col}"
    for col in columns
]

 

Then to create a dynamic sql expression, we need to merge all the elements from the above list into a single comma separated string using ",".join(column) 

select_query = f"""
SELECT
    {", ".join(getcol)}
FROM {schema}.{table}
"""
print(select_query)

 

working code below:

If this post helps, please accept this as a solution. Appreciate your kudos.

 

Thanks,

Pallavi

  • Ka13's avatar
    Ka13
    Helper II
    7 months ago

    Thanks Pallavi, can we pass this select query formed to the next activity ( Copy Activity) ? , to execute the query. Will test the above and let you know if any questions.

    • Ka13's avatar
      Ka13
      Helper II
      7 months ago

      Hi Pallavi, can we pass this formed select query in next Copy Activity?

      • pallavi_r's avatar
        pallavi_r
        Super User
        6 months ago

        Hi Ka13 ,

        Yes we can read this query in a copy activity. First convert this select query to json 

        import json
        from notebookutils import notebook
        nb_result = {
            "select_query": select_query
        }
        notebook.exit(json.dumps(nb_result))

        Then in pipeline add notebook activity and connect to copy data. In the dynamic content of copy data, add this json expression.

        @json(activity('Notebook1').output.exitValue).select_query

        This works.

        If this post helps, please accept this as a solution. Appreciate your kudos.

        Thanks,

        Pallavi_r

  • Ka13's avatar
    Ka13
    Helper II
    6 months ago

    Hi Pallavi, can we pass this formed select query in next Copy Activity?