Phoenix(HBase) 操作语法

参考

操作文档| Apache Phoenix

CDH 6.2 安装 Phoenix

NameSpace 操作

启用Phoenix操作NameSpace

在 hbase-site.xml 中增加配置，服务端和客户端都需要增加该配置

<property>
  <name>phoenix.schema.isNamespaceMappingEnabled</name>
  <value>true</value>
</property>
<property>
  <name>phoenix.schema.mapSystemTablesToNamespace</name>
  <value>true</value>
</property>

创建 NameSpace

-- 格式
create schema if not exists 名称;

-- 示例:
create schema if not exists "order";

切换 NameSpace

-- 格式
use 名称;

-- 示例
use customer;

删除 NameSpace

-- 格式
drop schema if exists 名称;

-- 示例
drop schema if exists "order";

表的操作

查看表列表

!table

创建表

-- 格式
-- 注意：建表的时候必须要有主键
CREATE TABLE IF NOT EXISTS 表名 (
  ROWKEY名称 数据类型 PRIMARY KEY,
	列蔟名.列名1 数据类型 NOT NULL,
	列蔟名.列名2 数据类型 NOT NULL,
	列蔟名.列名3 数据类型
);


-- 示例：创建 ORDER_DTL 表
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
);

创建表并指定列族的版本数量

-- 说明：该表包含 2 个列族 C1、C2,其中 C1 列族最大版本数量为 3 ,C2 使用默认的版本数量 1
CREATE TABLE my_table ( 
    id INTEGER not null primary key desc, 
    C1.date DATE,
    C2.db_utilization DECIMAL
) C1.VERSIONS='3';

创建表并设置联合主键

-- pk_name 和 created_date 为联合主键
CREATE TABLE prod_test ( 
    pk_name char(50) not null, 
    created_date date not null,
    txn_count bigint 
    CONSTRAINT pk PRIMARY KEY (pk_name, created_date) 
);

创建表并设置分区

CREATE TABLE IF NOT EXISTS mutile_setting( 
    id char(10) not null primary key, 
    setting_name integer
) 
DATA_BLOCK_ENCODING='NONE',
VERSIONS=5,
MAX_FILESIZE=2000000 
split on (3, 5, 7);

表的大小写问题

-- 1.在列蔟、列名没有添加双引号，Phoenix 会自动转换为大写
create table if not exists test_1(
    id varchar primary key,
    C1.status varchar,
    C1.money double,
    C1.pay_way integer,
    C1.user_id varchar,
    C1.operation_time varchar,
    C1.category varchar
);
-- 2.在列蔟、列名添加双引号，Phoenix 会自动转换为小写
-- 注意：一旦加了小写，在使用该列的时候必须要加双引号，否则会报错
create table if not exists test_2(
    "id" varchar primary key,
    C1."status" varchar,
    C1."money" double,
    C1."pay_way" integer,
    C1."user_id" varchar,
    C1."operation_time" varchar,
    C1."category" varchar
);

查看表的信息

-- 格式
!desc 表名称

-- 示例
!desc ORDER_DTL

删除表

-- 格式
drop table if exists 表名称;

-- 示例
drop table if exists ORDER_DTL;

数据操作

插入数据

-- 格式
UPSERT INTO 表名(列蔟列名, xxxx, ) VALUES(XXX, XXX, XXX)

-- 示例
UPSERT INTO ORDER_DTL VALUES('000001', '已提交', 4070, 1, '4944191', '2020-04-25 12:09:16', '手机;');

更新数据

-- 格式
UPSERT INTO 表名(列名, …) VALUES(对应的值, …);

-- 示例
-- 如果是大写字段的表，使用该操作
UPSERT INTO ORDER_DTL(id, C1.status) VALUES ('000001', '已付款');
-- 如果是小写字段的表，使用该操作
UPSERT INTO ORDER_DTL("id", C1."status") VALUES ('000001', '已付款');

删除数据

DELETE FROM ORDER_DTL WHERE id = '000001';

数据查询

查询数据

SELECT * FROM ORDER_DTL;
SELECT * FROM ORDER_DTL WHERE "id" = '000001';

分页查询

