Data Engineering using Spark SQL - Getting Started - Managing Spark Metastore Databases

Let us understand how to manage Spark Metastore Databases. It is essential to review the Language Manual regularly to stay updated.

Creating a database can be done using the CREATE DATABASE command. For instance, CREATE DATABASE itversity_demo;. If the database already exists, the command will fail. To avoid errors, you can use IF NOT EXISTS. For example, CREATE DATABASE IF NOT EXISTS itversity_demo;.

To view the existing databases, you can use SHOW databases;. The Spark Metastore is a multi-tenant database. You can switch to a specific database using the USE command. For instance, USE itversity_demo;.

To drop an empty database, use DROP DATABASE itversity_demo;. Including cascade will drop all the tables before dropping the database: DROP DATABASE itversity_demo CASCADE;.

When creating a database, you can specify the location using: CREATE DATABASE itversity_demo LOCATION '/user/itversity/custom/itversity_demo.db'.

Hands-On Tasks

  1. Create a new database named itversity_demo.
  2. List all the databases in the Spark Metastore.
  3. Switch to the itversity_demo database.
  4. Drop the itversity_demo database including all its tables.
  5. Create a new database with a specific location.


Managing Spark Metastore databases is crucial for organizing and accessing data efficiently. By following the steps outlined in this article, you can handle databases effectively and make the most out of Spark’s capabilities. Practice these tasks to gain hands-on experience and don’t hesitate to engage with the community for further learning opportunities.

Watch the video tutorial here