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

Posted to user@phoenix.apache.org by Skanda <sk...@gmail.com> on 2014/03/17 10:39:59 UTC

issue with batch upsert for tables with secondary indexes

Hi,

I have a table with immutable secondary indexes. When I do a batch upsert,
I get the following exception:

org.apache.phoenix.schema.IllegalDataException: java.sql.SQLException:
ERROR 1027 (42Y86): Delete not allowed on a table with IMMUTABLE_ROW
with non PK column in index. tableName=uh_repo
	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:198)
	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:179)
	at org.apache.phoenix.execute.MutationState.commit(MutationState.java:333)
	at org.apache.phoenix.jdbc.PhoenixConnection.commit(PhoenixConnection.java:293)
	at com.yumecorp.pig.udf.storage.phoenix.YumePhoenixHBaseStorage$PhoenixOutputFormat$1.commit(YumePhoenixHBaseStorage.java:449)
	at com.yumecorp.pig.udf.storage.phoenix.YumePhoenixHBaseStorage$PhoenixOutputFormat$1.commitTask(YumePhoenixHBaseStorage.java:400)
	at org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.PigOutputCommitter.commitTask(PigOutputCommitter.java:277)
	at org.apache.hadoop.mapred.Task.commit(Task.java:1014)
	at org.apache.hadoop.mapred.Task.done(Task.java:884)
	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:334)
	at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:396)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
	at org.apache.hadoop.mapred.Child.main(Child.java:262)
Caused by: java.sql.SQLException: ERROR 1027 (42Y86): Delete not
allowed on a table with IMMUTABLE_ROW with non PK column in index.
tableName=uh_repo
	at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:136)
	at org.apache.phoenix.util.IndexUtil.generateIndexData(IndexUtil.java:182)
	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:196)
	... 14 more

I don't understand why phoenix has to perform a delete while doing a
batch upsert. Pls note that I'm using PhoenixStorage in Pig which does
a batch upsert of records into hbase.

Further, I tried doing a count(*) in both the main table and the
secondary index tables. They don't match. (ideally it should have been
the same). Is it the reason behind phoenix trying to perform a delete
to keep the main and secondary tables in sync?

Regards,

Skanda

Re: issue with batch upsert for tables with secondary indexes

Posted by Skanda <sk...@gmail.com>.

Thanks a lot, James!


On Tue, Mar 18, 2014 at 2:06 AM, James Taylor <ja...@apache.org>wrote:

