phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From William Shen <wills...@marinsoftware.com>
Subject Re: System.Catalog Table
Date Thu, 29 Nov 2018 21:44:11 GMT
We've also run into this problem in Phoenix 4.13
Here are steps to reproduce:

1) create original table in phoenix

CREATE TABLE IF NOT EXISTS "test"."TRACKING_VALUES" (
  "cstId"               BIGINT NOT NULL,
  "cltId"               BIGINT NOT NULL,
  "trkblTp"             VARCHAR NOT NULL,
  "trkblId"             BIGINT NOT NULL,
  "id"                  BIGINT NOT NULL,
  "vl"                  VARCHAR,
  "dstTp"               VARCHAR,
  "crdAt"               TIMESTAMP,
  "crdBy"               BIGINT,
  "updAt"               TIMESTAMP,
  "updBy"               BIGINT,
  "stts"                VARCHAR,
  "lgcyId"              VARCHAR,
  CONSTRAINT "tracking_values_pk" PRIMARY KEY ("cstId", "cltId",
"trkblTp", "trkblId", "id")
)SALT_BUCKETS=10, DEFAULT_COLUMN_FAMILY='TV';

# Respective system.catalog table
0: jdbc:phoenix:labs-kumki-namenode-lv-101,la> select TENANT_ID,
TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, COLUMN_FAMILY from
SYSTEM.CATALOG where table_schem = 'test' and table_name =
'TRACKING_VALUES';
+------------+--------------+------------------+--------------+----------------+
| TENANT_ID  | TABLE_SCHEM  |    TABLE_NAME    | COLUMN_NAME  | COLUMN_FAMILY  |
+------------+--------------+------------------+--------------+----------------+
|            | test         | TRACKING_VALUES  |              |                |
|            | test         | TRACKING_VALUES  |              | TV             |
|            | test         | TRACKING_VALUES  | cltId        |                |
|            | test         | TRACKING_VALUES  | crdAt        | TV             |
|            | test         | TRACKING_VALUES  | crdBy        | TV             |
|            | test         | TRACKING_VALUES  | cstId        |                |
|            | test         | TRACKING_VALUES  | dstTp        | TV             |
|            | test         | TRACKING_VALUES  | id           |                |
|            | test         | TRACKING_VALUES  | lgcyId       | TV             |
|            | test         | TRACKING_VALUES  | stts         | TV             |
|            | test         | TRACKING_VALUES  | trkblId      |                |
|            | test         | TRACKING_VALUES  | trkblTp      |                |
|            | test         | TRACKING_VALUES  | updAt        | TV             |
|            | test         | TRACKING_VALUES  | updBy        | TV             |
|            | test         | TRACKING_VALUES  | vl           | TV             |
+------------+--------------+------------------+--------------+----------------+
15 rows selected (0.079 seconds)

2) Populate data into the table

0: jdbc:phoenix:labs-kumki-namenode-lv-101,la> select * from
"test".TRACKING_VALUES;
+--------+----------+--------------------+---------------------+------------+--------------+--------------+--------------------------+--------+--------------------------+--------+--------------+--------------+
| cstId  |  cltId   |      trkblTp       |       trkblId       |
id     |      vl      |    dstTp     |          crdAt           |
crdBy  |          updAt           | updBy  |     stts     |    lgcyId
  |
+--------+----------+--------------------+---------------------+------------+--------------+--------------+--------------------------+--------+--------------------------+--------+--------------+--------------+
| -42    | 4291717  | KeywordInstance    | 9224773823          |
81606793   | tlm0YryK     | SEARCH       | 2014-04-22 15:24:21.000  |
null   | 2014-04-22 15:24:21.000  | null   |              |
38783873798  |
| -42    | 4291717  | PublisherCreative  | 1971851927          |
81450003   | sRE2Ds8hZ    | SEARCH       | 2014-04-10 20:12:21.000  |
null   | 2014-04-10 20:12:21.000  | null   |              |
38615971930  |
| 100    | 100      | randomValue        | 100                 |
157124916  | randomValue  | randomValue  | 2018-08-16 05:34:42.000  |
100    | 2018-08-16 05:34:42.000  | 100    | randomValue  |
randomValue  |
| 7242   | 62235    | KEYWORD            | 74564665            |
105322310  | Qwerty123    |              | 2017-08-10 18:35:48.000  |
34447  |                          | null   | ACTIVE       |
  |
| 7242   | 64555    | CREATIVE           | 144115188096252274  |
157126212  | 20180903     |              | 2018-09-04 06:36:16.000  |
34447  |                          | null   | ACTIVE       |
  |
+--------+----------+--------------------+---------------------+------------+--------------+--------------+--------------------------+--------+--------------------------+--------+--------------+--------------+
5 rows selected (0.26 seconds)


3) Hbase snapshot

