ClickHouse Java multi-parameter UDF

1. Environment version

Environment	Version
docker clickhouse	22.3.10.22

docker pull clickhouse/clickhouse-server:22.3.10.22

2. XML configuration

2.1 Configuration File

# Create udf configuration file
vim /etc/clickhouse-server/demo_function.xml

<functions>
    <function>
        <type>executable</type>
        <!--udf function name-->
        <name>demo_clickhouse_udf</name>
        <!--Return value type-->
        <return_type>String</return_type>
        <!--Return value name, the default value is result-->
        <!--When the format is JSONEachRow, get the result field in the returned json-->
        <return_name>result</return_name>
        <!--Input parameters-->
        <!--When the format is JSONEachRow, the input parameters are {"argument_1": input parameter 1, "argument_2": input parameter 2}-->
        <argument>
            <type>UInt64</type>
            <name>argument_1</name>
        </argument>
        <argument>
            <type>UInt64</type>
            <name>argument_2</name>
        </argument>
        <!--Data formatting methods of input and output-->
        <format>JSONEachRow</format>
        <!--command running mode, 0 is the specified command, 1 is the default mode-->
        <execute_direct>0</execute_direct>
        <!--command command, it is best to bring the root directory to avoid problems with unsupported functions -->
        <command>/usr/bin/java -jar /var/lib/clickhouse/user_scripts/demo_clickhouse_udf-1.0-SNAPSHOT-jar-with-dependencies.jar</command>
    </function>
</functions>

3. Java code

Create a new Maven project

3.1 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">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>demo_clickhouse_udf</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <dependencies>
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.10.1</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-assembly-plugin</artifactId>
                <version>3.3.0</version>
                <configuration>
                    <archive>
                        <manifest>
                            <mainClass>org.example.Main</mainClass>
                        </manifest>
                    </archive>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
                <executions>
                    <execution>
                        <id>assemble-all</id>
                        <phase>package</phase>
                        <goals>
                            <goal>single</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

3.2 Main.java

package org.example;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.annotations.Expose;
import java.io.BufferedInputStream;
import java.io.DataInputStream;


public class Main {<!-- -->
    public static void main(String[] args) {<!-- -->
        try {<!-- -->
            DataInputStream in = new DataInputStream(new BufferedInputStream(System.in));
            String s;

            //Read data line by line
            while ((s = in.readLine()).length() != 0) {<!-- -->
                // Get input parameters
                Gson gson = new Gson();
                JsonElement jsonElement = gson.fromJson(s, JsonElement.class);
                JsonObject jsonObject = jsonElement.getAsJsonObject();
                String argument_1 = jsonObject.get("argument_1").getAsString();
                String argument_2 = jsonObject.get("argument_2").getAsString();
                //Encapsulate the output results
                String resultStr = new GsonBuilder().excludeFieldsWithoutExposeAnnotation().create().toJson(new Demo(argument_1, argument_2));
                System.out.println(gson.toJson(new Result(resultStr)));
            }
            System.out.flush();
        } catch (Exception e) {<!-- -->
            e.printStackTrace();
        }
    }

    /**
     * Return object
     */
    public static class Demo{<!-- -->
        @Expose
        private String param1;

        @Expose
        private String param2;

        public Demo(String param1, String param2){<!-- -->
            this.param1 = param1;
            this.param2 = param2;
        }

        public String getParam1() {<!-- -->
            return param1;
        }

        public void setParam1(String param1) {<!-- -->
            this.param1 = param1;
        }

        public String getParam2() {<!-- -->
            return param2;
        }

        public void setParam2(String param2) {<!-- -->
            this.param2 = param2;
        }
    }

    /**
     * Return results
     * The return value name must be consistent with the return_name in the xml file
     * xml file return_name default value result
     * That is: when the returned result is "{'result': {...}, 'other': {...}, ...}"
     * Return value The return value of ClickHouse calling function only returns the value of result.
     */
    public static class Result {<!-- -->
        private String result;

        public String getResult() {<!-- -->
            return result;
        }

        public void setResult(String result) {<!-- -->
            this.result = result;
        }

        public Result(String result){<!-- -->
            this.result = result;
        }
    }
}

3.3 Package and copy the jar package to ClickHouse

