How do I substitute the variable value instead of the variable name ?
Problem description:
I initially thought to hide the complexity but looks like you might need it to better understand the problem.
I need to build SQL query dynamically based on Databricks Notebook parameters.
So, I created a config dataframe in the notebook; this notebook has all the possible combinations. Created a function which takes the arguments needed for the dataframe filtering to select the appropriate variable name. These variables are actually the part of select list, or WHERE condition, etc. needed to build the final SQL query.
Config Dataframe:
|code|configName|variableName|
|----|-------------|
|ABC|var1|v_var1_name|
|DEF|var2|v_var2_name|
Pseudo Code:
// 1. Function, getVariableName, takes "code" and "configName" (dataframe column value) as arguments to filter on the dataframe and fetches the return value of variableName. As we can see the variableName changes depending the argument passed to the function.
// 2. example-1:
val v_var1_name = "select col1, col2 "
val v_var1 = getVariableName("ABC", var1)
// Output value:
v_sql = "v_select_list"
// Desired output:
v_sql = "select col1, col2 "
// example-2:
val v_sql_filter = "where a.col1 = 'CA' "
val v_sql = getVariableName("DEF")
// Output value:
v_sql = "v_sql_filter"
// Desired value:
v_sql = "where a.col1 = 'CA' "
// NOTE: I cannot embed the sql string into the dataframe column as it can be very big sometimes. It has to be a variable name only.
// 3. Now, building the final SQL query.
// For #2 example-1,
val sql_final = v_sql + " from table where col1 = 'OR' "
// For #2, example-2,
val sql_final = "select col1 from table" + v_sql_filter
Gaston thanks for your input but as Gael mentioned I used two maps to achieve this. Below is the source code (I changed the original code to achieve this)
Config Dataframe:
|code|configName|variableName|
|----|-------------|
|ABC|var1|var1_name|
|DEF|var2|var2_name|
Pseudo Code:
// 1. Function, getVariableName, takes "code" and "configName" (dataframe column value) as arguments to filter on the dataframe and fetches the return value of variableName. As we can see the variableName changes depending the argument passed to the function.
// 2. example-1:
val var1_name = "select col1, col2 "
val var1 = getVariableName("ABC", "var1")
val var2_name = "where a.col1 = 'CA' "
val var2 = getVariableName("DEF", "var2")
val var_map = Map("var1" -> var1, "var2" -> var2)
val var_sql_map = Map("var1_name" -> var1_name, "var2_name" -> var2_name)
val var1_value = var_map("var1)
val var2_value = var_map("var2)
val sql_string1 = var_sql_map(var1_value)
val sql_string2 = var_sql_map(var2_value)
println(sql_string1)
println(sql_string2)