Map with existing HBase tables
The section Use SQLLine to work with Phoenix describes how to create new tables in HBase via Phoenix. But you can also use Phoenix to work with existing HBase tables in a reading mode. Views are designed for this purpose. When creating them, the original HBase metadata is left AS-IS.
Let’s consider an example with the people_ages table. Use the HBase shell to check if the table exists:
listThe output is similar to the following:
TABLE BEST_BOOKS SYSTEM.CATALOG SYSTEM.FUNCTION SYSTEM.LOG SYSTEM.MUTEX SYSTEM.SEQUENCE SYSTEM.STATS ns1:temp2 people_ages t4 t5 t6 table10 temp1 temp10 wordcount wordcount2 wordcount3 wordcount4 19 row(s) Took 0.0428 seconds => ["BEST_BOOKS", "SYSTEM.CATALOG", "SYSTEM.FUNCTION", "SYSTEM.LOG", "SYSTEM.MUTEX", "SYSTEM.SEQUENCE", "SYSTEM.STATS", "ns1:temp2", "people_ages", "t4", "t5", "t6", "table10", "temp1", "temp10", "wordcount", "wordcount2", "wordcount3", "wordcount4"]
This table is not available in Phoenix yet. Check it using the appropriate SQLLine command:
!tablesThe output does not contain people_ages:
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE | +------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------+ | | SYSTEM | CATALOG | SYSTEM TABLE | | | | | | false | | | SYSTEM | FUNCTION | SYSTEM TABLE | | | | | | false | | | SYSTEM | LOG | SYSTEM TABLE | | | | | | true | | | SYSTEM | SEQUENCE | SYSTEM TABLE | | | | | | false | | | SYSTEM | STATS | SYSTEM TABLE | | | | | | false | | | | BEST_BOOKS | TABLE | | | | | | false | +------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------+
To create a new view in Phoenix, run the following command using SQLLine:
CREATE VIEW "people_ages" ("pk" VARCHAR PRIMARY KEY, "basic"."age" UNSIGNED_SMALLINT);Now, you can select data in Phoenix from the original table in HBase:
SELECT * FROM "people_ages" LIMIT 10;The output:
+--------------------+--------+ | pk | age | +--------------------+--------+ | Abbott Delia | 13874 | | Abbott Howard | 12852 | | Abbott Jack | 12857 | | Adams Clyde | 12857 | | Aguilar Myrtie | 12851 | | Aguilar Terry | 13877 | | Alexander Derrick | 13366 | | Alexander Gregory | 13620 | | Alexander Leon | 13362 | | Allen Austin | 13108 | +--------------------+--------+ 10 rows selected (0.029 seconds)
Get the number of rows:
SELECT COUNT(*) FROM "people_ages";The output:
+-----------+ | COUNT(1) | +-----------+ | 997 | +-----------+ 1 row selected (0.039 seconds)
If you get the list of Phoenix tables again, you can see the added view people_ages. Later you can delete it, if necessary, using the DROP VIEW command.
!tablesThe output:
+------------+--------------+--------------+---------------+----------+------------+----------------------------+-----------------+--------------+----------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABL | +------------+--------------+--------------+---------------+----------+------------+----------------------------+-----------------+--------------+----------+ | | SYSTEM | CATALOG | SYSTEM TABLE | | | | | | false | | | SYSTEM | FUNCTION | SYSTEM TABLE | | | | | | false | | | SYSTEM | LOG | SYSTEM TABLE | | | | | | true | | | SYSTEM | SEQUENCE | SYSTEM TABLE | | | | | | false | | | SYSTEM | STATS | SYSTEM TABLE | | | | | | false | | | | BEST_BOOKS | TABLE | | | | | | false | | | | people_ages | VIEW | | | | | | false | +------------+--------------+--------------+---------------+----------+------------+----------------------------+-----------------+--------------+----------+
Notice that Phoenix views are designed only for reading. If you try to modify data in a view, you will get an error message similar to the following.
The error message
0: jdbc:phoenix:> UPSERT INTO "people_ages" values('Test Test', 100);
Error: ERROR 505 (42000): Table is read only. (state=42000,code=505)
org.apache.phoenix.schema.ReadOnlyTableException: ERROR 505 (42000): Table is read only.
at org.apache.phoenix.compile.UpsertCompiler.compile(UpsertCompiler.java:359)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:784)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:770)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:401)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:391)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:390)
at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:378)
at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1825)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:813)
at sqlline.SqlLine.begin(SqlLine.java:686)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:291)