[jira] [Created] (PHOENIX-2134) Secondary index on existing hbase table not updating

["davidson hutton (JIRA)" <ji...@apache.org>]

davidson hutton created PHOENIX-2134:
----------------------------------------

             Summary: Secondary index on existing hbase table not updating
                 Key: PHOENIX-2134
                 URL: https://issues.apache.org/jira/browse/PHOENIX-2134
             Project: Phoenix
          Issue Type: Bug
    Affects Versions: 4.3.1
         Environment: hbase 1.0.0 ; CDH 5.4.0; Centos 6.6
            Reporter: davidson hutton


We are trying to use a phoenix secondary index on an existing hbase table.
When we insert a row in the underlying hbase table, the new row is not inserted in the phoenix secondary index.
If we drop the secondary phoenix index, the 3rd row is visible in phoenix.

Hbase is from 1.0.0 (installed as part of Cloudera 5.4.0)
Phoenix is 4.3.0-1 (downloaded from Cloudera)

Define hbase table from shell and put 2 rows.

hbase(main):040:0> create 't1', {NAME => 'cf1', VERSIONS => 1}
0 row(s) in 0.4200 seconds

=> Hbase::Table - t1
hbase(main):041:0> put 't1','r1','cf1:c1','r1cf1c1value'
0 row(s) in 0.0150 seconds

hbase(main):042:0> put 't1','r2','cf1:c1','r2cf1c1value'
0 row(s) in 0.0100 seconds

hbase(main):043:0> put 't1','r2','cf1:c2','r2cf1c2value'
0 row(s) in 0.0080 seconds

scan 't1'
ROW                       COLUMN+CELL
 r1                       column=cf1:c1, timestamp=1437498142975, value=r1cf1c1value
 r2                       column=cf1:c1, timestamp=1437498151146, value=r2cf1c1value
 r2                       column=cf1:c2, timestamp=1437498162417, value=r2cf1c2value
2 row(s) in 0.0280 seconds

Create phoenix table on top of hbase table.

0: jdbc:phoenix:localhost> create table "t1" (pk VARCHAR PRIMARY KEY, "cf1"."c1" VARCHAR, "cf1"."c2" VARCHAR)
. . . . . . . . . . . . .> ;
2 rows affected (5.436 seconds)
0: jdbc:phoenix:localhost> select * from "t1";
+------------------------------------------+-----------------------------------+
|                    PK                    |                    c1             |
+------------------------------------------+-----------------------------------+
| r1                                       | r1cf1c1value                      |
| r2                                       | r2cf1c1value                      |
+------------------------------------------+-----------------------------------+
2 rows selected (0.446 seconds)
0: jdbc:phoenix:localhost>

See the rows Phoenix added to hbase table

hbase(main):048:0> scan 't1'
ROW                       COLUMN+CELL
 r1                       column=cf1:_0, timestamp=1437498142975, value=
 r1                       column=cf1:c1, timestamp=1437498142975, value=r1cf1c1value
 r2                       column=cf1:_0, timestamp=1437498162417, value=
 r2                       column=cf1:c1, timestamp=1437498151146, value=r2cf1c1value
 r2                       column=cf1:c2, timestamp=1437498162417, value=r2cf1c2value
2 row(s) in 0.0230 seconds

Create index on table

0: jdbc:phoenix:localhost> create index "t1_index" on "t1" ("cf1"."c1");

Verify index exists and is usable

0: jdbc:phoenix:localhost> explain select "c1" from "t1" where "cf1"."c1"='r2cf1c1value';
+------------------------------------------+
|                   PLAN                   |
+------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER t1_index ['r2cf1c1value'] |
|     SERVER FILTER BY FIRST KEY ONLY      |
+------------------------------------------+
2 rows selected (0.039 seconds)

0: jdbc:phoenix:localhost> select "c1" from "t1" where "cf1"."c1"='r2cf1c1value';
+------------------------------------------+
|                    c1                    |
+------------------------------------------+
| r2cf1c1value                             |
+------------------------------------------+
1 row selected (0.062 seconds)
0: jdbc:phoenix:localhost>

Put 3rd row in hbase table

hbase(main):057:0> put 't1','r3','cf1:c1','r3cf1c1value'
0 row(s) in 0.0080 seconds

hbase(main):058:0> scan 't1'
ROW                       COLUMN+CELL
 r1                       column=cf1:_0, timestamp=1437498559626, value=
 r1                       column=cf1:c1, timestamp=1437498559626, value=r1cf1c1value
 r2                       column=cf1:_0, timestamp=1437498570189, value=
 r2                       column=cf1:c1, timestamp=1437498564413, value=r2cf1c1value
 r2                       column=cf1:c2, timestamp=1437498570189, value=r2cf1c2value
 r3                       column=cf1:c1, timestamp=1437498929197, value=r3cf1c1value
3 row(s) in 0.0150 seconds

Use secondary index to get 3rd row

0: jdbc:phoenix:localhost> select "c1" from "t1" where "cf1"."c1"='r3cf1c1value';
+------------------------------------------+
|                    c1                    |
+------------------------------------------+
+------------------------------------------+
No rows selected (0.051 seconds)

Scan index from hbase to verify 3rd row not in index

hbase(main):059:0> scan 't1_index'
ROW                       COLUMN+CELL
 r1cf1c1value\x00r1       column=0:_0, timestamp=1437498705264, value=
 r2cf1c1value\x00r2       column=0:_0, timestamp=1437498705264, value=
2 row(s) in 0.0180 seconds

Drop secondary index and select 3rd row.

0: jdbc:phoenix:localhost> drop index "t1_index" on "t1"
. . . . . . . . . . . . .> ;
No rows affected (1.634 seconds)
0: jdbc:phoenix:localhost> select "c1" from "t1" where "cf1"."c1"='r3cf1c1value';
+------------------------------------------+
|                    c1                    |
+------------------------------------------+
| r3cf1c1value                             |
+------------------------------------------+
1 row selected (0.209 seconds)




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)