Phoenix的shell简单操作

1&＃xff0c;进入(hbase01是主机名&＃xff0c;2181是zookeeper的端口)

sqlline.py hbase01:2181

2&＃xff0c;退出&＃xff08;注意结尾不加分号&＃xff09;

&＃xff01;quit

3&＃xff0c;查询所有表&＃xff08;注意结尾不加分号&＃xff09;

&＃xff01;tables

4&＃xff0c;创建表&＃xff08;除了上面带感叹号的语句&＃xff0c;其余语句都得加分号&＃xff09;

&＃xff08;1&＃xff09;表名和列族名以及表名如果需要小写都得加双引号。
create table "person" ("id" integer not null primary key, "cf"."name" varchar, "cf"."age" integer);
&＃xff08;2&＃xff09;这样创建出来的列族名称和列名都是大写的。
create table "person" (id integer not null primary key, cf.name varchar, cf.age integer);
注意&＃xff1a;***表名和列族名区分大小写***&＃xff0c;加了双引号就必须用小写表名去查否则查不到。列名不区分大小写&＃xff0c;显示是大写&＃xff0c;用小写依然查得到。

5&＃xff0c;删除表结构

drop table "person";

6&＃xff0c;修改表结构

alter table "person" add sex varchar;

7&＃xff0c;查看表结构信息

!describe "person"

8&＃xff0c;创建表某一列索引

create index "person_index" on "person"("cf"."name");

9&＃xff0c;删除索引

drop index "person_index" on "person"

8&＃xff0c;向表中插入一条数据&＃xff08;注意&＃xff1a;数据值需要用引号时只能用单引号&＃xff0c;双引号会报错&＃xff09;

upsert into "person" values(1,&＃39;zhangsan&＃39;,27,&＃39;男&＃39;);

9&＃xff0c;删除表中数据&＃xff08;注意&＃xff1a;数据值需要用引号时只能用单引号&＃xff0c;双引号会报错&＃xff09;

delete from "person" where name&＃61;&＃39;zhangsan&＃39;;

10&＃xff0c;修改表中数据&＃xff08;注意&＃xff1a;修改时必须带上id&＃xff0c;否则会报错&＃xff09;&＃xff08;注意&＃xff1a;数据值需要用引号时只能用单引号&＃xff0c;双引号会报错&＃xff09;

upsert into "person"(id,sex) values(1, &＃39;女&＃39;);

11&＃xff0c;查询表中数据&＃xff08;注意&＃xff1a;数据值需要用引号时只能用单引号&＃xff0c;双引号会报错&＃xff09;

&＃xff08;1&＃xff09;全表查询
select * from "person";
&＃xff08;2&＃xff09;条件查询
select * from "person" where name&＃61;&＃39;zhangsan&＃39;;
&＃xff08;3&＃xff09;group by
select sex ,count(sex) as num from "person" where age >20 group by sex;
(4)case when
select (case name when &＃39;zhangsan&＃39; then &＃39;sansan&＃39; when &＃39;lisi&＃39; then &＃39;sisi&＃39; else name end)as showname from "person";

12&＃xff0c;关联hbase中已经存在的表

create view "test"(id varchar not null primary key, "cf1"."name" varchar, "cf1"."age" varchar, "cf1"."sex" varchar);
注意&＃xff1a;
&＃xff08;1&＃xff09;如果不加列族会报错如下&＃xff1a;
Error: ERROR 505 (42000): Table is read only. (state&＃61;42000,code&＃61;505)
&＃xff08;2&＃xff09;如果不加双引号则会匹配不到hbase表中的字段&＃xff0c;结果就是虽然关联上数据库但是没有值&＃xff01;&＃xff01;&＃xff01;
&＃xff08;3&＃xff09;关联的时候&＃xff0c;Phoenix建表最好都是varchar类型&＃xff0c;不容易出错
&＃xff08;4&＃xff09;最好创建view视图&＃xff0c;不要创建table表格。因为Phoenix端删除table会连带删除hbase表格&＃xff0c;如果是view则不会。