Querying CQL collections

Example for creating a table that contains a CQL collection set.

DSE Search supports CQL collections. In this example, you create a table containing a CQL set collection of famous quotations.

Procedure

  1. Start DataStax Enterprise as a DSE Search node.
  2. Start cqlsh.
  3. Create a keyspace and a table for a collection column and other columns, and then insert data.
    CREATE KEYSPACE mykeyspace
      WITH REPLICATION = {'class':'NetworkTopologyStrategy', 'Solr':1};
    
    USE mykeyspace;
    
    CREATE TABLE mysolr (
      id text PRIMARY KEY,
      name text,
      title text,
      quotes set text
    );
  4. Download the quotations.zip file.
  5. Extract the quotations.zip file, copy the insert commands, and paste each command on the cqlsh command line.
  6. Run the following command:
    installation_location/bin/dsetool create_core mykeyspace.mysolr generateResources=true reindex=true
    If you are recreating the mykeyspace.mysolr core, use the reload_core command instead of the create_core command.
    There is no output from this command. You can search data after indexing finishes.
  7. In cqlsh, search the indexed data to find quotes like succ*.
    SELECT * FROM mykeyspace.mysolr WHERE solr_query='quotes:succ*';
    Because you created the core using automatically generated resources, the search index config defines the request handler for using CQL for search queries.
  8. Using a browser, search-indexed data using the Solr HTTP API to find titles like succ*.
    http://localhost:8983/solr/mykeyspace.mysolr/
      select?q=quotes%3Asucc*&wt=json&indent=on&omitHeader=on
    {
      "response":{"numFound":2,"start":0,"docs":[
          {
            "id":"126",
            "title":"Success",
            "quotes":["If A is success in life, then A equals x plus y
              plus z. Work is x; y is play; and z is keeping your mouth
              shut."],
            "name":"Albert Einstein"},
            {
            "id":"125",
            "title":"Success",
            "quotes":["Always bear in mind that your own resolution to
              succeed is more important than any one thing.",
              "Better to remain silent and be thought a fool than to speak
              out and remove all doubt."],
            "name":"Abraham Lincoln"}]
    }}