基于Tablestore的一站式物联网存储解决方案-数据操作篇-阿里云开发者社区

前言

上一章节介绍了共享充电宝场景的表结构设计。本章节主要为大家介绍如何使用表格存储Tabelstore数据表实现基本数据读写、批量更新，以及利用多元索引特性实现多维度查询功能。

准备工作

测试数据说明

数据表	数据表名	数据行数	说明
元数据表	cabinet	一千万行	模拟一千万台机柜
订单数据表	order	五千万行	模拟五千万条订单数据
元数据时序表	cabinet_time	一亿行	模拟跟踪一万台机柜元数据，十分钟上报一次，共一万个时序点。

测试数据导入。这里提供如下两种测试数据导入方式，二选一即可。
- 通过OSS BUCKET地址下载已生成的测试数据，通过Dataworks离线迁移到Tablestore数据表。下载地址：元数据表文件，元数据时序表文件，订单数据表文件，迁移脚本说明。
- 通过github下载demo，运行demo生成测试数据，直接写入Tablestore数据表。

说明：Dataworks可用于OSS与Tablestore之间做数据离线迁移。本场景中的迁移脚本可参考 Github中OSStoOTS.txt。关于Dataworks的介绍可参考 MaxCompute官方文档。

测试数据样例

数据表读写

在共享充电宝场景中，新增机柜、机柜下线、租借、归还、查看订单这些业务操作映射到数据库操作中均可看作是对数据表中行的新增、删除、更新、读取。数据操作的方法如下：

说明：更多关于基于主键的数据操作请参考单行数据操作和多行数据操作。

新增机柜。可看做在元数据表cabinet中新增一行记录。下面提供了控制台操作截图和SDK代码片段。

PutRowRequest putRowRequest = new PutRowRequest();
//指定表名
RowPutChange rowPutChange = new RowPutChange("cabinet");
//指定主键
rowUpdateChange.setPrimaryKey(PrimaryKeyBuilder.createPrimaryKeyBuilder()           .addPrimaryKeyColumn("cabinet_Md5ID",PrimaryKeyValue.fromString(Md5ID))
.addPrimaryKeyColumn("cabinet_ID",PrimaryKeyValue.fromString(ID)).build());
//添加机柜经纬度
rowPutChange.addColumn("cabinet_geo", ColumnValue.fromString(geo));
//添加机柜位置
rowPutChange.addColumn("cabinet_location",ColumnValue.fromString(position));
//添加机柜所在省份
rowPutChange.addColumn("cabinet_province",ColumnValue.fromString(province));
putRowRequest.setRowChange(rowPutChange);
//发起新增请求
client.putRow(putRowRequest);

租借充电宝。可看做两个步骤。1、订单数据表order写入一条记录。2、更新元数据表cabiet对应的机柜元数据信息。这里主要介绍更新元数据表的操作步骤。

注意：只可对属性列的值进行更新，主键值无法更新。

UpdateRowRequest updateRowRequest = new UpdateRowRequest();
//指定表名 
RowUpdateChange rowUpdateChange = new RowUpdateChange("cabinet");
//指定主键
rowUpdateChange.setPrimaryKey(PrimaryKeyBuilder.createPrimaryKeyBuilder()           .addPrimaryKeyColumn("cabinet_Md5ID",PrimaryKeyValue.fromString(Md5ID))
.addPrimaryKeyColumn("cabinet_ID",PrimaryKeyValue.fromString(ID)).build());
//更新可用充电宝数量
rowUpdateChange.put("cabinet_available_size",ColumnValue.fromLong(availableSize));
//更新机柜电量
rowUpdateChange.put("cabinet_powerPercent",ColumnValue.fromLong(powerPercent));
updateRowRequest.setRowChange(rowUpdateChange);
//发起更新请求
client.updateRow(updateRowRequest);

查看订单。根据订单Md5ID，订单ID查看订单详情。

