question Re: Override default Hive Database Path in Archives of Support Questions (Read Only)

Override default Hive Database Path

SatishS — Sun, 18 Aug 2019 06:32:10 GMT

Hi All,

I was trying to create a hive database by specifying a different location. The query got executed successfully, but I don't see the database in the specified location. Am I missing anything here?

PFA for the screenshots.

Re: Override default Hive Database Path

SatishS — Sun, 18 Aug 2019 06:31:57 GMT

When I execute with describe to check the location of the database, it gives me the same path that was mentioned while creating it, but when I browse the location, i don't see anything there. PFA for the screenshot.

Re: Override default Hive Database Path

SatishS — Sat, 03 Jun 2017 22:55:52 GMT

cross checked on the path through command prompt with hadoop command, even I don't see any .db folder. Please let me know if I am missing anything here.

Re: Override default Hive Database Path

Shelton — Sun, 04 Jun 2017 03:53:35 GMT

@Satish Sarapuri

How did you look for the db folder?

Usually running the below command

# su - hdfs
$ hdfs dfs -ls /

This should give you ,the most probably

hdfs dfs -ls /apps/hive/warehouse/

And also check the below property in the hive-site.xml file

<property>
   <name>hive.metastore.warehouse.dir</name>
   <value>/usr/hive/warehouse </value>
   <description>location of the warehouse directory</description>
 </property>

Re: Override default Hive Database Path

SatishS — Sun, 18 Aug 2019 06:31:49 GMT

@Geoffrey Shelton Okot

I have created this database by overriding the default path (used user/hive).

I used both hdfs dfs and hadoop fs commands to check the .db folder. I don't see anything on the mentioned path that was used while creating. PFA for the attached screenshot.

Re: Override default Hive Database Path

SatishS — Sun, 18 Aug 2019 06:31:41 GMT

@Geoffrey Shelton Okot

I tried to create a table in this db, when I created, I see a folder with same name as table in /user/hive path. but no folder with .db. PFA for the screenshots.

Re: Override default Hive Database Path

SatishS — Mon, 05 Jun 2017 04:47:18 GMT

@Geoffrey Shelton Okot

The default path is /apps/hive/warehouse. But, for this database, I have mentioned different path to create and I am am not able to find the .db folder in the mentioned path, I can see the table folder when I create one. The .db folder should be created under the same mentioned path right?

Re: Override default Hive Database Path

SatishS — Tue, 06 Jun 2017 21:18:48 GMT

Could anyone please let me know on this?

Re: Override default Hive Database Path

poojachawda_10 — Wed, 07 Jun 2017 14:10:35 GMT

@Satish Sarapuri

My default hive warehouse: 

hive> dfs -ls /user/hive/warehouse/;        
Found 8 items
drwxrwxrwx   - hive     hive          0 2016-02-22 23:04 /user/hive/warehouse/clickstream
drwxrwxrwx   - hive     hive          0 2015-12-02 21:52 /user/hive/warehouse/clickstream1
drwxrwxrwx   - cloudera hive          0 2017-02-15 23:03 /user/hive/warehouse/emp1
drwxrwxrwx   - cloudera hive          0 2016-09-07 23:10 /user/hive/warehouse/emp1_1
drwxrwxrwx   - cloudera hive          0 2016-09-07 23:12 /user/hive/warehouse/emp1_2
drwxrwxrwx   - cloudera hive          0 2017-02-15 23:03 /user/hive/warehouse/emp1_orc
drwxrwxrwx   - cloudera hive          0 2016-09-07 23:09 /user/hive/warehouse/emp2
drwxrwxrwx   - hive     hive          0 2017-02-16 01:24 /user/hive/warehouse/employee

Creating database without LOCATION:

hive> create database testing1;                             
OK
Time taken: 0.048 seconds
hive> dfs -ls /user/hive/warehouse/;
Found 9 items
drwxrwxrwx   - hive     hive          0 2016-02-22 23:04 /user/hive/warehouse/clickstream
drwxrwxrwx   - hive     hive          0 2015-12-02 21:52 /user/hive/warehouse/clickstream1
drwxrwxrwx   - cloudera hive          0 2017-02-15 23:03 /user/hive/warehouse/emp1
drwxrwxrwx   - cloudera hive          0 2016-09-07 23:10 /user/hive/warehouse/emp1_1
drwxrwxrwx   - cloudera hive          0 2016-09-07 23:12 /user/hive/warehouse/emp1_2
drwxrwxrwx   - cloudera hive          0 2017-02-15 23:03 /user/hive/warehouse/emp1_orc
drwxrwxrwx   - cloudera hive          0 2016-09-07 23:09 /user/hive/warehouse/emp2
drwxrwxrwx   - hive     hive          0 2017-02-16 01:24 /user/hive/warehouse/employee
drwxrwxrwx   - cloudera hive          0 2017-06-06 23:38 /user/hive/warehouse/testing1.db

Creating database with LOCATION:

hive> create database testing location '/user/hive/testing';
OK
Time taken: 0.147 seconds
hive> dfs -ls /user/hive/;                                  
Found 2 items
drwxrwxrwx   - cloudera hive          0 2017-06-06 23:35 /user/hive/testing
drwxrwxrwx   - hive     hive          0 2017-02-15 23:01 /user/hive/warehouse

In hive explicit LOCATION is just a pointer.

Re: Override default Hive Database Path

SatishS — Thu, 08 Jun 2017 02:53:35 GMT

@Pooja Chawda

Here my question is, It should create the <dbname>.db folder (as it does in default path), why It's not creating the <dbname>.db folder when I pass /override the default location? There supposed to be Testing.db under the testing folder right (in your case)?

It's strange behaviour when you override the database default path.

Re: Override default Hive Database Path

SatishS — Tue, 13 Jun 2017 07:53:27 GMT

I found that this is how hive works when I provide (override the default ) the path.

It will use the specified path assuming the path subdirectory ex: /user/hive/warehouse/, "warehouse" folder in this case as the main DB folder and it will create all the tables underneath.

Re: Override default Hive Database Path

SatishS — Tue, 13 Jun 2017 07:55:12 GMT

when we override the path, it won't create any <dbname>.db folder, assuming that the specified path it self is the db /main folder for the mentioned database.