Re: Need help with simple schema for time-series

[Noorul Islam K M]

http://kairosdb.github.io/

Regards,
Noorul

Peter Figliozzi  writes:

> I have data from many sensors as time-series:
>
>- Sensor name
>- Date
>- Time
>- value
>
> I want to query windows of both date and time.  For example, 8am - 9am from
> Aug. 1st to Aug 10th.
>
> Here's what I did:
>
> CREATE TABLE mykeyspace.mytable (
> sensorname text,
> date date,
> time time,
> data MAP,
> PRIMARY KEY (sensorname, date, time)
> );
>
>
> However, when we query this, Cassandra restricts us to an "equal" relation
> for the date, if we are to select a window of time.  So with that schema,
> I'd have to query once for each date.
>
>
> What's the right way to do this??  ("Right" defined as extracting a window
> of date and of time in one query.)
>
>
> Thank you,
>
>
> Pete

Re: Need help with simple schema for time-series

[Peter Figliozzi]

Thanks, guys, for your help.  I tried the filtering method and it works
great.
Sincerely,
Pete

On Sat, Aug 27, 2016 at 12:36 AM, Jonathan Haddad  wrote:

> Ah, i see what you're looking for.  No, my schema wouldn't work for that.
> I had read through your question a little quickly.
>
> In cassandra 3.5 support was added for more flexible ALLOW FILTERING
> statements.  Here's an example:
>
> CREATE TABLE mytable (
> sensorname text,
> date date,
> time time,
> data int,
> PRIMARY KEY (sensorname, date, time)
> );
>
> cqlsh:test> insert into mytable (sensorname, date, time, data) values
> ('test', '2016-03-03', '08:00:00', 1);
> cqlsh:test> insert into mytable (sensorname, date, time, data) values
> ('test', '2016-03-04', '13:00:00', 2);
> cqlsh:test> insert into mytable (sensorname, date, time, data) values
> ('test', '2016-03-05', '17:00:00', 3);
> cqlsh:test> insert into mytable (sensorname, date, time, data) values
> ('test', '2016-03-06', '12:00:00', 4);
> cqlsh:test> insert into mytable (sensorname, date, time, data) values
> ('test', '2016-03-07', '07:00:00', 5);
> cqlsh:test> insert into mytable (sensorname, date, time, data) values
> ('test', '2016-03-08', '15:00:00', 6);
> cqlsh:test>
> cqlsh:test>
> cqlsh:test> select * from mytable where sensorname = 'test'
> ...   and date >= '2016-03-04'
> ...   and date <= '2016-03-07'
> ...   and time >= '11:00:00'
> ...   and time <= '14:00:00'
> ...   allow filtering;
>
>  sensorname | date   | time   | data
> +++--
>test | 2016-03-04 | 13:00:00.0 |2
>test | 2016-03-06 | 12:00:00.0 |4
>
> (2 rows)
>
>
>
> On Fri, Aug 26, 2016 at 10:02 PM Peter Figliozzi 
> wrote:
>
>> I don't believe that would let me query a time of day range, over a date
>> range, would it?  For example, between 8am and 9am, August 1st through
>> August 10th.
>>
>> On Fri, Aug 26, 2016 at 11:52 PM, Jonathan Haddad 
>> wrote:
>>
>>> Use a timestamp instead of 2 separate fields and you can query on the
>>> range.
>>>
>>> CREATE TABLE mytable (
>>> sensorname text,
>>> reading_time timestamp,
>>> data MAP,
>>> PRIMARY KEY (sensorname, reading_time)
>>> );
>>>
>>>
>>>
>>> On Fri, Aug 26, 2016 at 8:17 PM Peter Figliozzi <
>>> pete.figlio...@gmail.com> wrote:
>>>
 I have data from many sensors as time-series:

- Sensor name
- Date
- Time
- value

 I want to query windows of both date and time.  For example, 8am - 9am
 from Aug. 1st to Aug 10th.

 Here's what I did:

 CREATE TABLE mykeyspace.mytable (
 sensorname text,
 date date,
 time time,
 data MAP,
 PRIMARY KEY (sensorname, date, time)
 );


 However, when we query this, Cassandra restricts us to an "equal"
 relation for the date, if we are to select a window of time.  So with that
 schema, I'd have to query once for each date.


 What's the right way to do this??  ("Right" defined as extracting a
 window of date and of time in one query.)


 Thank you,


 Pete

