Error when create an external location using code

[noimeta]

I'm trying to create an external location from notebook, and I got this kind of error

[PARSE_SYNTAX_ERROR] Syntax error at or near 'LOCATION'(line 1, pos 16)
 
== SQL ==
CREATE EXTERNAL LOCATION IF NOT EXISTS test_location URL 's3://test-bronze/db/test_schema/test_table' WITH (STORAGE CREDENTIAL test-credential)
----------------^^^

I tried with DBR10.4, DBR11.1 and DBR11.2, unfortunately, none of them worked.

However, SHOW EXTERNAL LOCATIONS and DESCRIBE EXTERNAL LOCATION *** work just fine.

Also, I can create an external location from the UI without any problem

Anyone knows how to solve this issue?

[-werners-]

Seems to be ok at first sight. Do you run it from Databricks SQL ( the SQL persona)?

[noimeta]

I tried with both Databricks SQL and notebook, but they outputted the same error.

[-werners-]

can you create an external location from that particular location using the UI?

To exclude that being an issue...

[noimeta]

yes, the UI works fine

[-werners-]

Strange. I can't test it myself as I am on Azure.

maybe it is the url you provide? On Azure I also have to define the blob container and storage account:

abfss://<container_name>@<storage_account>.dfs.core.windows.net/<path>

[noimeta]

I just imitated the code from an example here https://docs.databricks.com/data-governance/unity-catalog/manage-external-locations-and-credentials.... and it's also the same path when I inputted to the UI. Now, I have no idea why it doesn't work.

[noimeta]

Hi @Kaniz Fatma​ ,

Thank you very much for your response.

I've been trying to submit a case, but it always asks me to sign in.

After I signed in with my Databricks workspace account, it jumped to the community home page. So, I came to this post and clicked your link again, then it asked me to log in again.

[constantins]

I am facing the same problem. Was this resolved? If so, what was the solution?

[noimeta]

unfortunately, not yet

[mguentazdir]

Hi, is there a new status here? Is there a workaround which does not include the GUI? I am using Databricks on Azure and i am running into the same issue.

Thx.

[noimeta]

In my case, yes, the problem is solved. Please check the best answer.

[Pat · Accepted solution]

It seems like error is due to '-' in the storage credential name, you need to use backticks `some-name`:

CREATE EXTERNAL LOCATION IF NOT EXISTS test_location URL 's3://test-bronze/db/test_schema/test_table' WITH (STORAGE CREDENTIAL `test-credential`)

thanks,

Patryk.

[noimeta]

It works! Thank you so much!!

There's no back tick in the example, so I didn't think about it at all.

[Lokeshv]

Hey everyone,

I'm facing an issue with retrieving data from a volume or table that contains a string with a symbol, for example, 'databricks+'. Whenever I try to retrieve this data, I encounter a syntax error. Can anyone help me resolve this issue?