You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Ashwanth Kumar <as...@googlemail.com> on 2012/05/01 00:55:23 UTC
Re: add column to hive table
You can only add columns at the end. If you are using the native SerDe try
ALTER TABLE tbl_name REPLACE COLUMNS (column1 int, mynewcolumn string,
column2 int);
More information on Hive Wiki -
https://cwiki.apache.org/Hive/languagemanual-ddl.html#LanguageManualDDL-Add%252FReplaceColumns
On Tue, May 1, 2012 at 4:12 AM, Chalcy Raja
<Ch...@careerbuilder.com>wrote:
> Hi all,****
>
> Adding a column by alter table mytable add column (mynewcolumn string);
> works well for me. This adds a field to the end of the schema. ****
>
> Is there a way to add a column in between two columns?****
>
> Thanks,****
>
> Chalcy****
>
> ** **
>
> ** **
>
--
Ashwanth Kumar / ashwanthkumar.in
RE: add column to hive table
Posted by Chalcy Raja <Ch...@careerbuilder.com>.
Yes, Gabi, that is the reason I went this solution as well. Now it is hard to keep up with db changes in hive.
Question to the group: How are you all handling the ever changing schema?
Thanks,
Chalcy
From: Gabi D [mailto:gabid33@gmail.com]
Sent: Wednesday, May 02, 2012 2:50 AM
To: user@hive.apache.org
Cc: ashwanthkumar@googlemail.com
Subject: Re: add column to hive table
you probably noticed this already, but if you add a column in the middle and it did not exist in your older files then when you select from older dates you will get wrong values in the wrong columns since you will be looking at old files with the new format. Dangerous. We also went with the sqoop trick to cause new columns to be added only to the end of existing hive tables. It causes a difference in the order of fields between the db and hive but it's not that painful.
On Wed, May 2, 2012 at 3:38 AM, Chalcy Raja <Ch...@careerbuilder.com>> wrote:
In my situation, the tables I was importing into hive are daily tables. Couple of columns were added in a month which are not added to the end of the table. Also one field got dropped in between. Also I have data for a year.
Anyway the way, I handled is, created an external table on top of the older data and sqoop imported specifying columns in the query tag of the sqoop import into the hive external table. Had to have three different sqoop imports for the three periods. Anyway got them all together so nobody will notice the change :)
--Chalcy
From: Chalcy Raja [mailto:Chalcy.Raja@careerbuilder.com<ma...@careerbuilder.com>]
Sent: Monday, April 30, 2012 9:08 PM
To: user@hive.apache.org<ma...@hive.apache.org>; ashwanthkumar@googlemail.com<ma...@googlemail.com>
Subject: RE: add column to hive table
Good idea! Ashwanth. See if this can work for me and let you know.
Thanks,
CHalcy
From: ashwanth.kumar@gmail.com<ma...@gmail.com> [mailto:ashwanth.kumar@gmail.com<ma...@gmail.com>] On Behalf Of Ashwanth Kumar
Sent: Monday, April 30, 2012 6:55 PM
To: user@hive.apache.org<ma...@hive.apache.org>
Subject: Re: add column to hive table
You can only add columns at the end. If you are using the native SerDe try
ALTER TABLE tbl_name REPLACE COLUMNS (column1 int, mynewcolumn string, column2 int);
More information on Hive Wiki - https://cwiki.apache.org/Hive/languagemanual-ddl.html#LanguageManualDDL-Add%252FReplaceColumns
On Tue, May 1, 2012 at 4:12 AM, Chalcy Raja <Ch...@careerbuilder.com>> wrote:
Hi all,
Adding a column by alter table mytable add column (mynewcolumn string); works well for me. This adds a field to the end of the schema.
Is there a way to add a column in between two columns?
Thanks,
Chalcy
--
Ashwanth Kumar / ashwanthkumar.in<http://ashwanthkumar.in/>
Re: add column to hive table
Posted by Gabi D <ga...@gmail.com>.
you probably noticed this already, but if you add a column in the middle
and it did not exist in your older files then when you select from older
dates you will get wrong values in the wrong columns since you will be
looking at old files with the new format. Dangerous. We also went with the
sqoop trick to cause new columns to be added only to the end of existing
hive tables. It causes a difference in the order of fields between the db
and hive but it's not that painful.
On Wed, May 2, 2012 at 3:38 AM, Chalcy Raja
<Ch...@careerbuilder.com>wrote:
> In my situation, the tables I was importing into hive are daily tables.
> Couple of columns were added in a month which are not added to the end of
> the table. Also one field got dropped in between. Also I have data for a
> year. ****
>
> ** **
>
> Anyway the way, I handled is, created an external table on top of the
> older data and sqoop imported specifying columns in the query tag of the
> sqoop import into the hive external table. Had to have three different
> sqoop imports for the three periods. Anyway got them all together so
> nobody will notice the change J****
>
> ** **
>
> --Chalcy****
>
> ** **
>
> *From:* Chalcy Raja [mailto:Chalcy.Raja@careerbuilder.com]
> *Sent:* Monday, April 30, 2012 9:08 PM
> *To:* user@hive.apache.org; ashwanthkumar@googlemail.com
> *Subject:* RE: add column to hive table****
>
> ** **
>
> Good idea! Ashwanth. See if this can work for me and let you know.****
>
> ** **
>
> Thanks,****
>
> CHalcy****
>
> ** **
>
> *From:* ashwanth.kumar@gmail.com [mailto:ashwanth.kumar@gmail.com] *On
> Behalf Of *Ashwanth Kumar
> *Sent:* Monday, April 30, 2012 6:55 PM
> *To:* user@hive.apache.org
> *Subject:* Re: add column to hive table****
>
> ** **
>
> You can only add columns at the end. If you are using the native SerDe try
>
> ALTER TABLE tbl_name REPLACE COLUMNS (column1 int, mynewcolumn string,
> column2 int);
>
> More information on Hive Wiki -
> https://cwiki.apache.org/Hive/languagemanual-ddl.html#LanguageManualDDL-Add%252FReplaceColumns
> ****
>
> On Tue, May 1, 2012 at 4:12 AM, Chalcy Raja <Ch...@careerbuilder.com>
> wrote:****
>
> Hi all,****
>
> Adding a column by alter table mytable add column (mynewcolumn string);
> works well for me. This adds a field to the end of the schema. ****
>
> Is there a way to add a column in between two columns?****
>
> Thanks,****
>
> Chalcy****
>
> ****
>
> ****
>
>
>
>
> -- ****
>
> ** **
>
> Ashwanth Kumar / ashwanthkumar.in****
>
> ** **
>
> ** **
>
RE: add column to hive table
Posted by Chalcy Raja <Ch...@careerbuilder.com>.
In my situation, the tables I was importing into hive are daily tables. Couple of columns were added in a month which are not added to the end of the table. Also one field got dropped in between. Also I have data for a year.
Anyway the way, I handled is, created an external table on top of the older data and sqoop imported specifying columns in the query tag of the sqoop import into the hive external table. Had to have three different sqoop imports for the three periods. Anyway got them all together so nobody will notice the change :)
--Chalcy
From: Chalcy Raja [mailto:Chalcy.Raja@careerbuilder.com]
Sent: Monday, April 30, 2012 9:08 PM
To: user@hive.apache.org; ashwanthkumar@googlemail.com
Subject: RE: add column to hive table
Good idea! Ashwanth. See if this can work for me and let you know.
Thanks,
CHalcy
From: ashwanth.kumar@gmail.com [mailto:ashwanth.kumar@gmail.com] On Behalf Of Ashwanth Kumar
Sent: Monday, April 30, 2012 6:55 PM
To: user@hive.apache.org
Subject: Re: add column to hive table
You can only add columns at the end. If you are using the native SerDe try
ALTER TABLE tbl_name REPLACE COLUMNS (column1 int, mynewcolumn string, column2 int);
More information on Hive Wiki - https://cwiki.apache.org/Hive/languagemanual-ddl.html#LanguageManualDDL-Add%252FReplaceColumns
On Tue, May 1, 2012 at 4:12 AM, Chalcy Raja <Ch...@careerbuilder.com>> wrote:
Hi all,
Adding a column by alter table mytable add column (mynewcolumn string); works well for me. This adds a field to the end of the schema.
Is there a way to add a column in between two columns?
Thanks,
Chalcy
--
Ashwanth Kumar / ashwanthkumar.in<http://ashwanthkumar.in/>
RE: add column to hive table
Posted by Chalcy Raja <Ch...@careerbuilder.com>.
Good idea! Ashwanth. See if this can work for me and let you know.
Thanks,
CHalcy
From: ashwanth.kumar@gmail.com [mailto:ashwanth.kumar@gmail.com] On Behalf Of Ashwanth Kumar
Sent: Monday, April 30, 2012 6:55 PM
To: user@hive.apache.org
Subject: Re: add column to hive table
You can only add columns at the end. If you are using the native SerDe try
ALTER TABLE tbl_name REPLACE COLUMNS (column1 int, mynewcolumn string, column2 int);
More information on Hive Wiki - https://cwiki.apache.org/Hive/languagemanual-ddl.html#LanguageManualDDL-Add%252FReplaceColumns
On Tue, May 1, 2012 at 4:12 AM, Chalcy Raja <Ch...@careerbuilder.com>> wrote:
Hi all,
Adding a column by alter table mytable add column (mynewcolumn string); works well for me. This adds a field to the end of the schema.
Is there a way to add a column in between two columns?
Thanks,
Chalcy
--
Ashwanth Kumar / ashwanthkumar.in<http://ashwanthkumar.in/>