topic Re: No parent external location found for <S3-location> | External Table in UnityCatalog in Data Governance

No parent external location found for | External Table in UnityCatalog

Kanib — Thu, 25 Aug 2022 12:24:33 GMT

Hi,

We are trying to adopt the Unity Catalog and are testing out creating an External table with some data from the samples catalog that comes along with creating a meta store. The command we are using is:

CREATE TABLE IF NOT EXISTS my_catalog.quickstart_schema.trips_external
LOCATION 's3://somebucket/path'
AS SELECT * from samples.nyctaxi.trips;

When I am running this command- I keep on getting an error

Error in SQL statement: NoParentExternalLocationForPathException: No parent external location found for path 's3://somebucket/path'

I was following the doc: https://docs.databricks.com/data-governance/unity-catalog/create-tables.html#create-a-table-from-the-files

but I am not getting any information regarding this. Can someone please help me why is this error coming up?

When I try to read some data from this bucket - I am able to do so - meaning, I have the access to the bucket.

Re: No parent external location found for | External Table in UnityCatalog

Kanib — Fri, 26 Aug 2022 06:17:46 GMT

hey @Debayan Mukherjee thanks for the answer.

Yeah, I did not create a pre-defined external location- Does that means, in UC, I can create an external table only using the pre-defined External Location?

Also, if yes, then can I have more than one external Location in the UC?

Re: No parent external location found for | External Table in UnityCatalog

Kanib — Fri, 26 Aug 2022 17:25:02 GMT

Thanks @Debayan Mukherjee for the response and help- I will certainly try out with multiple External Locations (with separate Storage credentials).

Re: No parent external location found for | External Table in UnityCatalog

Kanib — Mon, 29 Aug 2022 11:31:01 GMT

hey @Debayan Mukherjee , while testing out the external table - I also wanted to add partitions using the command:

ALTER TABLE mycatalog.kanib.UnacastUSS3TargetMinimised ADD IF NOT EXISTS PARTITION (`year`='2022', `month`='02', `day`='05')

but this is failing with an error:

`AnalysisException: ALTER TABLE (add partition) is not supported in Unity Catalog`

What is the alternative in the case of UC- if any?

If not, then what is the way to tackle this scenario?

Re: No parent external location found for | External Table in UnityCatalog

Debayan — Thu, 25 Aug 2022 16:28:15 GMT

In Unity catalog you have to pre-define the EXTERNAL LOCATION (and a STORAGE CREDENTIAL) before you use it this way. Please refer below:

https://docs.databricks.com/data-governance/unity-catalog/manage-external-locations-and-credentials.html#manage-external-locations

Re: No parent external location found for | External Table in UnityCatalog

Debayan — Fri, 26 Aug 2022 14:33:28 GMT

@Mohammad Shoaib Yes, each cloud storage path can be associated with only one external location. If you attempt to create a second external location that references the same path, the command fails.

Re: No parent external location found for | External Table in UnityCatalog

Debayan — Fri, 02 Sep 2022 21:11:22 GMT

Hi @Mohammad Shoaib , ALTER TABLE is supported by Unity Catalog, this error is typically due to Network misconfigurations. Could you please check your network configuration and confirm?