hbase(main):001:0> snapshot 'test.TRACKING_VALUES', 'test-TRACKING_VALUES-SNAP'
0 row(s) in 0.7690 seconds

4) cloned Hbase snapshot

hbase(main):002:0> clone_snapshot 'test-TRACKING_VALUES-SNAP',
'testNew.TRACKING_VALUES'
0 row(s) in 0.6080 seconds

5) Created table in phoenix

0: jdbc:phoenix:labs-kumki-namenode-lv-101,la> CREATE TABLE IF NOT
EXISTS "testNew"."TRACKING_VALUES" (
. . . . . . . . . . . . . . . . . . . . . . .>   "cstId"
BIGINT NOT NULL,
. . . . . . . . . . . . . . . . . . . . . . .>   "cltId"
BIGINT NOT NULL,
. . . . . . . . . . . . . . . . . . . . . . .>   "trkblTp"
VARCHAR NOT NULL,
. . . . . . . . . . . . . . . . . . . . . . .>   "trkblId"
BIGINT NOT NULL,
. . . . . . . . . . . . . . . . . . . . . . .>   "id"
BIGINT NOT NULL,
. . . . . . . . . . . . . . . . . . . . . . .>   "vl"                  VARCHAR,
. . . . . . . . . . . . . . . . . . . . . . .>   "dstTp"               VARCHAR,
. . . . . . . . . . . . . . . . . . . . . . .>   "crdAt"
TIMESTAMP,
. . . . . . . . . . . . . . . . . . . . . . .>   "crdBy"               BIGINT,
. . . . . . . . . . . . . . . . . . . . . . .>   "updAt"
TIMESTAMP,
. . . . . . . . . . . . . . . . . . . . . . .>   "updBy"               BIGINT,
. . . . . . . . . . . . . . . . . . . . . . .>   "stts"                VARCHAR,
. . . . . . . . . . . . . . . . . . . . . . .>   "lgcyId"              VARCHAR,
. . . . . . . . . . . . . . . . . . . . . . .>   CONSTRAINT
"tracking_values_pk" PRIMARY KEY ("cstId", "cltId", "trkblTp",
"trkblId", "id")
. . . . . . . . . . . . . . . . . . . . . . .> )SALT_BUCKETS=10,
DEFAULT_COLUMN_FAMILY='TV';
5 rows affected (0.18 seconds)

# Respective system.catalog table
0: jdbc:phoenix:labs-kumki-namenode-lv-101,la> select TENANT_ID,
TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, COLUMN_FAMILY from
SYSTEM.CATALOG where table_schem = 'testNew' and table_name =
'TRACKING_VALUES';
+------------+--------------+------------------+--------------+----------------+
| TENANT_ID  | TABLE_SCHEM  |    TABLE_NAME    | COLUMN_NAME  | COLUMN_FAMILY  |
+------------+--------------+------------------+--------------+----------------+
|            | testNew      | TRACKING_VALUES  |              |                |
|            | testNew      | TRACKING_VALUES  | cltId        |                |
|            | testNew      | TRACKING_VALUES  | crdAt        | TV             |
|            | testNew      | TRACKING_VALUES  | crdBy        | TV             |
|            | testNew      | TRACKING_VALUES  | cstId        |                |
|            | testNew      | TRACKING_VALUES  | dstTp        | TV             |
|            | testNew      | TRACKING_VALUES  | id           |                |
|            | testNew      | TRACKING_VALUES  | lgcyId       | TV             |
|            | testNew      | TRACKING_VALUES  | stts         | TV             |
|            | testNew      | TRACKING_VALUES  | trkblId      |                |
|            | testNew      | TRACKING_VALUES  | trkblTp      |                |
|            | testNew      | TRACKING_VALUES  | updAt        | TV             |
|            | testNew      | TRACKING_VALUES  | updBy        | TV             |
|            | testNew      | TRACKING_VALUES  | vl           | TV             |
+------------+--------------+------------------+--------------+----------------+
14 rows selected (0.105 seconds)

3) Data comparision

0: jdbc:phoenix:labs-kumki-namenode-lv-101,la> select * from
"test".TRACKING_VALUES;
+--------+----------+--------------------+---------------------+------------+--------------+--------------+--------------------------+--------+--------------------------+--------+--------------+--------------+
| cstId  |  cltId   |      trkblTp       |       trkblId       |
id     |      vl      |    dstTp     |          crdAt           |
crdBy  |          updAt           | updBy  |     stts     |    lgcyId
  |