> Skanda,
> I filed https://issues.apache.org/jira/browse/PHOENIX-863 on your behalf
> and committed a fix in 3.0,4.0, and master branch that relaxes the times
> when we through this exception. Would you mind letting me know if this
> solves the issue you were running into?
> Thanks,
> James
>
>
> On Mon, Mar 17, 2014 at 9:59 AM, James Taylor <ja...@apache.org>wrote:
>
>> Good find, Skanda. In the case of a table with immutable rows, we don't
>> need to do that delete. In the general case, though, we do, as by setting
>> the column to null, the prior value needs to be removed.
>>
>> Would you mind filing a JIRA?
>>
>> Thanks,
>> James
>>
>>
>> On Mon, Mar 17, 2014 at 7:20 AM, Skanda <sk...@gmail.com>wrote:
>>
>>> Hi,
>>>
>>> I just went through the code and found that phoneix sets a delete
>>> mutation, if a column is null for a particular rowkey.
>>>
>>>  if (rowEntry.getValue() == null) { // means delete
>>>                 row.delete();
>>>             }
>>>
>>> I don't understand why a column has to be deleted when its value is null
>>> as part of a upsert. According to my understanding, an exception can be
>>> thrown or the column can be skipped.
>>> Please correct me if i am wrong.
>>>
>>> Regards,
>>> Skanda
>>>
>>>
>>> On Mon, Mar 17, 2014 at 3:09 PM, Skanda <sk...@gmail.com>wrote:
>>>
>>>> Hi,
>>>>
>>>> I have a table with immutable secondary indexes. When I do a batch
>>>> upsert, I get the following exception:
>>>>
>>>> org.apache.phoenix.schema.IllegalDataException: java.sql.SQLException: ERROR 1027 (42Y86): Delete not allowed on a table with IMMUTABLE_ROW with non PK column in index. tableName=uh_repo
>>>> 	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:198)
>>>> 	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:179)
>>>> 	at org.apache.phoenix.execute.MutationState.commit(MutationState.java:333)
>>>> 	at org.apache.phoenix.jdbc.PhoenixConnection.commit(PhoenixConnection.java:293)
>>>> 	at com.yumecorp.pig.udf.storage.phoenix.YumePhoenixHBaseStorage$PhoenixOutputFormat$1.commit(YumePhoenixHBaseStorage.java:449)
>>>> 	at com.yumecorp.pig.udf.storage.phoenix.YumePhoenixHBaseStorage$PhoenixOutputFormat$1.commitTask(YumePhoenixHBaseStorage.java:400)
>>>> 	at org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.PigOutputCommitter.commitTask(PigOutputCommitter.java:277)
>>>> 	at org.apache.hadoop.mapred.Task.commit(Task.java:1014)
>>>> 	at org.apache.hadoop.mapred.Task.done(Task.java:884)
>>>> 	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:334)
>>>> 	at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
>>>> 	at java.security.AccessController.doPrivileged(Native Method)
>>>> 	at javax.security.auth.Subject.doAs(Subject.java:396)
>>>> 	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
>>>> 	at org.apache.hadoop.mapred.Child.main(Child.java:262)
>>>> Caused by: java.sql.SQLException: ERROR 1027 (42Y86): Delete not allowed on a table with IMMUTABLE_ROW with non PK column in index. tableName=uh_repo
>>>> 	at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:136)
>>>> 	at org.apache.phoenix.util.IndexUtil.generateIndexData(IndexUtil.java:182)
>>>> 	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:196)
>>>> 	... 14 more
>>>>
>>>> I don't understand why phoenix has to perform a delete while doing a batch upsert. Pls note that I'm using PhoenixStorage in Pig which does a batch upsert of records into hbase.
>>>>
>>>>
>>>>
>>>> Further, I tried doing a count(*) in both the main table and the secondary index tables. They don't match. (ideally it should have been the same). Is it the reason behind phoenix trying to perform a delete to keep the main and secondary tables in sync?
>>>>
>>>>
>>>>
>>>>
>>>> Regards,
>>>>
>>>> Skanda
>>>>
>>>>
>>>>
>>>>
>>>
>>
>

Re: issue with batch upsert for tables with secondary indexes

Posted by James Taylor <ja...@apache.org>.

Skanda,
I filed https://issues.apache.org/jira/browse/PHOENIX-863 on your behalf
and committed a fix in 3.0,4.0, and master branch that relaxes the times
when we through this exception. Would you mind letting me know if this
solves the issue you were running into?
Thanks,
James


On Mon, Mar 17, 2014 at 9:59 AM, James Taylor <ja...@apache.org>wrote:

