Running and Calling Scripts

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search

This page describes how expression and SQL scripts can be run and called in QPR ProcessAnalyzer. Running a script means starting executing a stored script, which can be done in the UI or using the ScriptLauncher. A script can also call other scripts during the run. The differences between the running and calling are:

  • Script can run only once at a time, whereas there are no limitations on how many times a script can be called at the same time
  • Script log is written to the running script. No script log is written to the called script.
  • Script status shows whether the script is being run or not. The status does not change, when a script is called from other script.

Running Script using UI

Script runs can be started and stopped in the UI. Note that when running scripts using the UI, return values or results of the script cannot be viewed. If you want to use scripts that return data, there are following options:

  • Script can write data to the script log
  • Script can write data to a datatable
  • Script can be called from a dashboard and use the dashboard to present the returned data (see more below).

Running SQL Script using ScriptLauncher

To get started with the ScriptLauncher, first following the instructions for ScriptLauncher installation and configuration and then instructions for running. The ScriptLauncher is operated in the command line, allowing to pass parameters to the script. Possible results of the script are stored as CSV files to the local disk in the computer where the ScriptLauncher is running.

When a script is run using the ScriptLauncher, its progress can be followed in the UI in the script log.

Running Expression Script using ScriptLauncher

Expression scripts are run by following the same instructions as for SQL scripts. Parameters can be specified as in SQL scripts and the parameters are available as variables in the expression script. Note that in the ScriptLauncher configuration, parameters need to be prefixed with Parameter_, and in the expression script variable names don't have this prefix.

If an expression script returns a DataFrame, it's stored as a CSV file to the local disk (file name is same as the script name). Expression script can also return a dictionary of DataFrames, and all DataFrames are written as CSV files to the disk (file name will be the dictionary key). If the expression script returns any other type of value than mentioned previously, the value is converted into a DataFrame having one column and row and written to the disk.

Example of a dictionary type of return value defined as literal:

return #{
  "File 1": ToDataFrame([["Value 11", "Value 12", "Value 13"], ["Value 21", "Value 22", "Value 23"]], ["Column 1", "Column 2", "Column 3"]),
  "File 2": ToDataFrame([["Value 11", "Value 12", "Value 13"], ["Value 21", "Value 22", "Value 23"]], ["Column 4", "Column 5", "Column 6"]),
  "File 3": ToDataFrame([["Value 11", "Value 12"], ["Value 21", "Value 22"]], ["Column 7", "Column 8"])
};

Calling Expression Script from Expression

Expression scripts can be called from an expression using the Run function with the following syntax:

let result = ScriptById(123).Run(#{
  "parameter1": "value1",
  "parameter2": false,
  "parameter2": 123.45
})

The command waits until the run is completed, and the return value of the called script is returned by the Run function call.

Parameters can be passed to the called script, and the parameters are available as variables in the script. The parameters can contain any type of data.

Expression scripts can also be called from a dashboard. Expressions can be stored to scripts instead of dashboards, which is a way to separate complex expressions from dashboards and allow to reuse expressions across several dashboards.

Calling SQL Script from Expression

SQL script can be called from an expression using the Run function as follows (similar to calling expression scripts):

let result = ScriptById(123).Run(#{
  "parameter1": "value1",
  "parameter2": 321
});
let arrayOfAllReports = result.Keys;
let report1 = result.Report1;
let report2 = result.Report2;

SQL scripts can return multiple reports, which are combined to a dictionary, where the key is the name of the report ("sheet name") and value is the report data as a DataFrame. See in the above example, how the reports can be accessed by their name.

Calling Expression Script from SQL Script

Expression scripts can be called from SQL scripts by using the --#GetAnalysis command. There you need to call the AnalysisType=33 to run an expression to use the Run function. Here is an example (to call an expression script with id 123 and pass parameters values):

(SELECT 'AnalysisType', '33') UNION ALL
(SELECT 'TargetTable', '#ExampleTable') UNION ALL
(SELECT 'Configuration', '{
	"Root": "ScriptById(123).Run(#{
		\"parameter1\": \"value1\",
		\"parameter2\": 321
	})"
}')
--#GetAnalysis

In the above example, if the expression script returns a DataFrame, it is stored to the #ExampleTable temporary table in the scripting sandbox database.

Calling SQL Script from SQL Script

In an SQL script, it's possible to call other SQL script using the --#Run command. For more information, see its documentation and examples.