Re: [Geotools-devel] JDBCDataStore blocks search hints on virtual tables
Hi Andrea, Thanks for your reply. Your proposal sounds good to me. I'll open a Jira item and prepare a pull request. Cheers, Stefan From: Andrea Aime Sent: Tuesday, December 27, 2022 11:52 AM To: Uhrig, Stefan Cc: Jody Garnett ; GeoTools Subject: Re: [Geotools-devel] JDBCDataStore blocks search hints on virtual tables On Wed, Dec 14, 2022 at 2:45 PM Uhrig, Stefan mailto:stefan.uh...@sap.com>> wrote: If you don't have objections, I'd like to add a way to let the SQLDialect decide. However, I'm not sure what the best way would be. I considered the following options: 1. Ask the SQLDialect via a method whether hints should be added, e.g. boolean handleSelectHintsOnVirtualTableQueries() returning false by default. 2. Add a new method to add "general" hints independent of the query type, e.g. handleGeneralHints(SimpleFeatureType featureType, Query query, StringBuffer sql), doing nothing by default. 3. Pass along the information on the type of query to handleSelectHints(). That would be an incompatible change though. Maybe overloading the method would be an option. Yup, I'm all for letting the dialect decide. Maybe a shorter method name would help (we're using 4 chars indents and 100 columns, need to try and be short and to the point). What about "boolean applyHintsOnVirtualTables()", defaulting to false? Cheers Andrea == GeoServer Professional Services from the experts! Visit http://bit.ly/gs-services-us<https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fbit.ly%2Fgs-services-us=05%7C01%7Cstefan.uhrig%40sap.com%7C240fcecb30c64bb630e008dae7f87684%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C638077351574610055%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C=s9T%2FhdxYNOYBiVbeGCWWppQpVrKOzZIovf%2BNCTxJREQ%3D=0> for more information. == Ing. Andrea Aime @geowolf Technical Lead GeoSolutions Group phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549 https://www.geosolutionsgroup.com/<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.geosolutionsgroup.com%2F=05%7C01%7Cstefan.uhrig%40sap.com%7C240fcecb30c64bb630e008dae7f87684%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C638077351574610055%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C=oHusSxypU4GGO8anTMUmbG9%2FWVDPDl8%2BJUJYpbVAIZE%3D=0> http://twitter.com/geosolutions_it<https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Ftwitter.com%2Fgeosolutions_it=05%7C01%7Cstefan.uhrig%40sap.com%7C240fcecb30c64bb630e008dae7f87684%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C638077351574766778%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C=eWHuHyaD7aKWTETEMaoS93oLhawcQKk4zDErrpPtKIo%3D=0> --- Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati "GDPR"), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 "GDPR" - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail ___ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
Re: [Geotools-devel] JDBCDataStore blocks search hints on virtual tables
On Wed, Dec 14, 2022 at 2:45 PM Uhrig, Stefan wrote: > > If you don’t have objections, I’d like to add a way to let the SQLDialect > decide. However, I’m not sure what the best way would be. I considered the > following options: > > > >1. Ask the SQLDialect via a method whether hints should be added, e.g. >boolean handleSelectHintsOnVirtualTableQueries() returning false by > default. >2. Add a new method to add “general” hints independent of the query >type, e.g. handleGeneralHints(SimpleFeatureType featureType, Query query, >StringBuffer sql), doing nothing by default. >3. Pass along the information on the type of query to >handleSelectHints(). That would be an incompatible change though. Maybe >overloading the method would be an option. > > Yup, I'm all for letting the dialect decide. Maybe a shorter method name would help (we're using 4 chars indents and 100 columns, need to try and be short and to the point). What about "boolean applyHintsOnVirtualTables()", defaulting to false? Cheers Andrea == GeoServer Professional Services from the experts! Visit http://bit.ly/gs-services-us for more information. == Ing. Andrea Aime @geowolf Technical Lead GeoSolutions Group phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549 https://www.geosolutionsgroup.com/ http://twitter.com/geosolutions_it --- Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail ___ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
Re: [Geotools-devel] JDBCDataStore blocks search hints on virtual tables
Hi Andrea, hi Jody, Thanks for your responses and insights. Looking at the SQLServerDialect class, I understand the reasons for blocking search hints on virtual tables. Unlike in SQL Server, hints can only be added at the very end of a query or subquery in HANA. For example, it is not possible to put a hint after a table name in a query as long as other query elements appear after the table name (like ORDER BY or GROUP BY), i.e. -- Okay SELECT col1, SUM(val1) FROM tab GROUP BY col1 WITH HINT(MAX_CONCURRENCY(1)) -- Not okay SELECT col1, SUM(val1) FROM tab WITH HINT(MAX_CONCURRENCY(1)) GROUP BY col1 In Hana, it's always okay to append a hint at the end of the query. The motivation to add hints to GeoServer view queries is described in https://stackoverflow.com/questions/73647691/how-to-add-sql-hints-to-geoserver-layers. The OP wants to add hints for the HANA optimizer. He can add the hint to the query he enters in GeoServer: select ID,DISTRICT_ID,DISTRICT_NAME,GEOLOC FROM MG_SPATIAL.V_GIS_DISTRICT WITH HINT OPTIMIZATION_LEVEL (RULE_BASED) But then the hint is only applied to the subquery when the actual query is generated: SELECT "ID","DISTRICT_ID","DISTRICT_NAME","GEOLOC".ST_AsBinary() as "GEOLOC" FROM (select ID,DISTRICT_ID,DISTRICT_NAME,GEOLOC FROM MG_SPATIAL.V_GIS_DISTRICT WITH HINT OPTIMIZATION_LEVEL (RULE_BASED) ) as "vtable" WHERE "GEOLOC".ST_IntersectsRectPlanar(ST_GeomFromWKB(?, 3067), ST_GeomFromWKB(?, 3067)) = 1 LIMIT 100 OFFSET 0 As a result, the optimizer applies the hint only to the subquery, but not on the whole query. What the OP would like to achieve is the following query: SELECT "ID","DISTRICT_ID","DISTRICT_NAME","GEOLOC".ST_AsBinary() as "GEOLOC" FROM (select ID,DISTRICT_ID,DISTRICT_NAME,GEOLOC FROM MG_SPATIAL.V_GIS_DISTRICT ) as "vtable" WHERE "GEOLOC".ST_IntersectsRectPlanar(ST_GeomFromWKB(?, 3067), ST_GeomFromWKB(?, 3067)) = 1 LIMIT 100 OFFSET 0 WITH HINT OPTIMIZATION_LEVEL (RULE_BASED) Hence, it would be great if a SQLDialect could decide itself whether it wants to add hints to a query with an embedded VirtualTable query. If you don't have objections, I'd like to add a way to let the SQLDialect decide. However, I'm not sure what the best way would be. I considered the following options: 1. Ask the SQLDialect via a method whether hints should be added, e.g. boolean handleSelectHintsOnVirtualTableQueries() returning false by default. 2. Add a new method to add "general" hints independent of the query type, e.g. handleGeneralHints(SimpleFeatureType featureType, Query query, StringBuffer sql), doing nothing by default. 3. Pass along the information on the type of query to handleSelectHints(). That would be an incompatible change though. Maybe overloading the method would be an option. Maybe you have better ideas. What do you think? Thanks and best regards, Stefan From: Andrea Aime Sent: Wednesday, December 14, 2022 10:20 AM To: Uhrig, Stefan Cc: GeoTools Subject: Re: [Geotools-devel] JDBCDataStore blocks search hints on virtual tables HI Stefan, a virtual table can be built around any resultset returning statement (e.g., a stored procedure), and the JDBCDataStore machinery makes no attempt to understand the SQL being provided, it just uses it as a subquery in the from clause. Query hints, at least in the databases that I'm familiar with, must be provided in very specific positions, which requires an understanding of the SQL involved. If you check the SQLServerDialect handleSelectHints, it makes assumptions on how the query is built to find the exact point where a hint can be added (it's pretty much added mid-query). The assumption is fair because we control how a select against a table is built, the same cannot be said for virtual tables, where anything goes in the user provided query (a table might not be involved at all). Is SAP HANA so freeform that one can put query hints pretty much anywhere, regardless of what is found in the user provided query? Even if it was, you'd have to modify the API to allow other database (SQLServer) to tell apart actual table vs virtual table. Cheers Andrea On Tue, Dec 13, 2022 at 9:36 PM Uhrig, Stefan via GeoTools-Devel mailto:geotools-devel@lists.sourceforge.net>> wrote: Hi all, Recently, a group of GeoServer users on HANA requested the possibility to append hints to SQL queries. Luckily, there was already SQLDialect#handleSelectHints(). So, I could just override the method in HanaDialect and append the hints to the query, which I did in [GEOT-7230] (https://github.com/geotools/geotools/pull/4064<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fgeotools%2Fgeotools%2Fpull%2F4064=05%7C01%7Cstefan.uhrig%40sap.com%7Cb28354be92e248229e1008daddb458d3%7C42f7676cf
Re: [Geotools-devel] JDBCDataStore blocks search hints on virtual tables
HI Stefan, a virtual table can be built around any resultset returning statement (e.g., a stored procedure), and the JDBCDataStore machinery makes no attempt to understand the SQL being provided, it just uses it as a subquery in the from clause. Query hints, at least in the databases that I'm familiar with, must be provided in very specific positions, which requires an understanding of the SQL involved. If you check the SQLServerDialect handleSelectHints, it makes assumptions on how the query is built to find the exact point where a hint can be added (it's pretty much added mid-query). The assumption is fair because we control how a select against a table is built, the same cannot be said for virtual tables, where anything goes in the user provided query (a table might not be involved at all). Is SAP HANA so freeform that one can put query hints pretty much anywhere, regardless of what is found in the user provided query? Even if it was, you'd have to modify the API to allow other database (SQLServer) to tell apart actual table vs virtual table. Cheers Andrea On Tue, Dec 13, 2022 at 9:36 PM Uhrig, Stefan via GeoTools-Devel < geotools-devel@lists.sourceforge.net> wrote: > Hi all, > > > > Recently, a group of GeoServer users on HANA requested the possibility to > append hints to SQL queries. Luckily, there was already > SQLDialect#handleSelectHints(). So, I could just override the method in > HanaDialect and append the hints to the query, which I did in [GEOT-7230] ( > https://github.com/geotools/geotools/pull/4064). > > > > Now the group of users who requested the feature noticed that it works for > common GeoServer layers, but not for GeoServer views, which are based on > GeoTools’ VirtualTable. I investigated and found that JDBCDataStore > deliberately blocks the addition of search hints ( > https://github.com/geotools/geotools/blob/29394a11ce3128a625f5251abeb9b0b09cc0105b/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L3505 > ). > > > > I found that the SQLDialect#handleSelectHints() has been introduced with > “[GEOT-4707] Add a flag to force spatial index usage in sql server” ( > https://github.com/geotools/geotools/pull/371/files). Adding search hints > to VirtualTable queries was blocked from the start. > > > > Does someone know why search hints are not added to VirtualTable queries? > > > > In the HANA use case, I could not identify any reason yet why the addition > of search hints should be blocked. I might oversee something though. If > there is no urgent reason to block search hints in VirtualTable queries, > what would be a good way to support search hints on VirtualTable queries in > a SQLDialect? Would the introduction of an additional function like > appendSearchHintsToVirtualTableQueries() be acceptable? > > > > Thank you and best regards, > > Stefan > > > > > ___ > GeoTools-Devel mailing list > GeoTools-Devel@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/geotools-devel > -- Regards, Andrea Aime == GeoServer Professional Services from the experts! Visit http://bit.ly/gs-services-us for more information. == Ing. Andrea Aime @geowolf Technical Lead GeoSolutions Group phone: +39 0584 962313 fax: +39 0584 1660272 mob: +39 339 8844549 https://www.geosolutionsgroup.com/ http://twitter.com/geosolutions_it --- Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene notizia. This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. We remind that - as provided by European Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or the information herein by anyone other than the intended recipient is prohibited. If you have received this email by mistake, please notify us immediately by telephone or e-mail ___ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
Re: [Geotools-devel] JDBCDataStore blocks search hints on virtual tables
I am sure it was just lack of scope; since the virtual tables can be any SQL query - it was probably not obvious how to integrate a select hint? If you have a join for example which select would you like to add the hint to? Or would you like to create a subquery and do the select on the result? It sounds like a good idea though if you are interested in adding the functionality. -- Jody Garnett On Tue, Dec 13, 2022 at 12:36 PM Uhrig, Stefan via GeoTools-Devel < geotools-devel@lists.sourceforge.net> wrote: > Hi all, > > > > Recently, a group of GeoServer users on HANA requested the possibility to > append hints to SQL queries. Luckily, there was already > SQLDialect#handleSelectHints(). So, I could just override the method in > HanaDialect and append the hints to the query, which I did in [GEOT-7230] ( > https://github.com/geotools/geotools/pull/4064). > > > > Now the group of users who requested the feature noticed that it works for > common GeoServer layers, but not for GeoServer views, which are based on > GeoTools’ VirtualTable. I investigated and found that JDBCDataStore > deliberately blocks the addition of search hints ( > https://github.com/geotools/geotools/blob/29394a11ce3128a625f5251abeb9b0b09cc0105b/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L3505 > ). > > > > I found that the SQLDialect#handleSelectHints() has been introduced with > “[GEOT-4707] Add a flag to force spatial index usage in sql server” ( > https://github.com/geotools/geotools/pull/371/files). Adding search hints > to VirtualTable queries was blocked from the start. > > > > Does someone know why search hints are not added to VirtualTable queries? > > > > In the HANA use case, I could not identify any reason yet why the addition > of search hints should be blocked. I might oversee something though. If > there is no urgent reason to block search hints in VirtualTable queries, > what would be a good way to support search hints on VirtualTable queries in > a SQLDialect? Would the introduction of an additional function like > appendSearchHintsToVirtualTableQueries() be acceptable? > > > > Thank you and best regards, > > Stefan > > > > > ___ > GeoTools-Devel mailing list > GeoTools-Devel@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/geotools-devel > ___ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
