Skip to main content

CREATE CQ (query)

CREATE CQ name  
INSERT INTO {
  <output stream name>  | 
  <WActionStore name> [ ( <field name>, ... ) ] 
}
SELECT [DISTINCT] { { <expression or field name> [ AS <output stream field name> ], ... } 
[ ISTREAM ]
FROM {
  <input stream name> | 
  <cache name> |
  <window name> | 
  <WActionStore name>, ... |
  ITERATOR ( <nested collection name>.<member name> ) 
  }
[ { INNER | CROSS | LEFT | LEFT OUTER | RIGHT | RIGHT OUTER | FULL | FULL OUTER } JOIN ]
[ [ JUMPING <integer> { SECOND | MINUTE | HOUR | DAY } ] ]
[ ON { <expression> } ]
[ WHERE { <expression> } ]
[ GROUP BY { field name } ]
[ HAVING { <expression> } ]
[ ORDER BY { <expression> } [ ASC | DESC ] ]
[ LIMIT { <expression> } ]
[ SAMPLE BY <field names>,... [SELECTIVITY <#.#> | MAXLIMIT <number>] ]
[ LINK SOURCE EVENT ] 
[ EXCACHEBUFFERSIZE <number of events> ];

See Operators and Functions for more information about writing expressions.

  • INSERT INTO: When a CQ's INSERT INTO clause specifies a stream that does not exit, the stream and its type will be created automatically based on the SELECT statement. For an example, see Parsing the data field of WAEvent. This clause may include any of the options described in CREATE STREAM.

  • DISTINCT: When a CQ includes the DISTINCT option, at least one of the components in the FROM clause must be a cache or window.

  • SELECT timeStamp: When selecting from the output stream of a source, you may use SELECT timeStamp to get the system time in milliseconds (as a long) when the source processed the event. The timeStamp field exists only in the source's output stream and is dropped by any window, CQ, or target using that stream as a source.

  • ISTREAM: By default, a CQ will update calculated values when events are added to or removed from its input window. If you specify the ISTREAM option, the CQ will ignore removed events and update calculated values only when new events are added to the window. This option has no effect on the window itself, which will emit the same events regardless.

  • SELECT ... FROM: When a CQ's FROM clause includes multiple components, at least one must be a cache or window. See Joins.

  • SELECT ... FROM <WActionStore name>: supported only when the WActionStore is persisted. By default, this will run once, when the application is deployed. Add the [JUMPING ...] clause to re-run the query periodically. (Note that the square brackets are required.) For example, [JUMPING 5 MINUTE] will run the query every five minutes, each time (including the first) returning the most recent five minutes of data.

  • ITERATOR: see Using ITERATOR.

  • INNER JOIN: When a CQ includes the INNER JOIN option, two and only two components must be specified in the FROM clause.

  • GROUP BY: See Aggregate functions.

  • SAMPLE BY: When a CQ's FROM clause includes only a single WActionStore or jumping window, you may use the SAMPLE BY clause to reduce the number of events in the CQ's output. One use for this is to reduce the number of events to avoid overloading a dashboard (see Defining dashboard queries). For WActionStores, the CQ must include an ORDERY BY clause to order the events by time.

    • <field name>,... specifies one or more fields from the WActionStore or window. The data will be sampled so as to preserve roughly the same distribution of those fields' values as in the total data set. The field(s) must be of type double, float, integer, long, or short.

    • SELECTIVITY sets the sample size as a digital fraction between 0 and 1. For example, SELECTIVITY 0.05 would select approximately 5% of the events from the source.

    • MAXLIMIT sets the sample size as a maximum number of events. For example, MAXLIMIT 100 would select 100 events every time the CQ is run.

    • You may not specify both SELECTIVITY and MAXLIMIT in the same SELECT statement. If you specify neither, the default is SELECTIVITY 0.01.

    • When selecting from a very large WActionStore, you may want to reduce the amount of data returned before sampling. For example, SELECT DateTime, Temp FROM TemperatureWS ORDER BY DateTime DESC LIMIT 100000 SAMPLE BY Temp MAXLIMIT 500. would return a sample of 500 of the 100,000 most recent events.

    • The smaller the SELECTIVITY or MAXLIMIT value, the less representative of the full data set the sample will be. It may be helpful to experiment with various values.

  • LINK SOURCE EVENT: When a CQ's INSERT INTO clause specifies a WActionStore, the optional LINK SOURCE EVENT clause makes the details of the events in the FROM clause available in the WActionStore.

  • EXCACHEBUFFERSIZE: See CREATE EXTERNAL CACHE.CREATE EXTERNAL CACHE

  • NESTING: Queries may be nested by enclosing the subquery in parentheses: SELECT ... FROM (SELECT ... FROM ...) ....

See Continuous query (CQ) and Intermediate TQL programming: common patterns for some examples of common query types.