Return-Path: 
 <user-return-22644-apmail-cassandra-user-archive=cassandra.apache.org@cassandra.apache.org>
X-Original-To: apmail-cassandra-user-archive@www.apache.org
Delivered-To: apmail-cassandra-user-archive@www.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id 75E0C722D
	for <apmail-cassandra-user-archive@www.apache.org>;
 Thu,  1 Dec 2011 02:35:26 +0000 (UTC)
Received: (qmail 5023 invoked by uid 500); 1 Dec 2011 02:35:24 -0000
Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org
Received: (qmail 4971 invoked by uid 500); 1 Dec 2011 02:35:24 -0000
Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-help@cassandra.apache.org>
List-Unsubscribe: <mailto:user-unsubscribe@cassandra.apache.org>
List-Post: <mailto:user@cassandra.apache.org>
List-Id: <user.cassandra.apache.org>
Reply-To: user@cassandra.apache.org
Delivered-To: mailing list user@cassandra.apache.org
Received: (qmail 4963 invoked by uid 99); 1 Dec 2011 02:35:23 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 01 Dec 2011 02:35:23 +0000
X-ASF-Spam-Status: No, hits=2.2 required=5.0
	tests=HTML_MESSAGE,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: local policy)
Received: from [64.13.192.36] (HELO cl27.gs01.gridserver.com) (64.13.192.36)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 01 Dec 2011 02:35:17 +0000
Received: from c-24-7-82-252.hsd1.ca.comcast.net ([24.7.82.252]:50600
 helo=[192.168.26.105])
	by cl27.gs01.gridserver.com with esmtpsa (TLS-1.0:DHE_RSA_AES_256_CBC_SHA:32)
	(Exim 4.69)
	(envelope-from <deno@syncopated.net>)
	id 1RVwUD-00084n-9M
	for user@cassandra.apache.org; Wed, 30 Nov 2011 18:34:57 -0800
Message-ID: <4ED6E7AE.50702@syncopated.net>
Date: Wed, 30 Nov 2011 18:34:22 -0800
From: Deno Vichas <deno@syncopated.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
To: user@cassandra.apache.org
Subject: Re: data modeling question
References: <4ED698C0.60108@syncopated.net>
 <CAKc7CJTmgjGqwPShXxNCogHDbfGLs2_fdGd70FE1vtLHKPxRgQ@mail.gmail.com>
 <4ED6A40A.2010008@syncopated.net>
 <CAKc7CJTod8ttxFBSWV2a_UBboq8L9PA-TAbZOZ20-E4z+SgOQQ@mail.gmail.com>
In-Reply-To: 
 <CAKc7CJTod8ttxFBSWV2a_UBboq8L9PA-TAbZOZ20-E4z+SgOQQ@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------020609020503050603000301"
X-Authenticated-User: 32415 deno@syncopated.net
X-Spam-Level: 
X-Old-Spam-Status: "score=-0.4 tests=ALL_TRUSTED, HTML_MESSAGE version=3.1.7"

This is a multi-part message in MIME format.
--------------020609020503050603000301
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

here's what i ended up, this seems to work for me.


     @Test
     public void readAndWriteSettingTTL() throws Exception {
         int ttl = 2;
         String columnFamily = "Quote";
         Set<String> symbols = new HashSet<String>(){{
                                                 add("appl");
                                                 add("goog");
                                                 add("ibm");
                                                 add("csco");
                                             }};

         UUID timeUUID = TimeUUIDUtils.getUniqueTimeUUIDinMillis();

         Mutator<String> mutator = HFactory.createMutator(_keyspace, 
_stringSerializer);
         for(String symbol : symbols) 
addInsertionToMutator(columnFamily, timeUUID, mutator, symbol, ttl);
         mutator.execute();

         RangeSlicesQuery<String, UUID, String> rangeSlicesQuery = 
HFactory.createRangeSlicesQuery(_keyspace, _stringSerializer, 
_uuidSerializer, _stringSerializer);
         rangeSlicesQuery.setColumnFamily(columnFamily);
         rangeSlicesQuery.setKeys("", "");
         rangeSlicesQuery.setRange(null, null, false, 1);
         QueryResult<OrderedRows<String, UUID, String>> result = 
rangeSlicesQuery.execute();

         UUID uuid = 
result.get().getList().get(0).getColumnSlice().getColumns().get(0).getName();

         Assert.assertEquals("UUID should be the same", timeUUID, uuid);
         Assert.assertEquals("We should have 4 records", 4, 
result.get().getList().size());

         Thread.sleep(5000); // wait till TTL hits to make sure keys are 
getting flushed.

         QueryResult<OrderedRows<String, UUID, String>> result2 = 
rangeSlicesQuery.execute();
         for(Row<String, UUID, String> row : result2.get().getList()) {
             Assert.assertEquals("We should have no records", 0, 
row.getColumnSlice().getColumns().size());
         }

     }

     private void addInsertionToMutator(String columnFamily, UUID 
columnName, Mutator<String> mutator, String symbol, int ttl) {
         mutator.addInsertion(symbol, columnFamily, 
HFactory.createColumn(columnName, "", ttl, _uuidSerializer, 
_stringSerializer));
     }


