Problem with AWS Athena Virtual Schema

andreeroos
SQL-Fighter

Hi.

I have implemented the Athena Virtual Schema but have run into some problems. It should be noted that the first problem is that the tutorials/guides aren't entirely consistent so I'm not 100% sure of the settings to use. One guide seemed to use the generic virtual schema and another an Athena version. I'm not sure how the more specialized versions differ from the general ones.

When creating the adapter I went for:

andreeroos_0-1618930801240.png

According to one guide there should be a virtual-schema-dist-9.0.2-athena-2.0.0.jar to use but I couldn't find that file on github.

Anyway, everything goes fine until I try to query a table. The query sent to Athena was:

 

SELECT * FROM (SELECT * FROM ”daFolder".”daTable" LIMIT 200) T LIMIT 0

 

 The LIMIT 0 seems counterproductive when trying to get a result. I'm not sure why this happens. Any ideas?
I'm not sure if it matters, but I'm using DBeaver.

 

4 REPLIES 4

exa-Jakob
Team Exasol
Team Exasol

Hi,

could you provide links for the different guides you mentioned? Then we can check and make it more clear.

Version 9.0.2-2.0.0 is not yet released. That's why you can't find it on GitHub. You were probably viewing the documentation on the main branch. That is the most recent one, that might not yet be released.

The Virtual Schema adapter uses the query you showed to discover the schema of the Athena table. It will fetch the data later on. So this is expected.

 

Regards,
Jakob

 

mwellbro
Xpert

Hi @andreeroos ,

I just did try to gear up an Athena vSchema, but since my last DEV cluster went up in flames ( admittedly it was my fault ) I´m still adjusting a few things in my local environment....

In the meantime, just been meaning to ask: what is your use case for a vSchema to Athena ? I´m always a bit reluctant regarding services that "make me pay for data scanned"...
just to put it out here: you could just create a view with an "select * from (IMPORT FROM MY_S3_CONNECTION" in Exasol directly, if that´s the goal here.

Will get back to you once I had the time to fix up my test system, but hopefully until then someone already was able to help you out.

Cheers,
Malte

andreeroos
SQL-Fighter

It turned out that the connection didn't work either so that seemed to be the problem.
I knew that the settings for the connector was correct, since I've tried it in other tools, but efter playing around in ExaOperations with the setting Disable Security Manager it worked. I'm not sure what that is doing but the setting is marked with the words DANGEROUS, so I'm might need to dig into this a bit.

 

andreeroos_0-1618989784922.png

To sum it up, both the connection and virtual schema now works.

 

 

exa-Jakob
Team Exasol
Team Exasol

You're right. Disabling the security manager is currently required:


IMPORTANT: The latest Athena driver requires to Disable Security Manager. It is necessary because JDBC driver requires Java permissions which we do not grant by default.


(from the user-guide)