topic Re: I cannot see the Hive databases or tables once I terminate the cluster and use another cluster. in Data Engineering

I cannot see the Hive databases or tables once I terminate the cluster and use another cluster.

lnsnarayanan — Sun, 22 Aug 2021 07:05:47 GMT

I am using Databricks community edition for learning purposes.

I created some Hive-managed tables through spark sql as well as with df.saveAsTable options.

But when I connect to a new cluser,

"Show databases"

only returns the default database.

The database and tables I created with the previous cluster are not shown.

However when I run

spark.conf.get("spark.sql.warehouse.dir")

it returns

'/user/hive/warehouse'

and When I check the dbfs directory

%fs ls dbfs:/user/hive/warehouse

I see the databases and tables I created in other sessions. Am I missing anything or sparkSQL doesn't read the hive managed tables created in other clusters in commnunity edition?

Re: I cannot see the Hive databases or tables once I terminate the cluster and use another cluster.

-werners- — Mon, 23 Aug 2021 09:09:29 GMT

can you see the database and tables in the web UI, "Data" in the menu on the left?

Re: I cannot see the Hive databases or tables once I terminate the cluster and use another cluster.

lnsnarayanan — Mon, 23 Aug 2021 09:21:37 GMT

I cannot see databases and tables when no cluster is attached and when I attach a new cluster, I see only the default database.

But I can see previously created databases and tables using

%fs ls /user/hive/warehouse command.

It is not showing up in the data menu or using sparkSQL.

Re: I cannot see the Hive databases or tables once I terminate the cluster and use another cluster.

-werners- — Mon, 23 Aug 2021 09:38:21 GMT

Yes, but you mentioned you did thus by using the "show databases" command in a notebook.

I ask you to check using the Databricks Web UI.

It could be that Community Edition does not have this functionality though.

Re: I cannot see the Hive databases or tables once I terminate the cluster and use another cluster.

dududu — Sat, 25 Sep 2021 02:21:02 GMT

I have the same problem, so it's the feather of CE ?

Re: I cannot see the Hive databases or tables once I terminate the cluster and use another cluster.

Srihasa_Akepati — Thu, 07 Oct 2021 10:59:56 GMT

Hello,

Yes, the databases created from one cluster cannot be accessed from another in CE. Even a restart of the same cluster would not show the databases/tables created before the restart.

Re: I cannot see the Hive databases or tables once I terminate the cluster and use another cluster.

Prabakar — Tue, 12 Oct 2021 07:04:39 GMT

This is a limitation in CE.

Re: I cannot see the Hive databases or tables once I terminate the cluster and use another cluster.

dez — Fri, 05 Jan 2024 10:33:14 GMT

This "feature" in the Community edition is based on the fact that I cannot restart a cluster. So, in the morning I create a cluster for studies purposes and in the afternoon I have to recreate the cluster.

If there's any dependent objects from previous labs I have to rerun the Notebooks and recreate them, but before I do it I have to drop all files and directories (tables) I had previously created.

Here's a tip if anyone needs:
1. List the Database directory (usually it will be dbfs:/user/hive/datawarehouse/)

%sql

DESCRIBE DATABASE default

2. Get the files Location from the previous output and list the files

f = dbutils.fs.ls('dbfs:/user/hive/warehouse')

display(f)

3. Remove the files and directories so that you'll be able to rerun your Notebook to recreate the dependent tables to continue your labs.

%fs rm -r dbfs:/user/hive/warehouse/<replace_with_a_file_or_directory>/

Now you're good to go on recreating the tables and views.

Re: I cannot see the Hive databases or tables once I terminate the cluster and use another cluster.

dhpaulino — Mon, 03 Jun 2024 18:24:08 GMT

As the file still in the dbfs you can just recreate the reference of your tables and continue the work, with something like this:

db_name = "mydb" from pathlib import Path path_db = f"dbfs:/user/hive/warehouse/{db_name}.db/" tables_dirs = dbutils.fs.ls(path_db) for d in tables_dirs: table_name = Path(d.path).name spark.sql(f"""CREATE TABLE IF NOT EXISTS {table_name} LOCATION '{d.path}' """)