Inserting null values into cassandra

Question

I have some fields that I am storing into Cassandra, but some of them could be null at any given point. As there are quite a lot of them, it makes the code much more readable if I don’t check each one for null before adding it to the INSERT.

Is there any harm in doing so?

EDIT!!

There is a jira ticket that I found. But I am unable to understand what solution was finally implemented from the ticket. https://issues.apache.org/jira/browse/CASSANDRA-7304

score 21 · Answer 1 · edited May 07 '20 at 03:31

21

The beautiful thing about Cassandra's new storage engine is the ability to NOT store values. What it means is what it was meant to be: a null value is simply a value that should not be there.

This gives great flexibility, because a null value not explicitly (or implicitly, see later) inserted won't take storage space, nor use processing power and IO bandwidth.

Indeed, it is pretty easy to populate a row with null values:

INSERT INTO mytable (pk, c2, c3) VALUES (0x1234, null, null);

This way you are explicitly telling C* to store a null value in both c2 and c3. However, you could get the same macroscopic effect with:

INSERT INTO mytable (pk) VALUES (0x1234);

Notice that I say macroscopic effect, because when you explicitly insert a null value, C* will insert a tombstone under the hood. In the long run this will bite you, due to how C* perform searches, compactions, etc... so you should avoid whenever possible, the second version will perform much better.

Now, there is also a trap: you can also create tombstones implicitly. This will happen when you use the TTL features builtin in Cassandra.

In conclusion, if you care about yourself I'd suggest to NOT performing any null value inserts. Do a check at application level, you'll save time (and money) later, eg during reads.

edited May 07 '20 at 03:31

Eugene

117,005
15
201
306

answered Nov 21 '16 at 11:48

xmas79

5,060
2
14
35

Are there any reasons to explicitly insert null values? – andreycha Mar 06 '19 at 15:32
No, explicitly adding null values will hurt you. – xmas79 Mar 07 '19 at 07:03
I understand that. What I mean is if there is an opportunity to explicitly insert null in CQL, it was obviously left on purpose. So I wonder what are the reasons to insert null values. – andreycha Mar 08 '19 at 09:14
How could you delete a single value? – xmas79 Mar 08 '19 at 13:46
That'd be an update already :). But I got your point, thank you! – andreycha Mar 11 '19 at 09:34
Would inserting an empty string ('') or an empty node ([]) to signify emptiness lead to negative performance effect? – Hado99 Aug 18 '20 at 07:09

Ashraful Islam · Answer 2 · 2016-11-21T06:34:24.090

16

Inserting a null value creates a tombstone.
You should not create tombstone :
1. Tombstone take up space and can substantially increase the amount of storage you require.
2. Querying tables with a large number of tombstones causes performance problems and it causes Latency and heap pressure.

edited Nov 21 '16 at 06:34

answered Nov 21 '16 at 06:09

Ashraful Islam

12,470
3
32
53

can you suggest something that I can do? I dont want to check every column during each insert. Also I dont want to create the query dynamically in every case as it will take up a lot of time. – ArchitGarg Nov 21 '16 at 08:49
You can write your own code or Modify QueryBuilder's INSERT and UPDATE code to unset if null insert – Ashraful Islam Nov 21 '16 at 09:13
4

@BdLearner Cassandra 2.2 adds the ability to distinguish between null and unset parameters in native protocol v4. With previous versions of the protocol, when using a prepared statement you had to bind all its parameters or get an error. When using protocol v4+, the driver will now implicitly set missing values to unset (as long as missing values are not part of the partition key). Applications can also explicitly provide unset values using cassandra.query.UNSET_VALUE. https://www.datastax.com/blog/2015/07/python-driver-260-cassandra-22-features – Ashraful Islam Oct 17 '19 at 19:48

Inserting null values into cassandra

2 Answers2

Linked