phoenix error: hbase table undefined, even though it is present

Question

I am trying to access my hbase running on my local machine with zookeeper at localhost:2181. I installed phoenix-3.3.1-bin and trying to access an already existing hbase tabe, but could not. So, simply to test, i created a table using phoenix commandline and see it when i run !tables command. but when i run selet command, it shows error.

This is what I am doing and i am using mac, hbase-0.94.26. Same thing is happening with squirrel-sql client also.

0: jdbc:phoenix:localhost> CREATE TABLE stats.prod_metrics ( host char(50) not null, created_date date not null,
. . . . . . . . . . . . .>     txn_count bigint CONSTRAINT pk PRIMARY KEY (host, created_date) );
No rows affected (1.82 seconds)

0: jdbc:phoenix:localhost> !tables
+------------------------------------------+------------------------------------------+------------------------------------------+---------------------------+
|                TABLE_CAT                 |               TABLE_SCHEM                |                TABLE_NAME                |                TABLE_TYPE |
+------------------------------------------+------------------------------------------+------------------------------------------+---------------------------+
|                                          | SYSTEM                                   | CATALOG                                  | SYSTEM TABLE              |
|                                          | SYSTEM                                   | SEQUENCE                                 | SYSTEM TABLE              |
|                                          | SYSTEM                                   | STATS                                    | SYSTEM TABLE              |
|                                          | STATS                                    | PROD_METRICS                             | TABLE                     |
+------------------------------------------+------------------------------------------+------------------------------------------+---------------------------+

0: jdbc:phoenix:localhost> select * from PROD_METRICS;
    Error: ERROR 1012 (42M03): Table undefined. tableName=PROD_METRICS (state=42M03,code=1012)
    org.apache.phoenix.schema.TableNotFoundException: ERROR 1012 (42M03): Table undefined. tableName=PROD_METRICS
        at org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.createTableRef(FromCompiler.java:336)
        at org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.<init>(FromCompiler.java:236)
        at org.apache.phoenix.compile.FromCompiler.getResolverForQuery(FromCompiler.java:159)
        at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:318)
        at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:308)
        at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:225)
        at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:221)
        at org.apache.phoenix.util.PhoenixContextExecutor.call(PhoenixContextExecutor.java:54)
        at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:221)
        at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1059)
        at sqlline.Commands.execute(Commands.java:822)
        at sqlline.Commands.sql(Commands.java:732)
        at sqlline.SqlLine.dispatch(SqlLine.java:808)
        at sqlline.SqlLine.begin(SqlLine.java:681)
        at sqlline.SqlLine.start(SqlLine.java:398)
        at sqlline.SqlLine.main(SqlLine.java:292)

Whatever you have mentioned is correct. Thank you. But. when i do !tables, already existing tables that were created before phoenix installed should also be seen right ? Those tables are not seen here. And moreover, when i create tables in hbase, i don't use any schema explicity such as 'stats' or 'system'. — user553182, Jul 31 '15 at 06:13
The interesting thing is, if a table is created using phoenix, they are seen inside hbase shell. But, if a table is created using hbase shell, they are not seen using phoenix command line interface. I use ./sqlline.py localshost where hbase is running in psuedo distributed mode with zookeepr at 2181. I am trying to see the list of tables on phoenix with command : !tables , Is it right or something am i missing? Why is this unexpected behavior? — user553182, Jul 31 '15 at 09:26
Have you checked out this: http://phoenix.apache.org/faq.html ? Whatever, how have you fixed this? — Jnmgr, Oct 16 '15 at 09:21

score 8 · Answer 1 · answered Feb 10 '16 at 06:09

8

If you created the table in HBase using lowercase letters, you need to enclose the table name in quotations. Otherwise, Phoenix will convert the table name to uppercase and it will not find your table.

answered Feb 10 '16 at 06:09

kliew

3,073
1
14
25

score 1 · Answer 2 · answered Jul 31 '15 at 10:50

1

Phoenix tables (and views) are specially "decorated" hbase tables. I.e. they have coprocessors attached and some extra chunk of meta data, plus they are registered in the Phoenix system catalog, while plain hbase tables are not. So all Phoenix tables are hbase tables, but hbase tables are not necessarily Phoenix tables.

answered Jul 31 '15 at 10:50

Nicholas

15,916
4
42
66

Ok, intersting answer. But, how one can access the tables in phoenix, that were created using plain hbase shell? Is it not possible at all or some thing should be done so as to access them in phoenix? – user553182 Jul 31 '15 at 11:05
2 choices: create a table or create a view. If your plain hbase table already exists and you create a phoenix table or view of the same name, your hbase table will "phoenix-erated" and will become a phoenix table (or view). – Nicholas Jul 31 '15 at 11:53
Ok.But, consider a table created in hbase shell like this: create 'MSG', 'attr'; where, attr is columnfamily that can dynamically take any num of coulmns. Then, 1)how one can create an equvivalant table in phoenix with columnfamily 'attr' ? 2)If i ignore the columnfamily and create a table and upsert some data, i can see that upserted data in hbase shell. But, if i insert data from hbase shell, its not seen in phoenix shell. – user553182 Jul 31 '15 at 14:21
mmmm.... yes, it's not a 1 to 1 sort of thing. HBase naturally supports multiple columns with multiple qualifiers resulting in large number of possible combinations ( I read 10^1000 ). It's not a perfect mapping. I don't have the expertise to advise you on this in any depth, but I recommend you look at Phoenix views and dynamic columns. The later are a decent effort to handle underlying htables that have highly complex familly structures. – Nicholas Jul 31 '15 at 17:15

score 0 · Answer 3 · answered Jan 25 '17 at 10:14

0

I was also facing the same issue. It was due to different version of phoneix-client.jar then the server.

answered Jan 25 '17 at 10:14

Sonu

712
9
7

I was trying to upload the data via CsvBulkUpload utility from phoenix. The exact log message was 'Table undefined. tableName=SYSTEM.CATALOG' – Sonu Jan 25 '17 at 10:15

phoenix error: hbase table undefined, even though it is present

3 Answers3