Transcription
Run Java in SQL ServerNellie Gustafsson – Senior Program Manager
SQL Server external language integrationsSQL Server2016RSQL Server2017SQL Server2019PythonR-ServicesSQL Server Machine Learning ServicesSQL Server Language Extensibility
Why Java?Source: Stack Overflow survey 2018Tiobe programming language index
Java - Example use cases Move middle-tier (application server) logic closerto the data Oracle Java Procs migration Regular expressions (Regex) Natural language processing Machine Learning
SQL Server Java extensionAPI: Extensibility Framework forMicrosoft SQL ServerJava Extensibility SDK forMicrosoft SQL on.jarCREATE EXTERNAL LANGUAGECREATE EXTERNAL LIBRARYimport com.microsoft.sqlserver.javalangextensionpublic PrimitiveDataset execute( ) {//JAVA CODESQL Server2019EXEC sp execute external script@language N'Java', @script N'packageName.ClassName', @input data 1 N'SELECT id, text FROM testdata', @params N'@parameter nvarchar(40)', @parameter @parameterwith result sets ((column1 int, column2nvarchar(30)))
Architecture
3rd Party Extensibility Architecture“extension” process exthostMSSQLSERVER Service3MSSQLLAUNCHPAD Service12sp execute external script@language ll/extension.soJVMJNI
Security and Governance Reduced surface area and isolation Script execution requires explicit permission External language processes have limitedprivileges Built-in Resource Governance
External Language – New DDLCREATE EXTERNAL LANGUAGE JavaFROM(CONTENT ‘ sql server install path \MSSQL\Binn\javaextension.zip',FILE NAME N’javaextension.dll’,PLATFORM WINDOWS,ENVIRONMENT VARIABLES 'JRE HOME server path \Zulu\zulu-8\jre\;DEBUG 0;')CREATE EXTERNAL LANGUAGE JavaFROM(CONTENT n.tar.gz',FILE NAME N’javaextension.so’,PLATFORM LINUX,ENVIRONMENT VARIABLES 'JRE HOME server path \Zulu\zulu-8\jre\;DEBUG 0;')
External LibrariesNo need to set permissions to CLASSPATH if you use externallibraries.CREATE EXTERNAL LIBRARY myJarFROM (CONTENT ‘ path to .jar file or .zipfile ')WITH (LANGUAGE ‘Java');GO
Call external language from SQL ServerEXEC sp execute external script@language N’ Java', @script N'packageName.ClassName', @input data 1 N'SELECT id, text FROM testdata', @params N'@parameter nvarchar(40)', @parameter @parameterwith result sets ((column1 int, column2 nvarchar(30)))
Demo - Regex
Release of SDK and Extension API GitHub Microsoft Open Source Repository:aka.ms/mssql-java-lang-extension Host the source code for Java SDK, Java extension and Extension API on GitHub This means that the community can build 3rd party language extensions and use Java asexample Sub folders for language extension in repository that we support Under Releases on GitHub: Signed SDK jar & Source.zip/tar.gzSQL Server Setup Installs signed SDK jar file & Extension .dll/.so In future: Supported Java distribution
Questions?
Thank you.
Thank you.
Copyright Microsoft Corporation. All rights reserved.
SQL Server Java extension Java Extensibility SDK for Microsoft SQL Server mssql-java-lang-extension.jar API: Extensibility Framework for Microsoft SQL Server import com.microsoft.sqlserver.javalangextension public PrimitiveDataset execute( ) {//JAVA CODE javaextension.dll/.so SQL Server 2019 EXEC sp_execute_external_script @language N'Java'