GetRowRequest getRowRequest = new GetRowRequest();
//指定表名
SingleRowQueryCriteria criteria = new SingleRowQueryCriteria("order");
//指定主键
criteria.setPrimaryKey(PrimaryKeyBuilder.createPrimaryKeyBuilder()              .addPrimaryKeyColumn("order_Md5ID",PrimaryKeyValue.fromString(orderMd5ID))
.addPrimaryKeyColumn("order_ID",PrimaryKeyValue.fromString(orderID)).build());
//指定读取的版本数
criteria.setMaxVersions(1);
getRowRequest.setRowQueryCriteria(criteria);
//发起查询请求
clinet.getRow(getRowRequest);

上面介绍了数据新增、数据更新、数据查询的操作步骤。可以看出，上述操作均是基于主键的，无论是更新或者是查询，都需要传入完整的主键信息。然而在共享充电宝场景中，许多业务需求是要根据属性列作为查询条件来进行筛选。所以，仅仅依靠主键的读写并不能完全满足业务需求，还需要依赖索引能力。下面将为大家介绍Tablestore功能之一多元索引。

多元索引操作

什么是多元索引

多元索引是表格存储Tablestore的功能之一。多元索引基于倒排索引、列式存储，可满足多列自由组查询等复杂查询需求。不同于传统数据库MySQL的索引使用方式，没有最左匹配原则的限制，可灵活地在任意列上建立索引，将查询能力扩展到属性列上，实现对百亿数据毫秒级查询。

创建多元索引

控制台创建步骤

进入表管理-索引管理页面。点击创建多元索引。

小技巧：多元索引创建后，即可在控制台上看到数据表实时的总行数。

输入索引名。添加多个索引字段。点击确定。

SDK创建代码片段。

说明: 更多关于多元索引创建SDK示例请参考官网文档创建多元索引。

//这里为部分列创建索引
CreateSearchIndexRequest csir = new CreateSearchIndexRequest();
csir.setTableName("cabinet");//设置表名
csir.setTableName("cabinet_index_1");//设置索引名
IndexSchema indexSchema = new IndexSchema();
indexSchema.setFieldSchemas(Arrays.asList(
    new FieldSchema("cabinet_Md5ID", FieldType.KEYWORD),//指定字段名和字段类型
    new FieldSchema("cabinet_ID", FieldType.KEYWORD),
    new FieldSchema("cabinet_available_size", FieldType.LONG),
    new FieldSchema("cabinet_location", FieldType.KEYWORD),
    new FieldSchema("cabinet_geo", FieldType.GEO_POINT),
    new FieldSchema("cabinet_pricePerHour", FieldType.DOUBLE),
    new FieldSchema("cabinet_isonline", FieldType.KEYWORD)
));
csir.setIndexSchema(indexSchema);//设置索引schema
//发送创建多元索引请求
client.createSearchIndex(csir);

多元索引查询

多元索引可实现丰富的查询方式，例如全文检索、前缀查询、模糊查询、组合查询、地理位置查询等等功能。下面以共享充电宝场景中具体的查询需求为例，模拟四个业务场景介绍多元索引的几个重要功能：多字段排序、多字段组合查询、分组聚合等。

场景一。用户需要查询有可租用充电宝的机柜信息，并先按照距离远近倒序排序，再按照机柜时价倒序排序。

查询代码

