导读：
默认情况下，直接在hbase中创建的表，通过phoenix是查看不到的。如果需要在phoenix中操作直接在hbase中创建的表，则需要在phoenix中进行表的映射。映射方式有两种：视图映射和表映射。
0.创建hbase映射表：
映射表一定要和hbase中表的字段一一对应，首先pk是主键，也可以用rowkey来表示，剩下的就是各个列，不要忘了用“列族”.“具体列”这样的形式表示。
create table "device_data_test08"
("pk" varchar primary key,"data"."deviceID" varchar,
"data"."deviceTime" varchar,"data"."modelID" varchar,
"data"."processState" varchar,"data"."subDevice" varchar,
"data"."test08" varchar,"data"."abc1234567" varchar);1.创建索引：
两个双引号，第一个是索引的名称，第二个是原表的名称
这边的索引字段为：deviceID
Include括号里面是包含要返回的列是哪些。
CREATE INDEX "index_device_data_test08" ON
"device_data_test08"("data"."deviceID")
INCLUDE
("data"."deviceTime","data"."modelID","data"."processState","data"."subDevice","data"."test08");2.删除索引
drop index "index_device_data_test08" on "device_data_test08";3.重建索引
ALTER INDEX IF EXISTS "idx_spc_test08" on "device_data_test08" REBUILD;有可能重建索引失败，失败的原因可能是表的数据量太大，或者生产环境中正在进行该表的操作。多尝试几次即可。如果还是失败，建议删除索引，然后重新创建索引。
test08lil1634894735885
test08\x00lil\x001634894735885\x001\x00test08lil1634894735885
建立映射表：
create table "device_data_test08" ("pk" varchar primary key,"data"."deviceID" varchar,"data"."deviceTime" varchar,"data"."modelID" varchar,"data"."processState" varchar,"data"."subDevice" varchar,
"data"."test08" varchar,"data"."abc1234567" varchar);
create table "device_data_test01" ("pk" varchar primary key,"data"."1" varchar,"data"."deviceID" varchar,"data"."deviceTime" varchar,"data"."modelID" varchar,"data"."processState" varchar,"data"."subDevice" varchar);
建立索引表：
CREATE INDEX "index_device_data_test08" ON "device_data_test08"("data"."deviceID")INCLUDE("data"."deviceTime","data"."modelID","data"."processState","data"."subDevice","data"."test08");
CREATE INDEX "idx_spc_test01" ON "device_data_test01"("data"."modelID","data"."deviceID","data"."deviceTime","data"."processState")INCLUDE("data"."subDevice","data"."1","data"."abc1234567");
删除索引：
drop index "index_device_data_test08" on "device_data_test08";
drop index "index_device_data_test01" on "device_data_test01";
增加列：
alter table "device_data_test02" add "data"."abc1234567" varchar
删除列：
alter table "harve_role" drop column +列名字，如：createAt,name;
重建索引
ALTER INDEX IF EXISTS "idx_spc_test08" on "device_data_test08" REBUILD;
explain select "modelID" from "device_data_test08" where "modelID" = 'test08' and "deviceID" = 'lil' and "deviceTime" = '1634867582045' and "processState" = '1';
索引表改名称：
drop 'index_device_data_test01'
hbase(main):017:0> disable 'index_device_data_test02'
hbase(main):018:0> snapshot 'index_device_data_test02','index_device_data_test02Snapshot'
hbase(main):019:0> clone_snapshot 'index_device_data_test02Snapshot','idx_spc_test02'
hbase(main):020:0> delete_snapshot 'index_device_data_test02Snapshot'
hbase(main):021:0> drop 'index_device_data_test02'
查找最大rowkey
select max("rowkey") from "device_data_28UTB9" where
to_char(CONVERT_TZ(to_date(SUBSTR("deviceTime",1,10),'s'), 'UTC', 'Asia/Shanghai'),'yyyy-MM-dd')
= '2021-11-07' and "deviceID" = 'VWY3FL14M4'；
查找固定设备
select * from "device_data_28UTB9" where
to_char(CONVERT_TZ(to_date(SUBSTR("deviceTime",1,10),'s'), 'UTC', 'Asia/Shanghai'),'yyyy-MM-dd')
= '2021-11-07' and "deviceID" = 'VWY3FL14M4' limit 10;
计数值计量值控制图、工序能力分析，异常报警，支持手工录入和自动采集补充：
视图映射：
Phoenix创建的视图是只读的，所以只能用来做查询，无法通过视图对源数据进行修改等操作
# hbase shell 进入hbase命令行
hbase shell
# 创建hbase表
create 'test','name','company'
# 插入数据
put 'test','001','name:firstname','zhangsan1'
put 'test','001','name:lastname','zhangsan2'
put 'test','001','company:name','数加'
put 'test','001','company:address','合肥'
upsert into TEST values('002','xiaohu','xiaoxiao','数加','合肥');
# 在phoenix创建视图， primary key 对应到hbase中的rowkey
create view "test"(
empid varchar primary key,
"name"."firstname" varchar,
"name"."lastname" varchar,
"company"."name" varchar,
"company"."address" varchar
);
CREATE view "students" (
id VARCHAR NOT NULL PRIMARY KEY,
"info"."name" VARCHAR,
"info"."age" VARCHAR,
"info"."gender" VARCHAR ,
"info"."clazz" VARCHAR
) column_encoded_bytes=0;
# 在phoenix查询数据，表名通过双引号引起来
select * from "test";
# 删除视图
drop view "test";3.2、表映射
使用Apache Phoenix创建对HBase的表映射，有两类：
1） 当HBase中已经存在表时，可以以类似创建视图的方式创建关联表，只需要将create view改为create table即可。
2） 当HBase中不存在表时，可以直接使用create table指令创建需要的表，并且在创建指令中可以根据需要对HBase表结构进行显示的说明。
第1）种情况下，如在之前的基础上已经存在了test表，则表映射的语句如下：
create table "test" (
empid varchar primary key,
"name"."firstname" varchar,
"name"."lastname"varchar,
"company"."name" varchar,
"company"."address" varchar
)column_encoded_bytes=0;
upsert into "students" values('150011000100','xiaohu','24','男','理科三班');
upsert into "test" values('1001','xiaohu','xiaoxiao','数加','合肥');
CREATE table "students" (
id VARCHAR NOT NULL PRIMARY KEY,
"info"."name" VARCHAR,
"info"."age" VARCHAR,
"info"."gender" VARCHAR ,
"info"."clazz" VARCHAR
) column_encoded_bytes=0;
upsert into "students" values('150011000100','xiaohu','24','男','理科三班');
CREATE table "score" (
id VARCHAR NOT NULL PRIMARY KEY,
"info"."score_dan" VARCHAR
) column_encoded_bytes=0;
使用create table创建的关联表，如果对表进行了修改，源数据也会改变，同时如果关联表被删除，源表也会被删除。但是视图就不会，如果删除视图，源数据不会发生改变。一、重建索引步骤：
（1）从现有的EAP数据库中拿到所有模型的列表，modelID匹配modelName
（2）进入phoenix命令行
（3）看模型的更新与创建时间，如果模型与现有的索引不一致，先删掉现有索引
drop index "idx_spc_6CYPIO" on "device_data_6CYPIO";
(4) 新建索引，语句如下，字段自行替换（include之前的四个字段不可替换，通用的）
include内的字段通过model_ID进行查询，SQL语句如下：select * from device_data where model_ID ='';
create index "idx_spc_6CYPIO" on "device_data_6CYPIO"("data"."modelID","data"."deviceID","data"."deviceTime","data"."processState") include
(
"data"."subDevice"
,"data"."siteCode"
,"data"."equipNum"
,"data"."userName"
,"data"."productType"
,"data"."identification"
,"data"."qualityStatus"
,"data"."testOrderNum"
,"data"."completeQty"
,"data"."JSGDKW01"
,"data"."JSGDTD01"
,"data"."JSGDST01"
,"data"."JSGDPF01"
,"data"."JSGDSV01"
,"data"."JSGDTPTM"
,"data"."JSGDXHCS01"
,"data"."STATUS"
,"data"."STEP"
,"data"."CURRENT"
,"data"."VOLTAGE"
,"data"."CAPACITY"
,"data"."ENERGY"
,"data"."STIME"
,"data"."DTIME"
,"data"."TEMP"
,"data"."VACUUM"
,"data"."DEVICETYPE"
,"data"."MESDEVICEID");
(5) 等待执行成功即可。
二、检查需要重建索引集合
（1）SQL查询：根据李璐上次重建索引时间，凡是创建时间或者更新时间大于李璐上次重建时间，都需要重新创建。
（2）SQL语句如下：select * from device_model where update_time >'2022-04-26 22:25:00' or create_time > '2022-04-26 22:25:00';
（3）SQL结果如下,model_id(通过mdoel_id就可以查询具体的data_id字段，即include包括的字段)，一共需要重建29个索引:
189538
28UTB9
.........
例如：
新建索引表：8K4PNX
drop view "device_data_8K4PNX";
create index "idx_spc_8K4PNX" on "device_data_8K4PNX"("data"."modelID","data"."deviceID","data"."deviceTime","data"."processState") include
(
"data"."ZSDP_JZSJ"
,"data"."ZSDP_CZSJ"
,"data"."ZSDP_JQWZ"
,"data"."ZSDP_DPSJ"
,"data"."ZSDP_KZGCF_MIN"
,"data"."ZSDP_KZGCF_MAX"
,"data"."ZSDP_KFGCF_MIN"
,"data"."ZSDP_KFGCF_MAX"
,"data"."ZSDP_KZFCZ_MIN"
,"data"."ZSDP_KZFCZ_MAX"
,"data"."ZSDP_KFFCZ_MIN"
,"data"."ZSDP_KFFCZ_MAX"
,"data"."ZSDP_GSGCF_MAX"
,"data"."ZSDP_GSGCF_MIN"
,"data"."ZSDP_GSFCZ_MAX"
,"data"."ZSDP_GSFCZ_MIN"
,"data"."ZSDP_GDGCF_MAX"
,"data"."ZSDP_GDGCF_MIN"
,"data"."ZSDP_GDFCZ_MAX"
,"data"."ZSDP_GDFCZ_MIN"
,"data"."ZSDP_GMZL"
,"data"."ZSDP_SZRYYL"
,"data"."ZSDP_RYYL"
,"data"."ZSDP_SZRYSJ"
,"data"."ZSDP_DXTLSJ"
,"data"."ZSDP_SZSRYWD"
,"data"."ZSDP_SZXRYWD"
,"data"."ZSDP_SRYWD"
,"data"."ZSDP_XRYWD"
,"data"."MQEF_ANODE"
,"data"."MQEF_CATHODE"
,"data"."MQEF_ANODE_LH"
,"data"."MQEF_CATHODE_LH"
,"data"."SEPARATOR"
,"data"."TAPE1"
,"data"."CELL"
,"data"."ZSDP_JQH"
,"data"."ZSDP_CZRY"
,"data"."ZSDP_BFCCJG"
,"data"."ZSRY_RYJGPD"
,"data"."siteCode"
,"data"."equipNum"
,"data"."userName"
,"data"."productType"
,"data"."identification"
,"data"."qualityStatus"
,"data"."testOrderNum"
,"data"."completeQty"
,"data"."materialLotCode");
------------
新建视图：
CREATE VIEW "device_data_8K4PNX"(
"rowkey" varchar primary key
,"data"."modelID"varchar
,"data"."deviceID"varchar
,"data"."deviceTime"varchar
,"data"."ZSDP_JZSJ"varchar
,"data"."ZSDP_CZSJ"varchar
,"data"."ZSDP_JQWZ"varchar
,"data"."ZSDP_DPSJ"varchar
,"data"."ZSDP_KZGCF_MIN"varchar
,"data"."ZSDP_KZGCF_MAX"varchar
,"data"."ZSDP_KFGCF_MIN"varchar
,"data"."ZSDP_KFGCF_MAX"varchar
,"data"."ZSDP_KZFCZ_MIN"varchar
,"data"."ZSDP_KZFCZ_MAX"varchar
,"data"."ZSDP_KFFCZ_MIN"varchar
,"data"."ZSDP_KFFCZ_MAX"varchar
,"data"."ZSDP_GSGCF_MAX"varchar
,"data"."ZSDP_GSGCF_MIN"varchar
,"data"."ZSDP_GSFCZ_MAX"varchar
,"data"."ZSDP_GSFCZ_MIN"varchar
,"data"."ZSDP_GDGCF_MAX"varchar
,"data"."ZSDP_GDGCF_MIN"varchar
,"data"."ZSDP_GDFCZ_MAX"varchar
,"data"."ZSDP_GDFCZ_MIN"varchar
,"data"."ZSDP_GMZL"varchar
,"data"."ZSDP_SZRYYL"varchar
,"data"."ZSDP_RYYL"varchar
,"data"."ZSDP_SZRYSJ"varchar
,"data"."ZSDP_DXTLSJ"varchar
,"data"."ZSDP_SZSRYWD"varchar
,"data"."ZSDP_SZXRYWD"varchar
,"data"."ZSDP_SRYWD"varchar
,"data"."ZSDP_XRYWD"varchar
,"data"."MQEF_ANODE"varchar
,"data"."MQEF_CATHODE"varchar
,"data"."MQEF_ANODE_LH"varchar
,"data"."MQEF_CATHODE_LH"varchar
,"data"."SEPARATOR"varchar
,"data"."TAPE1"varchar
,"data"."CELL"varchar
,"data"."ZSDP_JQH"varchar
,"data"."ZSDP_CZRY"varchar
,"data"."ZSDP_BFCCJG"varchar
,"data"."ZSRY_RYJGPD"varchar
,"data"."siteCode"varchar
,"data"."equipNum"varchar
,"data"."userName"varchar
,"data"."productType"varchar
,"data"."identification"varchar
,"data"."qualityStatus"varchar
,"data"."testOrderNum"varchar
,"data"."completeQty"varchar
,"data"."materialLotCode"varchar
,"data"."processState"varchar
)column_encoded_bytes=0;
1087
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
