You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@hive.apache.org by Sai Naga <fr...@gmail.com> on 2012/06/19 22:19:25 UTC

create external table on existing hive partitioned table ?

Is it possible,  to create external table on a existing hive table which is
partitioned.

I have a existing hive table which is partitioned by dt and group like below


desc page_access;
page  string
country string
dt      string ( Partitioned column )
group     string ( Partitioned column )



dfs -tail
file  /user/hive/warehouse/page_access/dt=2012-06-01/group=a/000003_0


PXXXXX1  {"UY": 2, "BR": 1}
PXXXXX2  {"LV": 1, "BR": 1}


Is it possible to define a external table something like..

create external table page_access_m
(
page string
country map(<string>,<int>)
)PARTITIONED BY(dt STRING, group STRING)
location /user/hive/warehouse/page_access;

currently I'm using hive.0.6



Thanks
Sai

Re: create external table on existing hive partitioned table ?

Posted by Mark Grover <mg...@oanda.com>.

Hi Sai,
You might want to look into Alter table statements to see the type of the column can be modified. You may need to upgrade Hive if that functionality is not available in your version.

Mark

----- Original Message -----
From: "Sai Naga" <fr...@gmail.com>
To: user@hive.apache.org
Sent: Tuesday, June 19, 2012 6:16:31 PM
Subject: Re: create external table on existing hive partitioned table ?

Thanks Mark, 

The reason to create the 2nd table is One of the column is defined as string in the first table, I wanted to read the string into Map data type. 

i.e 

Existing table. 

{"UY": 2, "BR": 1} 
{"LV": 1, "BR": 1} 

To 

Country Map<Sting, int > 

Thanks 
Gopi 

On Tue, Jun 19, 2012 at 1:37 PM, Mark Grover < mgrover@oanda.com > wrote: 

Sai, 
Maybe I don't understand your question properly but creating an external table on a partitioned table is no different than create an external table on a non-partitioned one. 

Your syntax looks right. After table creation, you would have to add all existing partitions of the table so that the new table's metadata is properly populated. 
You can do that using a command like "ALTER TABLE page_access_m RECOVER PARTITIONS" on Amazon EMR Hive or using msck on Apache Hive (more details at: https://issues.apache.org/jira/browse/HIVE-874 ) 

Does that answer your question? 

On a related note, having two tables over the same data could be troublesome. For example, if one query adds a partition to one table, you would have to remember to recover partitions on the on the other table before you access your data through it. If the schema for both the tables is the same (except the fact that one is managed while other is external), any particular reason you'd like to create a new table? 

Mark 

----- Original Message ----- 
From: "Sai Naga" < freshair1947@gmail.com > 
To: user@hive.apache.org 
Sent: Tuesday, June 19, 2012 4:19:25 PM 
Subject: create external table on existing hive partitioned table ? 

Is it possible, to create external table on a existing hive table which is partitioned. 

I have a existing hive table which is partitioned by dt and group like below 

desc page_access; 
page string 
country string 
dt string ( Partitioned column ) 
group string ( Partitioned column ) 

dfs -tail file /user/hive/warehouse/page_access/dt=2012-06-01/group=a/000003_0 

PXXXXX1 {"UY": 2, "BR": 1} 
PXXXXX2 {"LV": 1, "BR": 1} 

Is it possible to define a external table something like.. 

create external table page_access_m 
( 
page string 
country map(<string>,<int>) 
)PARTITIONED BY(dt STRING, group STRING) 
location /user/hive/warehouse/page_access; 

currently I'm using hive.0.6 

Thanks 
Sai

Re: create external table on existing hive partitioned table ?

Posted by Mark Grover <mg...@oanda.com>.

In hindsight, I have learned to create external tables so if I get into a situation like this, I don't have to look up the appropriate alter table statment (if it exists at all), I can just drop the external table and re-create it:-)

Mark

----- Original Message -----
From: "Sai Naga" <fr...@gmail.com>
To: user@hive.apache.org
Sent: Tuesday, June 19, 2012 6:16:31 PM
Subject: Re: create external table on existing hive partitioned table ?

Thanks Mark, 

The reason to create the 2nd table is One of the column is defined as string in the first table, I wanted to read the string into Map data type. 

i.e 

Existing table. 

{"UY": 2, "BR": 1} 
{"LV": 1, "BR": 1} 

To 

Country Map<Sting, int > 

Thanks 
Gopi 

On Tue, Jun 19, 2012 at 1:37 PM, Mark Grover < mgrover@oanda.com > wrote: 

Sai, 
Maybe I don't understand your question properly but creating an external table on a partitioned table is no different than create an external table on a non-partitioned one. 

Your syntax looks right. After table creation, you would have to add all existing partitions of the table so that the new table's metadata is properly populated. 
You can do that using a command like "ALTER TABLE page_access_m RECOVER PARTITIONS" on Amazon EMR Hive or using msck on Apache Hive (more details at: https://issues.apache.org/jira/browse/HIVE-874 ) 

Does that answer your question? 

On a related note, having two tables over the same data could be troublesome. For example, if one query adds a partition to one table, you would have to remember to recover partitions on the on the other table before you access your data through it. If the schema for both the tables is the same (except the fact that one is managed while other is external), any particular reason you'd like to create a new table? 

Mark 

----- Original Message ----- 
From: "Sai Naga" < freshair1947@gmail.com > 
To: user@hive.apache.org 
Sent: Tuesday, June 19, 2012 4:19:25 PM 
Subject: create external table on existing hive partitioned table ? 

Is it possible, to create external table on a existing hive table which is partitioned. 

I have a existing hive table which is partitioned by dt and group like below 

desc page_access; 
page string 
country string 
dt string ( Partitioned column ) 
group string ( Partitioned column ) 

dfs -tail file /user/hive/warehouse/page_access/dt=2012-06-01/group=a/000003_0 

PXXXXX1 {"UY": 2, "BR": 1} 
PXXXXX2 {"LV": 1, "BR": 1} 

Is it possible to define a external table something like.. 

create external table page_access_m 
( 
page string 
country map(<string>,<int>) 
)PARTITIONED BY(dt STRING, group STRING) 
location /user/hive/warehouse/page_access; 

currently I'm using hive.0.6 

Thanks 
Sai

Re: create external table on existing hive partitioned table ?

Posted by Sai Naga <fr...@gmail.com>.

Thanks Mark,

The reason to create the 2nd table is One of the column is defined as
string in the first table, I wanted to read the string into Map data type.


i.e

Existing table.

{"UY": 2, "BR": 1}
{"LV": 1, "BR": 1}

To

Country Map<Sting, int >

Thanks
Gopi


On Tue, Jun 19, 2012 at 1:37 PM, Mark Grover <mg...@oanda.com> wrote:

> Sai,
> Maybe I don't understand your question properly but creating an external
> table on a partitioned table is no different than create an external table
> on a non-partitioned one.
>
> Your syntax looks right. After table creation, you would have to add all
> existing partitions of the table so that the new table's metadata is
> properly populated.
> You can do that using a command like "ALTER TABLE page_access_m RECOVER
> PARTITIONS" on Amazon EMR Hive or using msck on Apache Hive (more details
> at: https://issues.apache.org/jira/browse/HIVE-874)
>
> Does that answer your question?
>
> On a related note, having two tables over the same data could be
> troublesome. For example, if one query adds a partition to one table, you
> would have to remember to recover partitions on the on the other table
> before you access your data through it. If the schema for both the tables
> is the same (except the fact that one is managed while other is external),
> any particular reason you'd like to create a new table?
>
> Mark
>
> ----- Original Message -----
> From: "Sai Naga" <fr...@gmail.com>
> To: user@hive.apache.org
> Sent: Tuesday, June 19, 2012 4:19:25 PM
> Subject: create external table on existing hive partitioned table ?
>
>
> Is it possible, to create external table on a existing hive table which is
> partitioned.
>
>
> I have a existing hive table which is partitioned by dt and group like
> below
>
>
>
>
> desc page_access;
> page string
> country string
> dt string ( Partitioned column )
> group string ( Partitioned column )
>
>
>
>
>
>
> dfs -tail file
> /user/hive/warehouse/page_access/dt=2012-06-01/group=a/000003_0
>
>
>
>
> PXXXXX1 {"UY": 2, "BR": 1}
> PXXXXX2 {"LV": 1, "BR": 1}
>
>
>
>
> Is it possible to define a external table something like..
>
>
> create external table page_access_m
> (
> page string
> country map(<string>,<int>)
> )PARTITIONED BY(dt STRING, group STRING)
> location /user/hive/warehouse/page_access;
>
>
> currently I'm using hive.0.6
>
>
>
>
>
>
> Thanks
> Sai
>

Re: create external table on existing hive partitioned table ?

Posted by Mark Grover <mg...@oanda.com>.

Sai,
Maybe I don't understand your question properly but creating an external table on a partitioned table is no different than create an external table on a non-partitioned one.

Your syntax looks right. After table creation, you would have to add all existing partitions of the table so that the new table's metadata is properly populated.
You can do that using a command like "ALTER TABLE page_access_m RECOVER PARTITIONS" on Amazon EMR Hive or using msck on Apache Hive (more details at: https://issues.apache.org/jira/browse/HIVE-874)

Does that answer your question?

On a related note, having two tables over the same data could be troublesome. For example, if one query adds a partition to one table, you would have to remember to recover partitions on the on the other table before you access your data through it. If the schema for both the tables is the same (except the fact that one is managed while other is external), any particular reason you'd like to create a new table?

Mark

----- Original Message -----
From: "Sai Naga" <fr...@gmail.com>
To: user@hive.apache.org
Sent: Tuesday, June 19, 2012 4:19:25 PM
Subject: create external table on existing hive partitioned table ?


Is it possible, to create external table on a existing hive table which is partitioned. 


I have a existing hive table which is partitioned by dt and group like below 




desc page_access; 
page string 
country string 
dt string ( Partitioned column ) 
group string ( Partitioned column ) 






dfs -tail file /user/hive/warehouse/page_access/dt=2012-06-01/group=a/000003_0 




PXXXXX1 {"UY": 2, "BR": 1} 
PXXXXX2 {"LV": 1, "BR": 1} 




Is it possible to define a external table something like.. 


create external table page_access_m 
( 
page string 
country map(<string>,<int>) 
)PARTITIONED BY(dt STRING, group STRING) 
location /user/hive/warehouse/page_access; 


currently I'm using hive.0.6 






Thanks 
Sai