Phoenix说明文档

Phoenix说明文档
一、Phoenix简介

Phoenix ：使用SQL操作NoSql数据库，提供java操作hbase的API。

Phoenix大数据中的结构图：

Apache Phoenix 官方站点：https://phoenix.apache.org/

Phoenix支持的sql语句： https://phoenix.apache.org/language/index.html

Phoenix 支持的DataTypes：https://phoenix.apache.org/language/datatypes.html

Phoenix 支持的函数：https://phoenix.apache.org/language/functions.html

Phoenix 支持事务（测试版）：http://phoenix.apache.org/transactions.html

Phoenix快速入门：https://phoenix.apache.org/Phoenix-in-15-minutes-or-less.html

Phoenix 下载网址：http://www.apache.org/dyn/closer.lua/phoenix/

Phoenix 测试语句（SQL语句）：

http://phoenix-bin.github.io/client/performance/latest.htm#AGGREGATION_TABLE

二、Phoenix支持的数据类型

数据范围|Data Types

phoenix

JAVA

-2147483648 to 2147483647.

INTEGER

java.lang.Integer

0 to 2147483647

UNSIGNED_INT

java.lang.Integer

-9223372036854775807 to 9223372036854775807

BIGINT

java.lang.Long

0 to 9223372036854775807

UNSIGNED_LONG

java.lang.Long

-128 to 127

TINYINT

java.lang.Byte

0 to 127

UNSIGNED_TINYINT

java.lang.Byte

-32768 to 32767

SMALLINT

java.lang.Short

0 to 32767

UNSIGNED_SMALLINT

java.lang.Short

-3.402823466E+38 to 3.402823466 E + 38

FLOAT

java.lang.Float

0 to 3.402823466 E + 38

UNSIGNED_FLOAT

java.lang.Float

-1.7976931348623158 E + 308 to 1.7976931348623158 E + 308

DOUBLE

java.lang.Double

0 to 1.7976931348623158 E + 308

UNSIGNED_DOUBLE

java.lang.Double

最大精度为38位数

DECIMAL

java.math.BigDecimal

TRUE and FALSE

BOOLEAN

java.lang.Boolean

format is yyyy-MM-dd hh:mm:ss

TIME

java.sql.Time

yyyy-MM-dd hh:mm:ss

DATE

java.sql.Date

yyyy-MM-dd hh:mm:ss[.nnnnnnnnn]

(12 bytes: an 8 byte long for the epoch time plus a 4 byte integer for the nanos)

TIMESTAMP

java.sql.Timestamp

yyyy-MM-dd hh:mm:ss(an 8 byte long (the number of milliseconds from the epoch))

UNSIGNED_TIME

java.sql.Time

yyyy-MM-dd hh:mm:ss

UNSIGNED_DATE

java.sql.Date

yyyy-MM-dd hh:mm:ss[.nnnnnnnnn]

UNSIGNED_TIMESTAMP

java.sql.Timestamp

VARCHAR

VARCHAR(255)

VARCHAR

java.lang.String

CHAR(10)

CHAR

java.lang.String

Raw fixed length byte array.

BINARY

byte[]

Raw variable length byte array

VARBINARY

byte[]

VARCHAR ARRAY

CHAR(10) ARRAY [5]

INTEGER []

INTEGER [100]

ARRAY

java.sql.Array

三、JAVA API 操作

1、 先将phoenix的 core.jar包 和 phoenix的client.jar 包放到lib里

2、 创建连接，过程和mysql类似

public static Connection GetConnection() {

         Connection conn = null;         String driver ="org.apache.phoenix.jdbc.PhoenixDriver";         String url ="jdbc:phoenix:192.168.206.21:2181";         try {             Class.forName(driver);         } catch (ClassNotFoundException e) {             e.printStackTrace();         }         if (conn == null) {             try {                 conn =DriverManager.getConnection(url);             } catch (SQLException e) {                 e.printStackTrace();             }         }         return conn;     }     publicstatic void query() {    Connection conn = null;   try {       conn = TestPhoenix.GetConnection();       if (conn == null) {            System.out.println("connis null...");            return;       }       String sql = "select * from user";       PreparedStatement ps = conn.prepareStatement(sql);       ResultSet rs = ps.executeQuery();       if (rs != null) {            while (rs.next()) {                System.out.print(rs.getString("id")+ "\t");            }       }   } catch (SQLException e) {       e.printStackTrace();   } finally {       if (conn != null) {            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();            }       }   }}     //替换sql语句就可以实现增删改查

3、 常用sql语句

参考文档：https://phoenix.apache.org/language/index.html

(1) 创建表

CREATE TABLE IF NOT EXISTS us_population (

                        stateCHAR(2) NOT NULL,                        city VARCHARNOT NULL,                        populationBIGINT                        CONSTRAINTmy_pk PRIMARY KEY (state, city));

(2) 查询数据

SELECT state as “State”,count(city) as “CityCount”,sum(population) as “Population Sum”

FROM us_population

GROUP BY state

ORDER BY sum(population) DESC;

SELECT * FROM TEST LIMIT 1000;

SELECT full_name FROM SALES_PERSON WHERE ranking >= 5.0

UNION ALL SELECT reviewer_nameFROM CUSTOMER_REVIEW WHERE score >= 8.0

SELECT * FROM TEST;

SELECT DISTINCT NAME FROM TEST;

SELECT ID, COUNT(1) FROM TEST GROUP BY ID;

SELECT NAME, SUM(VAL) FROM TEST GROUP BY NAME HAVING COUNT(1) >2;

SELECT d.dept_id,e.dept_id,e.name FROM DEPT d JOIN EMPL e ONe.dept_id = d.dept_id;

SELECT ItemName, O.OrderValue FROM Items JOIN

(SELECT ItemID, sum(Price * Quantity) AS OrderValue

 FROM Orders WHERE CustomerID >'C002' GROUP BY ItemID) AS O

ON Items.ItemID = O.ItemID;

(3) 删除数据

DELETE FROM my_other_table WHERE k=2;

DELETE FROM TEST;

DELETE FROM TEST WHERE ID=123;

DELETE FROM TEST WHERE NAME LIKE ‘foo%’;

(4) 插入和更新数据

UPSERT INTO my_table VALUES (1,’A’);

UPSERT INTO test.targetTable(col1,col2) SELECT col3, col4 FROM test.sourceTable WHERE col5 < 100

UPSERT INTO foo SELECT * FROM bar;

(5) 更新表

ALTER TABLE my_table SET IMMUTABLE_ROWS=false

ALTER TABLE my_schema.my_table ADD d.dept_id char(10) VERSIONS=10

ALTER TABLE my_table ADD dept_name char(50), parent_id char(15) nullprimary key

ALTER TABLE my_table DROP COLUMN d.dept_id, parent_id;

ALTER VIEW my_view DROP COLUMN new_col;

ALTER TABLE my_table SET IMMUTABLE_ROWS=true,DISABLE_WAL=true;

(6) 删除表

ALTER TABLE my_table