topic Re: What's the equivalent of "DECLARE..." in Databricks SQL ? in Data Engineering

What's the equivalent of "DECLARE..." in Databricks SQL ?

wyzer — Tue, 11 Jan 2022 14:50:05 GMT

Hello everyone,

I'm new in Databricks SQL, and I'm comming from SQL Server.

I would like to know what's the equivalent of :

DECLARE @P_Name varchar(50) = 'BackOffice'

It's for use it like this :

CREATE DATABASE @P_Name

Thanks.

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

Anonymous — Wed, 12 Jan 2022 00:52:33 GMT

Hi there, @Salah K.! Welcome to the community and thanks for your question. Let's give the community more time to answer your question. Thanks!

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

wyzer — Wed, 12 Jan 2022 09:26:37 GMT

Hi,

Ok, thanks.

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

BilalAslamDbrx — Wed, 12 Jan 2022 12:49:07 GMT

@Salah K. Databricks SQL does not support T-SQL style variable declarations. However, Databricks SQL _does_ support variables in a different way.

Try this: "

CREATE DATABASE {{ database_name }}

You will see a "widget" where you can pass in the database name

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

wyzer — Wed, 12 Jan 2022 13:31:48 GMT

Thank you for your answer.

How can I use it like this please :

CREATE DATABASE CONCAT({{ database_name }}, 'BackOIffice')

Because it doesn't work.

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

BilalAslamDbrx — Wed, 12 Jan 2022 13:36:31 GMT

@Salah K. that won't work because these variables are evaluated outside of SQL.

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

wyzer — Wed, 12 Jan 2022 13:52:47 GMT

Ok.

So in Databricks SQL when can't create Stored Procedure for example ?

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

BilalAslamDbrx — Wed, 12 Jan 2022 14:06:05 GMT

No, we do support UDFs (our name for sprocs). For example:

CREATE OR REPLACE FUNCTION generate_db_name(name STRING COMMENT 'Database name')
  RETURNS STRING
  COMMENT 'Creates a database name postfixed by BackOffice'
  CONTAINS SQL DETERMINISTIC
  RETURN name || 'BackOffice'

However, this fails as it appears CREATE DATABASE only accepts a string literal.

create database generate_db_name('Testing');

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

wyzer — Wed, 12 Jan 2022 14:20:20 GMT

Ok, thank you very for your time.

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

Bilal1 — Wed, 19 Jan 2022 10:04:29 GMT

Use python and build up a sql string which can be executed.

e,g

a = 'backoffice'

sql = f'select * from {a}'

print(sql)

spark.sql(sql)

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

wyzer — Mon, 14 Feb 2022 08:47:40 GMT

Thanks

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

sauri — Tue, 31 May 2022 18:00:13 GMT

Hello,

Maybe you are looking that:

%sql

CREATE WIDGET TEXT P_Name DEFAULT 'BackOffice';

CREATE DATABASE getArgument("P_Name ")

https://docs.databricks.com/notebooks/widgets.html#widgets-in-sql

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

Ryan_Chynoweth — Mon, 03 Oct 2022 17:54:21 GMT

Please note that SQL Variables are supported in pure SQL. Here is an example notebook. https://github.com/rchynoweth/DemoContent/blob/main/Misc/SQL_Variables.sql

The syntax would be something like the following:

SET var.database_name = my_database ; 
 
SELECT * FROM ${var.database_name}.my_table;

Note that the "var" can be any string i.e. it can be c.var_name instead.

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

Anonymous — Mon, 03 Oct 2022 22:05:58 GMT

Awesome addition, thanks @Ryan Chynoweth !

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

verargulla — Thu, 23 Mar 2023 01:31:48 GMT

This should be the accepted answer!

Re: What's the equivalent of "DECLARE..." in Databricks SQL ?

Amit_352107 — Thu, 23 Mar 2023 07:04:26 GMT

Hi @Salah K.

you can go through this code block

%python

P_Name = 'BackOffice'

spark.sql(f""" create database {P_name} """)