Issue with Virtual Schema and JDBC Adapter

[harry]

Hi everyone!

I've been trying to register a virtual schema on an Exasol DB (within a docker container), but it get following error when creating the schema:

F-UDF-CL-LIB-1125: F-UDF-CL-SL-JAVA-1000: F-UDF-CL-SL-JAVA-1037: com.exasol.ExaCompilationException: F-UDF-CL-SL-JAVA-1158: /JDBC_ADAPTER.java:1: error: cannot access com.exasol package com.exasol; ^ zip END header not found

I am using the current docker-latest image (Exasol 7.0 if I am not mistaken). Furthermore, I followed the suggestions on the READMEs of the virtual schemas, that is: I created new bucket and uploaded virtual schema generic JDBC jar and my jdbc driver (also tested that they are correctly uploaded). Then I am running the following commands:

CREATE SCHEMA ADAPTER;

CREATE OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS
%scriptclass com.exasol.adapter.RequestDispatcher;
%jar /buckets/newbucketfs/newbucketfs-bucket/virtual-schema-dist-9.0.3-generic-2.0.0.jar;
%jar /buckets/newbucketfs/newbucketfs-bucket/postgresql-42.2.18.jar;
/

CREATE OR REPLACE CONNECTION GENERIC_CONNECTION
TO 'jdbc:postgresql://myhost:5432'
USER 'usr'
IDENTIFIED BY '***';

CREATE VIRTUAL SCHEMA virt_schema
USING ADAPTER.JDBC_ADAPTER
WITH
CATALOG_NAME = 'db1'
CONNECTION_NAME = 'GENERIC_CONNECTION';

I have also tried using the specialized PostgreSQL virtual schema, but I get the exact same error.

Did I forget to run something? Could it be that my docker setup is faulty? Grateful for any hints!

[drumcircle]

Can you verify that your compiled project includes all the dependencies.  They need to be packaged with the (fat) jar as I recall.

[harry]

Hi @drumcircle ,

Thanks for your answer! I have tried issuing the above mentioned commands through SQL clients (DataGrip & Exaplus). Is there any specific option that I should look at?

Best,

Harry

[mwellbro]

Hi @harry ,

as you mentioned you followed the READMEs of the virtual schema guides - as far as I understood it the docker way is a bit different, i.e.:

src: https://github.com/exasol/docker-db#installing-custom-jdbc-drivers

Maybe you need to pay special heed to the "you need to upload it into a subdirectory of drivers/jdbc of the default bucket (which is automatically created if you don't modify EXAConf)" passage, as well as the required ".cfg" file ? But I´m just guessing here.

Haven´t tried this yet, ramped up an AWS linux host,grabbed docker and pulled the exa-latest image , also got that to run but had to
put a stop to my experiments at that point - should I get it to run ( even though it feels somehow alien when you´ve worked with ExaOperation up to now ) I´ll post here again.

Cheers,
Malte

[exa-SebastianB]

Morning @harry ,

using the dedicated VS is always better than the generic one, since the generic VS implements no capabilities (like JOIN for example).

Your particular problem looks like an unresolved dependency to me. The JAR files of the Virtual Schema and the Driver must be uploaded to BucketFS. The driver must be in a specific bucket in a specific path and registered in a specific configuration file.
See https://github.com/exasol/docker-db#installing-custom-jdbc-drivers for details on driver installation. Please follow these steps. If all dependencies are satisfied, you should be able to create the VS.
Also please use the precompiled JAR from the GitHub releases, just to be sure.

Our @exa-Anastasiia is working on an installer by the way that does all these steps for the users, including downloading the VS from GitHub: https://github.com/exasol/virtual-schema-installer
The first release (which I expect in a couple of days) will cover MySQL and Postgres. Other dialects will follow soon.

[exa-Anastasiia]

Hello @harry 

I can see two possible problems with the scripts you sent.

1. In some SQL clients an adapter script is not parsed correctly without comment (--/) before the script. This comment helps to parse the script correctly. A working example:

--/
CREATE OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS
%scriptclass com.exasol.adapter.RequestDispatcher;
%jar /buckets/bfsdefault/virtualschemas/virtual-schema-dist-9.0.3-generic-2.0.0.jar;
%jar /buckets/bfsdefault/postgres/postgresql-42.2.6.jar;
/
;

 2. In the connection string a slash (/) is missing. A working example:

CREATE OR REPLACE CONNECTION GENERIC_CONNECTION
TO 'jdbc:postgresql://localhost:5432/'
USER 'postgres'
IDENTIFIED BY '';

Also as @exa-SebastianB mentioned, it's better to use Postgres Virtual Schema instead of Generic. But Generic should also work, I've just tested it.
Please let me know if you still have problems with it.

[exa-MathiasHo]

Hi @harry, 

Did the suggestion solutions help you or do you need further assistance?

[harry]

Hi @exa-MathiasHo, first of all thanks everyone for your answers!

I have tried following all suggestions, but unfortunately my issue persists. I will describe my setup & steps again:

I run Exasol using the following docker-compose:

version: "3.7"

