Support Questions

Heri · ‎05-20-2020

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 · ‎05-28-2020

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

View solution in original post

Heri · ‎06-06-2020

Finally,

Our DBA Team re-created the database correctly this time, and we no longer have this issue. We believe that the root cause was that the directory in the HDFS had an uppercase typo.

jagadeesan · ‎06-06-2020

Glad to hear that you have finally found the root cause of this issue. Thanks for sharing @Heri

Cloudera Community

Support Questions

Drop table not working as expected in Hive