Phoenix Development Guide

Introduction

The Apache Phoenix project was open sourced by saleforce and contributed to the Apache Foundation, and it is now a top-level project of the Apache Foundation. It is an SQL middlware built on top of HBase. Phoenix compiles SQL queries written by users into a series of scan operations, and finally returns a universal JDBC result set to the client. Small-scale queries can be returned in milliseconds, and tens of millions of data can be responded in seconds.

Usage

After the installation of Phoenix is completed, go to the Phoenix client directory and use the command line tool:

Select the cluster master or core node, enter the client directory

# Here we choose the master1 node
cd /home/hadoop/phoenix/bin

Use the Phoenix Python CLI tool

./sqlline.py uhadoop-xxx-master1:2181


0: jdbc:phoenix:uhadoop-xxxx-master1>

Use SQL to manipulate Phoenix tables

Refer to the documentation: https://phoenix.apache.org/language/index.html#create

tips:

Phoenix will automatically convert table names and field names to uppercase letters. If you do not want to convert, you can use double quotes to enclose field or table names

Use help, view all Phoenix commands

 0: jdbc:phoenix> !help

View table list

 0: jdbc:phoenix> !table

Create a new table

 0: jdbc:phoenix:> create table test (
                    mykey integer not null primary key, 
                    mycolumn varchar
                );

Insert data

0: jdbc:phoenix:> upsert into test values (1,'uhadoop');
0: jdbc:phoenix:> upsert into test values (2,'phoenix');

Check table content

0: jdbc:phoenix:> select * from test;

Delete table content

0: jdbc:phoenix:> delete from test where mykey=1;

Create index for table

0: jdbc:phoenix:> create local index test_idx on test(mycolumn);

View table index information

0: jdbc:phoenix:> !indexes test

Delete table

0: jdbc:phoenix:> drop table if exists test;

Exit client

0: jdbc:phoenix:>!exit

Establish table mapping relationship with existing HBase table using Phoenix

4.1 Enter Hbase shell command line to create a table and insert data

[root@uhadoop-xxx-master1 bin]# hbase shell

hbase(main):009:0> create 'phoenix','info'

hbase(main):010:0> put 'phoenix', 'row001','info:name','phoenix'

hbase(main):011:0> scan 'phoenix'

4.2 Enter the Phoenix Python client, establish the mapping relationship

tips:

1.Phoenix 4.10 and above versions, Phoenix has changed the encoding format for columns (official document), so when establishing the mapping relationship with hbase, you need to set the COLUMN_ENCODED_BYTES property to 0, that is, do not let Phoenix encode column family.

2.The default primary key column name of HBase data table is ROW

3.To prevent Phoenix from automatically converting lowercase columns to uppercase, you need to enclose columns in double quotes

4.If the mapping relationship is successfully established, you can see how many rows of data are affected after the table is created

Create table mapping

0: jdbc:phoenix:> create table "phoenix"("ROW" varchar primary key, "info"."name" varchar) column_encoded_bytes=0;
1 row affected (6.523 seconds)

Delete table (Note, the table in hbase will be deleted simultaneously)

0: jdbc:phoenix:> drop table "phoenix";

Establish a view mapping relationship with an existing HBase table using Phoenix

If the Phoenix table is just a query operation, establishing a table mapping is a better choice. Establishing a table mapping, if the table is deleted in the Phoenix client, the corresponding table in hbase will also be deleted. If you use view mapping, deleting the view does not affect the original table data.

5.1 Create a view mapping

# If the phoenix table does not exist in hbase, it is necessary to create a table in hbase according to section 4.1
0: jdbc:phoenix:> create view "phoenix"("ROW" varchar primary key, "info"."name" varchar);

5.2 Query view

0: jdbc:phoenix:>select * from "phoenix";

5.3 Delete view

0: jdbc:phoenix:> drop view "phoenix";

Presto Development Guide Zeppelin Development Guide