i can not excute native batch CQL ! #156

Closed
zhouhero opened this Issue Dec 25, 2012 · 28 comments

3 participants

@zhouhero

i try to excute batch cql by using kundera2.2's native Query in cassandra 1.1.5 , it failed.
Is kundera2.2 not support btach cql ?

[MY BTACH CQL3]

BEGIN BATCH USING CONSISTENCY QUORUM
INSERT INTO users (userid, password, name) VALUES ('user2', 'ch@ngem3b', 'second user');
UPDATE users SET password = 'ps22dhds' WHERE userid = 'user3';
INSERT INTO users (userid, password) VALUES ('user4', 'ch@ngem3c');
DELETE name FROM users WHERE userid = 'user1';
APPLY BATCH;

[MY CODE]
Query query = entityManager.createNativeQuery(strCql, clazz);
query.executeUpdate();

@mevivs
Collaborator

I did not try it yet. But i guess executing these as seperate cql3 statements should work!

There is another alternative(JPA Way) to execute this.

setConsistencyLevel() => QUORUM

define batch.size in persistence.xml

Perform your INSERT/UPDATE/DELETE via em over Objects. And then invoke em.flush() for explicit flush, else depending upon batch size it will implicitly flush them down.

-Vivek

@mevivs
Collaborator

I need to look into, but i guess cassandra java driver must be wrapping this up into addBatch() and executeBatch() jdbc calls!

-Vivek

@mevivs
Collaborator

I guess, you need to try it without ";"
Here it is:

BEGIN BATCH USING CONSISTENCY QUORUM
  INSERT INTO users (KEY, password, name) VALUES ('user2', 'ch@ngem3b', 'second user')
  UPDATE users SET password = 'ps22dhds' WHERE KEY = 'user2'
  INSERT INTO users (KEY, password) VALUES ('user3', 'ch@ngem3c')
  DELETE name FROM users WHERE key = 'user2'
  INSERT INTO users (KEY, password, name) VALUES ('user4', 'ch@ngem3c', 'Andrew')
APPLY BATCH;

http://www.datastax.com/docs/1.0/references/cql/BATCH

@zhouhero

i tried it. the error happend.

[Error Msg]
javax.persistence.PersistenceException: InvalidRequestException(why:line 4:0 no viable alternative at input 'APPLY')
at com.impetus.client.cassandra.CassandraClientBase.executeQuery(CassandraClientBase.java:710)
at com.impetus.client.cassandra.pelops.PelopsClient.executeQuery(PelopsClient.java:597)
at com.impetus.client.cassandra.query.CassQuery.onExecuteUpdate(CassQuery.java:209)
at com.impetus.kundera.query.QueryImpl.executeUpdate(QueryImpl.java:134)
at pokemon.common.base.BaseService.exeNativeUpdate(BaseService.java:287)
at pokemon.common.service.Test1Service.doBatch(Test1Service.java:126)
at pokemon.common.service.Test1Service$$FastClassByCGLIB$$e6f45817.invoke()
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)

@mevivs
Collaborator

Just now a test case is added demonstrate the same, have a look at commit ada7cac
for reference.

A new test case " testCQLBatch()" added.

@kkmishra kkmishra was assigned Dec 25, 2012
@zhouhero

thank you very much!
it is working well now!

@zhouhero zhouhero closed this Dec 25, 2012
@mevivs
Collaborator

Great! 1 question:

Using/Analyzing kundera for ?

-Vivek

@zhouhero

it's kundera 2.2

@mevivs
Collaborator

Ok . I mean using Kundera for developing any application or creating a case study?

-Vivek

@zhouhero

i am using kundera2.2(2.3) + cassandra1.2(cql3/jpql/jdbc) developing a big application.
thank you for your support!!!

@zhouhero

when there are more than one table to update/delete/insert, how can i do the batch?
you know that only one entity "CassandraBatchEntity " can be set in the createNativeQuery().

Query q = em.createNativeQuery(useNativeSql, CassandraBatchEntity.class);

@zhouhero

it did not work when update/insert two different tables in the batch !

@zhouhero zhouhero reopened this Dec 26, 2012
@mevivs
Collaborator

please share what are you trying to execute?
Entity class is required only to map with database and will wrap into that entity in case of select.(q.getresultList()) .
Hence it should work on multiple tables.

