, , , , , , ,

For latest updates on this post check
my new blog site

Apache Phoenix is an open source, relational database layer on top of noSQL store such as Apache HBase. Phoenix provides a JDBC driver that hides the intricacies of the noSQL store enabling users to create, delete, and alter SQL tables, views, indexes, and sequences; upsert and delete rows singly and in bulk; and query data through SQL.

To use an existing HBase table and data into Apache Phoenix we need to create a view in Phoenix pointing to the HBase table.

Lets say we have created a table users_data in our HBase table with few records in it. The following are commands to create and insert a record into HBase table.

create 'users_data', 'personal_info', 'contact_info'

put 'users_data', 'rowKey1','personal_info:firstName','Prasad'
put 'users_data', 'rowKey1','personal_info:lastName','Khode'
put 'users_data', 'rowKey1','personal_info:gender','male'
put 'users_data', 'rowKey1','contact_info:mail','xxxxxxxxxxx@gmail.com'
put 'users_data', 'rowKey1','contact_info:mobile','xxxxxxxxxx'

To start a terminal interface to execute SQL from the command line, execute the following from Phoenix bin directory:

phoenix-sqlline.py localhost


Now to use the same HBase table and query in Apache Phoenix, we create a view on top of HBase table like below:

CREATE VIEW "users_data" ( ROWKEY VARCHAR PRIMARY KEY, "personal_info"."firstName" VARCHAR, "personal_info"."lastName" VARCHAR, "personal_info"."gender" VARCHAR, "contact_info"."mail" VARCHAR, "contact_info"."mobile" VARCHAR ) ;

To check if the view created successfully use the following command



Now we can perform select operations on the table

SELECT * FROM "users_data";


Now if we insert or update any records in HBase table the same will be reflected in our Phoenix view and we can query in SQL format.



If we want to remove the view then we can issue the following command

DROP VIEW IF EXISTS "users_data";


This will not delete the records from HBase table or will not delete HBase table


In case if you see the below error:

Error: ERROR 505 (42000): Table is read only. (state=42000,code=505)
org.apache.phoenix.schema.ReadOnlyTableException: ERROR 505 (42000): Table is read only.

then instead of creating a view, create table pointing to existing HBase table

CREATE TABLE "users_data" ( ROWKEY VARCHAR PRIMARY KEY, "personal_info"."firstName" VARCHAR, "personal_info"."lastName" VARCHAR, "personal_info"."gender" VARCHAR, "contact_info"."mail" VARCHAR, "contact_info"."mobile" VARCHAR ) ;

Note: In this case, if we drop the table using Phoenix drop command, then it will also drop the table from HBase