Not able to get data from a virtual schema pointing to MS SQL Server

Help Needed
1

Dear Community,

on our Exasol environment (version 8.33) we’ve tried to implement a virtual schema to an SQL Server followed the docuemtnation and have:

  • uploaded the needed jar files and settings.cfg to bucketfs
  • defined a adapter script
  • created the connection

When we use the connection we only receive meta data from the schema. When trying to get data via an import statement the command never returns but does not result in an error.

Does anybody have an idea how to fix this?

jars:

  • virtual-schema-dist-12.0.0-sqlserver-2.1.4.jar
  • mssql-jdbc-13.2.0.jre11.jar

Adapter:

CREATE OR REPLACE JAVA ADAPTER SCRIPT “ADAPTER_SCRIPT_SQLSERVER” AS
%scriptclass com.exasol.adapter.RequestDispatcher;
%jar /buckets/bucketfs1/jars/mssql-jdbc-13.2.0.jre11.jar;
%jar /buckets/bucketfs1/jars/virtual-schema-dist-12.0.0-sqlserver-2.1.4.jar;

Connection:

CREATE CONNECTION SQLSERVER_JDBC_CONNECTION
TO ‘jdbc:sqlserver://\<instance_name>;encrypt=true;trustServerCertificate=true;’
USER ‘<user_name>’
IDENTIFIED BY ‘’;

Virtual Schema:

CREATE VIRTUAL SCHEMA AMS01_TEST
USING SCHEMA_FOR_VS_SCRIPT.ADAPTER_SCRIPT_SQLSERVER
WITH
CONNECTION_NAME = ‘SQLSERVER_JDBC_CONNECTION’
CATALOG_NAME = ‘<ms_db_name>’
SCHEMA_NAME = ‘dbo’

Import:

SELECT * FROM

(IMPORT FROM JDBC DRIVER=‘MSSQL’

AT SQLSERVER_JDBC_CONNECTION

STATEMENT ’ SELECT column1 FROM <schema_name>.<table_name> ');

1 Like
2

Can observe the created import command the VS adapter creates with EXPLAIN VIRTUAL. I am guessing here that this is where you got the import command above from.

You can then as a debugging step execute the import command via exaplus directly on the Exasol cluster and see what happens. If it succeeds, you know that the import command as such is valid.

Next step is to verify that the SQL Server driver is installed correctly. Unfortunately, you need to install it twice. Once in BucketFS as you did in the script above. And a second time in the core operating systems driver store. The reason for that is that BucketFS is a locked-down change root and UDFs (like the Virtual Schema adapter) are restricted to seeing the contents of BucketFS buckets.

I would also assume that you have to disable Java’s security manager in order to be able to use the MSSQL driver. In fact that could be your main issue. I don’t remember the settings by heart, so give me some time please, to look that up for you.

3

Here is the description of how to install the driver in COS: Add JDBC Drivers - AWS | Exasol DB Documentation

If you want to install it in only one bucket, please adapt your script to take the driver from the default bucket. The bucket needs to be publicly readable in any case.

4

Found the configuration option for disabling the security manager for the driver:

With some driver versions you may receive an error message indicating file permission issues. In this case you may have to disable the security manager by adding the line NOSECURITY=YES in the configuration.

One more thing. When you experiment with the IMPORT command, you can drop the surrounding sub-select statement. SELECT * FROM (…) without and filter or aggregation is redundant. It does not hurt but it also does not do anything useful.

5

We have written the following knowledgebase article for this issue:

Resolving the JDBC Import Hang