This function is only available when connected to an Aster database.
This SQL element, when selected, may be used to specify a SQL-MR function that was either user-defined or system-supplied.
When a SQL-MapReduce function is dragged onto the Tables work area or otherwise selected, a Function Table that looks like the results in the following figure in this section. Notice that an initial ON Clause SQL element has been placed in the ON Clauses folder. Additional ON Clauses may be specified by dropping one or more additional ON Clause elements onto the ON Clauses folder.
SQL-MapReduce Function utilizes substitution parameters in a manner similar to a SQL Text with Arguments SQL element, replacing <P1> with the SQL generated for the first expression in the Substitution Parameters folder, <P2> for the second, and so forth. By default, substitution parameters are not included with a SQL-MR function. You must check the Include Substitution Parameters checkbox in the SQL-MapReduce Function Properties dialog in order to use substitution parameters with the function.
When dragging a SQL-MR function into a Function Table, the following tree element is created.
- Table, via a Column in the table — Dropping a column onto the (empty) node results in the table that contains that column being used as the input table to the function.
- Table, via a SQL-MR function — Dropping a SQL-MR function onto the (empty) node results in the table created by that SQL-MR function being used as the input table to the operator (thus allowing nesting of SQL-MR functions).
- Query, via a column in an Analysis Table — When any column from an Analysis Table is dropped onto the (empty) node, the SQL from that analysis becomes the query input to the SQL-MR function, provided that Gen SQL Only is checked for the referenced analysis (thus allowing chaining of Analyses).
- Query from a SQL Text or SQL Text with Arguments element — A SQL Text or SQL Text with Arguments element can be used to specify a query that represents the input table to the SQL-MR function (e.g., when the input is “Select 1”).
To view or set the properties of the SQL-MR function, double-click on SQL-MR Function or highlight it and click Properties.
- Function Name — The name of the SQL-MR function to be invoked. The desired value may either be typed into this field or selected from the pull-down list. The pull-down list is populated by issuing the appropriate Aster command.
- Schema Name — The name of the schema where the SQL-MR function is installed. The desired value may be typed into this field.
-
Arguments — The Arguments tab contains the available arguments for the selected function in one of three formats. If the JSON description file for the function is found in the <TWM Installation Folder>\Scripts\Aster\JSON folder, then the arguments are displayed in a grid as shown in the Variable Creation > Input > Variables: SQL Elements pane - Table Functions figure. Required arguments are checked and cannot be unchecked. Optional arguments can be checked and the Value field edited as desired. Hover-over information is available for each argument, as well as default values (if available). Arguments with specific permitted values will display those values in a drop-down list. If a JSON file is not found, then the AsterFunctions.xml file will be searched for by the function and if found will display the arguments in an editable text area. Optional arguments will have square brackets around them (e.g., [ACCUMULATE('col_name')]), while required arguments will not. If the function is not found in either the JSON or XML file, then the arguments text area will be blank. See the Usage tab for the valid arguments to enter, if any.Column names can be selected, dragged and dropped from the Columns list in the Variable Creation window to the Value field of an argument that has its Use box checked.
- Omit Schema In Call — When checked, this option causes the operator name to appear without the containing schema name preceding it in the SQL generated for this operator. In this case, upon invocation of the operator in generated SQL, the database software will first search for the operator in the current schema (either the default schema of the data source connection or the last issued DATABASE command).
- Include Substitution Parameters — When checked, usage of substitution parameters in the argument values is allowed in a manner similar to a SQL Text with Arguments SQL element, replacing <P1> with the SQL generated for the first expression in the Substitution Parameters folder, <P2> for the second, etc. The Substitution Parameters folder is added to the Function Table tree once the OK or Apply button is clicked in the Properties dialog.