07-04-2023 01:30 AM
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'))
---------^^^
07-04-2023 01:38 AM
Why you are using secrets there ??
07-04-2023 01:39 AM
We store environment variables as secrets to maintain consistency across our various environments.
07-04-2023 04:53 AM
What does the below piece do?
SECRET('secret-scope', 'storage-account-name')
07-04-2023 05:00 AM - edited 07-04-2023 05:00 AM
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
07-04-2023 06:55 AM
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
07-04-2023 06:59 AM
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'))
---------^^^
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