ApsaraDB for HBase:DML

The following types of selectors are supported:

• column_name: specifies the name of a column in the specified table. The values of the specified column are queried.
• term: specifies a term. In most cases, a term is nested within other selectors, such as functions. If you specify only a term as a selector, the query returns a result set that contains rows whose column values match the term.
• CAST '(' selector AS cql_type ')': specifies a force conversion. This selector can be used to convert a nested selector into another selector that is supported by Lindorm CQL.
• function_name '(' [ selector ( ',' selector )* ] ')': specifies a function call in which parameters themselves are selectors.
• COUNT '(' '*' ')': specifies a special call to the COUNT function. The call is represented as COUNT(*). This call is used to calculate the number of non-empty results.

The WHERE clause specifies the rows to query. A WHERE clause consists of relations on the primary key columns or columns that have secondary indexes defined. Not all relations can be specified in a query. For example, non-equity relations are not allowed on a partition key. The IN operator is considered as an equity relation. For a given partition key, the clustering columns are used to order rows. The relations that can be specified for clustering columns are restricted to the relations that can select a contiguous set of rows. For example, execute the following statement to create the posts table:

CREATE TABLE posts (
    userid text,
    blog_title text,
    posted_at timestamp,
    entry_title text,
    content text,
    category int,
    PRIMARY KEY (userid, blog_title, posted_at)
)    

You can execute the following statement to perform a query:

 SELECT entry_title, content FROM posts
 WHERE userid = 'john doe'
   AND blog_title='John''s Blog'
   AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31'

However, you cannot execute the following statement to perform a query. This is because the statement cannot query a contiguous set of rows when no secondary indexes are specified.

SELECT entry_title, content FROM posts
 WHERE userid = 'john doe'
   AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31'

The IN relation can be specified only on the last column of the partition key and the last column of the primary key. You can also use tuple notation to combine clustering columns. The following statement provides an example:

SELECT * FROM posts
 WHERE userid = 'john doe'
   AND (blog_title, posted_at) > ('John''s Blog', '2012-01-01')

The preceding statement is used to query the rows whose blog_tile is John's Blog and post_at is 2012-01-01 and sort the rows into clustering order. If a row whose post_at is less than or equal to 2012-01-01 and blog_title is greater than John's Blog, the row is returned. However, the following case is excluded:

SELECT * FROM posts
 WHERE userid = 'john doe'
   AND blog_title > 'John''s Blog'
   AND posted_at > '2012-01-01'

Tuple notation can also be used for IN clauses on clustering columns. The following statement provides an example:

SELECT * FROM posts
 WHERE userid = 'john doe'
   AND (blog_title, posted_at) IN (('John''s Blog', '2012-01-01'), ('Extreme Chess', '2014-06-01'))

The GROUP BY clause can be used to group all the selected rows that share the same values for a set of columns into a single row. After the GROUP BY clause is specified, you can group rows only at the partition key or clustering column level. Therefore, you can specify the names of the primary key columns as the parameters for the GROUP BY clause only in the order in which the columns are specified in the primary key. If a primary key column is limited due to the equality constraint, this column cannot be specified in the GROUP BY clause.

The ORDER BY clause allows you to specify the order in which the results are returned. You can specify column names and an order as the parameters for the ORDER BY clause. To specify the ascending order, you can use the ASC keyword. To specify the descending order, you can use the DESC keyword. The default order is ascending. The possible orders are limited by the CLUSTERING ORDER clause defined on the table.

• If no CLUSTERING ORDER clause is specified, the valid orders are the order that is defined by clustering columns and the reverse of that order.
• Otherwise, the valid orders are the order that is specified by the CLUSTERING ORDER clause and the reverse of that order.

For example, you can execute the following statements to create a table that records user profiles and create a secondary index on the birth year column. The user profiles contain the username, first name, last name, birth year, and residence country or region of each user.

CREATE TABLE users (
    username text PRIMARY KEY,
    firstname text,
    lastname text,
    birth_year int,
    country text
)

CREATE INDEX ON users(birth_year);

The following two queries are valid:

SELECT * FROM users;
SELECT * FROM users WHERE birth_year = 1981;