services:

  exasol:
    image: exasol/docker-db:latest
    container_name: exa1
    ports:
      - 9563:8563
      - 8899:8888
      - 8081:80
      - 6932:6932
      - 2580:2580
    privileged: true

networks:
  default:
    name: exa-net

First, as suggested by @exa-SebastianB and @mwellbro I upload my Postgres driver and virtual schema jars (downloaded from the Exasol's github repo releases) to the default bucketfs:

curl -v -X PUT -T postgresql-42.2.18.jar http://w:JPu0EEe9itW8YefGslpTT1@localhost:2580/default/drivers/jdbc/postgres/postgresql-42.2.18.jar
curl -v -X PUT -T system.cfg http://w:JPu0EEe9itW8YefGslpTT1@localhost:2580/default/drivers/jdbc/postgres/system.cfg
curl -v -X PUT -T virtual-schema-dist-9.0.3-generic-2.0.0.jar http://w:JPu0EEe9itW8YefGslpTT1@localhost:2580/default/virtualschemas/virtual-schema-dist-9.0.3-generic-2.0.0.jar
curl -v -X PUT -T virtual-schema-dist-9.0.3-generic-2.0.0.jar http://w:JPu0EEe9itW8YefGslpTT1@localhost:2580/default/virtualschemas/virtual-schema-dist-9.0.1-postgresql-2.0.0.jar

The content of my system.cfg file looks as follows:

DRIVERNAME=MY_POSTGRES_DRIVER
JAR=postgresql-42.2.18.jar
DRIVERMAIN=org.postgresql.Driver
PREFIX=jdbc:postgresql:
FETCHSIZE=100000
INSERTSIZE=-1

To make sure that my files are there, I list the bucket contents:

curl -v http://r:2zwJ6VNqGvd82YzUNYIbT6@localhost:2580/default/

and I get the following files, which shows that my files are uploaded correctly:

EXASolution-7.0.8/udfplugin_protegrity_rest
EXASolution-7.0.8/udfplugin_protegrity_710
EXASolution-7.0.8/udfplugin_protegrity
EXAClusterOS/ScriptLanguages-standard-exasol-7.0.0-20201109.tar.gz
virtualschemas/virtual-schema-dist-9.0.1-postgresql-2.0.0.jar
drivers/jdbc/postgres/postgresql-42.2.18.jar
EXASolution-7.0.8/udfplugin_protegrity_665_ua
EXASolution-7.0.8/udfplugin_protegrity_665
drivers/jdbc/postgres/system.cfg
EXASolution-7.0.8/udfplugin_protegrity_rest_ua
EXASolution-7.0.8/udfplugin_protegrity_ua
EXASolution-7.0.8/udfplugin_protegrity_710_ua
EXASolution-7.0.8/udfplugin_protegrity_664
EXASolution-7.0.8/udfplugin_protegrity_664_ua
virtualschemas/virtual-schema-dist-9.0.3-generic-2.0.0.jar

Then, I start issuing the Virtual Schema creation commands (tried both with DataGrip and Exaplus), also trying out the commented versions suggested by @exa-Anastasiia.

CREATE SCHEMA ADAPTER;

--/
CREATE OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS
%scriptclass com.exasol.adapter.RequestDispatcher;
%jar /buckets/bfsdefault/default/virtualschemas/virtual-schema-dist-9.0.3-generic-2.0.0.jar;
%jar /buckets/bfsdefault/default/drivers/jdbc/postgres/postgresql-42.2.18.jar;
/
;

CREATE OR REPLACE CONNECTION GENERIC_CONNECTION
TO 'jdbc:postgresql://localhost:5432/'
USER 'postgres'
IDENTIFIED BY '******';

CREATE VIRTUAL SCHEMA virt_schema
USING ADAPTER.JDBC_ADAPTER
WITH
CATALOG_NAME = 'db1'
CONNECTION_NAME = 'GENERIC_CONNECTION';

  and then I get following error:

EXA: CREATE VIRTUAL SCHEMA virt_schema...
Error: [22002] VM error: F-UDF-CL-LIB-1125: F-UDF-CL-SL-JAVA-1000: F-UDF-CL-SL-JAVA-1037: 
com.exasol.ExaCompilationException: F-UDF-CL-SL-JAVA-1158: /JDBC_ADAPTER.java:1: error: cannot access com.exasol
package com.exasol;
^
  zip END header not found

Unfortunately I do not know yet the Exasol internals, in order to reason about where this error might be coming from, but it seems as some misconfiguration issue?

By the way, I also tried the https://github.com/exasol/virtual-schema-installer but there does not seem to exist a release yet. I tried to build the project with mvn package, but that wont work right now. I guess I can try again when there are some releases.

And again, thanks everyone for your suggestions up to now, those are really appreciated!

Best,

Harry

[exa-MathiasHo]

Hi @harry,

I have forwarded your situation once again internally. As this seems like there is no quick solution, please be a little patient, it might take a day or two to get the right answer.

Best regards,
Mathias

[exa-SebastianB]

Hi @harry, since this is a message that I never witnessed before, I will ask one of our UDF experts. The error chain should help locate the part of the UDF where the error originates.