SqlDataFrame in Expression Language: Difference between revisions
Line 231: | Line 231: | ||
||WithClusterColumn (SqlDataFrame) | ||WithClusterColumn (SqlDataFrame) | ||
|| | || | ||
# New column | # New column with cluster labels (String) | ||
# Parameters dictionary | # Parameters dictionary | ||
|| | || |
Revision as of 06:47, 6 June 2023
SqlDataFrame represents tabular data similar to an SQL query result. Data managed using SqlDataFrames is processed in the datasource where the data is located, i.e., in Snowflake or SQL Server (not in QPR ProcessAnalyzer Server memory). For each SqlDataFrame, there is an SQL query generated that is run in the datasource where the referenced datatables are located.
SqlDataFrame operations itself don't cause the SQL to execute in the datasource, but it will happen when the Collect function is called for an SqlDataFrame, which generates and executes the SQL query representing the SqlDataFrame and loads the data into QPR ProcessAnalyzer memory as a DataFrame (where it can be presented in a dashboard).
Each SqlDataFrame 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, to be able to execute the queries. If needed, data can be moved between datasources by using the Import or Persist functions. Alternatively, processing can be done in-memory by calling Collect for an SqlDataFrame and continuing calculation as in-memory DataFrame.
There is a similar API for the SqlDataFrames as there is for the DataFrames. Note that merging is not possible between SqlDataFrames like it's for DataFrames, but SqlDataFrame can be merged into a datatable.
SqlDataFrame properties
Property | Description |
---|---|
ColumnTypes (Dictionary) |
Returns information about the columns of the SqlDataFrame as an array of dictionaries with keys Name and DataType. Columns are returned in the same order as the columns exist in the data table. Column types are calculated based on the type of the column in the relational database management system (i.e. type of the column in an SQL table). Examples: table.SqlDataFrame.ColumnTypes Returns: [ #{ "Name": "name1", "DataType": "Integer" }, #{ "Name": "name2", "DataType": "String" } ] table.SqlDataFrame.ColumnTypes.Name Returns: ["name1", "name2"] table.SqlDataFrame.ColumnTypes.DataType Returns: ["Integer", "String"] |
NColumns (Integer) | Returns number of columns in the dataset represented by the SqlDataFrame. |
NRows (Integer) | Returns number of rows in the dataset represented by the SqlDataFrame. |
SqlDataFrame functions
Function | Parameters | Description |
---|---|---|
Aggregate (SqlDataFrame) |
|
Same functionality as in the DataFrame. |
Append | DataFrame to append | Same functionality as in the DataFrame. |
ApplyFilter (SqlDataFrame) |
|
Filters the SqlDataFrame (assumed to contain events data) using given filter and returns SqlDataFrame containing the remaining event data after filtering is performed. Requires that the source SqlDataFrame has CaseId, EventType and TimeStamp mappings defined. Parameters:
Example: Returns SqlDataFrame containing only events for case id "12345". let model = ModelById(123); model .EventsDataTable .SqlDataFrame .ApplyFilter( #{ "Items": [ #{ "Type": "IncludeCases", "Items": [ #{ "Type": "Case", "Values": [ "12345" ] }] }] }, model.CasesDataTable.SqlDataFrame ) |
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. Examples: DataTableById(123).SqlDataFrame.Head(100).Collect().ToCsv() Returns the top 100 rows from datatable id 123. |
ExcludeValues (SqlDataFrame) |
|
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) |
|
Same functionality as in the DataFrame. |
Join (SqlDataFrame) |
|
Same functionality as in the DataFrame. |
OrderByColumns (SqlDataFrame) |
|
Same functionality as in the DataFrame. |
Persist (SqlDataFrame) |
|
Same functionality as in the DataFrame. Additionally, if the SQL query for the SqlDataFrame is run in the same system as the target datatable, all data processing and storage is done within the system to achieve efficient operation. |
Pivot (SqlDataFrame) |
|
Performs the pivot operation for the dataframe. More information about pivot: https://www.techonthenet.com/sql_server/pivot.php. Parameters:
Example: let data = ModelById(1) .EventsDatatable .SqlDataFrame; let result1 = data .Select(["User", "Event type"]) .Pivot("User", "User", ["John", "Greg", "James", "Sharon"], "Count").Collect(); // Returns user counts (on columns) for each event type (on rows) let result2 = data .Select(["User", "Event type", "Cost"]) .Pivot("Cost", "User", ["John", "Greg", "James", "Sharon"], "Sum").Collect(); // Returns costs for each user (on columns) and event type (on rows) |
RemoveColumns (SqlDataFrame) | Column names (string array) | Same functionality as in the DataFrame. |
Select (SqlDataFrame) | Column names (string array, or key-value pairs) | Same functionality as in the DataFrame. |
SelectDistinct (SqlDataFrame) |
Column names (string array, or key-value pairs) |
Same functionality as in the DataFrame. |
Skip (SqlDataFrame) | Number of rows to skip | Same functionality as in the DataFrame. |
TakeSample (SqlDataFrame) | Number of rows (Integer) | Same functionality as in the DataFrame. |
Unpivot (SqlDataFrame) |
|
Performs the unpivot operation for the dataframe, i.e., rotates columns into rows. More information about unpivot: https://docs.snowflake.com/en/sql-reference/constructs/unpivot.html. Parameters:
Note that the Unpivot function is only supported for SqlDataFrames (not in-memory DataFrames). Example: let result = df.Unpivot( "Value", "Name", ["Column 1", "Column 2", "Column 3"] ).Collect(); This example reads case attributes from a model and performs unpivot for them. Only string type of columns are unpivotted and also the case id columns is ignored. let caseAttributes = ModelById(123).CasesDatatable; caseAttributes.SqlDataFrame.Unpivot( "Value", "Case attribute", caseAttributes.Columns.Where(Datatype == "String" && Name != "CaseId").Name ).Collect().toCsv() |
WithClusterColumn (SqlDataFrame) |
|
Performs clustering to the data and creates a new SqlDataFrame with a column containing cluster labels. Cluster labels are integers starting from 0. Parameters:
Uses K-Means algorithm implemented with scikit-learn KMeans python package where random_state is set to be 0. Different data types are preprocessed before performing the clustering as follows:
Example: Cluster data in a datatable into 5 clusters based on column "Region" and "Account Manager". DataTableById(1) .SqlDataFrame .WithClusterColumn("Cluster", #{"NClusters": 5, "Columns": ["Region", "Account Manager"]}) .Collect().ToCsv() |
WithDenseRankNumberColumn (SqlDataFrame) |
|
Same functionality as in the DataFrame. |
WithColumn (SqlDataFrame) |
|
Same functionality as in the DataFrame, except instead of in-memory expressions, SqlDataFrame use SQL Expressions. |
WithRankColumn (SqlDataFrame) |
|
Same functionality as in the DataFrame. |
WithRowNumberColumn (SqlDataFrame) |
|
Same functionality as in the DataFrame. |
Where (SqlDataFrame) | Condition expression | Same functionality as in the DataFrame, except instead of in-memory expressions, SqlDataFrame use SQL Expressions. |