SqlDataFrame in Expression Language: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
No edit summary
Line 120: Line 120:
||Same functionality as in the [[DataFrame_in_Expression_Language|DataFrame]]. Note that not all expression language functionality are available in the condition expression, as the condition is converted into SQL having differences and limitations comparing to the expression language functionality.
||Same functionality as in the [[DataFrame_in_Expression_Language|DataFrame]]. Note that not all expression language functionality are available in the condition expression, as the condition is converted into SQL having differences and limitations comparing to the expression language functionality.


Condition also supports ''CaseWhen'' function, which goes through conditions and returns a value when the first condition is met (like an if-then-else statement). The odd parameters are the conditions and the even parameters are the return values. If no conditions are true, it returns the value in the else-expression. If the "else" parameter is not defined (i.e. there are even number of parameters), null value is used as default.
Condition also supports ''CaseWhen'' function, which goes through conditions and returns a value when the first condition is met (like an if-then-else statement). The odd parameters are the conditions and the even parameters are the return values. If no conditions are true, it returns the value in the last parameter which is the "else" parameter. If the "else" parameter is not defined (i.e. there are even number of parameters), null value is used as default.
|}
|}

Revision as of 19:19, 8 June 2021

SqlDataFrame represents tabular data similar to a result of an SQL query. Data in the SqlDataFrames are processed in the original datasource where the data is located (not in the QPR ProcessAnalyzer server in-memory). For each SqlDataFrame, there is an SQL query that processes and generates the actual data in the datasource.

Running SqlDataFrames and operations between them don't yet execute the SQL in the original datasource. This happens when the Collect function is called for an SqlDataFrame, which executes the SQL query of the SqlDataFrames and loads the data into QPR ProcessAnalyzer server in-memory dataframe (where it can be presented in a dashboard).

Each SqlDataFrames contain information about the datasource where the SQL query will be executed. When writing queries with several SqlDataFrames, the SqlDataFrames need to be located in the same datasource, because otherwise the queries cannot be executed. To do that, data needs to be moved between datasources, and that can be done using the Import or Persist functions. Alternatively, processing can be done in the in-memory core by calling Collect for the SqlDataFrames and continuing queries using the in-memory DataFrames.

There is a similar API for the SqlDataFrames as there is for the DataFrames.

SqlDataFrame functions Parameters Description
Aggregate (SqlDataFrame)
  1. Aggregated columns (string array or key-value pairs)
  2. Aggregation methods (string array)
 Same functionality as in the DataFrame.
Collect (SqlDataFrame) (none)

Executes the SQL query for the SqlDataFrame in the datasource and returns results as an in-memory DataFrame. Then processing of the data can be continued as the in-memory DataFrame. In addition to the Persist function, Collect function is the only way to get the actual SQL query executed to see the results (or store them to a table).

Examples: 

DataTableById(123).SqlDataFrame.Head(100).Collect().ToCsv()
Returns the top 100 rows from datatable id 123.
ExcludeValues (SqlDataFrame)
  1. Column name (string)
  2. Value (single item) or values (array) to exclude

Same functionality as in the DataFrame.

GroupBy (GroupedDataFrame)

Grouped columns (string array)

Same functionality as in the DataFrame.

Head (SqlDataFrame) Number of top rows

Same functionality as in the DataFrame.

IncludeOnlyValues (SqlDataFrame)
  1. Column name (string)
  2. Value (single item) or values (array) to include

Same functionality as in the DataFrame.

Join (SqlDataFrame)
  1. DataFrame
  2. Columns to match (String or key-value pairs)
  3. Join type (String)
 Same functionality as in the DataFrame.
OrderByColumns (SqlDataFrame)
  1. Columns to be ordered (String array)
  2. Sorting order (boolean array)

Same functionality as in the DataFrame.

Persist (SqlDataFrame)
  1. DataTable name
  2. Additional parameters

Same functionality as in the DataFrame. If the SQL query for the SqlDataFrame is run in the same system as the target Datatable, for efficient operation, all data processing and storage is done within the system.

Select (SqlDataFrame)

Column names (string array, or key-value pairs)

Same functionality as in the DataFrame.

TakeSample (SqlDataFrame) Number of rows (Integer) Same functionality as in the DataFrame.
WithDenseRankNumberColumn (SqlDataFrame)
  1. New column name (String)
  2. Order by columns (String array)
  3. Partition by columns (String array)

Same functionality as in the DataFrame.

WithExpressionColumn (SqlDataFrame)
  1. New column name (String)
  2. Calculation expression

Same functionality as in the DataFrame. Note that not all expression language functionality are available in the expression, as the condition is converted into SQL having differences and limitations comparing to the expression language functionality.

WithRankColumn (SqlDataFrame)
  1. New column name (String)
  2. Order by columns (String array)
  3. Partition by columns (String array)

Same functionality as in the DataFrame.

WithRowNumberColumn (SqlDataFrame)
  1. New column name (String)
  2. Order by columns (String array)
  3. Partition by columns (String array)

Same functionality as in the DataFrame.

Where (SqlDataFrame) Condition expression Same functionality as in the DataFrame. Note that not all expression language functionality are available in the condition expression, as the condition is converted into SQL having differences and limitations comparing to the expression language functionality.

Condition also supports CaseWhen function, which goes through conditions and returns a value when the first condition is met (like an if-then-else statement). The odd parameters are the conditions and the even parameters are the return values. If no conditions are true, it returns the value in the last parameter which is the "else" parameter. If the "else" parameter is not defined (i.e. there are even number of parameters), null value is used as default.

Retrieved from "https://wiki.onqpr.com/pa/index.php?title=SqlDataFrame_in_Expression_Language&oldid=18328"