A data exporting tool for Cassandra inspired from mysqldump, with some additional slice and dice capabilities.
Disclaimer: most of the times, you really shouldn't be using this. It's fragile, non-scalable, inefficient and verbose. Cassandra already offers excellent exporting/importing tools:
- Snapshots
- CQL's COPY FROM/TO
- sstable2json
However, especially during development, I frequently need to:
- Quickly take a snapshot of an entire keyspace, and import it just as quickly without copying too many files around or losing too much time
- Ability to take a very small subset of a massive production database (according to some CQL-like filtering) and import it quickly on my development environment
If these use cases sound familiar, this tool might be useful for you.
It's still missing many major Cassandra features that I don't use daily, so feel free to open an issue pointing them out (or send a pull request) if you need something.
The help should already contain some useful information:
usage: cassandradump.py [-h] [--cf CF] [--export-file EXPORT_FILE]
[--filter FILTER] [--host HOST] [--port PORT]
[--import-file IMPORT_FILE] [--keyspace KEYSPACE]
[--exclude-cf EXCLUDE_CF] [--no-create] [--no-insert]
[--password PASSWORD]
[--protocol-version PROTOCOL_VERSION] [--quiet]
[--sync] [--username USERNAME] [--ssl]
[--certfile CERTFILE]
A data exporting tool for Cassandra inspired from mysqldump, with some added
slice and dice capabilities.
optional arguments:
-h, --help show this help message and exit
--cf CF export a column family. The name must include the
keyspace, e.g. "system.schema_columns". Can be
specified multiple times
--export-file EXPORT_FILE
export data to the specified file
--filter FILTER export a slice of a column family according to a CQL
filter. This takes essentially a typical SELECT query
stripped of the initial "SELECT ... FROM" part (e.g.
"system.schema_columns where keyspace_name
='OpsCenter'", and exports only that data. Can be
specified multiple times
--host HOST the address of a Cassandra node in the cluster
(localhost if omitted)
--port PORT the port of a Cassandra node in the cluster
(9042 if omitted)
--import-file IMPORT_FILE
import data from the specified file
--keyspace KEYSPACE export a keyspace along with all its column families.
Can be specified multiple times
--exclude-cf EXCLUDE_CF
when using --keyspace, specify column family to
exclude. Can be specified multiple times
--no-create don't generate create (and drop) statements
--no-insert don't generate insert statements
--password PASSWORD set password for authentication (only if
protocol-version is set)
--protocol-version PROTOCOL_VERSION
set auth_provider version (required for
authentication)
--quiet quiet progress logging
--sync import data in synchronous mode (default asynchronous)
--username USERNAME set username for auth (only if protocol-version is
set)
--ssl enable ssl connection to Cassandra cluster. Must also
set --certfile.
--certfile CERTFILE ca cert file for SSL. Assumes --ssl.
In its simplest invocation, it exports data and schemas for all keyspaces:
$ python cassandradump.py --export-file dump.cql Exporting all keyspaces Exporting schema for keyspace OpsCenter Exporting schema for column family OpsCenter.events_timeline Exporting data for column family OpsCenter.events_timeline Exporting schema for column family OpsCenter.settings Exporting data for column family OpsCenter.settings Exporting schema for column family OpsCenter.rollups60 Exporting data for column family OpsCenter.rollups60 ...
$ cat dump.cql
DROP KEYSPACE IF EXISTS "OpsCenter";
CREATE KEYSPACE "OpsCenter" WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'} AND durable_writes = true;
DROP TABLE IF EXISTS "OpsCenter"."events_timeline";
CREATE TABLE "OpsCenter".events_timeline (key text, column1 bigint, value blob, PRIMARY KEY (key, column1)) WITH COMPACT STORAGE AND CLUSTERING ORDER BY (column1 ASC) AND caching = '{"keys":"ALL", "rows_per_partition":"NONE"}' AND comment = '{"info": "OpsCenter management data.", "version": [5, 1, 0]}' AND compaction = {'min_threshold': '4', 'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '8'} AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'} AND dclocal_read_repair_chance = 0.0 AND default_time_to_live = 0 AND gc_grace_seconds = 864000 AND max_index_interval = 2048 AND memtable_flush_period_in_ms = 0 AND min_index_interval = 128 AND read_repair_chance = 0.25 AND speculative_retry = 'NONE';
INSERT INTO "OpsCenter"."events_timeline" (key, column1, value) VALUES ('201501', 1419841027332869, 0x)
INSERT INTO "OpsCenter"."events_timeline" (key, column1, value) VALUES ('201501', 1419841027352525, 0x)
INSERT INTO "OpsCenter"."events_timeline" (key, column1, value) VALUES ('201501', 1419928979070954, 0x)
...
The created dump file can be directly used with cqlsh -f, or there's
also a --import-file that uses asynchronous import so it goes
definitely fast.
Using --keyspace, it's possible to filter for a specific set of
keyspaces
$ python cassandradump.py --keyspace system --export-file dump.cql Exporting schema for keyspace system Exporting schema for column family system.peers Exporting data for column family system.peers Exporting schema for column family system.range_xfers Exporting data for column family system.range_xfers Exporting schema for column family system.schema_columns Exporting data for column family system.schema_columns ...
$ cat dump.cql
DROP KEYSPACE IF EXISTS "system";
CREATE KEYSPACE system WITH replication = {'class': 'LocalStrategy'} AND durable_writes = true;
DROP TABLE IF EXISTS "system"."peers";
CREATE TABLE system.peers (peer inet PRIMARY KEY, data_center text, host_id uuid, preferred_ip inet, rack text, release_version text, rpc_address inet, schema_version uuid, tokens set<text>) WITH bloom_filter_fp_chance = 0.01 AND caching = '{"keys":"ALL", "rows_per_partition":"NONE"}' AND comment = 'known peers in the cluster' AND compaction = {'min_threshold': '4', 'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32'} AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'} AND dclocal_read_repair_chance = 0.0 AND default_time_to_live = 0 AND gc_grace_seconds = 0 AND max_index_interval = 2048 AND memtable_flush_period_in_ms = 3600000 AND min_index_interval = 128 AND read_repair_chance = 0.0 AND speculative_retry = '99.0PERCENTILE';
...
Using --cf, it's possible to filter for a specific set of column
families:
$ python cassandradump.py --cf OpsCenter.rollups7200 --no-create --export-file dump.cql Exporting data for column family OpsCenter.rollups7200
$ cat dump.cql
INSERT INTO "OpsCenter"."rollups7200" (key, column1, value) VALUES ('127.0.0.1-foo', 718946047, 0x000000000000000000000000)
INSERT INTO "OpsCenter"."rollups7200" (key, column1, value) VALUES ('127.0.0.1-foo', 718953247, 0x000000000000000000000000)
INSERT INTO "OpsCenter"."rollups7200" (key, column1, value) VALUES ('127.0.0.1-foo', 718960447, 0x000000000000000000000000)
INSERT INTO "OpsCenter"."rollups7200" (key, column1, value) VALUES ('127.0.0.1-foo', 718967647, 0x000000000000000000000000)
INSERT INTO "OpsCenter"."rollups7200" (key, column1, value) VALUES ('127.0.0.1-foo', 719032447, 0x40073fc200000000437bc000)
...
Using --no-insert and --no-create it's possible to tweak what
CQL statements are actually included in the dump.
Most of the times, the column families in a production scenario are
huge, and you might just want a little slice of it. With --filter,
it's possible to specify a set of CQL filters, and just the data that
satisfies those filters will be included in the dump:
$ python cassandradump.py --filter "system.schema_columns WHERE keyspace_name='OpsCenter'" --export-file dump.cql Exporting data for filter "system.schema_columns where keyspace_name ='OpsCenter'"
$ cat dump.cql
INSERT INTO "system"."schema_columns" (keyspace_name, columnfamily_name, column_name, component_index, index_name, index_options, index_type, type, validator) VALUES ('OpsCenter', 'backup_reports', 'backup_id', 1, NULL, 'null', NULL, 'clustering_key', 'org.apache.cassandra.db.marshal.UTF8Type')
INSERT INTO "system"."schema_columns" (keyspace_name, columnfamily_name, column_name, component_index, index_name, index_options, index_type, type, validator) VALUES ('OpsCenter', 'backup_reports', 'deleted_at', 4, NULL, 'null', NULL, 'regular', 'org.apache.cassandra.db.marshal.TimestampType')