Lindorm:DML

A SELECT clause specifies the columns to query and return in the result set. The SELECT clause also converts the query results in the result set before the query results are returned. A SELECT clause can consist of comma-separated selectors. A SELECT clause can also contain only a wildcard character (*) to specify all columns that are defined in a specified table. A SELECT statement can contain the following information:

• Selectors

  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 retrieved.

  • term: specifies a term. In most cases, a term is nested in 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 supported by Lindorm CQL.

  • function_name '(' [ selector ( ',' selector )* ] ')': specifies a function call in which parameters serve as selectors.

  • COUNT '(' '*' ')': specifies a special call to the COUNT function. The call is made to execute the COUNT(*) function. COUNT(*) calculates the number of non-empty results.

• WRITETIME function

  SELECT clauses support the WRITETIME function. This function can be used only in SELECT clauses. This function requires only one parameter that specifies a column name. This function can be used to query the metadata that is stored in each column. The metadata contains timestamps for the values in the specified column.

• where clause

  The WHERE clause specifies the rows to query. A WHERE clause consists of conditions that are associated with the primary key columns or columns on which secondary indexes are created. The conditions are specified by relational operators. All relational operators cannot be specified in a query. For example, non-equality operators cannot be specified for a partition key. The IN operator is considered as an equality operator. 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, you can 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';

  You cannot execute the following statement to perform a query. This is because the statement cannot select 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 operator can be specified only for 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 sample code provides an example:

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

  The preceding statement sorts rows based on the clustering order and returns the rows that appear after a row in which the value of blog_tile is John''s Blog and the value of post_at is 2012-01-01. In lexicographical order, if the serial number of a data row is less than or equal to the serial number of a row whose value in the post_at column is 2012-01-01 and greater than the serial number of a row whose value in the blog_title column is John''s Blog, the data row can be returned. 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 sample code provides an example on how to use tuple notation for an IN clause on clustering columns:

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

• Grouping results

  The GROUP BY clause can be used to group all the selected rows that contain 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 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, you do not need to specify the column in the GROUP BY clause.

  Note

  If the GROUP BY clause is specified, Lindorm CQL transfers data to a server node for processing. Therefore, the process may be time-consuming if a large volume of data is processed.

• Ordering results

  The ORDER BY clause allows you to specify the order in which the results are returned. In the ORDER BY clause, you can specify column names and the order in which the columns are sorted. 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 the ascending order. 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.

  • If a CLUSTERING ORDER is specified, the valid orders are the order that is specified by the CLUSTERING ORDER clause and the reverse of that order.

• Limiting results

  The LIMIT clause in a SELECT statement is used to limit the number of rows that are returned by a query.

• Allowing filtering

  By default, Lindorm CQL supports only SELECT queries in which no filtering clause is specified on the server side. These SELECT queries return result sets that contain all real-time records or partial real-time records. This is because the performance of these queries is predictable. The period of time that is required to run these queries is proportional to the number of returned results. You can use the LIMIT clause to limit the number of returned results.

  The ALLOW FILTERING clause can be explicitly used to run queries that contain filters. After you specify the ALLOW FILTERING clause in a query, the performance of the query may be unpredictable, even if the query selects only a small number of records from the total data that is stored in a cluster. The performance of the query varies based on the total data volume.

  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 queries are valid:

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

  In the preceding queries, Lindorm CQL ensures that the query performance is proportional to the number of returned records. In particular, if no users were 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 may still be affected by the number of nodes in the cluster. The volume of the data stored in the cluster is also an indirect factor that affects 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 records. The following section provides an invalid query:

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

  This query is determined to be an invalid query because Lindorm CQL may scan large volumes of data even if the query needs to return 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. 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;

• HOTDATA

  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 sample code provides an example on how to specify the HOTDATA keyword in a SELECT statement:

  SELECT HOTDATA * FROM persioninfo;

  If you do not specify the HOTDATA keyword for a query, the query scans the hot data and cold data. Then, the query returns the combined results.

• Operations on search indexes

  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 perform fuzzy matching on these columns. You can use a percentage sign (%) to specify an arbitrary number of characters to perform fuzzy matching. You can perform prefix matches, suffix matches, contains matches, and exact matches. The following sample code provides examples:

    SELECT * FROM persioninfo WHERE c2 LIKE 'v2';// An exact match. The value in the c2 column is v2. 
    SELECT * FROM kss.tb WHERE c2 LIKE '%v2'; // A prefix match. The value in the c2 column is v2 or an arbitrary-length string that is suffixed with v2. 
    SELECT * FROM kss.tb WHERE c2 LIKE 'v2%' ;// A suffix match. The value in the c2 column is v2 or an arbitrary-length string that is prefixed with v2. 
    SELECT * FROM kss.tb WHERE c2 LIKE '%v2%';// A contains match. The value in the c2 column is v2 or an arbitrary-length 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 sample code 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 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 sample code provides an example:

    CREATE TABLE persioninfo (name text PRIMARY KEY, city text,id int) WITH extensions = {'CONSISTENCY_TYPE':'strong', 'MUTABILITY':'IMMUTABLE'};
    CREATE SEARCH INDEX sidx on persioninf WITH COLUMNS (id,city);
    INSERT INTO persioninfo (name,city, id) VALUES ('MY','hangzhou', 18);
    SELECT name,id FROM persioninfo  WHERE search_query = 'city:hangzhou';

    Note

    Lindorm CQL does not allow you to use search_query in combination with other conditions. This is because search_query supports a wide range of syntax types. For example, the select * from table where search_query = 'c1:name' And column = 'a' statement is not supported.