>>>
>>

Re: Need help with simple schema for time-series

[Jonathan Haddad]

Ah, i see what you're looking for.  No, my schema wouldn't work for that.
I had read through your question a little quickly.

In cassandra 3.5 support was added for more flexible ALLOW FILTERING
statements.  Here's an example:

CREATE TABLE mytable (
sensorname text,
date date,
time time,
data int,
PRIMARY KEY (sensorname, date, time)
);

cqlsh:test> insert into mytable (sensorname, date, time, data) values
('test', '2016-03-03', '08:00:00', 1);
cqlsh:test> insert into mytable (sensorname, date, time, data) values
('test', '2016-03-04', '13:00:00', 2);
cqlsh:test> insert into mytable (sensorname, date, time, data) values
('test', '2016-03-05', '17:00:00', 3);
cqlsh:test> insert into mytable (sensorname, date, time, data) values
('test', '2016-03-06', '12:00:00', 4);
cqlsh:test> insert into mytable (sensorname, date, time, data) values
('test', '2016-03-07', '07:00:00', 5);
cqlsh:test> insert into mytable (sensorname, date, time, data) values
('test', '2016-03-08', '15:00:00', 6);
cqlsh:test>
cqlsh:test>
cqlsh:test> select * from mytable where sensorname = 'test'
...   and date >= '2016-03-04'
...   and date <= '2016-03-07'
...   and time >= '11:00:00'
...   and time <= '14:00:00'
...   allow filtering;

 sensorname | date   | time   | data
+++--
   test | 2016-03-04 | 13:00:00.0 |2
   test | 2016-03-06 | 12:00:00.0 |4

(2 rows)



On Fri, Aug 26, 2016 at 10:02 PM Peter Figliozzi 
wrote:

> I don't believe that would let me query a time of day range, over a date
> range, would it?  For example, between 8am and 9am, August 1st through
> August 10th.
>
> On Fri, Aug 26, 2016 at 11:52 PM, Jonathan Haddad 
> wrote:
>
>> Use a timestamp instead of 2 separate fields and you can query on the
>> range.
>>
>> CREATE TABLE mytable (
>> sensorname text,
>> reading_time timestamp,
>> data MAP,
>> PRIMARY KEY (sensorname, reading_time)
>> );
>>
>>
>>
>> On Fri, Aug 26, 2016 at 8:17 PM Peter Figliozzi 
>> wrote:
>>
>>> I have data from many sensors as time-series:
>>>
>>>- Sensor name
>>>- Date
>>>- Time
>>>- value
>>>
>>> I want to query windows of both date and time.  For example, 8am - 9am
>>> from Aug. 1st to Aug 10th.
>>>
>>> Here's what I did:
>>>
>>> CREATE TABLE mykeyspace.mytable (
>>> sensorname text,
>>> date date,
>>> time time,
>>> data MAP,
>>> PRIMARY KEY (sensorname, date, time)
>>> );
>>>
>>>
>>> However, when we query this, Cassandra restricts us to an "equal"
>>> relation for the date, if we are to select a window of time.  So with that
>>> schema, I'd have to query once for each date.
>>>
>>>
>>> What's the right way to do this??  ("Right" defined as extracting a
>>> window of date and of time in one query.)
>>>
>>>
>>> Thank you,
>>>
>>>
>>> Pete
>>>
>>
>

Re: Need help with simple schema for time-series

[Jeff Jirsa]

To do 8-9am on Aug-1 through Aug-10, you’d likely need to do either multiple 
queries in parallel (fire off async), or use some clever IN logic.

 

Or, you’d need to break your table up so the first clustering key is the hour 
of the day, and then you could do this:

 

CREATE TABLE mytable (

sensorname text,

hour int,

reading_time timestamp,

data MAP<text, int>,

PRIMARY KEY ((sensorname, hour), reading_time)

);  

 

The obvious downside here is that if you need to query for more than one hour 
of a given day, you’re back to multiple queries.

 

 

