A good way to copy data from one Cassandra ColumnFamily to another on the same Keyspace (like SQL’s INSERT INTO)

cassandramigrationnosql

Trying to find a way to easily transfer all the rows from a Cassandra ColumnFamily/Table to another.

The COPY command, as I understand, is a good option. However, as it dumps all the data to .csv on disk and then loads it back, I can't help but wonder if there is a better way to do it in-engine.

A specific example of what I mean would be the INSERT * FROM my_table INTO my_other_table available in many SQL databases. Of course, I realize that Cassandra is NoSQL and therefore does not to work the same way – but it seems like something which might be available.

What is a good way to accomplish this?

Thanks very much!

Best Answer

cqlsh -k mykeyspace -e 'COPY fromTable(columnNames) TO STDOUT' | head -n -1 | 
cqlsh -k mykeyspace -e 'COPY toTable(columnNames) FROM STDIN'

Related Solutions

MySQL – Fastest Way to Move Database Between Servers

If you are changing versions, DO NOT MOVE THE mysql SCHEMA.

Why should you not move the mysql folder? It has to do with the authentication privileges.

The number of columns in mysql.user is different from version to version

If you run desc mysql.user

You will see 31 rows for MySQL 4.1
You will see 37 rows for MySQL 5.0
You will see 39 rows for MySQL 5.1
You will see 42 rows for MySQL 5.5

I wrote about this before

It is OK to move everything else. On the new machine that has MySQL 5.5.24, do this:

mv /var/lib/mysql /var/lib/mysql/mysql55
mkdir /var/lib/mysql
<scp or rsync /var/lib/mysql of MySQL 5.1.41 over to /var/lib.mysql of MySQL 5.5.24>
rm -f /var/lib/mysql/mysql/*
cp /var/lib/mysql/mysql55/* /var/lib/mysql/mysql/*
chown -R mysql:mysql /var/lib/mysql
service mysql start

So, the question remains:

How do you move the User Privileges in the old MySQL 5.1.41 to MySQL 5.5.24 ???

There are two ways to do this starting on the MySQL 5.1.41 machine:

METHOD #1 : Use pt-show-grants

This Percona Toolkit program move print out the User Permission in Pure SQL. You could run the result output into a Text File. Then, execute the Text File in MySQL 5.5.24. End of Story.

pt-show-grants ... > MySQLUserGrants.sql

METHOD #2 : Emulate pt-show-grants

I made my own technique for pt-show-grants

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Either way, move MySQLUserGrants.sql over to the MySQL 5.5.24 machine and execute the script

I wrote about this before : importing myisam 5.0 database into a 5.5 innodb server

Mysql – Scalable DBaaS that can scale to hundreds of terabytes with easy backup and restore

Barry Morris here from NuoDB. Answers below:

1) DBaaS (can be self hosted too) the can easily scale to terabytes of data (ie. big data)

NuoDB is a downloadable product that runs anywhere (laptop, rack, public cloud). We have not announced DBAAS at this point. NuoDB uses Key/Value stores at the storage layer so supported DB sizes will be related to whatever the particular KV store can handle.

2) Capable of very high throughput for real-time application that does many read/writes (although I will use caching too to reduce overload)

NuoDB is a general purpose database system designed for high-speed scaled-out transaction processing. There is no need for a separate cache tier because the NuoDB in-memory tier performs a similar function.

3) Being able to query the data using SQL, a RDBMS DB. Although if a NewSQL solution isn't available, I will go with NoSQL

Check. NuoDB is a SQL database.

4) Easy backup and restore big amount of data in case of data corruption. I know that many solutions are safe against server failure, but I worry about data corruption and I want to be able to restore the data back if that happens - IMPORTANT!

NuoDB supports JDBC, ODBC and other standards APIs, so backups can be done at the SQL level. Other alternatives include simply taking one of your storage managers offline and putting it somewhere safe.

5) I will be developing my web app / mobile app in ASP.NET, so I prefer a solution that have a .NET connector

NuoDB has an ODBC API and there is a community developed ASP.NET connector.

6) If hosted, I prefer hosting my DB on Amazon AWs.

NuoDB supports Amazon AWS

7) I don't want a self-managed solution and handle sharding and all the other complicated stuff

No sharding or partitioning (or caching, M/S replication etc) needed with NuoDB.

8) I want an affordable solution, not like Clustrix that costs thousands of dollars per node. I want a cost-effective solution where I can start small and pay more when my database grows (all idea behind cloud architecture, right?)

NuoDB is free forever for a baseline system.