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:

https://docs.snowflake.com/en/developer-guide/udf/java/udf-java.html

Installation and usage

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 (yauaa-snowflake-7.29.0-udf.jar).
NOTE: You MUST use the -udf.jar: yauaa-snowflake-7.29.0-udf.jar
Register the function in Snowflake with something like this:

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

NOTE: The argument of the UDF was in Yauaa 6 defined as a VARCHAR, it must now be defined as an ARRAY!

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 User-Agent Client Hints

With version 7.0.0 you are now able to analyze the Client Hints aswell.

Note: The arguments to the function are a single array of values!

select parse_useragent(
   ['User-Agent',                   'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.127 Safari/537.36',
    'Sec-Ch-Ua',                    '\" Not A;Brand\";v=\"99\", \"Chromium\";v=\"100\", \"Google Chrome\";v=\"100\"',
    'Sec-Ch-Ua-Arch',               '\"x86\"',
    'Sec-Ch-Ua-Bitness',            '\"64\"',
    'Sec-Ch-Ua-Full-Version',       '\"100.0.4896.127\"',
    'Sec-Ch-Ua-Full-Version-List',  '\" Not A;Brand\";v=\"99.0.0.0\", \"Chromium\";v=\"100.0.4896.127\", \"Google Chrome\";v=\"100.0.4896.127\"',
    'Sec-Ch-Ua-Mobile',             '?0',
    'Sec-Ch-Ua-Model',              '\"\"',
    'Sec-Ch-Ua-Platform',           '\"Linux\"',
    'Sec-Ch-Ua-Platform-Version',   '\"5.13.0\"',
    'Sec-Ch-Ua-Wow64',              '?0']
) as ua_obj, ua_obj:OperatingSystemNameVersion::string as operating_system_name_version;

When only examining the User-Agent this returns Linux ??, with the added information in the Client Hints you should get Linux 5.13.0 instead.

Note that this next form is also supported (the first is the User-Agent, from there it is a list of “header name” and “value”):

select parse_useragent(
    ['Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.127 Safari/537.36',
    'Sec-Ch-Ua',                    '\" Not A;Brand\";v=\"99\", \"Chromium\";v=\"100\", \"Google Chrome\";v=\"100\"',
    'Sec-Ch-Ua-Arch',               '\"x86\"',
    'Sec-Ch-Ua-Bitness',            '\"64\"',
    'Sec-Ch-Ua-Full-Version',       '\"100.0.4896.127\"',
    'Sec-Ch-Ua-Full-Version-List',  '\" Not A;Brand\";v=\"99.0.0.0\", \"Chromium\";v=\"100.0.4896.127\", \"Google Chrome\";v=\"100.0.4896.127\"',
    'Sec-Ch-Ua-Mobile',             '?0',
    'Sec-Ch-Ua-Model',              '\"\"',
    'Sec-Ch-Ua-Platform',           '\"Linux\"',
    'Sec-Ch-Ua-Platform-Version',   '\"5.13.0\"',
    'Sec-Ch-Ua-Wow64',              '?0']
) as ua_obj, ua_obj:OperatingSystemNameVersion::string as operating_system_name_version;