phoenix:凤凰 hbase的jdbc工具 数据库:schema //ns1 表:table //ns1.t1 sqlline.py s102,s103,s104 phoenix的jdbc编程: driver:org.apache.phoenix.jdbc.PhoenixDriver url: jdbc:phoenix:s102 name: null pass: null conn.setAutoCommit(); //设置自动提交 st.execute(""); unsigned_int //hbase integer //phoenix phoenix性质: ========================== 1、phoenix中默认所有名称均为大写,若想变为小写,需要加上"" select * from "test"."t1"; 2、phoenix是大小写区分的 create table "test"."t3"("id" integer primary key, id integer, "name" varchar, "age" integer); //create table "test"."t3"("id" integer primary key,id integer, "name" varchar, "age" integer); phoenix和hbase表映射: ============================ rowKey f1:callee f1:caller f1:duration f1:time f2:IDCard f2:location f2:signal ns1:t1表映射 ====================================== 1、创建hbase表 ns1:t1 ====>row1 f1:id, f1:name, f1:age row2 f1:id, f1:name, f1:age row3 f1:id, f1:name, f1:age 2、添加incr自增字段 ====> 目的是向hbase表中添加long型数据 incr 'ns1:t1','row1','f1:incr',1 3、phoenix表和hbase表做映射 create table "ns1:t1"("rowKey" varchar primary key, "f1"."id" varchar,"f1"."name" varchar,"f1"."age" varchar, "f1"."incr" unsigned_long ); create table "ns1:t1"("rowKey" varchar primary key, "f1"."id" varchar,"f1"."name" varchar,"f1"."age" varchar, "f1"."incr" bigint ); calllog表映射: =========================================== 1、将hbase的calllog表用phoenix表做映射 create table "calllog"("rowKey" varchar primary key, "f1"."caller" varchar, "f1"."callee" varchar, "f1"."duration" varchar,"f1"."time" varchar ,"f2"."IDCard" varchar, "f2"."location" varchar,"f2"."signal" varchar); 2、修改hbase表数据 upsert into "calllog" values('85,13800006520,20180109122356,0,15100005135','13800001111','15100005135','120','20180109122356','110121xxxxxxxxxxxxx','shahe'); 注意:在做表映射时,务必移除hbase表中所有协处理器。否则可能协处理器会冲突 总结:在创建表的时候,最好使用varchar字段或String(phoenix没有) integer -integer.MAX_VALUE --- integer.MAX_VALUE unsigned_int 0 --- integer.MAX_VALUE //hbase bigint unsigned_long //hbase tinyint unsigned_tinyint //hbase phoenix视图:只读表 ======================================== 相当于伪表: 和表映射的区别在于视图(view)不会被修改 1、向ns1:t3表创建phoenix视图 create view "ns1:t3"("rowKey" varchar primary key, "f1"."id" varchar,"f1"."name" varchar,"f1"."age" varchar, "f1"."incr" unsigned_long); 2、删除视图 drop view "ns1:t3"; phoenix索引: ======================================= 提升扫描效率 索引的value,指向表的key,避免hbase全表扫描 0、修改hbase配置文件:/soft/hbase/conf/hbase-site.xml <property> <name>hbase.regionserver.wal.codec</name> <value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value> </property> 配置完成后分发配置文件 1、重启hbase 2、创建索引 create index calllog_index on "calllog"("caller") ; 3、删除索引 drop index calllog_index on "calllog" ; 4、模糊查询(避免全表扫描) select * from "calllog" where "f1"."caller" like '%111'; phoenix修改表: =============================== 表中添加字段 alter table "ns1:t2" add sex boolean; 视图中添加字段 alter view "ns1:t3" add sex boolean; 表中删除字段 alter table "ns1:t2" drop column sex ; 视图中删除字段 alter view "ns1:t3" drop column sex ; 创建schema ================================= 在phoenix中不能显式创建schema, 可以用过创建表的方式创建schema。 phoenix函数:时间和时间戳互转,时间与字符串互转 ==================================== select now(); //0时区时间,date格式 select cast(now() as bigint) ; //将时间转换成时间戳 select cast(1504934664380 as date) ; //将时间戳转换成时间 //13位时间戳 select to_date('2017/09/09 02:03:04', 'yyyy/MM/dd hh:mm:ss') ; //将时间串转换成时间 //时间串格式需要与pattern相同 select to_char(now() , 'yyyy/MM/dd') ; //将时间转换成时间串,任意格式 phoenix简单聚合函数: ====================================== sum() min() max() avg() select FIRST_VALUE ("age") WITHIN GROUP ( ORDER BY "age" desc); phoenix字符串函数: ======================================== substr() //截串 trim() //去空格