You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Sandeep Nemuri <nh...@gmail.com> on 2016/02/22 10:03:44 UTC
Way to alter a column datatype(INTEGER to BIGINT) in phoenix tables?
Hi All,
We have a situation where we have to change the datatype of the existing
column of a phoenix table from INTEGER to BIGINT.
is there a way to alter the datatype without loosing the existing data?
Have tried to take the snapshot of hbase table and restored it in a new
pheonix table with new datatype.
but when selecting the altered columns it throws the following error.
java.lang.RuntimeException: java.sql.SQLException: ERROR 201 (22000):
Illegal data. ERROR 201 (22000): Illegal data. ERROR 201 (22000): Illegal
data. Expected length of *at least 8 bytes, but had 4*
at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2440)
at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074)
at sqlline.SqlLine.print(SqlLine.java:1735)
at sqlline.SqlLine$Commands.execute(SqlLine.java:3683)
at sqlline.SqlLine$Commands.sql(SqlLine.java:3584)
at sqlline.SqlLine.dispatch(SqlLine.java:821)
at sqlline.SqlLine.begin(SqlLine.java:699)
at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441)
at sqlline.SqlLine.main(SqlLine.java:424)
--
* Regards*
* Sandeep Nemuri*
ᐧ
Re: Way to alter a column datatype(INTEGER to BIGINT) in phoenix tables?
Posted by Sandeep Nemuri <nh...@gmail.com>.
Thanks for the response James.
We can turn off the writes id required, But the table has ~100B records.
Selecting the values and upserting them in new table will be challenging
with these many records.
ᐧ
On Mon, Feb 22, 2016 at 2:42 PM, James Heather <ja...@mendeley.com>
wrote:
> If you're able to turn off write access to the table from elsewhere
> temporarily, you could always SELECT the values and store them somewhere
> else (e.g., a new table) for the moment, drop and recreate the column, and
> then UPSERT them back in.
>
> It would be nice to think there's a better way, though. When column name
> indirection is in place, you'll at least be able to do this without needing
> a new table: add new column, copy data, rename old column to something
> else, rename new column to whatever old column was called, drop old column.
> On 22 Feb 2016 9:04 a.m., "Sandeep Nemuri" <nh...@gmail.com> wrote:
>
>> Hi All,
>>
>> We have a situation where we have to change the datatype of the existing
>> column of a phoenix table from INTEGER to BIGINT.
>> is there a way to alter the datatype without loosing the existing data?
>>
>> Have tried to take the snapshot of hbase table and restored it in a new
>> pheonix table with new datatype.
>>
>> but when selecting the altered columns it throws the following error.
>>
>> java.lang.RuntimeException: java.sql.SQLException: ERROR 201 (22000):
>> Illegal data. ERROR 201 (22000): Illegal data. ERROR 201 (22000): Illegal
>> data. Expected length of *at least 8 bytes, but had 4*
>> at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2440)
>> at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074)
>> at sqlline.SqlLine.print(SqlLine.java:1735)
>> at sqlline.SqlLine$Commands.execute(SqlLine.java:3683)
>> at sqlline.SqlLine$Commands.sql(SqlLine.java:3584)
>> at sqlline.SqlLine.dispatch(SqlLine.java:821)
>> at sqlline.SqlLine.begin(SqlLine.java:699)
>> at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441)
>> at sqlline.SqlLine.main(SqlLine.java:424)
>>
>>
>>
>> --
>> * Regards*
>> * Sandeep Nemuri*
>> ᐧ
>>
>
--
* Regards*
* Sandeep Nemuri*
Re: Way to alter a column datatype(INTEGER to BIGINT) in phoenix tables?
Posted by James Heather <ja...@mendeley.com>.
If you're able to turn off write access to the table from elsewhere
temporarily, you could always SELECT the values and store them somewhere
else (e.g., a new table) for the moment, drop and recreate the column, and
then UPSERT them back in.
It would be nice to think there's a better way, though. When column name
indirection is in place, you'll at least be able to do this without needing
a new table: add new column, copy data, rename old column to something
else, rename new column to whatever old column was called, drop old column.
On 22 Feb 2016 9:04 a.m., "Sandeep Nemuri" <nh...@gmail.com> wrote:
> Hi All,
>
> We have a situation where we have to change the datatype of the existing
> column of a phoenix table from INTEGER to BIGINT.
> is there a way to alter the datatype without loosing the existing data?
>
> Have tried to take the snapshot of hbase table and restored it in a new
> pheonix table with new datatype.
>
> but when selecting the altered columns it throws the following error.
>
> java.lang.RuntimeException: java.sql.SQLException: ERROR 201 (22000):
> Illegal data. ERROR 201 (22000): Illegal data. ERROR 201 (22000): Illegal
> data. Expected length of *at least 8 bytes, but had 4*
> at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2440)
> at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074)
> at sqlline.SqlLine.print(SqlLine.java:1735)
> at sqlline.SqlLine$Commands.execute(SqlLine.java:3683)
> at sqlline.SqlLine$Commands.sql(SqlLine.java:3584)
> at sqlline.SqlLine.dispatch(SqlLine.java:821)
> at sqlline.SqlLine.begin(SqlLine.java:699)
> at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441)
> at sqlline.SqlLine.main(SqlLine.java:424)
>
>
>
> --
> * Regards*
> * Sandeep Nemuri*
> ᐧ
>