[大数据] Phoenix 操作语法

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> Phoenix 操作语法 -> 正文阅读

[大数据]Phoenix 操作语法

Phoenix 操作语法

参考

操作文档| 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;