Querying "OneToMany" related data returns "null"

[emgsilva]

Hi,

I am having problems retrieving all the related elements from a related table in a "OneToMany" relation (one Domain has Many ServiceTypes):

@Id
@Column(name = "domainID")
private String domainID;

@Column(name = "domainDescription")
private String domainDescription;

@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
@JoinColumn(name="domainID")
private Set<ServiceTypes> serviceTypes;

I am able to query the Domain by the "domainID" (Domains domain = em.find(Domain.class, domainID);) but when I try to get the related "ServiceTypes" (domain.getServiceTypes()) I get a "null" (although I am sure I have two "ServiceTypes" as part of the domain in the DB - with the "domainID" field)....

I am using: Kundera 2.2, Cassandra 1.1.6.

[emgsilva]

Hi,

I did make some changes on the "schema", namely changed all the names to "lower case" only, I had "CamelCased" names, which I think was generating trouble... Now I am able to get some related elements on OneToMany... however this is still not working for the case of Domain OneToMany ServiceTypes... These are the class entities I have (Domain OneToMany ServiceTypes OneToMany ClassificationProperties OneToMany ClassificationPropertiesValues):

Domains:

@Id
@Column(name = "domain")
private String domain;

@Column(name = "domain_description")
private String domainDescription;

@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
@JoinColumn(name="domain")
private Set<ServiceTypes> serviceTypes;

ServiceTypes:

@Id
@Column(name = "service_id")
private String serviceID;

@Column(name = "domain")
private String domain;

@Column(name = "service_description")
private String serviceDescription;

@Column(name = "instance_property")
private String instanceProperty;

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinColumn(name="service_id")
private Set<ClassificationProperties> classificationProperties;

@Column(name = "count")
private double count;

ClassificationProperties:

@Id
@Column(name = "prop_id")
private String propID;

@Column(name = "service_id")
private String serviceID;

@Column(name = "classification_property")
private String classificationProperty;

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name="prop_id")
private Set<ClassificationPropertiesValues> classificationPropertiesValues;

ClassificationPropertiesValues:

@Id
@Column(name = "prop_value_id")
private String propValueID;

@Column(name = "prop_id")
private String propID;

@Column(name = "classification_property_value")
private String classificationPropertyValue;

I am only able to get "ClassificationProperties" from "ServiceTypes"... all the other OneToMany do not work... I am not able to get ServiceTypes from Domains, and ClassificationPropertiesValues from ClassificationProperties (which I get from ServiceTypes).

No idea why is this happening. My persistence.xml looks like this:

<persistence-unit name="cassandra_Services">
    <provider>com.impetus.kundera.KunderaPersistence</provider>
    <class>com.meta.ServiceTypes</class>
    <class>com.meta.ClassificationProperties</class>
    <class>com.meta.ClassificationPropertiesValues</class>
    <class>com.meta.Domains</class>
    <exclude-unlisted-classes>true</exclude-unlisted-classes>
    <properties>
        <property name="kundera.nodes" value="localhost" />
        <property name="kundera.port" value="9160" />
        <property name="kundera.keyspace" value="services" />
        <property name="kundera.dialect" value="cassandra" />
        <property name="kundera.client" value="pelops" />
        <property name="kundera.client.lookup.class"
            value="com.impetus.client.cassandra.thrift.ThriftClientFactory" />
            <property name="kundera.cache.provider.class"
            value="com.impetus.kundera.cache.ehcache.EhCacheProvider" />
        <property name="kundera.cache.config.resource" value="/ehcache-test.xml" />
    </properties>
</persistence-unit>

Thank you in advance for any guidelines to solve this problem...

[mevivs]

One problem which i am able to see is:

@Column(name = "domain")
private String domain;

in ServiceTypes entity. As Domains entity also holds reference to ServiceTypes with column of same name:

@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
@JoinColumn(name="domain")
private Set<ServiceTypes> serviceTypes;

Looks problematic to me.

-Vivek

[emgsilva]

Hi Vivek,

You are right, I was setting the "redundant" fields on my persistence classes... They do exist on Cassandra but we only need one in the OneToMany fields...