From: Peter Figliozzi <pete.figlio...@gmail.com>
Reply-To: "user@cassandra.apache.org" <user@cassandra.apache.org>
Date: Friday, August 26, 2016 at 10:02 PM
To: "user@cassandra.apache.org" <user@cassandra.apache.org>
Subject: Re: Need help with simple schema for time-series

 

I don't believe that would let me query a time of day range, over a date range, 
would it?  For example, between 8am and 9am, August 1st through August 10th.

 

On Fri, Aug 26, 2016 at 11:52 PM, Jonathan Haddad <j...@jonhaddad.com> wrote:

Use a timestamp instead of 2 separate fields and you can query on the range. 

 

CREATE TABLE mytable (

sensorname text,

reading_time timestamp,

data MAP<text, int>,

PRIMARY KEY (sensorname, reading_time)

);    

 

 

 

On Fri, Aug 26, 2016 at 8:17 PM Peter Figliozzi <pete.figlio...@gmail.com> 
wrote:

I have data from many sensors as time-series:
Sensor name
Date
Time
value
I want to query windows of both date and time.  For example, 8am - 9am from 
Aug. 1st to Aug 10th.

Here's what I did:

CREATE TABLE mykeyspace.mytable (
sensorname text,
date date,
time time,
data MAP<text, int>,
PRIMARY KEY (sensorname, date, time)
);

 

However, when we query this, Cassandra restricts us to an "equal" relation for 
the date, if we are to select a window of time.  So with that schema, I'd have 
to query once for each date.

 

What's the right way to do this??  ("Right" defined as extracting a window of 
date and of time in one query.)

 

Thank you,

 

Pete

 



smime.p7s
Description: S/MIME cryptographic signature

Re: Need help with simple schema for time-series

[Peter Figliozzi]

I don't believe that would let me query a time of day range, over a date
range, would it?  For example, between 8am and 9am, August 1st through
August 10th.

On Fri, Aug 26, 2016 at 11:52 PM, Jonathan Haddad  wrote:

> Use a timestamp instead of 2 separate fields and you can query on the
> range.
>
> CREATE TABLE mytable (
> sensorname text,
> reading_time timestamp,
> data MAP,
> PRIMARY KEY (sensorname, reading_time)
> );
>
>
>
> On Fri, Aug 26, 2016 at 8:17 PM Peter Figliozzi 
> wrote:
>
>> I have data from many sensors as time-series:
>>
>>- Sensor name
>>- Date
>>- Time
>>- value
>>
>> I want to query windows of both date and time.  For example, 8am - 9am
>> from Aug. 1st to Aug 10th.
>>
>> Here's what I did:
>>
>> CREATE TABLE mykeyspace.mytable (
>> sensorname text,
>> date date,
>> time time,
>> data MAP,
>> PRIMARY KEY (sensorname, date, time)
>> );
>>
>>
>> However, when we query this, Cassandra restricts us to an "equal"
>> relation for the date, if we are to select a window of time.  So with that
>> schema, I'd have to query once for each date.
>>
>>
>> What's the right way to do this??  ("Right" defined as extracting a
>> window of date and of time in one query.)
>>
>>
>> Thank you,
>>
>>
>> Pete
>>
>

Re: Need help with simple schema for time-series

[Jonathan Haddad]

Use a timestamp instead of 2 separate fields and you can query on the range.

CREATE TABLE mytable (
sensorname text,
reading_time timestamp,
data MAP,
PRIMARY KEY (sensorname, reading_time)
);



On Fri, Aug 26, 2016 at 8:17 PM Peter Figliozzi 
wrote:

> I have data from many sensors as time-series:
>
>- Sensor name
>- Date
>- Time
>- value
>
> I want to query windows of both date and time.  For example, 8am - 9am
> from Aug. 1st to Aug 10th.
>
> Here's what I did:
>
> CREATE TABLE mykeyspace.mytable (
> sensorname text,
> date date,
> time time,
> data MAP,
> PRIMARY KEY (sensorname, date, time)
> );
>
>
> However, when we query this, Cassandra restricts us to an "equal"
> relation for the date, if we are to select a window of time.  So with that
> schema, I'd have to query once for each date.
>
>
> What's the right way to do this??  ("Right" defined as extracting a window
> of date and of time in one query.)
>
>
> Thank you,
>
>
> Pete
>