You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Stephen Sprague <sp...@gmail.com> on 2017/05/16 13:38:53 UTC

drop table - external - aws

hey guys,
here's something bizarre.   i created about 200 external tables with a
location something like this 'hdfs://<ec2-host>/path'.  this was three
months ago and now i'm revisiting and want to drop these tables.

ha! no can do!

that <ec2-host> is long gone.

Upon issuing the drop table command i get this:

Error while processing statement: FAILED: Execution Error, return code 1
from org.apache.hadoop.hive.ql.exec.DDLTask.
MetaException(message:java.lang.IllegalArgumentException:
java.net.UnknownHostException: <ec2-host>)

where <ec2-host> is that old host name.

so i ask is there a work around for this?  given they are external tables
i'm surprised it "checks" that that location exists (or not.)

thanks,
Stephen

Re: drop table - external - aws

Posted by Edward Capriolo <ed...@gmail.com>.
Im pretty sure schema tool does this for people who convert to ha name node.

On Wednesday, May 17, 2017, Neil Jonkers <ne...@gmail.com> wrote:

> Hi,
>
> Inspecting the Hive Metastore tables.
> Table SDS has a location field.
>
> If for reason this does not work:
> "ALTER TABLE ... SET LOCATION ... ?"
>
> Manually updating the SDS metadata table is an option :
>
> update SDS set  location =  "hdfs://Node:8020/user/hive/warehouse/t"
> where ...
>
> On Wed, May 17, 2017 at 8:41 PM, Furcy Pin <furcy.pin@flaminem.com
> <javascript:_e(%7B%7D,'cvml','furcy.pin@flaminem.com');>> wrote:
>
>> for that, sublime text + multi-line edit is your friend !
>>
>> https://www.youtube.com/watch?v=-paR5m6m-Nw
>>
>> On Wed, May 17, 2017 at 7:24 PM, Stephen Sprague <spragues@gmail.com
>> <javascript:_e(%7B%7D,'cvml','spragues@gmail.com');>> wrote:
>>
>>> yeah. that's a potential idea too.  gotta put the time in to script it
>>> with 200+ tables though.
>>>
>>> On Wed, May 17, 2017 at 10:07 AM, Furcy Pin <furcy.pin@flaminem.com
>>> <javascript:_e(%7B%7D,'cvml','furcy.pin@flaminem.com');>> wrote:
>>>
>>>> Did you try ALTER TABLE ... SET LOCATION ... ? maybe it could have
>>>> worked.
>>>>
>>>>
>>>> On Wed, May 17, 2017 at 6:57 PM, Vihang Karajgaonkar <
>>>> vihang@cloudera.com
>>>> <javascript:_e(%7B%7D,'cvml','vihang@cloudera.com');>> wrote:
>>>>
>>>>> This is interesting and possibly a bug. Did you try changing them to
>>>>> managed tables and then dropping or truncating them? How do we reproduce
>>>>> this on our setup?
>>>>>
>>>>> On Tue, May 16, 2017 at 6:38 PM, Stephen Sprague <spragues@gmail.com
>>>>> <javascript:_e(%7B%7D,'cvml','spragues@gmail.com');>> wrote:
>>>>>
>>>>>> fwiw. i ended up re-creating the ec2 cluster with that same host name
>>>>>> just so i could drop those tables from the metastore.
>>>>>>
>>>>>> note to self.  be careful - be real careful - with "sharing" hive
>>>>>> metastores between different compute paradigms.
>>>>>>
>>>>>> Regards,
>>>>>> Stephen.
>>>>>>
>>>>>> On Tue, May 16, 2017 at 6:38 AM, Stephen Sprague <spragues@gmail.com
>>>>>> <javascript:_e(%7B%7D,'cvml','spragues@gmail.com');>> wrote:
>>>>>>
>>>>>>> hey guys,
>>>>>>> here's something bizarre.   i created about 200 external tables with
>>>>>>> a location something like this 'hdfs://<ec2-host>/path'.  this was three
>>>>>>> months ago and now i'm revisiting and want to drop these tables.
>>>>>>>
>>>>>>> ha! no can do!
>>>>>>>
>>>>>>> that <ec2-host> is long gone.
>>>>>>>
>>>>>>> Upon issuing the drop table command i get this:
>>>>>>>
>>>>>>> Error while processing statement: FAILED: Execution Error, return
>>>>>>> code 1 from org.apache.hadoop.hive.ql.exec.DDLTask.
>>>>>>> MetaException(message:java.lang.IllegalArgumentException:
>>>>>>> java.net.UnknownHostException: <ec2-host>)
>>>>>>>
>>>>>>> where <ec2-host> is that old host name.
>>>>>>>
>>>>>>> so i ask is there a work around for this?  given they are external
>>>>>>> tables i'm surprised it "checks" that that location exists (or not.)
>>>>>>>
>>>>>>> thanks,
>>>>>>> Stephen
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>

