how to use sstableloader with CQL 3.0

Question

I am trying to use the sstableloader for a table that has been created with CQL 3.0 I have created a table in Cassandra with CQL 3 as follows:

CREATE TABLE users1 (
  id text PRIMARY KEY,
  firstname text,
  lastname text, 
) WITH
compaction={'class': 'SizeTieredCompactionStrategy'} AND
 compression={'sstable_compression': 'SnappyCompressor'};

I create the sstables using the SSTableSimpleUnsortedWriter as follows:

IPartitioner partitioner = new Murmur3Partitioner();
SSTableSimpleUnsortedWriter usersWriter = new SSTableSimpleUnsortedWriter(  
        directory, partitioner,keyspace,"users1",AsciiType.instance, null,64);
    long timestamp = System.currentTimeMillis() * 1000;
    ByteBuffer id = bytes("a11");
    usersWriter.newRow(id);
    usersWriter.addColumn(bytes("firstname"), bytes("Ticho"), timestamp);
    usersWriter.addColumn(bytes("lastname"), bytes("Richie"), timestamp);
    usersWriter.close();
    System.exit(0);

Although the sstables are generated and I successfully loaded sstables into a 4-node cluster in EC2. But I am not able to query them. It just waits and eventually gives a RPC timeout error. I could do this successfully upload and query with columnfamily created using CLI as shown in the Datastax Developer Blog.

Pls advise me on how to use the SSTableSimpleUnsortedWriter correctly with CQL....

My final aim is to create a columnfamily with composite keys in CQL(replicating a SQL table) and upload data with SSTableLoader from a very large csv export file..

axle_h axle_h · Accepted Answer · 2013-08-12T08:47:35

Here's the cli DESCRIBE output for your cf:

Key Validation Class: org.apache.cassandra.db.marshal.UTF8Type
Default column value validator: org.apache.cassandra.db.marshal.BytesType
Columns sorted by: org.apache.cassandra.db.marshal.CompositeType(org.apache.cassandra.db.marshal.UTF8Type)

You have at least three problems:

You're using AsciiType instead of UTF8Type for the partition key validator. You don't need to do anything to your ByteBufferUtil.bytes(String) calls as they encode to UTF8.
Column names in CQL are always composite types so (as long as you aren't using collections) you need to build your column names using db.marshal.CompositeType, where the last type in the composite list is UTF8.
Check out cli LIST output for your table, it includes a column with a blank name and value which you need to add e.g.:

RowKey: 123 => (column=, value=, timestamp=1376298826474000) => (column=firstname, value=416c6578, timestamp=1376298826474000) => (column=lastname, value=4861736c656875727374, timestamp=1376298826474000)

how to use sstableloader with CQL 3.0

2 Answers