The corrections I did lead to the behavior expected in most of the times, namely: get ClassificaitionPropertiesValues from ClassificationProperties, and ClassificationProperties from ServiceTypes.... However, I cannot get the ServiceTypes from Domains... Please find bellow the new entity classes (with the commented fields where I did change) and also find the Cassandra table structures):

Domains (Cassandra: domain | domain_description):

@Id
@Column(name = "domain")
private String domain;

@Column(name = "domain_description")
private String domainDescription;

@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
@JoinColumn(name="domain")
private Set<ServiceTypes> serviceTypes

ServiceTypes(Cassandra: service_id | count | domain | instance_property | service_description):

@Id
@Column(name = "service_id")
private String serviceID;

//@Column(name = "domain")
//private String domain;

@Column(name = "service_description")
private String serviceDescription;

@Column(name = "instance_property")
private String instanceProperty;

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinColumn(name="service_id")
private Set<ClassificationProperties> classificationProperties;

@Column(name = "count")
private double count;

ClassificationProperties (Cassandra: prop_id | classification_property | service_id):

@Id
@Column(name = "prop_id")
private String propID;

//@Column(name = "service_id")
//private String serviceID;

@Column(name = "classification_property")
private String classificationProperty;

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name="prop_id")
private Set<ClassificationPropertiesValues> classificationPropertiesValues;

ClassificationPropertiesValues (Cassandra: prop_value_id | classification_property_value | prop_id):

@Id
@Column(name = "prop_value_id")
private String propValueID;

//@Column(name = "prop_id")
//private String propID;

@Column(name = "classification_property_value")
private String classificationPropertyValue;

Cheers!

[emgsilva]

Just an update on declaring "repeated" fields... it can be done if instead of using "@joincolumn" we use "mappedBy", as in the following:

@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY, mappedBy="domain")
private Set<ServiceTypes> serviceTypes;

However, I still have the problem referred in the previous comment...

[mevivs]

Let me try at my end. I will update you on this.

-Vivek

[kkmishra]

Hi emgsilva ,
It is working fine for me,
Here is my entity definition (still there is no change in entity definition but I am sharing just for reference)

Domains:-

@Id
    @Column(name = "domain")
    private String domain;

    @Column(name = "domain_description")
    private String domainDescription;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name = "domain")
    private Set<ServiceTypes> serviceTypes;

ServiceTypes:-

 @Id
    @Column(name = "service_id")
    private String serviceID;

    @Column(name = "service_description")
    private String serviceDescription;

    @Column(name = "instance_property")
    private String instanceProperty;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name = "service_id")
    private Set<ClassificationProperties> classificationProperties;

ClassificationProperties:-

 @Id
    @Column(name = "prop_id")
    private String propID;

    @Column(name = "classification_property")
    private String classificationProperty;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "prop_id")
    private Set<ClassificationPropertiesValues> classificationPropertiesValues;

ClassificationPropertiesValues:-

@Id
    @Column(name = "prop_value_id")
    private String propValueID;

    @Column(name = "classification_property_value")
    private String classificationPropertyValue;

