Declare a constant value in a Databricks noteboook to be used in futher SQL code

Thomas_Lehmann — Fri, 20 May 2022 12:07:19 GMT

I'm new to databricks and try to migrate some former SQL code used so far, in a DB notebook.

I have some constants declared and I can't find the right way to similarly declare those in my notebook.

I tried

%sql
DECLARE label_language CONSTANT VARCHAR(2) := 'FR'

but it is not 'understood' by BD SQL

so I tried

label_language='FR'

which creates the constant and its value.

But when I want to use the value of this constant in my SQL code .... or it fails, or it returns 0 records in

%sql
 
CREATE OR REPLACE TEMPORARY VIEW label_sku_table
AS
	SELECT plt.unv_num_univers,
		plt.fam_num_family,
		ltf.lat_long_label AS fam_name_family,
        plt.dsm_code,
		plt.sku_date_end
	FROM pre_label_sku_table plt
	INNER JOIN datalake_cds.d_label_translation ltf ON plt.lab_idr_label_fam = ltf.lab_idr_label
	WHERE ltf.lan_code_langue_lan = 'label_language'
		AND	plt.ranking = 1 
	GROUP BY plt.unv_num_univers,
		   plt.fam_num_family,
		   ltf.lat_long_label,
           plt.dsm_code,
		   plt.sku_date_end;

I tried this syntax as well :

WHERE ltf.lan_code_langue_lan = '{{label_language}}'

But again ... no resulting content. As if the SQL code did not recognize the [label_language] as a variable to which the value 'FR' has been assigned.

Thank you in advance for any relevant support.

Re: Declare a constant value in a Databricks noteboook to be used in futher SQL code

Hubert-Dudek — Sat, 21 May 2022 10:25:37 GMT

@Thomas Lehmann , SQL widgets are a way to go in Databricks https://docs.databricks.com/notebooks/widgets.html

Re: Declare a constant value in a Databricks noteboook to be used in futher SQL code

Thomas_Lehmann — Mon, 23 May 2022 07:38:13 GMT

Thank you @Hubert Dudek , gave it a try and it does the job, both in SQL and in Python btw. When the time will come to industrialize this, I'll have to figure out how to create/use/deal with some configuration files (json, yaml or so). But for exploratory purpose, widgets are enough thanks again

topic Re: Declare a constant value in a Databricks noteboook to be used in futher SQL code in Data Engineering

Declare a constant value in a Databricks noteboook to be used in futher SQL code

Re: Declare a constant value in a Databricks noteboook to be used in futher SQL code

Re: Declare a constant value in a Databricks noteboook to be used in futher SQL code