-- 
Sorry this was sent from mobile. Will do less grammar and spell check than
usual.

Re: drop table - external - aws

Posted by Neil Jonkers <ne...@gmail.com>.
Hi,

Inspecting the Hive Metastore tables.
Table SDS has a location field.

If for reason this does not work:
"ALTER TABLE ... SET LOCATION ... ?"

Manually updating the SDS metadata table is an option :

update SDS set  location =  "hdfs://Node:8020/user/hive/warehouse/t" where
...

On Wed, May 17, 2017 at 8:41 PM, Furcy Pin <fu...@flaminem.com> wrote:

> for that, sublime text + multi-line edit is your friend !
>
> https://www.youtube.com/watch?v=-paR5m6m-Nw
>
> On Wed, May 17, 2017 at 7:24 PM, Stephen Sprague <sp...@gmail.com>
> wrote:
>
>> yeah. that's a potential idea too.  gotta put the time in to script it
>> with 200+ tables though.
>>
>> On Wed, May 17, 2017 at 10:07 AM, Furcy Pin <fu...@flaminem.com>
>> wrote:
>>
>>> Did you try ALTER TABLE ... SET LOCATION ... ? maybe it could have
>>> worked.
>>>
>>>
>>> On Wed, May 17, 2017 at 6:57 PM, Vihang Karajgaonkar <
>>> vihang@cloudera.com> wrote:
>>>
>>>> This is interesting and possibly a bug. Did you try changing them to
>>>> managed tables and then dropping or truncating them? How do we reproduce
>>>> this on our setup?
>>>>
>>>> On Tue, May 16, 2017 at 6:38 PM, Stephen Sprague <sp...@gmail.com>
>>>> wrote:
>>>>
>>>>> fwiw. i ended up re-creating the ec2 cluster with that same host name
>>>>> just so i could drop those tables from the metastore.
>>>>>
>>>>> note to self.  be careful - be real careful - with "sharing" hive
>>>>> metastores between different compute paradigms.
>>>>>
>>>>> Regards,
>>>>> Stephen.
>>>>>
>>>>> On Tue, May 16, 2017 at 6:38 AM, Stephen Sprague <sp...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> hey guys,
>>>>>> here's something bizarre.   i created about 200 external tables with
>>>>>> a location something like this 'hdfs://<ec2-host>/path'.  this was three
>>>>>> months ago and now i'm revisiting and want to drop these tables.
>>>>>>
>>>>>> ha! no can do!
>>>>>>
>>>>>> that <ec2-host> is long gone.
>>>>>>
>>>>>> Upon issuing the drop table command i get this:
>>>>>>
>>>>>> Error while processing statement: FAILED: Execution Error, return
>>>>>> code 1 from org.apache.hadoop.hive.ql.exec.DDLTask.
>>>>>> MetaException(message:java.lang.IllegalArgumentException:
>>>>>> java.net.UnknownHostException: <ec2-host>)
>>>>>>
>>>>>> where <ec2-host> is that old host name.
>>>>>>
>>>>>> so i ask is there a work around for this?  given they are external
>>>>>> tables i'm surprised it "checks" that that location exists (or not.)
>>>>>>
>>>>>> thanks,
>>>>>> Stephen
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>

