Prashanth Jayaram
SQL Server Java file

Java Language Extensions in SQL Server 2019

August 13, 2020 by

In this article, we will discuss SQL Server 2019 new feature—Java Language Extensions. With the advent of SQL Server 2019, Microsoft always strives to expand its footprint on the capabilities of MLS (Machine Learning Services). It builds a deeper integration between the data-platform and data science under the data science umbrella. The Microsoft SQL Server 2019 MLS extensibility framework provides a solid base for allowing extensions in R, Python, and now Java.

In this article, we will discuss the following:

  1. Understand Machine Language Service
  2. Discuss JAVA SDK installation and configuration
  3. Configure Java Language Extension API
  4. Prepare the sample JAVA code using Eclipse IDE
  5. Deploy JAR file in SQL Server
  6. Run the JAVA program
  7. And more…

Get Started

In the last three major releases of SQL Server, SQL Server 2016, SQL Server 2017, and SQL Server 2019—integrated a framework that supports running an external programming language capability in the database server. SQL Server 2016 introduced R language; SQL Server 2017 supported Python and continued to support R. These two releases coined as the biggest game-changer in the field of data science, and these are categorized under MLS (Machine Language Service). Now, Microsoft strategically added another external language support under the Machine Language (ML). The trend continued in SQL Server 2019 with an addition of JAVA to the list of externally supported languages. In addition, SQL Server 2019 in-houses a wide range of new features and enhancements to the existing feature.

The extensibility framework APIs reference for running external scripts such as R and Python are supported in SQL Server 2017. This extensibility framework API now exposed to run JAVA programs using the sp_execute_external_script system stored procedure.

Pre-requisites

As we know that to run the Java program, you need JVM (Java Virtual Machine).

The pre-requisites to writing and running the Java program are as follows:

  1. Java SE Development Kit( JDK)
  2. Java Runtime Environment (JRE) is one of the components of the Java SDK. It enables and provides a platform to Java programs

Install JAVA SDK

In this section, we will discuss the steps to install and configure JAVA.

  1. Download Zulu Java SE SDK for windows 64bit

  2. Click the installation library

    zulu JAVA SDK

  3. After the installation, configure the environment variable. Follow the step-by-step details to configure environment variables:

    • Go to computer properties
    • Click the Advanced system settings
    • Click Environment Variables
    • Specify the value of the PATH environment variable

      • C:\Program Files\Zulu\zulu-8\bin\
      • C:\Program Files\Zulu\zulu-8\jre\bin\server

SQL Server JAVA - Update Environment Variable

Note: By default, SQL Server installs Zulu Open JRE (Java Runtime Environment) version 11.0.3. It is also possible to install the full JDK (Java Development Kit) for compilers and/or any other java distribution of your own choice.

Note: It is highly recommended to use Oracle Java SE and Zulu OpenJDK. In addition, during the installation process, it is advised to use the default Windows Program Files folder. 

Configure Java Language Extensions in SQL Server

SQL Server 2019 provides a platform to integrate the Java language extension library to leverage data exchange and communication using Extensibility Framework APIs. The language extensions allow you to extend SQL Server to use external code such as Python, R, and JAVA as part of SQL executions and operations.

Let us enable “external scripts” parameters using sp_configure option. Let us run the following T-SQL to validate the external scripts feature in the SQL Server configuration.

The value “0” indicates in the below output depicts the external scripts are disabled.

Check SQL Server Java external scripts enabled output using sys.configurations

Now, run the following command to enable the external scripts

Let us re-run the aforementioned SQL to validate the “external scripts enabled” parameter

Check SQL Server Java external scripts enabled using sys.configurations

In the following output, we can see that R and Python are referenced in the SQL Server.

This indicates that the Java program is not registered. In the next section, we can discuss how to register the Java libraries in SQL Server.

Shows SQL Server Java External Languge Reference Query is not added

