You can use {} in spark.sql() of pyspark/scala instead of making a sql cell using %sql.

This will result in a dataframe. If you want you can create a view on top of this using createOrReplaceTempView()

Below is an example to use a variable:-

# A variable

var = "Hello World"

# Using f in pyspark spark.sql.

spark.sql(f""" SELECT '{var}' AS Message """)

# Using format

spark.sql(""" SELECT '{}' AS Message """.format(var))