> Good find, Skanda. In the case of a table with immutable rows, we don't
> need to do that delete. In the general case, though, we do, as by setting
> the column to null, the prior value needs to be removed.
>
> Would you mind filing a JIRA?
>
> Thanks,
> James
>
>
> On Mon, Mar 17, 2014 at 7:20 AM, Skanda <sk...@gmail.com>wrote:
>
>> Hi,
>>
>> I just went through the code and found that phoneix sets a delete
>> mutation, if a column is null for a particular rowkey.
>>
>>  if (rowEntry.getValue() == null) { // means delete
>>                 row.delete();
>>             }
>>
>> I don't understand why a column has to be deleted when its value is null
>> as part of a upsert. According to my understanding, an exception can be
>> thrown or the column can be skipped.
>> Please correct me if i am wrong.
>>
>> Regards,
>> Skanda
>>
>>
>> On Mon, Mar 17, 2014 at 3:09 PM, Skanda <sk...@gmail.com>wrote:
>>
>>> Hi,
>>>
>>> I have a table with immutable secondary indexes. When I do a batch
>>> upsert, I get the following exception:
>>>
>>> org.apache.phoenix.schema.IllegalDataException: java.sql.SQLException: ERROR 1027 (42Y86): Delete not allowed on a table with IMMUTABLE_ROW with non PK column in index. tableName=uh_repo
>>> 	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:198)
>>> 	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:179)
>>> 	at org.apache.phoenix.execute.MutationState.commit(MutationState.java:333)
>>> 	at org.apache.phoenix.jdbc.PhoenixConnection.commit(PhoenixConnection.java:293)
>>> 	at com.yumecorp.pig.udf.storage.phoenix.YumePhoenixHBaseStorage$PhoenixOutputFormat$1.commit(YumePhoenixHBaseStorage.java:449)
>>> 	at com.yumecorp.pig.udf.storage.phoenix.YumePhoenixHBaseStorage$PhoenixOutputFormat$1.commitTask(YumePhoenixHBaseStorage.java:400)
>>> 	at org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.PigOutputCommitter.commitTask(PigOutputCommitter.java:277)
>>> 	at org.apache.hadoop.mapred.Task.commit(Task.java:1014)
>>> 	at org.apache.hadoop.mapred.Task.done(Task.java:884)
>>> 	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:334)
>>> 	at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
>>> 	at java.security.AccessController.doPrivileged(Native Method)
>>> 	at javax.security.auth.Subject.doAs(Subject.java:396)
>>> 	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
>>> 	at org.apache.hadoop.mapred.Child.main(Child.java:262)
>>> Caused by: java.sql.SQLException: ERROR 1027 (42Y86): Delete not allowed on a table with IMMUTABLE_ROW with non PK column in index. tableName=uh_repo
>>> 	at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:136)
>>> 	at org.apache.phoenix.util.IndexUtil.generateIndexData(IndexUtil.java:182)
>>> 	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:196)
>>> 	... 14 more
>>>
>>> I don't understand why phoenix has to perform a delete while doing a batch upsert. Pls note that I'm using PhoenixStorage in Pig which does a batch upsert of records into hbase.
>>>
>>>
>>> Further, I tried doing a count(*) in both the main table and the secondary index tables. They don't match. (ideally it should have been the same). Is it the reason behind phoenix trying to perform a delete to keep the main and secondary tables in sync?
>>>
>>>
>>>
>>> Regards,
>>>
>>> Skanda
>>>
>>>
>>>
>>>
>>
>

Re: issue with batch upsert for tables with secondary indexes

Posted by James Taylor <ja...@apache.org>.

Good find, Skanda. In the case of a table with immutable rows, we don't
need to do that delete. In the general case, though, we do, as by setting
the column to null, the prior value needs to be removed.

Would you mind filing a JIRA?

Thanks,
James


On Mon, Mar 17, 2014 at 7:20 AM, Skanda <sk...@gmail.com> wrote:

> Hi,
>
> I just went through the code and found that phoneix sets a delete
> mutation, if a column is null for a particular rowkey.
>
>  if (rowEntry.getValue() == null) { // means delete
>                 row.delete();
>             }
>
> I don't understand why a column has to be deleted when its value is null
> as part of a upsert. According to my understanding, an exception can be
> thrown or the column can be skipped.
> Please correct me if i am wrong.
>
> Regards,
> Skanda
>
>
> On Mon, Mar 17, 2014 at 3:09 PM, Skanda <sk...@gmail.com>wrote:
>
>> Hi,
>>
>> I have a table with immutable secondary indexes. When I do a batch
>> upsert, I get the following exception:
>>
>> org.apache.phoenix.schema.IllegalDataException: java.sql.SQLException: ERROR 1027 (42Y86): Delete not allowed on a table with IMMUTABLE_ROW with non PK column in index. tableName=uh_repo
>> 	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:198)
>> 	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:179)
>> 	at org.apache.phoenix.execute.MutationState.commit(MutationState.java:333)
>> 	at org.apache.phoenix.jdbc.PhoenixConnection.commit(PhoenixConnection.java:293)
>> 	at com.yumecorp.pig.udf.storage.phoenix.YumePhoenixHBaseStorage$PhoenixOutputFormat$1.commit(YumePhoenixHBaseStorage.java:449)
>> 	at com.yumecorp.pig.udf.storage.phoenix.YumePhoenixHBaseStorage$PhoenixOutputFormat$1.commitTask(YumePhoenixHBaseStorage.java:400)
>> 	at org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.PigOutputCommitter.commitTask(PigOutputCommitter.java:277)
>> 	at org.apache.hadoop.mapred.Task.commit(Task.java:1014)
>> 	at org.apache.hadoop.mapred.Task.done(Task.java:884)
>> 	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:334)
>> 	at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
>> 	at java.security.AccessController.doPrivileged(Native Method)
>> 	at javax.security.auth.Subject.doAs(Subject.java:396)
>> 	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
>> 	at org.apache.hadoop.mapred.Child.main(Child.java:262)
>> Caused by: java.sql.SQLException: ERROR 1027 (42Y86): Delete not allowed on a table with IMMUTABLE_ROW with non PK column in index. tableName=uh_repo
>> 	at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:136)
>> 	at org.apache.phoenix.util.IndexUtil.generateIndexData(IndexUtil.java:182)
>> 	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:196)
>> 	... 14 more
>>
>> I don't understand why phoenix has to perform a delete while doing a batch upsert. Pls note that I'm using PhoenixStorage in Pig which does a batch upsert of records into hbase.
>>
>> Further, I tried doing a count(*) in both the main table and the secondary index tables. They don't match. (ideally it should have been the same). Is it the reason behind phoenix trying to perform a delete to keep the main and secondary tables in sync?
>>
>>
>> Regards,
>>
>> Skanda
>>
>>
>>
>>
>

