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:
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 OUTPUT WINDOW OutWeightedAverage
SCHEMA ( Symbol STRING, avgPrice MONEY(2), wavgPrice MONEY(2))
PRIMARY KEY deduced
avg(MyWeightedAverage(t.Price, t.Volume)) wavgPrice
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.