//场景一。用户需要查询有可租用充电宝的机柜信息，并先按照距离远近倒序排序，再按照机柜时价倒序排序。
    public void searchDemo01(SyncClient client){
        SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName(Config.CABINET_TABLENAME)//设置表名
                .indexName(Config.CABINET_TABLENAME_INDEX)//设置多元索引名
                .searchQuery(SearchQuery.newBuilder()
                        .query(QueryBuilders.bool()
                                .must(QueryBuilders.range("cabinet_available_size").greaterThan(0))//可用充电宝个数>0
                                .must(QueryBuilders.range("cabinet_powerPercent").greaterThan(50))//电量>50
                               .must(QueryBuilders.term("cabinet_isonline","online"))//状态=在线
                        )
                        .sort(new Sort(Arrays.asList(
                                new GeoDistanceSort("cabinet_geo",Arrays.asList("30.17110,120.29937")),//先按照距离远近升序
                                new FieldSort("cabinet_pricePerHour", SortOrder.ASC)//再按照价格升序
                        )))
                        .build())
                .addColumnsToGet(Arrays.asList("cabinet_manufacturers","cabinet_type","cabinet_pricePerHour","cabinet_location"))//指定返回哪些字段
                .build();
        SearchResponse response = client.search(searchRequest);
        System.out.println(response.getRows());
    }

返回结果

场景二。用户需要查询租赁时价在2元/小时之内，并且有可租用充电宝的机柜。按照距离远近排序。

查询代码

//场景二。用户需要查询租赁时价在2元/小时之内，并且有可租用充电宝的机柜。按照距离远近排序。
    public static void searchDemo02(SyncClient client){
        SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName(Config.CABINET_TABLENAME)//设置表名
                .indexName(Config.CABINET_TABLENAME_INDEX)//设置多元索引名
                .searchQuery(SearchQuery.newBuilder()
                        .query(QueryBuilders.bool()
                                .must(QueryBuilders.range("cabinet_pricePerHour").greaterThan(0).lessThanOrEqual(2))//时价在0-2元内
                                .must(QueryBuilders.range("cabinet_available_size").greaterThan(0))//可用充电宝个数>0
                                .must(QueryBuilders.term("cabinet_isonline","online"))//状态=在线
                        )
                        .sort(new Sort(Arrays.asList(
                                new GeoDistanceSort("cabinet_geo",Arrays.asList("30.17110,120.29937"))//按照距离远近升序
                        )))
                        .build())
                .addColumnsToGet(Arrays.asList("cabinet_manufacturers","cabinet_type","cabinet_pricePerHour","cabinet_location"))//指定返回哪些字段
                .build();
        SearchResponse response = client.search(searchRequest);
        System.out.println(response.getRows());
    }

返回结果

场景三。运维人员需要查询浙江省内，机柜检修时间戳在半年之前，或者已经下线的机柜。取十条记录。

查询代码

//场景三。运维人员需要查询浙江省内，机柜检修时间戳在半年之前或者已经下线的机柜。取十条记录。
    public static void searchDemo03(SyncClient client){
        SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName(Config.CABINET_TABLENAME)//设置表名
                .indexName(Config.CABINET_TABLENAME_INDEX)//设置多元索引名
                .searchQuery(SearchQuery.newBuilder()
                        .query(QueryBuilders.bool()
                                .must(QueryBuilders.term("cabinet_province","浙江省"))
                                .must(QueryBuilders.bool()
                                        .should(QueryBuilders.range("cabinet_overhaul_time").lessThan(2592000000L))
                                        .should(QueryBuilders.term("cabinet_isonline","offline"))
                                .minimumShouldMatch(1)
                        ))
                        .limit(10)
                        .sort(new Sort(Arrays.asList(
                                new GeoDistanceSort("cabinet_geo",Arrays.asList("30.17110,120.29937"))//按照距离远近升序
                        )))
                        .build())
                .addColumnsToGet(Arrays.asList("cabinet_manufacturers","cabinet_type","cabinet_pricePerHour","cabinet_location"))//指定返回哪些字段
                .build();
        SearchResponse response = client.search(searchRequest);
        System.out.println(response.getRows());
    }

返回结果

场景四。运维人员需要统计每个省份已经上线的机柜个数，并按照机柜型号分组，取出前五个型号的数据。

查询代码

