-
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:13
Background
As a business user
When I connect to Insight Engine from Tableau
Then I want to be able use dates functions in the SELECT 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 MONTH(cm_modified) FROM ...
Potential Approach
Option 3
Handle the date functions "properly" in the 2.0 branch and then backport to other relevant branches
Implementation Recommendations
1) DATE functions (like MONTH or YEAR) doesn't work with Calcite 1.11.0, that is the version shipped with SOLR 6.6.x.
However, when using Calcite 1.12.0, DATE functions seem to work fine
Identify if Calcite 1.12 properly allow for the syntax MONTH(<date_field>), that is supposed to be automatically translated to the EXTRACT
2) DATA TYPE
Type Solr trieDate is parsed as Java util date in the SolrSchema
3) DATE PARSING IN DIVISION
In the org.alfresco.solr.sql.operation.ArithmeticOperation#getResult the date must be parsed and not the string.
So the division can happen between the date ms and 1DAY ms
4)IMPLEMENT EXTRACT OPERATION
It is necessary to implement an extract operation.
This can be implemented as a mathematical manipulation such as :
1970 + Days/365 ( this is a naive example for extract YEAR)
An alternative could be to add a new class here : org.alfresco.solr.sql.operation
let’s say DateOperations
in here : org/alfresco/solr/sql/SolrRules.java:124
Flag with the unit of time will be translated to a number of days
then it must be implemented an optimal way to extract year, month, day from the epoch
OR
implement directly an extract operation that parse the date and return the appropriate unit of time
OR
Re-Indexing Alternative
If we prefer to change the schema, and that could happen because of WHERE / GROUP BY, this implementation could be simplified + better query time performance but bigger disk space.
These new fields need to be stored.
This would be the recommended approach.
5) Select * and columns
The org/alfresco/solr/sql/SolrEnumerator.java:184 must address the Date type and return a java Date Object
6) Deprecate the transformer as it is unlikely necessary anymore
Note:
See https://git.alfresco.com/search_discovery/insightengine/commit/28f57c7d19af663bdd6278d68bc4537d4d07dc30 for details
The fields will be added to the index in a separate JIRA.
Acceptance Criteria
- no regression in the date sql related tests
- mentioned date functions to work in the SELECT
- add e2e tests related to this work
- 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
- mentioned in
-
Page Loading...