In Apache Hive, unlike MySQL or any other RDBMS, there is no SHOW CREATE DATABASE statement. At the very least, not in a direct way. There is, however, a way to get all of the database definitions so that they may be run in a new instance.

In the terminal window, use the below command to fetch all the database definitions in a text file.

mysql -N -D hive -e “SELECT concat( ‘CREATE DATABASE ‘ ,NAME , ‘ LOCATION ‘, DB_LOCATION_URI , ‘;’) FROM DBS” > createdbs.txt;

Now, you can simply type the CAT command to display the create database statements.


-N stands for skip-column-Names.
, -N
This command will prevent column names from appearing in the results.

-D stands for database.
Refer to the below command.
mysql -D dbName
It’s short form for ‘databases’

-e stands for executing.
Refer to the below command.
mysql -u root -p -e “SELECT VERSION();SELECT NOW()”
We are instructing it to connect to the mysql using “root” user, prompt for password (-p) and execute (-e) the given command.

-DBS is the table name in which all the database information exists.

Hope you find this article helpful.

Please subscribe for more interesting updates.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s