This is a Java library for exporting cassandra database to SQL that can be used to restore an SQL database like MySQL and Postgres
This is a simple library for exporting cassandra database to SQL that can be used to restore an SQL database like MySQL and Postgres.
It handles the database columns based on their types i.e. TEXT columns are processed differently from TIMESTAMP type of columns.
Every collection columns, by default, are converted into JSON string
Installed JRE 8+
Verify your installation by running java -version
From the command line execute java -jar ./dist/cassandra2sql-1.0.jar application.properties
Where application.properties is the full path to the .properties config file
Cassandra2SQL uses application.properties file as a config source. It’s a simple KEY=VALUE entry config file with a .properties extension. So the file can have any name other than application just supply it to the app during execution
TABLE CONFIGURATION
In the config file we can specify the cassandra (source) table we want to export as SQL in the following format:
TABLES=srcTableName1:targetTableName1,srcTableName2:targetTableName2, . . .
The srcTableName is the name of the table to be exported in cassandra, while the targetTableName is the corresponding
table name on the SQL-database (MYSQL/POSTGRES). Multiple tables can be separated with a comma ,
For example, this config will instruct the tool to export the tables user as users, sentmessage as sent_messages and so on.
TABLES=user:users,sentmessage:sent_messages,logs:logs
The SQL insert statement generated will look like INSERT INTO users ..., INSERT INTO sent_messages ... and so on.
COLUMN CONFIGURATION
Next is the column configuration for each tables. The configuration is under the key TABLE_CONFIG_SRCTABLE. It’s a combination of the uppercase format of the srcTableName supplied in the TABLES section.
The value of each table config is the source column name and the target column name separated by the : symbol.
Thus, we can config columns for the users table defined above:
TABLE_CONFIG_USER=firstName:first_name,bvnverified:bvn_verified,emailverified:email_verified,id=:id
For us to configure columns for the logs table, we just need to add this entry to the application.properties file:
TABLE_CONFIG_LOGS=data:data,createdat=created_at,action=action_log
So if we defined 10 tables in the TABLES section, it means we’ll have 10 TABLE_CONFIG_XXX in the configuration file.
More Configuration Properties
#option to either delete the generated zip file or not, default is falseDELETE_GENERATED_ZIPFILE=false#option to keep the generated sql, default is false as this can lead large memory consumptionKEEP_GENERATED_SQL=false#the keyspace to connect toKEYSPACE=claneapp#the target SQL database. Available options are: POSTGRES or MYSQLSQL_FLAVOUR=POSTGRES#the datetime format to use when exporting timestamp columnsDATETIME_FORMAT=yyyy-MM-dd HH:mm:ss#the date format to use when exporting date comlumnsDATE_FORMAT=yyyy-MM-dd#database connection configs. If not supplied, 127.0.0.1 is used as the host and 9042 as the port#with empty username and passwordDB_HOST=127.0.0.1DB_PORT=9042DB_USERNAME=DB_PASSWORD=#minimum email properties required for sending generated zip file to an email address as attachment#only required if you want generated zip file to be sent to your emailEMAIL_HOST=smtp.mailtrap.ioEMAIL_PORT=25EMAIL_USERNAME=your-passwordEMAIL_PASSWORD=your-usernameEMAIL_FROM=seun@example.comEMAIL_TO=smatt@example.com
Execute mvn clean package and the binary file will be available in target/cassandra2sql-1.0.jar.
To skip tests execute mvn clean package -DskipTests=true
MIT
Found a bug? please create an issue for it with as much details as possible.
Wanna improve the codebase? PRs are welcome