cratedb

CrateDB, How to drop all tables prior to restore?

CrateDB needs tables to be dropped before a restore.

Options unavailable:

  1. @SQL

  2. Multiple statements copied into console/crash cli

Is there an easy way to do this?

Solution

  • The way I resolved this was via a bash script using the Crash CLI which pulls the tables and drops them individually.

    You will need to set $HOST and $TABLE_CATALOG

    crash --hosts $HOST -c "SELECT CONCAT('\"', TABLE_CATALOG, '\".\"', TABLE_NAME, '\"') FROM INFORMATION_SCHEMA.tables WHERE  table_catalog = $TABLE_CATALOG --format="csv" | 
tail -n +2 | head -n -1 |  sed 's/"/\\"/g' | 
xargs -I {} crash --hosts $HOST -c 'DROP TABLE {}'