Re: drop table - external - aws

Posted by Furcy Pin <fu...@flaminem.com>.
for that, sublime text + multi-line edit is your friend !

https://www.youtube.com/watch?v=-paR5m6m-Nw

On Wed, May 17, 2017 at 7:24 PM, Stephen Sprague <sp...@gmail.com> wrote:

> yeah. that's a potential idea too.  gotta put the time in to script it
> with 200+ tables though.
>
> On Wed, May 17, 2017 at 10:07 AM, Furcy Pin <fu...@flaminem.com>
> wrote:
>
>> Did you try ALTER TABLE ... SET LOCATION ... ? maybe it could have worked.
>>
>>
>> On Wed, May 17, 2017 at 6:57 PM, Vihang Karajgaonkar <vihang@cloudera.com
>> > wrote:
>>
>>> This is interesting and possibly a bug. Did you try changing them to
>>> managed tables and then dropping or truncating them? How do we reproduce
>>> this on our setup?
>>>
>>> On Tue, May 16, 2017 at 6:38 PM, Stephen Sprague <sp...@gmail.com>
>>> wrote:
>>>
>>>> fwiw. i ended up re-creating the ec2 cluster with that same host name
>>>> just so i could drop those tables from the metastore.
>>>>
>>>> note to self.  be careful - be real careful - with "sharing" hive
>>>> metastores between different compute paradigms.
>>>>
>>>> Regards,
>>>> Stephen.
>>>>
>>>> On Tue, May 16, 2017 at 6:38 AM, Stephen Sprague <sp...@gmail.com>
>>>> wrote:
>>>>
>>>>> hey guys,
>>>>> here's something bizarre.   i created about 200 external tables with a
>>>>> location something like this 'hdfs://<ec2-host>/path'.  this was three
>>>>> months ago and now i'm revisiting and want to drop these tables.
>>>>>
>>>>> ha! no can do!
>>>>>
>>>>> that <ec2-host> is long gone.
>>>>>
>>>>> Upon issuing the drop table command i get this:
>>>>>
>>>>> Error while processing statement: FAILED: Execution Error, return code
>>>>> 1 from org.apache.hadoop.hive.ql.exec.DDLTask.
>>>>> MetaException(message:java.lang.IllegalArgumentException:
>>>>> java.net.UnknownHostException: <ec2-host>)
>>>>>
>>>>> where <ec2-host> is that old host name.
>>>>>
>>>>> so i ask is there a work around for this?  given they are external
>>>>> tables i'm surprised it "checks" that that location exists (or not.)
>>>>>
>>>>> thanks,
>>>>> Stephen
>>>>>
>>>>
>>>>
>>>
>>
>

Re: drop table - external - aws

Posted by Stephen Sprague <sp...@gmail.com>.
yeah. that's a potential idea too.  gotta put the time in to script it with
200+ tables though.

On Wed, May 17, 2017 at 10:07 AM, Furcy Pin <fu...@flaminem.com> wrote:

> Did you try ALTER TABLE ... SET LOCATION ... ? maybe it could have worked.
>
>
> On Wed, May 17, 2017 at 6:57 PM, Vihang Karajgaonkar <vi...@cloudera.com>
> wrote:
>
>> This is interesting and possibly a bug. Did you try changing them to
>> managed tables and then dropping or truncating them? How do we reproduce
>> this on our setup?
>>
>> On Tue, May 16, 2017 at 6:38 PM, Stephen Sprague <sp...@gmail.com>
>> wrote:
>>
>>> fwiw. i ended up re-creating the ec2 cluster with that same host name
>>> just so i could drop those tables from the metastore.
>>>
>>> note to self.  be careful - be real careful - with "sharing" hive
>>> metastores between different compute paradigms.
>>>
>>> Regards,
>>> Stephen.
>>>
>>> On Tue, May 16, 2017 at 6:38 AM, Stephen Sprague <sp...@gmail.com>
>>> wrote:
>>>
>>>> hey guys,
>>>> here's something bizarre.   i created about 200 external tables with a
>>>> location something like this 'hdfs://<ec2-host>/path'.  this was three
>>>> months ago and now i'm revisiting and want to drop these tables.
>>>>
>>>> ha! no can do!
>>>>
>>>> that <ec2-host> is long gone.
>>>>
>>>> Upon issuing the drop table command i get this:
>>>>
>>>> Error while processing statement: FAILED: Execution Error, return code
>>>> 1 from org.apache.hadoop.hive.ql.exec.DDLTask.
>>>> MetaException(message:java.lang.IllegalArgumentException:
>>>> java.net.UnknownHostException: <ec2-host>)
>>>>
>>>> where <ec2-host> is that old host name.
>>>>
>>>> so i ask is there a work around for this?  given they are external
>>>> tables i'm surprised it "checks" that that location exists (or not.)
>>>>
>>>> thanks,
>>>> Stephen
>>>>
>>>
>>>
>>
>

