Presto hash function (SHA1) UDF(s)

ExtendedHashFunctions.java

package com.company.udfs.scalar;

import com.facebook.presto.spi.function.Description;
import com.facebook.presto.spi.function.ScalarFunction;
import com.facebook.presto.spi.function.SqlType;
import com.facebook.presto.spi.type.StandardTypes;
import com.google.common.hash.Hashing;
import com.google.common.io.BaseEncoding;
import io.airlift.slice.Slice;
import io.airlift.slice.Slices;

public class ExtendedHashFunctions {

    private ExtendedHashFunctions() {
    }

    // MD5
    @ScalarFunction("md5")
    @Description("Converts value to its MD5 digest")
    @SqlType(StandardTypes.VARCHAR)
    public static Slice md5(@SqlType(StandardTypes.BIGINT) long value) {
        return Slices.utf8Slice(BaseEncoding.base16().encode(Hashing.md5().hashBytes(Long.toString(value).getBytes()).asBytes()));
    }

    @ScalarFunction("md5")
    @Description("Converts value to its MD5 digest")
    @SqlType(StandardTypes.VARCHAR)
    public static Slice md5(@SqlType(StandardTypes.DOUBLE) double value) {
        return Slices.utf8Slice(BaseEncoding.base16().encode(Hashing.md5().hashBytes(Double.toString(value).getBytes()).asBytes()));
    }

    @ScalarFunction("md5")
    @Description("Converts value to its MD5 digest")
    @SqlType(StandardTypes.VARCHAR)
    public static Slice md5(@SqlType(StandardTypes.VARCHAR) Slice value) {
        return Slices.utf8Slice(BaseEncoding.base16().encode(Hashing.md5().hashBytes(value.getBytes()).asBytes()));
    }

    // SHA1
    @ScalarFunction("sha1")
    @Description("Converts value to its SHA1 digest")
    @SqlType(StandardTypes.VARCHAR)
    public static Slice sha1(@SqlType(StandardTypes.BIGINT) long value) {
        return Slices.utf8Slice(BaseEncoding.base16().encode(Hashing.sha1().hashBytes(Long.toString(value).getBytes()).asBytes()));
    }

    @ScalarFunction("sha1")
    @Description("Converts value to its SHA1 digest")
    @SqlType(StandardTypes.VARCHAR)
    public static Slice sha1(@SqlType(StandardTypes.DOUBLE) double value) {
        return Slices.utf8Slice(BaseEncoding.base16().encode(Hashing.sha1().hashBytes(Double.toString(value).getBytes()).asBytes()));
    }

    @ScalarFunction("sha1")
    @Description("Converts value to its SHA1 digest")
    @SqlType(StandardTypes.VARCHAR)
    public static Slice sha1(@SqlType(StandardTypes.VARCHAR) Slice value) {
        return Slices.utf8Slice(BaseEncoding.base16().encode(Hashing.sha1().hashBytes(value.getBytes()).asBytes()));
    }

    // SHA256
    @ScalarFunction("sha256")
    @Description("Converts value to its SHA256 digest")
    @SqlType(StandardTypes.VARCHAR)
    public static Slice sha256(@SqlType(StandardTypes.BIGINT) long value) {
        return Slices.utf8Slice(BaseEncoding.base16().encode(Hashing.sha256().hashBytes(Long.toString(value).getBytes()).asBytes()));
    }

    @ScalarFunction("sha256")
    @Description("Converts value to its SHA256 digest")
    @SqlType(StandardTypes.VARCHAR)
    public static Slice sha256(@SqlType(StandardTypes.DOUBLE) double value) {
        return Slices.utf8Slice(BaseEncoding.base16().encode(Hashing.sha256().hashBytes(Double.toString(value).getBytes()).asBytes()));
    }

    @ScalarFunction("sha256")
    @Description("Converts value to its SHA256 digest")
    @SqlType(StandardTypes.VARCHAR)
    public static Slice sha256(@SqlType(StandardTypes.VARCHAR) Slice value) {
        return Slices.utf8Slice(BaseEncoding.base16().encode(Hashing.sha256().hashBytes(value.getBytes()).asBytes()));
    }
}

ExtendedHashFunctionsTest.java

package com.company.udfs.scalar;

import com.facebook.presto.operator.scalar.FunctionAssertions;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

import static com.facebook.presto.spi.type.VarcharType.VARCHAR;

public class ExtendedHashFunctionsTest {

    private FunctionAssertions functionAssertions;

    @BeforeClass
    public void setUp() {
        functionAssertions = new FunctionAssertions().addScalarFunctions(ExtendedHashFunctions.class);
    }

