如何在Cassandra社区版的CQLSH中生成克隆现有表的查询语句

Hey there! Since DevCenter's no longer a working option for you on macOS High Sierra, let's break down the straightforward ways to generate the CQL needed to clone an existing table directly in cqlsh.

I'll cover two reliable methods, depending on whether you just need the schema or want to copy data along with it.

1. Get the Exact Schema with DESCRIBE TABLE #

This is the most thorough approach—it gives you the full CREATE TABLE statement, including partition keys, clustering columns, secondary indexes, and table properties like compaction settings. Here's how to use it:

1. Fire up cqlsh and connect to your Cassandra cluster.
2. Switch to the keyspace containing your target table:

   USE your_target_keyspace;
   

3. Run the describe command for your existing table:

   DESCRIBE TABLE your_existing_table;
   

4. Copy the outputted CREATE TABLE statement, change the table name to your new table's name, and execute the modified statement in cqlsh.

Pro tip: If your table has secondary indexes, DESCRIBE TABLE will include the corresponding CREATE INDEX statements too. Just adjust the index names to match your new table if you want those indexes replicated.

2. Clone Schema + Data in One Go with CREATE TABLE ... AS SELECT #

If you want to copy both the schema and all existing data to a new table, this one-liner works great for smaller datasets:

CREATE TABLE your_new_table AS SELECT * FROM your_existing_table;

Important Caveats for This Method: #

• It won't copy secondary indexes, materialized views, or custom table properties (like TTL defaults or compaction strategies). You'll need to add those manually using the output from DESCRIBE TABLE.
• For large tables, this can be resource-heavy—stick to the first method for schema cloning, then use tools like COPY or bulk loaders to move data if needed.

Bonus: Save Schema to a File #

If you want to save the schema to a file for later editing, run cqlsh in non-interactive mode with the -e flag:

cqlsh -u your_username -p your_password -k your_target_keyspace -e "DESCRIBE TABLE your_existing_table;" > table_clone_schema.cql

This writes the full create statement to table_clone_schema.cql, which you can tweak and run whenever you need.

内容的提问来源于stack exchange，提问作者Knows Not Much