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