How to export timeseries using data queries

Context

You have a dashboard that contains a spark column and you wish to export that data so it can be used by another system.

The following image shows a simple dashboard displaying the value of an attribute over the course of the current hour using a Spark column.

In order to dump the data displayed in this type of graph, you need to use a Data Query in the Data Integration section.

Create the Query

In the data integration menu, open the Queries tab and create a new query:

  • Name: exportInProcessCount
  • Dimensions: Process
  • Inputs: inProcessCount

By default, the time configuration for the input will be At reference time which will only return one value. In your case, you want to query the data across an interval to retrieve more than one data points. To do this, you must change the time configuration of the input.

Time configuration

  1. Click At reference time hyperlink. The time configuration window is displayed.
  2. The Dimension to correlate with drop-down does not contain the required temporal dimension yet. From the drop-down select the New temporal dimension option. A pop-up is displayed.
  3. For this example, configure a Current 1 hour dimension to match what is configured in the dashboard. Once you've created the dimension, it becomes selected by default.

 

Tip: Because the input is based on a temporal dimension the time configuration will give us two big choices: Either retrieve more than one value or a single one.  When retrieving multiple values you could choose to select all the values that end during the current hour whereas for a single data point you would decide to take either at the start or the end of the interval.

For this example, retrieve all the values that intersect the current hour. Then, click Done

Retrieve the Data

Create the Route

Now that the query has been configured it must be used in a route. The following route is executed once when it starts and simply executes the query and logs the values

 

<?xml version="1.0" encoding="UTF-8"?>
<routes xmlns="http://camel.apache.org/schema/spring" xmlns:u="http://www.systar.com/aluminium/camel-util">
    <route>
        <from uri="timer://once?repeatCount=1"/>
        <setProperty propertyName="tnd-query.parameters">
            <u:map-create> 
                <u:map-entry key="ReferenceTime">
					<!-- set a specific time for the execution time of the query to match what we saw in the dashboard -->
                    <constant>2015-01-13T13:49:00+01:00</constant>
                </u:map-entry>
            </u:map-create> 
        </setProperty>
		<!-- execute the query -->
        <to uri="tnd-query:exportInProcessCount"/>
        <setBody>
			<!-- Because there is only one instance of Process we can go get the value of inProcessCount directly -->
            <simple>${body.rows[0][inProcessCount]}</simple>
        </setBody>
        <to uri="direct:inProcessCountArray"/>
    </route>
    <route>
        <from uri="direct:inProcessCountArray"/>
        <log loggingLevel="INFO" message="New values array: ${body.values}"/>
        <split>
            <simple>${body.values}</simple>
            <to uri="direct:value"/>
        </split>
    </route>
    <route>
        <from uri="direct:value"/>
        <log loggingLevel="INFO" message="value: ${body.value}"/>        
    </route>
</routes>

Parse the result

The complete description of the result can be found in the page Inside a Query Result but the following paragraph will offer a quick description of the result before looking specifically at the data returned by the query to see if it matches what was in the dashboard.

Structure

A query returns an object of type TabularResult which itself contains rows. Because our query contained an input called inProcessCount each row will have a property of that name which will contain the values from the current hour stored in an array. So the basic format of our result will look like this : 

TabularResult{
				rows=[{
						inProcessCount={
										values=[
											{data point 1},
											{data point 2},
											...
										]
						}
					}]
}

Because inProcessCount was queried over an interval the data point array is made available through an accessor named values. Even though it is documented in Inside a Query Result, it is not obvious when looking at a dump of the query result.

 

Content

In our dashboard we displayed the tooltips of 4 of the 50 data points used in the Spark column. The following table shows the content taken from the query result for each of these data points.

Data point
{value=31, validTime=2015-01-13T13:06:00.000/2015-01-13T13:07:00.000, knowledgeTime=2015-01-13T12:06:02.504Z}
{value=35, validTime=2015-01-13T13:20:00.000/2015-01-13T13:21:00.000, knowledgeTime=2015-01-13T12:20:02.554Z}
{value=34, validTime=2015-01-13T13:34:00.000/2015-01-13T13:35:00.000, knowledgeTime=2015-01-13T12:34:02.490Z}
{value=35, validTime=2015-01-13T13:48:00.000/2015-01-13T13:49:00.000, knowledgeTime=2015-01-13T12:48:02.666Z}

The value content depends on its data type structure. See Attribute types for more details.

 

The full output of the query can be seen below:

Query Result
TabularResult{rows=[{
inProcessCount=[{value=35, validTime=2015-01-13T13:00:00.000/2015-01-13T13:01:00.000, knowledgeTime=2015-01-13T12:00:02.507Z}
, {value=34, validTime=2015-01-13T13:01:00.000/2015-01-13T13:02:00.000, knowledgeTime=2015-01-13T12:01:03.272Z}
, {value=30, validTime=2015-01-13T13:02:00.000/2015-01-13T13:03:00.000, knowledgeTime=2015-01-13T12:02:02.474Z}
, {value=34, validTime=2015-01-13T13:03:00.000/2015-01-13T13:04:00.000, knowledgeTime=2015-01-13T12:03:02.293Z}
, {value=30, validTime=2015-01-13T13:04:00.000/2015-01-13T13:05:00.000, knowledgeTime=2015-01-13T12:04:02.465Z}
, {value=29, validTime=2015-01-13T13:05:00.000/2015-01-13T13:06:00.000, knowledgeTime=2015-01-13T12:05:02.466Z}
, {value=31, validTime=2015-01-13T13:06:00.000/2015-01-13T13:07:00.000, knowledgeTime=2015-01-13T12:06:02.504Z}
, {value=34, validTime=2015-01-13T13:07:00.000/2015-01-13T13:08:00.000, knowledgeTime=2015-01-13T12:07:02.484Z}
, {value=35, validTime=2015-01-13T13:08:00.000/2015-01-13T13:09:00.000, knowledgeTime=2015-01-13T12:08:02.451Z}
, {value=36, validTime=2015-01-13T13:09:00.000/2015-01-13T13:10:00.000, knowledgeTime=2015-01-13T12:09:02.497Z}
, {value=33, validTime=2015-01-13T13:10:00.000/2015-01-13T13:11:00.000, knowledgeTime=2015-01-13T12:10:02.503Z}
, {value=35, validTime=2015-01-13T13:11:00.000/2015-01-13T13:12:00.000, knowledgeTime=2015-01-13T12:11:02.512Z}
, {value=37, validTime=2015-01-13T13:12:00.000/2015-01-13T13:13:00.000, knowledgeTime=2015-01-13T12:12:02.514Z}
, {value=35, validTime=2015-01-13T13:13:00.000/2015-01-13T13:14:00.000, knowledgeTime=2015-01-13T12:13:02.504Z}
, {value=36, validTime=2015-01-13T13:14:00.000/2015-01-13T13:15:00.000, knowledgeTime=2015-01-13T12:14:02.303Z}
, {value=35, validTime=2015-01-13T13:15:00.000/2015-01-13T13:16:00.000, knowledgeTime=2015-01-13T12:15:02.461Z}
, {value=36, validTime=2015-01-13T13:16:00.000/2015-01-13T13:17:00.000, knowledgeTime=2015-01-13T12:16:02.453Z}
, {value=34, validTime=2015-01-13T13:17:00.000/2015-01-13T13:18:00.000, knowledgeTime=2015-01-13T12:17:02.450Z}
, {value=36, validTime=2015-01-13T13:18:00.000/2015-01-13T13:19:00.000, knowledgeTime=2015-01-13T12:18:02.589Z}
, {value=32, validTime=2015-01-13T13:19:00.000/2015-01-13T13:20:00.000, knowledgeTime=2015-01-13T12:19:02.523Z}
, {value=35, validTime=2015-01-13T13:20:00.000/2015-01-13T13:21:00.000, knowledgeTime=2015-01-13T12:20:02.554Z}
, {value=32, validTime=2015-01-13T13:21:00.000/2015-01-13T13:22:00.000, knowledgeTime=2015-01-13T12:21:02.584Z}
, {value=36, validTime=2015-01-13T13:22:00.000/2015-01-13T13:23:00.000, knowledgeTime=2015-01-13T12:22:02.491Z}
, {value=33, validTime=2015-01-13T13:23:00.000/2015-01-13T13:24:00.000, knowledgeTime=2015-01-13T12:23:02.509Z}
, {value=33, validTime=2015-01-13T13:24:00.000/2015-01-13T13:25:00.000, knowledgeTime=2015-01-13T12:24:02.396Z}
, {value=35, validTime=2015-01-13T13:25:00.000/2015-01-13T13:26:00.000, knowledgeTime=2015-01-13T12:25:02.509Z}
, {value=35, validTime=2015-01-13T13:26:00.000/2015-01-13T13:27:00.000, knowledgeTime=2015-01-13T12:26:02.400Z}
, {value=35, validTime=2015-01-13T13:27:00.000/2015-01-13T13:28:00.000, knowledgeTime=2015-01-13T12:27:02.428Z}
, {value=34, validTime=2015-01-13T13:28:00.000/2015-01-13T13:29:00.000, knowledgeTime=2015-01-13T12:28:02.450Z}
, {value=31, validTime=2015-01-13T13:29:00.000/2015-01-13T13:30:00.000, knowledgeTime=2015-01-13T12:29:02.533Z}
, {value=36, validTime=2015-01-13T13:30:00.000/2015-01-13T13:31:00.000, knowledgeTime=2015-01-13T12:30:02.487Z}
, {value=32, validTime=2015-01-13T13:31:00.000/2015-01-13T13:32:00.000, knowledgeTime=2015-01-13T12:31:04.187Z}
, {value=32, validTime=2015-01-13T13:32:00.000/2015-01-13T13:33:00.000, knowledgeTime=2015-01-13T12:32:02.234Z}
, {value=35, validTime=2015-01-13T13:33:00.000/2015-01-13T13:34:00.000, knowledgeTime=2015-01-13T12:33:02.274Z}
, {value=34, validTime=2015-01-13T13:34:00.000/2015-01-13T13:35:00.000, knowledgeTime=2015-01-13T12:34:02.490Z}
, {value=34, validTime=2015-01-13T13:35:00.000/2015-01-13T13:36:00.000, knowledgeTime=2015-01-13T12:35:02.551Z}
, {value=35, validTime=2015-01-13T13:36:00.000/2015-01-13T13:37:00.000, knowledgeTime=2015-01-13T12:36:02.509Z}
, {value=34, validTime=2015-01-13T13:37:00.000/2015-01-13T13:38:00.000, knowledgeTime=2015-01-13T12:37:02.480Z}
, {value=35, validTime=2015-01-13T13:38:00.000/2015-01-13T13:39:00.000, knowledgeTime=2015-01-13T12:38:02.553Z}
, {value=34, validTime=2015-01-13T13:39:00.000/2015-01-13T13:40:00.000, knowledgeTime=2015-01-13T12:39:02.471Z}
, {value=34, validTime=2015-01-13T13:40:00.000/2015-01-13T13:41:00.000, knowledgeTime=2015-01-13T12:40:02.448Z}
, {value=40, validTime=2015-01-13T13:41:00.000/2015-01-13T13:42:00.000, knowledgeTime=2015-01-13T12:41:02.413Z}
, {value=37, validTime=2015-01-13T13:42:00.000/2015-01-13T13:43:00.000, knowledgeTime=2015-01-13T12:42:02.499Z}
, {value=38, validTime=2015-01-13T13:43:00.000/2015-01-13T13:44:00.000, knowledgeTime=2015-01-13T12:43:02.518Z}
, {value=37, validTime=2015-01-13T13:44:00.000/2015-01-13T13:45:00.000, knowledgeTime=2015-01-13T12:44:02.492Z}
, {value=36, validTime=2015-01-13T13:45:00.000/2015-01-13T13:46:00.000, knowledgeTime=2015-01-13T12:45:02.523Z}
, {value=38, validTime=2015-01-13T13:46:00.000/2015-01-13T13:47:00.000, knowledgeTime=2015-01-13T12:46:02.499Z}
, {value=35, validTime=2015-01-13T13:47:00.000/2015-01-13T13:48:00.000, knowledgeTime=2015-01-13T12:47:02.396Z}
, {value=35, validTime=2015-01-13T13:48:00.000/2015-01-13T13:49:00.000, knowledgeTime=2015-01-13T12:48:02.666Z}
, {value=37, validTime=2015-01-13T13:49:00.000/2015-01-13T13:50:00.000, knowledgeTime=2015-01-13T12:49:02.514Z}]}]}

 

 

Related Links