一、通过view映射hbase中的表
1、创建hbase表
hbase(main):005:0> create 'test1','cf1' hbase(main):006:0> put 'test1','rk0001','cf1:NAME','zhang1' hbase(main):007:0> put 'test1','rk0002','cf1:age','20' hbase(main):008:0> put 'test1','rk0002','cf1:NAME','li2' hbase(main):009:0> put 'test1','rk0001','cf1:age','30' hbase(main):010:0> scan 'test1' ROW COLUMN+CELL rk0001 column=cf1:NAME, timestamp=1579069474489, value=zhang1 rk0001 column=cf1:age, timestamp=1579069475656, value=30 rk0002 column=cf1:NAME, timestamp=1579069474551, value=li2 rk0002 column=cf1:age, timestamp=1579069474525, value=20
2、在Phoenix中创建视图
0: jdbc:phoenix:node1> create view "test1"( . . . . . . . . . . .> user_id varchar primary key, . . . . . . . . . . .> "cf1".NAME varchar, . . . . . . . . . . .> "cf1"."age" varchar); #查询数据 0: jdbc:phoenix:node1> select * from "test1"; +----------+---------+------+ | USER_ID | NAME | age | +----------+---------+------+ | rk0001 | zhang1 | 30 | | rk0002 | li2 | 20 | +----------+---------+------+
--phoenix列名区分大小写，建表时不加""会转成大写，加了""就不会转
--列族.列名
2、在Phoenix表中创建索引
我的三台节点上hbase服务情况： node1 HMaster(没有HRegionServer) node2 HRegionServer node2 HRegionServer RegionSever节点!!，修改hbase-site.xml配置文件，加入如下配置，不然建索引会报错： <property> <name>phoenix.query.maxServerCacheBytes</name> <value>2097152000</value> </property> <property> <name>hbase.regionserver.wal.codec</name> <value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value> </property> <property> <name>hbase.region.server.rpc.scheduler.factory.class</name> <value>org.apache.hadoop.hbase.ipc.PhoenixRpcSchedulerFactory</value> </property> <property> <name>hbase.rpc.controllerfactory.class</name> <value>org.apache.hadoop.hbase.ipc.controller.ServerRpcControllerFactory</value> </property> HMaster节点不用设置；
建索引
#"cf1".name 列族.列名 0: jdbc:phoenix:node2> create index test1_name on "test1"("cf1".name) include("cf1"."age"); --通过view映射是只读的 0: jdbc:phoenix:node2> select name,count(1) from "test1" group by name; +---------+-----------+ | NAME | COUNT(1) | +---------+-----------+ | li2 | 1 | | zhang1 | 1 | +---------+-----------+
3、删除视图
0: jdbc:phoenix:node1> drop view "test1"; 在Phoenix中删除视图后，hbase中的表依然还在；
二、通过table映射hbase中的表
1、创建Phoenix表
--建表 create table "test1"( user_id varchar primary key, "cf1".NAME varchar, "cf1"."age" varchar); --建索引 create index test1_name on "test1"("cf1".name) include("cf1"."age");
2、插入数据
--Phoenix中 0: jdbc:phoenix:node1> upsert into "test1" values ('rk0003','haha','123'); 1 row affected (0.141 seconds) 0: jdbc:phoenix:node1> upsert into "test1" values ('rk0004','haha','123'); 1 row affected (0.012 seconds) 0: jdbc:phoenix:node1> select * from "test1"; +----------+---------+------+ | USER_ID | NAME | age | +----------+---------+------+ | rk0003 | haha | 123 | | rk0004 | haha | 123 | | rk0002 | li2 | 20 | | rk0001 | zhang1 | 30 | +----------+---------+------+ --查看hbase中的数据，已经有新增的数据了 hbase(main):004:0> scan 'test1' ROW COLUMN+CELL rk0001 column=cf1:NAME, timestamp=1579069474489, value=zhang1 rk0001 column=cf1:_0, timestamp=1579069475656, value= #此条是映射产生的 rk0001 column=cf1:age, timestamp=1579069475656, value=30 rk0002 column=cf1:NAME, timestamp=1579069474551, value=li2 rk0002 column=cf1:_0, timestamp=1579069474551, value= rk0002 column=cf1:age, timestamp=1579069474525, value=20 rk0003 column=cf1:NAME, timestamp=1579077684759, value=haha rk0003 column=cf1:_0, timestamp=1579077684759, value=x rk0003 column=cf1:age, timestamp=1579077684759, value=123 rk0004 column=cf1:NAME, timestamp=1579077695441, value=haha rk0004 column=cf1:_0, timestamp=1579077695441, value=x rk0004 column=cf1:age, timestamp=1579077695441, value=123 --说明通过table映射是可读写的
3、删除表
--在Phoenix中删除表 0: jdbc:phoenix:node1> drop table "test1"; --在hbase中查看，表已经没有了
三、全新的表
在hbase中没有表，直接在Phoenix中创建；
1、在Phoenix中创建表
0: jdbc:phoenix:node1> create table "test1"( . . . . . . . . . . .> user_id varchar primary key, . . . . . . . . . . .> "cf1".NAME varchar, . . . . . . . . . . .> "cf1"."age" varchar); --此时hbase中应该也会出现表test1
2、插入数据
0: jdbc:phoenix:node1> upsert into "test1" values ('rk0003','haha','123'); 0: jdbc:phoenix:node1> upsert into "test1" values ('rk0002','haha','123'); 0: jdbc:phoenix:node1> select * from "test1"; +----------+-------+------+ | USER_ID | NAME | age | +----------+-------+------+ | rk0002 | haha | 123 | | rk0003 | haha | 123 | +----------+-------+------+ --此时去hbase中查看，同样数据也会出现；
2、删除表
--在Phoenix中将表或者表中的数据删除后，hbase中的表或者表数据也会消失
3、在hbase端操作数据
--在hbase中操作数据后，Phoenix中也会有相应的变化