I have spark code written in scala. Spark Reads meta tables (already in spark as temp table) which stores the SQL to be executed.
Problem I am facing is that we have queries which uses variables (defined in scala code)
I tried different methods but I am not able to substitute variable with value.
var begindate= s"2017-01-01";
var enddate = s"2017-01-05";
Msg.print_info(s"begin processing from ${beginDate} to ${endDate}");
//Reading SQL from MetaData table stored in spark as meta_table (temp table)
val dynamic_read_sql = s"""
select SQL_TEXT
from meta_table""";
val dynamic_sql_query = sqlContext.sql(dynamic_read_sql);
val check_query = dynamic_sql_query.first().getString(0);
Msg.print_info(s"check_query = $check_query");
I am geting sql displayed correctly.
// date is also temp table in spark
select * from date where load_date >= '${begindate}' and load_date <='${enddate}'
Next step is to execute this sql
dynamic_sql_find = sqlContext.sql(check_query);
But it fails to replace '${begindate}' and '${enddate}' which are already defined in code. Hence, returns 0 records.
I tried to store the store in another variable.
val replace_check_query = s"${check_query}"
But, It did not replace the variable.
Can you please help ?
As @radumanolescu correctly said, begindate and enddate are substituted only at compile time. To substitute these at runtime you can replace substrings manually:
val dynamic_sql_query = sqlContext.sql(check_query).replace("${begindate}", begindate).replace("${enddate}", enddate)
The substitution of the variables begindate
/ enddate
into a string (e.g. s"From $begindate to $enddate"
) is set up at compile time, i.e. your expression is translated into something like "From " + begindate + " to " + enddate
. This translation cannot be done at runtime with a random string that is only known at runtime.
The values begindate
/ enddate
are only substituted at runtime, i.e. the expression "From " + begindate + " to " + enddate
is computed at runtime, but the translation from s"..."
to "..." + "..."
is done at compile time.
Other observations:
var
or s"..."
for begin/end. Do this: val begindate = "2017-01-01"
Msg.print_info(dynamic_read_sql)
and let us know the outputIf you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With