1Phoenix的快速入门
案例:创建一个订单明细表,名为ORDER_DTL,字段如下:
1.1 创建表
基本语法:
CREATE TABLE IF NOT EXISTS 表名 (
ROWKEY名称 数据类型 PRIMARY KEY
列蔟名.列名1 数据类型 NOT NULL,
列蔟名.列名2 数据类型 NOT NULL,
列蔟名.列名3 数据类型);
订单明细表创表语句:
大写:
create table if not exists ORDER_DTL(
ID varchar primary key,
C1.STATUS varchar,
C1.MONEY float,
C1.PAY_WAY integer,
C1.USER_ID varchar,
C1.OPERATION_TIME varchar,
C1.CATEGORY varchar
);
小写:Phoenix 小写要加""
create table if not exists "oder_dtl"(
"id" varchar primary key,
"c1"."status" varchar,
"c1"."money" float,
"c1"."pay_way" integer,
"c1"."user_id" varchar,
"c1"."operation" varchar,
"c1"."category" varchar
);
1.3 删除表
drop table if exists ORDER_DTL;
1.4 插入数据
upsert into 表名(列蔟列名, xxxx, ) VALUES(XXX, XXX, XXX)
UPSERT INTO ORDER_DTL VALUES('000001', '已提交', 4070, 1, '4944191', '2020-04-25 12:09:16', '手机;');
1.5 更新数据
UPSERT INTO ORDER_DTL("id", C1."status") VALUES ('000001', '已付款');
1.6 删除数据
DELETE FROM ORDER_DTL WHERE "id" = '000001';
1.7 查询数据
SELECT * FROM ORDER_DTL WHERE "id" = '000001';
1.8 分页查询
-- 第一页
select * from ORDER_DTL limit 10 offset 0;
-- 第二页
-- offset从10开始
select * from ORDER_DTL limit 10 offset 10;
-- 第三页
select * from ORDER_DTL limit 10 offset 20;
**更多语法:**http://phoenix.apache.org/language/index.html#delete
1.9 Phoenix与HBase进行集成
(1)**视图映射:**Hbase已经有的表,只需Phoenix创建视图与其映射即可
注意:
①名称空间和表名需一模一样;
②列簇名和列名需一一对应;
③视图映射,在Phoenix中只能进行查询操作;
-- 创建MOMO_CHAT:MSG视图
create view if not exists "MOMO_CHAT". "MSG" (
"pk" varchar primary key, -- 指定ROWKEY映射到主键
"C1"."msg_time" varchar,
"C1"."sender_nickyname" varchar,
"C1"."sender_account" varchar,
"C1"."sender_sex" varchar,
"C1"."sender_ip" varchar,
"C1"."sender_os" varchar,
"C1"."sender_phone_type" varchar,
"C1"."sender_network" varchar,
"C1"."sender_gps" varchar,
"C1"."receiver_nickyname" varchar,
"C1"."receiver_ip" varchar,
"C1"."receiver_account" varchar,
"C1"."receiver_os" varchar,
"C1"."receiver_phone_type" varchar,
"C1"."receiver_network" varchar,
"C1"."receiver_gps" varchar,
"C1"."receiver_sex" varchar,
"C1"."msg_type" varchar,
"C1"."distance" varchar,
"C1"."message" varchar
);
(2)表映射:在Phoenix操作表,在Hbase中对应的表也会受到影响,两者是同步的
①Hbase已经有的表,只需Phoenix创建表与其映射即可;
create table "test"
(id varchar primary key,
"info1"."name" varchar,
"info2"."address" varchar)
column_encoded_bytes=0;
注意:需后面添加column_encoded_bytes=0编码信息;
②Hbase若还没有表,在Phoenix创建表时,系统将会自动在HBase中同时创建表(使用create table指令创建表即可);