Import and export schema in cassandra

Question

How to import and export schema from Cassandra or Cassandra cqlsh prompt?

Sheila

Answer 1

To export keyspace schema:

cqlsh -e "DESC KEYSPACE user" > user_schema.cql

To export entire database schema:

cqlsh -e "DESC SCHEMA" > db_schema.cql

To import schema open terminal at 'user_schema.cql' ('db_schema.cql') location (or you can specify the full path) and open cqlsh shell. Then use the following command to import keyspace schema:

source 'user_schema.cql'

To import full database schema:

source 'db_schema.cql'

Chilli

Thanks, works perfect! (I had to include IP/hostname after cqlsh because our instance uses an rpc_address in the config). – Retrace

I am trying to export my schema called crmdata with its data, can you please tell me where do i need to run this cmd cqlsh -e "crmdata" > db_schema.cql – Brewage

Answer 2

Everything straight from the command line. No need to go into cqlsh.

Import schema (.cql file):

$ cqlsh -e "SOURCE '/path/to/schema.cql'"

Export keyspace:

$ cqlsh -e "DESCRIBE KEYSPACE somekeyspace" > /path/to/somekeyspace.cql

Export database schema:

$ cqlsh -e "DESCRIBE SCHEMA" > /path/to/schema.cql

Canard

Answer 3

If using cassandra-cli, you can use the 'show schema;' command to dump the whole schema. You can restrict to a specific keyspace by running 'use keyspace;' first.

You can store the output in a file, then import with 'cassandra-cli -f filename'.

If using cqlsh, you can use the 'describe schema' command. You can restrict to a keyspace with 'describe keyspace keyspace'.

You can save this to a file then import with 'cqlsh -f filename'.

Hyaloid

Hi Richard, Thank for your reply. You have specified import the keyspace in both service. But, I need to both service in how to import and export keyspace on cassandra-cli and cqlsh?. Please provide the information with syntax. – Sheila

@Sheila I think you might have to pay him for that. – Jessi

If you used cassandra-cli to create your schema then use the cassandra-cli commands I wrote above. If you used cqlsh then use the cqlsh commands. Don't use both and note that cassandra-cli won't include cql3 tables in its output. – Hyaloid

Thank u Richard, How to export schema in cqlsh using command?. – Sheila

It's written in my answer. Use 'describe schema'. – Hyaloid

Richard, its working fine, but where it is stored the exported schema we have used the "describe schema" command and which format it will be stored?. – Sheila

It's the text output. Put it in a file and import with the 'cqlsh -f' command. – Hyaloid

Thank you Richard, If possible to write the 'describe keyspace' command output text on any file format?. – Sheila

Richard, how to use aggregate function in cassandra 1.2.3 OR which version is support to aggregate function? – Sheila

@Sheila : you can easily get the schema in a file using echo -e "describe keyspace <keyspace>;\n" | cqlsh > <keyspace>.shema – Enwrap

Answer 4

For someone who comes in future, just to get ddl for schema/keyspace with "myschema" in "CassandraHost" server.

echo -e "use myschema;\nDESCRIBE KEYSPACE;\n" | cqlsh  CassandraHost > mySchema.cdl

and you can use following to import just DDL (without data):

cqlsh  CassandraNEWhost -f mySchema.cdl

Showplace

This only works with a remote host if they're on a compatible version. – Adrenocorticotropic

Answer 5

With authentication

cqlsh -u <user-name> -e "DESC KEYSPACE user" > user_schema.cql

password will be promted.

Allantois

Answer 6

I am currently working with cassandra 1.1 and moving towards 1.2

For older Cassandra versions (<2.0), I found the below to be a handy way:

$ cqlsh -f testFile > user_schema_export.cql 

Usage:

cqlsh [options] [host [port]]

CQL Shell for Apache Cassandra

Options:

  -f FILE, --file=FILE  Execute commands from FILE, then exit

The testFile can contain the DESC command as needed for our keyspace.

 $ vagrant@vagrant-cass-VM:/cassandra/bin$ cat testFile 
   use testkeyspace;
   DESC KEYSPACE testkeyspace;

Bucksaw