大数据之Phoenix SQL操作

码客说

发布于 2023-03-06 15:53:23

9190

发布于 2023-03-06 15:53:23

文章被收录于专栏：码客

数据类型

序号	类型名	对应的java类型	取值范围	说明
1	INTEGER	INTEGER	[-2147483648， 2147483647]	binary表示是4个byte的整数, 符号位被翻转（为了让负数排在正数前面）
2	UNSIGNED_INT	Integer	[ 0,2147483647]	binary表示是4个byte的整型。这个类型主要用作序列化映射到已经存在Hbase表的数据，适配HBase Bytes.toBytes(int)方法。
3	BIGINT	Long	[-9223372036854775808 ，9223372036854775807]	binary表示是8位byte的Long类型, 符号位被翻转（为了让负数排在正数前面）
4	UNSIGNED_LONG	Long	[0 ,9223372036854775807]	binary表示是8位byte的Long类型。这个类型主要用作序列化映射到已经存在Hbase表的数据，适配HBase Bytes.toBytes(long)方法。
5	TINYINT	Byte	[-128,127]	binary表示是单个byte，为了排序符号位被翻转。
6	UNSIGNED_TINYINT	Byte	[0,127]	binary表示是单个byte。这个类型主要用作序列化映射到已经存在Hbase表的数据，适配 HBase Bytes.toBytes(byte)方法。
7	SMALLINT	Short	[-32768,32767]	binary表示是两个byte，为了排序符号位被翻转。
8	UNSIGNED_SMALLINT	Short	[0,32767]	binary表示是两个byte。这个类型主要用作序列化映射到已经存在Hbase表的数据，适配HBase Bytes.toBytes(short)方法。
9	FLOAT	Float	[-3.402823466 E + 38,3.402823466 E + 38]	binary表示是四个byte, 为了排序符号位被翻转。
10	UNSIGNED_FLOAT	Float	[0,3.402823466 E + 38]	binary表示是四个byte。这个类型主要用作序列化映射到已经存在Hbase表的数据，适配HBase Bytes.toBytes(float)方法。
11	DOUBLE	DOUBLE	[-1.7976931348623158 E + 308,1.7976931348623158 E + 308]	binary表示是8个byte，为了排序符号位被翻转。
12	UNSIGNED_DOUBLE	DOUBLE	[0,1.7976931348623158 E + 308]	binary表示是8个byte。这个类型主要用作序列化映射到已经存在Hbase表的数据，适配HBase Bytes.toBytes(double)方法。
13	DECIMAL(precision,scale)	BigDecimal	最大精度38位	binary是可比较的边长格式。如果用于rowkey。当它不是最后一列时，比较终结符号是null byte
14	BOOLEAN	BOOLEAN	0或1	binary表示0是flase, 1是true
15	TIME	java.sql.Time	格式： yyyy-MM-dd hh:mm:ss	二进制表示是8位byte的long类型数据, 数据内容是客户端时区自1970-01-01 00:00:00 UTC到现在的毫秒大小（GMT）。此类型与 SQL 92中的Time类型不兼容
16	DATE	java.sql.Date	格式： yyyy-MM-dd hh:mm:ss	二进制表示是8位byte的long类型数据, 数据内容是客户端时区自1970-01-01 00:00:00 UTC到现在的毫秒大小（GMT）。此类型与 SQL 92中的DATE类型不兼容。
17	TIMESTAMP	java.sql.Timestamp	格式：yyyy-MM-dd hh:mm:ss[.nnnnnnnnn]	二进制表示是8位byte的long类型和4位整型纳秒。8位byte的long类型数据是客户端时区自1970-01-01 00:00:00 UTC到现在的毫秒大小（GMT）。
18	UNSIGNED_TIME	java.sql.Time	格式： yyyy-MM-dd hh:mm:ss	二进制表示是8位byte的long类型数据, 数据内容是客户端时区自1970-01-01 00:00:00 UTC到现在的毫秒大小（GMT）。这个类型主要用作序列化映射到已经存在Hbase表的数据，适配HBase Bytes.toBytes(long)方法。
19	UNSIGNED_DATE	java.sql.Date	格式： yyyy-MM-dd hh:mm:ss	二进制表示是8位byte的long类型数据, 数据内容是客户端时区自1970-01-01 00:00:00 UTC到现在的毫秒大小（GMT）。这个类型主要用作序列化映射到已经存在Hbase表的数据，适配HBase Bytes.toBytes(long)方法。
20	UNSIGNED_TIMESTAMP	java.sql.Timestamp	格式：yyyy-MM-dd hh:mm:ss[.nnnnnnnnn]	二进制表示是8位byte的long类型和4位整型纳秒。8位byte的long类型数据是客户端时区自1970-01-01 00:00:00 UTC到现在的毫秒大小（GMT）。这个类型主要用作序列化映射到已经存在Hbase表的数据，适配HBase Bytes.toBytes(long)方法。
21	VARCHAR（precisionInt）	java.lang.String	变长，可选最大长度	对应UTF-8字符通过HBase Bytes.toBytes(String)转换的二进制。如果用于rowkey。当它不是最后一列时，比较终结符号是null byte
22	CHAR ( precisionInt )	java.lang.String	定长	对应UTF-8字符通过HBase Bytes.toBytes(String)转换的二进制。
23	BINARY ( precisionInt )	byte[]	定长	定长byte数组
24	VARBINARY	byte[]	变长	变长byte数组
25	ARRAY [dimension]	java.sql.Array	-	Java原始类型数组，只支持一维数组。例如：VARCHAR ARRAY， CHAR(10) ARRAY [5]，INTEGER []，INTEGER [100]

