search

exasol / athena-virtual-schema

Virtual Schema for connecting Athena as a data source to Exasol
MIT License
0 stars 1 forks source link

if athena database name needs to be quoted, querying doesn't work #6

Closed frschwab closed 3 years ago

frschwab commented 3 years ago

Hi, I can create a virtual schema, but I cannot query it. CREATE VIRTUAL SCHEMA VS_ufos USING ADAPTER.ATHENA_JDBC_ADAPTER WITH SQL_DIALECT = 'ATHENA' CONNECTION_NAME = 'FS_UFOS' SCHEMA_NAME = 'fs-eu-west-1'; Error: An error occurred while performing the operation: VM error: F-UDF-CL-LIB-1126: F-UDF-CL-SL-JAVA-1006: F-UDF-CL-SL-JAVA-1026: com.exasol.ExaUDFException: F-UDF-CL-SL-JAVA-1068: Exception during singleCall adapterCall java.lang.AssertionError: E-ID-2: Unable to create identifier "fs-eu-west-1" because it contains illegal characters. For information about valid identifiers, please refer to https://docs.aws.amazon.com/athena/latest/ug/tables-databases-columns-names.html com.exasol.adapter.dialects.athena.AthenaIdentifier.of(AthenaIdentifier.java:49) com.exasol.adapter.dialects.athena.AthenaSqlDialect.applyQuote(AthenaSqlDialect.java:112) com.exasol.adapter.dialects.SqlGenerationVisitor.visit(SqlGenerationVisitor.java:184) com.exasol.adapter.dialects.SqlGenerationVisitor.visit(SqlGenerationVisitor.java:30) com.exasol.adapter.sql.SqlTable.accept(SqlTable.java:46) com.exasol.adapter.dialects.SqlGenerationVisitor.visit(SqlGenerationVisitor.java:78) com.exasol.adapter.dialects.SqlGenerationVisitor.visit(SqlGenerationVisitor.java:30) com.exasol.adapter.sql.SqlStatementSelect.accept(SqlStatementSelect.java:104) com.exasol.adapter.dialects.AbstractQueryRewriter.createPushdownQuery(AbstractQueryRewriter.java:62) com.exasol.adapter.dialects.AbstractQueryRewriter.rewrite(AbstractQueryRewriter.java:48) com.exasol.adapter.dialects.AbstractSqlDialect.rewriteQuery(AbstractSqlDialect.java:134) com.exasol.adapter.jdbc.JdbcAdapter.pushdown(JdbcAdapter.java:210) com.exasol.adapter.RequestDispatcher.dispatchPushDownRequestToAdapter(RequestDispatcher.java:142) com.exasol.adapter.RequestDispatcher.processRequest(RequestDispatcher.java:80) com.exasol.adapter.RequestDispatcher.executeAdapterCall(RequestDispatcher.java:52) com.exasol.adapter.RequestDispatcher.adapterCall(RequestDispatcher.java:41) com.exasol.ExaWrapper.runSingleCall(ExaWrapper.java:100) (Session: 1703537717580726272)

It is probably due to the minus characters in the schema_name. I think the adapter would have to put it in backticks as described here: https://docs.aws.amazon.com/athena/latest/ug/tables-databases-columns-names.html Thank you for investigating.

AnastasiiaSergienko commented 3 years ago

@frschwab we explicitly do not allow using schemas with names that have special characters other than an underscore, error comes from our validation. AWS doesn't recommend doing this (see IMPORTANT part): https://docs.aws.amazon.com/athena/latest/ug/tables-databases-columns-names.html

So the best solution here would be renaming the database to follow the best practices.