Re: drop table - external - aws

Posted by Furcy Pin <fu...@flaminem.com>.
Did you try ALTER TABLE ... SET LOCATION ... ? maybe it could have worked.


On Wed, May 17, 2017 at 6:57 PM, Vihang Karajgaonkar <vi...@cloudera.com>
wrote:

> This is interesting and possibly a bug. Did you try changing them to
> managed tables and then dropping or truncating them? How do we reproduce
> this on our setup?
>
> On Tue, May 16, 2017 at 6:38 PM, Stephen Sprague <sp...@gmail.com>
> wrote:
>
>> fwiw. i ended up re-creating the ec2 cluster with that same host name
>> just so i could drop those tables from the metastore.
>>
>> note to self.  be careful - be real careful - with "sharing" hive
>> metastores between different compute paradigms.
>>
>> Regards,
>> Stephen.
>>
>> On Tue, May 16, 2017 at 6:38 AM, Stephen Sprague <sp...@gmail.com>
>> wrote:
>>
>>> hey guys,
>>> here's something bizarre.   i created about 200 external tables with a
>>> location something like this 'hdfs://<ec2-host>/path'.  this was three
>>> months ago and now i'm revisiting and want to drop these tables.
>>>
>>> ha! no can do!
>>>
>>> that <ec2-host> is long gone.
>>>
>>> Upon issuing the drop table command i get this:
>>>
>>> Error while processing statement: FAILED: Execution Error, return code 1
>>> from org.apache.hadoop.hive.ql.exec.DDLTask.
>>> MetaException(message:java.lang.IllegalArgumentException:
>>> java.net.UnknownHostException: <ec2-host>)
>>>
>>> where <ec2-host> is that old host name.
>>>
>>> so i ask is there a work around for this?  given they are external
>>> tables i'm surprised it "checks" that that location exists (or not.)
>>>
>>> thanks,
>>> Stephen
>>>
>>
>>
>

Re: drop table - external - aws

Posted by Stephen Sprague <sp...@gmail.com>.
hi vihang,
here's how to duplicate (hive version 2.1.0).

1. install hadoop on a host called "test-nn"

2. install hive on a host called  "hive-client" and use a metastore on
"hive-client" (or anywhere else except "test-nn").

3. using hive on "hive-client" do this: create external table foo(a int)
location 'hdfs://test-nn/hive_root';

3. shutdown host "test-nn" or rename it.

4. using hive on "hive-client" try dropping table foo.

pretty sure step 4 is metastore only operation and hence does not need a
active hadoop cluster to work. if it were a managed table then yeah we'd
need to bring up a another cluster.

but you get the idea, right?

On Wed, May 17, 2017 at 9:57 AM, Vihang Karajgaonkar <vi...@cloudera.com>
wrote:

