Querying CQL collections

In this example, you create a table containing a CQL collection set of famous quotations. Then, you insert data into the table by copying/pasting INSERT commands from a file that you download.

DataStax Enterprise supports CQL collections. In this example, you create a table containing a CQL set collection of famous quotations. You insert data into the table by copying/pasting INSERT commands from a file that you download.

Next, you insert a collection into Cassandra, index the data in DSE Search, and finally, query the search index.

Procedure

  1. Start DataStax Enterprise as a DSE Search node.
  2. Start cqlsh.
  3. Create a keyspace and a table consisting of a collection column and other columns, and then insert some data for DSE Search to index.
    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 INSERT commands in the quotations.zip file. Unzip the quotations.zip file that you downloaded, copy the insert commands, and paste the commands on the cqlsh command line.
  5. Run the following command, which is located in the bin directory of tarball installations. For example, from a tarball installation:
    install_location/bin/dsetool create_core mykeyspace.mysolr generateResources=true reindex=true
    If you are recreating the mykeyspace.mysolr core, use the reload_core instead of the create_core command.
    There is no output from this command. You can search Solr data after indexing finishes.
  6. In cqlsh, search Solr-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 solrconfig defines the request handler for using CQL for Solr queries.
  7. Using a browser, search Solr-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"}]
    }}