Using external scripts as SQL derived tables

It is possible to utilize the results a Kognitio external script SENDS back to Kognitio after it has been invoked. This is useful if the output of the external script requires some SQL processing or if they need to be input into another external scrtipt.

Like with traditional SQL, this is done with a derived table using the syntax:

output-sql
FROM ( EXTERNAL SCRIPT script-name
       [PARAMETERS param-name1=value1, ...
       [FROM (sql-input-query)]) dt1
[output-sql]

Note

dt1 is simply an SQL alias for the external script invocation in the same way that any derived table must be aliased in a Kognitio SQL query. This alias can be any string. The output-sql strings can be any SQL acting on the derived table containing the script invocation. The most simple example being SELECT * FROM.

Example: using a perl word parsing script to produce a word count

Consider the previous example where a perl script is used to split text fields into words. This could be used as a derived table for reporting on most frequent words in SQL query commands on Kognitio:

SELECT UPPER(words),COUNT(*)
FROM ( EXTERNAL SCRIPT myschema.word_split
FROM ( SELECT command FROM sys.ipe_command
        WHERE tno IN ( SELECT DISTINCT tno
            FROM sys.ipe_transaction
            WHERE TDATE = current_date)
    )
)dt1
WHERE CHAR_LENGTH(words) > 2
GROUP BY 1
ORDER BY 2 DESC;

The query is restricted to commands run on the current date and words of length greater than 2.