Reply
Explorer
Posts: 7
Registered: ‎12-06-2017

Why All Hive databases share the same underlying data file

[ Edited ]

Hi,

I saw a very piculiar problem in hive.

1. created two databases in hive with the same tables(tables1,table2,table3).

2. Loaded data in one database tables say tables1,table2,table3 is loaded.

3. The other database reflects the same data if i query.

4. Checked the warehouse location in hdfs through HUE its the same location both the databasees share, there is no databases.db i could see. 

Its like below for both the databases, and it has got the same two files(_SUCCESS,part-m-00000).

/user/hive/warehouse/table1

 

Is this behaviour expected? or any change in settings has resulted like this? 

 

Regards,

Mano

 

 

 

Expert Contributor
Posts: 127
Registered: ‎01-08-2018

Re: Why All Hive databases share the same underlying data file

This is not expected at all.

Tables should be created under db directory. E.g.

/user/hive/warehouse/database1.db/table1

/user/hive/warehouse/database2.db/tableA

etc.

 

This case is very weird. In such cases I suspect user error. Can you provide the command you have used?

Highlighted
Explorer
Posts: 7
Registered: ‎12-06-2017

Re: Why All Hive databases share the same underlying data file

Thanks for the reply,

 

What I noticed is i was giving the LOCATION as my warehouse directory.

That is why all the schemas were sharing the same underlying file if the table names are same, and not creating the schemaname.db

Below is the command i used. 

 

CREATE SCHEMA IF NOT EXISTS hiveschemaname COMMENT 'datadom for hiveschemaname' LOCATION 'hdfs://ipaddress:8020/user/hive/warehouse' 

 

One more thing i noticed is if i place the hive-site.xml in spark2 conf directory for implicit reference  to hive in spark context, the file vanishes and i have to palce it back with the change 

 

hive.metastore.warehouse.dir --> spark.sql.warehouse.dir

 

Any idea why or do we have any other way to achieve it? Thnak you.

 

Announcements