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: 

Databricks SQL format_string in LOCATION

kellybe
New Contributor II

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'))
---------^^^

  

6 REPLIES 6

Aviral-Bhardwaj
Esteemed Contributor III

Why you are using secrets there ??

AviralBhardwaj

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

VaibB
Contributor

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

 

kellybe
New Contributor II

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
New Contributor II

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
New Contributor II

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'))
---------^^^

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group