-
Type:
Story
-
Status: Done
-
Resolution: Done
-
Affects Version/s: None
-
Fix Version/s: Search Services 2.0
-
Labels:None
-
Epic Link:
-
Sprint:Team Ninja-King - S&I 29, Team Ninja-King - S&I 30, Team Ninja-King - S&I 31, Team Ninja-King - S&I 32, Team Ninja-King - S&I 33
-
Release Train:Southall
-
Delivery Team:Search
-
Story Points:20
Background
As a business user
When I connect to Insight Engine from Tableau
Then I want to be able use dates functions in the GROUP BY clause
The Tableau functions we need to support are DAY, MONTH, YEAR, Extract <unit of time> from <date field> (this is the function DAY, MONTH, YEAR translates to) and DATE
e.g.
SELECT ... FROM ... WHERE ... GROUP BY MONTH(cm_modified)
SELECT ... FROM ... WHERE ... GROUP BY YEAR(cm_modified)
SELECT ... FROM ... WHERE ... GROUP BY Extract YEAR from cm_modified
Potential Approach
Option 3
Handle the date functions "properly" in the 2.0 branch and then backport to other relevant branches
Implementation Recommendations
org.alfresco.solr.query.stream.DistributedSqlTimeSeriesIT#timeseries_descDayOrder has been used as a test to explore the behaviour.
The initial exploration of how Calcite handles the transformation revealed to be extremely convoluted and expensive.
So the approached moved to translate the functions calls to the virtual fields currently in place.
It is possible to replace the original SQL string doing the transformation from
MONTH(cm_date) to cm_date_month
YEAR(cm_date) to cm_date_year ,
EXTRACT(<UNIT OF TIME> FROM cm_date) to cm_date_<UNIT OF TIME>
This can be achieved introducing the replacement in an ad hoc transformer following the same approach presented here:
org/alfresco/solr/sql/transformer/SqlInClauseTransformer.java:110
A brutal approach just doing class check is implemented in the draf branch just as Poc
A recommended approach would be to add an ad hoc transformer for dates, with a similar Structure than the SqlInClauseTransformer.
For DAY(cm_date) the solution is different as it is not supported by the Calcite parser, so before parsing it, it must be replaced in here:
org.alfresco.solr.sql.SqlUtil
N.B. if real fields are introduced instead of the virtual ones, extra care must be taken in the implementation, but it is not of much difference
Acceptance Criteria
- no regression in the date sql WHERE related tests
- mentioned date functions to work in the WHERE
- add e2e tests
- Depends on
-
SEARCH-2169 Upgrade Insight Engine to Calcite 1.12.0
- Done
-
SEARCH-2171 Index dates in a decomposed way (year, month, day and etc)
- Done
- relates to
-
SEARCH-2141 Spike: Planning for CDATA Date support
- Done
-
SEARCH-2173 Support for Date Functions in ORDER BY Clause
- Done
- mentioned in
-
Page Loading...