-- 第 1 页,每页 10 条数据
select * from ORDER_DTL limit 10 offset 0;

-- 第2页
select * from ORDER_DTL limit 10 offset 10;

预分区表操作

创建 ROWKEY 预分区表

drop table if exists ORDER_DTL;
-- 按照用户ID来分区，一共4个分区,并指定数据的压缩格式为GZ
-- 创建后增加模拟数据 100 条，查看各个区的分布
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
) 
CONPRESSION='GZ'
SPLIT ON ('3','5','7');

创建 加盐指定数量分区

drop table if exists ORDER_DTL;
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
) 
CONPRESSION='GZ', SALT_BUCKETS=10;

可以看到 100 条数据在 10 个 Region 的分布情况，同时每个 Start Key 和 End Key 发生了变化

Phoenix 在每个 ID 前，都添加了一个 Hash 值，用来将分布分布到不同的 Region 中

视图操作

Phoenix 视图说明

使用 Phoenix 中的 视图，通过视图来建立与 HBase 表之间的映射，从而实现数据快速查询

phoenix 4.10 版本后，对列映射做了优化，采用一套新的机制，不在基于列名方式映射到 hbase，如果只做查询，强烈建议使用 phoenix 视图方式映射，删除视图不影响 hbase 源数据

注：比较明显的是通过 hbase shell 创建了 namespace 或 table，通过 phoenix 却查询不到数据，，甚至会报错 namespace 或 table 找不到。此时只能通过创建视图，然后查询视图

视图映射HBase中的表

CREATE VIEW "my_hbase_table"
    ( k VARCHAR primary key, "v" UNSIGNED_LONG) default_column_family='a';

视图Phoenix中的表

CREATE VIEW my_view ( new_col SMALLINT )
    AS SELECT * FROM my_table WHERE k = 100;

视图到一个SQL查询

CREATE VIEW my_view_on_view
    AS SELECT * FROM my_view WHERE new_col > 70;

创建一个视图

create view if not exists "default"."MSG" (
    "pk" varchar primary key,
    "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
);

二级索引

二级索引说明

在 HBase 中，通过 Scan + ROWKEY 的方式才能通过索引提高查询效率。但是在多条件查询下， 因为没有建立索引，所以组合条件查询效率较低，而通过使用 Phoenix，我们可以非常方便地创建二级索引。Phoenix 中的索引，其实底层还是表现为 HBase 中的表结构。这些索引表专门用来加快查询速度

索引分类

① 全局索引

全局索引一般和覆盖索引搭配使用，读的效率很高，但写入效率会受影响

② 本地索引

③ 覆盖索引

④ 函数索引

全局索引

全局索引说明

① 全局索引适用于读多写少业务

② 全局索引绝大多数负载都发生在写入时，当构建了全局索引时，Phoenix 会拦截写入(DELETE、UPSERT值和UPSERT SELECT)上的数据表更新，构建索引更新，同时更新所有相关的索引表，开销较大

③ 读取时，Phoenix 将选择最快能够查询出数据的索引表。默认情况下，除非使用Hint，如果SELECT查询中引用了其他非索引列，该索引是不会生效的

全局索引的创建

-- 格式
CREATE INDEX 索引名称 ON 表名 (列名1, 列名2, 列名3...)

本地索引

本地索引说明

① 本地索引适合写操作频繁，读相对少的业务

② 当使用 SQL 查询数据时，Phoenix 会自动选择是否使用本地索引查询数据

③ 在本地索引中，索引数据和业务表数据存储在同一个服务器上，避免写入期间的其他网络开销

④ 在 Phoenix 4.8.0 之前，本地索引保存在一个单独的表中，在 Phoenix 4.8.1 中，本地索引的数据是保存在一个影子列蔟中

⑤ 本地索引查询即使 SELECT 引用了非索引中的字段，也会自动应用索引的

注意：创建表的时候指定了 SALT_BUCKETS，是不支持本地索引的

本地索引创建

-- 格式
CREATE local INDEX 索引名称 ON 表名 (列名1, 列名2, 列名3...)

覆盖索引

覆盖索引说明