In the preceding two queries, Lindorm CQL ensures that the query performance is proportional to the number of returned results. In particular, if no users are born in 1981, the performance of the second query does not vary based on the number of user profiles that are stored in the database. No direct relationship exists between the query performance and the number of user profiles. Due to the implementation of the secondary index, the performance of the second query is still based on the number of nodes in the cluster. The volume of the data stored in the cluster is also an indirect factor on the performance of the second query. In practice, both queries may return large result sets. However, you can always use the LIMIT clause to limit the number of returned results. The following query is rejected:

SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR';

This is because Lindorm CQL may still scan large amounts of data even if the query returns only a small number of results. In most cases, this query scans all indexed rows for users who were born in 1981 even if only a few users are from France. However, you can specify the ALLOW FILTERING clause to force the system to run the query. The following query is valid:

SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR' ALLOW FILTERING;

You can separate hot data and cold data when you create tables. If you want to query hot data, you can specify the HOTDATA keyword in a SELECT statement. The following statement provides an example:

SELECT HOTDATA * FROM persioninfo;

Lindorm CQL supports fuzzy queries, multi-dimensional queries, and sorting for columns on which search indexes are created.

• Common fuzzy matches
  You can create search indexes on columns. When you execute SELECT statements, you can implement fuzzy matching on these columns. You can use a percentage sign (%) to specify an arbitrary number of characters to perform fuzzy matches. Common fuzzy matches include prefix matches, suffix matches, contains matches, and exact matches. The following statements provide examples:

  SELECT * FROM persioninfo WHERE c2 LIKE 'v2';// The exact match. The value of the c2 column is v2. 
  SELECT * FROM kss.tb WHERE c2 LIKE '%v2' // The prefix match. The value of the c2 column is v2 or an arbitrary length of string that is suffixed with v2. 
  SELECT * FROM kss.tb WHERE c2 LIKE 'v2%' // The suffix match. The value of the c2 column is v2 or an arbitrary length of string that is prefixed with v2. 
  SELECT * FROM kss.tb WHERE c2 LIKE '%v2%'// The contains match. The value of the c2 column is v2 or an arbitrary length of string that contains v2. 

• search_query keyword
  You can specify the search_query keyword in queries. Lindorm CQL allows you to include a statement that is similar to a Solr query statement into a SELECT statement. This feature helps meet various requirements for queries. The following statement provides an example:

  SELECT selectors
    FROM table
    WHERE (indexed_column_expression | search_query = 'search_expression') 
    [ LIMIT n ]
    [ ORDER BY column_name ] ;

  In a SELECT statement, the search_expression parameter is specified after the search_query keyword. For more information about the limits on the characters that can be specified in the search_expression parameter, see Local parameters in queries. You can specify the following common options after the search_query keyword:

  {
    "q": query_expression (string), 
    "fq": filter_query_expression(s) (string_or_array_of_strings, ...),  
    "facet": facet_query_expression (object) 
    "sort": sort_expression (string), 
    "start": start_index(number),
    timeAllowed: search_time_limit_ms,  
    "TZ": zoneID),   //  Any valid zone ID in java TimeZone class                            
    "paging": "driver" (string),
    "distrib.singlePass": true|false (boolean),
    "shards.failover": true|false (boolean),    // Default: true
    "shards.tolerant": true|false (boolean),    // Default: false
    "commit": true|false (boolean),
    "route.partition": partition_routing_expression (array_of_strings), 
    "route.range": range_routing_expression (array_of_strings), 
    "query.name": query_name (string),
  }

  The following statements provide an example on how to use search_query:

  CREATE TABLE persioninfo (name text PRIMARY KEY, city text,id int, friend nested) WITH extensions = {'CONSISTENCY_TYPE':'strong', 'MUTABILITY':'IMMUTABLE'};
  CREATE SEARCH INDEX sidx on persioninf WITH COLUMNS (id,city);
  INSERT INTO persioninfo (name,city, id, friend) VALUES ('MY','hangzhou', 18, '[{"lili":18}, {"gg":20}]');
  SELECT name,id FROM persioninfo  WHERE search_query = '+city:hangzhou +age:[1 TO 18]';

  Note Lindorm CQL does not allow you to use search_query clauses in combination with other query conditions. This is because search_query clauses already have rich semantics. For example, the select * from table where search_query = 'c1:name' And column = 'a' statement is not supported.