0

I am sorry if this is basic question; I'm still ramping up on Cassandra.

Problem: Let's say I had a table. Each entry has a unique user ID as a "row key" and three columns (City, State, and Age)

I want to split this table into two tables with the same row keys but different columns. So there'd be one table called Location Table with two columns (City and State) and a second table called Age table with one column (Age).

Question: Is there a Cassandra command to split a table in this fashion?

2 Answers 2

1

There is no built-in command for that so you need to use some external tool, like:

  • DSBulk with which you can unload data from table to disk, and then load data selectively - see the example 3 from this blog post on how to use -m option.

  • Spark with Spark Cassandra connector - read data, select only columns that you need & write into new table(s). Main advantage of Spark is that it can do everything in memory, you don't need much disk space. something like this (not tested, did use spark-shell with Scala. Follow documentation to set necessary connection properties):

import org.apache.spark.sql.cassandra._

val df = spark.read
  .format("org.apache.spark.sql.cassandra")
  .options(Map( "table" -> "tbl_name", "keyspace" -> "ks_name"))
  .load()
// write locations
df.select("rowid", "city", "state")
  .createCassandraTableEx("ks_name", "location_table", 
    partitionKeyColumns = Some(Seq("rowid"))
// write age
df.select("rowid", "age")
  .createCassandraTableEx("ks_name", "age_table", 
    partitionKeyColumns = Some(Seq("rowid"))
0

Cassandra can't do that for itself - you have to do the work.

Question: Why are you splitting out such a "small" value into its own table?
There is a [small] storage and processing overhead in doing so, so I'm guessing you have some other reason (Data Security, perhaps?).

Also, do not store Age.
It changes - every single day - and making changes to every row in a table [every day] is an expensive operation, in terms of the database power needed to do it, the potential impact on your system while it's being done and to your Business [reputation] if you accidentally "miss" a day and everyone suddenly becomes a day "younger"!

Instead, store a fixed value like Date of Birth and calculate the Age, as and when you need it.

1
  • The example was intended to be for illustrative purposes only. The general reason I'm interested in splitting a table is to move some data to a non-Cassandra data source.
    – Craig
    Dec 26, 2021 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.