CREATE PARAMETERS Statement

Syntax 

CREATE PARAMETERS '('parameter_name DEFAULT "initial_value"[,...]
  ')';

Substitutable Fields

parameter_name

A unique identifier (name) for the parameter.

initial_value

The default, or initial, value for the parameter. The entry must be enclosed within within quotation marks ("initial_value") regardless of its type. String types must also be enclosed within escaped quotation marks ("\"initial_value\""). Multiple parameters can be declared within one CREATE PARAMETERS statement; separate each parameter declaration with a comma. The parentheses are a required part of the syntax.

Discussion

In StreamSQL, a parameter is considered to be a named character string that can be referenced at other places in the StreamSQL document through its assigned name. Consequently, the default, or initial, value assigned to the parameter should be enclosed within quotation marks as illustrated in the following example. 

CREATE PARAMETERS (parameter_name DEFAULT "value");

If the parameter is a string type, then value must also be enclosed within escaped quotation marks. 

CREATE PARAMETERS (parameter_name DEFAULT "\"value\"");

By following these conventions, parameters of all types can be referenced using a common syntax - ${parameter_name}.

A parameter in StreamSQL can represent a value, a constant, a literal, or a character string that can be used in a StreamSQL expression or predicate or as a StreamSQL statement.

Notes

Expression parameters are distinct from both module parameters, which are described in Using Module Parameters, and StreamSQL CREATE PARAMETER statements:

StreamSQL parameters are distinct from both module parameters (described in Using Module Parameters), and expression parameters (described in the section, Parameters in Expressions, in StreamBase Expression Language and Functions):

  • A module parameter is defined by using an APPLY STATEMENT in a parameterized module, Java operator or an embedded Java adapter, not using a CREATE PARAMETER statement.

  • You can use both parameter types, but if there is a name conflict, the module parameter value takes precedence.

  • Expression parameters are not defined at the application level, but globally in the server's sbd.sbconf configuration file.

  • Both module parameters and expression parameters can only be used in expressions. StreamSQL parameters are more flexible: they can be used as expressions as well as other parts of a statement.

For example, the parameter declaration: 

CREATE PARAMETERS (max_value DEFAULT "10000");

Can be used in an expression 

SELECT (if tuple_field < ${max_value}) then tuple_field else ${max_value} AS alias_name ...

Or in a predicate 

SELECT ...
      WHERE tuple_field < ${max_value} ...

And a string parameter type could be used as follows. 

CREATE PARAMETERS (preface DEFAULT "\"Hello \"");
CREATE INPUT STREAM in (name string(10));

SELECT ${preface} + name AS greeting
FROM in => CREATE OUTPUT STREAM out;

This example would emit a tuple with a string field named greeting that contains the content Hello name.

Additionally, a parameter can contain another StreamSQL statement, which can then be used within the StreamSQL document. For example, consider the following module: 

CREATE PARAMETERS (in_stream DEFAULT "CREATE INPUT STREAM",
  in_stream_name DEFAULT "in",
  out_stream_name DEFAULT "out");

  ${in_stream} ${in_stream_name} (name string(10), age int);
  SELECT * FROM ${in_stream_name} => CREATE OUTPUT STREAM ${out_stream_name};

Note that CREATE INPUT STREAM, in, and out are treated as character strings (enclosed in quotation marks), not as string literals (enclosed in escaped quotation marks).

When a module that uses parameters is referenced from another module, the referencing module can override the default (initial) parameter values by including a parenthesized list of parameter_name=value entries within the APPLY clause. As in the CREATE PARAMETER statement, value must be enclosed within quotation marks ("value") and strings must also be enclosed within escaped quotation marks ("\"value\""). In the following example, the APPLY statement is used to reference the module1.ssql StreamSQL module. Note that only one of the parameters (in_stream_name) was overridden from the referencing module. 

CREATE INPUT STREAM i (name string(10), age int);
CREATE OUTPUT STREAM o;

APPLY MODULE "module1.ssql" (in_stream_name = "myInStream")
 FROM i INTO o;

Related Topics

Back to Top ^