topic Re: Databricks SQL format_string in LOCATION in Data Engineering

Databricks SQL format_string in LOCATION

kellybe — Tue, 04 Jul 2023 08:30:03 GMT

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

Re: Databricks SQL format_string in LOCATION

Aviral-Bhardwaj — Tue, 04 Jul 2023 08:38:08 GMT

Why you are using secrets there ??

Re: Databricks SQL format_string in LOCATION

kellybe — Tue, 04 Jul 2023 08:39:52 GMT

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

Re: Databricks SQL format_string in LOCATION

VaibB — Tue, 04 Jul 2023 11:53:56 GMT

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

Re: Databricks SQL format_string in LOCATION

kellybe — Tue, 04 Jul 2023 12:00:55 GMT

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

Re: Databricks SQL format_string in LOCATION

pcbzmani — Tue, 04 Jul 2023 13:55:09 GMT

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

Re: Databricks SQL format_string in LOCATION

kellybe — Tue, 04 Jul 2023 13:59:01 GMT

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