Last we had to remove all the files in /var/lib/cassandra/ simply because somewhere it break when we upgraded from cassandra 2.0.0 to 2.0.2 and everybody in the teams just do not have the time to goes into details. So since this is just4fun cluster, we agreed to removed the dir /var/lib/cassandra/ and start the cluster using cassandra 2.0.2.
In order to better understand cassandra, we take a detail look at alter table. But before that, let's create a new keyspace and table.
cqlsh> CREATE KEYSPACE jw_schema1 WITH replication = {'class':'SimpleStrategy', 'replication_factor':3};
cqlsh>and the correspondance cassandra system.log
INFO [Thrift:7] 2013-12-06 16:17:21,902 MigrationManager.java (line 217) Create new Keyspace: jw_schema1, rep strategy:SimpleStrategy{}, strategy_options: {replication_factor=3}, durable_writes: true
INFO [MigrationStage:1] 2013-12-06 16:17:21,987 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-schema_keyspaces@19766800(138/1380 serialized/live bytes, 3 ops)
INFO [FlushWriter:2] 2013-12-06 16:17:21,988 Memtable.java (line 328) Writing Memtable-schema_keyspaces@19766800(138/1380 serialized/live bytes, 3 ops)
INFO [FlushWriter:2] 2013-12-06 16:17:22,146 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/schema_keyspaces/system-schema_keyspaces-jb-3-Data.db (163 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=189892)
okay now, we create table in the jw_schema1 keyspace;
cqlsh> use jw_schema1;
cqlsh:jw_schema1> CREATE TABLE users (user_id text, age int, firest text, last text, PRIMARY KEY (user_id)) WITH comment = 'storing user data';
cqlsh:jw_schema1>
INFO [Thrift:7] 2013-12-06 16:19:53,170 MigrationManager.java (line 231) Create new ColumnFamily: org.apache.cassandra.config.CFMetaData@56d88b[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=storing user data,readRepairChance=0.1,dclocalReadRepairChance=0.0,replicateOnWrite=true,gcGraceSeconds=864000,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=7 cap=7]=ColumnDefinition{name=757365725f6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}},compactionStrategyClass=class org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.LZ4Compressor},bloomFilterFpChance=0.01,memtable_flush_period_in_ms=0,caching=KEYS_ONLY,defaultTimeToLive=0,speculative_retry=99.0PERCENTILE,indexInterval=128,populateIoCacheOnFlush=false,droppedColumns={},triggers={}]
INFO [MigrationStage:1] 2013-12-06 16:19:53,189 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-schema_columnfamilies@27441128(1313/13130 serialized/live bytes, 25 ops)
INFO [FlushWriter:3] 2013-12-06 16:19:53,190 Memtable.java (line 328) Writing Memtable-schema_columnfamilies@27441128(1313/13130 serialized/live bytes, 25 ops)
INFO [FlushWriter:3] 2013-12-06 16:19:53,367 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-3-Data.db (816 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=193345)
INFO [MigrationStage:1] 2013-12-06 16:19:53,371 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-schema_columns@11068035(1259/12590 serialized/live bytes, 28 ops)
INFO [FlushWriter:3] 2013-12-06 16:19:53,371 Memtable.java (line 328) Writing Memtable-schema_columns@11068035(1259/12590 serialized/live bytes, 28 ops)
INFO [FlushWriter:3] 2013-12-06 16:19:53,517 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/schema_columns/system-schema_columns-jb-3-Data.db (460 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=193345)
INFO [MigrationStage:1] 2013-12-06 16:19:53,547 DefsTables.java (line 341) Loading org.apache.cassandra.config.CFMetaData@1306e0a[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=storing user data,readRepairChance=0.1,dclocalReadRepairChance=0.0,replicateOnWrite=true,gcGraceSeconds=864000,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=7 cap=7]=ColumnDefinition{name=757365725f6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}},compactionStrategyClass=class org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.LZ4Compressor},bloomFilterFpChance=0.01,memtable_flush_period_in_ms=0,caching=KEYS_ONLY,defaultTimeToLive=0,speculative_retry=99.0PERCENTILE,indexInterval=128,populateIoCacheOnFlush=false,droppedColumns={},triggers={}]
INFO [MigrationStage:1] 2013-12-06 16:19:53,554 ColumnFamilyStore.java (line 251) Initializing jw_schema1.usersTo confirm if the table really created, we do a few tests just to be sure.
cqlsh:jw_schema1> desc tables;
users
cqlsh:jw_schema1> select * from users;
(0 rows)
cqlsh:jw_schema1> desc table users;
CREATE TABLE users (
user_id text,
age int,
firest text,
last text,
PRIMARY KEY (user_id)
) WITH
bloom_filter_fp_chance=0.010000 AND
caching='KEYS_ONLY' AND
comment='storing user data' AND
dclocal_read_repair_chance=0.000000 AND
gc_grace_seconds=864000 AND
index_interval=128 AND
read_repair_chance=0.100000 AND
replicate_on_write='true' AND
populate_io_cache_on_flush='false' AND
default_time_to_live=0 AND
speculative_retry='99.0PERCENTILE' AND
memtable_flush_period_in_ms=0 AND
compaction={'class': 'SizeTieredCompactionStrategy'} AND
compression={'sstable_compression': 'LZ4Compressor'};
cqlsh:jw_schema1>
So everything looks good now. We added two index for users table just so we can query with criterias later.
CREATE INDEX idxAge ON users (age);
CREATE INDEX idxLast ON users (last);
Alright, noticed that the field in the table users is wrong? Let's correct it.
cqlsh:jw_schema1> ALTER TABLE users RENAME firest TO first AND last TO laste;
Bad Request: Cannot rename non PRIMARY KEY part last
cqlsh:jw_schema1> ALTER TABLE users RENAME 'firest' TO 'first' AND 'last' TO 'laste';
Bad Request: line 1:59 no viable alternative at input 'laste'
cqlsh:jw_schema1> ALTER TABLE users RENAME "firest" TO "first" AND "last" TO "laste";
Bad Request: Cannot rename non PRIMARY KEY part last
cqlsh:jw_schema1>
cqlsh:jw_schema1>
cqlsh:jw_schema1> ALTER TABLE users RENAME firset TO first;
Bad Request: Cannot rename unknown column firset in keyspace users
cqlsh:jw_schema1> ALTER TABLE users RENAME 'firset' TO 'first';
Bad Request: line 1:37 no viable alternative at input 'first'
cqlsh:jw_schema1> ALTER TABLE users RENAME "firset" TO "first";
Bad Request: Cannot rename unknown column firset in keyspace users
cqlsh:jw_schema1> ALTER TABLE users RENAME user_id TO id;
cqlsh:jw_schema1>
INFO [Thrift:7] 2013-12-06 17:50:25,410 MigrationManager.java (line 257) Update ColumnFamily 'jw_schema1/users' From org.apache.cassandra.config.CFMetaData@1306e0a[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=storing user data,readRepairChance=0.1,dclocalReadRepairChance=0.0,replicateOnWrite=true,gcGraceSeconds=864000,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=idxLast, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=6d6964646c65, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=idxAge, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=7 cap=7]=ColumnDefinition{name=757365725f6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}},compactionStrategyClass=class org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.LZ4Compressor},bloomFilterFpChance=0.01,memtable_flush_period_in_ms=0,caching=KEYS_ONLY,defaultTimeToLive=0,speculative_retry=99.0PERCENTILE,indexInterval=128,populateIoCacheOnFlush=false,droppedColumns={},triggers={}] To org.apache.cassandra.config.CFMetaData@345501[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=storing user data,readRepairChance=0.1,dclocalReadRepairChance=0.0,replicateOnWrite=true,gcGraceSeconds=864000,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=idxLast, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=2 cap=2]=ColumnDefinition{name=6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=6d6964646c65, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=idxAge, indexType=COMPOSITES}},compactionStrategyClass=class org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.LZ4Compressor},bloomFilterFpChance=0.01,memtable_flush_period_in_ms=0,caching=KEYS_ONLY,defaultTimeToLive=0,speculative_retry=99.0PERCENTILE,indexInterval=128,populateIoCacheOnFlush=false,droppedColumns={},triggers={}]
INFO [MigrationStage:1] 2013-12-06 17:50:25,459 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-schema_columnfamilies@28891305(1308/13080 serialized/live bytes, 25 ops)
INFO [FlushWriter:7] 2013-12-06 17:50:25,472 Memtable.java (line 328) Writing Memtable-schema_columnfamilies@28891305(1308/13080 serialized/live bytes, 25 ops)
INFO [FlushWriter:7] 2013-12-06 17:50:25,589 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-10-Data.db (812 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=211852)
INFO [MigrationStage:1] 2013-12-06 17:50:25,599 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-schema_columns@26050002(298/2980 serialized/live bytes, 7 ops)
INFO [FlushWriter:7] 2013-12-06 17:50:25,600 Memtable.java (line 328) Writing Memtable-schema_columns@26050002(298/2980 serialized/live bytes, 7 ops)
INFO [FlushWriter:7] 2013-12-06 17:50:25,732 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/schema_columns/system-schema_columns-jb-10-Data.db (250 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=211852)what the... ? why is table altering rename do not work on other columns but just primary key?! Nevermind of that, let's add column then.
cqlsh:jw_schema1> ALTER TABLE users ADD middle int;
cqlsh:jw_schema1>
INFO [Thrift:7] 2013-12-06 17:47:06,884 MigrationManager.java (line 257) Update ColumnFamily 'jw_schema1/users' From org.apache.cassandra.config.CFMetaData@1306e0a[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=storing user data,readRepairChance=0.1,dclocalReadRepairChance=0.0,replicateOnWrite=true,gcGraceSeconds=864000,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=idxLast, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=idxAge, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=7 cap=7]=ColumnDefinition{name=757365725f6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}},compactionStrategyClass=class org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.LZ4Compressor},bloomFilterFpChance=0.01,memtable_flush_period_in_ms=0,caching=KEYS_ONLY,defaultTimeToLive=0,speculative_retry=99.0PERCENTILE,indexInterval=128,populateIoCacheOnFlush=false,droppedColumns={},triggers={}] To org.apache.cassandra.config.CFMetaData@119723e[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=storing user data,readRepairChance=0.1,dclocalReadRepairChance=0.0,replicateOnWrite=true,gcGraceSeconds=864000,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=idxLast, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=6d6964646c65, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=7 cap=7]=ColumnDefinition{name=757365725f6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=idxAge, indexType=COMPOSITES}},compactionStrategyClass=class org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.LZ4Compressor},bloomFilterFpChance=0.01,memtable_flush_period_in_ms=0,caching=KEYS_ONLY,defaultTimeToLive=0,speculative_retry=99.0PERCENTILE,indexInterval=128,populateIoCacheOnFlush=false,droppedColumns={},triggers={}]
INFO [MigrationStage:1] 2013-12-06 17:47:06,945 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-schema_columnfamilies@10947620(1313/13130 serialized/live bytes, 25 ops)
INFO [FlushWriter:6] 2013-12-06 17:47:06,946 Memtable.java (line 328) Writing Memtable-schema_columnfamilies@10947620(1313/13130 serialized/live bytes, 25 ops)
INFO [FlushWriter:6] 2013-12-06 17:47:07,124 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-8-Data.db (816 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=208140)
INFO [CompactionExecutor:19] 2013-12-06 17:47:07,129 CompactionTask.java (line 115) Compacting [SSTableReader(path='/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-6-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-8-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-5-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-7-Data.db')]
INFO [MigrationStage:1] 2013-12-06 17:47:07,130 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-schema_columns@1654082(321/3210 serialized/live bytes, 7 ops)
INFO [FlushWriter:6] 2013-12-06 17:47:07,133 Memtable.java (line 328) Writing Memtable-schema_columns@1654082(321/3210 serialized/live bytes, 7 ops)
INFO [FlushWriter:6] 2013-12-06 17:47:07,293 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/schema_columns/system-schema_columns-jb-8-Data.db (226 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=208140)
INFO [CompactionExecutor:20] 2013-12-06 17:47:07,298 CompactionTask.java (line 115) Compacting [SSTableReader(path='/var/lib/cassandra/data/system/schema_columns/system-schema_columns-jb-6-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/schema_columns/system-schema_columns-jb-7-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/schema_columns/system-schema_columns-jb-8-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/schema_columns/system-schema_columns-jb-5-Data.db')]
INFO [CompactionExecutor:19] 2013-12-06 17:47:07,329 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-compaction_history@26162716(404/4040 serialized/live bytes, 10 ops)
INFO [FlushWriter:6] 2013-12-06 17:47:07,332 Memtable.java (line 328) Writing Memtable-compaction_history@26162716(404/4040 serialized/live bytes, 10 ops)
INFO [FlushWriter:6] 2013-12-06 17:47:07,512 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/compaction_history/system-compaction_history-jb-4-Data.db (270 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=208737)
INFO [CompactionExecutor:19] 2013-12-06 17:47:07,513 CompactionTask.java (line 272) Compacted 4 sstables to [/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-9,]. 8,498 bytes to 6,426 (~75% of original) in 195ms = 0.031427MB/s. 6 total rows, 3 unique. Row merge counts were {1:2, 2:0, 3:0, 4:1, }
INFO [CompactionExecutor:20] 2013-12-06 17:47:07,528 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-compaction_history@14411848(397/3970 serialized/live bytes, 10 ops)
INFO [FlushWriter:6] 2013-12-06 17:47:07,528 Memtable.java (line 328) Writing Memtable-compaction_history@14411848(397/3970 serialized/live bytes, 10 ops)
INFO [FlushWriter:6] 2013-12-06 17:47:07,664 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/compaction_history/system-compaction_history-jb-5-Data.db (268 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=209461)
INFO [CompactionExecutor:19] 2013-12-06 17:47:07,666 CompactionTask.java (line 115) Compacting [SSTableReader(path='/var/lib/cassandra/data/system/compaction_history/system-compaction_history-jb-2-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/compaction_history/system-compaction_history-jb-4-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/compaction_history/system-compaction_history-jb-3-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/compaction_history/system-compaction_history-jb-1-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/compaction_history/system-compaction_history-jb-5-Data.db')]
INFO [CompactionExecutor:20] 2013-12-06 17:47:07,672 CompactionTask.java (line 272) Compacted 4 sstables to [/var/lib/cassandra/data/system/schema_columns/system-schema_columns-jb-9,]. 7,892 bytes to 7,481 (~94% of original) in 214ms = 0.033338MB/s. 6 total rows, 3 unique. Row merge counts were {1:2, 2:0, 3:0, 4:1, }
INFO [CompactionExecutor:19] 2013-12-06 17:47:07,853 CompactionTask.java (line 272) Compacted 5 sstables to [/var/lib/cassandra/data/system/compaction_history/system-compaction_history-jb-6,]. 1,343 bytes to 749 (~55% of original) in 182ms = 0.003925MB/s. 5 total rows, 5 unique. Row merge counts were {1:5, 2:0, 3:0, 4:0, 5:0, }So adding column is fine. Let's now change column type.
cqlsh:jw_schema1> ALTER TABLE users ALTER middle TYPE text;
Bad Request: Cannot change middle from type int to type text: types are incompatible.
So this make sense, imagine if you have populate table users and you realized you need to change type. Remember this alteration is not always possible as it depend on the type change from and type change to. It's better you think through and make sure what kind of data you want to store early.
cqlsh:jw_schema1> ALTER TABLE users ALTER middle TYPE text;
cqlsh:jw_schema1> ALTER TABLE users ADD middle text;
cqlsh:jw_schema1>
So I end up dropping old field and added back a new field.
cqlsh:jw_schema1> ALTER TABLE users ADD removeMe bigint;
cqlsh:jw_schema1> ALTER TABLE users ADD removeMeMe varchar;
cqlsh:jw_schema1> ALTER TABLE users DROP removeMe;
cqlsh:jw_schema1>
so addition and dropping normal columns are as easy as peanut. Below are column drop logging.
INFO [Thrift:7] 2013-12-06 18:02:02,309 MigrationManager.java (line 257) Update ColumnFamily 'jw_schema1/users' From org.apache.cassandra.config.CFMetaData@1306e0a[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=storing user data,readRepairChance=0.1,dclocalReadRepairChance=0.0,replicateOnWrite=true,gcGraceSeconds=864000,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=idxLast, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=10 cap=10]=ColumnDefinition{name=72656d6f76656d656d65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=8 cap=8]=ColumnDefinition{name=72656d6f76656d65, validator=org.apache.cassandra.db.marshal.LongType, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=6d6964646c65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=7 cap=7]=ColumnDefinition{name=757365725f6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=idxAge, indexType=COMPOSITES}},compactionStrategyClass=class org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.LZ4Compressor},bloomFilterFpChance=0.01,memtable_flush_period_in_ms=0,caching=KEYS_ONLY,defaultTimeToLive=0,speculative_retry=99.0PERCENTILE,indexInterval=128,populateIoCacheOnFlush=false,droppedColumns={java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=1386323878805000},triggers={}] To org.apache.cassandra.config.CFMetaData@fd34f3[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=storing user data,readRepairChance=0.1,dclocalReadRepairChance=0.0,replicateOnWrite=true,gcGraceSeconds=864000,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=idxLast, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=10 cap=10]=ColumnDefinition{name=72656d6f76656d656d65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=6d6964646c65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=idxAge, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=7 cap=7]=ColumnDefinition{name=757365725f6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}},compactionStrategyClass=class org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.LZ4Compressor},bloomFilterFpChance=0.01,memtable_flush_period_in_ms=0,caching=KEYS_ONLY,defaultTimeToLive=0,speculative_retry=99.0PERCENTILE,indexInterval=128,populateIoCacheOnFlush=false,droppedColumns={java.nio.HeapByteBuffer[pos=0 lim=8 cap=8]=1386324122307000, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=1386323878805000},triggers={}]
INFO [MigrationStage:1] 2013-12-06 18:02:02,322 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-schema_columnfamilies@27218308(1417/14170 serialized/live bytes, 27 ops)
INFO [FlushWriter:11] 2013-12-06 18:02:02,326 Memtable.java (line 328) Writing Memtable-schema_columnfamilies@27218308(1417/14170 serialized/live bytes, 27 ops)
INFO [FlushWriter:11] 2013-12-06 18:02:02,495 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-18-Data.db (876 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=228374)
INFO [MigrationStage:1] 2013-12-06 18:02:02,500 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-schema_columns@10324109(0/0 serialized/live bytes, 1 ops)
INFO [FlushWriter:11] 2013-12-06 18:02:02,501 Memtable.java (line 328) Writing Memtable-schema_columns@10324109(0/0 serialized/live bytes, 1 ops)
INFO [FlushWriter:11] 2013-12-06 18:02:02,689 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/schema_columns/system-schema_columns-jb-18-Data.db (73 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=228374)So let's change the table metadata
cqlsh:jw_schema1> ALTER TABLE users WITH comment = 'revert me later';
cqlsh:jw_schema1>
everything seem fine.
INFO [Thrift:7] 2013-12-06 18:03:40,264 MigrationManager.java (line 257) Update ColumnFamily 'jw_schema1/users' From org.apache.cassandra.config.CFMetaData@1306e0a[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=storing user data,readRepairChance=0.1,dclocalReadRepairChance=0.0,replicateOnWrite=true,gcGraceSeconds=864000,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=idxLast, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=10 cap=10]=ColumnDefinition{name=72656d6f76656d656d65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=6d6964646c65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=7 cap=7]=ColumnDefinition{name=757365725f6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=idxAge, indexType=COMPOSITES}},compactionStrategyClass=class org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.LZ4Compressor},bloomFilterFpChance=0.01,memtable_flush_period_in_ms=0,caching=KEYS_ONLY,defaultTimeToLive=0,speculative_retry=99.0PERCENTILE,indexInterval=128,populateIoCacheOnFlush=false,droppedColumns={java.nio.HeapByteBuffer[pos=0 lim=8 cap=8]=1386324122307000, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=1386323878805000},triggers={}] To org.apache.cassandra.config.CFMetaData@1a87cb7[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=revert me later,readRepairChance=0.1,dclocalReadRepairChance=0.0,replicateOnWrite=true,gcGraceSeconds=864000,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=idxLast, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=10 cap=10]=ColumnDefinition{name=72656d6f76656d656d65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=6d6964646c65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=idxAge, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=7 cap=7]=ColumnDefinition{name=757365725f6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}},compactionStrategyClass=class org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.LZ4Compressor},bloomFilterFpChance=0.01,memtable_flush_period_in_ms=0,caching=KEYS_ONLY,defaultTimeToLive=0,speculative_retry=99.0PERCENTILE,indexInterval=128,populateIoCacheOnFlush=false,droppedColumns={java.nio.HeapByteBuffer[pos=0 lim=8 cap=8]=1386324122307000, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=1386323878805000},triggers={}]
INFO [MigrationStage:1] 2013-12-06 18:03:40,280 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-schema_columnfamilies@9688391(1415/14150 serialized/live bytes, 27 ops)
INFO [FlushWriter:12] 2013-12-06 18:03:40,282 Memtable.java (line 328) Writing Memtable-schema_columnfamilies@9688391(1415/14150 serialized/live bytes, 27 ops)
INFO [FlushWriter:12] 2013-12-06 18:03:40,417 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-19-Data.db (874 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=230451)Now we change all remaining attributes and chain them using AND as specified in the help file.
cqlsh:jw_schema1> ALTER TABLE users WITH bloom_filter_fp_chance = '0.1' AND
... caching = 'all' AND
... dclocal_read_repair_chance = 0.123 AND
... gc_grace_seconds = 86400 AND
... index_interval = 256 AND
... Read_repair_chance = 0.75 AND
... replicate_on_write = 'false' AND
... populate_io_cache_on_flush = 'true' AND
... default_time_to_live = 100 AND
... speculative_retry = 'always' AND
... memtable_flush_period_in_ms = 100000 AND
... compaction={'class': 'LeveledCompactionStrategy'} AND
... compression={'sstable_compression': 'SnappyCompressor'};
cqlsh:jw_schema1>
So everything looks good..
INFO [Thrift:7] 2013-12-06 18:05:25,490 MigrationManager.java (line 257) Update ColumnFamily 'jw_schema1/users' From org.apache.cassandra.config.CFMetaData@1306e0a[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=revert me later,readRepairChance=0.1,dclocalReadRepairChance=0.0,replicateOnWrite=true,gcGraceSeconds=864000,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=idxLast, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=10 cap=10]=ColumnDefinition{name=72656d6f76656d656d65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=6d6964646c65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=7 cap=7]=ColumnDefinition{name=757365725f6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=idxAge, indexType=COMPOSITES}},compactionStrategyClass=class org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.LZ4Compressor},bloomFilterFpChance=0.01,memtable_flush_period_in_ms=0,caching=KEYS_ONLY,defaultTimeToLive=0,speculative_retry=99.0PERCENTILE,indexInterval=128,populateIoCacheOnFlush=false,droppedColumns={java.nio.HeapByteBuffer[pos=0 lim=8 cap=8]=1386324122307000, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=1386323878805000},triggers={}] To org.apache.cassandra.config.CFMetaData@4f2b58[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=revert me later,readRepairChance=0.75,dclocalReadRepairChance=0.123,replicateOnWrite=false,gcGraceSeconds=86400,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=idxLast, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=10 cap=10]=ColumnDefinition{name=72656d6f76656d656d65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=6d6964646c65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=idxAge, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=7 cap=7]=ColumnDefinition{name=757365725f6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}},compactionStrategyClass=class org.apache.cassandra.db.compaction.LeveledCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.SnappyCompressor},bloomFilterFpChance=0.1,memtable_flush_period_in_ms=100000,caching=ALL,defaultTimeToLive=100,speculative_retry=ALWAYS,indexInterval=128,populateIoCacheOnFlush=true,droppedColumns={java.nio.HeapByteBuffer[pos=0 lim=8 cap=8]=1386324122307000, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=1386323878805000},triggers={}]
INFO [MigrationStage:1] 2013-12-06 18:05:25,505 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-schema_columnfamilies@27711753(1401/14010 serialized/live bytes, 27 ops)
INFO [FlushWriter:13] 2013-12-06 18:05:25,506 Memtable.java (line 328) Writing Memtable-schema_columnfamilies@27711753(1401/14010 serialized/live bytes, 27 ops)
INFO [FlushWriter:13] 2013-12-06 18:05:25,630 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-20-Data.db (860 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=232514)
INFO [CompactionExecutor:33] 2013-12-06 18:05:25,633 CompactionTask.java (line 115) Compacting [SSTableReader(path='/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-19-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-18-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-20-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-17-Data.db')]
INFO [CompactionExecutor:33] 2013-12-06 18:05:25,829 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-compaction_history@16766765(404/4040 serialized/live bytes, 10 ops)
INFO [FlushWriter:13] 2013-12-06 18:05:25,830 Memtable.java (line 328) Writing Memtable-compaction_history@16766765(404/4040 serialized/live bytes, 10 ops)
INFO [FlushWriter:13] 2013-12-06 18:05:25,941 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/compaction_history/system-compaction_history-jb-12-Data.db (270 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=233245)
INFO [CompactionExecutor:33] 2013-12-06 18:05:25,944 CompactionTask.java (line 272) Compacted 4 sstables to [/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-21,]. 9,077 bytes to 6,530 (~71% of original) in 192ms = 0.032435MB/s. 6 total rows, 3 unique. Row merge counts were {1:2, 2:0, 3:0, 4:1, }by now, you should check your table change using command
cqlsh:jw_schema1> desc table users;
CREATE TABLE users (
user_id text,
age int,
firest text,
last text,
middle text,
removememe text,
PRIMARY KEY (user_id)
) WITH
bloom_filter_fp_chance=0.100000 AND
caching='ALL' AND
comment='revert me later' AND
dclocal_read_repair_chance=0.123000 AND
gc_grace_seconds=86400 AND
index_interval=256 AND
read_repair_chance=0.750000 AND
replicate_on_write='false' AND
populate_io_cache_on_flush='true' AND
default_time_to_live=100 AND
speculative_retry='ALWAYS' AND
memtable_flush_period_in_ms=100000 AND
compaction={'class': 'LeveledCompactionStrategy'} AND
compression={'sstable_compression': 'SnappyCompressor'};
CREATE INDEX idxAge ON users (age);
CREATE INDEX idxLast ON users (last);
Now reset to the default.
cqlsh:jw_schema1> ALTER TABLE users WITH bloom_filter_fp_chance = '0.010000' AND
... caching = 'KEYS_ONLY' AND
... comment = 'storing user data' AND
... dclocal_read_repair_chance = 0.000000 AND
... gc_grace_seconds = 864000 AND
... index_interval = 128 AND
... Read_repair_chance = 0.100000 AND
... replicate_on_write = 'true' AND
... populate_io_cache_on_flush = 'false' AND
... default_time_to_live = 0 AND
... speculative_retry = '99.0PERCENTILE' AND
... memtable_flush_period_in_ms = 0 AND
... compaction = {'class': 'SizeTieredCompactionStrategy'} AND
... compression = {'sstable_compression': 'LZ4Compressor'};
cqlsh:jw_schema1>
INFO [Thrift:7] 2013-12-06 18:10:19,355 MigrationManager.java (line 257) Update ColumnFamily 'jw_schema1/users' From org.apache.cassandra.config.CFMetaData@1306e0a[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=storing user data,readRepairChance=0.75,dclocalReadRepairChance=0.123,replicateOnWrite=false,gcGraceSeconds=86400,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=idxLast, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=10 cap=10]=ColumnDefinition{name=72656d6f76656d656d65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=6d6964646c65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=7 cap=7]=ColumnDefinition{name=757365725f6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=idxAge, indexType=COMPOSITES}},compactionStrategyClass=class org.apache.cassandra.db.compaction.LeveledCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.SnappyCompressor},bloomFilterFpChance=0.01,memtable_flush_period_in_ms=100000,caching=KEYS_ONLY,defaultTimeToLive=100,speculative_retry=ALWAYS,indexInterval=128,populateIoCacheOnFlush=true,droppedColumns={java.nio.HeapByteBuffer[pos=0 lim=8 cap=8]=1386324122307000, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=1386323878805000},triggers={}] To org.apache.cassandra.config.CFMetaData@3a4b0c[cfId=53bb6412-bd01-313d-8a6f-0f00b8d3c555,ksName=jw_schema1,cfName=users,cfType=Standard,comparator=org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type),comment=storing user data,readRepairChance=0.1,dclocalReadRepairChance=0.0,replicateOnWrite=true,gcGraceSeconds=864000,defaultValidator=org.apache.cassandra.db.marshal.BytesType,keyValidator=org.apache.cassandra.db.marshal.UTF8Type,minCompactionThreshold=4,maxCompactionThreshold=32,column_metadata={java.nio.HeapByteBuffer[pos=0 lim=4 cap=4]=ColumnDefinition{name=6c617374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=idxLast, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=666972657374, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=10 cap=10]=ColumnDefinition{name=72656d6f76656d656d65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=ColumnDefinition{name=6d6964646c65, validator=org.apache.cassandra.db.marshal.UTF8Type, type=REGULAR, componentIndex=0, indexName=null, indexType=null}, java.nio.HeapByteBuffer[pos=0 lim=3 cap=3]=ColumnDefinition{name=616765, validator=org.apache.cassandra.db.marshal.Int32Type, type=REGULAR, componentIndex=0, indexName=idxAge, indexType=COMPOSITES}, java.nio.HeapByteBuffer[pos=0 lim=7 cap=7]=ColumnDefinition{name=757365725f6964, validator=org.apache.cassandra.db.marshal.UTF8Type, type=PARTITION_KEY, componentIndex=null, indexName=null, indexType=null}},compactionStrategyClass=class org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy,compactionStrategyOptions={},compressionOptions={sstable_compression=org.apache.cassandra.io.compress.LZ4Compressor},bloomFilterFpChance=0.01,memtable_flush_period_in_ms=0,caching=KEYS_ONLY,defaultTimeToLive=0,speculative_retry=99.0PERCENTILE,indexInterval=128,populateIoCacheOnFlush=false,droppedColumns={java.nio.HeapByteBuffer[pos=0 lim=8 cap=8]=1386324122307000, java.nio.HeapByteBuffer[pos=0 lim=6 cap=6]=1386323878805000},triggers={}]
INFO [MigrationStage:1] 2013-12-06 18:10:19,370 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-schema_columnfamilies@18158703(1417/14170 serialized/live bytes, 27 ops)
INFO [FlushWriter:14] 2013-12-06 18:10:19,371 Memtable.java (line 328) Writing Memtable-schema_columnfamilies@18158703(1417/14170 serialized/live bytes, 27 ops)
INFO [FlushWriter:14] 2013-12-06 18:10:19,493 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-24-Data.db (876 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=239324)
INFO [CompactionExecutor:35] 2013-12-06 18:10:19,497 CompactionTask.java (line 115) Compacting [SSTableReader(path='/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-21-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-23-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-22-Data.db'), SSTableReader(path='/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-24-Data.db')]
INFO [CompactionExecutor:35] 2013-12-06 18:10:19,734 ColumnFamilyStore.java (line 734) Enqueuing flush of Memtable-compaction_history@29175602(404/4040 serialized/live bytes, 10 ops)
INFO [FlushWriter:14] 2013-12-06 18:10:19,735 Memtable.java (line 328) Writing Memtable-compaction_history@29175602(404/4040 serialized/live bytes, 10 ops)
INFO [FlushWriter:14] 2013-12-06 18:10:19,872 Memtable.java (line 366) Completed flushing /var/lib/cassandra/data/system/compaction_history/system-compaction_history-jb-13-Data.db (270 bytes) for commitlog position ReplayPosition(segmentId=1386234428882, position=240055)
INFO [CompactionExecutor:35] 2013-12-06 18:10:19,875 CompactionTask.java (line 272) Compacted 4 sstables to [/var/lib/cassandra/data/system/schema_columnfamilies/system-schema_columnfamilies-jb-25,]. 9,137 bytes to 6,486 (~70% of original) in 231ms = 0.026777MB/s. 6 total rows, 3 unique. Row merge counts were {1:2, 2:0, 3:0, 4:1, }You can read more on data type created in the table and different metadata settings to the table to fine tune specific environment.
So this wrap up of the cassandra cqlsh table altering lesson.
--------------------
UPDATE :
It is currently not able to modify the primary key once it is created. Let's just say you created a column family and later day you want to add a column into column family and want to use that column in the primary key, currently it is not possible. There are some workaround, example, the column family and re-create with new schema or create a new column family with different name.
[...] a previous article, we covered a basic data definition language, and in this article, we are going to cover data manipulation language. With cql3, composite data [...]
ReplyDelete