Is it possible to rename columns?

Question

Is it possible to issue something like

RENAME COLUMN col1 col2

in Google Cloud Spanner? It looks from the DDL that this isn't possible; if not, is this a design choice or a limitation whilst in Beta?

Womanlike

Answer 1

No, this is not possible. Currently you can only do the following with regard to altering columns in a table:

• Add a new one
• Delete an existing one, unless it's a key column
• Change delete behavior (cascading or not)
• Convert between STRING and BYTES
• Change length of STRING and BYTES
• Add or remove NOT NULL modifier

A work around is possible by following these steps in order:

• Add the new column to your table
• Update your code to read to from both columns
• Update your code to only write to the new one
• Run a Cloud Dataflow job to migrate the data from the old column to the new column
• Update your code to only read from the new column
• Drop the old column

Keep in mind the above steps will not work for the primary key column, you'll have to do by creating a new table, and doing the data migration that way.

Genip

Dan - thanks for the hints. How would propose going about running a Cloud Dataflow job that performs the migration? Is there anything built in to allow it to get/stream data from Cloud Spanner? – Womanlike

Unless you already have a large dataset, just writing any old process to query read and write everything in a single thread would be sufficient to get the job done. – Genip