Why Phoenix always add a extra column (named _0) to hbase when I execute UPSERT command?

Question

When I execute the UPSERT command on apache phoenix, I always see that Phoenix add an extra column (named _0) with an empty value in the hbase, this column(_0) is auto generate by phoenix, but I don't need it, like this:

ROW    COLUMN+CELL                                                                   
abc    column=F:A,timestamp=1451305685300,value=123                                
abc    column=F:_0, timestamp=1451305685300, value=    　# I want to avoid generate this row

Could you tell me how to avoid that? Thank you very much!

Answer 1

"At create time, to improve query performance, an empty key value is added to the first column family of any existing rows or the default column family if no column families are explicitly defined. Upserts will also add this empty key value. This improves query performance by having a key value column we can guarantee always being there and thus minimizing the amount of data that must be projected and subsequently returned back to the client."

Apache Phoenix Documentation

Answer 2

Regarding your question if that is avoidable:

You could work around the problem by adding the following statements at the end of your sql:

ALTER TABLE "<your-table>" ADD "<your-cf>"."_0" VARCHAR(1);
ALTER TABLE "<your-table>" DROP COLUMN "<your-cf>"."_0";

You should only do this if you query some table with phoenix but then access the table with another system that is not aware of this phoenix-specific dummy value.