New in Snowflake: Java UDFs (with a Kotlin NLP example)

Snowflake now let’s you easily create Java UDFs, which is an incredibly powerful and versatile feature. Let’s check it out with by running a library written in Kotlin — to detect written languages. Out of GitHub and into your SQL code, in 3 easy steps.

Felipe Hoffa
Jun 16 · 3 min read
Detect written languages with a Java UDF

Quick example: Detect written languages

create function add(x integer, y integer)
returns integer
language java
handler='Test.add'
as
$$
class Test {
public static int add(int x, int y) {
return x + y;
}
}
$$;

Then you can use that function in your SQL statements:

select add(1, 3)// 4

That’s easy — and then the real power comes with the ability to load and use jar packages. For example, to detect written languages using Lingua:

create or replace function detect_lang(x string)
returns string
language java
imports = ('@~/lingua-1.1.0-with-dependencies.jar')
handler='MyClass.detect'
as
$$
import com.github.pemistahl.lingua.api.*;
import static com.github.pemistahl.lingua.api.Language.*;
class MyClass {
static LanguageDetector detector = LanguageDetectorBuilder.fromLanguages(ENGLISH, FRENCH, GERMAN, SPANISH).build();
public static String detect(String x) {
return detector.detectLanguageOf(x).toString();
}
}
$$;

If you have the right jar staged into your Snowflake account, that’s all it takes:

select $1, detect_lang($1)
from values('languages are awesome'),('hola mi amigo'),('hallo Freunde'),('je ne parle pas');

Notes

Setup

To package this library into a single fat jar for Snowflake, I followed these steps:

  1. Clone the git project
  2. Build a jar with dependencies with Gradle.
git clone https://github.com/pemistahl/lingua.git
./gradlew jarWithDependencies

Then you can PUT this jar into your Snowflake account with SnowSQL:

put 'file://build/libs/lingua-1.1.0-with-dependencies.jar' @~;

Read more

Acknowledgements

Thanks to Peter M. Stahl for the awesome Lingua library:

Want more?

I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter and LinkedIn, and check reddit.com/r/snowflake for the most interesting Snowflake news.

Snowflake

Articles for engineers, by engineers.

Snowflake

Snowflake articles from engineers using Snowflake to power their data.

Felipe Hoffa

Written by

Data Cloud Advocate at Snowflake ❄️. Originally from Chile, now in San Francisco and around the world. Previously at Google. Let’s talk data.

Snowflake

Snowflake articles from engineers using Snowflake to power their data.