Using Repeatable Queries to update a database with result sets

In EdgeLake, a Repeatable Query is a query placed on the EdgeLake rule engine, executed repeatedly, and configured to update a target table with the query results.

A common use case is to provide result sets to BI tools and third-party applications that do not support the REST API.
For these tools and applications, results sets of queries over the network data are hosted in a database.
For example, Tableu and Lookr do not support the REST API but can leverage Repeatable Queries.

The examples below places the network data in PotgreSQL.

Setting up PostgreSQL

  1. Install PostgresSQL
  2. In postgresql.conf, update listen_address value to allow remote access
    listen_addresses = '*'
  3. In pg_hba.conf, add the following line at the bottom of the paga
    host    all            all             0.0.0.0/0               md5
  4. Restart PostgresSQL instance

Executing Query

  1. On EdgeLake connect >system_query to Postgres database
    <connect dbms system_query where 
   type=psql and 
   ip=127.0.0.1 and
   port=5432 and 
   user=anylog and 
   password=demo>
  2. Execute query - as repeatable query
    run client () sql aiops format=table and table=fic11_mv and drop=true "select increments(hour, 1, timestamp), min(timestamp), min(value), avg(value), max(value) from fic11_mv where timestamp >= NOW() - 1 day"
  3. Utilize query explain to view how the results are generated
    AL aiops-single-node < query explain
   07 Remote DBMS    : aiops
   07 Remote Table   : fic11_mv
   07 Source Command : select increments(hour, 1, timestamp), min(timestamp), min(value), avg(value), max(value) from fic11_mv where timestamp >= NOW() - 1 day
   07 Remote Query   : select date_trunc('day',timestamp), (extract(hour FROM timestamp)::int / 1), min(timestamp), min(value), SUM(value), COUNT(value), max(value) from fic11_mv where timestamp >= '2022-01-17T18:31:31.442147Z' group by 1,2
   07 Local Create   : create table new_table (increments_1_trunc timestamp without time zone, increments_1_extract integer, min_2 timestamp without time zone, min_3 double precision, SUM__value numeric, COUNT__value integer, max_5 double precision);
   07 Local Query    : select min(min_2), min(min_3), SUM(SUM__value) /NULLIF(SUM(COUNT__value),0), max(max_5) from new_table group by increments_1_trunc,increments_1_extract order by increments_1_trunc,increments_1_extract

Note:

  • In the example above, output is placed in table named: fic11_mv.
  • If drop is set to True, every query execution deletes the previous result sets. Users can configure the process to be incremental to the previous result sets.
  • The Query Options document details the query configuration options.

Extract Data onto Tableau

  1. Download & Install Tableau
  2. Under Data → Data Sources select PostgresSQL connector type
  3. Fill-out the information to connect to database & Press "Ok"
  4. Double-click on the table you want to use (in this case new_table) and go to worksheet

Generating Graphs

The system_query database gathers (query) results from the different instances to generate a unified dataset for the user. As such, generating graphs from the final results is a bit complicated.

  • Min 2 - is column MIN(timestamp)
  • Min 3 - is column MIN(value)
  • SUM(SUM__VALUE) / COUNT(new_table_count) – is column AVG(value)
  • MAX 5 - is column MAX(value)

To generate a graph, use “Min 2” as Columns and all others for Rows