You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by hardik doshi <ko...@yahoo.com> on 2013/01/30 09:14:18 UTC
ALTER TABLE CHANGE COLUMN issue
Hi,
I am running into an issue where ALTER TABLE CHANGE COLUMN does not seem to be working.
I have a table with a column data type looking like array<struct<a:int, b:int>> and I am trying to it change to array<struct<a:int, b:int, c:string>> based
on the underlying data schema change.
The alter command succeeds and subsequent describe call shows me the updated table structure. But when tried querying the table,
it returns null for the newly added field.
This does not happen when a new table with updated column data type is created.
Is this a known bug?
Thanks,
Hardik.
PS:- My alter command: ALTER TABLE hardiktest CHANGE COLUMN col1 col2 array<struct<a:int, b:int, c:string>>.
Re: ALTER TABLE CHANGE COLUMN issue
Posted by Nitin Pawar <ni...@gmail.com>.
it will not work old partition because old data did not have this new
column as metadata for old partition
your new meta data applies only to new partitions
always remember there is nothing called update or alter row on hive.
alter is only on the table meta data from that time onwards
if you really want to check if your old data has the new column then you
can do a select * from table where condition for old data limit 1
and then table definition old and new.
also how do you recreate the partition? you are reading from old table and
writing into a new table with the same data? or you have external table so
you are just registering the partitions with metadata store like hcatalog
in that case it will be easy to recreate the table and register the
partitions again to have your new metadata applied to old partitions
On Thu, Jan 31, 2013 at 1:02 AM, Mark Grover <gr...@gmail.com>wrote:
> Hardik,
> The schema is associated per partition. It sounds to me that the structure
> of your data remains the same, you are just expressing it differently in
> your Hive table.
>
> If you are table is external this is no biggie, just drop the external
> table, re-create it and re-add the partitions. If not, you'll have to look
> into the documentation to see if there is an alter table partition
> (partition spec)... command that will let you alter metadata about the
> partition.
>
> If I am not mistaken, alter table doesn't touch your existing columns,
> just modifies the partitions going forward.
>
> Mark
>
>
> On Wed, Jan 30, 2013 at 11:12 AM, hardik doshi <ko...@yahoo.com>wrote:
>
>> Thanks, Nitin & Dean.
>>
>> My hive table is backed by data files in hdfs and they do contain the
>> additional field that I am adding in my hive table schema.
>>
>> I noticed that if I remove partitions and recreate them after changing
>> the column type, it works. But it does not work
>> on old partition for some weird reasons.
>>
>> Any ideas?
>>
>> -Hardik.
>>
>>
>> ------------------------------
>> *From:* Dean Wampler <de...@thinkbiganalytics.com>
>> *To:* user@hive.apache.org
>> *Cc:* hardik doshi <ko...@yahoo.com>
>> *Sent:* Wednesday, January 30, 2013 5:51 AM
>> *Subject:* Re: ALTER TABLE CHANGE COLUMN issue
>>
>> Right, the very important thing to remember about ALTER TABLE is that it
>> only changes metadata about your table. It doesn't modify the data in any
>> way. You have to do that yourself.
>>
>> On Wed, Jan 30, 2013 at 2:17 AM, Nitin Pawar <ni...@gmail.com>wrote:
>>
>> after u did alter table, did you add any new data to table with new
>> schema?
>>
>> for the old data already present in data, if you add anything new in
>> columns it will be null value
>>
>>
>> On Wed, Jan 30, 2013 at 1:44 PM, hardik doshi <ko...@yahoo.com>wrote:
>>
>> Hi,
>>
>> I am running into an issue where ALTER TABLE CHANGE COLUMN does not seem
>> to be working.
>>
>> I have a table with a column data type looking like array<struct<a:int,
>> b:int>> and I am trying to it change to array<struct<a:int, b:int,
>> c:string>> based
>> on the underlying data schema change.
>>
>> The alter command succeeds and subsequent describe call shows me the
>> updated table structure. But when tried querying the table,
>> it returns null for the newly added field.
>>
>> This does not happen when a new table with updated column data type is
>> created.
>>
>> Is this a known bug?
>>
>> Thanks,
>> Hardik.
>>
>> PS:- My alter command: ALTER TABLE hardiktest CHANGE COLUMN col1 col2
>> array<struct<a:int, b:int, c:string>>.
>>
>>
>>
>>
>> --
>> Nitin Pawar
>>
>>
>>
>>
>> --
>> *Dean Wampler, Ph.D.*
>> thinkbiganalytics.com
>> +1-312-339-1330
>>
>>
>>
>>
>
--
Nitin Pawar
Re: ALTER TABLE CHANGE COLUMN issue
Posted by Mark Grover <gr...@gmail.com>.
Hardik,
The schema is associated per partition. It sounds to me that the structure
of your data remains the same, you are just expressing it differently in
your Hive table.
If you are table is external this is no biggie, just drop the external
table, re-create it and re-add the partitions. If not, you'll have to look
into the documentation to see if there is an alter table partition
(partition spec)... command that will let you alter metadata about the
partition.
If I am not mistaken, alter table doesn't touch your existing columns, just
modifies the partitions going forward.
Mark
On Wed, Jan 30, 2013 at 11:12 AM, hardik doshi <ko...@yahoo.com>wrote:
> Thanks, Nitin & Dean.
>
> My hive table is backed by data files in hdfs and they do contain the
> additional field that I am adding in my hive table schema.
>
> I noticed that if I remove partitions and recreate them after changing the
> column type, it works. But it does not work
> on old partition for some weird reasons.
>
> Any ideas?
>
> -Hardik.
>
>
> ------------------------------
> *From:* Dean Wampler <de...@thinkbiganalytics.com>
> *To:* user@hive.apache.org
> *Cc:* hardik doshi <ko...@yahoo.com>
> *Sent:* Wednesday, January 30, 2013 5:51 AM
> *Subject:* Re: ALTER TABLE CHANGE COLUMN issue
>
> Right, the very important thing to remember about ALTER TABLE is that it
> only changes metadata about your table. It doesn't modify the data in any
> way. You have to do that yourself.
>
> On Wed, Jan 30, 2013 at 2:17 AM, Nitin Pawar <ni...@gmail.com>wrote:
>
> after u did alter table, did you add any new data to table with new
> schema?
>
> for the old data already present in data, if you add anything new in
> columns it will be null value
>
>
> On Wed, Jan 30, 2013 at 1:44 PM, hardik doshi <ko...@yahoo.com>wrote:
>
> Hi,
>
> I am running into an issue where ALTER TABLE CHANGE COLUMN does not seem
> to be working.
>
> I have a table with a column data type looking like array<struct<a:int,
> b:int>> and I am trying to it change to array<struct<a:int, b:int,
> c:string>> based
> on the underlying data schema change.
>
> The alter command succeeds and subsequent describe call shows me the
> updated table structure. But when tried querying the table,
> it returns null for the newly added field.
>
> This does not happen when a new table with updated column data type is
> created.
>
> Is this a known bug?
>
> Thanks,
> Hardik.
>
> PS:- My alter command: ALTER TABLE hardiktest CHANGE COLUMN col1 col2
> array<struct<a:int, b:int, c:string>>.
>
>
>
>
> --
> Nitin Pawar
>
>
>
>
> --
> *Dean Wampler, Ph.D.*
> thinkbiganalytics.com
> +1-312-339-1330
>
>
>
>
Re: ALTER TABLE CHANGE COLUMN issue
Posted by hardik doshi <ko...@yahoo.com>.
Thanks, Nitin & Dean.
My hive table is backed by data files in hdfs and they do contain the additional field that I am adding in my hive table schema.
I noticed that if I remove partitions and recreate them after changing the column type, it works. But it does not work
on old partition for some weird reasons.
Any ideas?
-Hardik.
________________________________
From: Dean Wampler <de...@thinkbiganalytics.com>
To: user@hive.apache.org
Cc: hardik doshi <ko...@yahoo.com>
Sent: Wednesday, January 30, 2013 5:51 AM
Subject: Re: ALTER TABLE CHANGE COLUMN issue
Right, the very important thing to remember about ALTER TABLE is that it only changes metadata about your table. It doesn't modify the data in any way. You have to do that yourself.
On Wed, Jan 30, 2013 at 2:17 AM, Nitin Pawar <ni...@gmail.com> wrote:
after u did alter table, did you add any new data to table with new schema?
>
>
>for the old data already present in data, if you add anything new in columns it will be null value
>
>
>
>On Wed, Jan 30, 2013 at 1:44 PM, hardik doshi <ko...@yahoo.com> wrote:
>
>Hi,
>>
>>I am running into an issue where ALTER TABLE CHANGE COLUMN does not seem to be working.
>>
>>I have a table with a column data type looking like array<struct<a:int, b:int>> and I am trying to it change to array<struct<a:int, b:int, c:string>> based
>>on the underlying data schema change.
>>
>>
>>
>>The alter command succeeds and subsequent describe call shows me the updated table structure. But when tried querying the table,
>>it returns null for the newly added field.
>>
>>
>>This does not happen when a new table with updated column data type is created.
>>
>>
>>Is this a known bug?
>>
>>
>>Thanks,
>>Hardik.
>>
>>
>>PS:- My alter command: ALTER TABLE hardiktest CHANGE COLUMN col1 col2 array<struct<a:int, b:int, c:string>>.
>>
>
>
>
>--
>Nitin Pawar
>
--
Dean Wampler, Ph.D.
thinkbiganalytics.com
+1-312-339-1330
Re: ALTER TABLE CHANGE COLUMN issue
Posted by Dean Wampler <de...@thinkbiganalytics.com>.
Right, the very important thing to remember about ALTER TABLE is that it
only changes metadata about your table. It doesn't modify the data in any
way. You have to do that yourself.
On Wed, Jan 30, 2013 at 2:17 AM, Nitin Pawar <ni...@gmail.com>wrote:
> after u did alter table, did you add any new data to table with new
> schema?
>
> for the old data already present in data, if you add anything new in
> columns it will be null value
>
>
> On Wed, Jan 30, 2013 at 1:44 PM, hardik doshi <ko...@yahoo.com>wrote:
>
>> Hi,
>>
>> I am running into an issue where ALTER TABLE CHANGE COLUMN does not seem
>> to be working.
>>
>> I have a table with a column data type looking like array<struct<a:int,
>> b:int>> and I am trying to it change to array<struct<a:int, b:int,
>> c:string>> based
>> on the underlying data schema change.
>>
>> The alter command succeeds and subsequent describe call shows me the
>> updated table structure. But when tried querying the table,
>> it returns null for the newly added field.
>>
>> This does not happen when a new table with updated column data type is
>> created.
>>
>> Is this a known bug?
>>
>> Thanks,
>> Hardik.
>>
>> PS:- My alter command: ALTER TABLE hardiktest CHANGE COLUMN col1 col2
>> array<struct<a:int, b:int, c:string>>.
>>
>
>
>
> --
> Nitin Pawar
>
--
*Dean Wampler, Ph.D.*
thinkbiganalytics.com
+1-312-339-1330
Re: ALTER TABLE CHANGE COLUMN issue
Posted by Nitin Pawar <ni...@gmail.com>.
after u did alter table, did you add any new data to table with new schema?
for the old data already present in data, if you add anything new in
columns it will be null value
On Wed, Jan 30, 2013 at 1:44 PM, hardik doshi <ko...@yahoo.com> wrote:
> Hi,
>
> I am running into an issue where ALTER TABLE CHANGE COLUMN does not seem
> to be working.
>
> I have a table with a column data type looking like array<struct<a:int,
> b:int>> and I am trying to it change to array<struct<a:int, b:int,
> c:string>> based
> on the underlying data schema change.
>
> The alter command succeeds and subsequent describe call shows me the
> updated table structure. But when tried querying the table,
> it returns null for the newly added field.
>
> This does not happen when a new table with updated column data type is
> created.
>
> Is this a known bug?
>
> Thanks,
> Hardik.
>
> PS:- My alter command: ALTER TABLE hardiktest CHANGE COLUMN col1 col2
> array<struct<a:int, b:int, c:string>>.
>
--
Nitin Pawar