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.
Explanation:
-N stands for skip-column-Names.
–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.
Big Data and SQL 