To register the Java external language, walk-through the following step-by-step instruction.

  1. In the SQL Server installation directory, you will find the Microsoft Extensibility SDK—mssql-java-lang-extension.jar. Let us locate the java-lang-extension.zip. In this case, the SQL Server installation folder G:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn. Now, browse the folder and locate the java language extension

    java-lang-extension Zip file

  2. Copy the java-lang-extension.zip to another folder. In this case, the java-lang-extension.zip is copied to G:\SQL

    To Run SQL Server Java, extract java-lang-extension Zip file

  3. Run the following SQL to register Java external language

    In the following output, you can see that the Java external language registered successfully.

    Add SQL Server Java external language reference using Create External Lanaguage command

  4. Let us verify the Java language registration using the following SQL

    SQL Server Java listed in the External Language Reference commands

    We have successfully registered the Java library. We are not done yet….

  5. As I mentioned above, you still need to reference the library to build a jar file. In this case, I use Eclipse editor to compile and build the java file(JAR file)

Build the JAR file

In this section, let us walk-through step-by-step instructions to build the JAR file. In this case, we will create the Java SQL2019 Demo Package and SQL2019 class.

Let us write the simple Java code in a .class file and save it in the folder G:\SQL.

Note: Ensure you have the full permissions on the folder G:\SQL folder.

Import the following packages in the class:

import com.microsoft.sqlserver.javalangextension.PrimitiveDataset;

import com.microsoft.sqlserver.javalangextension. AbstractSqlServerExtensionExecutor;

  1. Right-click SQL2019 project and go to the Properties to open the Properties dialog
  2. Click Java Build Path on the left navigation tree

    Include MSSQL Library to run SQL Server Java Programs

  3. Select the Libraries right
  4. Click Add JARs

    Extract java lanaguage extension to Custom folder

  5. Now, browse G:\SQL and select mssql-java-lang-extension.jar
  6. Copy the below code and paste it in the editor, or you can also define your own code

    Please refer to the editor for more details:

    SQL Server Java file

  7. Next, compile the java program
  8. Now, to export the SQL2019Demo project, right-click and select Export

    Export SQL Server Java file

  9. Select Java
  10. Select Runnable JAR file and click Next

    Select Runnable JAR file

  11. Type in the export destination and click finish

    Create SQL Server java jar file

  12. Now, create an EXTERNAL library from your program:

  13. Let us verify the external references using the following SQL:

    Register SQL Server Java External libary

  14. Finally, we are ready to run the Java program in SQL Server. The @script parameter is a combination of the package name and the class name. In this case, the package name is SQL2019Demo, and the Class name is SQL2019

Summary

SQL Server 2019 extensibility language framework provides a platform for allowing extensions in R, Python, and now Java. So far, we discussed Machine Language Service (MLS); Configure JAVA external language; run the JAVA program in SQL Server. In addition, we discussed the use of the Eclipse editor—Integrated Development Environment (IDE) to run and compile the JAVA programs. Also, we discussed the JAVA integration in SQL Server 2019 Machine Learning Services. SQL Server 2019 supports Python, R, and Java. Now, the support of Java language extension in SQL Server 2019 allows data scientists or data engineers to run the precompiled Java programs (JAR) and securely execute JAR code on SQL Server.

Note: SQL Server 2019 supports Java on both Windows and Linux platforms.

You can also refer to the following table to understand more about the Java Language extensions

New feature or update

Details

The support of Java SDK

It provides a platform and support to run Java programs. Please refer to the following link for more information

https://docs.microsoft.com/en-us/sql/language-extensions/how-to/extensibility-sdk-java-sql-server?view=sql-server-ver15

The support of open source

The open-source Microsoft Extensibility SDK for Java is made available in GitHub

The Supported data types

Please refer to below link to understand the supported data types

https://docs.microsoft.com/en-us/sql/language-extensions/how-to/java-to-sql-data-types?view=sql-server-ver15

The Java Runtime

The installation library includes Azul Systems Zulu Embedded for Java support is made available in the product

https://cloudblogs.microsoft.com/sqlserver/2019/07/24/free-supported-java-in-sql-server-2019-is-now-available/

The Language Extensions

It supports the execution of the external code with the extensibility framework

https://docs.microsoft.com/sql/language-extensions/language-extensions-overview

Register external language references

To register to Java language refer to the below link

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-language-transact-sql?view=sql-server-ver15

Prashanth Jayaram
Data science, SQL Server 2019

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

298 Views