Virtual Schema - Extending dialect ?

Xpert

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 😉

1 ACCEPTED SOLUTION

Team Exasol
Team Exasol

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?

View solution in original post

10 REPLIES 10

Team Exasol
Team Exasol

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?

View solution in original post

Xpert

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

Community Manager
Community Manager

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

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

Xpert

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

mwellbro_0-1606826940043.png

 

Community Manager
Community Manager

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...

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

Xpert

ORA-00054 ( without the nowait & timeout part  ) 😉

Team Exasol
Team Exasol

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

Xpert

...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 😉

Xpert

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.

Xpert

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