phoenix创建映射表和创建索引、删除索引、重建索引_phoenix删除索引

导读：

默认情况下，直接在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;