Virtual Schema - Extending dialect ?

[mwellbro]

Hi guys,

perhaps someone who knows their way around java and/or v-schema dialects might be able to help me out here:

When letting an exa-v-schema talk to saphana we see the following behavior:

2020-11-24 11:07:16.377 INFO [c.e.l.RemoteLogManager] Attached to output service with log level ALL.
2020-11-24 11:07:16.378 CONFIG [c.e.a.AdapterRegistry] Loading Virtual Schema Adapter: JDBC Adapter 5.0.4
2020-11-24 11:07:16.379 CONFIG [c.e.a.d.SqlDialectRegistry] Loading SQL dialect: SAPHANA dialect adapter 4.0.3
2020-11-24 11:07:16.383 FINE [c.e.a.j.RemoteConnectionFactory] Connecting to ... as user ... using password authentication.
2020-11-24 11:07:16.680 INFO [c.e.a.j.RemoteConnectionFactory] Connected to ... in 286 milliseconds.
2020-11-24 11:07:16.700 FINE [c.e.a.d.AbstractIdentifierConverter] Creating identifier converter with unqoted handling "INTERPRET_AS_UPPER" and quoted handling "INTERPRET_CASE_SENSITIVE".
2020-11-24 11:07:16.707 FINER [c.e.a.d.BaseQueryRewriter] Push-down query generated with SqlGenerationVisitor:
SELECT * FROM "SAPABAP1"."SKA1" WHERE false
2020-11-24 11:07:16.709 FINE [c.e.a.j.ResultSetMetadataReader] Generating columns description for push-down query using BaseColumnMetadataReader:
SELECT * FROM "SAPABAP1"."SKA1" WHERE false
2020-11-24 11:07:16.732 SEVERE [c.e.a.RequestDispatcher] Unable to read remote metadata for push-down query trying to generate result column description. Please, make sure that you provided valid CATALOG_NAME and SCHEMA_NAME properties if required. Caused by: SAP DBTech JDBC: [257]: sql syntax error: line 1 col 39 (at pos 39)
2020-11-24 11:07:16.733 FINE [c.e.a.RequestDispatcher] Stack trace:
(...)

After checking the source for the saphana dialect I saw that there is no saphana specific SqlGenerationVisitor, so I implemented one ( because "where false" is not hana compatible and the actual SQL_TEXT was SELECT * FROM SAPABAP1.SKA1 WHERE 'Y'='N' )

public class SapHanaSqlGenerationVisitor extends SqlGenerationVisitor {
/**
* Create a new instance of the {@link SapHanaSqlGenerationVisitor}.
*
* @param dialect {@link SapHanaSqlDialect} SQL dialect
* @param context SQL generation context
*/
public SapHanaSqlGenerationVisitor(final SqlDialect dialect, final SqlGenerationContext context) {
super(dialect, context);
}

@Override
public String visit(final SqlLiteralBool literal) {
if (literal.getValue())

{
return "1=1";
} else

{
return "1=0";
}
}
}

After updating the compiled class file into the v-schema jar-file and testing I still see the general SqlGenerationVisitor in the remote log so I wonder
where I went wrong ? Shouldn´t a dialect specific implementation that extends SqlGenerationVisitor replace it during execution ?

Sorry, I´m more of an SQL "programmer" and my c++ days were way-back-when so my knowledge about what I´m doing here is limited.

Happy about any pointers...oh, wait, java....oh well 😉

[mwellbro]

@exa-Anastasiia & @exa-SebastianB , might I draw upon your expertise for this one ? 🙂

[exa-SebastianB · Accepted solution]

Did you instantiate the new type in your Dialect like in this example of the MySQL dialect?
https://github.com/exasol/virtual-schemas/blob/a9f299dee493615b06dcb8d4409110714a52ec8e/src/main/jav...

@Override
public SqlNodeVisitor<String> getSqlGenerationVisitor(final SqlGenerationContext context) {
    return new MySqlSqlGenerationVisitor(this, context);
}

