How to use variables in SQL queries?

Question

Since in SQL Server ,we can declare variables like declare @sparksql='<any query/value/string>' but in spark sql what alternative can be used . So that we don't need to hard code any values/query/strings.

Solution 1

There is support for the variables substitution in the Spark, at least from version of the 2.1.x. It's controlled by the configuration option spark.sql.variable.substitute - in 3.0.x it's set to true by default (you can check it by executing SET spark.sql.variable.substitute).

With that option set to true, you can set variable to specific value with SET myVar=123, and then use it using the ${varName} syntax, like: select ${myVar}...

On Databricks, parser also recognizes that syntax, and creates a field to populate value, although it would be easier to use widgets from SQL as described in documentation

P.S. According to the code, besides variables themselves, it also supports getting the data from environment variables & from the Java system properties, like this:

select '${env:PATH}';
select '${system:java.home}';

P.S. This answer is about using variables defined in Spark SQL itself. If you're looking about using variables defined in Python/Scala in Spark SQL, then please refer to this answer.

Solution 2

If you are using a Databricks Notebook then one easy way is to use Scala or Python to declare the variable and execute the SQL statement.

Here's a simple example in Scala:

val x = 1

val df = spark.sql(s"select * from t where col1 = $x")

df.show()

Comments

[Shrince]

Yup thanks for it,I am using databricks but mine one is purely based on spark sql(only sql queries are been used),so any alternative with that would be great.i have tried with widget option but there moreover i found manual intervention.

[Alex Ott]

@Shrince see my answer on that topic.

[Climbs_lika_Spyder]

Here is the documentation docs.databricks.com/notebooks/widgets.html#widgets-in-sql