How do I filter out records where the value is NULL?

Question

looking for a query to filter out all records where the opened_date is null. getting a Unable to coerce '' to a formatted date (long) error i tried.

select * from schema.table_name_account where opened_date = '' ;
select * from schema.table_name_account where opened_date = ' '  ;
select * from schema.table_name_account where opened_date in ('') ;  

thanks for the help ( i know null isnt supported in cassandra )

Answer 1

As you already stated, it is not possible to filter on a null value.

The reason for this is that Cassandra storage is sparse -- columns which do not have a value assigned are not stored in the database.

To illustrate, I'll use this example table of users:

 Name   | Username | Email          | Phone       | Address
--------+----------+----------------+-------------+---------
 Alice  | alice80  | [email protected] |             |
 Bob    | bob456   |                | 555-987-395 |
 Charli | ch4rli   |                |             |

In a traditional relational database, the record for Alice will have phone stored with a null value.

But in Cassandra, only the columns with a value are stored. On disk, it looks something like this:

 name='Alice' | username='alice80' | email='[email protected]'

Notice that neither the phone nor address fields are stored so they don't occupy any disk space, hence the term "sparse".

Since those columns are not stored on disk, Cassandra cannot search for them. Additionally, Cassandra is optimised for retrieving a single partition by primary key. In the case where you're retrieving records where a column is empty, it is no longer OLTP but an analytics workload so you'll need to use software like Spark or Solr to achieve it. Cheers!