vertica batch

Python UDXs are cool!

There are several Python UDX examples here:
https://github.com/vertica/UDx-Examples/tree/master/Python

The next post in this thread has the code for a Python UDX that implements the interesting Jaro–Winkler distance string metric...

See:
https://en.wikipedia.org/wiki/Jaro–Winkler_distance

To install it:

dbadmin=> CREATE LIBRARY jaro_winkler_lib AS '/home/dbadmin/jaro_winkler.py' LANGUAGE 'Python';
CREATE LIBRARY

dbadmin=> CREATE FUNCTION jaro_winkler AS LANGUAGE 'Python' NAME 'jaro_winkler_factory' LIBRARY jaro_winkler_lib;
CREATE FUNCTION

dbadmin=> GRANT EXECUTE ON FUNCTION jaro_winkler(varchar, varchar, int) TO public;
GRANT PRIVILEGE

dbadmin=> select jaro_winkler('jellyfish', 'smellyfish', 1);
jaro_winkler
--------------
0.8962962963
(1 row)

import vertica_sdk
import decimal

class jaro_winkler(vertica_sdk.ScalarFunction):
    def __init__(self):
        pass

    def setup(self, server_interface, col_types):
        pass

    def processBlock(self, server_interface, arg_reader, res_writer):
        server_interface.log(" $$$ ")

        while(True):
            if arg_reader.isNull(0) or arg_reader.isNull(1):
                result = decimal.Decimal(0.0)
            else:
                s1 = arg_reader.getString(0)
                s2 = arg_reader.getString(1)

                s1_len = len(s1)
                s2_len = len(s2)

                match_distance = (max(s1_len, s2_len) // 2) - 1

                s1_matches = [False] * s1_len
                s2_matches = [False] * s2_len

                matches = 0
                transpositions = 0

                for i in range(s1_len):
                    start = max(0, i-match_distance)
                    end = min(i+match_distance+1, s2_len)

                    for j in range(start, end):
                        if s2_matches[j]:
                            continue
                        if s1[i] != s2[j]:
                            continue
                        s1_matches[i] = True
                        s2_matches[j] = True
                        matches += 1
                        break
                    if matches == 0:
                        result = decimal.Decimal(0.0)
                    else:
                        k = 0
                        for i in range(s1_len):
                            if not s1_matches[i]:
                                continue
                            while not s2_matches[k]:
                                k += 1
                            if s1[i] != s2[k]:
                                transpositions += 1
                            k += 1

                        result = decimal.Decimal(((matches / s1_len) + (matches / s2_len) + ((matches - transpositions/2) / matches)) / 3)

            # If winkler = 1
            if arg_reader.getInt(2) == 1:
                max_len = min(len(s1), len(s2))
                for i in range(0, max_len):
                    if not s1[i] == s2[i]:
                        max_len = i
                        break

                if max_len > 4:
                  max_len = 4
                else:
                  max_len = max_len

                result = result + (max_len * decimal.Decimal(0.1) * (1 - result))

            res_writer.setNumeric(result)
            res_writer.next()
            if not arg_reader.next():
                break

    def destroy(self, server_interface, col_types):
        pass

class jaro_winkler_factory(vertica_sdk.ScalarFunctionFactory):

    def createScalarFunction(self, srv):
        return jaro_winkler()

    def getPrototype(self, srv_interface, arg_types, return_type):
        arg_types.addVarchar()
        arg_types.addVarchar()
        arg_types.addInt()
        return_type.addNumeric()

    def getReturnType(self, srv_interface, arg_types, return_type):
        return_type.addNumeric(11,10)