Databricks SQL format_string in LOCATION

[kellybe]

Hi,

I'm trying to assign a location to a new database in Databricks SQL. Normally I'd do this in Python since we specify storage account names from secret scopes, however I'm attempting to do all of this from a SQL warehouse. When doing this I seem to get a PARSE_SYNTAX_ERROR, when I'm not sure what's wrong with the query:

CREATE DATABASE IF NOT EXISTS new_database
LOCATION format_string('abfss://container-name@%s.dfs.core.windows.net/', SECRET('secret-scope', 'storage-account-name'));

Has anyone seen similar errors to this one? It appears to have an issue with the use of format_string as a location.

[PARSE_SYNTAX_ERROR] Syntax error at or near 'format_string'.line 2, pos 9

== SQL ==
CREATE DATABASE IF NOT EXISTS new_database
LOCATION format_string('abfss://container-name@%s.dfs.core.windows.net/', SECRET('secret-scope', 'storage-account-name'))
---------^^^

[Aviral-Bhardwaj]

Why you are using secrets there ??

[kellybe]

We store environment variables as secrets to maintain consistency across our various environments.

[VaibB]

What does the below piece do?
SECRET('secret-scope', 'storage-account-name')

[kellybe]

The SECRET function takes a secret scope and a key and returns the specified secret as a string.

Here are the docs for it: https://docs.databricks.com/sql/language-manual/functions/secret.html

[pcbzmani]

Hello @kellybe ,

CREATE DATABASE IF NOT EXISTS new_database
LOCATION format_string('abfss://container-name@%s.dfs.core.windows.net/', select SECRET('secret-scope', 'storage-account-name'));

 Add Select before secert 

[kellybe]

Hi @pcbzmani ,

Unfortunately that doesn't seem to work. The issue appears to be with the use of format_string

[PARSE_SYNTAX_ERROR] Syntax error at or near 'format_string'.line 2, pos 9

== SQL ==
CREATE DATABASE IF NOT EXISTS new_database
LOCATION format_string('abfss://container-name@%s.dfs.core.windows.net/', SELECT SECRET('secret-scope', 'storage-account-name'))
---------^^^