This is essential if you want to use your own SqlGenerationVisitor.

Side note:
According to this article here SAP Hana (or at least a version) seem to support boolean types. If that is true, then I sounds strange that a Boolean literal should not be supported:
https://www.tutorialspoint.com/sap_hana/sap_hana_data_types.htm
I found another article from 2013 that denied the existence of boolean data types in SAP Hana. Could this be an addition made in a recent version?

[mwellbro]

thanks for the tip, no of course I didn´t instantiate my SqlGenerationVisitor - will try that 😀

As for your side note: Hana does understand the concept of a boolean to the extent that comparisons are valid, i.e. 

select * from (IMPORT FROM JDBC AT your-trusty-hana-connection STATEMENT 'SELECT * FROM DUMMY where true = true'); 
is allowed and I think they do have the ability to define columns as boolean.

As a kind of stand alone expression like in exa, i.e. "select * from dummy where false" - that´s not considered valid syntax in hana.

[mwellbro]

Hi @exa-SebastianB  ,

thank you so much for the assist , after actually instantiating the SapHanaSqlGenerationVisitor it´s doing what it´s supposed to:

2020-11-26 21:54:00.695 INFO [c.e.l.RemoteLogManager] Attached to output service with log level ALL.
2020-11-26 21:54:00.695 CONFIG [c.e.a.AdapterRegistry] Loading Virtual Schema Adapter: JDBC Adapter 5.0.4
2020-11-26 21:54:00.696 CONFIG [c.e.a.d.SqlDialectRegistry] Loading SQL dialect: SAPHANA dialect adapter 4.0.3
2020-11-26 21:54:00.700 FINE [c.e.a.j.RemoteConnectionFactory] Connecting to ... as user ... using password authentication.
2020-11-26 21:54:00.951 INFO [c.e.a.j.RemoteConnectionFactory] Connected to ... in 239 milliseconds.
2020-11-26 21:54:00.953 FINE [c.e.a.d.AbstractIdentifierConverter] Creating identifier converter with unqoted handling "INTERPRET_AS_UPPER" and quoted handling "INTERPRET_CASE_SENSITIVE".
2020-11-26 21:54:00.976 FINER [c.e.a.d.BaseQueryRewriter] Push-down query generated with SapHanaSqlGenerationVisitor:
SELECT * FROM "SAPABAP1"."SKA1" WHERE 1=0
2020-11-26 21:54:00.978 FINE [c.e.a.j.ResultSetMetadataReader] Generating columns description for push-down query using BaseColumnMetadataReader:
SELECT * FROM "SAPABAP1"."SKA1" WHERE 1=0 

Another fun side note on SapHana: SELECT * FROM DUMMY where 'Y'='Y' ( i.e. expression "true" ) will work on hana, it was just false giving me a headache here - have a local fork now, will see how the git issue turns out, might even 
hand in my solution 😄
Thanks again, that´s one additional solution on your tab 😉

Cheers,
Malte

[exa-SebastianB]

Hi Malte,

that's great news, I am happy I could help. Would be awesome if you shared your fix with us and the OSS community. We will probably have to add an automated integration test (if we find a Hana docker image that is).

Best,

Sebastian

[mwellbro]

...aaaand here it is: https://github.com/exasol/virtual-schemas/pull/416 

As outlined in the pull request, it´s really barebones so maybe it´s not really "living up to the standard" that such a class should - it helped me, and if it´s "good enough" 
you guys may keep it 😉

[exa-Chris]

My be @mwellbro wants to share this in an Xpert Talk soon? 😉

[mwellbro]

@exa-Chris : Maybe mwellbro prefers to talk about things he actually knows something about 😉 This was more like a sort of "improvisation" - but sadly:

[exa-Chris]

Who else would love to hear @mwellbro talk about something he does with Exasol. I can't wait to fix the date. Will be a pleasure to have our most frequent Usergroup Guest, not in the Audience but actually talking. 🙂 Lets find a timeslot in January/February...