UDFs in KSQL: DateAdd
In Data we trust
KSQL, the SQL engine for streaming data, is a very powerful tool that helps great deals in Streaming Analytics use cases. It comes with a set of functions that could be used to transform, filter or aggregate data and the good thing is that you can extend it easily by implementing and adding your own UDF (User Defined Function) or UDAF (User Defined Aggregate Function). Let’s see how we can do so and add a simple function to KSQL.
The UDF I want to implement here is DATEADD. If you’re familiar with SQL, you have definitely used it: it takes in a date and adds or subtracts a specific number value to a specific part of datetime, and spits out a new datetime.
To implement a User Defined Function (UDF or UDAF) you would need to code your function in Java and then import the jar file in your KSQL server. You can read about the full process here , I point out a couple of things that I believe you should pay attention to:
Make sure you set @UdfDescription and @Udf in your java code properly
Change the versions in pom.xml according to your environment. For example:
Pay attention the data types you can use in your java code. You can use only the following types as parameters or return values of your function:
Java Type
KSQL Type
int
INTEGER
boolean
long
double
String
VARCHAR
List
Array
Map
As I said above, we need to implement our UDF in Java. And let me start talking about the code by saying that I’m not a Java developer. I can code in it, with lots of help from Google, but certainly not the best code optimiser and applier of best practices. So please be gentle:
It’s a very simple function that takes 3 parameters:
date: with a type of long (that represents the number of milliseconds passed since 1/Jan/1970) since KSQL UDFs don’ accept Date data types. You can use KSQL’s TIMESTAMPTOSTRING to convert long or BIGINT values representing dates into readable formats.
period: With the data type of String. This is the period you want to add or subtract from in your date. As you can see in the code, options are Year, Month, Day, Hour, Minute and Second.
amount: of type Int, the amount of time you want to move date back or forth. e.g. 1 month or 23 days
And spits out a new long representation of the new date/time which is the result of applying the amount of periods on date.
To be able to start using the UDF with KSQl you need to deploy it to your KSQL cluster. Steps are listed in the link I mentioned above, this is basically what you need to do:
Compile your code by running following command in the root directory of your Java project:
Take the jar file with “_with-dependencies” postfix to the server where KSQL is running and copy it to “ < path – to – confluent>/etc/ksql/ext”. Make sure “ksql-server.properties point to this location, for example”:
And restart KSQL Server:
Then Fire off KSQL CLI:
And list the functions. DATEADD should be there:
And there you go. Your new UDF is ready to be used.
Our new UDF is ready to be used. Just use like any other function in your KSQL queries, here’s an example:
This function could be useful for period to period comparisons. For example, you’re running a marketing campaign and you want to compare the number of hits on your website from this year with last year when you were running the same campaign.
Or in sales: month to month comparison on how well your sales is going in real time.
Hope this is useful for some of you out there. Like always, feel free to reach out if you had any questions or comments/feedbacks.
Tagged
Confluent
KSQL
Streaming
Streaming Analytics
UDF
User Defined Function
Big and streaming data enthusiast based in Melbourne, Australia and travelling throughout Asia View all posts by Saeed Barghi
barrett808 says: February 28, 2019 at 4:14 pm Great post, thanks for the deets! Reply
Great post, thanks for the deets!
Pingback: Creating a UDF in KSQL – Curated SQL
Pingback: Last Week in Stream Data Integration & Stream Analytics – 4.3.2019 | Enjoy IT - SOA, Java, Event-Driven Computing and Integration
Fill in your details below or click an icon to log in:
You are commenting using your WordPress.com account. ( Log Out / Change )
You are commenting using your Google account. ( Log Out / Change )
You are commenting using your Twitter account. ( Log Out / Change )
You are commenting using your Facebook account. ( Log Out / Change )
Connecting to %s
Notify me of new comments via email.
Notify me of new posts via email.
June 2019 (1)
April 2019 (1)
February 2019 (1)
January 2019 (1)
May 2018 (1)
April 2018 (2)
January 2018 (1)
December 2017 (1)
October 2017 (1)
September 2017 (1)
August 2017 (1)
June 2016 (2)
May 2016 (1)
July 2015 (1)
February 2015 (2)
May 2014 (1)
April 2014 (1)
June 2013 (1)
March 2012 (1)
September 2011 (1)
August 2011 (3)
June 2011 (1)
March 2011 (1)
February 2011 (2)
January 2011 (2)
Enter your email address to subscribe to this blog and receive notifications of new posts by email.
Join 29 other followers
Sign me up!