[SOLVED] Spark Check If Table Exists In Hive Using Apache Spark Or PySpark? – Great And Simple 2 Snippets Of Code!

In this tutorial I will show you how using Spark check if table exists in Hive. The approach is the same for Spark + Scala and for PySpark.

Hive Metastore

Hive keeps all the metadata in Hive Metastore which it’s simply RDBMS like Oracle, Mysql, Postgres database. Any request to Hive like when you execute SQL query starts from asking Hive Metastore about the metadata of tables which are included in SQL statement.

Information about metadata is very important. In case when your table is partitioned all the information about where on HDFS is stored data in specific partition allow Hive to do not scan all the HDFS directories under Hive table, but go directly to specific directory. It causes performance advantages.

Hive Metastore is a component of Apache Hive that acts as a central repository for metadata about Hive tables and partitions. It stores information about the structure and location of Hive tables and partitions, as well as the properties of the data stored in those tables and partitions.

The Hive Metastore is used by Hive and other tools, such as Apache Spark, to access Hive data. It helps to abstract away the details of the storage location and structure of the data, allowing users to access and query the data using SQL-like commands.

The Hive Metastore is typically deployed as a standalone service that can be accessed by multiple clients. It can store metadata in a variety of backends, such as a relational database or a file system.

The Hive Metastore is an important component of the Hive ecosystem and plays a central role in the management and querying of Hive data. It helps to provide a consistent and standardized way to access and query Hive data across different tools and applications.

Spark check if table exists in Hive

When you are looking for Hive table please provide table name in lowercase, due to fact that spark.sqlContext.tableNames returns the array of table names only in lowercase.

Information about tables in Hive are stored in Hive Metastore.

The Newest Approach

In Apache Spark or PySpark, you can check if a table exists in Hive by using the tableExists method of the SparkSession object. This method returns a boolean value indicating whether the specified table exists in the Hive metastore.

Here is an example of how to check if a table named “mytable” exists in Hive using PySpark:

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("MyApp").getOrCreate()

# Check if the table exists in Hive
if spark.tableExists("mytable"):
  print("Table exists")
else:
  print("Table does not exist")

This will print “Table exists” if the table exists in Hive, and “Table does not exist” if it does not.

You can also use the catalog attribute of the SparkSession object to access the Hive metastore and check for the existence of a table. Here is an example of how to do this in PySpark:

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("MyApp").getOrCreate()

# Get the list of tables in the default database
tables = spark.catalog.listTables()

# Check if the table exists in the list of tables
if "mytable" in [table.name for table in tables]:
  print("Table exists")
else:
  print("Table does not exist")

This will also check if the table “mytable” exists in Hive and print the appropriate message.

Spark 2.0 or higher

// Create SparkSession object with enabled Hive support
val spark = SparkSession
.builder()
.appName("Check table")
.enableHiveSupport()
.getOrCreate()
// Select database where you will search for table - lowercase
spark.sqlContext.sql("use bigdata_etl")
spark.sqlContext.tableNames.contains("schemas")
res4: Boolean = true

// With Uppercase
spark.sqlContext.tableNames.contains("Schemas")
res4: Boolean = false

Since Spark 1.6 to 2.0

// Get HiveContext from SparkContext
val sparkConf = new SparkConf().setAppName("Check table")
val sc = new SparkContext(sparkConf)
val hiveContext = new HiveContext(sc)
hiveContext.sql("use bigdata_etl")
hiveContext.tableNames.contains("schemas")

// With Uppercase
hiveContext.tableNames.contains("Schemas")
res4: Boolean = false

If table will exist you will give the “true”, otherwise “false”

PySpark Check if table exists

The above examples are presented using Scala language, but we are using a pure Spark API here, so in PySpark it will look the same. In that easy way you can check if table exists PySpark.

Could You Please Share This Post? 
I appreciate It And Thank YOU! :)
Have A Nice Day!