    // MD5
    @Test
    public void testMd5Integers() {
        // negative numbers
        functionAssertions.assertFunction("MD5(-9223372036854775807)", VARCHAR, "AD2AE9A93702D215536D4CA99A7D4852");
        functionAssertions.assertFunction("MD5(-2147483647)", VARCHAR, "FCF03F174C4EA5DBD76708560B590755");
        functionAssertions.assertFunction("MD5(-1)", VARCHAR, "6BB61E3B7BCE0931DA574D19D1D82C88");

        // zero
        functionAssertions.assertFunction("MD5(-0)", VARCHAR, "CFCD208495D565EF66E7DFF9F98764DA");
        functionAssertions.assertFunction("MD5(0)", VARCHAR, "CFCD208495D565EF66E7DFF9F98764DA");
        functionAssertions.assertFunction("MD5(+0)", VARCHAR, "CFCD208495D565EF66E7DFF9F98764DA");

        // positive numbers
        functionAssertions.assertFunction("MD5(1)", VARCHAR, "C4CA4238A0B923820DCC509A6F75849B");
        functionAssertions.assertFunction("MD5(2147483647)", VARCHAR, "C588C0A459F4CCC6F3DD26518D24707A");
        functionAssertions.assertFunction("MD5(9223372036854775807)", VARCHAR, "15767B252275CF5107BBA9267B88E787");
    }

    @Test
    public void testMd5Reals() {
        // reals
        functionAssertions.assertFunction("MD5(3.14)", VARCHAR, "4BEED3B9C4A886067DE0E3A094246F78");
        functionAssertions.assertFunction("MD5(6.023E23)", VARCHAR, "054E49FD8DC59869ADE891AD0F956B89");
        functionAssertions.assertFunction("MD5(-6.67e-11)", VARCHAR, "9090D1D0D722CCAD77747BD1F30817CE");
        functionAssertions.assertFunction("MD5(-6.67E-11)", VARCHAR, "9090D1D0D722CCAD77747BD1F30817CE");
    }

    @Test
    public void testMd5Strings() {
        // strings
        functionAssertions.assertFunction("MD5('[email protected]')", VARCHAR, "8A3F490D1968ACF3C70178F0BF6D45EB");
        functionAssertions.assertFunction("MD5('[email protected]')", VARCHAR, "C3AF2CBDDE2C3F9FC9A5B4859AAAC7F9");
        functionAssertions.assertFunction("MD5('')", VARCHAR, "D41D8CD98F00B204E9800998ECF8427E");
    }

    // SHA1
    @Test
    public void testSha1Integers() {
        // negative numbers
        functionAssertions.assertFunction("SHA1(-9223372036854775807)", VARCHAR, "799287FF4423FDC0397767E4E85E2C855128404A");
        functionAssertions.assertFunction("SHA1(-2147483647)", VARCHAR, "B177929C4A7560C473CA55EBED0944A8B71C237C");
        functionAssertions.assertFunction("SHA1(-1)", VARCHAR, "7984B0A0E139CABADB5AFC7756D473FB34D23819");

        // zero
        functionAssertions.assertFunction("SHA1(-0)", VARCHAR, "B6589FC6AB0DC82CF12099D1C2D40AB994E8410C");
        functionAssertions.assertFunction("SHA1(0)", VARCHAR, "B6589FC6AB0DC82CF12099D1C2D40AB994E8410C");
        functionAssertions.assertFunction("SHA1(+0)", VARCHAR, "B6589FC6AB0DC82CF12099D1C2D40AB994E8410C");

        // positive numbers
        functionAssertions.assertFunction("SHA1(1)", VARCHAR, "356A192B7913B04C54574D18C28D46E6395428AB");
        functionAssertions.assertFunction("SHA1(2147483647)", VARCHAR, "75878664309B1E12165B0823315376B0D27E4F80");
        functionAssertions.assertFunction("SHA1(9223372036854775807)", VARCHAR, "458B642B137E2C76E0B746C6FA43E64C3D4C47F1");
    }

    @Test
    public void testSha1Reals() {
        // reals
        functionAssertions.assertFunction("SHA1(3.14)", VARCHAR, "983B34771FB7185D04C9603559C9E46F4B70496D");
        functionAssertions.assertFunction("SHA1(6.023E23)", VARCHAR, "EBC757DC01FF06D94B208859D72F442858EE8F8C");
        functionAssertions.assertFunction("SHA1(-6.67e-11)", VARCHAR, "CCF898E38DF82B49F403A1AE09491E945E15765B");
        functionAssertions.assertFunction("SHA1(-6.67E-11)", VARCHAR, "CCF898E38DF82B49F403A1AE09491E945E15765B");
    }

    @Test
    public void testSha1Strings() {
        // strings
        functionAssertions.assertFunction("SHA1('[email protected]')", VARCHAR, "C8A04148307F4F0027DE9CE822AF553648AE581F");
        functionAssertions.assertFunction("SHA1('[email protected]')", VARCHAR, "23E5C674FD72067A35DA7E7BC39C667E4253FCAB");
        functionAssertions.assertFunction("SHA1('')", VARCHAR, "DA39A3EE5E6B4B0D3255BFEF95601890AFD80709");
    }

