Tags

, , , , , , ,

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

phoenix_hbase_view_1

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

!tables

phoenix_hbase_view_2

Now we can perform select operations on the table

SELECT * FROM "users_data";

phoenix_hbase_view_3

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.

phoenix_hbase_view_4

phoenix_hbase_view_5

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

DROP VIEW IF EXISTS "users_data";

phoenix_hbase_view_6

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

Update:

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

Advertisements