Failed to create table and schema

[felipe_regis]

hi folks

I failed to run a simple create table into a spark notebook using python and then in SQL as well.

It follows my code:

%%sql
CREATE TABLE IF NOT EXISTS sales.sales_silver (
    SalesOrderNumber string
    , SalesOrderLineNumber int
    , OrderDate date
    , CustomerName string
    , Email string
    , Item string
    , Quantity int
    , UnityPrice float
    , Tax float
    , FileName string
    , IsFlagged boolean
    , CustomerID bigint
    , ItemID bigint
    , CreatedTS date
    , ModifiedTS date
) USING delta;

It follows the error:

[SCHEMA_NOT_FOUND] The schema `sales` 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.

It follows picture from Lab I'm doing with same code working:

Does anybody knows why and can explain?

Thanks in advance

[rahuldwh · Accepted solution]

@felipe_regis , Yes we can't create schema in the lakehouse. In the code given in the lab the sales denotes lakehouse name not the schema. So if you haven't created the sales lakehouse your code will show this error.

[frithjof_v]

Thank you for pointing this out @rahuldwh 

I didn't realize we can use this kind of notation [lakehouseName].[tableName] when working with Lakehouse tables in a Notebook. That is nice!

This is how I understand it:

In the Notebook, you can have a Lakehouse as the Default Lakehouse for the Notebook. The Default Lakehouse for the Notebook is the Lakehouse which has the pin icon

next to the Lakehouse name.

When the Lakehouse is the Default Lakehouse for the Notebook, we can write simpler code.
If we want to create a table named TestTable in the "DE_LH_100_RandomData" lakehouse, then we can use the simple code below. We only need to use the table name, we don't need to mention the lakehouse name. This is because the pin icon

 is next to the Lakehouse name, which makes it the Default Lakehouse.

%%sql
CREATE TABLE TestTable (SalesOrderNumber string, SalesOrderLineNumber int);

We could also use the [lakehouseName.tableName] notation if we want:

%%sql
CREATE TABLE DE_LH_100_RandomData.TestTable (SalesOrderNumber string, SalesOrderLineNumber int);

What if we want to use this same notebook to create a table in another Lakehouse? I think the other Lakehouse must also be in the same Fabric workspace as the Notebook's current Default Lakehouse.
Let's say we have another lakehouse called "DE_LH_100_WideWorldImporters".
If we want to create a table in the "DE_LH_100_WideWorldImporters" lakehouse from the same notebook, then we must use the [lakehouseName].[tableName] notation because this is not the default lakehouse for the notebook:

%%sql
CREATE TABLE DE_LH_100_WideWorldImporters.TestTable (SalesOrderNumber string, SalesOrderLineNumber int);

Or, in the notebook we can move the pin icon to set the "DE_LH_100_WideWorldImporters" lakehouse as the Default Lakehouse. Then we can write simpler code to create the table in the "DE_LH_100_WideWorldImporters lakehouse".

The simple code below will now create the TestTable in the "DE_LH_100_WideWorldImporters" lakehouse because the pin icon 

is now next to that lakehouse's name.

%%sql
CREATE TABLE TestTable (SalesOrderNumber string, SalesOrderLineNumber int);

[frithjof_v]

It also made me think... 

Switching between Default Lakehouses in a Notebook could lead to undesirable consequenses. 

However as long as we are aware of what code we have in the Notebook, and aware of what is our Notebook's current Default Lakehouse, I think we should be good. 

If there is code in the Notebook which uses the simple [tableName] notation, then all the code with the simple [tableName] notation will be executed on the current Default Lakehouse, if I understand this correctly. 

Furthermore, I believe the [lakehouseName].[tableName] notation only applies to lakehouses in the same Fabric Workspace as the Notebook's Default Lakehouse.

The Notebook's Default Lakehouse doesn't need to be in the same Fabric workspace as the Notebook itself.
We can select a Default Lakehouse from another Fabric workspace.

So if we are using [lakehouseName].[tableName] notation, and then switching Default Lakehouse to a Lakehouse which is in another Fabric workspace, then the code using [lakehouseName].[tableName] notation will only work if there is a Lakehouse with the same name [lakehouseName] in that Fabric workspace.

If I understand this correctly.

I did some testing and this seems to be the case so far.

I think we also need to remember that the pin icon is the indicator of the Default Lakehouse. Even if a Lakehouse is visible in the explorer menu in the Notebook, it doesn't mean it is the Default Lakehouse. It is the Lakehouse with the pin icon which is the Notebook's Default Lakehouse.

In the case below, "DE_LH_100_RandomData" is not the Default Lakehouse, even if it is visible in the explorer pane in the Notebook. We can also see that the pin icon is not there.
Execution of the code cell will not create a table in the "DE_LH_100_RandomData" lakehouse.
Instead, it will create a table in another lakehouse (the Default Lakehouse).

When clicking the bi-directional arrows, we can see that the Notebook's Default Lakehouse in this case is the "DummyLakehouse" in a workspace called "Fabric 2", because the pin icon is placed next to that Lakehouse.
So the current Notebook code would create a table in the "DummyLakehouse" in "Fabric 2" workspace (and not in the visible "DE_LH_100_RandomData" lakehouse in "Fabric" workspace... Which by the way could be a confusing name for a Fabric workspace, sorry about that).

[frithjof_v]

If I remember correctly, only Warehouse support schema, not Lakehouse 

If you try with

CREATE TABLE IF NOT EXISTS sales_silver (SalesOrderNumber string,

SalesOrderLineNumber int);

then it should work, if you are trying to create a Lakehouse delta table from a Notebook

(leave out the "sales." part and I don't think you need the "using delta" part)

[v-nikhilan-msft]

Hi @felipe_regis 

Thanks for using Fabric Community.

Apologies I could not understand the issue properly. Are you trying to say that you are able to create a new table in the Lakehouse using SQL code in the notebook, but getting an error while using python?

[felipe_regis]

Actually, answers and thoughts mentioned were pretty useful. Just to clarify, I was not able to create a table using the following synthax [sales].[table_name]. As "sales" lakehouse didn't exist I got that error, "schema not found or does not exist".

I understood that it's not possible to create schemas when using a Lakehouse but, indeed, it's possible to create a table when referencing a lakehouse name instead. Such as [Lakehouse_name].[table_name].

Thanks anyways

Regards