docker cp path/demo_clickhouse_udf-1.0-SNAPSHOT-jar-with-dependencies.jar container id:/var/lib/clickhouse/user_scripts/demo_clickhouse_udf-1.0-SNAPSHOT-jar-with-dependencies.jar

3.4 SQL verification

SYSTEM RELOAD FUNCTIONS; # Refresh function
SELECT * FROM system.functions WHERE name = 'demo_clickhouse_udf'; # Query the udf function just added
select demo_clickhouse_udf(1,2)

return

{<!-- -->"param1":"1","param2":"2"}

3.5 Json dictionary, data expansion

return

{<!-- -->"param1":"1","param2":"2"}

Data expansion

select
    JSON_VALUE(result, '$.param1') as param1,
    JSON_VALUE(result, '$.param2') as param2
from(
    select demo_clickhouse_udf(1,2) as result
) t1;

3.6 Json array, data expansion

java routine

 public static void main(String[] args) {<!-- -->
        try {<!-- -->
            DataInputStream in = new DataInputStream(new BufferedInputStream(System.in));
            String s;

            //Read data line by line
            while ((s = in.readLine()).length() != 0) {<!-- -->
                // Get input parameters
                Gson gson = new Gson();
                JsonElement jsonElement = gson.fromJson(s, JsonElement.class);
                JsonObject jsonObject = jsonElement.getAsJsonObject();
                String argument_1 = jsonObject.get("argument_1").getAsString();
                String argument_2 = jsonObject.get("argument_2").getAsString();
                List<Demo> demoList = new ArrayList<>();
                demoList.add(new Demo(argument_1, argument_2));
                demoList.add(new Demo("3", "4"));
                demoList.add(new Demo("5", "6"));
                //Encapsulate the output results
                String resultStr = new GsonBuilder().excludeFieldsWithoutExposeAnnotation().create().toJson(demoList);
                System.out.println(gson.toJson(new Result(resultStr)));
            }
            System.out.flush();
        } catch (Exception e) {<!-- -->
            e.printStackTrace();
        }
    }

return

[{<!-- -->"param1":"1","param2":"2"},{<!-- -->"param1":"3","param2":" 4"},{<!-- -->"param1":"5","param2":"6"}]

Data expansion

select
    JSONExtractString(arrayElement, 'param1') as param1,
    JSONExtractString(arrayElement, 'param2') as param2
from(
    select demo_clickhouse_udf(1,2) as result
) t1
ARRAY JOIN JSONExtractArrayRaw(result) AS arrayElement;

4. Abnormal problems

4.1 UNKNOWN_FUNCTION

The xml file name must end with function.xml, otherwise the addition will fail and the function cannot be found.

# Run sql
SYSTEM RELOAD FUNCTIONS; # Refresh function
SELECT * FROM system.functions WHERE name = 'demo_clickhouse_udf'; # Query the udf function just added

# Report error
Code: 46. DB::Exception: Unknown function demo_clickhouse_udf: While processing demo_clickhouse_udf(1, 2). (UNKNOWN_FUNCTION) (version 22.3.10.22 (official build))

4.2 UNSUPPORTED_METHOD

1.execute_direct needs to be 0

<execute_direct>0</execute_direct>

2. The command needs to bring the root directory, such as /usr/bin/java

<command>/usr/bin/java -jar /var/lib/clickhouse/user_scripts/demo_clickhouse_udf-1.0-SNAPSHOT-jar-with-dependencies.jar</command>

3. There is no language environment such as java installed.

4. No command or script permissions

5. The version is not supported, such as: 22.2.3.5

# Report error
Code: 1. DB::Exception: Executable file /usr/bin/java does not exist inside user scripts folder /var/lib/clickhouse/user_scripts/: While processing demo_clickhouse_udf(1, 2). (UNSUPPORTED_METHOD) (version 22.3. 10.22 (official build))

4.3 CANNOT_PARSE_QUOTED_STRING

The returned string format is incorrect and the result is not encapsulated into json.

// Wrong java example
System.out.println("{'result':1}");

# Report error
Code: 26. DB::ParsingException: Cannot parse JSON string: expected opening quote: While executing ParallelParsingBlockInputFormat: While executing ShellCommandSource: While processing demo_clickhouse_udf(1, 2): (at row 1) . (CANNOT_PARSE_QUOTED_STRING) (version 22.3.10.22 ( official build))

5. Reference

ClickHouse Doc
Github Issues