On 11/30/2011 1:56 PM, David McNelis wrote:
> You wouldn't query for all the keys that have a column name x exactly. 
>  Instead what you would do is for sector x grab your list of symbols 
> S.  Then you would get the last column for each of those symbols 
> (which you do in different ways depending on the API), and then test 
> if that date is within your threshold.  If not, it goes into your list 
> of symbols to fetch.
>
> Alternatively, you could iterate over the symbols grabbing data where 
> the date is between range A and B, if you get an empty set / no 
> columns returned, then you need to re-pull for that symbol.  Does that 
> make sense?
>
> Either way you end up hitting on each of the individual symbols. 
>  Maybe someone else has a better idea of how to structure the data for 
> that particular use case.
>
> On Wed, Nov 30, 2011 at 3:45 PM, Deno Vichas <deno@syncopated.net 
> <mailto:deno@syncopated.net>> wrote:
>
>     with the quote CF below how would one query for all keys that have
>     a column name value that have a timeuuid of later than x minutes?
>      i need to be able to find all symbols that have not been fetch in
>     x minutes by sector.  i know i get list of symbol by sector from
>     my sector CF.
>
>     thanks,
>     deno
>
>
>     On 11/30/2011 1:07 PM, David McNelis wrote:
>
>
>         Then I would have a column family for quotes where I have the
>         key as the symbol, the column name as the timestamp, the value
>         as the quote:
>
>         quote : {
>            key: symbol
>            column names:  timeuuid
>            column values:  quote at that time for that symbol
>         }
>
>
>
>
>
>
> -- 
> *David McNelis*
> Lead Software Engineer
> Agentis Energy
> www.agentisenergy.com <http://www.agentisenergy.com>
> c: 219.384.5143
>
> /A Smart Grid technology company focused on helping consumers of 
> energy control an often under-managed resource./
>
>