test:-

        Set<ClassificationPropertiesValues> classificationPropertiesValues1 = new HashSet<ClassificationPropertiesValues>();
        Set<ClassificationPropertiesValues> classificationPropertiesValues2 = new HashSet<ClassificationPropertiesValues>();
        Set<ClassificationPropertiesValues> classificationPropertiesValues3 = new HashSet<ClassificationPropertiesValues>();
        Set<ClassificationPropertiesValues> classificationPropertiesValues4 = new HashSet<ClassificationPropertiesValues>();

        ClassificationPropertiesValues values1 = new ClassificationPropertiesValues();
        values1.setPropValueID("propValueID1");
        values1.setClassificationPropertyValue("classificationPropertyValue1");

        ClassificationPropertiesValues values2 = new ClassificationPropertiesValues();
        values2.setPropValueID("propValueID2");
        values2.setClassificationPropertyValue("classificationPropertyValue2");

        classificationPropertiesValues1.add(values1);
        classificationPropertiesValues1.add(values2);

        ClassificationPropertiesValues values3 = new ClassificationPropertiesValues();
        values3.setPropValueID("propValueID3");
        values3.setClassificationPropertyValue("classificationPropertyValue3");

        ClassificationPropertiesValues values4 = new ClassificationPropertiesValues();
        values4.setPropValueID("propValueID4");
        values4.setClassificationPropertyValue("classificationPropertyValue4");

        classificationPropertiesValues2.add(values3);
        classificationPropertiesValues2.add(values4);

        ClassificationPropertiesValues values5 = new ClassificationPropertiesValues();
        values5.setPropValueID("propValueID5");
        values5.setClassificationPropertyValue("classificationPropertyValue5");

        ClassificationPropertiesValues values6 = new ClassificationPropertiesValues();
        values6.setPropValueID("propValueID6");
        values6.setClassificationPropertyValue("classificationPropertyValue6");

        classificationPropertiesValues3.add(values5);
        classificationPropertiesValues3.add(values6);

        ClassificationPropertiesValues values7 = new ClassificationPropertiesValues();
        values7.setPropValueID("propValueID7");
        values7.setClassificationPropertyValue("classificationPropertyValue7");

        ClassificationPropertiesValues values8 = new ClassificationPropertiesValues();
        values8.setPropValueID("propValueID8");
        values8.setClassificationPropertyValue("classificationPropertyValue8");

        classificationPropertiesValues4.add(values7);
        classificationPropertiesValues4.add(values8);

        Set<ClassificationProperties> classificationProperties1 = new HashSet<ClassificationProperties>();
        Set<ClassificationProperties> classificationProperties2 = new HashSet<ClassificationProperties>();

        ClassificationProperties properties1 = new ClassificationProperties();
        properties1.setPropID("propID1");
        properties1.setClassificationProperty("classificationProperty1");
        properties1.setClassificationPropertiesValues(classificationPropertiesValues1);

        ClassificationProperties properties2 = new ClassificationProperties();
        properties2.setPropID("propID2");
        properties2.setClassificationProperty("classificationProperty2");
        properties2.setClassificationPropertiesValues(classificationPropertiesValues2);

        classificationProperties1.add(properties1);
        classificationProperties1.add(properties2);

        ClassificationProperties properties3 = new ClassificationProperties();
        properties3.setPropID("propID3");
        properties3.setClassificationProperty("classificationProperty3");
        properties3.setClassificationPropertiesValues(classificationPropertiesValues3);

        ClassificationProperties properties4 = new ClassificationProperties();
        properties4.setPropID("propID4");
        properties4.setClassificationProperty("classificationProperty4");
        properties4.setClassificationPropertiesValues(classificationPropertiesValues4);

        classificationProperties2.add(properties3);
        classificationProperties2.add(properties4);

        Set<ServiceTypes> serviceTypes = new HashSet<ServiceTypes>();

        ServiceTypes serviceType1 = new ServiceTypes();
        serviceType1.setServiceID("service1");
        serviceType1.setServiceDescription("service one");
        serviceType1.setCount(111.1D);
        serviceType1.setInstanceProperty("instanceProperty for service one");
        serviceType1.setClassificationProperties(classificationProperties1);

        ServiceTypes serviceType2 = new ServiceTypes();
        serviceType2.setServiceID("service2");
        serviceType2.setServiceDescription("service two");
        serviceType2.setCount(111.2D);
        serviceType2.setInstanceProperty("instanceProperty for service two");
        serviceType2.setClassificationProperties(classificationProperties2);

        serviceTypes.add(serviceType1);
        serviceTypes.add(serviceType2);

        Domains domain = new Domains();
        domain.setDomain("domain1");
        domain.setDomainDescription("myDomain");
        domain.setServiceTypes(serviceTypes);

        em.persist(domain);

        em.clear();

        Domains foundDomain = em.find(Domains.class, "domain1");
        Assert.assertNotNull(foundDomain);
        Assert.assertNotNull(foundDomain.getDomain());
        Assert.assertNotNull(foundDomain.getDomainDescription());
        Assert.assertNotNull(foundDomain.getServiceTypes());
        Assert.assertEquals(2, foundDomain.getServiceTypes().size());

        Set<ServiceTypes> foundServiceTypes = foundDomain.getServiceTypes();
        for (ServiceTypes types : foundServiceTypes)
        {
            Assert.assertNotNull(types.getInstanceProperty());
            Assert.assertNotNull(types.getServiceDescription());
            Assert.assertNotNull(types.getServiceID());
            Assert.assertNotNull(types.getClassificationProperties());
            Assert.assertNotNull(types.getCount());
            Assert.assertEquals(2, types.getClassificationProperties().size());

            Set<ClassificationProperties> foundProperties = types.getClassificationProperties();
            for (ClassificationProperties properties : foundProperties)
            {
                Assert.assertNotNull(properties.getClassificationProperty());
                Assert.assertNotNull(properties.getPropID());
                Assert.assertNotNull(properties.getClassificationPropertiesValues());
                Assert.assertEquals(2, properties.getClassificationPropertiesValues().size());

                Set<ClassificationPropertiesValues> foundValues = properties.getClassificationPropertiesValues();
                for (ClassificationPropertiesValues values : foundValues)
                {
                    Assert.assertNotNull(values.getClassificationPropertyValue());
                    Assert.assertNotNull(values.getPropValueID());
                }
            }
        }

