Smart Data Streaming: Using CCLScript Functions in a Query

Here’s a feature you might never have thought to use in your smart data streaming project: CCLScript functions. These functions are incredibly versatile, and can help you accomplish even more with your streaming project. You can use any CCLScript function, even a custom one, within a CCL query’s SELECT or WHERE clause.

There are several CCLScript use-case examples available in the SAP HANA Smart Data Streaming: Examples Guide. For this blog, I’ll be using the CCL function example that is included with smart data streaming, and is covered in the CCL Function topic underneath DECLARE Block Examples.

Calculating a Weighted Average

This simple example uses a basic input window, and an output window with a custom CCLScript function.

First, you create a schema named TradeSchema for use with the input window, TradeWindow:

CREATE SCHEMA TradeSchema (
	Ts BIGDATETIME,
	Symbol STRING,
 	Price MONEY(2),
	Volume INTEGER 
);

Then, use a DECLARE block to create the function MyWeightedAverage, with variables of Value1 and Value2, and a local variable called Weight1:

DECLARE MONEY(2) MyWeightedAverage 
  (MONEY(2) Value1, INTEGER Value2)
{
	FLOAT Weight1 := 1.0;
	
    IF (Value2 > 10000 ) 
      { Weight1 := 0.5; }
    ELSE IF (Value2 > 4000) 
      {Weight1 := 0.75; }
    ELSE IF (Value2 < 100) 
      { Weight1 := 3.0; }
    ELSE IF (Value2 < 500) 
      { Weight1 := 0.25; }
    RETURN to_money(Value1 * Weight1 ,2);
}
END;

Using a series of if and else if conditions, the function determines the value of Weight1 based on whether Value2 is greater or less than the specified values. Then, the resulting Weight1 value is used in the to_money() function of the RETURN statement to calculate the average.

Next, create the input window TradeWindow using TradeSchema, then create the output window OutWeightedAverage:

CREATE INPUT WINDOM TradeWindow
SCHEMA TradeSchema
PRIMARY KEY (Ts);

CREATE OUTPUT WINDOW OutWeightedAverage
	SCHEMA ( Symbol STRING, avgPrice MONEY(2), wavgPrice MONEY(2))
	PRIMARY KEY deduced
	AS
	SELECT 
	    t.Symbol,
	    avg(t.Price) avgPrice,
	    avg(MyWeightedAverage(t.Price, t.Volume)) wavgPrice
	FROM 
	    TradeWindow t
	GROUP BY t.Symbol;

The output window specifies its own inline schema, and uses MyWeightedAverage within the avg() function inside its SELECT statement.

Using the custom CCLScript function MyWeightedAverage within the output window allows the project to output more useful data by handling some of the number processing internally.

Don’t forget to use your own CCLScript functions within queries in your streaming projects to open up more possibilities for data use, and save time spent on external processing. See more examples in the SAP HANA Smart Data Streaming: Examples Guide.