Setting global constants using Spark SQL?

I have a notebook global_constant in Synapse and I have defined a global constant using pyspark constant= 'test':

Now I created another notebook and I want to refer to the constant using SparkSQL with "SET". I have done %run global_constant so far but how can I refer to the constant variable?

Solution

AFAIK, in there are no Global constants in Azure synapse notebooks. The variables which declared in cells can be global variables and if you want to get the variable of one notebook from another notebook without using %run, you can try this workaround.

First create a temporary view with the value of the constant in global_constant notebook.

constant='Rakesh'
spark.sql("create or replace view con_view AS SELECT '{}' as const".format(constant))

enter image description here

Then, access this value in another like below.

spark.sql("set constant = (SELECT const FROM con_view)")
spark.sql("select ${constant} as con").show()

enter image description here