Snowflake

Introduction

User Defined Function for Snowflake.

STATUS: … EXPERIMENTAL …

The Snowflake UDF is very experimental for two reasons:

  • Snowflake has marked (last checked on 2021-11-07) Java based UDFs as a Preview Feature.
  • I do not have Snowflake so I do not have any way of testing this other than getting feedback from you.

Thanks to Luke Ambrosetti for helping out here!

See for more information:

Installation and usage

  1. Download the UDF jar to the local file system and upload into a Snowflake internal or external stage.

    You can get the prebuilt UDF from maven central.

  2. Register the function in Snowflake with something like this:

create or replace function parse_useragent(useragent VARCHAR)
returns object
language java
imports = ('@cs_stage/yauaa-snowflake-6.3-udf.jar')
handler='nl.basjes.parse.useragent.snowflake.ParseUserAgent.parse';
  1. And from there you can use it as a function in your SQL statements
select parse_useragent('Mozilla/5.0 (Linux; Android 7.0; Nexus 6 Build/NBD90Z) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/53.0.2785.124 Mobile Safari/537.36') as ua_obj, ua_obj:AgentClass::string as agent_class;

Using Yauaa in Snowflake