persistence-unit:-

<persistence-unit name="cassandra_Services">
        <provider>com.impetus.kundera.KunderaPersistence</provider>
        <class>com.impetus.kundera.cassandra.ServiceTypes</class>
        <class>com.impetus.kundera.cassandra.ClassificationProperties</class>
        <class>com.impetus.kundera.cassandra.ClassificationPropertiesValues</class>
        <class>com.impetus.kundera.cassandra.Domains</class>
        <exclude-unlisted-classes>true</exclude-unlisted-classes>
        <properties>
            <property name="kundera.nodes" value="localhost" />
            <property name="kundera.port" value="9160" />
            <property name="kundera.keyspace" value="services" />
            <property name="kundera.dialect" value="cassandra" />
            <property name="kundera.client.lookup.class"
                value="com.impetus.client.cassandra.thrift.ThriftClientFactory" />
            <property name="kundera.cache.provider.class"
                value="com.impetus.kundera.cache.ehcache.EhCacheProvider" />
            <property name="kundera.ddl.auto.prepare" value="create" />
            <property name="kundera.cache.config.resource" value="/ehcache-test.xml" />
        </properties>
    </persistence-unit>

Only one difference in persistence unit is

<property name="kundera.ddl.auto.prepare" value="create" />

for creating keyspace and column family.

Cheers
-Kuldeep

[emgsilva]

Hi Kuldeep,

Thank you for taking the time to test it in depth... I have just performed the same test myself... and again I get the very same behavior... cannot get the ServiceTypes from the Domains (Assert.assertNotNull(foundDomain.getServiceTypes()); fails!).... If I skip that (get the services directly) all the other tests working fine (I also synchronized the persistence.xml).

Just to make sure everything we have the exact same setup here are my cqlsh CQL3 (cqlsh --cql3) scripts to create the tables in Cassandra:

Domains:

CREATE TABLE domains(    
domain varchar,
domain_description varchar,
PRIMARY KEY (domain)
);

ServiceTypes:

CREATE TABLE servicetypes(    
service_id varchar,
domain varchar,
service_description varchar,
instance_property varchar,
count double,
PRIMARY KEY (service_id)
);

ClassificationProperties:

CREATE TABLE classificationproperties(    
prop_id varchar,
service_id varchar,
classification_property varchar,
PRIMARY KEY (prop_id)
);

ClassificationPropertiesValues:

CREATE TABLE classificationpropertiesvalues(    
prop_value_id varchar,
prop_id varchar,
classification_property_value varchar,
PRIMARY KEY (prop_value_id)
);

Please let me know if you see any problem with this!

Thank you for your assistance! Cheers!

Eduardo

[mevivs]

Hi Eduardo,
Just to share with you, There are known issues b/w CQL and thrift API interoperability prior to cassandra 1.2 .
I think Kuldeep has tried it via cassandra-cli or using with "kundera.ddl.auto.prepare" option.

I would suggest, if you could try it at your end in case if it works for you.

-Vivek

[emgsilva]

Hi Vivek,

