Skip to main content

Quantela platform user manual

Quantela Data Connector

The QuantelaData provider is used to connect to the internal Quantela platform database and retrieve real-time or historical data for use in Adapters or Datasets.

To add a Quantela Data provider to the data flow for an adapter or dataset, drag the corresponding provider icon from the palette on the left of the studio canvas.

updated_provider_qdc.png

Enter the following settings to configure the Quantela Data provider:

Configuration

Description

What data do you need?

Select one of the following to determine what type of data should be returned from the Quantela platform database:

  • RealTime - this returns the most recent record for each entity that matches the query parameters.

  • Metrics - this returns aggregated historical data for a specific time interval for each entity that matches the query parameters.

  • MetricsV3 - this returns MetricsV3 aggregated historical data for a specific time interval for each entity that matches the query parameters.

    Note

    In Metrics, each attribute will be saved as a separate record whereas in MetricsV3 all the attributes will be saved in the same record. Also, in Metrics the memory usage is high whereas in MetricsV3 memory usage is less and it has better performance in aggregations, similar to TimeSeries.

  • Events - select this option to return details of any events that have been generated for the selected entities.

  • Raw - select this option to enter a raw query against the Quantela platform database.

  • TimeSeries - this can be used for returning either global or tenant specific data and returns the most recent record for each entity that matches the query parameters if the table name selected ends with "_entity" or returns the aggregated historical data for a specific time interval for each entity that matches the query parameters if the table name selected ends with "_metric". The global table names begin with "gbl_".

Choose Layer

Select an application domain from the displayed list of domains that have been configured for this deployment of the Quantela platform.

Metrics

If you have chosen to return Metrics or MetricsV3 data then this configuration option allows you to select which type of aggregation is required, which fields to aggregate and to give a custom label name. The following aggregate functions are available:

  • Average - calculate the mean value for each time interval.

  • Count - return the number of matching records within each time interval.

  • Max - calculate the maximum value for each time interval.

  • Min - calculate the minimum value for each time interval.

  • Sum - calculate the sum of all values for each time interval.

  • sumdistinct - calculate the sum of all unique values for each time interval.

  • averagedistinct - calculate the mean value of all unique values for each time interval.

Metric Interval

If you have chosen to return Metrics or MetricsV3 data then this configuration option allows you to select what time interval to use for aggregate calculations. The following options are available:

  • {{metricInterval}} - use the current metric interval that has been specified in the dashboard.

  • Every year - calculate an aggregate value for each year.

  • Every month - calculate an aggregate value for each month.

  • Every week - calculate an aggregate value for each week.

  • Every day - calculate an aggregate value for each day.

  • Every hour - calculate an aggregate value for each hour.

BUILDER/RAW

If you have chosen to return TimeSeries data then this configuration option allows you to select how you can get the data from the Quantela platform database by toggling between the following:

  • BUILDER

  • RAW

Select Table (A)

If you have chosen to return TimeSeries data through BUILDER then select a table having an alias as 'A', from the list of displayed tables from which the data has to be returned. The list of tables displayed are the ones that belong to the domains which the user has access to by following Role-based Access Control (RBAC) and Data Access Control (DAC).

Tip

If the table name ends with "_entity" it is from a real-time table and if it ends with "_metric" it is from a metric table. Also, global table names begin with "gbl_".

Joins

If you have chosen to return TimeSeries data then this configuration option allows you to select which type of join is required, which second table having an alias as 'B' to join, which column from the second table 'B' to join along with which column from the first table 'A'. The following types of joins are available:

  • innerjoin - joins rows from different tables if the join condition is satisfied.

  • fullouterjoin - returns all the records when there is a match in the first or second table records.

  • leftjoin - returns all the records from the first table and the matched records from the second table.

  • rightjoin - returns all the records from the second table and the matched records from the first table.

You can also add multiple conditions in joins and multiple-layered condition filters using the And and Or conditions.

Group By

If you have chosen to return TimeSeries data then this configuration option allows you to group the entities by the columns of the table.

Metrics

If you have chosen to return TimeSeries data then this configuration option allows you to select which type of aggregation is required, which fields to aggregate and to give a custom label name. The following aggregate functions are available:

  • Average - calculate the mean value.

  • Count - return the number of matching records.

  • Max - calculate the maximum value.

  • Min - calculate the minimum value.

  • Sum - calculate the sum of all values.

  • sumdistinct - calculate the sum of all unique values.

  • averagedistinct - calculate the mean value of all unique values.

Enable Streams

Select whether or not the data can be sent in streams. If this option is selected then the data is sent in blocks as it is received so that the data can be processed a record at a time. If the data is formatted as CSV then it is sent one record at a time; if it is in JSON format then it will be sent one object at a time. The dataset can either process each record/object individually and pass it on to the consumer, or it can accumulate all of the fragments and construct a complete CSV file or JSON structure before passing this on to the consumer.

Reference For Tables

If you have chosen to return TimeSeries data through RAW then select a table from the list of displayed tables from which the data has to be returned. The list of tables displayed are the ones that belong to the domains which the user has access to by following Role-based Access Control (RBAC) and Data Access Control (DAC).

Tip

If the table name ends with "_entity" it is from a real-time table and if it ends with "_metric" it is from a metric table. Also, global table names begin with "gbl_".

Reference For Columns

If you have chosen to return TimeSeries data through RAW then select a column from the list of displayed columns based on the table selected in Reference For Tables.

Date Range

Select the date range to be used for historical reports. You can either select one of the pre-configured quick-access date ranges, or you can specify the start and end dates for a custom date range.

Type of Boundary

Select one of the following to query the entities by geographical region:

  • None - select all entities, regardless of their location.

  • Polygon - select all entities that are contained within the polygon defined in Geo Shape. Geo Shape is a JSON array of coordinates, for example: [ [1,1], [1,2], [2,2], [2,1], [1,1] ].

  • Region - select all entities that are in the chosen regions. A list of defined regions is presented, along with the option {{region}} which allows you to indicate that the current region selected in the dashboard should be used for the query.

  • Near By - select all entities that are within a particular Radius (in Km) of the specified Coordinates.

Entity ID

Optionally specify the ID of an individual entity to return data for this one device.

Event ID

Optionally specify the ID of an individual event.

Event Types

Select the type of events from the displayed list of event types that are shown based on the layer that was previously selected.

GeoSpatial

For RealTime and Events data, indicate if the data is to be returned in GeoJSON format.

Query Builder

If you have selected Real-Time, Metrics, MetricsV3, Event or TimeSeries data then the interactive query builder allows you to select attributes related to the entity types in the chosen layer, and to specify corresponding filter values. For example, you can select only Lights whose Intensity is greater than 50 AND whose powerConsumption is less than or equal to 20.

In the Column Selection list-box, you can select one or more columns to be returned in the dataset. The list of columns would depend on which functional layers have been selected.

If you have chosen to return TimeSeries data then the Distinct By option allows you to select one or more columns without duplicate values. Also, the Order By option allows you to select a column and sort the values in it either by ASC or DESC order from the Order drop-down.

You can also specify a Limit for the number of records to be returned by the query, and an Offset if you do not want to return the first records in the query result.

If you have chosen to return Real-Time data then the Extended limit option allows you to specify an Entity Id and Last Updated value of the previous page’s last entity along with the Limit. The default limit is 10,000.

Query builder allows three types of values for filters:

  1. Static Value – Static value can be used to filter or restrict the data.

    Example : Age is greater than “50”, where 50 is the static value

  2. Dynamic Value – Placeholder parameter can be used to filter or restrict the data Example : Age is greater than “{{age_value}}”, where the dynamic value assigned for age_value variable will be used to restrict the data

  3. Dynamic Script – outcome of the script can be used to filter or restrict the data. This is majorly used in the case of ABAC where the access specifier values are assigned when users are created refer create a user to filter or restrict the data. Hence, the data restricted for each user will depend on the user attribute values and hence the data output differs for different users.Create a user

Query Type

If you have chosen to return Raw data then this would indicate whether the query would return Entity, Metric, Event or MetricV3 type data.

Query

If you have chosen to return Raw data, then this panel allows you to edit a raw Elasticsearch query against the Quantela platform database. Queries must be written in Elasticsearch Query DSL format. For example, the following query would return all entities whose entity-type is "Environment".

{
  "query": { 
    "bool": { 
      "must": [
        {"match": { "entity_type": "Environment" }}
      ]
    }
  }
}

Group By

If you have chosen to return Metrics or MetricsV3 data then this configuration option allows you to group the entities by certain attributes. The following options are available:

  • entityId - the ID of an individual entity to return data for this one device.

Show Query

Shows the Elastic DSL query and which can be copied using the Copy Query Text icon new_copy_preview.png. Likewise, you can hide the query by clicking Hide Query.

 

Querying through TimeSeries - RAW using QDC() functions

If you have chosen to return TimeSeries - RAW data, then the Query panel allows you to enter raw queries using the following QDC() functions:

  • from - fetches data from a table using the syntax QDC().from(tableName). For example, QDC().from("gbl_spatial_demographics_entity").

  • select - selects all or the specified columns from a table.

    • selects specific columns from a table using the syntax QDC().select(columns).from(tableName). For example, QDC().select(["location_key","name"]).from("gbl_spatial_demographics_entity").

    • selects all the columns from a table using the syntax QDC().select("*").from(tableName). For example, QDC().select("*").from("gbl_spatial_demographics_entity").

  • where - fetches the data from a table which satisfies a condition using the syntax QDC().from(tableName).where(key, operator, value). For example, QDC().from("gbl_spatial_demographics_entity").where("name","=","India").

  • andWhere - fetches the data from a table which satisfies two conditions using the syntax QDC().from(tableName).andWhere(key, operator, value). For example, QDC().from("gbl_spatial_demographics_entity").andWhere("name","=","India").

  • orWhere - fetches the data from a table which satisfies any of the two conditions using the syntax QDC().from(tableName).orWhere(key, operator, value). For example, QDC().from("gbl_spatial_demographics_entity").orWhere("name","=","India").

  • offset - limits the number of records to be returned and doesn't fetch the ones before the offset using the syntax QDC().from(tableName).limit(value).offset(value). For example, QDC().from("gbl_spatial_demographics_entity").limit(10).offset(1).

  • limit - limits the number of records to be returned using the syntax QDC().from(tableName).limit(value). For example, QDC().from("gbl_spatial_demographics_entity").limit(10).

  • orderBy - sorts the values of a single column or multiple columns of a table using the syntax QDC().from(tableName).orderBy(columns, order).

    • sorts the values of a single column. For example, QDC().from("gbl_spatial_demographics_entity").orderBy("last_updated", "desc").

    • sorts the values of multiple columns. For example, QDC ().from("gbl_spatial_demographics_entity").orderBy([{"column": "last_updated", "order": "asc"}, {"column": "name", "order": "desc"}]).

  • groupBy - arranges identical data into groups using the syntax QDC().from(tableName).groupBy(column). For example, QDC().from("gbl_spatial_demographics_entity").groupBy("entity_id").

  • having - fetches a group of data which satisfies a condition using the syntax QDC().from(tableName).groupBy(column).having(key, operator, value). For example, QDC().from("gbl_spatial_demographics_entity").groupBy("entity_id").having("name", ">", 100).

  • distinct - fetches the data of a single column or multiple columns of a table without duplicates using the syntax QDC().from(tableName).distinct(columns).

    • fetches the unique values of single column. For example, QDC().from("gbl_spatial_demographics_entity").distinct("entityId").

    • fetches the unique values of multiple columns. For example, QDC().from("gbl_spatial_demographics_entity").distinct(["entity_id", "id"]).

  • distinctOn - fetches the data of a single column or multiple columns of a table without duplicates and is used in certain cases in PostgreSQL using the syntax QDC().from(tableName).distinctOn(columns).

    • fetches the unique values of a single column. For example, QDC().from("gbl_spatial_demographics_entity").distinctOn("entityId").

    • fetches the unique values of multiple columns. For example, QDC().from("gbl_spatial_demographics_entity").distinctOn(["entity_id", "id"]).

  • count - counts the number of rows in a column using the syntax QDC().from(tableName).count(columns). For example, QDC().from("gbl_spatial_demographics_entity").count("entity_id").

  • min - fetches the minimum value of a single column or multiple columns of a table using the syntax QDC().from(tableName).min(columns).

    • fetches the minimum value of a single column. For example, QDC().from("gbl_spatial_demographics_entity").min("intensity").

    • fetches the minimum value of multiple columns. For example, QDC().from("gbl_spatial_demographics_entity").min({"min":["intensity", "lightId"]}).

  • max - fetches the maximum value of a single column or multiple columns of a table using the syntax QDC().from(tableName).max(columns).

    • fetches the maximum value of a single column. For example, QDC().from("gbl_spatial_demographics_entity").max("intensity").

    • fetches the maximum value of multiple columns. For example, QDC().from("gbl_spatial_demographics_entity").max({"min":["intensity", "lightId"]}).

  • sum - calculates the sum of all the values of a column or multiple columns of a table using the syntax QDC().from(tableName).sum(columns).

    • calculates the sum of a single column. For example, QDC().from("gbl_spatial_demographics_entity").sum("products").

    • calculates the sum of multiple columns. For example, QDC().from("gbl_spatial_demographics_entity").sum({"sum": ["products", "orders"]}).

  • sumDistinct - adds a distinct expression inside the aggregate function using the syntax QDC().from(tableName).sumDistinct(columns). For example, QDC().from("gbl_spatial_demographics_entity").sumDistinct("products").

  • avgDistinct - calculates the average of a distinct expression inside the aggregate function using the syntax QDC().from(tableName).avgDistinct(columns). For example, QDC().from("gbl_spatial_demographics_entity").avgDistinct("location_admin_level").

  • avg - calculates the average of all the values of a column or multiple columns of a table using the syntax QDC().from(tableName).avg(columns).

    • calculates the average of a single column. For example, QDC().from("gbl_spatial_demographics_entity").avg("products").

    • calculates the average of multiple columns. For example, QDC().from("gbl_spatial_demographics_entity").avg({"avg": ["products", "orders"]}).

  • pluck - plucks the specified column from each row using the syntax QDC().from(tableName).pluck(columns). For example, QDC().from("gbl_spatial_demographics_entity").pluck("entity_id").

  • first - fetches the first record of the specified column using the syntax QDC().from(tableName).first(columns). For example, QDC().from("gbl_spatial_demographics_entity").first("entity_id").

  • join - specifies joins between tables using the syntax QDC().from(tableName1).join(tableName2, first, operator, second). For example, QDC().from("gbl_spatial_geography_entity").join("geography_columns", "geography_columns.srid", "=", "table1.id)".

  • innerJoin - joins rows from different tables if the join condition is satisfied using the syntax QDC().from(tableName1).innerJoin(tableName2, first, operator, second). For example, QDC().from("gbl_spatial_geography_entity").innerJoin("geography_columns", "geography_columns.srid", "=", "table1.id)".

  • leftJoin - returns all the records from the first table and the matched records from the second table using the syntax QDC().from(tableName1).leftJoin(tableName2, first, operator, second). For example, QDC().from("gbl_spatial_geography_entity").leftJoin("geography_columns", "geography_columns.srid", "=", "table1.id)".

  • leftOuterJoin - returns all the records from the first table and all the records from the second table even if they are not matched using the syntax QDC().from(tableName1).leftOuterJoin(tableName2, first, operator, second). For example, QDC().from("gbl_spatial_geography_entity").leftOuterJoin("geography_columns", "geography_columns.srid", "=", "table1.id)".

  • rightJoin - returns all the records from the second table and the matched records from the first table using the syntax QDC().from(tableName1).rightJoin(tableName2, first, operator, second). For example, QDC().from("gbl_spatial_geography_entity").rightJoin("geography_columns", "geography_columns.srid", "=", "table1.id)".

  • rightOuterJoin - returns all the records from the second table and all the records from the first table even if they are not matched using the syntax QDC().from(tableName1).rightOuterJoin(tableName2, first, operator, second). For example, QDC().from("gbl_spatial_geography_entity").rightOuterJoin("geography_columns", "geography_columns.srid", "=", "table1.id)".

  • fullOuterJoin - returns all the records when there is a match in the first or second table records using the syntax QDC().from(tableName1).fullOuterJoin(tableName2, first, operator, second). For example, QDC().from("gbl_spatial_geography_entity").fullOuterJoin("geography_columns", "geography_columns.srid", "=", "table1.id)".

  • crossJoin - combines each row of the first table with each row of the second table using the syntax QDC().from(tableName1).crossJoin(tableName2, first, operator, second). For example, QDC().from("gbl_spatial_geography_entity").crossJoin("geography_columns", "geography_columns.srid", "=", "table1.id)".

  • whereIn - fetches the data from a table for the values in a column using the syntax QDC().from(tableName).whereIn(key, value). For example, QDC().from("gbl_spatial_geography_entity").whereIn("geography_columns.srid", ["val1","val2","val3"]).

  • whereNotIn - fetches the data from a table for not the exact values in a column using the syntax QDC().from(tableName).whereNotIn(key, value). For example, QDC().from("gbl_spatial_geography_entity").whereNotIn("geography_columns.srid", ["val1","val2","val3"]).

  • whereNull - fetches the data from a table for the null values in a column using the syntax QDC().from(tableName).whereNull(key). For example, QDC().from("gbl_spatial_geography_entity").whereNull("geography_columns.srid").

  • whereNotNull - fetches the data from a table for the values that are not null in a column using the syntax QDC().from(tableName).whereNotNull(key). For example, QDC().from("gbl_spatial_geography_entity").whereNotNull("geography_columns.srid").

  • whereBetween - fetches the data from a table for the values that are between a range in a column using the syntax QDC().from(tableName).whereBetween(key, range). For example, QDC().from("gbl_spatial_geography_entity").whereBetween("geography_columns.srid", [1,100]).

  • whereNotBetween - fetches the data from a table for the values that are not between a range in a column using the syntax QDC().from(tableName).whereNotBetween(key, range). For example, QDC().from("gbl_spatial_geography_entity").whereNotBetween("geography_columns.srid", [1,100]).

  • orWhereIn - fetches the data from a table for the values in a column which satisfies any of the two "where" conditions using the syntax QDC().from(tableName).orWhereIn(key, value). For example, QDC().from("gbl_spatial_geography_entity").orWhereIn("geography_columns.srid", ["val1","val2","val3"]).

  • orWhereNotIn - fetches the data from a table for not the exact values in a column which satisfies any of the two "where" conditions using the syntax QDC().from(tableName).orWhereNotIn(key, value). For example, QDC().from("gbl_spatial_geography_entity").orWhereNotIn("geography_columns.srid", ["val1","val2","val3"]).

  • orWhereNull - fetches the data from a table for the null values in a column which satisfies any of the two "where" conditions using the syntax QDC().from(tableName).orWhereNull(key). For example, QDC().from("gbl_spatial_geography_entity").orWhereNull("geography_columns.srid").

  • orWhereNotNull - fetches the data from a table for the values that are not null in a column which satisfies any of the two "where" conditions using the syntax QDC().from(tableName).orWhereNotNull(key). For example, QDC().from("gbl_spatial_geography_entity").orWhereNotNull("geography_columns.srid").

  • orWhereBetween - fetches the data from a table for the values that are between a range in a column which satisfies any of the two "where" conditions using the syntax QDC().from(tableName).orWhereBetween(key, range). For example, QDC().from("gbl_spatial_geography_entity").orWhereBetween("geography_columns.srid", [1,100]).

  • orWhereNotBetween - fetches the data from a table for the values that are not between a range in a column which satisfies any of the two "where" conditions using the syntax QDC().from(tableName).orWhereNotBetween(key, range). For example, QDC().from("gbl_spatial_geography_entity").orWhereNotBetween("geography_columns.srid", [1,100]).

  • Simple Select - selects all or the specified columns from a table for the specified criterion in the select statement using the syntax QDC().select("count(*) as total").from("tableName"). For example, QDC().select("count(*) as total").from("gbl_abstractiontimeseries_gbllinestring_entity").

  • Simple PostGIS - returns the geometry as a Geometry Javascript Object Notation (GeoJSON) element using PostGIS methods and using the syntax QDC().select(["ST_AsGeoJSON(geometryColumnName)::json"]).from("tableName"). For example, QDC().select(["ST_AsGeoJSON(linestring)::json"]).from("gbl_abstractiontimeseries_gbllinestring_entity").

  • Multi Condition Joins - joins rows from different tables with multiple conditions using the syntax QDC().from("table_name_1").innerJoin("table_name_2",{ "condition": "and", "rules": [ { "field": "table_name_1.table_1_column1", "operator": "=", "value": "table_name_2.table_2_column1" }, { "field": "table_name_1.table_1_column2", "operator": "=", "value": "table_name_2.table_2_column2" } ] }).

    For example, QDC().from("gbl_joins_singlecondition_metric").fullOuterJoin("gurugram_com_joins_multicondition_metric",{"condition": "and","rules":[{"field":"gbl_joins_singlecondition_metric.provider","operator": "=","value": "gurugram_com_joins_multicondition_metric.provider"},{"field": "gbl_joins_singlecondition_metric.version","operator": "=","value": "gurugram_com_joins_multicondition_metric.version"}]}).

  • Multi-layered Condition Filters - filters the data in a table using nested conditions and using the syntax QDC().from("table_name_1").where({ "condition": "and", "rules": [ { "field": "column_1", "operator": "is", "value": "column_1_value" }, { "condition": "or", "rules": [ { "field": "column_2", "operator": "is", "value": "column_2_value" }, { "field": "column_3", "operator": "is", "value": "column_3_value" } ] } ] }).

    For example, QDC().from("gbl_joins_singlecondition_entity").where({"condition": "and", "rules":[{"field": "gbl_joins_singlecondition_entity", "operator": "is", "value": "humidity"}, {"field": "gbl_joins_singlecondition_entity", "operator": "is",  "value": "aqi" }]}).

Click the Save and Generate Schema button at the bottom of the Configuration panel to save the configuration with schema or Save Adapter/Dataset to save the configuration without schema.

In this section: