topic Not able to create external table in a schema under a Catalog. in Data Engineering

Not able to create external table in a schema under a Catalog.

Meshynix — Thu, 28 Mar 2024 06:22:46 GMT

Problem Statement

Cluster 1 (Shared Cluster) is not able to read the file location at "dbfs:/mnt/landingzone/landingzonecontainer/Inbound/" and hence we are not able to create an external table in a schema inside Enterprise Catalog.

Cluster 2 (No Isolation Shared) is able to read the file location at "dbfs:/mnt/landingzone/landingzonecontainer/Inbound/" and we are able to create an external table but the external table is getting created in default hive metastore catalog and not in the desired Enterprise Catalog.

landingzone is an ADLS Gen2 mounted to Databricks Workspace through external location and storage credentials.

Re: Not able to create external table in a schema under a Catalog.

Meshynix — Thu, 28 Mar 2024 12:43:59 GMT

Hi @Retired_mod Wondering if you can please advise a solution. Thanks heaps in advance.

Re: Not able to create external table in a schema under a Catalog.

YuliyanBogdanov — Thu, 28 Mar 2024 14:41:23 GMT

Hi @Meshynix,

Can you provide the code snippet you execute to create your tables, this would give us a better insight for both use cases. Also can you provide the error that is being returned in the first use case. This would help a lot.

Re: Not able to create external table in a schema under a Catalog.

Meshynix — Thu, 28 Mar 2024 15:45:14 GMT

%sql
use catalog enterprise_catalog;
use schema bronze;
create table bronze_hmi
(sl_no string ,
date   string ,
time   string ,
auger_motor_rpm decimal(22,3) ,
auger_motor_rpm_act decimal(22,3) ,
auger_curr decimal(22,3) ,
auger_torque   decimal(22,3) ,
auger_on_time decimal(22,3) ,
auger_off_time decimal(22,3) ,
blower_1_rpm   decimal(22,3) ,
blower_2_rpm   decimal(22,3) ,
blower_3_rpm   decimal(22,3) ,
blower_4_rpm   decimal(22,3) ,
blower_5_rpm   decimal(22,3) ,
blower_6_rpm   decimal(22,3) ,
temp1 decimal(22,3) ,
temp2 decimal(22,3) ,
temp3 decimal(22,3) ,
temp4 decimal(22,3) ,
temp5 decimal(22,3) ,
temp6 decimal(22,3) ,
total_power decimal(22,3) ,
system_current decimal(22,3) ,
cooling_pump_on_time   decimal(22,3) ,
cooling_pump_off_time decimal(22,3) ,
input_load_cell decimal(22,3) ,
output_load_cell   decimal(22,3) ,
misc_col   string)
    USING CSV
    OPTIONS (header "true", inferSchema "true")
    LOCATION "dbfs:/mnt/landingzone/landingzonecontainer/Inbound/hot_test_data/hmi/";

Error Message

[UC_FILE_SCHEME_FOR_TABLE_CREATION_NOT_SUPPORTED] Creating table in Unity Catalog with file scheme dbfs is not supported.
Instead, please create a federated data source connection using the CREATE CONNECTION command for the same table provider, then create a catalog based on the connection with a CREATE FOREIGN CATALOG command to reference the tables therein. SQLSTATE: 0AKUC

Re: Not able to create external table in a schema under a Catalog.

YuliyanBogdanov — Fri, 29 Mar 2024 06:29:11 GMT

Seems like the answer is in the error. Can you try and follow this part of documentation and confirm if you have storage credential, external location, external table set up correctly:

https://docs.databricks.com/en/sql/language-manual/sql-ref-external-tables.html

Re: Not able to create external table in a schema under a Catalog.

Meshynix — Sat, 30 Mar 2024 10:04:31 GMT

It started working after we gave the exact external location path like abfss:// of the landing zone mounted external location and we were able to create an external table in the managed external mounted location using shared cluster in the desired catalog and schema.

Re: Not able to create external table in a schema under a Catalog.

Avi_Bricks — Wed, 15 May 2024 22:08:10 GMT

External table creation failing with error :-

UnityCatalogServiceException:[RequestId=**** ErrorClass=INVALID_PARAMETER_VALUE] Unsupported path operation PATH_CREATE_TABLE on volume.

Able to access and create files on external location.