Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

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.

data engineering

5 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    2 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        ibarrau
        Super User
        2 years ago

        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,