HBase Phoenix Cause and Solution for dummy column "_0" or "Column Family:_0"

Cause -
This dummy column is added by Phoenix- if someone created Phoenix table on top of existing HBase table.

Solutions- 

Following solutions can be used to delete _0 column from each row - 

• Execute Unix command like below - 

echo "scan 'ns:tbl1', {COLUMNS => 'cf:_0'}" |hbase shell | grep "column=cf:_0" | cut -d' ' -f 2 | awk '{$1=$1};1'|sed -e 's/^/delete '"'"'ns:tbl1'"'"', '"'"'/' -e 's/$/'"'"', '"'"'cf:_0'"'"'/'  | hbase shell

Above command will scan rows which has these columns and prepare delete statements and execute them to remove _0 column.

But, above will not give a good performance in case of bigger tables.

-------------------------------------------------------------------------------------------------------------------------

• Other Solution can be executed from Phoenix Shell 
• Drop the respective Phoenix table for given HBase table. Refer step below - to drop a Phoenix table without droping HBase Table
• Recreate the Phoenix table. But, specify "_0" column. For example - 

create table "ns:tbl1"(k VARCHAR primary key, "cf"."name" VARCHAR, "cf"."_0" VARCHAR);

• Drop the column by executing SQL like below - 

alter table "ns:tbl1" drop column "cf"."_0";

• If above fails then add column  again to table definition and retry delete. 

alter table "ns:tbl1" add "cf"."_0" VARCHAR;

Note - Failure can be due to Client Timeout or RowTooBigException. Take assistance from admin team to change respective cluster configurations and bounce HBase Master.

• Once drop column succeeds then drop Phoenix table. Follow below steps, this will drop Phoenix table but will not drop HBase table.

delete from SYSTEM.CATALOG where TABLE_NAME='ns:tbl1';

Note - This will require bounce of Region Server that host regions for table SYSTEM.CATALOG

Refer - https://community.cloudera.com/t5/Community-Articles/How-to-delete-a-Phoenix-Table-created-on-Top-of-Existing/ta-p/247781

• Post that remove Phoenix coo-processors from HBase table by executing below HBase commands

alter 'ns:tbl1' , METHOD => 'table_att_unset',NAME => 'coprocessor$5'

-------------------------------------------------------------------------------------------------------------------------

• Refer https://techdevins.blogspot.com/2019/11/hbase-bulk-delete-column-qualifiers.html