//场景四。运维人员需要统计每个省份已经上线的机柜个数，并按照机柜型号分组，取出前五个型号的数据。
    public static void searchDemo04(SyncClient client){
        SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName(Config.CABINET_TABLENAME)//设置表名
                .indexName(Config.CABINET_TABLENAME_INDEX)//设置多元索引名
                .searchQuery(SearchQuery.newBuilder()
                        .query(QueryBuilders.matchAll())
                        .addGroupBy(GroupByBuilders.groupByField("groupByProvince","cabinet_province")
                                .addSubGroupBy(GroupByBuilders.groupByField("groupByType","cabinet_type").size(5)))
                        .build())
                .build();
        SearchResponse searchResponse = client.search(searchRequest);
        List<GroupByFieldResultItem> list = searchResponse.getGroupByResults().getAsGroupByFieldResult("groupByProvince").getGroupByFieldResultItems();
        for(GroupByFieldResultItem groupByFieldResultItem : list){
            System.out.println("省份：【"+groupByFieldResultItem.getKey()+"】 机柜数量：【"+groupByFieldResultItem.getRowCount()+"】");
            List<GroupByFieldResultItem> subList = groupByFieldResultItem.getSubGroupByResults().getAsGroupByFieldResult("groupByType").getGroupByFieldResultItems();
            for(GroupByFieldResultItem subItem : subList){
                System.out.println("型号："+subItem.getKey()+"，机柜数量："+subItem.getRowCount());
            }
        }
    }

返回结果

SQL操作

可通过
可通过表格存储控制台进行SQL查询，关于表格存储SQL相关操作请参考文档表格存储SQL。

创建数据表映射关系

进入表格存储控制台，实例管理-SQL查询页面。点击一键生成映射关系。

选择需要映射的表，点击生成SQL。

执行SQL，得到cabinet、cabinet_time、order三张表的映射。

DML操作示例

场景一。用户需要查询有可租用充电宝的机柜信息，按照机柜时价倒序排序，取前100条。

select * from cabinet where
cabinet_isonline = "online" and
cabinet_available_size > 0 and
cabinet_powerPercent > 50 
order by cabinet_pricePerHour asc limit 100;

用户需要查询租赁时价在2元/小时之内，并且有可租用充电宝的机柜，取100条。

select * from cabinet where cabinet_pricePerHour between 0 and 2 
and cabinet_available_size > 0 and cabinet_isonline = 'online' limit 100;

运维人员需要查询浙江省内，机柜检修时间戳在半年之前，或者已经下线的机柜。取十条记录

select * from cabinet where 
(cabinet_province = '浙江省' and cabinet_overhaul_time < 2592000000) 
or cabinet_isonline = 'offline' limit 10;

运维人员需要统计每个省份已经上线的机柜个数，并按照机柜型号分组

select cabinet_province,cabinet_type,count(*) cabinet_count,any_value(cabinet_Md5ID)
from cabinet
where cabinet_isonline = 'online' 
group by cabinet_province,cabinet_type order by cabinet_count

小结

表格存储Tablestore实现了表引擎和多元索引引擎。其中表引擎可以基于主键实现高并发数据读写，可满足元数据并发更新、订单数据新增等业务。多元索引引擎则提供了多字段组合查询、排序、统计聚合等功能，可在毫秒延迟内对百亿级元数据进行检索、轻量级分析。下一章节将为大家介绍如何通过Spark访问Tablestore实现离线流批计算。

联系我们

如对本章节所述有疑问或有其他问题需要咨询，欢迎加入钉钉群：“表格存储公开交流群-2”。群内提供免费的在线专家服务，欢迎扫码加入，群号23307953。

基于Tablestore的一站式物联网存储解决方案-数据操作篇

前言

准备工作

数据表读写

多元索引操作

什么是多元索引

创建多元索引

多元索引查询

SQL操作

创建数据表映射关系

DML操作示例

小结

联系我们

物联网

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景

基于Tablestore的一站式物联网存储解决方案-数据操作篇

前言

准备工作

数据表读写

多元索引操作

什么是多元索引

创建多元索引

多元索引查询

​

SQL操作

创建数据表映射关系

DML操作示例

小结

联系我们

物联网

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景