Drop table not working as expected in Hive

Question

Hello guys!

I hope your day is going well.

Something really weird (at least for me) is happening when I drop a table in Hive.

1.- A table called "Test" is created by a sqoop import job using "--create-hcatalog-table". See the full job below.

2.-Once the import job completes (with a select * from the source), I drop the table without issues:

drop table test purge;

At this point I expect that all the information is deleted, gone, bye, finito, adios!

3.-I run the same sqoop job again, but it not only loads the table with the fresh information from the source. It also shows me the previous information before the drop table!

Some guys have told me that's because the table has been created as an external table by the sqoop job. But even that it was an external table, a drop table <table> purge; is used to get rid of the data, isn't it?

Pls let me know your thoughts.

Sqoop job:

sqoop import \
-Dhadoop.security.credential.provider.path=jceks://hdfs/user/lrm0613/mydb2.jceks \
--connection-manager org.apache.sqoop.manager.SQLServerManager \
--driver net.sourceforge.jtds.jdbc.Driver \
--connect 'jdbc:jtds:sqlserver://SQLQP002:1433;useNTLMv2=true;domain=JNL_NT;databaseName=TC31Reporting' \
--username 'lrm0613' \
--password-alias sqlserver2.password \
--query 'select * from JobHistory where $CONDITIONS' \
--hcatalog-database dataengsandbox \
--hcatalog-table Test \
--compress \
--compression-codec snappy \
--map-column-hive jobid=INT \
-m 1 \
--create-hcatalog-table \
--hcatalog-storage-stanza 'stored as parquet'

jagadeesan · Accepted Answer

HI @Heri,

Here I just wanna add some points. You can use PURGE option to delete data file as well along with partition metadata but it works only in Internal/ Managed tables

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec PURGE;

But for External tables have a two-step process to alter table drop partition + removing file

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec; hdfs dfs -rm -r <partition file path>

I hope this gives some insights here. cc @aakulov

aakulov · Answer

Hi Heri, After you execute drop table test purge; can you check that the data is actually deleted? Do a query on the table first, but also check with hdfs dfs to see if the underlying files have been deleted from Hadoop (they should be). Let me know what you see.  You may be right that EXTERNAL table data does not get deleted, just the metadata is deleted. That's why I'm asking you to check for data with hdfs dfs. Now, to be able to drop the EXTERNAL table (both metadata and data) you'd need to follow the steps here: https://docs.cloudera.com/HDPDocuments/HDP3/HDP-3.1.4/using-hiveql/content/hive_drop_external_table_data.html Hope that helps.

46	Location:	hdfs://nameservice1/data/DataEngSandbox/test	NULL
47	Table Type:	MANAGED_TABLE	NULL
48	Table Parameters:	NULL	NULL
49		COLUMN_STATS_ACCURATE	false
50		external.table.purge	true
51		numFiles	1
52		numRows	-1
53		rawDataSize	-1
54		totalSize	19098747
55		transient_lastDdlTime	1590587689
56		NULL	NULL
57	# Storage Information	NULL	NULL
58	SerDe Library:	org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe	NULL
59	InputFormat:	org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat	NULL
60	OutputFormat:	org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat	NULL
61	Compressed:	No	NULL
62	Num Buckets:	-1	NULL
63	Bucket Columns:	[]	NULL
64	Sort Columns:	[]	NULL
65	Storage Desc Params:	NULL	NULL

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.