Indeed, that seems to be the problem (I am still using Cassandra 1.1.6, probably need to update to the latest version). I decided to use CQL3 because in some other tables (and keyspaces) where I needed to use "composite keys", so I decided to stick around and use "cqlsh --cql3" for creating all my new cassandra keyspaces/tables.

These are the tests I did, following your advise:

1. I used the "" (after "dropping" all my tables) and it worked fine. However, by mistake I also added this property to another "keyspace" persistence unit and it wiped out my existing DB... which is not very nice, so I decided to use the "update" instead of the "create", I think the "create" can be a bit "dangerous"...

In order to make sure my "workflow" is working OK (I normally create the schemas beforehand on cqlsh or cassandra-cli), I used "cassandra-cli" and create all the tables "manually". Then I performed the test from Kuldeep and also mine (previously failing) are now working fine!

I will proceed with my developments, it may be that I need to use composite keys very soon, and I will see how it works, however I am not sure I can make those in "cassandra-cli"... any thoughts on this?

Thank you again for the always quick feedback and support!

Cheers,

Eduardo

[kkmishra]

Hi Eduardo,
Great, on composite keys part you may refer:
https://github.com/impetus-opensource/Kundera/wiki/Using-Compound-keys-with-Kundera (Composite/Compound Key in Cassandra) section.
kundera will work seamlessly as this support is enabled for CQL only . Let me know, if you face any issue regarding its usage.
Are we good to close on this ?

Thanks
Kuldeep

[emgsilva]

Hi Kuldeep,

Indeed, as you refer in your link, to enable composite keys I need to use cqlsh --cql3... this is what I was already doing on another table (it is a single table with a composite key). Hopefully I will not get this "weird" behavior I was getting in this issue if I need to create new tables with composite keys with relations with other tables. I think I will also upgrade to the new version of cassandra, which according to Vivek seems to not have these issues anymore...

I will let you know if something pops up... but this issue is now closed!

Thanks again for all the support and keep up with the great work! Cheers,

Eduardo

[mevivs]

Hi Kuldeep/Eduardo,
One thing would like discuss on CQL/Thrift interoperability front.
Even with 1.1., if you perform CRUD over composite key/compound keys using cqlsh, that should work fine. But interoperability fails once you start managing your column families which contain non-composite/compound columns.

Composite/compound primary key support is enabled via CQL only. So Kundera simply translates everything in CQL format(bypassing thrift API). But for others it will still go by thrift way.

I have discussed this issue with Jonathan(cofounder of datastax) and it has been promised to be released with cassandra 1.2. However while upgrading Kundera for cassandra 1.2 APIs. We did encounter some issues and have posted them on user group. Still waiting for some concrete answers on that. Rest is fine.

So, i would suggest, to use cqlsh for fetching data over non-composite column family, but not manage(insert/delete/update).

Sharing my understanding with you guys 👍

-Vivek

[emgsilva]

Hi Vivek,

I can see this is still these developments (on Cassandra) are still being matured... but it is important to provide some guidelines (maybe on the wiki - troubleshooting), so whenever one comes across to these problems can get some quick reference to overcome it. I understand that this is not a Kundera problem, but at a given point one cannot trace back where the problem is taking place, in my case I still do not understand why it working between some tables (relations) and the other not...

Thank you for sharing your understanding Vivek, it is very good to keep on learning these details!

Cheers,

Eduardo

[emgsilva]

Hi Vivek,

Just a quick follow-up... at this moment I am implementing a mix of tables with composite keys and other (related tables) with non-composite keys... It seems to me the best way to implement my requirements.

I did a very quick test implementing such a mix and got problems, namely I am getting this message (which I suppose does have something to do with the handling you refer above - through thrift if it is non-composite):

ERROR [main] (ThriftClient.java:163) - Error while persisting record. Details: null
Exception in thread "main" com.impetus.kundera.KunderaException: com.impetus.kundera.KunderaException: InvalidRequestException(why:line 1:365 mismatched character ']' expecting '-')
    at com.impetus.kundera.persistence.EntityManagerImpl.persist(EntityManagerImpl.java:208)
    at com.sigmabees.tests.ServiceUsageDbDataTest.main(ServiceUsageDbDataTest.java:78)
