大数据之Phoenix SQL操作
数据类型
序号 | 类型名 | 对应的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。
相关文章
- 大数据ClickHouse(十六):ClickHouse SQL语法之DML 操作
- SQL Server如何通过SQL语句直接操作另一台服务器上的SQL SERVER的数据
- 自定义Hive Sql Job分析工具详解大数据
- 数据使用SQL Server缓存提升数据访问效率(sqlserver缓存)
- MySQL查询功能:学会使用SQL语句(mysql查询sql语句)
- Oracle 建表SQL实例示范操作指南(oracle建表sql)
- SQL Server导入MySQL,实现快速数据迁移(sqlserver导入mysql)
- 轻松使用Oracle实现SQL数据导入(oracle导入sql)
- MySQL实现数据分页的SQL技术(mysql分页sql)
- 实现Oracle数据库分页查询的SQL示例(oracle分页sql)
- 实现Oracle数据库乘法操作的SQL语句(oracle乘法sql)
- 微软SQL Server安装指南:一步步操作,快速上手(微软sqlserver安装)
- SQL Server数据列计算与可能性分析(sqlserver计算列)
- SQL Server快速恢复还原数据(sqlserver的还原)
- 取数据SQL Server 快速读取数据的正确方式(sqlserver如何读)
- 导入SQL Server:快速有效的数据迁移解决方案(导入sqlserver)
- 数据分析尽享SQL Server:挖掘数据揭示未知秘密(sqlserver下钻)
- MySQL实现行转列的SQL操作(mysql中sql行转列)
- MySQL操作如何使用SQL窗口进行数据查询和修改(mysql 中sql窗口)
- Oracle SQL替换指南简单操作轻松有效实现(oracle中sql替换)
- 优化深入探究Oracle下的SQL优化之道(oracle下sql)
- Oracle SQL环比分析发掘数据背后的规律(oracle_sql环比)
- Oracle SQL小试牛刀踏上编程之路(oracle sql练手)
- Oracle 64位SQL助力数据库优化管理(oracle64位sql)
- SQLSERVER2005中使用sql语句对xml文件和其数据的进行操作(很全面)