The schema `delta` cannot be found error after first execution of delta table creation with path

[kmascent]

Below statement works fine for the first time in Microsoft Fabric, subsequent execution throwing schema not found error.

%%sql

create table if not exists delta.`Files/deltatables/table5`

(

    id integer,

    name string

)using delta

[SCHEMA_NOT_FOUND] The schema `delta` cannot be found. Verify the spelling and correctness of the schema and catalog. If you did not qualify the name with a catalog, verify the current_schema() output, or qualify the name with the correct catalog. To tolerate the error on drop use DROP SCHEMA IF EXISTS.

[ibarrau]

Hi. I haven't seen that way of creating the table before. Are you trying to create a delta table at "Tables" Lakehouse section?

When I'm working in order to move a table from Files to Tables, I usually load it as temp table to make some transformations or join and then store it at Tables. Like this:

CREATE OR REPLACE TEMPORARY VIEW Dim_Shipments_Temp
USING PARQUET
OPTIONS(path "Files/Silver/Shipments/*.parquet", header "true", mode "FAILFAST");

DROP TABLE IF EXISTS Dim_Ship;
CREATE TABLE Dim_Ship USING delta
AS SELECT * FROM Dim_Shipments_Temp 
--[After the "AS" the select would have a transformation of temp queries, as an example it's just SELECT *

I guess if you just want to create the table from a file directly you can write like:

CREATE TABLE products
USING DELTA
LOCATION 'Files/external_products';

I hope that helps

[kmascent]

Thanks for the response.
In my case I'm trying to create the structure for target first and load the data to target  incrementally. 
Creating delta table under tables section ("Tables" Lakehouse section) works fine, but I want to create it using folder structure under Files section within lakehouse 
Here is the link to reference script:
https://docs.delta.io/latest/delta-batch.html#-deltausegeneratedcolumns&language-sql 

[ibarrau]

Hi I haven't seen a way to insert with only sql to files at fabric. If you want to try it make sure you are querying the right schema. Check your schema with:

%%sql
SELECT current_schema()

That will show if "delta" is ok. I would say it might be the name of the lakehouse. 

As an alternative, you can consider pyspark. Have you tried delta api instead?

# Create or replace table with path and add properties
DeltaTable.createOrReplace(spark) \
  .addColumn("id", "INT") \
  .addColumn("firstName", "STRING") \
  .addColumn("middleName", "STRING") \
  .addColumn("lastName", "STRING", comment = "surname") \
  .addColumn("gender", "STRING") \
  .addColumn("birthDate", "TIMESTAMP") \
  .addColumn("ssn", "STRING") \
  .addColumn("salary", "INT") \
  .property("description", "table with people data") \
  .location("/tmp/delta/people10m") \
  .execute()

 I hope that helps,

[v-cboorla-msft]

Hi @kmascent 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

Thanks.

[v-cboorla-msft]

Hi @kmascent 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others .
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread.

Thanks.