Snowflake UDF

[lambrosetti]

Hi Niels,

Any plans on creating a Snowflake UDF? https://docs.snowflake.com/en/developer-guide/udf/java/udf-java.html

If not, that's okay, I might try it myself soon. If you were already planning on it, then I didn't want to duplicate efforts.

Thanks,

Luke

[nielsbasjes]

At this point I hadn't thought of adding this because I didn't know about this system.
Seems like a perfectly valid addition to the list of UDFs.

Now since this library does use ~200MiB of RAM I'm curious to see if this is allowed; this page is not clear to me on this subject: https://docs.snowflake.com/en/developer-guide/udf/java/udf-java-designing.html#label-udf-java-limitations

I'll have a look at the documentation

[nielsbasjes]

Looking at this documentation https://docs.snowflake.com/en/developer-guide/udf/java/udf-java-creating.html#label-udf-java-in-line-examples the "inline" java UDF may be a simply quick way for you to use it?

I do not have any way of testing this (so this WILL have mistakes I cannot verify) but it seems that something like this may work:

create or replace function parse_useragent(useragent VARCHAR)
returns variant
language java
handler='SnowflakeUseragentParser.parse'
target_path='@~/SnowflakeUseragentParser.jar'
as
$$
import nl.basjes.parse.useragent.UserAgentAnalyzer;

import java.util.Map;

class SnowflakeUseragentParser {
    private static final UserAgentAnalyzer ANALYZER = UserAgentAnalyzer.newBuilder().dropTests().immediateInitialization().build();
    public static Map<String, String> parse(String useragent) {
        return ANALYZER.parse(useragent).toMap();
    }
}
$$;

I would really appreciate it if you can verify this.
If this works I think that just adding a page to the documentation should be sufficient.

[lambrosetti]

Thanks for the quick response!

I didn't think this would work because your UserAgentAnalyzer is not a part of core java, but I wanted to try it out anyway. Here was the full error message that I received:

Error while compiling source: /InlineCode.java:2: error: package nl.basjes.parse.useragent does not exist import nl.basjes.parse.useragent.UserAgentAnalyzer; ^ /InlineCode.java:7: error: cannot find symbol private static final UserAgentAnalyzer ANALYZER = UserAgentAnalyzer.newBuilder().dropTests().immediateInitialization().build(); ^ symbol: class UserAgentAnalyzer location: class SnowflakeUseragentParser /InlineCode.java:7: error: cannot find symbol private static final UserAgentAnalyzer ANALYZER = UserAgentAnalyzer.newBuilder().dropTests().immediateInitialization().build(); ^ symbol: variable UserAgentAnalyzer location: class SnowflakeUseragentParser 3 errors in function PARSE_USERAGENT with handler SnowflakeUseragentParser.parse

When I have some more time next week, I'll see if I can compile my own version of the in-line code that you gave me to use as a pre-compiled UDF. Also, I don't think memory should be an issue - I'm assuming that Snowflake is giving an extremely basic example here for documentation purposes, but we shall see!

[nielsbasjes]

Yes, this error makes sense. You must put the Yauaa jar file somewhere that Snowflake can find it. With that in place it may just work.
Looking forward to the results of your tests.

[lambrosetti]

Hi Niels,

Decided to go ahead and give this a try this evening and I was able to get this working! It took some tinkering with maven (I'm not a Java developer), but because Snowflake forces you to pre-compile everything and upload as 1 jar, I did the following steps:

• Created a snowflake module in udfs
• Took your code snippet from above and created Snowflake's ParseUserAgent.java (and added a line for the package, because apparently Snowflake forces you to)
• Copied Hive pom.xml and assembly and changed pom to remove Hive dependencies (Hive seemed like the easiest one to reverse-engineer)
• Packaged just the snowflake module (and dependencies)

The only other minor thing is that Snowflake is actually going to return an object instead of a variant.

create or replace function parse_useragent(useragent VARCHAR)
returns object
language java
imports = ('@cs_stage/snowflake-6.2-SNAPSHOT-udf.jar')
handler='nl.basjes.parse.useragent.snowflake.UserAgentPkg.ParseUserAgent.parse';

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;

I'll need to do some actual load testing, but the 2-3 seconds of compute seem in-line with what you've mentioned in your documentation.

Thanks very much for the help here - I really appreciate it!

[nielsbasjes]

I'm going to try to repeat what you did.
If I can make that to work you can just download a "ready to run" latest version when I release a new one.
My questions to you:

• Is it ok with you if I use your screenshot in the documentation?
• Would you please review the documentation and test it when I have something?

[nielsbasjes]

A did a first attempt to build this at my end.
I would appreciate it if you could test if this works.

The code is in a separate branch SnowflakeUDF
The UDF code:
https://github.com/nielsbasjes/yauaa/tree/SnowflakeUDF/udfs/snowflake
The documentation:
https://github.com/nielsbasjes/yauaa/tree/SnowflakeUDF/documentation/content/udf/Snowflake

You can download the binary here:
https://oss.sonatype.org/content/repositories/snapshots/nl/basjes/parse/useragent/yauaa-snowflake/6.2-SNAPSHOT/

[lambrosetti]

I'm happy to provide screenshots and provide whatever else needed for documentation.

[nielsbasjes]

Yes, some screenshots would be nice.
Also please check if the documentation is correct.
Thanks!

[lambrosetti]

The only change I would have to the documentation:

Download the UDF and put it in the right directory so Snowflake can load it.

Should be something like the following for Snowflake:

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

Attached is a new screenshot showing the function creation as well:

Lastly, thanks again for making this happen! I also appreciate the shoutout in the documentation. I'm writing up a Snowflake version of this dbt guide that was created for Redshift (https://discourse.getdbt.com/t/how-to-create-a-series-of-python-udfs-for-parsing-user-agents-in-redshift/159) - I'll be linking directly to your documentation from the new page.

[nielsbasjes]

I released 6.2 just now. So this UDF should arrive at maven central in an hour or so.

[nielsbasjes]

Thanks Luke! I really appreciate what you sent.