> This is interesting and possibly a bug. Did you try changing them to
> managed tables and then dropping or truncating them? How do we reproduce
> this on our setup?
>
> On Tue, May 16, 2017 at 6:38 PM, Stephen Sprague <sp...@gmail.com>
> wrote:
>
>> fwiw. i ended up re-creating the ec2 cluster with that same host name
>> just so i could drop those tables from the metastore.
>>
>> note to self.  be careful - be real careful - with "sharing" hive
>> metastores between different compute paradigms.
>>
>> Regards,
>> Stephen.
>>
>> On Tue, May 16, 2017 at 6:38 AM, Stephen Sprague <sp...@gmail.com>
>> wrote:
>>
>>> hey guys,
>>> here's something bizarre.   i created about 200 external tables with a
>>> location something like this 'hdfs://<ec2-host>/path'.  this was three
>>> months ago and now i'm revisiting and want to drop these tables.
>>>
>>> ha! no can do!
>>>
>>> that <ec2-host> is long gone.
>>>
>>> Upon issuing the drop table command i get this:
>>>
>>> Error while processing statement: FAILED: Execution Error, return code 1
>>> from org.apache.hadoop.hive.ql.exec.DDLTask.
>>> MetaException(message:java.lang.IllegalArgumentException:
>>> java.net.UnknownHostException: <ec2-host>)
>>>
>>> where <ec2-host> is that old host name.
>>>
>>> so i ask is there a work around for this?  given they are external
>>> tables i'm surprised it "checks" that that location exists (or not.)
>>>
>>> thanks,
>>> Stephen
>>>
>>
>>
>

Re: drop table - external - aws

Posted by Vihang Karajgaonkar <vi...@cloudera.com>.
This is interesting and possibly a bug. Did you try changing them to
managed tables and then dropping or truncating them? How do we reproduce
this on our setup?

On Tue, May 16, 2017 at 6:38 PM, Stephen Sprague <sp...@gmail.com> wrote:

> fwiw. i ended up re-creating the ec2 cluster with that same host name just
> so i could drop those tables from the metastore.
>
> note to self.  be careful - be real careful - with "sharing" hive
> metastores between different compute paradigms.
>
> Regards,
> Stephen.
>
> On Tue, May 16, 2017 at 6:38 AM, Stephen Sprague <sp...@gmail.com>
> wrote:
>
>> hey guys,
>> here's something bizarre.   i created about 200 external tables with a
>> location something like this 'hdfs://<ec2-host>/path'.  this was three
>> months ago and now i'm revisiting and want to drop these tables.
>>
>> ha! no can do!
>>
>> that <ec2-host> is long gone.
>>
>> Upon issuing the drop table command i get this:
>>
>> Error while processing statement: FAILED: Execution Error, return code 1
>> from org.apache.hadoop.hive.ql.exec.DDLTask.
>> MetaException(message:java.lang.IllegalArgumentException:
>> java.net.UnknownHostException: <ec2-host>)
>>
>> where <ec2-host> is that old host name.
>>
>> so i ask is there a work around for this?  given they are external tables
>> i'm surprised it "checks" that that location exists (or not.)
>>
>> thanks,
>> Stephen
>>
>
>

Re: drop table - external - aws

Posted by Stephen Sprague <sp...@gmail.com>.
fwiw. i ended up re-creating the ec2 cluster with that same host name just
so i could drop those tables from the metastore.

note to self.  be careful - be real careful - with "sharing" hive
metastores between different compute paradigms.

Regards,
Stephen.

On Tue, May 16, 2017 at 6:38 AM, Stephen Sprague <sp...@gmail.com> wrote:

> hey guys,
> here's something bizarre.   i created about 200 external tables with a
> location something like this 'hdfs://<ec2-host>/path'.  this was three
> months ago and now i'm revisiting and want to drop these tables.
>
> ha! no can do!
>
> that <ec2-host> is long gone.
>
> Upon issuing the drop table command i get this:
>
> Error while processing statement: FAILED: Execution Error, return code 1
> from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:java.lang.IllegalArgumentException:
> java.net.UnknownHostException: <ec2-host>)
>
> where <ec2-host> is that old host name.
>
> so i ask is there a work around for this?  given they are external tables
> i'm surprised it "checks" that that location exists (or not.)
>
> thanks,
> Stephen
>