-Vivek

@zhouhero

[MY CQL]
BEGIN BATCH USING CONSISTENCY QUORUM
INSERT INTO test1(id, url) VALUES ('64907b40-29a1-11e2-93fa-90b11c71b811','w')
INSERT INTO test2(key, count) VALUES ('key1',12)

APPLY BATCH;

[MY CODE]
...
Query q = em.createNativeQuery(useNativeSql, Test1Entity.class);
q.executeUpdate
...

[ERROR MSG]
javax.persistence.PersistenceException: InvalidRequestException(why:Unknown identifier key)
at com.impetus.client.cassandra.CassandraClientBase.executeQuery(CassandraClientBase.java:710)
at com.impetus.client.cassandra.pelops.PelopsClient.executeQuery(PelopsClient.java:597)
at com.impetus.client.cassandra.query.CassQuery.onExecuteUpdate(CassQuery.java:209)
at com.impetus.kundera.query.QueryImpl.executeUpdate(QueryImpl.java:134)

@mevivs
Collaborator

As per error message:

[ERROR MSG]
javax.persistence.PersistenceException: InvalidRequestException(why:Unknown identifier key)
at com.impetus.client.cassandra.CassandraClientBase.executeQuery(CassandraClientBase.java:710)
at com.impetus.client.cassandra.pelops.PelopsClient.executeQuery(PelopsClient.java:597)
at com.impetus.client.cassandra.query.CassQuery.onExecuteUpdate(CassQuery.java:209)
at com.impetus.kundera.query.QueryImpl.executeUpdate(QueryImpl.java:134)

Looks like column family "test2" does not have a column with name "key" ! It has nothing to do Kundera.

@zhouhero

i have confirmed that there is a column in the "test2" table!

@mevivs
Collaborator

"InvalidRequestException(why:Unknown identifier key)" is thrown by Cassandra itself. Which means it is not able to find that identifier.

-Vivek

@mevivs
Collaborator

Are you able to execute:

BEGIN BATCH USING CONSISTENCY QUORUM
INSERT INTO test1(id, url) VALUES ('64907b40-29a1-11e2-93fa-90b11c71b811','w')
INSERT INTO test2(key, count) VALUES ('key1',12)

APPLY BATCH;

from CQL shell?

-Vivek

@zhouhero

yes,
it can work well in CQL shell;

@mevivs
Collaborator

Ok. Please share column family creation script with "test1" and "test2"

-Vivek

@zhouhero

cqlsh:pgl> create table test1 (id timeuuid primary key, url text, userid uuid, datetime timestamp, linkcounts int);
cqlsh:pgl> create table test2 (key text primary key, count int);

@zhouhero

you can test it in CQL3 shell

@mevivs
Collaborator

Working fine for me, have a look at at commit 77ce60e

Change made is:

        // Multiple table batch processing.

        createColumnFamily = "create table test1 (id timeuuid primary key, url text, userid uuid, datetime timestamp, linkcounts int)";
        em.createNativeQuery(createColumnFamily, CassandraBatchEntity.class).executeUpdate();
        createColumnFamily = "create table test2 (key text primary key, count int)";
        em.createNativeQuery(createColumnFamily, CassandraBatchEntity.class).executeUpdate();
        batchOps = "BEGIN BATCH USING CONSISTENCY QUORUM INSERT INTO test1(id, url) VALUES ('64907b40-29a1-11e2-93fa-90b11c71b811','w') INSERT INTO test2(key, count) VALUES ('key1',12) APPLY BATCH";
        em.createNativeQuery(batchOps, CassandraBatchEntity.class).executeUpdate();

``
@zhouhero

sorry, i am using cassadra 1.2 beta.
when cassadra 1.1.6 , it works well.

@mevivs
Collaborator

It might be an issue with cassandra 1.2 beta( we did not migrate kundera on this version yet!). You may want to raise this issue with Cassandra.

-Vivek

@zhouhero

i see,
when cassandra 1.2 released, i will confirm it again!
Thank you very much!

@mevivs
Collaborator

Verified and working fine with Cassandra 1.2 now.

@zhouhero

thank you very much.
let me try it.

@zhouhero zhouhero closed this Jan 22, 2013
@kkmishra kkmishra was unassigned by zhouhero Feb 13, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment