SqlExecution

Applies arbitrary SQL on any data. SQL is applied in memory.

Inputs

Any number of inputs. targetProperty is used as the table name when SQL is executed.

Outputs

One output for each query. outputKey names are 0, 1, 2 etc... corresponding to the number of the query.

Parameters

name	description	default value
`sql.engine`	SQL Engine to use. Either `CALCITE` or `HYPERSQL`.	`HYPERSQL`
`sql.queries`	A list of sql queries to execute.
`jdbc.parameters`	Additional jdbc parameters to pass. In a key-value format.

Available SQL Engines for `engine`

HYPERSQL

HSQLDB SQL. See SQL reference (opens in a new tab).
Input data is copied to an in-memory HSQLDB, then SQL is applied.

CALCITE

Apache Calcite SQL. Very close to ANSI SQL. See SQL reference (opens in a new tab).
Try Calcite SQL online here (opens in a new tab).
BigQuery functions listed here (opens in a new tab) are available. In particular, TIMESTAMP_MILLIS and UNIX_MILLIS are available to easily convert epoch to time and vice-versa.
The implementation is zero-copy, meaning no data is copied from the ThirdEye app to another process. SQL is run on the Java data directly.

Example

{
  "name": "sql-join",
  "type": "SqlExecution",
  "params": {
    "sql.engine": "HYPERSQL",
    "sql.queries": [
      "SELECT ts as timestamp, met as value FROM baseline WHERE ts BETWEEN 20200715 AND 20200720",
      "SELECT ts as timestamp, met as value FROM current WHERE ts BETWEEN 20200715 AND 20200720",
      "SELECT ts, avg(met) as met FROM (SELECT ts, met FROM baseline UNION ALL SELECT ts, met FROM current) WHERE ts BETWEEN 20200715 AND 20200720 GROUP BY ts ORDER BY ts"
    ],
    "jdbc.parameters": {"key":  "value"}   # dictionnary format
  },
  "inputs": [                              # two inputs
    {
      "targetProperty": "baseline",        # name of the table in the queries
      "sourcePlanNode": "baselineDataFetcher",
      "sourceProperty": "baselineOutput"
    },
    {
      "targetProperty": "current",         # name of the table in the queries
      "sourcePlanNode": "currentDataFetcher",
      "sourceProperty": "currentOutput"
    }
  ],
  "outputs": [
    {
      "outputKey": "0",                   # result of first query
      "outputName": "baseline"
    },
    {
      "outputKey": "1",                   # result of second query
      "outputName": "current"
    },
    {
      "outputKey": "2",                   # result of last query
      "outputName": "final"
    }
  ]
}

EventFetcher TimeIndexFiller