Drop table using Pyspark

user_10552469 0
Viewed 7446

The SparkSession.catalog object has a bunch of methods to interact with the metastore, namely:

['cacheTable',
 'clearCache',
 'createExternalTable',
 'createTable',
 'currentDatabase',
 'dropGlobalTempView',
 'dropTempView',
 'isCached',
 'listColumns',
 'listDatabases',
 'listFunctions',
 'listTables',
 'recoverPartitions',
 'refreshByPath',
 'refreshTable',
 'registerFunction',
 'setCurrentDatabase',
 'uncacheTable']

Unfortunately, there seems to have no programmatic way to drop a table.

There are multiple ways to achieve this like

spark.sql(f"drop table my_table")

or

spark._jsparkSession.sharedState().externalCatalog().dropTable(db, table, True, True)

but they look a little bit hackish compared to a simple, nonetheless missing, dropTable method?

Is there a better way ?

pyspark apache-spark-sql apache-spark hive scala
1 Answers

AFAIK from the above approaches mentioned are most commonly used ones. No other way I feel..

But alternative way I can see from these docs...

you might try this org.apache.spark.sql.hive.HiveUtils which has goodies (to drop tables..) for you.

I am not so good in python, you can see below scala example and follow the same way for python. 

package org.apache.spark.sql.hive {
import org.apache.spark.sql.hive.HiveUtils
import org.apache.spark.SparkContext
object utils {
    def dropTable(sc: SparkContext, dbName: String, tableName: String, ignoreIfNotExists: Boolean, purge: Boolean): Unit = {
      HiveUtils
          .newClientForMetadata(sc.getConf, sc.hadoopConfiguration)
          .dropTable(dbName, tableName, ignoreIfNotExists, false)
    }
  }
}

Caller would be like 

import org.apache.spark.sql.hive.utils
utils.dropTable(sc, "default", "my_table", true, true)
user_6470530
Related