cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Declare Variable equivalent in databricks

elgeo
Valued Contributor II

Hello. What would be the equivalent of the below in databricks?

DECLARE @LastChangeDate as date

SET @LastChangeDate = GetDate()

I already tried the below and worked. However I need to know how set a sql variable dynamically

SET da.dbname = test;

SELECT "${da.dbname}" AS db_name

Thank you in advance.

1 ACCEPTED SOLUTION

Accepted Solutions

DeepakMakwana74
New Contributor III

Hi @ELENI GEORGOUSI​ 

In databricks sql it will works in the following manner

SET LastChangeDate =current_date()

​Select ${hiveconf:LastChangeDate}

View solution in original post

9 REPLIES 9

elgeo
Valued Contributor II

Hello. Is the above something that can be done in databricks sql?

Soma
Valued Contributor

@ELENI GEORGOUSI​ 

set LastChangeDate =current_date();

or

in your sql query

select current_date() as date

elgeo
Valued Contributor II

Hello @somanath Sankaran​. Thank you for your reply. Neither of the above worked. Please find attached the related screenshots:

imageimage 

Any other ideas please? Thank you in advance

Soma
Valued Contributor

image

elgeo
Valued Contributor II

Hello @somanath Sankaran​. I need an equivalent to Declare variable. In the above example you attached, I cannot refer to this variable as for example ${dt} throughout the notebook.

elgeo
Valued Contributor II

A workaround is the below:

%python

result = spark.sql("""select date_format(current_date(),"yyyy-MM-dd") as dt""")

spark.conf.set('da.businessbr',result.collect()[0][0])

select '${da.businessbr}'

image.png

DeepakMakwana74
New Contributor III

Hi @ELENI GEORGOUSI​ 

In databricks sql it will works in the following manner

SET LastChangeDate =current_date()

​Select ${hiveconf:LastChangeDate}

Hello - thanks for this suggestion.  However, I am getting this error in databricks: 

"Error running query: [_LEGACY_ERROR_TEMP_DBR_0222] org.apache.spark.sql.AnalysisException: Configuration LastChangeDate is not available."

I've tried many different suggestions for this and can't seem to find a way to do this simple thing.

 

srinitechworld
New Contributor II

hi try to to control the variables

image.png 

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!