topic Re: SQL Declare Variable equivalent in databricks in Data Engineering

SQL Declare Variable equivalent in databricks

elgeo — Wed, 19 Oct 2022 11:30:21 GMT

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.

Re: SQL Declare Variable equivalent in databricks

elgeo — Mon, 31 Oct 2022 08:11:48 GMT

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

Re: SQL Declare Variable equivalent in databricks

Soma — Tue, 01 Nov 2022 03:09:45 GMT

@ELENI GEORGOUSI

set LastChangeDate =current_date();

in your sql query

select current_date() as date

Re: SQL Declare Variable equivalent in databricks

elgeo — Tue, 01 Nov 2022 07:38:05 GMT

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

Any other ideas please? Thank you in advance

Re: SQL Declare Variable equivalent in databricks

Soma — Tue, 01 Nov 2022 08:16:13 GMT

Re: SQL Declare Variable equivalent in databricks

elgeo — Tue, 01 Nov 2022 08:19:48 GMT

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.

Re: SQL Declare Variable equivalent in databricks

elgeo — Tue, 01 Nov 2022 08:42:37 GMT

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}'

Re: SQL Declare Variable equivalent in databricks

DeepakMakwana74 — Sat, 05 Nov 2022 04:46:38 GMT

Hi @ELENI GEORGOUSI

In databricks sql it will works in the following manner

SET LastChangeDate =current_date()

Select ${hiveconf:LastChangeDate}

Re: SQL Declare Variable equivalent in databricks

srinitechworld — Sat, 20 May 2023 11:05:26 GMT

hi try to to control the variables

Re: SQL Declare Variable equivalent in databricks

JohnLerch — Mon, 21 Aug 2023 19:53:29 GMT

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.