question

haripriyan.mr_191315 avatar image
haripriyan.mr_191315 asked Erick Ramirez commented

Can we filter on a date column using LIKE '2013%'?

[FOLLOW UP QUESTION TO #9296]

Change your query to retrieve videos made in 2013 or later? How to achieve this. In SQL we shall use like select * from videos_by_tag where added_date like '2013%' but in cassandra we don't have like and we have to use the full column value like added_date > '2013-01-00 00:00:00.0000'. Since the tables have only 5 rows we can view the least value in the table and write the query however if the table has millions of rows how do we know the value to be passed on a column?

data modeling
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered Erick Ramirez commented

In SQL, the LIKE operator example you provided is a string comparison with a "2013" prefix. You will need to use SSTable-Attached Secondary Index (SASI) or Apache Solr do be able to do complex text searches.

As you already worked out, you can do range queries on the rows within a partition. But if you wanted to run queries across the whole table (not just within one partition), then you will need to use Apache Spark to run analytics queries.

Cassandra is designed for OLTP workloads. OLAP queries particularly those which involve full table scans aren't ideal to be run via CQL since analytics queries are resource-intensive and will affect the performance of the OLTP workloads.

Spark is optimised for OLAP workloads and is recommended to analytics queries. Cheers!

2 comments Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

haripriyan.mr_191315 avatar image haripriyan.mr_191315 commented ·

Hi Erick, Thank you so much for your responses. I got a better idea based on your answers. Now I have one more query, can you give me an example of the range queries on the rows within a partition? Is it something like select * from videos_by_tag where added_date < '2015-10-01' and added_date > '2015-04-01' and where tag='cassandra'? Even for this query I need to be aware of the exact format of data value in the column added_date right?

0 Likes 0 ·
Erick Ramirez avatar image Erick Ramirez ♦♦ haripriyan.mr_191315 commented ·

The added_date column is a timestamp type:

CREATE TABLE videos_by_tag (
    tag text,
    video_id timeuuid,
    added_date timestamp,
    title text,
    PRIMARY KEY ((tag), video_id, added_date)
) WITH CLUSTERING ORDER BY (video_id ASC, added_date DESC);

so you need to specify the correct format as you already had in your original question. Cheers!

0 Likes 0 ·