1、phoenix使用
1)连接sqlline
sqlline.py master,node1,node2
2)常用命令(区分大小写)
# 1、创建表
CREATE TABLE IF NOT EXISTS STUDENT (
id VARCHAR NOT NULL PRIMARY KEY,
name VARCHAR,
age BIGINT,
gender VARCHAR ,
clazz VARCHAR
);# 2、显示所有表
!table# 3、插入数据
upsert into STUDENT values('1500100004','葛德曜',24,'男','理科三班');
upsert into STUDENT values('1500100005','宣谷芹',24,'男','理科六班');
upsert into STUDENT values('1500100006','羿彦昌',24,'女','理科三班');
# 4、查询数据,支持大部分sql语法,
select * from STUDENT ;
select * from STUDENT where age=24;
select gender ,count(*) from STUDENT group by gender;
select * from student order by gender;# 5、删除数据
delete from STUDENT where id='1500100004';
# 6、删除表
drop table STUDENT;
# 7、退出命令行
!quit更多语法参照官网
https://phoenix.apache.org/language/index.html#upsert_select
3)phoenix表映射(将HBase中的表映射到Phoenix中)
1.视图映射(只读,只能用来查询)
2.表映射(关联表被删,源数据原表也会删除)
2、phoenix二级索引
1)全局索引
读多写少
注意:在查询语句中检索的列如果不在索引表中,phoenix将不会使用索引表
2)本地索引
写多读少
注意:无论查询的列是否在索引表中,都会使用索引表
3)覆盖索引(相当于全局索引+数据)
将原数据存储在索引表中
注意:查询时select列和where的列都需要出现在索引中
3、phoenix JDBC
代码中写SQL
package com.shujia;
import java.sql.*;
public class Demo7PhoenixJDBC {
public static void main(String[] args) throws SQLException {
//1、创建连接
Connection conn = DriverManager.getConnection("jdbc:phoenix:master,node1,node2:2181");
//2、创建statement
String sql="select /*+ INDEX(DIANXIN DIANXIN_INDEX_COVER) */ * from DIANXIN where x=? and y =?";
PreparedStatement ps = conn.prepareStatement(sql);
//3、配置参数
ps.setDouble(1,117.233);
ps.setDouble(2,31.833);
//4、执行SQL
ResultSet rs = ps.executeQuery();
//5、获取数据
while(rs.next()){
String mdn = rs.getString("mdn");
String start_date = rs.getString("start_date");
String end_date = rs.getString("end_date");
String county = rs.getString("county");
System.out.println(mdn+","+start_date+","+end_date+","+county);
}
//6、关闭连接
ps.close();
rs.close();
conn.close();
}
}