+--------+----------+--------------------+---------------------+------------+--------------+--------------+--------------------------+--------+--------------------------+--------+--------------+--------------+
| -42    | 4291717  | KeywordInstance    | 9224773823          |
81606793   | tlm0YryK     | SEARCH       | 2014-04-22 15:24:21.000  |
null   | 2014-04-22 15:24:21.000  | null   |              |
38783873798  |
| -42    | 4291717  | PublisherCreative  | 1971851927          |
81450003   | sRE2Ds8hZ    | SEARCH       | 2014-04-10 20:12:21.000  |
null   | 2014-04-10 20:12:21.000  | null   |              |
38615971930  |
| 100    | 100      | randomValue        | 100                 |
157124916  | randomValue  | randomValue  | 2018-08-16 05:34:42.000  |
100    | 2018-08-16 05:34:42.000  | 100    | randomValue  |
randomValue  |
| 7242   | 62235    | KEYWORD            | 74564665            |
105322310  | Qwerty123    |              | 2017-08-10 18:35:48.000  |
34447  |                          | null   | ACTIVE       |
  |
| 7242   | 64555    | CREATIVE           | 144115188096252274  |
157126212  | 20180903     |              | 2018-09-04 06:36:16.000  |
34447  |                          | null   | ACTIVE       |
  |
+--------+----------+--------------------+---------------------+------------+--------------+--------------+--------------------------+--------+--------------------------+--------+--------------+--------------+
5 rows selected (0.221 seconds)
0: jdbc:phoenix:labs-kumki-namenode-lv-101,la> select * from
"testNew".TRACKING_VALUES;
+--------+----------+--------------------+---------------------+------------+-----+--------+--------+--------+--------+--------+-------+---------+
| cstId  |  cltId   |      trkblTp       |       trkblId       |
id     | vl  | dstTp  | crdAt  | crdBy  | updAt  | updBy  | stts  |
lgcyId  |
+--------+----------+--------------------+---------------------+------------+-----+--------+--------+--------+--------+--------+-------+---------+
| -42    | 4291717  | KeywordInstance    | 9224773823          |
81606793   |     |        |        | null   |        | null   |
|         |
| -42    | 4291717  | PublisherCreative  | 1971851927          |
81450003   |     |        |        | null   |        | null   |
|         |
| 100    | 100      | randomValue        | 100                 |
157124916  |     |        |        | null   |        | null   |
|         |
| 7242   | 62235    | KEYWORD            | 74564665            |
105322310  |     |        |        | null   |        | null   |
|         |
| 7242   | 64555    | CREATIVE           | 144115188096252274  |
157126212  |     |        |        | null   |        | null   |
|         |
+--------+----------+--------------------+---------------------+------------+-----+--------+--------+--------+--------+--------+-------+---------+
5 rows selected (0.15 seconds)


4) Even update statistics in phoenix did not solve this
0: jdbc:phoenix:labs-kumki-namenode-lv-101,la> update statistics
"testNew".TRACKING_VALUES;
No rows affected (0.08 seconds)
0: jdbc:phoenix:labs-kumki-namenode-lv-101,la> select * from
"testNew".TRACKING_VALUES;
+--------+----------+--------------------+---------------------+------------+-----+--------+--------+--------+--------+--------+-------+---------+
| cstId  |  cltId   |      trkblTp       |       trkblId       |
id     | vl  | dstTp  | crdAt  | crdBy  | updAt  | updBy  | stts  |
lgcyId  |
+--------+----------+--------------------+---------------------+------------+-----+--------+--------+--------+--------+--------+-------+---------+
| -42    | 4291717  | KeywordInstance    | 9224773823          |
81606793   |     |        |        | null   |        | null   |
|         |
| -42    | 4291717  | PublisherCreative  | 1971851927          |
81450003   |     |        |        | null   |        | null   |
|         |
| 100    | 100      | randomValue        | 100                 |
157124916  |     |        |        | null   |        | null   |
|         |
| 7242   | 62235    | KEYWORD            | 74564665            |
105322310  |     |        |        | null   |        | null   |
|         |
| 7242   | 64555    | CREATIVE           | 144115188096252274  |
157126212  |     |        |        | null   |        | null   |
|         |
+--------+----------+--------------------+---------------------+------------+-----+--------+--------+--------+--------+--------+-------+---------+
5 rows selected (0.122 seconds)


On Wed, Nov 28, 2018 at 11:15 PM Jaanai Zhang <cloud.poster@gmail.com>
wrote:

>  Could you reproduce this problem? What is your Pheonix's version number?
>
> ----------------------------------------
>    Jaanai Zhang
>    Best regards!
>
>
>
> Ayola Jayamaha <raphaelanne2@gmail.com> 于2018年11月28日周三 下午4:53写道:
>
> >   Hi,
> > I have a question regarding system.catelog table
> > How is this table is updated when we clone hbase table and apply ddl in
> > pheonix client?
> > We see a problem when we clone the table. For example a table has 15 rows
> > in system.catalog  we cloned the hbase snapshot and recreated the same
> > table
> > when we check the info in system.catalog about this table we see only 14
> > rows
> > because of this few columns showing null values instead of original
> values.
> >
> > Thank you
> >
> >
> > --
> > Best Regards,
> > Ayola Jayamaha
> > http://ayolajayamaha.blogspot.com/
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message