phoenix 表 增删改查

phoenix对表的操作，看似根其他sql差不多，但是总感觉很变扭。特别是不加双引号默认大写，值必须用单引号。

1，phoenix schema操作

0: jdbc:phoenix:bigserver4:2181> create schema "tank_schema";    //创建schema
20/01/20 13:23:22 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... usCREATE INDEX my_idx ON sales.opportunity(last_updated_date DESC)g builtin-java classes where applicable
No rows affected (2.193 seconds)

0: jdbc:phoenix:bigserver4:2181> select TENANT_ID,TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,COLUMN_FAMILY,TABLE_SEQ_NUM,TABLE_TYPE,PK_NAME from SYSTEM.CATALOG;
+------------+--------------+-------------+-------------------------------------+----------------+----------------+-------------+----------+
| TENANT_ID  | TABLE_SCHEM  | TABLE_NAME  |             COLUMN_NAME             | COLUMN_FAMILY  | TABLE_SEQ_NUM  | TABLE_TYPE  | PK_NAME  |
+------------+--------------+-------------+-------------------------------------+----------------+----------------+-------------+----------+
|...............................................................省略....................................................................... |
|            | tank_ns      |             |                                     |                | null           |             |          |
|            | tank_ns      | user        |                                     |                | 0              | u           |          |
|            | tank_ns      | user        | ROW                                 |                | null           |             |          |
|            | tank_ns      | user        | id                                  | login          | null           |             |          |
|            | tank_ns      | user        | sex                                 | info           | null           |             |          |
|            | tank_ns      | user        | username                            | login          | null           |             |          |
|            | tank_schema  |             |                                     |                | null           |             |          |
+------------+--------------+-------------+-------------------------------------+----------------+----------------+-------------+----------+
147 rows selected (0.054 seconds)

0: jdbc:phoenix:bigserver4:2181> drop schema if exists "tank_schema"; //删除schema
No rows affected (0.868 seconds)

0: jdbc:phoenix:bigserver4:2181> use "tank_ns";  //切换schema
No rows affected (0.009 seconds)

0: jdbc:phoenix:bigserver4:2181> use "tank_schema";
No rows affected (0.004 seconds)

hbase(main):001:0> list_namespace
NAMESPACE
SYSTEM
TANK_NS
TANK_SCHEMA
default
hbase
tank_ns
tank_schema   //创建schema就是hbase的namespace
7 row(s)
Took 0.8705 seconds

2，phoenix 表 操作

0: jdbc:phoenix:bigserver4:2181> CREATE TABLE "tank_schema"."test1" (  //创建表
. . . . . . . . . . . . . . . .> id BIGINT not null primary key,
. . . . . . . . . . . . . . . .> date Date
. . . . . . . . . . . . . . . .> );
No rows affected (4.783 seconds)

//添加字段
0: jdbc:phoenix:bigserver4:2181> alter table "tank_schema"."test1" add "sex" varchar(18);
No rows affected (8.625 seconds)

//删除字段
0: jdbc:phoenix:bigserver4:2181> ALTER TABLE "tank_schema"."test1" drop column "sex";
No rows affected (0.069 seconds)

//创建多主健表，not null必须是主键之一，这个有点搞
0: jdbc:phoenix:bigserver4:2181> CREATE TABLE "tank_schema"."test" (
. . . . . . . . . . . . . . . .> id BIGINT not null,
. . . . . . . . . . . . . . . .> pid BIGINT not null,
. . . . . . . . . . . . . . . .> date Date,
. . . . . . . . . . . . . . . .> constraint pk primary key(id, pid)
. . . . . . . . . . . . . . . .> )SALT_BUCKETS = 8;
No rows affected (2.57 seconds)

//删除表
0: jdbc:phoenix:bigserver4:2181> drop table "tank_schema"."test";
No rows affected (2.298 seconds)

3，创建view

0: jdbc:phoenix:bigserver4:2181> CREATE VIEW "tank_user_view" AS SELECT * FROM "tank_ns"."user";
No rows affected (0.066 seconds)

0: jdbc:phoenix:bigserver4:2181> alter view "tank_user_view" drop column "sex";
20/01/20 14:39:56 WARN query.ConnectionQueryServicesImpl: Unable to update meta data repo within 1 seconds for tank_user_view
No rows affected (1.018 seconds)

0: jdbc:phoenix:bigserver4:2181> drop view "tank_user_view";

4，创建自增SEQUENCE

//自增ID的语法如下：
//START 用于指定第一个值。如果不指定默认为1.
//INCREMENT指定每次调用NEXT VALUE FOR后自增大小。 如果不指定默认为1。
//MINVALUE和MAXVALUE一般与CYCLE连用, 让自增数据形成一个环，从最小值到最大值，再从最大值到最小值。
//CACHE默认为100, 表示server端生成100个自增序列缓存在客户端，可以减少rpc次数。
//此值也可以通过phoenix.sequence.cacheSize来配置。

CREATE SEQUENCE [IF NOT EXISTS] SCHEMA.SEQUENCE_NAME
[START WITH number]
[INCREMENT BY number]
[MINVALUE number]
[MAXVALUE number]
[CYCLE]
[CACHE number]

