DROP Multiple Tables in Database using Databricks-CodePudding

I am trying to drop multiple tables in databrick scala using the following command

select 'DROP TABLE '   tableName from ABC where tableName LIKE 'in%'

Error results saying

Error in SQL statement: AnalysisException: Table or view not found: ABC

However the ABC database is present

Thank you

CodePudding user response：

No, it doesn't work this way... You need to use SHOW TABLES IN ... LIKE ... in combination with the explicit deletes. Something like this (in Python):

db_name = "ABC"
pattern = "in%"
tables = spark.sql(f"SHOW TABLES IN {db_name} LIKE '{pattern}'")
for row in tables.collect():
  spark.sql(f"DROP TABLE {db_name}.{row[1]}

CodePudding user response：

I found the following way in scala

val dbName="ABC"
val df = spark.sql(s"""show tables from """  dbName)
df.createOrReplaceTempView("temp_tables")
val temp_tables = spark.sql("""select tableName from temp_tables where tableName like 'in%' """)

temp_tables.collect().foreach(row => println("DROP TABLE "   dbName   "."  row.toString().replace("[", "").replace("]", "")))