Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-201

Using arbitrary JAR (Java Archive) libraries within UDFs (User Defined Functions)

    Details

    • Solution:
      Hide

      Note: This solution is no longer maintained. For the latest information, please visit our Knowledge Base:

      https://community.exasol.com/t5/data-science/using-custom-jar-java-archive-libraries-within-udfs/ta-p/1113

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Background

      You have an own jar you want to use within Java UDFs. 

      Exasol version 5 and older are no longer supported. References to Exasol 5 left in for archival purposes only. 

      Prerequisites

      Requirements:

      A (temporary) FTP/HTTP server with your jar file (To upload the jar to BucketFS) OR one may use 'curl' from the command line to upload the jar to BucketFS.

      How to use JAR libraries within UDFs

      For testing purposes, you can use my-app-1.0-SNAPSHOT.jar.
      This jar was generated using the following tutorial:
      http://maven.apache.org/guides/getting-started/maven-in-five-minutes.html

      Step 1. Install the jar library

      • (Deprecated) EXASOL 5 or before: Use EXAoperation like in java_udf.jpg.
      • EXASOL 6 or later: See detailed information about EXABucket FS in SOL-503 and in particular the subsection Expanding script languages using BucketFS in the EXASOL user manual. There you can see, how to adapt java_udf.sql to import java libraries from buckets.

      Example using curl to upload the jar file to BucketFS directory '/buckets/bucketfs1/udf':

      c:\Users\joas\Downloads>curl --user w -v -X PUT -T my-app-1.0-SNAPSHOT.jar  http://192.168.1.158:8080/udf/my-app-1.0-SNAPSHOT.jar

      Step 2. Modify java_udf.sql to use the jar in BucketFS

      2. Adapt the java_udf.sql if you are using EXASOL 6 or later. Here is an example of a modified java_udf.sql to incorporate the uploaded jar:

      CREATE OR REPLACE JAVA SCALAR SCRIPT HELLOWORLD() RETURNS VARCHAR(200) AS 
      // Tested using Exasol 6.2.5
      // This jar was generated using the following tutorial: 
      // http://maven.apache.org/guides/getting-started/maven-in-five-minutes.html 
      %jar /buckets/bucketfs1/udf/my-app-1.0-SNAPSHOT.jar;
      import java.io.*;
      import com.mycompany.app.App; 
      class HELLOWORLD { 
          static String run(ExaMetadata exa, ExaIterator ctx) throws Exception { 
              ByteArrayOutputStream baos = new ByteArrayOutputStream(); 
              System.setOut(new PrintStream(baos)); 
              App.main(null); 
              return baos.toString(); } }
      /
      
      SELECT HELLOWORLD();

      Step 3. Run java_udf.sql

      Additional Notes

      • EXASOL 5 or before only: The jar name is different now, it is the name you chose in EXAoperation + ".jar" (here: "myapp.jar")
      • The script and the class name must be equal respecting case sensitivity (here: "HELLOWORLD")
      • If you have a scalar script, the return type of the script (here: "VARCHAR(200)") must correspond to the return type of the run method of the class (here: "String")
      • If you have an emits script, the return type of the run method is void and you must use the method ExaIterator.emit of your ExaIterator instance (here: "ctx")

      Additional References

      SOL-503 How to create an EXABucketFS service and bucket

      https://docs.exasol.com/database_concepts/udf_scripts/expand_script_using_bucketfs.htm

      SOL-367 XML-parsing using Java UDFs

      SOL-443 How to create and use a UDF

      Show
      Note: This solution is no longer maintained. For the latest information, please visit our Knowledge Base: https://community.exasol.com/t5/data-science/using-custom-jar-java-archive-libraries-within-udfs/ta-p/1113 ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Background You have an own jar you want to use within Java UDFs.  Exasol version 5 and older are no longer supported. References to Exasol 5 left in for archival purposes only.  Prerequisites Requirements: A (temporary) FTP/HTTP server with your jar file (To upload the jar to BucketFS) OR one may use 'curl' from the command line to upload the jar to BucketFS. How to use JAR libraries within UDFs For testing purposes, you can use my-app-1.0-SNAPSHOT.jar . This jar was generated using the following tutorial: http://maven.apache.org/guides/getting-started/maven-in-five-minutes.html Step 1. Install the jar library (Deprecated) EXASOL 5 or before: Use EXAoperation like in java_udf.jpg . EXASOL 6 or later: See detailed information about EXABucket FS in SOL-503 and in particular the subsection Expanding script languages using BucketFS in the EXASOL user manual. There you can see, how to adapt java_udf.sql to import java libraries from buckets. Example using curl to upload the jar file to BucketFS directory '/buckets/bucketfs1/udf': c:\Users\joas\Downloads>curl --user w -v -X PUT -T my-app-1.0-SNAPSHOT.jar http://192.168.1.158:8080/udf/my-app-1.0-SNAPSHOT.jar Step 2. Modify java_udf.sql to use the jar in BucketFS 2. Adapt the java_udf.sql if you are using EXASOL 6 or later. Here is an example of a modified java_udf.sql to incorporate the uploaded jar: CREATE OR REPLACE JAVA SCALAR SCRIPT HELLOWORLD() RETURNS VARCHAR(200) AS // Tested using Exasol 6.2.5 // This jar was generated using the following tutorial: // http://maven.apache.org/guides/getting-started/maven-in-five-minutes.html %jar /buckets/bucketfs1/udf/my-app-1.0-SNAPSHOT.jar; import java.io.*; import com.mycompany.app.App; class HELLOWORLD { static String run(ExaMetadata exa, ExaIterator ctx) throws Exception { ByteArrayOutputStream baos = new ByteArrayOutputStream(); System.setOut(new PrintStream(baos)); App.main(null); return baos.toString(); } } / SELECT HELLOWORLD(); Step 3. Run java_udf.sql Additional Notes EXASOL 5 or before only: The jar name is different now, it is the name you chose in EXAoperation + ".jar" (here: "myapp.jar") The script and the class name must be equal respecting case sensitivity (here: "HELLOWORLD") If you have a scalar script, the return type of the script (here: "VARCHAR(200)") must correspond to the return type of the run method of the class (here: "String") If you have an emits script, the return type of the run method is void and you must use the method ExaIterator.emit of your ExaIterator instance (here: "ctx") Additional References SOL-503  How to create an EXABucketFS service and bucket https://docs.exasol.com/database_concepts/udf_scripts/expand_script_using_bucketfs.htm SOL-367  XML-parsing using Java UDFs SOL-443  How to create and use a UDF
    • Category 1:
      Cluster Administration - BucketFS
    • Category 2:
      UDFs and In-Database Analytics

      Attachments

        Issue Links

        1. java_udf.jpg
          java_udf.jpg
          61 kB
        2. java_udf.sql
          0.6 kB
        3. my-app-1.0-SNAPSHOT.jar
          2 kB

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: