edit-icon download-icon

Query data

Last Updated: Mar 01, 2018

SQL

You can use the SELECT statement to query primary key columns, non-primary key columns, or a combination of these columns, and use the WHERE clause to confirm the rows to return.

  1. // Query one row based on a primary key
  2. SELECT * FROM UserHistory
  3. WHERE user_id = '10100' AND time_stamp = 1479265526;
  4. // Query all data for a specified user_id
  5. SELECT * FROM UserHistory
  6. WHERE user_id = '10100';
  7. // Query all records of a specified user_id in a specified time period
  8. SELECT * FROM UserHistory
  9. WHERE user_id = '10100' AND time_stamp > 1478660726 AND time_stamp < 1479265526;
  10. // Query all favorite records for a specified user_id
  11. SELECT * FROM UserHistory
  12. WHERE user_id = '10100' AND behavior_type = 'collect';

Table Store

In Table Store, the data query API can be used to retrieve data. For single row queries, the GetRow and GetRange APIs provide an efficient way to access the physical location of stored data. This means only the query performance is affected by the size of the resulting dataset, rather than the total volume of data in the table.

  • By providing all primary key information, you can use the GetRow API to quickly query the specified row of data as follows.

    1. // SELECT * FROM UserHistory WHERE user_id = '10100' AND time_stamp = 1479265526;
    2. // Set primary key information
    3. PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    4. primaryKeyBuilder.addPrimaryKeyColumn('user_id', PrimaryKeyValue.fromString("10100"));
    5. primaryKeyBuilder.addPrimaryKeyColumn('time_stamp', PrimaryKeyValue.fromLong(1479265526));
    6. PrimaryKey primaryKey = primaryKeyBuilder.build();
    7. // Read a row
    8. SingleRowQueryCriteria criteria = new SingleRowQueryCriteria(TABLE_NAME, primaryKey);
    9. // Set the latest version to be read
    10. criteria.setMaxVersions(1);
    11. GetRowResponse getRowResponse = client.getRow(new GetRowRequest(criteria));
  • You can use the GetRange API to query all data of a specified user_id as follows.

    1. // Equivalent to SELECT * FROM UserHistory WHERE user_id = '10100'
    2. RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria(TABLE_NAME);
    3. // Set StartPrimaryKey
    4. PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    5. primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("10100"));
    6. primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.INF_MIN);
    7. rangeRowQueryCriteria.setInclusiveStartPrimaryKey(primaryKeyBuilder.build());
    8. // Set EndPrimaryKey
    9. primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    10. primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("10100"));
    11. primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.INF_MAX);
    12. rangeRowQueryCriteria.setExclusiveEndPrimaryKey(primaryKeyBuilder.build());
    13. // Set the latest version to be read
    14. rangeRowQueryCriteria.setMaxVersions(1);
    15. // Read all attribute columns by default
    16. GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));

    Note:

    • When using the GetRange API, you must specify the start point for all primary keys. However, the ranges of each primary key are not connected by the AND operator. When sorting rows from the first to the last primary key, priority is given to the primary key added first. If this primary key is in the GetRange operation’s start-end primary key range, data from the corresponding row is read. For example, if the range for two primary keys is (‘a’,5)-(‘c’,10), data with the primary key (‘b’,4) is read because ‘a’ < ‘b’ < ‘c’ .
    • GetRange supports the limit and direction parameters, which control the number of rows in the results set and the order in which rows are read.
    • To prevent network delays, the GetRange API implements a limit on the returned results set. It also determines the next_start_primary_key returned in the response. If this is blank, it indicates all results have been returned; otherwise, the API continues reading results.
    • GetRange supports the filter function.
    • Table Store supports the multiple data versions function. When using the GetRow and GetRange APIs, you can specify the range of historical attribute column data versions to read.
  • You can use the GetRange API to query all data from a specified time period as follows.

    1. // SELECT * FROM UserHistory WHERE user_id = '10100' AND time_stamp >= 1478660726 AND time_stamp < 1479265526;
    2. RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria(TABLE_NAME);
    3. // Set StartPrimaryKey
    4. PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    5. primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("10100"));
    6. primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.fromLong(1478660726));
    7. rangeRowQueryCriteria.setInclusiveStartPrimaryKey(primaryKeyBuilder.build());
    8. // Set EndPrimaryKey
    9. primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    10. primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("10100"));
    11. primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.fromLong(1479265526));
    12. rangeRowQueryCriteria.setExclusiveEndPrimaryKey(primaryKeyBuilder.build());
    13. // Set the latest version to be read
    14. rangeRowQueryCriteria.setMaxVersions(1);
    15. // Read all attribute columns by default
    16. GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));

    This query is equivalent to the following SQL statement.

    1. SELECT * FROM UserHistory
    2. WHERE user_id = '10100' AND time_stamp > 1478660726 AND time_stamp < 1479265526;
  • To perform condition checks on attribute columns, you can use the filter function. For example, to query all favorite records for a specified user_id as follows.

    1. // SELECT * FROM UserHistory WHERE user_id = '10100' AND behavior_type = 'collect';
    2. RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria(TABLE_NAME);
    3. // Set StartPrimaryKey
    4. PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    5. primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("10100"));
    6. primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.INF_MIN);
    7. rangeRowQueryCriteria.setInclusiveStartPrimaryKey(primaryKeyBuilder.build());
    8. // Set EndPrimaryKey
    9. primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    10. primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.fromString("10100"));
    11. primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.INF_MAX);
    12. rangeRowQueryCriteria.setExclusiveEndPrimaryKey(primaryKeyBuilder.build());
    13. // Set the attribute column filter condition: behavior_type = 'collect'
    14. SingleColumnValueFilter filter = new SingleColumnValueFilter("behavior_type", SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromString("collect"));
    15. // Table Store is a schemafree model, so some rows do not have the attribute column behavior_type
    16. // Set to false, indicating that, if this row does not have the attribute column behavior_type, it does not satisfy the conditions
    17. filter.setPassIfMissing(false);
    18. rangeRowQueryCriteria.setFilter(filter);
    19. // Set the latest version to be read
    20. rangeRowQueryCriteria.setMaxVersions(1);
    21. // Read all attribute columns by default
    22. GetRangeResponse getRangeResponse = client.getRange(new GetRangeRequest(rangeRowQueryCriteria));

    This query is equivalent to the following SQL statement.

    1. SELECT * FROM UserHistory
    2. WHERE user_id = '10100' AND behavior_type = 'collect';

    You can also achieve this using the following method.

    1. // SELECT * FROM UserHistory WHERE user_id = '10100' AND behavior_type = 'collect';
    2. RangeRowQueryCriteria rangeRowQueryCriteria = new RangeRowQueryCriteria(TABLE_NAME);
    3. // Set StartPrimaryKey
    4. PrimaryKeyBuilder primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    5. primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.INF_MIN);
    6. primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.INF_MIN);
    7. rangeRowQueryCriteria.setInclusiveStartPrimaryKey(primaryKeyBuilder.build());
    8. // Set EndPrimaryKey
    9. primaryKeyBuilder = PrimaryKeyBuilder.createPrimaryKeyBuilder();
    10. primaryKeyBuilder.addPrimaryKeyColumn("user_id", PrimaryKeyValue.INF_MAX);
    11. primaryKeyBuilder.addPrimaryKeyColumn("time_stamp", PrimaryKeyValue.INF_MAX);
    12. rangeRowQueryCriteria.setExclusiveEndPrimaryKey(primaryKeyBuilder.build());
    13. // Set the data filer conditions: user_id='10100' and behavior_type = 'collect'
    14. SingleColumnValueFilter filter1 = new SingleColumnValueFilter("user_id", SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromString("10100"));
    15. SingleColumnValueFilter filter2 = new SingleColumnValueFilter("behavior_type", SingleColumnValueFilter.CompareOperator.EQUAL, ColumnValue.fromString("collect"));
    16. CompositeColumnValueFilter filter = new CompositeColumnValueFilter(CompositeColumnValueFilter.LogicOperator.AND);
    17. filter.addFilter(filter1);
    18. filter.addFilter(filter2);
    19. rangeRowQueryCriteria.setFilter(filter);

    This operation scans the entire table and finds records that meet the conditions user_id='10100' AND behavior_type='collect'. However, as this is a full table scan, it is less efficient than a query that is based on a specified primary key range.

    Note:

    • Filters support combinations of up to 10 conditions and can be used in the GetRow, BatchGetRow, and GetRange APIs.
    • Filters are applied to GetRange data at the server side, so they do not reduce the number of disk I/O operations. However, filters effectively reduce traffic transmitted over the network.
    • A well-designed primary key can significantly improve the efficiency of range queries.
Thank you! We've received your feedback.