Caused by: com.impetus.kundera.KunderaException: InvalidRequestException(why:line 1:365 mismatched character ']' expecting '-')
    at com.impetus.client.cassandra.thrift.ThriftClient.onPersist(ThriftClient.java:164)
    at com.impetus.kundera.client.ClientBase.persist(ClientBase.java:83)
    at com.impetus.client.cassandra.thrift.ThriftClient.persist(ThriftClient.java:126)
    at com.impetus.kundera.lifecycle.states.ManagedState.handleFlush(ManagedState.java:193)
    at com.impetus.kundera.graph.Node.flush(Node.java:525)
    at com.impetus.kundera.persistence.PersistenceDelegator.flush(PersistenceDelegator.java:411)
    at com.impetus.kundera.persistence.PersistenceDelegator.persist(PersistenceDelegator.java:169)
    at com.impetus.kundera.persistence.EntityManagerImpl.persist(EntityManagerImpl.java:202)

I get this error when using Kundera

<property name="kundera.ddl.auto.prepare" value="update" />

which automatically generated the tables that have "composite keys" (the tables without composite keys are not generated)... The same took place when I generated the tables manually via cassandra-cli and cqlsh...

Is it impossible to get it running (Kundera and Cassandra) at the moment with a mix of (related - OneToMany) tables with composite keys and tables without composite keys? (I am still using cassandra 1.1.6... but you said it is still a problem with 1.2)... I may opt to implement only "non" composite key tables but it would be much more elegant (an I suppose faster - given that I would not need to implement secondary indexes) with composite keys...

EDIT: quick update, testing only with two Tables with composite keys (created from cqlsh --cql3, I removed the kundera.ddl.auto.prepare), I do have more tables without composite keys but I wanted to test this first... When I add values to one table alone and commit it works fine (I can see the values are stored), however when I add a values to the related table (OneToMany), I get an error message:

Exception in thread "main" com.impetus.kundera.KunderaException: com.impetus.kundera.KunderaException: InvalidRequestException(why:line 1:191 no viable alternative at character ']')
    at com.impetus.kundera.persistence.EntityManagerImpl.persist(EntityManagerImpl.java:208)
    at com.tests.ServiceUsageDbDataTest.main(ServiceUsageDbDataTest.java:88)
Caused by: com.impetus.kundera.KunderaException: InvalidRequestException(why:line 1:191 no viable alternative at character ']')
    at com.impetus.client.cassandra.thrift.ThriftClient.onPersist(ThriftClient.java:164)
    at com.impetus.kundera.client.ClientBase.persist(ClientBase.java:83)
    at com.impetus.client.cassandra.thrift.ThriftClient.persist(ThriftClient.java:126)
    at com.impetus.kundera.lifecycle.states.ManagedState.handleFlush(ManagedState.java:193)

These are the persistency classes:

