Hive, mail # user - dropping an "external" table without deleting the data

dropping an "external" table without deleting the data

Igor Tatarinov 2012-01-24, 23:54

I keep making the same mistake of creating an external table without saying
"EXTERNAL". As a result, as you can imagine, I am stuck with a table that I
cannot drop or I'll lose the external (S3) data. I end up terminating my
EMR instance and creating a new one to avoid dropping the table
accidentally.

Is there a way to drop a "should-be-external" table without losing the
data? Thru mysql perhaps?

Thanks!
igor

RE: dropping an "external" table without deleting the data

Steven Wong 2012-01-25, 00:27

You can change your table to external first and then drop it:

ALTER TABLE my_table SET TBLPROPERTIES ('EXTERNAL'='FALSE');

Please test it on an unimportant table first.
From: Igor Tatarinov [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 24, 2012 3:55 PM
To: [EMAIL PROTECTED]
Subject: dropping an "external" table without deleting the data

I keep making the same mistake of creating an external table without saying "EXTERNAL". As a result, as you can imagine, I am stuck with a table that I cannot drop or I'll lose the external (S3) data. I end up terminating my EMR instance and creating a new one to avoid dropping the table accidentally.

Is there a way to drop a "should-be-external" table without losing the data? Thru mysql perhaps?

Thanks!
igor

Re: dropping an "external" table without deleting the data

Jerome Boulon 2012-01-25, 00:56

… And you may want to double check your table properties using:
describe formatted <YourTableName>;

/Jerome

From: Steven Wong <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Reply-To: <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Date: Wed, 25 Jan 2012 00:27:26 +0000
To: "[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Subject: RE: dropping an "external" table without deleting the data

You can change your table to external first and then drop it:

ALTER TABLE my_table SET TBLPROPERTIES ('EXTERNAL'='FALSE');

Please test it on an unimportant table first.
From: Igor Tatarinov [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 24, 2012 3:55 PM
To: [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>
Subject: dropping an "external" table without deleting the data

I keep making the same mistake of creating an external table without saying "EXTERNAL". As a result, as you can imagine, I am stuck with a table that I cannot drop or I'll lose the external (S3) data. I end up terminating my EMR instance and creating a new one to avoid dropping the table accidentally.

Is there a way to drop a "should-be-external" table without losing the data? Thru mysql perhaps?

Thanks!
igor