0: jdbc:phoenix:bigserver4:2181> create schema tank_schema; //创建一个大定的schema
No rows affected (0.919 seconds)

0: jdbc:phoenix:bigserver4:2181> CREATE SEQUENCE "tank_schema"."tank_user_sequence" START WITH 100000 INCREMENT BY 2 CACHE 1000;
No rows affected (0.551 seconds)

0: jdbc:phoenix:bigserver4:2181> DROP SEQUENCE IF EXISTS "tank_schema"."tank_user_sequence";
No rows affected (0.008 seconds)

没搞明白，为什么非要创建一个大写的tank_schema，小写的已创建，并且用也只用小写的。是BUG吗？

如果不创建大写的，就会报以下错误：

0: jdbc:phoenix:bigserver4:2181> CREATE SEQUENCE "tank_schema"."tank_user_sequence" START WITH 100000 INCREMENT BY 2 CACHE 1000;;
Error: ERROR 722 (43M05): Schema does not exist schemaName=TANK_NS (state=43M05,code=722)
org.apache.phoenix.schema.SchemaNotFoundException: ERROR 722 (43M05): Schema does not exist schemaName=TANK_SCHEMA
at org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.createSchemaRef(FromCompiler.java:538)
at org.apache.phoenix.compile.FromCompiler$SchemaResolver.<init>(FromCompiler.java:315)

5，表的增，删，改，查

//创建测试表
0: jdbc:phoenix:bigserver4:2181> CREATE TABLE "tank_schema"."test2" (
. . . . . . . . . . . . . . . .> id BIGINT not null primary key,
. . . . . . . . . . . . . . . .> pid BIGINT,
. . . . . . . . . . . . . . . .> name varchar
. . . . . . . . . . . . . . . .> )SALT_BUCKETS = 8;
No rows affected (4.962 seconds)

//插入二个数据，phoenix没有insert和update，只有他们的结合休upsert
0: jdbc:phoenix:bigserver4:2181> UPSERT INTO "tank_schema"."test2" ("id", "pid", "name") VALUES
. . . . . . . . . . . . . . . .> ( 1,100,'tanktest');

0: jdbc:phoenix:bigserver4:2181> UPSERT INTO "tank_schema"."test2" ("id", "pid", "name") VALUES
. . . . . . . . . . . . . . . .> ( NEXT VALUE FOR "tank_schema"."tank_user_sequence",100,'tanktest');
1 row affected (0.322 seconds)

0: jdbc:phoenix:bigserver4:2181> select * from "tank_schema"."test2";
+---------+------+-----------+
|   id    | pid  |   name    |
+---------+------+-----------+
| 100000  | 100  | tanktest  |
| 1       | 100  | tanktest  |
+---------+------+-----------+

//更新数据
0: jdbc:phoenix:bigserver4:2181> UPSERT INTO "tank_schema"."test2" ("id", "pid", "name") VALUES
. . . . . . . . . . . . . . . .> ( 1,100,'tank1');
1 row affected (0.017 seconds)

0: jdbc:phoenix:bigserver4:2181> select * from "tank_schema"."test2" ;
+---------+------+-----------+
|   id    | pid  |   name    |
+---------+------+-----------+
| 100000  | 100  | tanktest  |
| 1       | 100  | tank1     |
+---------+------+-----------+
2 rows selected (0.029 seconds)

//分页
0: jdbc:phoenix:bigserver4:2181> select * from "tank_schema"."test2" limit 1 offset 1;
+-----+------+-----------+
| id  | pid  |   name    |
+-----+------+-----------+
| 1   | 100  | tanktest  |
+-----+------+-----------+
1 row selected (0.117 seconds)

0: jdbc:phoenix:bigserver4:2181> select * from "tank_schema"."test2" limit 1 offset 0;
+---------+------+-----------+
|   id    | pid  |   name    |
+---------+------+-----------+
| 100000  | 100  | tanktest  |
+---------+------+-----------+
1 row selected (0.049 seconds)

0: jdbc:phoenix:bigserver4:2181> select * from "tank_schema"."test2" limit 2;
+---------+------+-----------+
|   id    | pid  |   name    |
+---------+------+-----------+
| 100000  | 100  | tanktest  |
| 1       | 100  | tank1     |
+---------+------+-----------+
2 rows selected (0.105 seconds)

如果报以下错误

Error: ERROR 204 (22008): Values in UPSERT must evaluate to a constant. (state=22008,code=204)
java.sql.SQLException: ERROR 204 (22008): Values in UPSERT must evaluate to a constant.
at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptio

原因是：

upsert值，只能用单引号，但是如果要用SEQUENCE，并且名称是小写，就要用双引号。

6，其他

!list //查看phoenix连接是那台服务器
!tables //查看所有表
!describe "tank_ns"."user"; //查看表结构
!columns "tank_ns"."user"; //查看列
!indexes "tank_ns"."user"; //查看所有索引
!help //帮助
!quit //退出