You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by Ayola Jayamaha <ra...@gmail.com> on 2018/11/28 08:53:23 UTC
System.Catalog Table
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/
Re: System.Catalog Table
Posted by William Shen <wi...@marinsoftware.com>.
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 <cl...@gmail.com>
wrote:
> Could you reproduce this problem? What is your Pheonix's version number?
>
> ----------------------------------------
> Jaanai Zhang
> Best regards!
>
>
>
> Ayola Jayamaha <ra...@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/
> >
>
Re: System.Catalog Table
Posted by Jaanai Zhang <cl...@gmail.com>.
Could you reproduce this problem? What is your Pheonix's version number?
----------------------------------------
Jaanai Zhang
Best regards!
Ayola Jayamaha <ra...@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/
>