24-11-2020 01:20 PM - edited 24-11-2020 01:21 PM
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 😉
Solved! Go to Solution.
24-11-2020 09:20 PM
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?
24-11-2020 09:20 PM
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?
26-11-2020 11:00 PM
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
30-11-2020 09:07 AM
My be @mwellbro wants to share this in an Xpert Talk soon? 😉
01-12-2020 01:49 PM - edited 01-12-2020 01:49 PM
@exa-Chris : Maybe mwellbro prefers to talk about things he actually knows something about 😉 This was more like a sort of "improvisation" - but sadly:
01-12-2020 02:11 PM
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...
02-12-2020 10:28 AM
27-11-2020 07:20 AM
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
29-11-2020 10:11 PM
...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 😉
25-11-2020 01:39 AM
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.
24-11-2020 01:27 PM
@exa-Anastasiia & @exa-SebastianB , might I draw upon your expertise for this one ? 🙂
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In