    // SHA256
    @Test
    public void testSha256Integers() {
        // negative numbers
        functionAssertions.assertFunction("SHA256(-9223372036854775807)", VARCHAR, "B7AE81320053F61245ED2D36E72E1D05AD4235D8C784E60285F1EB1F06DA7845");
        functionAssertions.assertFunction("SHA256(-2147483647)", VARCHAR, "BAC58860F09A144C19F0DFA1FEF10A70B82A18E7EAEB8E06BA2ECF21123D3BB4");
        functionAssertions.assertFunction("SHA256(-1)", VARCHAR, "1BAD6B8CF97131FCEAB8543E81F7757195FBB1D36B376EE994AD1CF17699C464");

        // zero
        functionAssertions.assertFunction("SHA256(-0)", VARCHAR, "5FECEB66FFC86F38D952786C6D696C79C2DBC239DD4E91B46729D73A27FB57E9");
        functionAssertions.assertFunction("SHA256(0)", VARCHAR, "5FECEB66FFC86F38D952786C6D696C79C2DBC239DD4E91B46729D73A27FB57E9");
        functionAssertions.assertFunction("SHA256(+0)", VARCHAR, "5FECEB66FFC86F38D952786C6D696C79C2DBC239DD4E91B46729D73A27FB57E9");

        // positive numbers
        functionAssertions.assertFunction("SHA256(1)", VARCHAR, "6B86B273FF34FCE19D6B804EFF5A3F5747ADA4EAA22F1D49C01E52DDB7875B4B");
        functionAssertions.assertFunction("SHA256(2147483647)", VARCHAR, "972DCAFA6FB4C2C88BCE752FCA4AB18C6BD88599330A4AD9813915B05BFBE76D");
        functionAssertions.assertFunction("SHA256(9223372036854775807)", VARCHAR, "B34A1C30A715F6BF8B7243AFA7FAB883CE3612B7231716BDCBBDC1982E1AED29");
    }

    @Test
    public void testSha256Reals() {
        // reals
        functionAssertions.assertFunction("SHA256(3.14)", VARCHAR, "2EFFF1261C25D94DD6698EA1047F5C0A7107CA98B0A6C2427EE6614143500215");
        functionAssertions.assertFunction("SHA256(6.023E23)", VARCHAR, "F6AAD5B28FE14F464C567828F6F133C203024A0A58AD9C435F7E730347FAFAEC");
        functionAssertions.assertFunction("SHA256(-6.67e-11)", VARCHAR, "70AAAC604FB82F5A6766AF34058A72FFDD0B62DD873BB6F60F23022D8A67883A");
        functionAssertions.assertFunction("SHA256(-6.67E-11)", VARCHAR, "70AAAC604FB82F5A6766AF34058A72FFDD0B62DD873BB6F60F23022D8A67883A");
    }

    @Test
    public void testSha256Strings() {
        // strings
        functionAssertions.assertFunction("SHA256('[email protected]')", VARCHAR, "532CCB78AC241B2971FA2E7BE3632CCFA023C684EC3B224731F3FA3B9D9E8CAC");
        functionAssertions.assertFunction("SHA256('[email protected]')", VARCHAR, "354316069E69CBDFD95B67A78C0E5E8C95EE7E1FC88E595F1CF56409B2DB90D7");
        functionAssertions.assertFunction("SHA256('')", VARCHAR, "E3B0C44298FC1C149AFBF4C8996FB92427AE41E4649B934CA495991B7852B855");
    }
}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>company-plugin</artifactId>
        <groupId>com.company.plugin</groupId>
        <version>1.1.0</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>udfs</artifactId>
    <version>1.0.0</version>
    <packaging>jar</packaging>
    <name>Presto-UDFs</name>

    <dependencies>
        <dependency>
            <groupId>com.facebook.presto</groupId>
            <artifactId>presto-main</artifactId>
            <version>${presto.version}</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>io.airlift</groupId>
            <artifactId>slice</artifactId>
            <version>0.36</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>io.airlift</groupId>
            <artifactId>testing</artifactId>
            <version>0.172</version>
            <scope>provided</scope>
        </dependency>

        <!-- for testing -->
        <dependency>
            <groupId>com.facebook.presto</groupId>
            <artifactId>presto-main</artifactId>
            <version>${presto.version}</version>
            <type>test-jar</type>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

UdfsPlugin.java

package com.company.udfs;

import com.facebook.presto.spi.Plugin;
import com.google.common.collect.ImmutableSet;
import com.company.udfs.scalar.ExtendedHashFunctions;

import java.util.Set;

public class UdfsPlugin implements Plugin {

    public Set<Class<?>> getFunctions() {
        return ImmutableSet.<Class<?>>builder().
                add(ExtendedHashFunctions.class).
                build();
    }
}