LENS-851 : queries where results of two storage tables of same fact are unioned, the rows should be aggregated
Project: http://git-wip-us.apache.org/repos/asf/lens/repo Commit: http://git-wip-us.apache.org/repos/asf/lens/commit/c445730c Tree: http://git-wip-us.apache.org/repos/asf/lens/tree/c445730c Diff: http://git-wip-us.apache.org/repos/asf/lens/diff/c445730c Branch: refs/heads/LENS-581 Commit: c445730c40533b5f51d239dc501a631416a54979 Parents: 2870be7 Author: Rajat Khandelwal <[email protected]> Authored: Thu Nov 19 10:46:50 2015 +0530 Committer: Amareshwari Sriramadasu <[email protected]> Committed: Thu Nov 19 10:46:50 2015 +0530 ---------------------------------------------------------------------- lens-api/src/main/resources/lens-errors.conf | 6 + .../lens/cube/error/LensCubeErrorCode.java | 1 + .../lens/cube/parse/CubeQueryConfUtil.java | 2 + .../lens/cube/parse/CubeQueryContext.java | 23 +- .../org/apache/lens/cube/parse/HQLParser.java | 14 +- .../lens/cube/parse/SimpleHQLContext.java | 55 +--- .../parse/SingleFactMultiStorageHQLContext.java | 193 ++++++++++++-- .../apache/lens/cube/parse/UnionHQLContext.java | 60 +---- .../src/main/resources/olap-query-conf.xml | 15 ++ .../apache/lens/cube/parse/CubeTestSetup.java | 88 +++++-- .../lens/cube/parse/TestAggregateResolver.java | 72 +++--- .../lens/cube/parse/TestCubeRewriter.java | 252 ++++++++++++------- .../cube/parse/TestDenormalizationResolver.java | 30 +-- .../lens/cube/parse/TestExpressionResolver.java | 12 +- .../org/apache/lens/cube/parse/TestQuery.java | 100 +++++--- src/site/apt/user/olap-query-conf.apt | 38 +-- 16 files changed, 617 insertions(+), 344 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-api/src/main/resources/lens-errors.conf ---------------------------------------------------------------------- diff --git a/lens-api/src/main/resources/lens-errors.conf b/lens-api/src/main/resources/lens-errors.conf index 7526456..f50433a 100644 --- a/lens-api/src/main/resources/lens-errors.conf +++ b/lens-api/src/main/resources/lens-errors.conf @@ -282,6 +282,12 @@ lensCubeErrorsForQuery = [ httpStatusCode = ${BAD_REQUEST} errorMsg = "No storage table available for candidate fact: %s" } + + { + errorCode = 3031 + httpStatusCode = ${BAD_REQUEST} + errorMsg = "The query is answerable from two storages but union is disabled." + } ] lensCubeErrorsForMetastore = [ http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-cube/src/main/java/org/apache/lens/cube/error/LensCubeErrorCode.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/error/LensCubeErrorCode.java b/lens-cube/src/main/java/org/apache/lens/cube/error/LensCubeErrorCode.java index 2119b64..24fb80b 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/error/LensCubeErrorCode.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/error/LensCubeErrorCode.java @@ -53,6 +53,7 @@ public enum LensCubeErrorCode { NO_CANDIDATE_FACT_AVAILABLE(3028, 1200), NO_CANDIDATE_DIM_STORAGE_TABLES(3029, 1300), NO_STORAGE_TABLE_AVAIABLE(3030, 1400), + STORAGE_UNION_DISABLED(3031, 100), ERROR_IN_ENTITY_DEFINITION(3101, 100); public LensErrorInfo getLensErrorInfo() { http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-cube/src/main/java/org/apache/lens/cube/parse/CubeQueryConfUtil.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/CubeQueryConfUtil.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/CubeQueryConfUtil.java index 87972c8..d96b567 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/CubeQueryConfUtil.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/CubeQueryConfUtil.java @@ -51,6 +51,8 @@ public final class CubeQueryConfUtil { public static final String ENABLE_SELECT_TO_GROUPBY = "lens.cube.query.promote.select.togroupby"; public static final String ENABLE_ATTRFIELDS_ADD_DISTINCT = "lens.cube.query.enable.attrfields.add.distinct"; public static final boolean DEFAULT_ATTR_FIELDS_ADD_DISTINCT = true; + public static final String ENABLE_STORAGES_UNION = "lens.cube.query.enable.storages.union"; + public static final boolean DEFAULT_ENABLE_STORAGES_UNION = false; public static final String REPLACE_TIMEDIM_WITH_PART_COL = "lens.cube.query.replace.timedim"; public static final boolean DEFAULT_MULTI_TABLE_SELECT = true; http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-cube/src/main/java/org/apache/lens/cube/parse/CubeQueryContext.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/CubeQueryContext.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/CubeQueryContext.java index 450d172..a660133 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/CubeQueryContext.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/CubeQueryContext.java @@ -19,6 +19,8 @@ package org.apache.lens.cube.parse; +import static org.apache.lens.cube.parse.CubeQueryConfUtil.*; + import static org.apache.hadoop.hive.ql.parse.HiveParser.Identifier; import static org.apache.hadoop.hive.ql.parse.HiveParser.TOK_TABLE_OR_COL; import static org.apache.hadoop.hive.ql.parse.HiveParser.TOK_TMP_FILE; @@ -132,9 +134,9 @@ public class CubeQueryContext implements TrackQueriedColumns { protected final Map<Dimension, Set<CandidateDim>> candidateDims = new HashMap<Dimension, Set<CandidateDim>>(); // query trees - @Getter + @Getter @Setter private ASTNode havingAST; - @Getter + @Getter @Setter private ASTNode selectAST; // Will be set after the Fact is picked and time ranges replaced @@ -142,7 +144,7 @@ public class CubeQueryContext implements TrackQueriedColumns { @Setter private ASTNode whereAST; - @Getter + @Getter @Setter private ASTNode orderByAST; // Setter is used in promoting the select when promotion is on. @Getter @@ -667,6 +669,9 @@ public class CubeQueryContext implements TrackQueriedColumns { public Integer getLimitValue() { return qb.getParseInfo().getDestLimit(getClause()); } + public void setLimitValue(Integer value) { + qb.getParseInfo().setDestLimit(getClause(), value); + } private String getStorageStringWithAlias(CandidateFact fact, Map<Dimension, CandidateDim> dimsToQuery, String alias) { if (cubeTbls.get(alias) instanceof CubeInterface) { @@ -764,14 +769,14 @@ public class CubeQueryContext implements TrackQueriedColumns { } } } - conf.set(CubeQueryConfUtil.NON_EXISTING_PARTITIONS, partsStr); + conf.set(NON_EXISTING_PARTITIONS, partsStr); } else { - conf.unset(CubeQueryConfUtil.NON_EXISTING_PARTITIONS); + conf.unset(NON_EXISTING_PARTITIONS); } } public String getNonExistingParts() { - return conf.get(CubeQueryConfUtil.NON_EXISTING_PARTITIONS); + return conf.get(NON_EXISTING_PARTITIONS); } private Map<Dimension, CandidateDim> pickCandidateDimsToQuery(Set<Dimension> dimensions) throws LensException { @@ -942,6 +947,9 @@ public class CubeQueryContext implements TrackQueriedColumns { return new DimOnlyHQLContext(dimsToQuery, query); } else if (facts.size() == 1 && facts.iterator().next().getStorageTables().size() > 1) { //create single fact with multiple storage context + if (!conf.getBoolean(ENABLE_STORAGES_UNION, DEFAULT_ENABLE_STORAGES_UNION)) { + throw new LensException(LensCubeErrorCode.STORAGE_UNION_DISABLED.getLensErrorInfo()); + } return new SingleFactMultiStorageHQLContext(facts.iterator().next(), dimsToQuery, query); } else if (facts.size() == 1 && facts.iterator().next().getStorageTables().size() == 1) { // create single fact context @@ -1129,8 +1137,7 @@ public class CubeQueryContext implements TrackQueriedColumns { } public boolean shouldReplaceTimeDimWithPart() { - return getConf().getBoolean(CubeQueryConfUtil.REPLACE_TIMEDIM_WITH_PART_COL, - CubeQueryConfUtil.DEFAULT_REPLACE_TIMEDIM_WITH_PART_COL); + return getConf().getBoolean(REPLACE_TIMEDIM_WITH_PART_COL, DEFAULT_REPLACE_TIMEDIM_WITH_PART_COL); } public String getPartitionColumnOfTimeDim(String timeDimName) { http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-cube/src/main/java/org/apache/lens/cube/parse/HQLParser.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/HQLParser.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/HQLParser.java index 16e1aa3..9a9d134 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/HQLParser.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/HQLParser.java @@ -30,6 +30,7 @@ import java.util.regex.Pattern; import org.apache.lens.server.api.error.LensException; +import org.apache.commons.lang.StringUtils; import org.apache.hadoop.hive.conf.HiveConf; import org.apache.hadoop.hive.ql.Context; import org.apache.hadoop.hive.ql.exec.FunctionRegistry; @@ -49,8 +50,14 @@ public final class HQLParser { private HQLParser() { } + public static final Pattern P_WSPACE = Pattern.compile("\\s+"); + public static boolean isTableColumnAST(ASTNode astNode) { + return !(astNode == null || astNode.getChildren() == null || astNode.getChildCount() != 2) && astNode.getChild(0) + .getType() == HiveParser.TOK_TABLE_OR_COL && astNode.getChild(1).getType() == HiveParser.Identifier; + } + public interface ASTNodeVisitor { void visit(TreeNode node) throws LensException; } @@ -786,8 +793,11 @@ public final class HQLParser { } // Compare text. For literals, comparison is case sensitive - if ((n1.getToken().getType() == StringLiteral && !n1.getText().equals(n2.getText())) - || !n1.getText().equalsIgnoreCase(n2.getText())) { + if ((n1.getToken().getType() == StringLiteral && !StringUtils.equals(n1.getText(), n2.getText()))) { + return false; + } + + if (!StringUtils.equalsIgnoreCase(n1.getText(), n2.getText())) { return false; } http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-cube/src/main/java/org/apache/lens/cube/parse/SimpleHQLContext.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/SimpleHQLContext.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/SimpleHQLContext.java index 067a37a..62ceb12 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/SimpleHQLContext.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/SimpleHQLContext.java @@ -26,6 +26,7 @@ import org.apache.lens.server.api.error.LensException; import org.apache.commons.lang.StringUtils; +import lombok.Data; import lombok.extern.slf4j.Slf4j; /** @@ -34,6 +35,7 @@ import lombok.extern.slf4j.Slf4j; * Making this as an abstract class because it provides constructors without all expressions being set. */ @Slf4j +@Data public abstract class SimpleHQLContext implements HQLContextInterface { private String select; @@ -131,57 +133,4 @@ public abstract class SimpleHQLContext implements HQLContextInterface { } return queryFormat.toString(); } - - public String getFrom() { - return from; - } - - public String getWhere() { - return where; - } - - public String getSelect() { - return select; - } - - public String getGroupby() { - return groupby; - } - - public String getHaving() { - return having; - } - - public String getOrderby() { - return orderby; - } - - public Integer getLimit() { - return limit; - } - - protected void setFrom(String from) { - this.from = from; - } - - protected void setWhere(String where) { - this.where = where; - } - - protected void setSelect(String select) { - this.select = select; - } - - protected void setGroupby(String groupby) { - this.groupby = groupby; - } - - protected void setHaving(String having) { - this.having = having; - } - - protected void setOrderby(String orderby) { - this.orderby = orderby; - } - } http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-cube/src/main/java/org/apache/lens/cube/parse/SingleFactMultiStorageHQLContext.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/SingleFactMultiStorageHQLContext.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/SingleFactMultiStorageHQLContext.java index 15a98dd..418ef5a 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/SingleFactMultiStorageHQLContext.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/SingleFactMultiStorageHQLContext.java @@ -19,38 +19,203 @@ package org.apache.lens.cube.parse; +import static org.apache.lens.cube.parse.HQLParser.getString; + +import static org.apache.hadoop.hive.ql.parse.HiveParser.*; + import java.util.ArrayList; +import java.util.HashMap; import java.util.Map; import org.apache.lens.cube.metadata.Dimension; import org.apache.lens.server.api.error.LensException; -import lombok.Getter; +import org.apache.hadoop.hive.ql.lib.Node; +import org.apache.hadoop.hive.ql.parse.ASTNode; +import org.apache.hadoop.hive.ql.parse.HiveParser; + +import org.antlr.runtime.CommonToken; +import org.antlr.runtime.tree.Tree; + +import lombok.Data; public class SingleFactMultiStorageHQLContext extends UnionHQLContext { - @Getter - private CubeQueryContext query = null; - private CandidateFact fact = null; + int aliasCounter = 0; + + @Data + public static class HashableASTNode { + private ASTNode ast; + private int hashCode = -1; + private boolean hashCodeComputed = false; + + public HashableASTNode(ASTNode ast) { + this.ast = ast; + } + + public void setAST(ASTNode ast) { + this.ast = ast; + hashCodeComputed = false; + } + + public ASTNode getAST() { + return ast; + } + + @Override + public int hashCode() { + if (!hashCodeComputed) { + hashCode = HQLParser.getString(ast).hashCode(); + hashCodeComputed = true; + } + return hashCode; + } + + @Override + public boolean equals(Object o) { + return o instanceof HashableASTNode && this.hashCode() == o.hashCode() && HQLParser.getString(this.getAST()) + .trim().equalsIgnoreCase(HQLParser.getString(((HashableASTNode) o).getAST()).trim()); + } + } + + private Map<HashableASTNode, ASTNode> innerToOuterASTs = new HashMap<>(); SingleFactMultiStorageHQLContext(CandidateFact fact, Map<Dimension, CandidateDim> dimsToQuery, CubeQueryContext query) throws LensException { - this.query = query; - this.fact = fact; - setUnionContexts(fact, dimsToQuery, query); + super(query, fact); + processSelectAST(); + processGroupByAST(); + processHavingAST(); + processOrderByAST(); + processLimit(); + setHqlContexts(getUnionContexts(fact, dimsToQuery, query)); + } + + private void processSelectAST() { + query.getSelectFinalAliases().clear(); + ASTNode originalSelectAST = HQLParser.copyAST(query.getSelectAST()); + query.setSelectAST(new ASTNode(originalSelectAST.getToken())); + ASTNode outerSelectAST = processExpression(originalSelectAST); + setSelect(HQLParser.getString(outerSelectAST)); + } + + private void processGroupByAST() { + if (query.getGroupByAST() != null) { + setGroupby(getString(processExpression(query.getGroupByAST()))); + } } - private void setUnionContexts(CandidateFact fact, Map<Dimension, CandidateDim> dimsToQuery, CubeQueryContext query) + private void processHavingAST() throws LensException { + if (query.getHavingAST() != null) { + setHaving(HQLParser.getString(processExpression(query.getHavingAST()))); + query.setHavingAST(null); + } + } + + private void processOrderByAST() { + if (query.getOrderByAST() != null) { + setOrderby(HQLParser.getString(processExpression(query.getOrderByAST()))); + query.setOrderByAST(null); + } + } + + private void processLimit() { + setLimit(query.getLimitValue()); + query.setLimitValue(null); + } + /* + Perform a DFS on the provided AST, and Create an AST of similar structure with changes specific to the + inner query - outer query dynamics. The resultant AST is supposed to be used in outer query. + + Base cases: + 1. ast is null => null + 2. ast is table.column => add this to inner select expressions, generate alias, return cube.alias. Memoize the + mapping table.column => cube.alias + 3. ast is aggregate_function(table.column) => add aggregate_function(table.column) to inner select expressions, + generate alias, return aggregate_function(cube.alias). Memoize the mapping + aggregate_function(table.column) => aggregate_function(cube.alias) + Assumption is aggregate_function is transitive i.e. f(a,b,c,d) = f(f(a,b), f(c,d)). SUM, MAX, MIN etc + are transitive, while AVG, COUNT etc are not. For non-transitive aggregate functions, the re-written + query will be incorrect. + 4. If given ast is memoized as mentioned in the above cases, return the mapping. + + Recursive case: + Copy the root node, process children recursively and add as children to the copied node. Return the copied node. + */ + private ASTNode processExpression(ASTNode astNode) { + if (astNode == null) { + return null; + } + if (innerToOuterASTs.containsKey(new HashableASTNode(astNode))) { + return innerToOuterASTs.get(new HashableASTNode(astNode)); + } + if (HQLParser.isAggregateAST(astNode)) { + ASTNode innerSelectASTWithoutAlias = HQLParser.copyAST(astNode); + ASTNode innerSelectExprAST = new ASTNode(new CommonToken(HiveParser.TOK_SELEXPR)); + innerSelectExprAST.addChild(innerSelectASTWithoutAlias); + String alias = decideAlias(astNode); + ASTNode aliasNode = new ASTNode(new CommonToken(Identifier, alias)); + innerSelectExprAST.addChild(aliasNode); + addToInnerSelectAST(innerSelectExprAST); + ASTNode dotAST = getDotAST(query.getCube().getName(), alias); + ASTNode outerAST = new ASTNode(new CommonToken(TOK_FUNCTION)); + outerAST.addChild(new ASTNode(new CommonToken(Identifier, astNode.getChild(0).getText()))); + outerAST.addChild(dotAST); + innerToOuterASTs.put(new HashableASTNode(innerSelectASTWithoutAlias), outerAST); + return outerAST; + } else if (HQLParser.isTableColumnAST(astNode)) { + ASTNode innerSelectASTWithoutAlias = HQLParser.copyAST(astNode); + ASTNode innerSelectExprAST = new ASTNode(new CommonToken(HiveParser.TOK_SELEXPR)); + innerSelectExprAST.addChild(innerSelectASTWithoutAlias); + String alias = decideAlias(astNode); + ASTNode aliasNode = new ASTNode(new CommonToken(Identifier, alias)); + innerSelectExprAST.addChild(aliasNode); + addToInnerSelectAST(innerSelectExprAST); + ASTNode outerAST = getDotAST(query.getCube().getName(), alias); + innerToOuterASTs.put(new HashableASTNode(innerSelectASTWithoutAlias), outerAST); + return outerAST; + } else { + ASTNode outerHavingExpression = new ASTNode(astNode); + if (astNode.getChildren() != null) { + for (Node child : astNode.getChildren()) { + outerHavingExpression.addChild(processExpression((ASTNode) child)); + } + } + return outerHavingExpression; + } + } + + private void addToInnerSelectAST(ASTNode selectExprAST) { + if (query.getSelectAST() == null) { + query.setSelectAST(new ASTNode(new CommonToken(TOK_SELECT))); + } + query.getSelectAST().addChild(selectExprAST); + } + + private ASTNode getDotAST(String tableAlias, String fieldAlias) { + ASTNode child = new ASTNode(new CommonToken(DOT, ".")); + child.addChild(new ASTNode(new CommonToken(TOK_TABLE_OR_COL, "TOK_TABLE_OR_COL"))); + child.getChild(0).addChild(new ASTNode(new CommonToken(Identifier, tableAlias))); + child.addChild(new ASTNode(new CommonToken(Identifier, fieldAlias))); + return child; + } + + private String decideAlias(Tree child) { + // Can add intelligence in aliases someday. Not required though :) + return "alias" + (aliasCounter++); + } + + private static ArrayList<HQLContextInterface> getUnionContexts(CandidateFact fact, Map<Dimension, CandidateDim> + dimsToQuery, CubeQueryContext query) throws LensException { - hqlContexts = new ArrayList<HQLContextInterface>(); - String alias = getQuery().getAliasForTableName(getQuery().getCube().getName()); + ArrayList<HQLContextInterface> contexts = new ArrayList<>(); + String alias = query.getAliasForTableName(query.getCube().getName()); for (String storageTable : fact.getStorageTables()) { SingleFactHQLContext ctx = new SingleFactHQLContext(fact, storageTable + " " + alias, dimsToQuery, query, - fact.getWhereClause(storageTable.substring(storageTable.indexOf(".") + 1))); - hqlContexts.add(ctx); + fact.getWhereClause(storageTable.substring(storageTable.indexOf(".") + 1))); + contexts.add(ctx); } - super.setHqlContexts(hqlContexts); + return contexts; } - } http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-cube/src/main/java/org/apache/lens/cube/parse/UnionHQLContext.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/UnionHQLContext.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/UnionHQLContext.java index 9005826..c9ba561 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/UnionHQLContext.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/UnionHQLContext.java @@ -20,70 +20,36 @@ package org.apache.lens.cube.parse; import java.util.ArrayList; -import java.util.LinkedHashSet; import java.util.List; -import java.util.Set; import org.apache.lens.server.api.error.LensException; import org.apache.commons.lang.NotImplementedException; -import org.apache.commons.lang.StringUtils; import lombok.AllArgsConstructor; -import lombok.Getter; -import lombok.NoArgsConstructor; -import lombok.Setter; +import lombok.RequiredArgsConstructor; @AllArgsConstructor -@NoArgsConstructor -public abstract class UnionHQLContext implements HQLContextInterface { +@RequiredArgsConstructor +public abstract class UnionHQLContext extends SimpleHQLContext { + protected final CubeQueryContext query; + private final CandidateFact fact; - @Getter - @Setter - List<HQLContextInterface> hqlContexts = new ArrayList<HQLContextInterface>(); + List<HQLContextInterface> hqlContexts = new ArrayList<>(); - @Override - public String toHQL() throws LensException { - Set<String> queryParts = new LinkedHashSet<String>(); + public void setHqlContexts(List<HQLContextInterface> hqlContexts) throws LensException { + this.hqlContexts = hqlContexts; + StringBuilder queryParts = new StringBuilder("("); + String sep = ""; for (HQLContextInterface ctx : hqlContexts) { - queryParts.add(ctx.toHQL()); + queryParts.append(sep).append(ctx.toHQL()); + sep = " UNION ALL "; } - return StringUtils.join(queryParts, " UNION ALL "); - } - - @Override - public String getSelect() { - throw new NotImplementedException("Not Implemented"); - } - - @Override - public String getFrom() { - throw new NotImplementedException("Not Implemented"); + setFrom(queryParts.append(") ").append(query.getCube().getName()).toString()); } @Override public String getWhere() { throw new NotImplementedException("Not Implemented"); } - - @Override - public String getGroupby() { - throw new NotImplementedException("Not Implemented"); - } - - @Override - public String getHaving() { - throw new NotImplementedException("Not Implemented"); - } - - @Override - public String getOrderby() { - throw new NotImplementedException("Not Implemented"); - } - - @Override - public Integer getLimit() { - throw new NotImplementedException("Not Implemented"); - } - } http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-cube/src/main/resources/olap-query-conf.xml ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/resources/olap-query-conf.xml b/lens-cube/src/main/resources/olap-query-conf.xml index 4c7b7fa..1436cd1 100644 --- a/lens-cube/src/main/resources/olap-query-conf.xml +++ b/lens-cube/src/main/resources/olap-query-conf.xml @@ -181,6 +181,21 @@ </description> </property> <property> + <name>lens.cube.query.enable.storages.union</name> + <value>false</value> + <description>Sometimes One storage table doesn't contain all required partitions, and the query needs to be + answered from two storage tables. Enabling this (make value = <true>) allows rewrite of such queries. + If it's <false>, then such queries will fail in rewrite phase. + The feature should only be enabled when all the aggregate + functions used in the query (explicitly or implicitly picked from default aggregates of used measures) are + transitive. Transitive aggregate functions are those that follow the following property:\ + \ + <<<f(a, b, c, d) = f(f(a, b), f(c, d)) for all possible values of a,b,c,d.>>>\ + \ + e.g. SUM, MAX, MIN etc are transitive aggregate functions, while AVG, COUNT etc are not. + </description> + </property> + <property> <name>lens.cube.query.enable.flattening.bridge.tables</name> <value>false</value> <description>Flag specifies if fields selected have to be flattened or not, if they are coming from tables with many http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-cube/src/test/java/org/apache/lens/cube/parse/CubeTestSetup.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/CubeTestSetup.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/CubeTestSetup.java index 92f5067..aa15a2c 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/CubeTestSetup.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/CubeTestSetup.java @@ -139,7 +139,7 @@ public class CubeTestSetup { private static String c99 = "C99"; private static Map<String, String> factValidityProperties = Maps.newHashMap(); @Getter - private static Map<String, String> storageToUpdatePeriodMap = new LinkedHashMap<String, String>(); + private static Map<String, List<UpdatePeriod>> storageToUpdatePeriodMap = new LinkedHashMap<>(); static { Calendar cal = Calendar.getInstance(); @@ -213,6 +213,32 @@ public class CubeTestSetup { return MONTH_PARSER.format(dt); } + interface StoragePartitionProvider { + Map<String, String> providePartitionsForStorage(String storage); + } + + public static String getExpectedUnionQuery(String cubeName, List<String> storages, StoragePartitionProvider provider, + String outerSelectPart, String outerWhere, String outerPostWhere, String innerQuerySelectPart, + String innerWhere, String innerPostWhere) { + if (!innerQuerySelectPart.trim().toLowerCase().endsWith("from")) { + innerQuerySelectPart += " from "; + } + StringBuilder sb = new StringBuilder(); + sb.append(outerSelectPart); + if (!outerSelectPart.trim().toLowerCase().endsWith("from")) { + sb.append(" from "); + } + sb.append(" ("); + String sep = ""; + for (String storage : storages) { + sb.append(sep).append(getExpectedQuery(cubeName, innerQuerySelectPart + " ", + innerWhere, innerPostWhere, provider.providePartitionsForStorage(storage))); + sep = " UNION ALL "; + } + return sb.append(") ").append(cubeName).append(" ").append(outerWhere == null ? "" : outerWhere) + .append(" ").append(outerPostWhere == null ? "" : outerPostWhere).toString(); + } + public static String getExpectedQuery(String cubeName, String selExpr, String whereExpr, String postWhereExpr, Map<String, String> storageTableToWhereClause) { return getExpectedQuery(cubeName, selExpr, whereExpr, postWhereExpr, storageTableToWhereClause, null); @@ -241,7 +267,7 @@ public class CubeTestSetup { expected.append(entry.getValue()); expected.append(")"); if (postWhereExpr != null) { - expected.append(postWhereExpr); + expected.append(" ").append(postWhereExpr); } } return expected.toString(); @@ -349,10 +375,11 @@ public class CubeTestSetup { storageTableToWhereClause.put(getStorageTableString(storageTables), whereClause); } else { for (String tbl : storageTables) { - String updatePeriod = storageToUpdatePeriodMap.get(tbl); - String whereClause = getWhereForDailyAndHourly2daysWithTimeDimUnionQuery(cubeName, timedDimension, from, to) - .get(updatePeriod); - storageTableToWhereClause.put(getStorageTableString(tbl), whereClause); + for (UpdatePeriod updatePeriod : storageToUpdatePeriodMap.get(tbl)) { + String whereClause = getWhereForDailyAndHourly2daysWithTimeDimUnionQuery(cubeName, timedDimension, from, to) + .get(updatePeriod.getName()); + storageTableToWhereClause.put(getStorageTableString(tbl), whereClause); + } } } return storageTableToWhereClause; @@ -392,7 +419,7 @@ public class CubeTestSetup { } public static Map<String, String> getWhereForDailyAndHourly2daysWithTimeDimUnionQuery(String cubeName, - String timedDimension, Date from, Date to) { + String timedDimension, Date from, Date to) { Map<String, String> updatePeriodToWhereMap = new HashMap<String, String>(); List<String> hourlyparts = new ArrayList<String>(); List<String> dailyparts = new ArrayList<String>(); @@ -400,7 +427,7 @@ public class CubeTestSetup { if (!CubeTestSetup.isZerothHour()) { addParts(hourlyparts, HOURLY, from, DateUtil.getCeilDate(from, DAILY)); addParts(hourlyparts, HOURLY, DateUtil.getFloorDate(to, DAILY), - DateUtil.getFloorDate(to, HOURLY)); + DateUtil.getFloorDate(to, HOURLY)); dayStart = DateUtil.getCeilDate(from, DAILY); } else { dayStart = from; @@ -475,9 +502,9 @@ public class CubeTestSetup { Date monthStart = TWO_MONTHS_BACK; if (!CubeTestSetup.isZerothHour()) { addParts(hourlyparts, HOURLY, TWO_MONTHS_BACK, - DateUtil.getCeilDate(TWO_MONTHS_BACK, DAILY)); + DateUtil.getCeilDate(TWO_MONTHS_BACK, DAILY)); addParts(hourlyparts, HOURLY, DateUtil.getFloorDate(NOW, DAILY), - DateUtil.getFloorDate(NOW, HOURLY)); + DateUtil.getFloorDate(NOW, HOURLY)); dayStart = DateUtil.getCeilDate(TWO_MONTHS_BACK, DAILY); monthStart = DateUtil.getCeilDate(TWO_MONTHS_BACK, MONTHLY); } @@ -488,7 +515,7 @@ public class CubeTestSetup { monthStart = DateUtil.getCeilDate(TWO_MONTHS_BACK, MONTHLY); } addParts(dailyparts, DAILY, DateUtil.getFloorDate(NOW, MONTHLY), - DateUtil.getFloorDate(NOW, DAILY)); + DateUtil.getFloorDate(NOW, DAILY)); addParts(monthlyparts, MONTHLY, monthStart, DateUtil.getFloorDate(NOW, MONTHLY)); updatePeriodToPart.put("HOURLY", hourlyparts); @@ -496,12 +523,14 @@ public class CubeTestSetup { updatePeriodToPart.put("MONTHLY", monthlyparts); List<String> unionParts = new ArrayList<String>(); - for (Map.Entry<String, String> entry : storageToUpdatePeriodMap.entrySet()) { - String uperiod = entry.getKey(); - String table = entry.getValue(); - if (table.equals(storageTable) && updatePeriodToPart.containsKey(uperiod)) { - unionParts.addAll(updatePeriodToPart.get(uperiod)); - Collections.sort(unionParts); + for (Map.Entry<String, List<UpdatePeriod>> entry : storageToUpdatePeriodMap.entrySet()) { + String table = entry.getKey(); + for (UpdatePeriod updatePeriod : entry.getValue()) { + String uperiod = updatePeriod.getName(); + if (table.equals(storageTable) && updatePeriodToPart.containsKey(uperiod)) { + unionParts.addAll(updatePeriodToPart.get(uperiod)); + Collections.sort(unionParts); + } } } @@ -591,6 +620,10 @@ public class CubeTestSetup { cubeMeasures.add(new ColumnMeasure(new FieldSchema("msr1", "int", "first measure"))); cubeMeasures.add(new ColumnMeasure(new FieldSchema("msr2", "float", "second measure"), "Measure2", null, "SUM", "RS")); + cubeMeasures.add(new ColumnMeasure(new FieldSchema("msr21", "float", "second measure"), "Measure22", null, "SUM", + "RS")); + cubeMeasures.add(new ColumnMeasure(new FieldSchema("msr22", "float", "second measure"), "Measure22", null, "SUM", + "RS")); cubeMeasures.add(new ColumnMeasure(new FieldSchema("msr3", "double", "third measure"), "Measure3", null, "MAX", null)); cubeMeasures.add(new ColumnMeasure(new FieldSchema("msr4", "bigint", "fourth measure"), "Measure4", null, "COUNT", @@ -600,7 +633,7 @@ public class CubeTestSetup { cubeMeasures.add(new ColumnMeasure(new FieldSchema("newmeasure", "bigint", "measure available from now"), "New measure", null, null, null, NOW, null, 100.0)); cubeMeasures.add(new ColumnMeasure(new FieldSchema("msr15", "int", "first measure"), "Measure15", null, "SUM", - "RS")); + "RS")); cubeDimensions = new HashSet<CubeDimAttribute>(); cubeDimensions.add(new BaseDimAttribute(new FieldSchema("d_time", "timestamp", "d time"))); @@ -689,6 +722,10 @@ public class CubeTestSetup { "(1000 + sum(msr1) + sum(msr2))/100")); exprs.add(new ExprColumn(new FieldSchema("msr5", "double", "materialized in some facts"), "Fifth Msr", "msr2 + msr3")); + exprs.add(new ExprColumn(new FieldSchema("msr8", "double", "measure expression"), "Sixth Msr", + "msr2 + msr3")); + exprs.add(new ExprColumn(new FieldSchema("msr7", "double", "measure expression"), "Seventh Msr", + "case when sum(msr2) = 0 then 0 else sum(case when cityid='x' then msr21 else msr22 end)/sum(msr2) end")); exprs.add(new ExprColumn(new FieldSchema("equalsums", "double", "sums are equals"), "equalsums", new ExprSpec("msr3 + msr4", null, null), new ExprSpec("(msr3 + msr2)/100", null, null))); exprs.add(new ExprColumn(new FieldSchema("roundedmsr1", "double", "rounded measure1"), "Rounded msr1", @@ -708,7 +745,7 @@ public class CubeTestSetup { exprs.add(new ExprColumn(new FieldSchema("msr6", "bigint", "sixth measure"), "Measure6", "sum(msr2) + max(msr3)/ count(msr4)")); exprs.add(new ExprColumn(new FieldSchema("booleancut", "boolean", "a boolean expression"), "Boolean cut", - "dim1 != 'x' AND dim2 != 10 ")); + "(dim1 != 'x' AND dim2 != 10)")); exprs.add(new ExprColumn(new FieldSchema("substrexpr", "string", "a sub-string expression"), "Substr expr", new ExprSpec("substr(dim1, 3))", null, null), new ExprSpec("substr(ascii(testdim2.name), 3)", null, null))); exprs.add(new ExprColumn(new FieldSchema("substrexprdim2", "string", "a sub-string expression"), "Substr expr", @@ -1146,7 +1183,7 @@ public class CubeTestSetup { } - private void createCubeContinuousFact(CubeMetastoreClient client) throws Exception{ + private void createCubeContinuousFact(CubeMetastoreClient client) throws Exception { // create continuous raw fact only with extra measures String factName = "testFact_CONTINUOUS"; List<FieldSchema> factColumns = new ArrayList<FieldSchema>(); @@ -1253,16 +1290,16 @@ public class CubeTestSetup { CubeFactTable fact = client.getFactTable(factName); Table table = client.getTable(MetastoreUtil.getStorageTableName(fact.getName(), Storage.getPrefix(c1))); assertEquals(table.getParameters().get(MetastoreUtil.getPartitionTimelineCachePresenceKey()), "true"); - for (UpdatePeriod period: Lists.newArrayList(MINUTELY, MINUTELY, DAILY, MONTHLY, YEARLY, QUARTERLY)) { - for (String partCol: Lists.newArrayList("dt")) { + for (UpdatePeriod period : Lists.newArrayList(MINUTELY, MINUTELY, DAILY, MONTHLY, YEARLY, QUARTERLY)) { + for (String partCol : Lists.newArrayList("dt")) { assertTimeline(client, fact.getName(), c1, period, partCol, EndsAndHolesPartitionTimeline.class); } } table = client.getTable(MetastoreUtil.getStorageTableName(fact.getName(), Storage.getPrefix(c4))); assertEquals(table.getParameters().get(MetastoreUtil.getPartitionTimelineCachePresenceKey()), "true"); - for (UpdatePeriod period: Lists.newArrayList(MINUTELY, MINUTELY, DAILY, MONTHLY, YEARLY, QUARTERLY)) { - for (String partCol: Lists.newArrayList("ttd", "ttd2")) { + for (UpdatePeriod period : Lists.newArrayList(MINUTELY, MINUTELY, DAILY, MONTHLY, YEARLY, QUARTERLY)) { + for (String partCol : Lists.newArrayList("ttd", "ttd2")) { assertTimeline(client, fact.getName(), c4, period, partCol, EndsAndHolesPartitionTimeline.class); } } @@ -2266,7 +2303,7 @@ public class CubeTestSetup { }); } }); - Dimension userDim = new Dimension(dimName, dimAttrs, null, joinChains, dimProps, 0L); + Dimension userDim = new Dimension(dimName, dimAttrs, null, joinChains, dimProps, 0L); client.createDimension(userDim); String dimTblName = "usertable"; @@ -2377,6 +2414,7 @@ public class CubeTestSetup { client.createCubeDimensionTable(dimName, dimTblName, dimColumns, 0L, dumpPeriods, dimProps, storageTables); } + public void createSources(HiveConf conf, String dbName) throws Exception { try { Database database = new Database(); http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-cube/src/test/java/org/apache/lens/cube/parse/TestAggregateResolver.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestAggregateResolver.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestAggregateResolver.java index 8da5263..753ca33 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestAggregateResolver.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestAggregateResolver.java @@ -115,7 +115,7 @@ public class TestAggregateResolver extends TestQueryRewrite { getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); String expectedq7 = getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2) from ", null, - "group by testcube.cityid having" + " sum(testCube.msr2) > 100) OR (sum(testCube.msr2) < 100 AND" + "group by testcube.cityid having" + " sum(testCube.msr2) > 100 OR (sum(testCube.msr2) < 100 AND" + " max(testcube.msr3) > 1000)", getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); String expectedq8 = getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2) * max(testCube.msr3) from ", null, @@ -139,7 +139,7 @@ public class TestAggregateResolver extends TestQueryRewrite { for (int i = 0; i < tests.length; i++) { String hql = rewrite(tests[i], conf); System.out.println("hql[" + i + "]:" + hql); - compareQueries(expected[i], hql); + compareQueries(hql, expected[i]); } aggregateFactSelectionTests(conf); rawFactSelectionTests(getConfWithStorages("C1,C2")); @@ -156,7 +156,7 @@ public class TestAggregateResolver extends TestQueryRewrite { String expectedQL1 = getExpectedQuery(cubeName, "SELECT distinct testcube.cityid, testcube.zipcode, testcube.stateid" + " from ", null, null, getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); - compareQueries(expectedQL1, hQL1); + compareQueries(hQL1, expectedQL1); //Don't add distinct String query2 = "SELECT count (distinct testcube.cityid) from testcube where " + TWO_DAYS_RANGE; @@ -164,7 +164,7 @@ public class TestAggregateResolver extends TestQueryRewrite { String expectedQL2 = getExpectedQuery(cubeName, "SELECT count (distinct testcube.cityid)" + " from ", null, null, getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); - compareQueries(expectedQL2, hQL2); + compareQueries(hQL2, expectedQL2); //Don't add distinct String query3 = "SELECT testcube.cityid, count(distinct testcube.stateid) from testcube where " + TWO_DAYS_RANGE; @@ -172,7 +172,7 @@ public class TestAggregateResolver extends TestQueryRewrite { String expectedQL3 = getExpectedQuery(cubeName, "SELECT testcube.cityid, count(distinct testcube.stateid)" + " from ", null, "group by testcube.cityid", getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); - compareQueries(expectedQL3, hQL3); + compareQueries(hQL3, expectedQL3); //Don't add distinct String query4 = "SELECT count(testcube.stateid) from testcube where " + TWO_DAYS_RANGE; @@ -180,7 +180,7 @@ public class TestAggregateResolver extends TestQueryRewrite { String expectedQL4 = getExpectedQuery(cubeName, "SELECT count(testcube.stateid)" + " from ", null, null, getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); - compareQueries(expectedQL4, hQL4); + compareQueries(hQL4, expectedQL4); //Don't add distinct, by setting the flag false conf.setBoolean(CubeQueryConfUtil.ENABLE_ATTRFIELDS_ADD_DISTINCT, false); @@ -189,7 +189,7 @@ public class TestAggregateResolver extends TestQueryRewrite { String expectedQL5 = getExpectedQuery(cubeName, "SELECT testcube.stateid" + " from ", null, null, getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); - compareQueries(expectedQL5, hQL5); + compareQueries(hQL5, expectedQL5); } @@ -210,7 +210,7 @@ public class TestAggregateResolver extends TestQueryRewrite { String expectedQL = getExpectedQuery(cubeName, "SELECT testcube.cityid," + " testCube.msr2 from ", null, null, getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); conf2.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C2"); aggregateFactSelectionTests(conf2); conf2.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1,C2"); @@ -222,16 +222,16 @@ public class TestAggregateResolver extends TestQueryRewrite { CubeQueryContext cubeql = rewriteCtx(query, conf); String hQL = cubeql.toHQL(); String expectedQL = - getExpectedQuery(cubeName, "SELECT count(distinct testcube.cityid)," + " from ", null, null, + getExpectedQuery(cubeName, "SELECT count(distinct testcube.cityid) from ", null, null, getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); query = "SELECT distinct cityid from testcube where " + TWO_DAYS_RANGE; hQL = rewrite(query, conf); expectedQL = - getExpectedQuery(cubeName, "SELECT distinct testcube.cityid," + " from ", null, null, + getExpectedQuery(cubeName, "SELECT distinct testcube.cityid from ", null, null, getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); // with aggregate resolver on/off, msr with its default aggregate around it // should pick up aggregated fact @@ -241,7 +241,7 @@ public class TestAggregateResolver extends TestQueryRewrite { expectedQL = getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2) from ", null, "group by testcube.cityid", getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); query = "SELECT cityid, sum(testCube.msr2) m2 FROM testCube WHERE " + TWO_DAYS_RANGE + " order by m2"; cubeql = rewriteCtx(query, conf); @@ -249,7 +249,7 @@ public class TestAggregateResolver extends TestQueryRewrite { expectedQL = getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2) as `m2` from ", null, "group by testcube.cityid order by m2 asc", getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); query = "SELECT cityid, sum(testCube.msr2) FROM testCube WHERE " + TWO_DAYS_RANGE + " having max(msr3) > 100"; cubeql = rewriteCtx(query, conf); @@ -258,7 +258,7 @@ public class TestAggregateResolver extends TestQueryRewrite { getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2) from ", null, "group by testcube.cityid having max(testcube.msr3) > 100", getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); } private void rawFactSelectionTests(Configuration conf) throws ParseException, LensException { @@ -270,9 +270,9 @@ public class TestAggregateResolver extends TestQueryRewrite { CandidateFact candidateFact = cubeql.getCandidateFacts().iterator().next(); Assert.assertEquals("testFact2_raw".toLowerCase(), candidateFact.fact.getName().toLowerCase()); String expectedQL = - getExpectedQuery(cubeName, "SELECT testcube.cityid," + " avg(testCube.msr2)) from ", null, + getExpectedQuery(cubeName, "SELECT testcube.cityid," + " avg(testCube.msr2) from ", null, "group by testcube.cityid", getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); // query with measure in a where clause query = "SELECT cityid, sum(testCube.msr2) FROM testCube WHERE testCube.msr1 < 100 and " + TWO_DAYS_RANGE; @@ -282,9 +282,9 @@ public class TestAggregateResolver extends TestQueryRewrite { Assert.assertEquals("testFact2_raw".toLowerCase(), candidateFact.fact.getName().toLowerCase()); hQL = cubeql.toHQL(); expectedQL = - getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2)) from ", "testcube.msr1 < 100", + getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2) from ", "testcube.msr1 < 100", "group by testcube.cityid", getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); query = "SELECT cityid, testCube.msr2 FROM testCube WHERE testCube.msr2 < 100 and " + TWO_DAYS_RANGE; cubeql = rewriteCtx(query, conf); @@ -295,7 +295,7 @@ public class TestAggregateResolver extends TestQueryRewrite { expectedQL = getExpectedQuery(cubeName, "SELECT testcube.cityid," + " testCube.msr2 from ", "testcube.msr2 < 100", null, getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); query = "SELECT cityid, sum(testCube.msr2) FROM testCube WHERE " + TWO_DAYS_RANGE + " group by testCube.msr1"; cubeql = rewriteCtx(query, conf); @@ -304,9 +304,9 @@ public class TestAggregateResolver extends TestQueryRewrite { Assert.assertEquals("testFact2_raw".toLowerCase(), candidateFact.fact.getName().toLowerCase()); hQL = cubeql.toHQL(); expectedQL = - getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2)) from ", null, + getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2) from ", null, " group by testCube.msr1, testcube.cityid", getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); query = "SELECT cityid, sum(testCube.msr2) FROM testCube WHERE " + TWO_DAYS_RANGE + " group by testCube.msr3"; cubeql = rewriteCtx(query, conf); @@ -315,9 +315,9 @@ public class TestAggregateResolver extends TestQueryRewrite { Assert.assertEquals("testFact2_raw".toLowerCase(), candidateFact.fact.getName().toLowerCase()); hQL = cubeql.toHQL(); expectedQL = - getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2)) from ", null, + getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2) from ", null, " group by testCube.msr3, testcube.cityid", getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); query = "SELECT cityid, sum(testCube.msr2) FROM testCube WHERE " + TWO_DAYS_RANGE + " order by testCube.msr1"; cubeql = rewriteCtx(query, conf); @@ -326,9 +326,9 @@ public class TestAggregateResolver extends TestQueryRewrite { Assert.assertEquals("testFact2_raw".toLowerCase(), candidateFact.fact.getName().toLowerCase()); hQL = cubeql.toHQL(); expectedQL = - getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2)) from ", null, + getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2) from ", null, " group by testcube.cityid order by testcube.msr1 asc", getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); query = "SELECT cityid, sum(testCube.msr2) FROM testCube WHERE " + TWO_DAYS_RANGE + " order by testCube.msr3"; cubeql = rewriteCtx(query, conf); @@ -337,9 +337,9 @@ public class TestAggregateResolver extends TestQueryRewrite { Assert.assertEquals("testFact2_raw".toLowerCase(), candidateFact.fact.getName().toLowerCase()); hQL = cubeql.toHQL(); expectedQL = - getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2)) from ", null, + getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2) from ", null, " group by testcube.cityid order by testcube.msr3 asc", getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); query = "SELECT distinct cityid, round(testCube.msr2) from testCube where " + TWO_DAYS_RANGE; cubeql = rewriteCtx(query, conf); @@ -350,7 +350,7 @@ public class TestAggregateResolver extends TestQueryRewrite { expectedQL = getExpectedQuery(cubeName, "SELECT distinct testcube.cityid," + " round(testCube.msr2) from ", null, null, getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); query = "SELECT cityid, count(distinct(testCube.msr2)) from testCube where " + TWO_DAYS_RANGE; cubeql = rewriteCtx(query, conf); @@ -361,7 +361,7 @@ public class TestAggregateResolver extends TestQueryRewrite { expectedQL = getExpectedQuery(cubeName, "SELECT testcube.cityid, count(distinct testCube.msr2) from ", null, "group by testcube.cityid", getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); // query with no default aggregate measure query = "SELECT cityid, round(testCube.msr1) from testCube where " + TWO_DAYS_RANGE; @@ -373,7 +373,7 @@ public class TestAggregateResolver extends TestQueryRewrite { expectedQL = getExpectedQuery(cubeName, "SELECT testcube.cityid," + " round(testCube.msr1) from ", null, null, getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); query = "SELECT distinct cityid, round(testCube.msr1) from testCube where " + TWO_DAYS_RANGE; cubeql = rewriteCtx(query, conf); @@ -384,7 +384,7 @@ public class TestAggregateResolver extends TestQueryRewrite { expectedQL = getExpectedQuery(cubeName, "SELECT distinct testcube.cityid," + " round(testCube.msr1) from ", null, null, getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); query = "SELECT cityid, count(distinct(testCube.msr1)) from testCube where " + TWO_DAYS_RANGE; cubeql = rewriteCtx(query, conf); @@ -395,7 +395,7 @@ public class TestAggregateResolver extends TestQueryRewrite { expectedQL = getExpectedQuery(cubeName, "SELECT testcube.cityid, count(distinct testCube.msr1) from ", null, "group by testcube.cityid", getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); query = "SELECT cityid, sum(testCube.msr1) from testCube where " + TWO_DAYS_RANGE; cubeql = rewriteCtx(query, conf); @@ -404,15 +404,15 @@ public class TestAggregateResolver extends TestQueryRewrite { Assert.assertEquals("testFact2_raw".toLowerCase(), candidateFact.fact.getName().toLowerCase()); hQL = cubeql.toHQL(); expectedQL = - getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr1)) from ", null, + getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr1) from ", null, "group by testcube.cityid", getWhereForHourly2days("c1_testfact2_raw")); - + compareQueries(hQL, expectedQL); query = "SELECT cityid, sum(testCube.msr2) FROM testCube WHERE " + TWO_DAYS_RANGE + " having max(msr1) > 100"; cubeql = rewriteCtx(query, conf); hQL = cubeql.toHQL(); expectedQL = getExpectedQuery(cubeName, "SELECT testcube.cityid," + " sum(testCube.msr2) from ", null, "group by testcube.cityid having max(testcube.msr1) > 100", getWhereForHourly2days("c1_testfact2_raw")); - compareQueries(expectedQL, hQL); + compareQueries(hQL, expectedQL); } } http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-cube/src/test/java/org/apache/lens/cube/parse/TestCubeRewriter.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestCubeRewriter.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestCubeRewriter.java index 4acd063..494f81b 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestCubeRewriter.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestCubeRewriter.java @@ -19,10 +19,12 @@ package org.apache.lens.cube.parse; -import static org.apache.lens.cube.metadata.UpdatePeriod.DAILY; -import static org.apache.lens.cube.metadata.UpdatePeriod.HOURLY; +import static org.apache.lens.cube.metadata.UpdatePeriod.*; import static org.apache.lens.cube.parse.CandidateTablePruneCause.CandidateTablePruneCode.*; +import static org.apache.lens.cube.parse.CubeQueryConfUtil.getValidStorageTablesKey; +import static org.apache.lens.cube.parse.CubeQueryConfUtil.getValidUpdatePeriodsKey; import static org.apache.lens.cube.parse.CubeTestSetup.*; +import static org.apache.lens.cube.parse.CubeTestSetup.getWhereForMonthlyDailyAndHourly2monthsUnionQuery; import static org.testng.Assert.*; @@ -37,7 +39,6 @@ import org.apache.lens.cube.parse.CandidateTablePruneCause.SkipStorageCode; import org.apache.lens.server.api.error.LensException; import org.apache.commons.lang.time.DateUtils; - import org.apache.hadoop.conf.Configuration; import org.apache.hadoop.hive.conf.HiveConf; import org.apache.hadoop.hive.metastore.api.FieldSchema; @@ -51,8 +52,8 @@ import org.testng.annotations.Test; import com.google.common.base.Splitter; import com.google.common.collect.Iterables; +import com.google.common.collect.Lists; import com.google.common.collect.Sets; - import lombok.extern.slf4j.Slf4j; @Slf4j @@ -361,7 +362,7 @@ public class TestCubeRewriter extends TestQueryRewrite { conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1"); conf.set(CubeQueryConfUtil.getValidFactTablesKey(cubeName), "testFact2"); - conf.set(CubeQueryConfUtil.getValidStorageTablesKey("testFact2"), "C1_testFact2"); + conf.set(getValidStorageTablesKey("testFact2"), "C1_testFact2"); hqlQuery = rewrite("select SUM(msr2) from testCube" + " where " + TWO_DAYS_RANGE, conf); expected = getExpectedQuery(cubeName, "select sum(testcube.msr2) FROM ", null, null, @@ -369,16 +370,16 @@ public class TestCubeRewriter extends TestQueryRewrite { compareQueries(hqlQuery, expected); conf.set(CubeQueryConfUtil.getValidFactTablesKey(cubeName), "testFact"); - conf.set(CubeQueryConfUtil.getValidStorageTablesKey("testfact"), "C1_testFact"); - conf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact", "C1"), "HOURLY"); + conf.set(getValidStorageTablesKey("testfact"), "C1_testFact"); + conf.set(getValidUpdatePeriodsKey("testfact", "C1"), "HOURLY"); hqlQuery = rewrite("select SUM(msr2) from testCube" + " where " + TWO_DAYS_RANGE, conf); expected = getExpectedQuery(cubeName, "select sum(testcube.msr2) FROM ", null, null, getWhereForHourly2days("c1_testfact")); compareQueries(hqlQuery, expected); conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C2"); - conf.set(CubeQueryConfUtil.getValidStorageTablesKey("testfact"), "C2_testFact"); - conf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact", "C2"), "HOURLY"); + conf.set(getValidStorageTablesKey("testfact"), "C2_testFact"); + conf.set(getValidUpdatePeriodsKey("testfact", "C2"), "HOURLY"); hqlQuery = rewrite("select SUM(msr2) from testCube" + " where " + TWO_DAYS_RANGE, conf); expected = getExpectedQuery(cubeName, "select sum(testcube.msr2) FROM ", null, null, getWhereForHourly2days("c2_testfact")); @@ -395,33 +396,110 @@ public class TestCubeRewriter extends TestQueryRewrite { } @Test - public void testCubeWhereQueryDuplicatePartitionElimination() throws Exception { + public void testUnionQueries() throws Exception { Configuration conf = getConf(); - conf.set(CubeQueryConfUtil.getValidStorageTablesKey("testfact"), "C1_testFact,C2_testFact"); - conf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact", "C1"), "DAILY,HOURLY"); - conf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact2", "C1"), "YEARLY"); - conf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact", "C2"), "MONTHLY,DAILY"); - + conf.set(getValidStorageTablesKey("testfact"), "C1_testFact,C2_testFact"); + conf.set(getValidUpdatePeriodsKey("testfact", "C1"), "DAILY,HOURLY"); + conf.set(getValidUpdatePeriodsKey("testfact2", "C1"), "YEARLY"); + conf.set(getValidUpdatePeriodsKey("testfact", "C2"), "MONTHLY,DAILY"); + ArrayList<String> storages = Lists.newArrayList("c1_testfact", "c2_testfact"); try { - CubeTestSetup.getStorageToUpdatePeriodMap().put("HOURLY", "c1_testfact"); - CubeTestSetup.getStorageToUpdatePeriodMap().put("DAILY", "c1_testfact"); - CubeTestSetup.getStorageToUpdatePeriodMap().put("MONTHLY", "c2_testfact"); + CubeTestSetup.getStorageToUpdatePeriodMap().put("c1_testfact", Lists.newArrayList(HOURLY, DAILY)); + CubeTestSetup.getStorageToUpdatePeriodMap().put("c2_testfact", Lists.newArrayList(MONTHLY)); // Union query - String hqlQuery = rewrite("select SUM(msr2) from testCube" + " where " + TWO_MONTHS_RANGE_UPTO_HOURS, conf); - System.out.println("HQL: " + hqlQuery); - - String expected1 = getExpectedQuery(cubeName, "select sum(testcube.msr2) FROM ", null, null, - getWhereForMonthlyDailyAndHourly2monthsUnionQuery("c1_testfact")); - String expected2 = getExpectedQuery(cubeName, "select sum(testcube.msr2) FROM ", null, null, - getWhereForMonthlyDailyAndHourly2monthsUnionQuery("c2_testfact")); - - System.out.println("Expected1 : " + expected1); - System.out.println("Expected2 : " + expected2); - - TestCubeRewriter.compareContains(expected1, hqlQuery); - TestCubeRewriter.compareContains(expected2, hqlQuery); - TestCubeRewriter.compareContains("UNION ALL", hqlQuery); + String hqlQuery; + String expected; + StoragePartitionProvider provider = new StoragePartitionProvider() { + @Override + public Map<String, String> providePartitionsForStorage(String storage) { + return getWhereForMonthlyDailyAndHourly2monthsUnionQuery(storage); + } + }; + try{ + rewrite("select cityid as `City ID`, msr8, msr7 as `Third measure` " + + "from testCube where " + TWO_MONTHS_RANGE_UPTO_HOURS, conf); + fail("Union feature is disabled, should have failed"); + } catch (LensException e) { + assertEquals(e.getErrorCode(), LensCubeErrorCode.STORAGE_UNION_DISABLED.getLensErrorInfo().getErrorCode()); + } + conf.setBoolean(CubeQueryConfUtil.ENABLE_STORAGES_UNION, true); + + hqlQuery = rewrite("select cityid as `City ID`, msr8, msr7 as `Third measure` " + + "from testCube where " + TWO_MONTHS_RANGE_UPTO_HOURS, conf); + + expected = getExpectedUnionQuery(cubeName, storages, provider, + "SELECT testcube.alias0 as `City ID`, sum(testcube.alias1) + max(testcube.alias2), " + + "case when sum(testcube.alias1) = 0 then 0 else sum(testcube.alias3)/sum(testcube.alias1) end " + + "as `Third Measure`", + null, "group by testcube.alias0", + "select testcube.cityid as `alias0`, sum(testcube.msr2) as `alias1`, " + + "max(testcube.msr3) as `alias2`, " + + "sum(case when testcube.cityid = 'x' then testcube.msr21 else testcube.msr22 end) as `alias3`", + null, "group by testcube.cityid"); + + compareQueries(hqlQuery, expected); + + hqlQuery = rewrite("select cityid as `City ID`, msr3 as `Third measure` from testCube where " + + TWO_MONTHS_RANGE_UPTO_HOURS + " having msr7 > 10", conf); + + expected = getExpectedUnionQuery(cubeName, storages, provider, + "SELECT testcube.alias0 as `City ID`, max(testcube.alias1) as `Third measure`", + null, "group by testcube.alias0 having " + + "(case when sum(testcube.alias2)=0 then 0 else sum(testcube.alias3)/sum(testcube.alias2) end > 10 )", + "SELECT testcube.cityid as `alias0`, max(testcube.msr3) as `alias1`, " + + "sum(testcube.msr2) as `alias2`, " + + "sum(case when testcube.cityid='x' then testcube.msr21 else testcube.msr22 end) as `alias3`", + null, "group by testcube.cityid"); + compareQueries(hqlQuery, expected); + + hqlQuery = rewrite("select cityid as `City ID`, msr3 as `Third measure` from testCube where " + + TWO_MONTHS_RANGE_UPTO_HOURS + " having msr8 > 10", conf); + + expected = getExpectedUnionQuery(cubeName, storages, provider, + "SELECT testcube.alias0 as `City ID`, max(testcube.alias1) as `Third measure`", + null, "GROUP BY testcube.alias0 " + + "HAVING (sum(testcube.alias2) + max(testcube.alias1)) > 10 ", + "SELECT testcube.cityid as `alias0`, max(testcube.msr3) as `alias1`, " + + "sum(testcube.msr2)as `alias2`", null, "group by testcube.cityid"); + compareQueries(hqlQuery, expected); + + hqlQuery = rewrite("select msr3 as `Measure 3` from testCube where " + + TWO_MONTHS_RANGE_UPTO_HOURS + " having msr2 > 10 and msr2 < 100", conf); + + expected = getExpectedUnionQuery(cubeName, storages, provider, + "SELECT max(testcube.alias0) as `Measure 3` ", + null, " HAVING sum(testcube.alias1) > 10 and sum(testcube.alias1) < 100", + "SELECT max(testcube.msr3) as `alias0`, sum(testcube.msr2) as `alias1`", null, null); + compareQueries(hqlQuery, expected); + + hqlQuery = rewrite("select zipcode, cityid as `City ID`, msr3 as `Measure 3`, msr4, " + + "SUM(msr2) as `Measure 2` from testCube where " + + TWO_MONTHS_RANGE_UPTO_HOURS + " having msr4 > 10 order by cityid desc limit 5", conf); + + expected = getExpectedUnionQuery(cubeName, storages, provider, + "SELECT testcube.alias0, testcube.alias1 as `City ID`, max(testcube.alias2) as `Measure 3`, " + + "count(testcube.alias3), sum(testcube.alias4) as `Measure 2`", + null, "group by testcube.alias0, testcube.alias1 " + + " having count(testcube.alias3) > 10 order by testcube.alias1 desc limit 5", + "select testcube.zipcode as `alias0`, testcube.cityid as `alias1`, " + + "max(testcube.msr3) as `alias2`,count(testcube.msr4) as `alias3`, sum(testcube.msr2) as `alias4`", + null, "group by testcube.zipcode, testcube.cityid "); + compareQueries(hqlQuery, expected); + + conf.setBoolean(CubeQueryConfUtil.ENABLE_GROUP_BY_TO_SELECT, false); + conf.setBoolean(CubeQueryConfUtil.ENABLE_SELECT_TO_GROUPBY, false); + hqlQuery = rewrite("select cityid as `City ID`, msr3 as `Measure 3`, " + + "SUM(msr2) as `Measure 2` from testCube" + " where " + + TWO_MONTHS_RANGE_UPTO_HOURS + " group by zipcode having msr4 > 10 order by cityid desc limit 5", conf); + + expected = getExpectedUnionQuery(cubeName, storages, provider, + "SELECT testcube.alias0 as `City ID`,max(testcube.alias1) as `Measure 3`,sum(testcube.alias2) as `Measure 2` ", + null, "group by testcube.alias3 having count(testcube.alias4) > 10 order by testcube.alias0 desc limit 5", + "SELECT testcube.cityid as `alias0`, max(testcube.msr3) as `alias1`, " + + "sum(testcube.msr2) as `alias2`, testcube.zipcode as `alias3`, count(testcube .msr4) as `alias4` FROM ", + null, "GROUP BY testcube.zipcode"); + compareQueries(hqlQuery, expected); } finally { CubeTestSetup.getStorageToUpdatePeriodMap().clear(); } @@ -431,30 +509,30 @@ public class TestCubeRewriter extends TestQueryRewrite { @Test public void testCubeWhereQueryWithMultipleTables() throws Exception { Configuration conf = getConf(); - conf.set(CubeQueryConfUtil.getValidStorageTablesKey("testfact"), "C1_testFact,C2_testFact"); - conf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact", "C1"), "DAILY"); - conf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact2", "C1"), "YEARLY"); - conf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact", "C2"), "HOURLY"); - - CubeTestSetup.getStorageToUpdatePeriodMap().put("c1_testfact", "DAILY"); - CubeTestSetup.getStorageToUpdatePeriodMap().put("c2_testfact", "HOURLY"); - + conf.setBoolean(CubeQueryConfUtil.ENABLE_STORAGES_UNION, true); + conf.set(getValidStorageTablesKey("testfact"), "C1_testFact,C2_testFact"); + conf.set(getValidUpdatePeriodsKey("testfact", "C1"), "DAILY"); + conf.set(getValidUpdatePeriodsKey("testfact2", "C1"), "YEARLY"); + conf.set(getValidUpdatePeriodsKey("testfact", "C2"), "HOURLY"); + + CubeTestSetup.getStorageToUpdatePeriodMap().put("c1_testfact", Lists.newArrayList(DAILY)); + CubeTestSetup.getStorageToUpdatePeriodMap().put("c2_testfact", Lists.newArrayList(HOURLY)); + StoragePartitionProvider provider = new StoragePartitionProvider() { + @Override + public Map<String, String> providePartitionsForStorage(String storage) { + return getWhereForDailyAndHourly2days(cubeName, storage); + } + }; try { // Union query String hqlQuery = rewrite("select SUM(msr2) from testCube" + " where " + TWO_DAYS_RANGE, conf); System.out.println("HQL:" + hqlQuery); - String expected1 = getExpectedQuery(cubeName, "select sum(testcube.msr2) FROM ", null, null, - getWhereForDailyAndHourly2days(cubeName, "c1_testfact")); - String expected2 = getExpectedQuery(cubeName, "select sum(testcube.msr2) FROM ", null, null, - getWhereForDailyAndHourly2days(cubeName, "c2_testfact")); - - System.out.println("Expected1 : " + expected1); - System.out.println("Expected2 : " + expected2); - - TestCubeRewriter.compareContains(expected1, hqlQuery); - TestCubeRewriter.compareContains(expected2, hqlQuery); - TestCubeRewriter.compareContains("UNION ALL", hqlQuery); + String expected = getExpectedUnionQuery(cubeName, Lists.newArrayList("c1_testfact", "c2_testfact"), provider, + "select sum(testcube.alias0) ", null, null, + "select sum(testcube.msr2) as `alias0` from ", null, null + ); + compareQueries(hqlQuery, expected); } finally { CubeTestSetup.getStorageToUpdatePeriodMap().clear(); } @@ -463,38 +541,34 @@ public class TestCubeRewriter extends TestQueryRewrite { @Test public void testCubeWhereQueryWithMultipleTablesForMonth() throws Exception { Configuration conf = getConf(); - conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C0,C1,C2,C3,C4,C5"); - conf.set(CubeQueryConfUtil.getValidStorageTablesKey("testfact"), ""); - conf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact", "C1"), "HOURLY"); - conf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact2", "C1"), "YEARLY"); - conf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact2_raw", "C3"), "YEARLY"); - conf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact", "C2"), "DAILY"); - conf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact", "C3"), "MONTHLY"); - - CubeTestSetup.getStorageToUpdatePeriodMap().put("HOURLY", "c1_testfact"); - CubeTestSetup.getStorageToUpdatePeriodMap().put("DAILY", "c2_testfact"); - CubeTestSetup.getStorageToUpdatePeriodMap().put("MONTHLY", "c3_testfact"); - + conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1,C2,C3"); + conf.setBoolean(CubeQueryConfUtil.ENABLE_STORAGES_UNION, true); + conf.set(getValidStorageTablesKey("testfact"), ""); + conf.set(getValidUpdatePeriodsKey("testfact", "C1"), "HOURLY"); + conf.set(getValidUpdatePeriodsKey("testfact2", "C1"), "YEARLY"); + conf.set(getValidUpdatePeriodsKey("testfact2_raw", "C3"), "YEARLY"); + conf.set(getValidUpdatePeriodsKey("testfact", "C2"), "DAILY"); + conf.set(getValidUpdatePeriodsKey("testfact", "C3"), "MONTHLY"); + + CubeTestSetup.getStorageToUpdatePeriodMap().put("c1_testfact", Lists.newArrayList(HOURLY)); + CubeTestSetup.getStorageToUpdatePeriodMap().put("c2_testfact", Lists.newArrayList(DAILY)); + CubeTestSetup.getStorageToUpdatePeriodMap().put("c3_testfact", Lists.newArrayList(MONTHLY)); + StoragePartitionProvider provider = new StoragePartitionProvider() { + @Override + public Map<String, String> providePartitionsForStorage(String storage) { + return getWhereForMonthlyDailyAndHourly2monthsUnionQuery(storage); + } + }; try { // Union query String hqlQuery = rewrite("select SUM(msr2) from testCube" + " where " + TWO_MONTHS_RANGE_UPTO_HOURS, conf); System.out.println("HQL:" + hqlQuery); - - String expected1 = getExpectedQuery(cubeName, "select sum(testcube.msr2) FROM ", null, null, - getWhereForMonthlyDailyAndHourly2monthsUnionQuery("c1_testfact")); - String expected2 = getExpectedQuery(cubeName, "select sum(testcube.msr2) FROM ", null, null, - getWhereForMonthlyDailyAndHourly2monthsUnionQuery("c2_testfact")); - String expected3 = getExpectedQuery(cubeName, "select sum(testcube.msr2) FROM ", null, null, - getWhereForMonthlyDailyAndHourly2monthsUnionQuery("c3_testfact")); - - System.out.println("Expected1 : " + expected1); - System.out.println("Expected2 : " + expected2); - System.out.println("Expected3 : " + expected3); - - TestCubeRewriter.compareContains(expected1, hqlQuery); - TestCubeRewriter.compareContains(expected2, hqlQuery); - TestCubeRewriter.compareContains(expected3, hqlQuery); - TestCubeRewriter.compareContains("UNION ALL", hqlQuery); + ArrayList<String> storages = Lists.newArrayList("c1_testfact", "c3_testfact", "c2_testfact"); + String expected = getExpectedUnionQuery(cubeName, storages, provider, + "select sum(testcube.alias0)", null, null, + "select sum(testcube.msr2) as `alias0` from ", null, null + ); + compareQueries(hqlQuery, expected); } finally { CubeTestSetup.getStorageToUpdatePeriodMap().clear(); } @@ -679,19 +753,19 @@ public class TestCubeRewriter extends TestQueryRewrite { hqlQuery = rewrite("select SUM(msr2) from testCube" + " join citydim on testCube.cityid = citydim.id" + " where " + TWO_DAYS_RANGE + " group by name", conf); - compareQueries(expected, hqlQuery); + compareQueries(hqlQuery, expected); hqlQuery = rewrite("select cityid, SUM(msr2) from testCube" + " where " + TWO_DAYS_RANGE, conf); expected = getExpectedQuery(cubeName, "select testcube.cityid," + " sum(testcube.msr2) FROM ", null, " group by testcube.cityid ", getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); - compareQueries(expected, hqlQuery); + compareQueries(hqlQuery, expected); hqlQuery = rewrite("select round(cityid), SUM(msr2) from" + " testCube where " + TWO_DAYS_RANGE, conf); expected = getExpectedQuery(cubeName, "select round(testcube.cityid)," + " sum(testcube.msr2) FROM ", null, " group by round(testcube.cityid) ", getWhereForDailyAndHourly2days(cubeName, "C2_testfact")); - compareQueries(expected, hqlQuery); + compareQueries(hqlQuery, expected); hqlQuery = rewrite("select SUM(msr2) from testCube" + " where " + TWO_DAYS_RANGE + "group by round(zipcode)", conf); @@ -1125,7 +1199,7 @@ public class TestCubeRewriter extends TestQueryRewrite { hqlQuery = rewrite("select name n, count(1) from citydim" + " group by name order by n ", conf); expected = getExpectedQuery("citydim", "select citydim.name as `n`," + " count(1) from ", - "groupby citydim.name order by n asc", "c2_citytable", false); + " group by citydim.name order by n asc", "c2_citytable", false); compareQueries(hqlQuery, expected); hqlQuery = rewrite("select name as `n`, count(1) from citydim" + " order by n ", conf); @@ -1133,7 +1207,7 @@ public class TestCubeRewriter extends TestQueryRewrite { hqlQuery = rewrite("select count(1) from citydim" + " group by name order by name ", conf); expected = getExpectedQuery("citydim", "select citydim.name," + " count(1) from ", - "groupby citydim.name order by citydim.name asc ", "c2_citytable", false); + " group by citydim.name order by citydim.name asc ", "c2_citytable", false); compareQueries(hqlQuery, expected); } @@ -1188,11 +1262,11 @@ public class TestCubeRewriter extends TestQueryRewrite { getExpectedQuery("t", "SELECT t.cityid, sum(t.msr2) FROM ", null, " group by t.cityid", getWhereForDailyAndHourly2days("t", "C2_testfact")), getExpectedQuery(cubeName, "SELECT testCube.cityid, sum(testCube.msr2)" + " FROM ", - " testcube.cityid > 100 ", " group by testcube.cityid having" + " sum(testCube.msr2 < 1000)", + " testcube.cityid > 100 ", " group by testcube.cityid having" + " sum(testCube.msr2) < 1000", getWhereForDailyAndHourly2days(cubeName, "C2_testfact")), getExpectedQuery(cubeName, "SELECT testCube.cityid, sum(testCube.msr2)" + " FROM ", " testcube.cityid > 100 ", " group by testcube.cityid having" - + " sum(testCube.msr2 < 1000) orderby testCube.cityid asc", + + " sum(testCube.msr2) < 1000 order by testCube.cityid asc", getWhereForDailyAndHourly2days(cubeName, "C2_testfact")), }; Configuration conf = getConf(); @@ -1435,10 +1509,10 @@ public class TestCubeRewriter extends TestQueryRewrite { String hqlQuery = rewrite(cubeQl, conf); String db = getDbName(); String expectedJoin = - " LEFT OUTER JOIN " + db + ".c1_citytable c1 ON (( testcube . cityid ) = ( c1 . id )) AND (c1.dt = 'latest') " + " LEFT OUTER JOIN " + db + "c1_citytable c1 ON (( testcube . cityid ) = ( c1 . id )) AND (c1.dt = 'latest') " + " LEFT OUTER JOIN " + db - + ".c1_statetable s1 ON (( c1 . stateid ) = ( s1 . id )) AND (s1.dt = 'latest') " + " LEFT OUTER JOIN " - + db + ".c1_citytable c2 ON (( s1 . countryid ) = ( c2 . id )) AND (c2.dt = 'latest')"; + + "c1_statetable s1 ON (( c1 . stateid ) = ( s1 . id )) AND (s1.dt = 'latest') " + " LEFT OUTER JOIN " + + db + "c1_citytable c2 ON (( s1 . countryid ) = ( c2 . id )) AND (c2.dt = 'latest')"; String expected = getExpectedQuery(cubeName, "select sum(testcube.msr2)" + " FROM ", expectedJoin, null, null, null, http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-cube/src/test/java/org/apache/lens/cube/parse/TestDenormalizationResolver.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestDenormalizationResolver.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestDenormalizationResolver.java index 64b1ac6..d16ea4c 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestDenormalizationResolver.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestDenormalizationResolver.java @@ -63,7 +63,7 @@ public class TestDenormalizationResolver extends TestQueryRewrite { getExpectedQuery(cubeName, "select testcube.dim2big1," + " max(testcube.msr3), sum(testcube.msr2) FROM ", null, " group by testcube.dim2big1", getWhereForDailyAndHourly2daysWithTimeDim(cubeName, "it", "C2_summary4"), null); - TestCubeRewriter.compareQueries(expecteddim2big1, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expecteddim2big1); // with another table hqlQuery = rewrite("select dim2big1, citydim.name, max(msr3)," + " msr2 from testCube" + " where " + twoDaysITRange, conf); @@ -73,14 +73,14 @@ public class TestDenormalizationResolver extends TestQueryRewrite { " group by testcube.dim2big1, citydim.name", null, getWhereForDailyAndHourly2daysWithTimeDim(cubeName, "it", "C2_summary4"), null); - TestCubeRewriter.compareQueries(expecteddim2big1WithAnotherTable, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expecteddim2big1WithAnotherTable); hqlQuery = rewrite("select dim2big2, max(msr3)," + " msr2 from testCube" + " where " + twoDaysITRange, conf); String expecteddim2big2 = getExpectedQuery(cubeName, "select testcube.dim2big2, max(testcube.msr3), sum(testcube.msr2) FROM ", null, " group by testcube.dim2big2", getWhereForDailyAndHourly2daysWithTimeDim(cubeName, "it", "C2_summary4"), null); - TestCubeRewriter.compareQueries(expecteddim2big2, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expecteddim2big2); Configuration conf2 = new Configuration(conf); conf2.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C2"); @@ -94,12 +94,12 @@ public class TestDenormalizationResolver extends TestQueryRewrite { " group by testdim3.name, (testcube.dim2big1)", null, getWhereForDailyAndHourly2daysWithTimeDim(cubeName, "it", "C2_summary4"), null); - TestCubeRewriter.compareQueries(expected, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expected); hqlQuery = rewrite("select dim2big1, max(msr3)," + " msr2 from testCube" + " where " + twoDaysITRange, conf2); - TestCubeRewriter.compareQueries(expecteddim2big1, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expecteddim2big1); hqlQuery = rewrite("select dim2big2, max(msr3)," + " msr2 from testCube" + " where " + twoDaysITRange, conf2); - TestCubeRewriter.compareQueries(expecteddim2big2, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expecteddim2big2); } @Test @@ -114,7 +114,7 @@ public class TestDenormalizationResolver extends TestQueryRewrite { + getDbName() + "c1_testdim2tbl2 testdim2 ON testcube.dim2 = " + " testdim2.id and (testdim2.dt = 'latest') ", null, "group by (testdim2.bigid1)", null, getWhereForDailyAndHourly2days(cubeName, "c1_summary2")); - TestCubeRewriter.compareQueries(expected, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expected); hqlQuery = rewrite("select testdim2.name, dim2big1, max(msr3)," + " msr2 from testCube" + " where " + TWO_DAYS_RANGE, tconf); @@ -124,7 +124,7 @@ public class TestDenormalizationResolver extends TestQueryRewrite { + getDbName() + "c1_testdim2tbl2 testdim2 ON testcube.dim2 = " + " testdim2.id and (testdim2.dt = 'latest') ", null, "group by testdim2.name, testdim2.bigid1", null, getWhereForDailyAndHourly2days(cubeName, "c1_summary2")); - TestCubeRewriter.compareQueries(expected, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expected); hqlQuery = rewrite("select testdim2.name, dim2big1, max(msr3)," + " msr2 from testCube left outer join testdim2" @@ -135,7 +135,7 @@ public class TestDenormalizationResolver extends TestQueryRewrite { + getDbName() + "c1_testdim2tbl2 testdim2 ON testcube.dim2 = " + " testdim2.id and (testdim2.dt = 'latest') ", null, "group by testdim2.name, testdim2.bigid1", null, getWhereForDailyAndHourly2days(cubeName, "c1_summary2")); - TestCubeRewriter.compareQueries(expected, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expected); hqlQuery = rewrite("select testdim3.name, dim2big1, max(msr3)," + " msr2 from testCube" + " where " + TWO_DAYS_RANGE, tconf); @@ -147,7 +147,7 @@ public class TestDenormalizationResolver extends TestQueryRewrite { + "c1_testdim3tbl testdim3 on " + "testdim2.testdim3id = testdim3.id AND (testdim3.dt = 'latest')", null, " group by testdim3.name, (testdim2.bigid1)", null, getWhereForDailyAndHourly2days(cubeName, "c1_summary2")); - TestCubeRewriter.compareQueries(expected, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expected); LensException e = getLensExceptionInRewrite( "select dim2big2, max(msr3)," + " msr2 from testCube" + " where " + TWO_DAYS_RANGE, tconf); PruneCauses.BriefAndDetailedError error = extractPruneCause(e); @@ -200,7 +200,7 @@ public class TestDenormalizationResolver extends TestQueryRewrite { null, " group by substr(testcube.dim2big1, 5)", getWhereForDailyAndHourly2daysWithTimeDim(cubeName, "it", "C2_summary4"), null); - TestCubeRewriter.compareQueries(expecteddim2big1, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expecteddim2big1); } @Test @@ -214,7 +214,7 @@ public class TestDenormalizationResolver extends TestQueryRewrite { " JOIN " + getDbName() + "c1_testdim2tbl2 testdim2 ON testcube.dim2 = " + " testdim2.id and (testdim2.dt = 'latest') ", null, "group by substr(testdim2.bigid1, 5)", null, getWhereForDailyAndHourly2days(cubeName, "c1_summary2")); - TestCubeRewriter.compareQueries(expected, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expected); } @Test @@ -225,13 +225,13 @@ public class TestDenormalizationResolver extends TestQueryRewrite { + " citydim.stateid = statedim.id and (statedim.dt = 'latest')"; String expected = getExpectedQuery("citydim", "SELECT citydim.name, statedim.name FROM ", joinExpr, null, null, "c1_citytable", true); - TestCubeRewriter.compareQueries(expected, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expected); hqlQuery = rewrite("select citydim.statename, citydim.name from" + " citydim", conf); expected = getExpectedQuery("citydim", "SELECT statedim.name, citydim.name FROM ", joinExpr, null, null, "c1_citytable", true); - TestCubeRewriter.compareQueries(expected, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expected); // Query would fail because citydim.nocandidatecol does not exist in any // candidate @@ -321,7 +321,7 @@ public class TestDenormalizationResolver extends TestQueryRewrite { String expected = getExpectedQuery("citydim", "SELECT citydim.name, concat(citydim.name, \":\", statedim.name) FROM ", joinExpr, null, null, "c1_citytable", true); - TestCubeRewriter.compareQueries(expected, hqlQuery); + TestCubeRewriter.compareQueries(hqlQuery, expected); } @Test http://git-wip-us.apache.org/repos/asf/lens/blob/c445730c/lens-cube/src/test/java/org/apache/lens/cube/parse/TestExpressionResolver.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestExpressionResolver.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestExpressionResolver.java index 9dcced0..1e21fb0 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestExpressionResolver.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestExpressionResolver.java @@ -417,9 +417,9 @@ public class TestExpressionResolver extends TestQueryRewrite { String joinExpr = "" - + " join " + getDbName() + ".c1_statetable statedim on ct.stateid = statedim.id and (statedim.dt = 'latest')" - + " join " + getDbName() + ".c1_countrytable countrydim on statedim.countryid = countrydim.id" - + " join " + getDbName() + ".c1_ziptable zipdim on ct.zipcode = zipdim.code and (zipdim.dt = 'latest')" + + " join " + getDbName() + "c1_statetable statedim on ct.stateid = statedim.id and (statedim.dt = 'latest')" + + " join " + getDbName() + "c1_countrytable countrydim on statedim.countryid = countrydim.id" + + " join " + getDbName() + "c1_ziptable zipdim on ct.zipcode = zipdim.code and (zipdim.dt = 'latest')" + ""; String expected = @@ -443,9 +443,9 @@ public class TestExpressionResolver extends TestQueryRewrite { String joinExpr = "" - + " join " + getDbName() + ".c1_statetable statedim on ct.stateid = statedim.id and (statedim.dt = 'latest')" - + " join " + getDbName() + ".c1_countrytable countrydim on statedim.countryid = countrydim.id" - + " join " + getDbName() + ".c1_ziptable zipdim on ct.zipcode = zipdim.code and (zipdim.dt = 'latest')" + + " join " + getDbName() + "c1_statetable statedim on ct.stateid = statedim.id and (statedim.dt = 'latest')" + + " join " + getDbName() + "c1_countrytable countrydim on statedim.countryid = countrydim.id" + + " join " + getDbName() + "c1_ziptable zipdim on ct.zipcode = zipdim.code and (zipdim.dt = 'latest')" + ""; String expected =