Phoenix 提供了覆盖的索引，可以不需要在找到索引条目后返回到主表。Phoenix 可以将关心的数据捆绑在索引行中，从而节省了读取时间的开销

覆盖索引创建

-- 示例
-- 在 v1 和 v2 列上创建索引，并在索引中包括 v3 列，也就是通过 v1、v2 就可以直接把数据查询出来
CREATE INDEX my_index ON my_table (v1,v2) INCLUDE(v3)

函数索引

函数索引说明

函数索引(4.3和更高版本)可以支持在列上创建索引，还可以基于任意表达式上创建索引。然后，当查询使用该表达式时，可以使用索引来检索结果，而不是数据表

函数索引创建

-- 示例
-- 在 UPPER(FIRST_NAME||‘ ’||LAST_NAME) 上创建一个索引，这样将来搜索两个名字拼接在一起时，索引依然可以生效
-- 创建索引
CREATE INDEX UPPER_NAME_IDX ON EMP (UPPER(FIRST_NAME||' '||LAST_NAME))

-- 以下查询会走索引
SELECT EMP_ID FROM EMP WHERE UPPER(FIRST_NAME||' '||LAST_NAME)='JOHN DOE'

索引案例：全局索引 + 覆盖索引

建表语句

create table if not exists ORDER_DTL(
    "id" varchar primary key,
    C1."status" varchar,
    C1."money" double,
    C1."pay_way" integer,
    C1."user_id" varchar,
    C1."operation_time" varchar,
    C1."category" varchar
);

创建索引

根据用户ID来查询订单的ID以及对应的支付金额。例如：查询已付款的订单ID和支付金额，此时，就可以在 USER_ID 列上创建索引，来加快查询

create index GBL_IDX_ORDER_DTL on ORDER_DTL(C1."user_id") INCLUDE("id", C1."money");

查看索引数据
会额外创建一张表，索引其实就是一张表，将索引表的数据和原数据表得知：

① 索引表中的 ROWKEY = 用户ID + \x00 + 原始表ROWKEY

② 索引表中的列族 = 全局索引字段

查看执行计划

explain select "user_id", "id", "money" from ORDER_DTL where "user_id" = '1625615';

删除索引

drop index IDX_ORDER_DTL_DATE on ORDER_DTL;

查看索引

!table

使用Hint强制使用索引

explain select /*+ INDEX(ORDER_DTL GBL_IDX_ORDER_DTL) */ * from ORDER_DTL where "user_id" = '8237476';

索引案例：本地索引

建表语句

create table if not exists ORDER_DTL(
    "id" varchar primary key,
    C1."status" varchar,
    C1."money" double,
    C1."pay_way" integer,
    C1."user_id" varchar,
    C1."operation_time" varchar,
    C1."category" varchar
);

创建索引

create local index LOCAL_IDX_ORDER_DTL on ORDER_DTL("id", "status", "money", "pay_way", "user_id") ;

查看索引数据

注意：不会额外创建索引表

Phoenix 对数据进行处理，原有的数据发生了变化。建立了本地二级索引表，不能再使用 Hbase 的 Java API查询，只能通过JDBC来查询

scan "ORDER_DTL", {LIMIT => 1}

查看执行计划

explain select * from ORDER_DTL WHERE "status" = '已提交';
explain select * from ORDER_DTL WHERE "status" = '已提交' AND "pay_way" = 1;

删除本地索引

删除本地索引后，数据重新恢复原样

drop index LOCAL_IDX_ORDER_DTL on ORDER_DTL;

索引案例：本地函数索引

建表语句

create table if not exists ORDER_DTL(
    "id" varchar primary key,
    C1."status" varchar,
    C1."money" double,
    C1."pay_way" integer,
    C1."user_id" varchar,
    C1."operation_time" varchar,
    C1."category" varchar
);

创建索引

CREATE LOCAL INDEX LOCAL_IDX_MSG ON MSG(substr("msg_time", 0, 10), "sender_account", "receiver_account");

查看索引数据

注意：不会额外创建索引表

SELECT * FROM "MSG" T 
WHERE substr("msg_time", 0, 10) = '2020-08-29'
    AND T."sender_account" = '13504113666'
    AND T."receiver_account" = '18182767005' LIMIT 100;