GBase 8t时间序列+json实现物联网应用快速入门

GBase 8t时间序列+json实现物联网应用快速入门

准备：创建dbspace及数据库

--------------------------------------------------------------

--第一步：准备dbspace,这里调用api，创建datadbs2，并设置chunk 可扩展

--连接数据库sysadmin执行；

--EXECUTE FUNCTION TASK ('create dbspace', 'dbspace_name', 'file_path', '大小K为单位', '0', 'Page size', '400', '1600');

EXECUTE FUNCTION TASK ('create dbspace', 'datadbs2', '/data/dbs/storagepool/datadbs2', '40000', '0', '8', '400', '1600');

SELECT A.chknum as num FROM sysmaster:syschktab A, sysmaster:sysdbstab B WHERE A.dbsnum = B.dbsnum AND B.name='datadbs2';

--返回chunk number :12

EXECUTE FUNCTION task("modify chunk extendable", "12"); 

--------------------------------------------------------------

--第二步：创建数据库testdb，

--dbaccess sysadmin

drop  database IF EXISTS testdb;

create database testdb in datadbs2 with log ;

--------------------------------------------------------------

--------------------------------------------------------------

--第三步：创建TS表带bson，并使用tsloder进行数据加载测试

--dbaccess testdb

--创建自定义数据类型，使用bson。json灵活可扩展

CREATE row type tsbson (t datetime year to fraction(5), data bson);

--创建iot_device表存储时间序列数据

CREATE TABLE iot_devicedata_ts (id char(128) NOT NULL, 

ts timeseries(tsbson), primary key(id))  

EXTENT SIZE 64 NEXT SIZE 64 LOCK MODE ROW;

--创建时间序列表虚表及存储容器

EXECUTE PROCEDURE TSCreateVirtualTab('iot_vt','iot_devicedata_ts','calendar(ts_1min), origin(2014-01-01 00:00:00.00000), irregular','scan_discreet','ts');

EXECUTE PROCEDURE TSContainerCreate('c_tsbson', 'datadbs2', 'tsbson', 0, 0);

 insert into iot_devicedata_ts values 

('id1',  "calendar(ts_1min),origin(2014-01-01 00:00:00.00000),  threshold(0),container(c_tsbson), irregular"); 

EXECUTE FUNCTION TSL_init('iot_devicedata_ts','ts');

EXECUTE PROCEDURE ifx_allow_newline('t');   

--注意tsl_put多行记录时，多行记录不能有空格，否则不能识别

EXECUTE FUNCTION TSL_Put('iot_devicedata_ts|ts', 

'id1|2016-01-13 01:00:00.00000|{name:"mall1",value:"1"}|

id1|2016-01-13 03:00:00.00000|{name:"mall1",value:"2"}|

id1|2016-01-13 03:00:04.00000|{name:"mall1",value:"3"}|

id1|2016-01-13 05:00:03.00000|{name:"mall1",value:"4"},value2:"3"}|

');

--必须在一个事务中控制

BEGIN work;

EXECUTE FUNCTION TSL_Flush('iot_devicedata_ts|ts');

commit;

EXECUTE FUNCTION TSL_SessionClose('iot_devicedata_ts|ts');

EXECUTE PROCEDURE TSL_Shutdown('iot_devicedata_ts|ts');

select * from iot_devicedata_ts;

select * from  iot_vt;