How to create a database at specific location in Hive?
Contents
Create database in Hive
The database is an organised collection of tables.Hive has the default database with the name as “default“. If we do not specify the database name while creating the table, it will be created in the default database. The default location is /apps/hive/warehouse/<table_name>.
If we want to create custom database, we need to run the create database command as below. It will create the database in the following location /apps/hive/warehouse/<database_name>.db
|
1 |
create database <database_name>; |
Example
Let’s create a database in Hive and find the HDFS location of that database.
|
1 2 3 |
hive> create database seller_offers; OK Time taken: 0.047 seconds |
As we mentioned, we have created the database as seller_offers in Hive. In order to find the location of that database, we need to run the describe database command as below.
|
1 2 3 4 5 6 |
hive> describe database seller_offers; OK +-------------+-------------+-------------+-------------+------------+-------------+-------------+--------+ | db_name | location | owner_name | owner_type | +-----------+----------+----------------------------------------------------+------------------+------------ | seller_offers | hdfs://test_hdp_cluster/apps/hive/warehouse/seller_offers.db | hive | USER |
In the output, we can see the location column that has HDFS directory name. Here test_hdp_cluster is a Hadoop cluster name. Followed by cluster name, it is showing the database location as /apps/hive/warehouse/seller_offers.db
Create database at specific location in Hive
Create database command can creates a database at specific location as well. For that, we need to give the location property in the create database command. Please follow the syntax as mentioned below
|
1 2 |
create database <database_name> location 'hdfs_location'; |
Example
Lets create a database sells_report in the following HDFS location /apps/seller/datamart/sells_report.
|
1 2 |
create database sells_report location '/apps/seller/datamart/sells_report'; |
|
1 2 3 4 5 6 |
hive> describe database sells_report; OK +-------------+-------------+-------------+-------------+------------+-------------+-------------+--------+ | db_name | location | owner_name | owner_type | +-----------+----------+----------------------------------------------------+------------------+------------ | sells_report | hdfs://test_hdp_cluster/apps/hive/warehouse/sells_report | hive | USER |
The database sells_report is created in the given directory. On this database, we can create all the sells report related tables. While creating the tables, we need to mention this location in the create table statement. Sot that the data will be stored under the sells_report database location.
|
1 2 3 4 5 |
create table sells_report.micro_seller_report (seller_id int, name string, total_revenue bigint) location ‘/apps/seller/datamart/sells_report/micro_seller_report’; |
As we shown in the create table statement, we have used <database_name>.<table_name> to create the table on specific database. Next we have mentioned the location as database location with table name.
- Database directory is /apps/seller/datamart/sells_report
- Table directory is /apps/seller/datamart/sells_report/micro_seller_report
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
hive> show create table sells_report.micro_seller_report; OK CREATE TABLE `sells_report.micro_seller_report`( `seller_id` int, `name` string, `total_revenue` bigint) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 'hdfs://test_hdp_cluster/apps/seller/datamart/sells_report/micro_seller_report' TBLPROPERTIES ( 'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}', 'numFiles'='0', 'numRows'='0', 'rawDataSize'='0', 'totalSize'='0', 'transient_lastDdlTime'='1636814020') |
The create table statement is created another directory micro_seller_report under the database location. If we insert the records into this table, the data will be stored in a table directory. Similarly we can create other tables under the database location.
Recommended Articles
