Is there difference in storing a list of floats vs. denormalising into multiple rows?

user_304151 0
Viewed 38

I need to store multiple floating point numbers per record in Cassandra. My current schema looks like:

 CREATE
  TABLE
        data_point
      ( account ASCII
      , groupkey TINYINT
      , productid TEXT
      , vectors LIST<FLOAT>
      , PRIMARY KEY ((account, groupkey), productid))
   WITH CLUSTERING
  ORDER
     BY
      ( productid ASC
      );

Each record has 1280 floats. These rows, once inserted, are never updated or deleted. While this works, I've been thinking if it better to have these in separate 1280 rows.

 CREATE
  TABLE
        data_point
      ( account ASCII
      , groupkey TINYINT
      , productid TEXT
      , vector FLOAT
      , PRIMARY KEY ((account, groupkey), productid))
   WITH CLUSTERING
  ORDER
     BY
      ( productid ASC
      );

The Datastax docs reads:

Collections are meant for storing/denormalizing relatively small amount of data.

...but I'm unsure what defines a little or lot. The ordering of the list is not relevant. The rows are never individually read. All reads come from Spark and use token ranges to read large swathes of data.

cassandra
2 Answers

If data is never changing, then use frozen version of the list, so all points will be stored as one binary object:

vectors frozen<LIST<FLOAT>>

Using the separate rows make sense only if you need to read only one value, or something like. If you always read the whole dataset - use frozen list.

Alex_Ott_186270

I would echo Alex's advice, a frozen list would suit your use case better than the non-frozen above - however there is also some points I would add.

On the 2nd table example, there is no additional column to denote the different list items when normalized - the primary key remains the same, so in essence that would store just 1 value per primary key and not 1280 you intended. There would have to be an additional column within the key to make it a unique row per list entry still.

For the 1st table, while you can use a frozen list - if there is no actual order to the items within the list and no duplication, you could opt for a set which would be simpler since there is no ordinal position being stored / considered. (The lack of any ordering denoted in the 2nd table design is the catalyst for the consideration)

user_1111240
Related