Re: issue with batch upsert for tables with secondary indexes

Posted by Skanda <sk...@gmail.com>.

Hi,

I just went through the code and found that phoneix sets a delete mutation,
if a column is null for a particular rowkey.

 if (rowEntry.getValue() == null) { // means delete
                row.delete();
            }

I don't understand why a column has to be deleted when its value is null as
part of a upsert. According to my understanding, an exception can be thrown
or the column can be skipped.
Please correct me if i am wrong.

Regards,
Skanda


On Mon, Mar 17, 2014 at 3:09 PM, Skanda <sk...@gmail.com> wrote:

> Hi,
>
> I have a table with immutable secondary indexes. When I do a batch upsert,
> I get the following exception:
>
> org.apache.phoenix.schema.IllegalDataException: java.sql.SQLException: ERROR 1027 (42Y86): Delete not allowed on a table with IMMUTABLE_ROW with non PK column in index. tableName=uh_repo
> 	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:198)
> 	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:179)
> 	at org.apache.phoenix.execute.MutationState.commit(MutationState.java:333)
> 	at org.apache.phoenix.jdbc.PhoenixConnection.commit(PhoenixConnection.java:293)
> 	at com.yumecorp.pig.udf.storage.phoenix.YumePhoenixHBaseStorage$PhoenixOutputFormat$1.commit(YumePhoenixHBaseStorage.java:449)
> 	at com.yumecorp.pig.udf.storage.phoenix.YumePhoenixHBaseStorage$PhoenixOutputFormat$1.commitTask(YumePhoenixHBaseStorage.java:400)
> 	at org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.PigOutputCommitter.commitTask(PigOutputCommitter.java:277)
> 	at org.apache.hadoop.mapred.Task.commit(Task.java:1014)
> 	at org.apache.hadoop.mapred.Task.done(Task.java:884)
> 	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:334)
> 	at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
> 	at java.security.AccessController.doPrivileged(Native Method)
> 	at javax.security.auth.Subject.doAs(Subject.java:396)
> 	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
> 	at org.apache.hadoop.mapred.Child.main(Child.java:262)
> Caused by: java.sql.SQLException: ERROR 1027 (42Y86): Delete not allowed on a table with IMMUTABLE_ROW with non PK column in index. tableName=uh_repo
> 	at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:136)
> 	at org.apache.phoenix.util.IndexUtil.generateIndexData(IndexUtil.java:182)
> 	at org.apache.phoenix.execute.MutationState$1.next(MutationState.java:196)
> 	... 14 more
>
> I don't understand why phoenix has to perform a delete while doing a batch upsert. Pls note that I'm using PhoenixStorage in Pig which does a batch upsert of records into hbase.
>
> Further, I tried doing a count(*) in both the main table and the secondary index tables. They don't match. (ideally it should have been the same). Is it the reason behind phoenix trying to perform a delete to keep the main and secondary tables in sync?
>
> Regards,
>
> Skanda
>
>
>
>