Why All Hive databases share the same underlying data file [ Edited ]

[Mano]

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

[GeKas]

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?

[Mano]

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.