phoenix 表 增删改查

张映 发表于 2020-01-20

分类目录: hadoop/spark/scala

标签:

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)
字段类型 映射的类 值的范围 占用字节
INTEGER java.lang.Integer -2147483648 到 2147483647 4字节
UNSIGNED_INT java.lang.Integer 0 到 2147483647 4字节
BIGINT java.lang.Long -9223372036854775808 到 9223372036854775807 8字节
UNSIGNED_LONG java.lang.Long 0 到 9223372036854775807 8字节
TINYINT java.lang.Byte -128 到 127 1字节
UNSIGNED_TINYINT java.lang.Byte 0 到 127 1字节
SMALLINT java.lang.Short -32768 到 32767 2字节
UNSIGNED_SMALLINT java.lang.Short 0 到 32767 2字节
FLOAT java.lang.Float -3.402823466 E + 38 到 3.402823466 E + 38 4字节
UNSIGNED_FLOAT java.lang.Float 0到3.402823466 E + 38 4字节
DOUBLE java.lang.Double -1.7976931348623158 E + 308 到 1.7976931348623158 E + 308 8字节
UNSIGNED_DOUBLE java.lang.Double 0 到 1.7976931348623158 E + 308 8字节
DECIMAL 或 DECIMAL(precisionInt , scaleInt) java.math.BigDecimal 最大精度为38位。可变长度
BOOLEAN java.lang.Boolean 0表示false,1表示true 1字节
TIME java.sql.Time 自纪元以来的毫秒数(基于时间GMT) 8字节
UNSIGNED_TIME java.sql.Time 格式为 yyyy-MM-dd hh:mm:ss 8字节
DATE java.sql.Date 自纪元以来的毫秒数(基于时间GMT) 8字节
UNSIGNED_DATE java.sql.Date 格式为 yyyy-MM-dd hh:mm:ss 8字节
TIMESTAMP java.sql.Timestamp 自纪元以来的毫秒数(基于时间GMT) 12字节(纪元时间长8字节加纳秒的4字节整数)
UNSIGNED_TIMESTAMP java.sql.Timestamp 格式为 yyyy-MM-dd hh:mm:ss[.nnnnnnnnn] 12字节
VARCHAR 或 VARCHAR(precisionInt) java.lang.String 可选的最大字节长度
CHAR(precisionInt) java.lang.String 固定长度字符串
BINARY(precisionInt) byte[] 原始固定长度字节数组
VARBINARY byte[] 原始可变长度字节数组
ARRAY 或 ARRAY[dimensionInt] java.sql.Array

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 //退出


转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/hadoop/2361.html