topic Re: Error when create an external location using code in Data Engineering

Error when create an external location using code

noimeta — Thu, 29 Sep 2022 08:11:43 GMT

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?

Re: Error when create an external location using code

-werners- — Thu, 29 Sep 2022 08:25:30 GMT

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

Re: Error when create an external location using code

noimeta — Thu, 29 Sep 2022 08:29:56 GMT

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

Re: Error when create an external location using code

-werners- — Thu, 29 Sep 2022 08:35:25 GMT

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

To exclude that being an issue...

Re: Error when create an external location using code

noimeta — Thu, 29 Sep 2022 08:42:21 GMT

yes, the UI works fine

Re: Error when create an external location using code

-werners- — Thu, 29 Sep 2022 09:17:28 GMT

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>

Re: Error when create an external location using code

noimeta — Thu, 29 Sep 2022 11:07:06 GMT

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

Re: Error when create an external location using code

noimeta — Mon, 03 Oct 2022 03:01:54 GMT

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.

Re: Error when create an external location using code

constantins — Tue, 11 Oct 2022 19:48:54 GMT

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

Re: Error when create an external location using code

noimeta — Wed, 12 Oct 2022 03:24:17 GMT

unfortunately, not yet

Re: Error when create an external location using code

mguentazdir — Wed, 09 Nov 2022 20:30:04 GMT

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.

Re: Error when create an external location using code

Pat — Wed, 09 Nov 2022 22:00:54 GMT

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.

Re: Error when create an external location using code

noimeta — Thu, 10 Nov 2022 02:17:28 GMT

It works! Thank you so much!!

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

Re: Error when create an external location using code

noimeta — Thu, 10 Nov 2022 02:19:13 GMT

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

Re: Error when create an external location using code

Lokeshv — Tue, 13 Feb 2024 16:48:41 GMT

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?

Re: Error when create an external location using code

karunakaran_r — Mon, 06 Jan 2025 17:24:57 GMT

I am facing the same issue even with backticks, Is there anything else needs to be fixed in the query?

CREATE EXTERNAL LOCATION examplelocation

URL 'abfss://<container>@<staccount>.dfs.core.windows.net/'

WITH STORAGE CREDENTIAL `sc-test-cred-dev-mi-01`;