Phoenix说明文档.docx

Phoenix说明文档一、 Phoenix简介Phoenix ：使用SQL操作NoSql数据库，提供java操作hbase的API。Phoenix大数据中的结构图：Apache Phoenix 官方站点：/ Phoenix支持的sql语句： /language/index.html Phoenix 支持的DataTypes：/language/datatypes.html Phoenix 支持的函数：/language/functions.htmlPhoenix 支持事务（测试版）：/transactions.html Phoenix快速入门：/Phoenix-in-15-minutes-or-less.html Phoenix 下载网址：/dyn/closer.lua/phoenix/ Phoenix 测试语句（SQL语句）：http:/phoenix-bin.github.io/client/performance/latest.htm#AGGREGATION_TABLE二、 Phoenix支持的数据类型数据范围|Data TypesphoenixJAVA-2147483648 to 2147483647.INTEGERjava.lang.Integer0 to 2147483647UNSIGNED_INTjava.lang.Integer-9223372036854775807 to 9223372036854775807BIGINTjava.lang.Long0 to 9223372036854775807UNSIGNED_LONGjava.lang.Long-128 to 127TINYINTjava.lang.Byte0 to 127UNSIGNED_TINYINTjava.lang.Byte-32768 to 32767SMALLINTjava.lang.Short0 to 32767UNSIGNED_SMALLINTjava.lang.Short-3.402823466E+38 to 3.402823466 E + 38FLOATjava.lang.Float0 to 3.402823466 E + 38UNSIGNED_FLOATjava.lang.Float-1.7976931348623158 E + 308 to 1.7976931348623158 E + 308DOUBLEjava.lang.Double0 to 1.7976931348623158 E + 308UNSIGNED_DOUBLEjava.lang.Double最大精度为38位数DECIMALjava.math.BigDecimalTRUE and FALSEBOOLEANjava.lang.Booleanformat is yyyy-MM-dd hh:mm:ssTIMEjava.sql.Timeyyyy-MM-dd hh:mm:ssDATEjava.sql.Dateyyyy-MM-dd hh:mm:ss.nnnnnnnnn(12 bytes: an 8 byte long for the epoch time plus a 4 byte integer for the nanos)TIMESTAMPjava.sql.Timestampyyyy-MM-dd hh:mm:ss(an 8 byte long (the number of milliseconds from the epoch)UNSIGNED_TIMEjava.sql.Timeyyyy-MM-dd hh:mm:ssUNSIGNED_DATEjava.sql.Dateyyyy-MM-dd hh:mm:ss.nnnnnnnnnUNSIGNED_TIMESTAMPjava.sql.TimestampVARCHARVARCHAR(255)VARCHARjava.lang.StringCHAR(10)CHARjava.lang.StringRaw fixed length byte array.BINARYbyteRaw variable length byte arrayVARBINARYbyteVARCHAR ARRAYCHAR(10) ARRAY 5 INTEGER INTEGER 100ARRAYjava.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:1: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;public static void query() Connection conn = null; try conn = TestPhoenix.GetConnection(); if (conn = null) System.out.println(conn is 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语句参考文档：/language/index.html (1) 创建表CREATE TABLE IF NOT EXISTS us_population ( state CHAR(2) NOT NULL, city VARCHAR NOT NULL, population BIGINT CONSTRAINT my_pk PRIMARY KEY (state, city);(2) 查询数据SELECT state as State,count(city) as City Count,sum(population) as Population SumFROM us_populationGROUP BY stateORDER BY sum(population) DESC;SELECT * FROM TEST LIMIT 1000;SELECT full_name FROM SALES_PERSON WHERE ranking = 5.0 UNION ALL SELECT reviewer_name FROM CUSTOMER_REVIEW WHERE score = 8.0SELECT * 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, FROM DEPT d JOIN EMPL e ON e.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 OON 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 100UPSERT INTO foo SELECT * FROM bar;(5) 更新表ALTER TABLE my_table SET IMMUTABLE_ROWS=falseALTER TABLE my_schema.my_table ADD d.dept_id char(10) VERSIONS=10ALTER TABLE my_table ADD dept_name cha