Discover how to implement a UDF (User-defined function) in Snowflake with Kronos by codbex, calculating leave request days and factoring in holidays from OpenHolidays API.
Prerequisites
To implement the UDF logic you will need to deploy a Kronos instance on Snowflake.
Here you can find a blog which will help you with this task.
Implementation steps
Follow the steps below or watch the recorded video.
- Create a Snowflake UDF project
- open Kronos and login
- at the
Welcome
view search forSnowflake
and selectSnowflake Function API
template. If the view is missing -Window
->Show View
->Welcome
- type project and file name (service name) - for example
leave-request-days
andleave-request-days-service
- click on
Ok
button - a simple Snowflake Function project will be automatically generated for you
- Let’s see what was generated
api/leave-request-days-service.ts
is a simple REST service which complies with the Snowflake UDF specification. This service will be called when the UDF is used.api/function-data-dto.ts
describes the REST service parameter and result typessecurity/api-constraints.access
allows calling the generated service without authentication from Kronos side
- Define a table in our application which will hold the leave requests
- create folder named
db
- create file
leave-requests.table
in folderdb
- right-click on the file and select
Open With
->Code Editor
- paste this content
- save the file
- create folder named
- Add sample data to the
LEAVE_REQUESTS
table- Define data
- create file
LEAVE_REQUESTS.csv
in folderdb
- right-click on the file and select
Open With
->Code Editor
- paste this content
- save the file
- create file
- Configure the CSV import
- create file
sample-data.csvim
in folderdb
- right-click on the file and select
Open With
->Code Editor
- paste this content
- save the file
- create file
- Publish the project by clicking on
Publish All
button - After a few seconds, open the
Database
perspective and check whether the table and its data have been created in theSNOWFLAKE
data source
SELECT * FROM LEAVE_REQUESTS;
You may need to click on theRefresh
button to see the newly created table.
- Define data
- Implement the logic for leave request days calculation. In folder
api
:- create data util file
date-util.ts
with this content - add OpenHolidays API client in file
open-holidays-api-client.ts
which returns all holidays for a given period. Here is the content of the file. - let’s use the API client in the UDF logic located in the
leave-request-days-service.ts
file by replacing its content with this - save all files and publish the changes
- create data util file
- Define Snowflake UDF
- go to
Database
perspective (alternatively, you can use a Snowflake worksheet) - select
SNOWFLAKE
data source - create function
calculate_leave_request_days
using the following sqlUSE ROLE CONTAINER_USER_ROLE; USE DATABASE CONTAINER_HOL_DB; CREATE OR REPLACE FUNCTION calculate_leave_request_days (countryIsoCode string, fromDate date, toDate date) RETURNS integer SERVICE=codbex_kronos ENDPOINT='app-endpoint' AS '/public/ts/leave-request-days/api/leave-request-days-service.ts';
It is important that
SERVICE
andENDPOINT
match the configurations of your Kronos instance. Also, make sure that the REST path inAS
matches your implementation.
CREATE FUNCTION
reference here.
- go to
Test implemented Snowflake UDF
We have the following sample data in table LEAVE_REQUESTS
.
Let’s use the created function to calculate the number of leave request days for our entries.
Execute the following sql to calculate column REQUESTED_DAYS
UPDATE LEAVE_REQUESTS
SET REQUESTED_DAYS = calculate_leave_request_days(COUNTRY_ISO_CODE, FROM_DATE, TO_DATE);
Now, let’s see whether the days were calculated correctly.
SELECT * FROM LEAVE_REQUESTS;
According to the German public holidays calendar for December 2024, it seems that the implemented UDF is working.
Congratulations, you have implemented Snowflake UDF using Kronos!
Summary
Using Kronos you can
- easily implement simple and complex Snowflake UDFs
- use languages like TypeScript, JavaScript or ABAP in your implementation
- use the comprehensive codbex SDK which uses different modern open source projects for messaging, jobs scheduling, REST, OData, mails etc.
- benefit from the codbex platform, tooling and modules
The project we implemented can be found in this GitHub repository.
I hope you enjoyed this blog. Stay tuned for more great functionality by codbex!
If you have any questions, ideas or want to contribute, feel free to contact us.