This discussion is archived
9 Replies Latest reply: Feb 15, 2013 12:44 AM by chpruvos RSS

The best way to store my value

chpruvos Newbie
Currently Being Moderated
Hi all,

I am a beginner with Oracle NoSQL so I explain my use case :

I have an object called Position coming from Coherence.

Position {
int id
string symbol
int amount
double price
}

I wonder what is the best way to store this object knowing that I want to do some queries like "select position from xxx where symbol = "ORCL", "select symbol, count(symbol) from xxx group by symbol" (I know we cannot use SQL it is just to give you easily an idea of my need)

1) Like that

majorComponents = new ArrayList<String>();
majorComponents.add("position");
majorComponents.add(String.valueOf(position.getId()));     
          
myKey = Key.createKey(majorComponents);
          
myValue = Value.createValue(SerializationUtils.serialize(position));
store.put(myKey, myValue);

2) Or like that

majorComponents = new ArrayList<String>();
majorComponents.add("position");
majorComponents.add(String.valueOf(position.getId()));     
          
minorComponents.add("symbol");

myKey = createKey(majorComponents, minorComponents);
          
myValue = Value.createValue(SerializationUtils.serialize(position.getSymbol));
store.put(myKey, myValue);

etc...with amount and price

Thank you for all...
  • 1. Re: The best way to store my value
    chpruvos Newbie
    Currently Being Moderated
    Amount is a int and price is a double. Should I use Integer and Double for storing like the code below ?

    position = Utils.makeNewPosition(i);
                   
                   majorComponents = new ArrayList<String>();
                   majorComponents.add("position");
                   majorComponents.add(String.valueOf(position.getId()));     
                   
                   minorComponents = new ArrayList<String>();
                   minorComponents.add("symbol");
                   myKey = Key.createKey(majorComponents,minorComponents);
                   myValue = Value.createValue(position.getSymbol().getBytes());
                   store.put(myKey, myValue);
                   
                   minorComponents = new ArrayList<String>();
                   minorComponents.add("amount");
                   myKey = Key.createKey(majorComponents,minorComponents);
                   myValue = Value.createValue(SerializationUtils.serialize(new Integer(position.getAmount())));
                   store.put(myKey, myValue);
                   
                   minorComponents = new ArrayList<String>();
                   minorComponents.add("price");
                   myKey = Key.createKey(majorComponents,minorComponents);
                   myValue = Value.createValue(SerializationUtils.serialize(new Double(position.getPrice())));
                   store.put(myKey, myValue);

    And please explain the code to get an Integer or a Double from a Value v (Value v = vv.getValue();) ..

    Thank you for all

    Christophe.

    Edited by: chpruvos on Feb 12, 2013 8:09 AM
  • 2. Re: The best way to store my value
    chpruvos Newbie
    Currently Being Moderated
    So I answer to my question N 2...because I found the solution...

    For the int we can use SerializationUtils

    SerializationUtils.serialize(new Integer(position.getAmount()))

    and

    (Integer)SerializationUtils.deserialize(v.getValue())

    The same for a double...
  • 3. Re: The best way to store my value
    chpruvos Newbie
    Currently Being Moderated
    So I stored like that

    position = Utils.makeNewPosition(i);
                   
                   majorComponents = new ArrayList<String>();
                   majorComponents.add("position");
                   majorComponents.add(String.valueOf(position.getId()));     
                   
                   minorComponents = new ArrayList<String>();
                   minorComponents.add("symbol");
                   myKey = Key.createKey(majorComponents,minorComponents);
                   myValue = Value.createValue(position.getSymbol().getBytes());
                   store.put(myKey, myValue);
                   
                   minorComponents = new ArrayList<String>();
                   minorComponents.add("amount");
                   myKey = Key.createKey(majorComponents,minorComponents);
                   myValue = Value.createValue(SerializationUtils.serialize(new Integer(position.getAmount())));
                   store.put(myKey, myValue);
                   
                   minorComponents = new ArrayList<String>();
                   minorComponents.add("price");
                   myKey = Key.createKey(majorComponents,minorComponents);
                   myValue = Value.createValue(SerializationUtils.serialize(new Double(position.getPrice())));
                   store.put(myKey, myValue);

    I can get a position with the code below

    majorComponents = new ArrayList<String>();
                   majorComponents.add("position");
                   majorComponents.add(String.valueOf(i));     
                   
                   myKey = Key.createKey(majorComponents);
              
                   SortedMap<Key, ValueVersion> myRecords = null;
                   
                   try {
                        myRecords = store.multiGet(myKey, null, null);
                   } catch (ConsistencyException ce) {
                        ce.printStackTrace();
                   } catch (RequestTimeoutException re) {
                        re.printStackTrace();
                   }
                   
                   for (Map.Entry<Key, ValueVersion> entry : myRecords.entrySet()) {
                        Key key = entry.getKey();
                        ValueVersion vv = entry.getValue();
                        Value v = vv.getValue();
                        System.out.println("Key : " + key.getMinorPath().get(0));
                        if (key.getMinorPath().get(0).equals("symbol")) System.out.println("Symbol : " + new String(v.getValue()));
                        else if (key.getMinorPath().get(0).equals("amount")) System.out.println("Amount : " + (Integer)SerializationUtils.deserialize(v.getValue()));
                        else if (key.getMinorPath().get(0).equals("price")) System.out.println("Price : " + (Double)SerializationUtils.deserialize(v.getValue()));
                        else System.out.println("Value : " + v.getValue());
                   }     

    But I do not find how to get all the positions that have a symbol = "ORCL" ???

    Please help

    Edited by: chpruvos on Feb 13, 2013 8:24 AM
  • 4. Re: The best way to store my value
    gmfeinberg Journeyer
    Currently Being Moderated
    Hi,

    I'm a member of the Oracle NoSQL Database development team. Your situation is a good example for thinking about how to model data in a key/value store and NoSQL Database. I'm going to use the URI syntax for keys, e.g. /major/-/minor. In addition I'll use a $ to indicate a variable component in a key.

    If you think about the problem in relational terms what you want is a "table" of Positions, with a primary key of id, and indexes on the fields, where a Position is:

    Position {
    int id
    string symbol
    int amount
    double price
    }

    I'm assuming that the Position id is unique. When turning this into a key/value entry your "primary keys" turn into components of the key, as you've done, so your key for a single position is:
    /Positions/$id
    It's value is a serialized version of:
    {id, symbol, amount, price}

    This is essentially what you came up with as well. You can factor out the id from the serialized value since it's redundant (or not, up to you, but I would)

    So there's your "table" of Positions. Now, what about indexes? An index just maps a value to a primary key, so in this case it might map the value of symbol to a corresponding id, or ids. So consider this key:
    /Symbols/$symbol/-/$id
    This record is key-only and has no value as it's not necessary.

    This "index" on Symbols gives you a unique key for the symbol for each Position. Another interesting feature of this is that you can iterate all symbols by getting an iterator on "/Symbols". You can also efficiently look up all Positions with the symbol "ORCL" with a multiGetKeys() on key "/Symbols/ORCL". You'd iterate the resulting keys and use the last minor key component to look up the Position in the Positions "table."

    You can create similar "indexes" for the other fields.

    As for serialization of the initial Position Oracle NoSQL Database is recommending using Avro-based schemas and serialization. It involves creating a simple schema and some extra work to serialize relative to SerializationUtils but it will be more forward-compatible with future work in the product.

    Some other general modeling rules that are important to remember:
    1. Sort order of keys. In order to benefit from ordering of keys any fields you use in keys must be fixed-length and collate in a way you want. For example, let's say you have an index on count:
    /Counts/$count/-/$id
    In order to get the Positions in "id" order the ids need to be something like "000123" (pick a length large enough for your largest id). This is true for $count as well, which should be padded out. In general it is true for any non-string (e.g. double).

    You could choose to not used fixed-length string values for numbers but you'd not get the benefits of implicitly sorted keys and giving up much of the power of the system. Recall that there are range operations for keys that allow what amounts to range queries on keys. These require a well-defined sort order. Note that range operations are most efficient using complete major keys. This is because different major keys can reside on different partitions. It is possible to iterate over key ranges using partial major keys but it can take longer, is not transactional, and results are not sorted.

    2. All keys with the same major key will reside on the same partition, which means:
    a. operations to get them are transactional and efficient (they only go to one server)
    b. results will be sorted in key order

    3. Because the "indexes" described above do not share a major key with the Position "table," updates to both are not atomic. This means you need to consider what order you use for these operations and how to deal with errors. For example, let's say you update indexes first, and the Positions table second. A failure between the two in your client means that you'll have index entries pointing to nowhere. If you flip this around you might have a Position with no indexes. You need to choose which case you'll more easily handle and do that.

    If you'd like to see example code that does some of this, including Avro serialization, with your model, let me know.

    Regards,
    George

    Edited by: gmfeinberg on Feb 13, 2013 5:21 PM. Clarified range operation semantics
  • 5. Re: The best way to store my value
    chpruvos Newbie
    Currently Being Moderated
    Thanks it was very interesting so just a few question...

    - Do you confirm that we can do queries (a kind of..for exemple key range) just using key major components and not key minor components ?

    - So For me no need to store attributes of a position not together. I can store key = Positions/$id and Value = Position (using Avro Serialization) ?

    - If I want to be able to find all positions having symbol = "ORCL" then before when storing a position I need to update an index => key = /Symbols/$symbol/-/$id , Value = null. In this case I need to be carefull because if a put in Positions is OK but a put in the index is NOK then it could be a problem ?

    - To be more efficient if sorted key is needed then I should avoid to store an id like "10" but I should prefer an id like "000010" a fixed-length id ?

    And to finish..

    - What's about an index that will be manage automatically by Oracle NoSQL ? is it planned for a future release ?

    Again Thank you for your answer ...
  • 6. Re: The best way to store my value
    gmfeinberg Journeyer
    Currently Being Moderated
    chpruvos wrote:
    Thanks it was very interesting so just a few question...

    - Do you confirm that we can do queries (a kind of..for exemple key range) just using key major components and not key minor components ?
    Yes, you can using a variant of KVStore.storeIterator(). The results are not necessarily sorted and they are not transactional. It is still good practice to be sure to use keys that sort well.

    >
    - So For me no need to store attributes of a position not together. I can store key = Positions/$id and Value = Position (using Avro Serialization) ?
    It's not clear if this is a question or not. I'll just explain more things. The approach you seemed to be taking in your posts implied that you'd store a position as:
    /position/$id/-/symbol -> symbol
    /position/$id/-/amount -> amount
    /position/$id/-/price -> price

    While this works it doesn't give you the index behavior you want (find all Positions with symbol "ORCL"). It's also less efficient in terms of network round trips and storage space than serializing the position as one record. Storing fields as individual records like this is not mutually exclusive with respect to using the index approach I mentioned, but I don't see an advantage over using Avro or other serialization for the entire record. Then again, I don't know what you do with the results of your "find me all positions with symbol "ORCL"."

    >
    - If I want to be able to find all positions having symbol = "ORCL" then before when storing a position I need to update an index => key = /Symbols/$symbol/-/$id , Value = null. In this case I need to be carefull because if a put in Positions is OK but a put in the index is NOK then it could be a problem ?
    Correct. I personally would probably insert into indexes first, since that issue seems easier to handle but for a given application you may want the opposite behavior.

    Note that you can't directly do store.put(key, null) or you'll get an exception. You have to create an empty value.

    >
    - To be more efficient if sorted key is needed then I should avoid to store an id like "10" but I should prefer an id like "000010" a fixed-length id ?
    Yes. In this case it'd make sense to create functions to easily map integers into fixed-length strings suitable for keys.

    >
    And to finish..

    - What's about an index that will be manage automatically by Oracle NoSQL ? is it planned for a future release ?
    That sort of thing is on the radar but I can't speak specifically about futures.

    >
    Again Thank you for your answer ...
    No problem.

    George

    Edited by: gmfeinberg on Feb 13, 2013 5:29 PM. Clarified use of storeIterator()
  • 7. Re: The best way to store my value
    gja Newbie
    Currently Being Moderated
    Your reply was not only very interesting but very inspiring as well

    Thanks a lot!
  • 8. Re: The best way to store my value
    gmfeinberg Journeyer
    Currently Being Moderated
    Hi,

    I'm editing my previous responses to be more correct. You can use storeIterator() to iterate over partial major keys but you do not get predictable order because it goes to all shards.

    Regards,

    George
  • 9. Re: The best way to store my value
    chpruvos Newbie
    Currently Being Moderated
    Ok I use Avro..my code runs well thanks...but I will ask another question on another thread...:o)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points