SQL
SQL SELECT语句可以查询关键列、非关键列或任意组合。WHERE子句确定返回的行,如以下示例所示
// 根据主键查询一行
SELECT * FROM UserHistory
WHERE user_id = '10100' AND time_stamp = 1479265526;
// 查询某个 user_id 下的所有数据
SELECT * FROM UserHistory
WHERE user_id = '10100';
// 根据某个 user_id 下的某段时间的所有记录
SELECT * FROM UserHistory
WHERE user_id = '10100' AND time_stamp > 1478660726 AND time_stamp < 1479265526;
// 查询某个 user_id 所有收藏的记录
SELECT * FROM UserHistory
WHERE user_id = '10100' AND behavior_type = 'collect';
表格存储
表格存储使用单行查询
GetRow 和范围查询
GetRange 来检索数据。这两种方式能够提供对存储数据物理位置的快速高效访问,查询的性能只受到结果数据集大小的影响,不会受到表中数据总量大小的影响。
// 等同于 SELECT * FROM UserHistory WHERE user_id = '10100' AND time_stamp = 1479265526;
// 设置主键信息
PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
primaryKeyBuilder.addPrimaryKeyColumn('user_id', PrimaryKeyValue.fromString("10100"));
primaryKeyBuilder.addPrimaryKeyColumn('time_stamp', PrimaryKeyValue.fromLong(1479265526));
PrimaryKey primaryKey = primaryKeyBuilder.build();
// 读一行
SingleRowQueryCriteria criteria = new SingleRowQueryCriteria(TABLE_NAME, primaryKey);
// 设置读取最新版本
criteria.setMaxVersions(1);
GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
// 等同于 SELECT * FROM UserHistory WHERE user_id = '10100'
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria(TABLE_NAME);
// 设置起始主键
PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("10100"));
primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.INF_MIN);
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(primaryKeyBuilder.build());
// 设置结束主键
primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("10100"));
primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.INF_MAX);
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(primaryKeyBuilder.build());
// 设置读取最新版本
rangeRowQueryCriteria.setMaxVersions(1);
// 默认读取所有的属性列
GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
[backcolor=transparent]注意:
// 等同于 SELECT * FROM UserHistory WHERE user_id = '10100' AND time_stamp >= 1478660726 AND time_stamp < 1479265526;
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria(TABLE_NAME);
// 设置起始主键
PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("10100"));
primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.fromLong(1478660726));
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(primaryKeyBuilder.build());
// 设置结束主键
primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("10100"));
primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.fromLong(1479265526));
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(primaryKeyBuilder.build());
// 设置读取最新版本
rangeRowQueryCriteria.setMaxVersions(1);
// 默认读取所有的属性列
GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
SELECT * FROM UserHistory
WHERE user_id = '10100' AND time_stamp > 1478660726 AND time_stamp < 1479265526;
如果需要继续对属性列做条件查询,可以使用过滤器功能。查询某个 user_id 下所有的收藏记录,如下所示: // 等同于 SELECT * FROM UserHistory WHERE user_id = '10100' AND behavior_type = 'collect';
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria(TABLE_NAME);
// 设置起始主键
PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("10100"));
primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.INF_MIN);
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(primaryKeyBuilder.build());
// 设置结束主键
primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("10100"));
primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.INF_MAX);
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(primaryKeyBuilder.build());
// 设置属性列的过滤条件: behavior_type = 'collect'
SingleColumnValueFilter filter = new SingleColumnValueFilter("behavior_type", SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromString("collect"));
// 表格存储是 schemafree 模型,有些行不包括属性列 behavior_type
// 设置为 false 表示如果该行没有属性列 behavior_type,则不满足条件条件
filter.setPassIfMissing(false);
rangeRowQueryCriteria.setFilter(filter);
// 设置读取最新版本
rangeRowQueryCriteria.setMaxVersions(1);
// 默认读取所有的属性列
GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));
该查询等同于如下 SQL 语句:
SELECT * FROM UserHistory
WHERE user_id = '10100' AND behavior_type = 'collect';
当然,也可以通过如下方式来实现:
// 等同于 SELECT * FROM UserHistory WHERE user_id = '10100' AND behavior_type = 'collect';
RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria(TABLE_NAME);
// 设置起始主键
PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.INF_MIN);
primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.INF_MIN);
rangeRowQueryCriteria.setInclusiveStartPrimaryKey(primaryKeyBuilder.build());
// 设置结束主键
primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.INF_MAX);
primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.INF_MAX);
rangeRowQueryCriteria.setExclusiveEndPrimaryKey(primaryKeyBuilder.build());
// 设置数据过滤条件:user_id='10100' 并且 behavior_type = 'collect'
SingleColumnValueFilter filter1 = new SingleColumnValueFilter("user_id", SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromString("10100"));
SingleColumnValueFilter filter2 = new SingleColumnValueFilter("behavior_type", SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromString("collect"));
CompositeColumnValueFilter filter = new CompositeColumnValueFilter(CompositeColumnValueFilter.LogicOperator.AND);
filter.addFilter(filter1);
filter.addFilter(filter2);
rangeRowQueryCriteria.setFilter(filter);
以上操作对整张表进行了扫描,并找出 user_id='10100' AND behavior_type='collect' 的记录,但是由于是全表扫描,其效率会远远低于基于特定主键范围的查询。
[backcolor=transparent]注意:
过滤器 Filter 最多支持 10 个条件组合,可以用于 GetRow、BatchGetRow 和 GetRange 接口中。
过滤器 Filter 是对 GetRange 的数据在服务端进行过滤,并不会减少磁盘的 IO 次数,但是能够有效降低网络传输流量。
良好的主键设计能够大大提高范围查询的效率。
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。