40

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

sazzad
  • 5,740
  • 6
  • 25
  • 42
vpggopal
  • 437
  • 1
  • 4
  • 3

5 Answers5

69

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'
Raman Yelianevich
  • 1,117
  • 11
  • 10
  • 1
    Thanks, works perfect! (I had to include IP/hostname after cqlsh because our instance uses an rpc_address in the config). – Jess Feb 12 '16 at 16:01
  • 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` – ZAJ Jul 30 '17 at 05:16
14

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
rouble
  • 16,364
  • 16
  • 107
  • 102
12

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'.

Richard
  • 11,050
  • 2
  • 46
  • 33
  • 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. – vpggopal May 09 '13 at 06:48
  • @vpggopal I think you might have to pay him for that. – Lyuben Todorov May 09 '13 at 08:22
  • 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. – Richard May 09 '13 at 08:25
  • Thank u Richard, How to export schema in cqlsh using command?. – vpggopal May 09 '13 at 10:22
  • It's written in my answer. Use 'describe schema'. – Richard May 09 '13 at 10:24
  • 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?. – vpggopal May 10 '13 at 05:00
  • It's the text output. Put it in a file and import with the 'cqlsh -f' command. – Richard May 10 '13 at 06:42
  • Thank you Richard, If possible to write the 'describe keyspace' command output text on any file format?. – vpggopal May 10 '13 at 07:03
  • Richard, how to use aggregate function in cassandra 1.2.3 OR which version is support to aggregate function? – vpggopal May 13 '13 at 10:29
  • 2
    @vpggopal : you can easily get the schema in a file using `echo -e "describe keyspace ;\n" | cqlsh > .shema` – Pierre Rust Jan 23 '14 at 09:48
12

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
dillip
  • 1,782
  • 17
  • 16
0

With authentication

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

password will be promted.

Shakeel
  • 1,869
  • 15
  • 23