Hive LOCATION and WITH DBPROPERTIES command in create table command

Question

Hi, could you please explain the use of LOCATION and WITH DBPROPERTIES command in below create table command?

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

Answer 1

By default, the Hive database will be created inside the default warehouse directory i.e /user/hive/warehouse. But if we want we can store the database in some other HDFS location as well bt mentioning the same in the location field. The DB properties are nothing but mentioning the details about the database created by the user. Suppose the name of the user, the type of the database and the tables it has, the date on which the database is created etc. This makes the other user easy the recognize the database and use it according to the requirement.