@Embeddable
public class ServiceInstancesKey {

@Column(name = "app_profile_id")
private String appProfileID;

@Column(name = "service_type_id")
private String serviceTypeID;

@Column(name = "instance_property")
private String instanceProperty;

@Entity
@Table(name = "serviceinstances", schema = "serviceusage@cassandra_ServiceUsage")
public class ServiceInstances {

@EmbeddedId
private ServiceInstancesKey key;

@Column(name = "instance_id")
private String instanceID;

@Column(name = "usage_count")
private String usageCound;

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name="instance_id")
private Set<FollowUpServiceInstances> followUpServices;

@Embeddable
public class FollowUpServiceInstancesKey {

@Column(name = "src_serv_instance")
private String srcServInstace;

@Column(name = "dest_serv_instance")
private String destServInstance;

@Entity
@Table(name = "followupserviceinstances", schema = "serviceusage@cassandra_ServiceUsage")
public class FollowUpServiceInstances {

@EmbeddedId
private FollowUpServiceInstancesKey key;

@Column(name = "usage_count")
private Double usageCount;

(sorry for the very long post... I wanted to add as much info as I could gather).

Cheers!

Eduardo

[mevivs]

I will have a look on OneToMany relationship b/w entities referring to composite keys.
Could you please share CQL scripts to create these tables and also code snippet to perform CRUD. It may help us to resolve this quickly.

On Mixing thrift and CQL part, as you have encountered same happens even from cassandra-cli and cql. I would suggest to try that with cassandra 1.2(There are changes are large level around such handling). Issues which i have been talking about in 1.2 are bit different.

-Vivek

[mevivs]

Hi Eduardo,
This scenario does not look correct to me. Suppose for Entity A and entity B has a one to many relationship. So it means @id field of entity A would be available as a field in entity B. Now if entity A is holding a composite key as @id field. Which means all available field in embeddable entity needs to be available on entity B as well!
Now if entity B also holds a composite key as @id field, this will not work as secondary indexes over composite key column family is not possible.

So valid scenarios are:

1. Entity A holds @EmbeddedId and have a 1-1,m-1 relationship with non composite entity definition

hope it helps!

-Vivek

[emgsilva]

Hi Vivek,

Thank you for the insights... You reasoning sounds right, but I am not sure if I understand it fully... I have the following two questions:

1: I agree with you reasoning on the 1-M relations between two tables with compositeKeys, namely the Key of table A should be available in table B (as foreign key). Can't we use a non-key field (granting it has a unique value) to be the "JoinColumn" between table A and table B (you can see in my declaration above as "instance_id")? Is this reasoning wrong?

2: I am not sure I agree that if table A has a composite key it cannot have 1-M relations with another table (with or without composite key, from what I understand of your comment)... Can't we use the values/metadata of the EmbeddedId of table A and map them to columns of table B? I cannot see how could we do that, I suppose we cannot use "JoinColumn" or "mappedBy", these are defined by one column only... I understand Hibernate uses some annotations to do that (1-M relations) on the M-1 side, for example I have this one another MySQL DB (got this via reveng the DB):

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumns({
        @JoinColumn(name = "appInstanceID", referencedColumnName = "appInstanceID", nullable = false, insertable = false, updatable = false),
        @JoinColumn(name = "username", referencedColumnName = "username", nullable = false, insertable = false, updatable = false),
        @JoinColumn(name = "sessionID", referencedColumnName = "sessionID", nullable = false, insertable = false, updatable = false) })
public Apps getApps() {

The point of my design using composite keys is trying to identify an element on table A "quickly" (by using a compositeKey - and no secondary indexes)... then "get" all the related elements on other table(s) B (1-M relations). I could live without "composite keys" on tables B... If this design is problematic I may opt for non-composite keys and use "secondary indexes", but this would not be so performant, from my understanding.

I would like to see your points on these issues... I will be performing some further testing and come back soon.

Cheers,

Eduardo

[mevivs]

1: I agree with you reasoning on the 1-M relations between two tables with compositeKeys, namely the Key of table A should be available in table B (as foreign key). Can't we use a non-key field (granting it has a unique value) to be the "JoinColumn" between table A and table B (you can see in my declaration above as "instance_id")? Is this reasoning wrong?

I am not sure, but as per JPA specification, it has to be in foreign key relationship. That's what we follow. Also on composite key column family we cannot hold secondary indexes. So concept of holding unique key will not possible.

2: I am not sure I agree that if table A has a composite key it cannot have 1-M relations with another table (with or without composite key, from what I understand of your comment)... Can't we use the values/metadata of the EmbeddedId of table A and map them to columns of table B? I cannot see how could we do that, I suppose we cannot use "JoinColumn" or "mappedBy", these are defined by one column only... I understand Hibernate uses some annotations to do that (1-M relations) on the M-1 side, for example I have this one another MySQL DB (got this via reveng the DB):


For example, If entity A holds 1-m relationship with entity B, means @id field will be held within mapped column family for entity B. Now if entity A is holding reference composite key(using @EmbeddedId) which does not make much sense to copy all fields to entity B.  Also to share with you, the way relationship works in Kundera is it depends on secondary index feature of cassandra. Now as mentioned earlier, Cassandra does not allow secondary indexes over column families containing compositeKeys. That's why it is difficult to achieve untill cassandra enable such support over composite keys.


Now on design part:

The point of my design using composite keys is trying to identify an element on table A "quickly" (by using a compositeKey - and no secondary indexes)... then "get" all the related elements on other table(s) B (1-M relations). I could live without "composite keys" on tables B... If this design is problematic I may opt for non-composite keys and use "secondary indexes", but this would not be so performant, from my understanding.

For this i guess, what may work for you, is if you have:
1) 1-M from entity B to entity A
2) M-1 from entity A to entity B

If your composite key contains 1 partition key and more than 1 clustering key, still you won't be able to fetch your data unless you define 1st part of your clustering key(in case search required to fetch using clustering key part)!   Composite keys are useful, when you want to fetch your data based on more than key. Secondary indexes are useful when you want to enable search over a singly column. Cost of indexing might hit performance at the time of insertion. So i would suggest, in case you have major portion of your data set in place, you may prefer to update your column family to enable for secondary indexes on later stage. 

Go for composite keys, if you want remaining values under same partition key.  So i would suggest, not to hold association b/w 2 column families but to de- normalize them against same composite key, as it will also guarantee data co location, which is not possible with two column families.  


Cheers,
-Vivek

[emgsilva]

Thank you for the feedback.

I still need to think further about this... but I do not seem to be able to implement a {B (1-M) A, A (M-1) B}, this does not make sense for my DB as I want B to hold different counts/occurrences for the same A (so the same B cannot be part of Many A). Maybe I am not yet seeing clearly what you meant with your suggestion...

I clearly need {A (1-M) B}... however, if I do use a CompositeKey on A, I will then not be able to get it working with B (even if B is not compositeKey) from what I get from your feedback...

I could implement A with a compositeKey of 2 key-elements, however the ideal situation in my case would be to have 2 key-elements.

What I am thinking about to do is to create a new table to work as a kind of index (IndexTable) with a composite key (with the 3-keys I have to identify my "ServiceInstance"), then a unique ID.... I then use this ID as a single key on my ServiceInstance table, which can have 1-M mappings to the other tables... This means this IndexTable does not have any JPA relation with the ServiceInstance table, I have to "persist" the IndexTable.ID on the application. This may not be very orthodox, but I have the feeling it can be a good decision in terms of performance. What do you think? (I have the feeling this kind of design decisions are made on NoSQL DB... I am just starting, coming from RDBMs, so still learning, but need to learn fast :), and may be even thinking wrongly).

Cheers,

Eduardo

[mevivs]

I guess that should work for you. That should even meet purpose/reason behind composite key and still you can exploit you IndexTable for other functional use cases. At the same time, ServiceInstance and associated entity should meet your requirement as well. Only catch is it is dependent on IndexTable for it's row key value.

In terms of performance, if you deploying cassandra on single node, there is absolutly no problem. But on multi node deployment, there are some other factors like partition scheme and node selection strategy plays a vital role as well. You may need to build in some intelligence in order to achieve data co location with such functional aspects.

So if i am getting your requirement correctly, IndexTable will hold a unique key which is essentially row key for ServiceInstance. So while finiding data you need to fetch this unique key first from IndexTable and then perform search over ServiceInstance and associate entity, correct? I think it should be good to go. And still you could fetch a single column from IndexTable(i.e. unique id for ServiceInstance), so performance should not be such bottleneck.

Also Kundera's first level cache should help you out to reduce repititive I/O calls!

Hope it helps,

Cheers,
-Vivek

[emgsilva]

hi Vivek,

Thank you for your feedback... you completely got my reasoning and it is good to hear that it sound reasonable :)

Indeed, whenever I get multi-node the partitioning may be tricky (I am still trying to see if I get 2/3 keys compositeKey, it looks like 3 would be the best). But I do think this will indeed some optimization in the future when I move to a multi-node.

I will be implementing this solution (create the IndexTable with cqlsh --cql3, and all the others withe cassandra-cli, like the solution taken to solve in the original issue of this thread)... It should all work fine, I will "bother you" again if something pops up...

Again, thank you for the quick support and very detailed feedback! Cheers,

Eduardo

[mevivs]

Great . Do let us know, if you face any issue.
Feedback and suggestions from community and github group are very valuable for use, you may want to fill this up for your suggestions to improve further on Kundera's development:
http://www.surveymonkey.com/s/BMB9PWG

Kundera 2.3 is releasing tomorrow :) will post an update on kundera-discuss group.

-Vivek