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, which is located in the bin directory of tarball installations. For example, from a tarball installation:
    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"}]
    }}