CODEWITHSUNDEEP
exceptioncassandracompositehector
Ramanathan Subbiah
Ramanathan Subbiah

Reputation: 37

Inserting rows in Cassandra table with 3 fields as composite key

I'm a newbie to Cassandra and I need it for a quick and small adhoc job. However, I'm on a stalemate with a problem. I have a column family created with the CQL below:


CREATE TABLE dummy_file_test
(
 dtPtn          INT,
 pxID           INT,
 startTm        INT,
 endTm          INT,
 patID          BIGINT,
 efile          BLOB,
 PRIMARY KEY(dtPtn, pxID, startTm)
);

I wrote the following method to insert data into the table.

public static void insertDataKey(HashMap nameValuePair, String colFamily) {
    try {
        Cluster cluster = HFactory.getOrCreateCluster(clusterName, hostPort);
        Keyspace keyspace = HFactory.createKeyspace(CASSANDRA_DUMMY_KEY_SPACE, cluster);


        Integer dtPtn = (Integer)nameValuePair.get("dtPtn");
        Integer pxID = (Integer)nameValuePair.get("pxID");
        Integer startTm = (Integer)nameValuePair.get("startTm");
        Integer endTm = (Integer)nameValuePair.get("endTm");
        Long patID = (Long)nameValuePair.get("patID");
        byte[] efile = (byte[])nameValuePair.get("efile");


        HColumn<String, Integer> column1 = HFactory.createColumn("dtPtn", dtPtn, new StringSerializer(), IntegerSerializer.get());
        HColumn<String, Integer> column2 = HFactory.createColumn("pxID", pxID, new StringSerializer(), IntegerSerializer.get());
        HColumn<String, Integer> column3 = HFactory.createColumn("startTm", startTm, new StringSerializer(), IntegerSerializer.get());
        HColumn<String, Integer> column4 = HFactory.createColumn("endTm", endTm, new StringSerializer(), IntegerSerializer.get());
        HColumn<String, Long> column5 = HFactory.createColumn("patID", patID, new StringSerializer(), LongSerializer.get());
        HColumn<String, byte[]> column6 = HFactory.createColumn("efile", efile, new StringSerializer(), BytesArraySerializer.get());


        Composite rowKey = new Composite();
        rowKey.addComponent("dtPtn", StringSerializer.get());
        rowKey.addComponent(dtPtn, IntegerSerializer.get());

        rowKey.addComponent("pxID", StringSerializer.get());
        rowKey.addComponent(pxID, IntegerSerializer.get());

        rowKey.addComponent("startTm", StringSerializer.get());
        rowKey.addComponent(startTm, IntegerSerializer.get());


        Mutator<Composite> mutator = HFactory.createMutator(keyspace, CompositeSerializer.get());
        mutator.addInsertion(rowKey, colFamily, column1);
        mutator.addInsertion(rowKey, colFamily, column2);
        mutator.addInsertion(rowKey, colFamily, column3);
        mutator.addInsertion(rowKey, colFamily, column4);
        mutator.addInsertion(rowKey, colFamily, column5);
        mutator.addInsertion(rowKey, colFamily, column6);

        mutator.execute();
    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

However, when I run the code, I get

InvalidRequestException(why:Expected 4 or 0 byte int (21))

I'm pretty much confused with Hector APIs and can't find any complete tutorial/material that I can fully rely on. Any advice in resolving the above would be of a great help.

Thanks in advance.

ps: I'm on Cassandra 1.2

Upvotes: 3

Views: 2875

Answers (1)

Richard
Richard

Reputation: 11100

If you're creating schemas in CQL you will probably be better off accessing your data through CQL too. But you can still access it through the thrift interface.

When using composite primary keys in CQL as you have specified, the first key is the partition key which becomes the row key. The other keys are part of the column composite.

So for your example, if you did

insert into dummy_file_test (dtPtn, pxID, startTm, endTm, patID, efile) values (1, 2, 3, 4, 5, 0x06);

then list in cassandra-cli:

[default@ks] list dummy_file_test; 
RowKey: 1
=> (column=2:3:, value=, timestamp=1366620262555000)
=> (column=2:3:efile, value=06, timestamp=1366620262555000)
=> (column=2:3:endtm, value=00000004, timestamp=1366620262555000)
=> (column=2:3:patid, value=0000000000000005, timestamp=1366620262555000)

you see the row key is simply the integer 1 corresponding to dtPtn. The other columns in the primary key have been prefixed onto the column name.

If you want to use Hector to insert into this you need to use dtPtn for your row key, then a composite column key of pxID:startTm:col_name for your columns.

You can also tell Cassandra to make your partition key a composite of your columns. To do this, you need extra brackets in the primary key clause:

CREATE TABLE dummy_file_test
(
 dtPtn          INT,
 pxID           INT,
 startTm        INT,
 endTm          INT,
 patID          BIGINT,
 efile          BLOB,
 PRIMARY KEY((dtPtn, pxID, startTm))
);

Now your row key is a composite of dtPtn, pxID, startTm:

[default@ks] list dummy_file_test;
RowKey: 1:2:3
=> (column=, value=, timestamp=1366620916952000)
=> (column=efile, value=06, timestamp=1366620916952000)
=> (column=endtm, value=00000004, timestamp=1366620916952000)
=> (column=patid, value=0000000000000005, timestamp=1366620916952000)

Note that the column names don't appear in the row key composite, so you don't need to add them. Your code should simply be:

Composite rowKey = new Composite();
rowKey.addComponent(dtPtn, IntegerSerializer.get());
rowKey.addComponent(pxID, IntegerSerializer.get());
rowKey.addComponent(startTm, IntegerSerializer.get());

Upvotes: 4

Related Questions