--------------020609020503050603000301
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    here's what i ended up, this seems to work for me.<br>
    <br>
    <br>
    &nbsp;&nbsp;&nbsp; @Test<br>
    &nbsp;&nbsp;&nbsp; public void readAndWriteSettingTTL() throws Exception {<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; int ttl = 2;<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; String columnFamily = "Quote";<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set&lt;String&gt; symbols = new HashSet&lt;String&gt;(){{<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; add("appl");<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; add("goog");<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; add("ibm");<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; add("csco");<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }};<br>
    <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UUID timeUUID = TimeUUIDUtils.getUniqueTimeUUIDinMillis();<br>
    <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Mutator&lt;String&gt; mutator =
    HFactory.createMutator(_keyspace, _stringSerializer);<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for(String symbol : symbols)
    addInsertionToMutator(columnFamily, timeUUID, mutator, symbol, ttl);<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mutator.execute();<br>
    <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RangeSlicesQuery&lt;String, UUID, String&gt;
    rangeSlicesQuery = HFactory.createRangeSlicesQuery(_keyspace,
    _stringSerializer, _uuidSerializer, _stringSerializer);<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rangeSlicesQuery.setColumnFamily(columnFamily);<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rangeSlicesQuery.setKeys("", "");<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rangeSlicesQuery.setRange(null, null, false, 1);<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QueryResult&lt;OrderedRows&lt;String, UUID, String&gt;&gt;
    result = rangeSlicesQuery.execute();<br>
    <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UUID uuid =
result.get().getList().get(0).getColumnSlice().getColumns().get(0).getName();<br>
    <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Assert.assertEquals("UUID should be the same", timeUUID,
    uuid);<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Assert.assertEquals("We should have 4 records", 4,
    result.get().getList().size());<br>
    <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Thread.sleep(5000); // wait till TTL hits to make sure keys
    are getting flushed.<br>
    <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QueryResult&lt;OrderedRows&lt;String, UUID, String&gt;&gt;
    result2 = rangeSlicesQuery.execute();<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for(Row&lt;String, UUID, String&gt; row :
    result2.get().getList()) {<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Assert.assertEquals("We should have no records", 0,
    row.getColumnSlice().getColumns().size());<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>
    <br>
    &nbsp;&nbsp;&nbsp; }<br>
    <br>
    &nbsp;&nbsp;&nbsp; private void addInsertionToMutator(String columnFamily, UUID
    columnName, Mutator&lt;String&gt; mutator, String symbol, int ttl) {<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mutator.addInsertion(symbol, columnFamily,
    HFactory.createColumn(columnName, "", ttl, _uuidSerializer,
    _stringSerializer));<br>
    &nbsp;&nbsp;&nbsp; }<br>
    <br>
    <br>
    On 11/30/2011 1:56 PM, David McNelis wrote:
    <blockquote
cite="mid:CAKc7CJTod8ttxFBSWV2a_UBboq8L9PA-TAbZOZ20-E4z+SgOQQ@mail.gmail.com"
      type="cite">You wouldn't query for all the keys that have a column
      name x exactly. &nbsp;Instead what you would do is for sector x grab
      your list of symbols S. &nbsp;Then you would get the last column for
      each of those symbols (which you do in different ways depending on
      the API), and then test if that date is within your threshold. &nbsp;If
      not, it goes into your list of symbols to fetch. &nbsp;
      <div>
        <br>
      </div>
      <div>Alternatively, you could iterate over the symbols grabbing
        data where the date is between range A and B, if you get an
        empty set / no columns returned, then you need to re-pull for
        that symbol. &nbsp;Does that make sense?
        <div>
          <br>
        </div>
        <div>Either way you end up hitting on each of the individual
          symbols. &nbsp;Maybe someone else has a better idea of how to
          structure the data for that particular use case.</div>
        <div><br>
        </div>
        <div>
          <div class="gmail_quote">
            On Wed, Nov 30, 2011 at 3:45 PM, Deno Vichas <span
              dir="ltr">&lt;<a moz-do-not-send="true"
                href="mailto:deno@syncopated.net">deno@syncopated.net</a>&gt;</span>
            wrote:<br>
            <blockquote class="gmail_quote" style="margin:0 0 0
              .8ex;border-left:1px #ccc solid;padding-left:1ex;">
              with the quote CF below how would one query for all keys
              that have a column name value that have a timeuuid of
              later than x minutes? &nbsp;i need to be able to find all
              symbols that have not been fetch in x minutes by sector.
              &nbsp;i know i get list of symbol by sector from my sector CF.<br>
              <br>
              thanks,<br>
              <font color="#888888">
                deno</font>
              <div>
                <div class="h5"><br>
                  <br>
                  On 11/30/2011 1:07 PM, David McNelis wrote:<br>
                  <blockquote class="gmail_quote" style="margin:0 0 0
                    .8ex;border-left:1px #ccc solid;padding-left:1ex">
                    <br>
                    Then I would have a column family for quotes where I
                    have the key as the symbol, the column name as the
                    timestamp, the value as the quote:<br>
                    <br>
                    quote : {<br>
                    &nbsp; &nbsp;key: symbol<br>
                    &nbsp; &nbsp;column names: &nbsp;timeuuid<br>
                    &nbsp; &nbsp;column values: &nbsp;quote at that time for that
                    symbol<br>
                    }<br>
                    <br>
                    <br>
                  </blockquote>
                  <br>
                </div>
              </div>
            </blockquote>
          </div>
          <br>
          <br clear="all">
          <div><br>
          </div>
          -- <br>
          <b>David McNelis</b>
          <div><font color="#666666" size="1">Lead Software Engineer</font></div>
          <div><font color="#666666" size="1">Agentis Energy</font></div>
          <div><font color="#666666" size="1"><a moz-do-not-send="true"
                href="http://www.agentisenergy.com" target="_blank">www.agentisenergy.com</a></font></div>
          <div><span style="font-size:x-small;color:rgb(102, 102, 102)">c:
              219.384.5143</span></div>
          <div>
            <span style="font-size:x-small;color:rgb(102, 102, 102)"><br>
            </span></div>
          <div><span style="font-family:'Helvetica Neue', Helvetica,
              Arial, sans-serif;line-height:18px"><font color="#666666"
                size="1"><i>A Smart Grid technology company focused on
                  helping consumers of energy control an often
                  under-managed resource.</i></font></span></div>
          <div><br>
          </div>
          <br>
        </div>
      </div>
    </blockquote>
    <br>
  </body>
</html>

--------------020609020503050603000301--