Lots of developers are not aware that they can do it using single DROP statement. sqlContext.sql('drop table test') //spark 1.6 spark.sql('drop table test') //spark 2.0 Both code works fine in pyspark-shell , I can see from the hive cli that the test table no longer exist. Consequently, dropping of an external table does not affect the data. Let us create the following three tables to demonstrate dropping multiple tables using a single DROP statement. Use DROP TABLE to drop a table, like any other RDBMS, dropping a table in hive drops the table description from Hive Metastore and it’s data from the Hive warehouse store(For internal tables). The uses of SCHEMA and DATABASE are interchangeable – they mean the same thing. I have to load multiple s3 CSV to hive. External tables provide an option to create multiple schemas for the data stored in HDFS instead of deleting the data every time whenever schema updates; When to Choose External Table: CREATE DATABASE was added in Hive 0.6 ().. The external table data is stored externally, while Hive metastore only contains the metadata schema. One way is to query hive metastore but this is always not possible as we may not have permission to access it. The WITH DBPROPERTIES clause was added in Hive 0.7 ().MANAGEDLOCATION was added to database in Hive 4.0.0 ().LOCATION now refers to the default directory for external tables and MANAGEDLOCATION refers to the default directory for managed tables. Hive>DROP TABLE guruhive_internaltable; If you dropped the guruhive_internaltable, including its metadata and its data will be deleted from Hive. In this tutorial, you will learn how to create, query, and drop an external table in Hive. Then do Ctrl+H to replace schema with DROP TABLE SCHEMA that will give you all the drop queries, copy and paste this big sql into your sql tool and execute. However, if the code was in a python file and later submitted to cluster using spark-submit, the code never took effect. Hive will remove all of its data and metadata from the hive meta-store. DROP TABLE command in the hive is used to drop a table inside the hive. There is no direct way to drop multiple tables in hive. You will need multiple calls to accomplish this and handle them in an external driver. hive> ALTER TABLE sales drop if exists partition (year = 2020, quarter = 1), partition (year = 2020, quarter = 2); Here is how we dynamically pick partitions to drop. Hive is very much capable such that it can query petabytes of records stored inside the hive table. In the same hive database there are other tables but I can use wild card as the tables for this particular load … Let say that there is a scenario in which you need to find the list of External Tables from all the Tables in a Hive Database using Spark. I showed you how to DROP Statement multiple tables together using a wizard in SQL Server Management Studio (SSMS). myschema.table1 myschema.table2 after replace, it'll look like this. Drop multiple partitions With the below alter script, we provide the exact partitions we would like to delete. We can try the below approach as well: Step1: Create 1 Internal Table and 2 External Table. The file name, contents keep on changing in every load so I have to drop the existing tables in HIVE, create and full refresh. Then open that CSV in a notepad. if your results are. DROP TABLE MYSCHEMA.TABLE1 DROP TABLE MYSCHEMA.TABLE2 You can achieve it via another way. Approach 1: Using shell script/command Approach 2: You can keep all the table into a database and then drop the database. Hive does not support like semantics for drop table, see official DDL semantics here. The hive DROP TABLE … You will need multiple calls to accomplish this and handle them in an external driver. Hive has a Internal and External tables.