连接的两种方式

Phoenix Shell

启动 Zookeeper => HDFS => Yarn => HBase

sqlline.py hadoop01,hadoop02,hadoop03:2181

第一次启动比较慢，请耐心等待。

查询

!tables
!quit

Phoenix Query Server

在 4.4-4.14 和5.0 releases 中 query server 及其 JDBC client 是内置的.

4.15以后及5.1 release版本，Phoenix Query Server 需要单独下载, 版本号为 6.0.

下载及安装

下载地址：Download page

tar -zxvf phoenix-queryserver-6.0.0-bin.tar.gz -C /root/
cp -rn /root/phoenix-queryserver-6.0.0/* $PHOENIX_HOME/

启动query server

queryserver.py start

lsof -i:8765

测试

sqlline-thin.py http://hadoop01:8765

停止

queryserver.py stop

SQL操作

Schema的操作

1）创建schema

查看所有schema

!schema

创建schema

create schema "zdb";

注意:在phoenix中，schema名，表名，字段名等会自动转换为大写，若要小写，使用双引号，如"zdb"。

执行如下命令使用这个新建的 schema：

use "zdb";

执行如下命令则使用默认的 schema：

USE DEFAULT;

执行如下命令可以删除 zdb 这个 schema：

注意：确保该 schema 下的表都已删除，否则该 schema 会删除失败。

DROP SCHEMA "zdb";

表操作

显示所有表

!table
# 或 
!tables

use "zdb";

创建表

创建表的时候一定要设置主键，这个会作为RowKey使用。

直接指定单个列作为RowKey

CREATE TABLE IF NOT EXISTS "tuser"(
  id VARCHAR primary key,
  name VARCHAR,
  addr VARCHAR
);

指定多个列的联合作为RowKey

CREATE TABLE IF NOT EXISTS us_population (
  State CHAR(2) NOT NULL,
  City VARCHAR NOT NULL,
  Population BIGINT
  CONSTRAINT my_pk PRIMARY KEY (state, city)
);

插入数据

upsert into "tuser" values('1001','zhangsan','beijing');
upsert into "tuser" values('1002','lisi','上海');
upsert into "tuser"(id,name,address) values('1002','lisi','上海');

查询记录

select * from "tuser";
select * from "tuser" where id='1001';
select * from "tuser" where addr like '上%';

分页查询

select * from "tuser" order by id desc limit 1 offset 0;
select * from "tuser" order by id desc limit 1 offset 1;

其中

limit 取多少条
offset 从多少条开始

在Hbase中查看

hbase shell

list
scan 'zdb:tuser', {FORMATTER => 'toString'}

索引

查看是否用到索引

explain select * from "tuser";
explain select * from "tuser" where id='1001';
explain select * from "tuser" where name='zhangsan';

只要出现FULL SCAN就证明没用用到索引

创建索引

CREATE INDEX "index_tuser_name" ON "zdb"."tuser"(name);

删除索引

DROP INDEX "index_tuser_name" ON "zdb"."tuser";

如果创建二级索引报错

Mutable secondary indexes must have the hbase.regionserver.wal.codec property

停止hbase

$HBASE_HOME/bin/stop-hbase.sh

在每个regionServer的hbase-site.xml添加以下语句：

<property> 
  <name>hbase.regionserver.wal.codec</name> 
  <value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value> 
</property>

分发配置

ha-call.sh "rm -rf $HBASE_HOME/logs/*"
ha-fenfa.sh $HBASE_HOME

重启hbase即可

$HBASE_HOME/bin/start-hbase.sh

也要重启queryserver

queryserver.py stop
queryserver.py start

删除记录

delete from "tuser" where id='1001';

删除表

drop table "tuser";

7）退出命令行

!quit

表的映射

默认情况下：

Phoenix中创建的表在HBase中是可以看到的。
直接在HBase中创建的表，通过Phoenix是查看不到的。

1）表的关系

如果要在Phoenix中操作直接在HBase中创建的表，则需要在Phoenix中进行表的映射。

映射方式有两种：

视图映射
表映射。

2）命令行中创建表test HBase 中test的表结构如下，两个列族info1、info2。

Rowkey	info1	info2
id	name	address

启动HBase Shell

hbase shell

创建HBase表test

create 'test','info1','info2'

3）视图映射 Phoenix创建的视图是只读的，所以只能用来做查询，无法通过视图对源数据进行修改等操作。

在phoenix中创建关联test表的视图

create view "test"(id varchar primary key,"info1"."name" varchar, "info2"."address" varchar);

删除视图

drop view "test";

4）表映射使用Apache Phoenix创建对HBase的表映射，有两种方法：

HBase中不存在表时，可以直接使用create table指令创建需要的表,系统将会自动在Phoenix和HBase中创建同名的表，并会根据指令内的参数对表结构进行初始化。
当HBase中已经存在表时，可以以类似创建视图的方式创建关联表，只需要将create table改为create view即可。

create table "test"(id varchar primary key,"info1"."name" varchar, "info2"."address" varchar) column_encoded_bytes=0;
create view "test"(id varchar primary key,"info1"."name" varchar, "info2"."address" varchar) column_encoded_bytes=0;

表映射中数值类型的问题

Hbase中存储数值类型的值(如int,long等)会按照正常数字的补码进行存储. 而phoenix对数字的存储做了特殊的处理. phoenix 为了解决遇到正负数同时存在时，导致负数排到了正数的后面（负数高位为1，正数高位为0，字典序0 < 1）的问题。

phoenix在存储数字时会对高位进行转换.原来为1,转换为0，原来为0，转换为1. 因此，如果hbase表中的数据的写是由phoenix写入的,不会出现问题，因为对数字的编解码都是phoenix来负责。

如果hbase表中的数据不是由phoenix写入的，数字的编码由hbase负责. 而phoenix读数据时要对数字进行解码。因为编解码方式不一致。导致数字出错。

1）在hbase中创建表，并插入数值类型的数据

create 'person','info'
put 'person','1001', 'info:salary',Bytes.toBytes(123456)

注意: 如果要插入数字类型，需要通过Bytes.toBytes(123456)来实现。

2）在phoenix中创建映射表并查询数据

create table "person"(id varchar primary key,"info"."salary" integer) column_encoded_bytes=0;

select * from "person"

会发现数字显示有问题

3）解决办法: 在phoenix中创建表时使用无符号的数值类型unsigned_long

create table "person"(id varchar primary key,"info"."salary" unsigned_long) column_encoded_bytes=0;

DBeaver连接

DBeaver中搜索phoenix

修改DBeaver配置

修改dbeaver.ini文件，增加如下内容

-vm
D:/Tools/Java/jdk1.8.0_102/bin

连接配置

连接属性

驱动属性中也添加

属性

phoenix.schema.isNamespaceMappingEnabled

值

true

自动下载的Jar版本较老

我们要引用对应版本的Jar

phoenix-client-hbase-2.1-5.1.2.jar

服务配置

如果报错

CATALOG is found but client does not have phoenix.schema.isNamespaceMappingEnabled enabled

查询

select * from SYSTEM."CATALOG";

查看表的 TABLE_SCHEM 发现有些表这个属性为空。那么如果你没有指定自动映射命名空间，就会报错。

在 hbase 的 conf 目录下 hbase-site.xml 文件加入下面的属性：

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

然后，重启 hbase。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-12-16，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度