Forum Discussion
Failed to create table and schema
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.
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.
Community ChampionThank 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).