stupid sql query error :((

[AmaliaP]

Hi

more newbie questions:

I have an input stream (columns: id, idL, idE, value) and I want to
make a running query that stores all data in the last 10 minutes and
counts the events with the same idL and idE. I run into some examples
from the SC site..and I finally got this query:

select idL, idE, event_counter from tblInputStream
(partition by store last 10 seconds with count(id) as
event_counter)
as tblRunQuery
where tblRunQuery.$row_status is not dead

I know this is not a good query..but can you help me (i don't have
group by anywhere and I don't know where to put it)?

Also, in the output stream I want to have idL,idE,event_counter and a
few fields from other not in-memory tables from the database..Is this
possible? I will try it my self but now I receive in the output
session the error "unknown field idL" and I guess I receive it because
of the malformed sql in the running query...

ty for help in advance

amalia

[Ashwin]

Use the Partition clause:

select idLocation, idEventType, counter
from tbl_facts_stream
(partition by idLocation, idEventType store last 10 seconds with
count(idEventType) as counter)
as tbl_facts_p
where tbl_facts_p.$row_status is new;

[Ashwin]

The "unknown field idL" error is because of this - http://
groups.google.com/group/streamcruncher/t/f74ef614de2920a2?hl=en-GB.
You've already asked this before.

On 1 Feb, 18:12, "AmaliaP" <amalia.pirvane...@gmail.com> wrote:

[AmaliaP]

Here are the input stream and the running query I built.

//columns of the generated events
String[] columnNames = new String[]{
"id",
"idEventType",
"idLocation",
"value",
"obs",
"status",
"timestamp"};
String[] columnTypes = new String[]{
"bigint not null", //id
"bigint not null", //idEventType
"bigint not null", //idLocation
"varchar(255)", //value
"varchar(255)", //observation
"integer", //status
"timestamp"}; //timestamp

//thread for input events
InputEventsStream inputStream = new InputEventsStream(
cruncher, columnNames, columnTypes, "tbl_facts_stream",
0, 6, "tbl_facts_stream_index");

String rql = "select idLocation, idEventType, counter" +
" from tbl_facts_stream " +
" (partition by idLocation, idEventType store last 10 seconds " +
" with count(id) as counter)" +
" as tbl_facts_p " +
" where tbl_facts_p.$row_status is new;";

columnNames = new String[] {
"idLocation",
"idEventType",
"counter"};
columnTypes = new String[] {
"bigint",
"bigint",
"integer"};

TableFQN tableFQN = new TableFQN("tbl_facts_partitioned");

ParserParameters parameters = new ParserParameters();
parameters.setQuery(rql);
parameters.setQueryName(queryName);
parameters.setResultColumnNames(columnNames);
parameters.setResultColumnTypes(columnTypes);
parameters.setResultTableFQN(tableFQN);

ParsedQuery parsedQuery = cruncher.parseQuery(parameters);

QueryConfig config = parsedQuery.getQueryConfig();
config.setQuerySchedulePolicy(new
QueryConfig.QuerySchedulePolicyValue(
QuerySchedulePolicy.ATLEAST_OR_SOONER, Integer.MAX_VALUE));

//cruncher.unregisterQuery(queryName);
cruncher.registerQuery(parsedQuery);

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
and here the result:

SEVERE: An error occurred while executing the Query:
tbl_facts_partitioned_rql
streamcruncher.A.O0OO: Error(s) occurred while executing the Jobs. [1
Exceptions]
at streamcruncher.A.A.Ó00000(SimpleJobFixedBatchExecutor.java:108)
at streamcruncher.D.OOoO.while(SchedulableQuery.java:246)
at streamcruncher.D.B.new(QueryRunnerJob.java:73)
at streamcruncher.D.B.run(QueryRunnerJob.java:55)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown
Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
-- Exceptions --
[tbl_facts_p]
java.lang.NullPointerException
at streamcruncher.C.B.super.C.o00000(AbstractTablePartitioner.java:
222)
at streamcruncher.D.OOoO$_Oo.Ò00000(SchedulableQuery.java:435)
at streamcruncher.A.N.run(SimpleJob.java:72)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown
Source)
at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown
Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

[Ashwin]

Hmm.. can you pls give me the DB name/version that you are using. I'll
see if I can replicate it.

Ashwin.