How to delete a range of rows from Cassandra using c#

Question

I am trying to get this program to run. The database is created, and there are values in it. Column "ts" of type BigInt is the primary key. When I try to execute

session.Execute(deleteAll);

it throws an exception. What is the correct way to delete all rows in Cassandra from c# where a key is between value1 and value2?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Cassandra;
using Cassandra.Data;

namespace CasandraGettingStarted
{
    class Program
    {
        static long ninetySeventyTicks = new DateTime(1970, 1, 1).Ticks;
        static string symbol = "SPX";

        static void Main(string[] args)
        {
            StartCassandra();
        }

        protected static long ToUnixTimestampMicro(DateTime dateTime)
        {
            return (dateTime.Ticks - ninetySeventyTicks) / 10;
        }

        static DateTime ConvertFromUnixTimestampMicro(double timestamp)
        {
            return new DateTime((long)timestamp * 10).AddYears(1969);//.ToLocalTime();
        }

        static void StartCassandra()
        {
            try
            {
                System.Random random = new Random(123456);

                Cluster cluster = Cluster.Builder().AddContactPoint("127.0.0.1").Build();
                ISession session = cluster.Connect("timeseries");

                double minPrice = 1100.00;
                double maxPrice = 1200.00;

                for (int i = 0; i < 10; i++)
                {
                    //long timestamp = (DateTime.Now.Ticks - new DateTime(1970, 1, 1).Ticks) / 10;
                    System.DateTime nowMicro = System.DateTime.Now;
                    long timestamp = ToUnixTimestampMicro(nowMicro);
                    System.DateTime nowMicro1 = ConvertFromUnixTimestampMicro(timestamp);

                    Console.WriteLine(nowMicro.ToString("HH:mm:ss.ffffff"));
                    Console.WriteLine(nowMicro1.ToString("HH:mm:ss.ffffff"));

                    double bidD = random.NextDouble();
                    bidD = bidD * (maxPrice - minPrice) + minPrice;
                    double askD = bidD + random.NextDouble();

                    decimal bid = (decimal)bidD;
                    decimal ask = (decimal)askD;

                    bid = decimal.Round(bid, 2);
                    ask = decimal.Round(ask, 2);

                    Console.WriteLine("Adding {0} {1} {2} {3}", timestamp, symbol, bid, ask);

                    var batchStmt = new BatchStatement();
                    var v2Insert = new SimpleStatement("insert into underlying_data " +
                        "(ts, symbol, bid, ask) values(?, ?, ?, ?);");
                    batchStmt.Add(v2Insert.Bind(timestamp, symbol, bid, ask));

                    session.Execute(batchStmt);
                    // Insert Data
                    //session.Execute("insert into underlying_data (ts, symbol, bid, ask) values (timestamp, 'SPX', bid, ask)");

                    //Thread.Sleep(1);
                }

                RowSet result = session.Execute("select * from underlying_data");

                foreach (Row row in result)
                    Console.WriteLine("{0} {1} {2} {3}", row["ts"], row["symbol"], row["bid"], row["ask"]);


                string deleteAll = @"select * from underlying_data where ts > ";
                deleteAll += ninetySeventyTicks.ToString();

                session.Execute(deleteAll);

                RowSet rows = session.Execute("select * from underlying_data");

                foreach (Row row in result)
                    Console.WriteLine("{0} {1} {2} {3}", row["ts"], row["symbol"], row["bid"], row["ask"]);
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            // Wait for enter key before exiting
            Console.ReadLine();
        }
    }
}

Aaron Aaron · Accepted Answer · 2014-09-29T16:58:38

session.Execute(deleteAll);

First of all, it looks like deleteAll is defined as a SELECT query:

string deleteAll = @"select * from underlying_data where ts > ";
    deleteAll += ninetySeventyTicks.ToString();

So that's not going to delete anything. In fact, Cassandra only allows the = and IN operators on a primary key SELECT. Although you didn't explicitly mention it, I'm sure the exception you saw was something like:

[Invalid query] message="Only EQ and IN relation are supported on the partition key (unless you use the token() function)"

As the exception indicates, you can't use > or <, unless you encapsulate the values with the token() function. But token() won't give you want you want, because primary key tokens are not ordered.

Likewise, if you attempt an actual DELETE with anything other than =, you'll get this:

[Invalid query] message="Invalid operator < for PRIMARY KEY part id"

And unlike SELECT, a DELETE won't allow you do use greater/less than operators with the token() function. The only alternative to deleting with the = operator and a primary key, is to give DELETE an IN clause of multiple primary keys.

You should also be aware that deleting data on a regular basis will produce a lot of tombstones, so delete-heavy usage patterns are generally discouraged with Cassandra. For more information, check the documentation on the CQL DELETE command.

How to delete a range of rows from Cassandra using c#

1 Answers