Forum Discussion
Spark Job Definition vs Notebooks
Hi DennesTorres ,
Yes, you are correct we need to use "spark.catalog", it will list out all the lakehouses present inside the workspace, even if not linked to the notebook.
Code:lakehouses = spark.catalog.listDatabases() lakehouse_list = [] for lakehouse in lakehouses: lakehouse_list.append(lakehouse.name) print(lakehouse_list)
In order get list of tables present inside particular lakehouse, you can refer below -
Code:# Get the list of lakehouses to read tables from. lakehouses = ["gopi_lake_house", "gopi_lakehouse_2"] # Loop through the lakehouses and read all tables from each lakehouse. for lakehouse in lakehouses: tables = spark.sql(f"SHOW TABLES IN {lakehouse}") tables.show()
Note: SHOW TABLES IN - will be working even if the lakehouse is not default. In my case only gopi_lakehouse_2 is selected as default, but I am able to see tables present inside gopi_lake_house and gopi_lakehouse_2.
For Example:
Executed in Fabric Notebooks:
Executed in Spark Job Application:
The above code is working fine both in notebook and spark job application.
Hope this was helpful.Hi,
Using the information provided until this point, I was able to write a code to make the maintenance of all lakehouses in the same workspace.
The Spark Job Definition, on the other way, can be linked to multiple workspaces. One of the workspace is turned into the default workspace while the other workspaces become a configuration.
We can loop through the configurations and use mssparkutils to make the mount of the lakehouse addresses as local folders.
Once mounted, we loop through the mounts discovering the tables of each lakehouse and executing the maintenance.
It worked like a charm, I will write an article about it.
Thank you for all the help!
Kind Regards,
Dennes
Hi DennesTorres ,
Try using code:
# Get the list of lakehouses to read tables from.
lakehouses = ["gopi_lake_house", "gopi_lakehouse_2"]
# Loop through the lakehouses and read all tables from each lakehouse.
for lakehouse in lakehouses:
tables = spark.sql(f"SHOW TABLES IN {lakehouse}")
display(tables)
Hi,
I was trying the idea of the array as well, but I also need to recover the list of tables from each lakehouse.
The "Show Tables In ..." in your example only works for the default lakehouse. If the lakehouse is not the default, it doesn't work.
Kind Regards,
Dennes
Hi,
Additional attempts I made:lakehouses = ["demolake", "MaltaLake","Sales"]for lake in lakehouses:spark.catalog.setCurrentDatabase(lake)spark.sql('show tables').show()The setCurrentDatabase fails in the second one, because it doesn't work with a database located in a different workspace than the default.lakehouses = ["demolake", "MaltaLake","Sales"]for lake in lakehouses:spark.sql(f'USE {lake}')spark.sql('show tables').show()
Same problem: USE doesn't work in a database in a different workspace than the default.Am I missing something?
Kind Regards,DennesHi DennesTorres ,
Yes, you are correct we need to use "spark.catalog", it will list out all the lakehouses present inside the workspace, even if not linked to the notebook.
Code:lakehouses = spark.catalog.listDatabases() lakehouse_list = [] for lakehouse in lakehouses: lakehouse_list.append(lakehouse.name) print(lakehouse_list)
In order get list of tables present inside particular lakehouse, you can refer below -
Code:# Get the list of lakehouses to read tables from. lakehouses = ["gopi_lake_house", "gopi_lakehouse_2"] # Loop through the lakehouses and read all tables from each lakehouse. for lakehouse in lakehouses: tables = spark.sql(f"SHOW TABLES IN {lakehouse}") tables.show()
Note: SHOW TABLES IN - will be working even if the lakehouse is not default. In my case only gopi_lakehouse_2 is selected as default, but I am able to see tables present inside gopi_lake_house and gopi_lakehouse_2.
For Example:
Executed in Fabric Notebooks:
Executed in Spark Job Application:
The above code is working fine both in notebook and spark job application.
Hope this was helpful.Hi,
Thank you, this part I got.
However, this is limited to lakehouses in the same workspace and not related to the lakehouses linked to the notebook (if I can't loop through the lakehouses linked to the notebook, why link them at all? Is this a bug?)
I'm working on a solution using spark jobs and mssparkutils to mount the different lakehouses in folders, looping through the configurations.
In summary: In a notebook schedule we are limited to a single workspace and the attachment to lakehouses doesn't work very well.
In a spark job we can break the workspace limitation by using mssparkutils and mount (still testing).
But there are many mismatches which seems missing features or even bugs:
We can't loop through the workspaces linked to a notebook
The spark job uses different syntaxes than a notebook (for example, the session needs to be manually stablished)
We can't build a notebook and schedule as a spark job, the development process needs to be different.
By the way, when we try to schedule a notebook, there is a huge limitation on the time we can use on the schedule.
The image below illustrates this. Of couse, we can't schedule something in the past, but the day is not taken into consideration the the choice of hours is very limited, not allowing edition.
Kind Regards,
Dennes