Adding a column to a table with compact storage

[Jacob Edelstein <ja...@curalate.com>]

Hi all,
We made a decision to use compact storage for a couple of very large tables to get maximum storage efficiency. We understood that this would limit us to a single non-primary key column. We did not realize at the time that we would not be able to add this column using the cql3 alter command. This is well documented, but we just missed it. I have a few questions: 

1. Is there any way to add this column without using the cql3 alter command? We want to add a boolean column to the table below. The new column will not be part of the primary key. 

create table no_extra_column (
  a varchar,
  b timestamp,
  c varchar,
  PRIMARY KEY (a, b, c)
) WITH COMPACT STORAGE and compaction={'class':'LeveledCompactionStrategy'};

2. If it's simply not possible, we wish to create new tables that don't use compact storage and copy the existing data over. What's the easiest way to do this? 

Thanks,
Jacob

Re: Adding a column to a table with compact storage

[Jacob Edelstein <ja...@curalate.com>]

Hi Sylvain,
That is correct - we currently have only primary-key columns, and wish to add a single non-primary key column. Based on the data stored in the table, it is highly unlikely we will need to add more columns in the future, but we can always migrate the data then if we need to. 

We have a test environment and current back-ups, so a little black magic isn't too scary. We are currently running 1.2.6. 

Thanks,
Jacob

On Nov 28, 2013, at 1:16 AM, Sylvain Lebresne <sy...@datastax.com> wrote:

> On Wed, Nov 27, 2013 at 7:06 PM, Jacob Edelstein <ja...@curalate.com> wrote:
> Hi all,
> We made a decision to use compact storage for a couple of very large tables to get maximum storage efficiency. We understood that this would limit us to a single non-primary key column. We did not realize at the time that we would not be able to add this column using the cql3 alter command. This is well documented, but we just missed it. I have a few questions:
> 
> 1. Is there any way to add this column without using the cql3 alter command? We want to add a boolean column to the table below. The new column will not be part of the primary key.
> 
> create table no_extra_column (
>   a varchar,
>   b timestamp,
>   c varchar,
>   PRIMARY KEY (a, b, c)
> ) WITH COMPACT STORAGE and compaction={'class':'LeveledCompactionStrategy'};
> 
> So you mean that you currently have no PK columns at all and want to add one? If so, it's "possible" because COMPACT STORAGE can support one non-PK column and you have none, but it will involve messing with the underlying schema manually and is not "supported" per se (it's black magic territory). Might be worth asking yourself the question "might I need more than one column later on"? If you suspect you may, migrating to a non compact table could be worth the trouble. Just saying.
> 
> Anyway, if you feel like going through the route of manually modifying your schema, I can tell you what you can try but:
> 1) I'd need to know which version of C* you use (at least whether it's 1.2 or 2.0 but the exact version can't hurt).
> 2) I make no guarantee your datacenters won't be reduced to dust following those manipulations. Messing with the black arts has great powers but is dangerous. 
> 
> --
> Sylvain

Re: Adding a column to a table with compact storage

[Sylvain Lebresne <sy...@datastax.com>]

On Wed, Nov 27, 2013 at 7:06 PM, Jacob Edelstein <ja...@curalate.com> wrote:

> Hi all,
> We made a decision to use compact storage for a couple of very large
> tables to get maximum storage efficiency. We understood that this would
> limit us to a single non-primary key column. We did not realize at the time
> that we would not be able to add this column using the cql3 alter command.
> This is well documented, but we just missed it. I have a few questions:
>
> 1. Is there any way to add this column without using the cql3 alter
> command? We want to add a boolean column to the table below. The new column
> will not be part of the primary key.
>
> create table no_extra_column (
>   a varchar,
>   b timestamp,
>   c varchar,
>   PRIMARY KEY (a, b, c)
> ) WITH COMPACT STORAGE and
> compaction={'class':'LeveledCompactionStrategy'};
>

So you mean that you currently have no PK columns at all and want to add
one? If so, it's "possible" because COMPACT STORAGE can support one non-PK
column and you have none, but it will involve messing with the underlying
schema manually and is not "supported" per se (it's black magic territory).
Might be worth asking yourself the question "might I need more than one
column later on"? If you suspect you may, migrating to a non compact table
could be worth the trouble. Just saying.

Anyway, if you feel like going through the route of manually modifying your
schema, I can tell you what you can try but:
1) I'd need to know which version of C* you use (at least whether it's 1.2
or 2.0 but the exact version can't hurt).
2) I make no guarantee your datacenters won't be reduced to dust following
those manipulations. Messing with the black arts has great powers but is
dangerous.

--
Sylvain