Spark SQL - How do i set a variable within the query, to re-use throughout?

Question

I'm trying to convert a query from T-SQL to Spark's SQL. I've got 99% of the way, but we've made strong use of the DECLARE statement in T-SQL.

I can't seem to find an alternative in Spark SQL that behaves the same - that is, allows me to declare variables in the query itself, to be re-used in that query.

Example in T-SQL:

DECLARE @varA int
SET @varA = '4'
SELECT * FROM tblName where id = @varA;

How do i do the declaration of such a variable in Spark SQL? (I don't want to use string interpolation, unless necessary)

Answer 1

You can try this:

sqlContext.sql("set id_value = 3")
sqlContext.sql("select * from country where id = ${id_value}").show()