LENS-926 : Fixes Chain ref column from bridge table to be resolved correctly
Project: http://git-wip-us.apache.org/repos/asf/lens/repo Commit: http://git-wip-us.apache.org/repos/asf/lens/commit/91ccec77 Tree: http://git-wip-us.apache.org/repos/asf/lens/tree/91ccec77 Diff: http://git-wip-us.apache.org/repos/asf/lens/diff/91ccec77 Branch: refs/heads/current-release-line Commit: 91ccec775bfff7732da41b6a6d13e01752750f7f Parents: 5d2dccb Author: Amareshwari Sriramadasu <[email protected]> Authored: Thu Jan 21 17:43:26 2016 +0530 Committer: Rajat Khandelwal <[email protected]> Committed: Thu Jan 21 17:43:26 2016 +0530 ---------------------------------------------------------------------- .../cube/parse/DenormalizationResolver.java | 46 +-- .../lens/cube/parse/join/AutoJoinContext.java | 2 +- .../apache/lens/cube/parse/CubeTestSetup.java | 20 +- .../lens/cube/parse/TestBridgeTableQueries.java | 293 +++++++++++++++++++ .../lens/cube/parse/TestCubeRewriter.java | 3 +- .../lens/cube/parse/TestJoinResolver.java | 225 ++------------ 6 files changed, 354 insertions(+), 235 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/lens/blob/91ccec77/lens-cube/src/main/java/org/apache/lens/cube/parse/DenormalizationResolver.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/DenormalizationResolver.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/DenormalizationResolver.java index c83b9ac..f2dc2e5 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/DenormalizationResolver.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/DenormalizationResolver.java @@ -37,6 +37,7 @@ import org.apache.hadoop.hive.ql.parse.HiveParser; import org.antlr.runtime.CommonToken; +import lombok.Getter; import lombok.ToString; import lombok.extern.slf4j.Slf4j; @@ -67,14 +68,13 @@ public class DenormalizationResolver implements ContextRewriter { @ToString public static class PickedReference { - TableReference reference; + @Getter ChainRefCol chainRef; String srcAlias; String pickedFor; - PickedReference(TableReference reference, String srcAlias, String pickedFor) { + PickedReference(String srcAlias, String pickedFor) { this.srcAlias = srcAlias; - this.reference = reference; this.pickedFor = pickedFor; } @@ -83,38 +83,22 @@ public class DenormalizationResolver implements ContextRewriter { this.chainRef = chainRef; this.pickedFor = pickedFor; } - - String getDestTable() { - if (chainRef != null) { - return chainRef.getChainName(); - } - return reference.getDestTable(); - } - - String getRefColumn() { - if (chainRef != null) { - return chainRef.getRefColumn(); - } - return reference.getDestColumn(); - } } public static class DenormalizationContext { // map of column name to all references - private Map<String, Set<ReferencedQueriedColumn>> referencedCols = - new HashMap<String, Set<ReferencedQueriedColumn>>(); + private Map<String, Set<ReferencedQueriedColumn>> referencedCols = new HashMap<>(); // candidate table name to all the references columns it needs - private Map<String, Set<ReferencedQueriedColumn>> tableToRefCols = - new HashMap<String, Set<ReferencedQueriedColumn>>(); + private Map<String, Set<ReferencedQueriedColumn>> tableToRefCols = new HashMap<>(); private CubeQueryContext cubeql; // set of all picked references once all candidate tables are picked - private Set<PickedReference> pickedRefs = new HashSet<PickedReference>(); + private Set<PickedReference> pickedRefs = new HashSet<>(); // index on column name for picked references with map from column name to // pickedrefs - private Map<String, Set<PickedReference>> pickedReferences = new HashMap<String, Set<PickedReference>>(); + private Map<String, Set<PickedReference>> pickedReferences = new HashMap<>(); DenormalizationContext(CubeQueryContext cubeql) { this.cubeql = cubeql; @@ -123,7 +107,7 @@ public class DenormalizationResolver implements ContextRewriter { void addReferencedCol(String col, ReferencedQueriedColumn refer) { Set<ReferencedQueriedColumn> refCols = referencedCols.get(col); if (refCols == null) { - refCols = new HashSet<ReferencedQueriedColumn>(); + refCols = new HashSet<>(); referencedCols.put(col, refCols); } refCols.add(refer); @@ -144,7 +128,7 @@ public class DenormalizationResolver implements ContextRewriter { log.info("Adding denormalized column for column:{} for table:{}", col, table); Set<ReferencedQueriedColumn> refCols = tableToRefCols.get(table.getName()); if (refCols == null) { - refCols = new HashSet<ReferencedQueriedColumn>(); + refCols = new HashSet<>(); tableToRefCols.put(table.getName(), refCols); } refCols.add(refer); @@ -167,7 +151,7 @@ public class DenormalizationResolver implements ContextRewriter { private void addPickedReference(String col, PickedReference refer) { Set<PickedReference> refCols = pickedReferences.get(col); if (refCols == null) { - refCols = new HashSet<PickedReference>(); + refCols = new HashSet<>(); pickedReferences.put(col, refCols); } refCols.add(refer); @@ -187,7 +171,7 @@ public class DenormalizationResolver implements ContextRewriter { public Set<Dimension> rewriteDenormctx(CandidateFact cfact, Map<Dimension, CandidateDim> dimsToQuery, boolean replaceFact) throws LensException { - Set<Dimension> refTbls = new HashSet<Dimension>(); + Set<Dimension> refTbls = new HashSet<>(); if (!tableToRefCols.isEmpty()) { // pick referenced columns for fact @@ -206,7 +190,8 @@ public class DenormalizationResolver implements ContextRewriter { // Add the picked references to dimsToQuery for (PickedReference picked : pickedRefs) { if (isPickedFor(picked, cfact, dimsToQuery)) { - refTbls.add((Dimension) cubeql.getCubeTableForAlias(picked.getDestTable())); + refTbls.add((Dimension) cubeql.getCubeTableForAlias(picked.getChainRef().getChainName())); + cubeql.addColumnsQueried(picked.getChainRef().getChainName(), picked.getChainRef().getRefColumn()); } } } @@ -286,10 +271,11 @@ public class DenormalizationResolver implements ContextRewriter { return; } ASTNode newTableNode = - new ASTNode(new CommonToken(HiveParser.Identifier, query.getAliasForTableName(refered.getDestTable()))); + new ASTNode(new CommonToken(HiveParser.Identifier, refered.getChainRef().getChainName())); tableNode.setChild(0, newTableNode); - ASTNode newColumnNode = new ASTNode(new CommonToken(HiveParser.Identifier, refered.getRefColumn())); + ASTNode newColumnNode = new ASTNode(new CommonToken(HiveParser.Identifier, + refered.getChainRef().getRefColumn())); node.setChild(1, newColumnNode); } else { // recurse down http://git-wip-us.apache.org/repos/asf/lens/blob/91ccec77/lens-cube/src/main/java/org/apache/lens/cube/parse/join/AutoJoinContext.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/join/AutoJoinContext.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/join/AutoJoinContext.java index 4c30d3f..f70cbe4 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/join/AutoJoinContext.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/join/AutoJoinContext.java @@ -167,7 +167,7 @@ public class AutoJoinContext { public String getFromString(String fromTable, CandidateFact fact, Set<Dimension> qdims, Map<Dimension, CandidateDim> dimsToQuery, CubeQueryContext cubeql) throws LensException { String fromString = fromTable; - log.info("All paths dump:{}", cubeql.getAutoJoinCtx().getAllPaths()); + log.info("All paths dump:{} Queried dims:{}", cubeql.getAutoJoinCtx().getAllPaths(), qdims); if (qdims == null || qdims.isEmpty()) { return fromString; } http://git-wip-us.apache.org/repos/asf/lens/blob/91ccec77/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 caea3af..100d7c9 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 @@ -945,6 +945,18 @@ public class CubeTestSetup { cubeDimensions2.add(new BaseDimAttribute(new FieldSchema("userid", "int", "userid"))); cubeDimensions2.add(new BaseDimAttribute(new FieldSchema("xuserid", "int", "userid"))); cubeDimensions2.add(new BaseDimAttribute(new FieldSchema("yuserid", "int", "userid"))); + cubeDimensions2.add(new ReferencedDimAttribute(new FieldSchema("xsports", "array<string>", ""), + "xuser sports", "xusersports", "name", null, null, null)); + cubeDimensions2.add(new ReferencedDimAttribute(new FieldSchema("ysports", "array<string>", ""), + "yuser sports", "yusersports", "name", null, null, null)); + cubeDimensions2.add(new ReferencedDimAttribute(new FieldSchema("sports", "array<string>", ""), + "user sports", "usersports", "name", null, null, null)); + cubeDimensions2.add(new ReferencedDimAttribute(new FieldSchema("sportids", "array<int>", ""), + "user sports", "userInterestIds", "sport_id", null, null, null)); + cubeDimensions2.add(new ReferencedDimAttribute(new FieldSchema("statecountry", "string", ""), + "state country", "cubestatecountry", "name", null, null, null)); + cubeDimensions2.add(new ReferencedDimAttribute(new FieldSchema("citycountry", "string", ""), + "city country", "cubecitystatecountry", "name", null, null, null)); Map<String, String> cubeProperties = new HashMap<>(); cubeProperties.put(MetastoreUtil.getCubeTimedDimensionListKey(BASE_CUBE_NAME), @@ -1104,9 +1116,13 @@ public class CubeTestSetup { cubeDimensions2.add( new ReferencedDimAttribute(new FieldSchema("cityStateCapital", "string", "State's capital thru city"), "State's capital thru city", "cityState", "capital", null, null, null)); - client.createCube(BASE_CUBE_NAME, cubeMeasures2, cubeDimensions2, exprs, joinChains, cubeProperties); + Set<ExprColumn> baseExprs = new HashSet<>(exprs); + baseExprs.add(new ExprColumn(new FieldSchema("substrsprorts", "String", "substr of sports"), "substr sports", + "substr(sports, 10)")); - Map<String, String> derivedProperties = new HashMap<String, String>(); + client.createCube(BASE_CUBE_NAME, cubeMeasures2, cubeDimensions2, baseExprs, joinChains, cubeProperties); + + Map<String, String> derivedProperties = new HashMap<>(); derivedProperties.put(MetastoreConstants.CUBE_ALL_FIELDS_QUERIABLE, "true"); Set<String> measures = new HashSet<>(); measures.add("msr1"); http://git-wip-us.apache.org/repos/asf/lens/blob/91ccec77/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java new file mode 100644 index 0000000..e66c39b --- /dev/null +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestBridgeTableQueries.java @@ -0,0 +1,293 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.lens.cube.parse; + +import static org.apache.lens.cube.metadata.DateFactory.TWO_DAYS_RANGE; +import static org.apache.lens.cube.parse.CubeTestSetup.*; + +import static org.testng.Assert.assertTrue; + +import org.apache.hadoop.conf.Configuration; +import org.apache.hadoop.hive.conf.HiveConf; + +import org.testng.annotations.BeforeTest; +import org.testng.annotations.Test; + +public class TestBridgeTableQueries extends TestQueryRewrite { + + private static HiveConf hConf = new HiveConf(TestBridgeTableQueries.class); + + @BeforeTest + public void setupInstance() throws Exception { + hConf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1"); + hConf.setBoolean(CubeQueryConfUtil.DISABLE_AUTO_JOINS, false); + hConf.setBoolean(CubeQueryConfUtil.ENABLE_GROUP_BY_TO_SELECT, true); + hConf.setBoolean(CubeQueryConfUtil.ENABLE_SELECT_TO_GROUPBY, true); + hConf.setBoolean(CubeQueryConfUtil.DISABLE_AGGREGATE_RESOLVER, false); + hConf.setBoolean(CubeQueryConfUtil.ENABLE_FLATTENING_FOR_BRIDGETABLES, true); + } + + @Test + public void testBridgeTablesWithoutDimtablePartitioning() throws Exception { + String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + String hqlQuery = rewrite(query, hConf); + String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", + null, "group by usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sports, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + hqlQuery = rewrite(query, hConf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testBridgeTablesForExprFieldWithoutDimtablePartitioning() throws Exception { + String query = "select substr(usersports.name, 10), sum(msr2) from basecube where " + TWO_DAYS_RANGE; + String hqlQuery = rewrite(query, hConf); + String expected = getExpectedQuery("basecube", "select substr(usersports.name, 10), sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", + null, "group by substr(( usersports . name ), 10 )", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select substrsprorts, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + hqlQuery = rewrite(query, hConf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testFlattenBridgeTablesOFF() throws Exception { + Configuration conf = new Configuration(hConf); + conf.setBoolean(CubeQueryConfUtil.ENABLE_FLATTENING_FOR_BRIDGETABLES, false); + String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + String hqlQuery = rewrite(query, conf); + String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join " + getDbName() + "c1_user_interests_tbl user_interests on userdim.id = user_interests.user_id" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id", + null, "group by usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sports, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + hqlQuery = rewrite(query, conf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testFlattenBridgeTablesWithCustomAggregate() throws Exception { + Configuration conf = new Configuration(hConf); + conf.set(CubeQueryConfUtil.BRIDGE_TABLE_FIELD_AGGREGATOR, "custom_aggr"); + String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + String hqlQuery = rewrite(query, conf); + String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,custom_aggr(usersports.name) as name" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", + null, "group by usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sports, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + hqlQuery = rewrite(query, conf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testBridgeTablesWithMegringChains() throws Exception { + String query = "select userInterestIds.sport_id, usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + String hqlQuery = rewrite(query, hConf); + String expected = getExpectedQuery("basecube", "select userInterestIds.sport_id, usersports.name," + + " sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim on basecube.userid = userdim.id join (select userinterestids" + + ".user_id as user_id,collect_set(userinterestids.sport_id) as sport_id from " + getDbName() + + "c1_user_interests_tbl userinterestids group by userinterestids.user_id) userinterestids on userdim.id = " + + "userinterestids.user_id join (select userinterestids.user_id as user_id,collect_set(usersports.name) as name" + + " from " + getDbName() + "c1_user_interests_tbl userinterestids join " + + getDbName() + "c1_sports_tbl usersports on userinterestids.sport_id = usersports.id" + + " group by userinterestids.user_id) usersports on userdim.id = usersports.user_id", + null, "group by userInterestIds.sport_id, usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sportids, sports, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + hqlQuery = rewrite(query, hConf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testBridgeTablesWithMultipleFacts() throws Exception { + String query = "select usersports.name, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE; + String hqlQuery = rewrite(query, hConf); + String expected1 = getExpectedQuery("basecube", + "select usersports.name as `name`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + "group by usersports.name", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + String expected2 = getExpectedQuery("basecube", + "select usersports.name as `name`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + "group by usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); + TestCubeRewriter.compareContains(expected1, hqlQuery); + TestCubeRewriter.compareContains(expected2, hqlQuery); + String lower = hqlQuery.toLowerCase(); + assertTrue( + lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq2.msr2 msr2, mq1.msr12 msr12 from ") + || lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq1.msr2 msr2, mq2.msr12 msr12 from "), hqlQuery); + + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.name <=> mq2.name"), + hqlQuery); + + // run with chain ref column + query = "select sports, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE; + hqlQuery = rewrite(query, hConf); + expected1 = getExpectedQuery("basecube", + "select usersports.name as `sports`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + "group by usersports.name", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + expected2 = getExpectedQuery("basecube", + "select usersports.name as `sports`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() + + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " + + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() + + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, + "group by usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); + TestCubeRewriter.compareContains(expected1, hqlQuery); + TestCubeRewriter.compareContains(expected2, hqlQuery); + lower = hqlQuery.toLowerCase(); + assertTrue( + lower.startsWith("select coalesce(mq1.sports, mq2.sports) sports, mq2.msr2 msr2, mq1.msr12 msr12 from ") + || lower.startsWith("select coalesce(mq1.sports, mq2.sports) sports, mq1.msr2 msr2, mq2.msr12 msr12 from "), + hqlQuery); + + assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.sports <=> mq2.sports"), + hqlQuery); + } + + @Test + public void testBridgeTablesWithMultipleChains() throws Exception { + String query = "select usersports.name, xusersports.name, yusersports.name, sum(msr2) from basecube where " + + TWO_DAYS_RANGE; + String hqlQuery = rewrite(query, hConf); + String expected = getExpectedQuery("basecube", "select usersports.name, xusersports.name, yusersports.name," + + " sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim_1 on basecube.userid = userdim_1.id " + + " join (select user_interests_1.user_id as user_id, collect_set(usersports.name) as name from " + + getDbName() + "c1_user_interests_tbl user_interests_1 join " + getDbName() + "c1_sports_tbl usersports on " + + "user_interests_1.sport_id = usersports.id group by user_interests_1.user_id) " + + "usersports on userdim_1.id = usersports.user_id" + + " join " + getDbName() + "c1_usertable userdim_0 on basecube.yuserid = userdim_0.id " + + " join (select user_interests_0.user_id as user_id,collect_set(yusersports.name) as name from " + + getDbName() + "c1_user_interests_tbl user_interests_0 join " + getDbName() + "c1_sports_tbl yusersports on " + + " user_interests_0.sport_id = yusersports.id group by user_interests_0.user_id) yusersports on userdim_0.id =" + + " yusersports.user_id join " + getDbName() + "c1_usertable userdim on basecube.xuserid = userdim.id" + + " join (select user_interests.user_id as user_id,collect_set(xusersports.name) as name from " + + getDbName() + "c1_user_interests_tbl user_interests join " + getDbName() + "c1_sports_tbl xusersports" + + " on user_interests.sport_id = xusersports.id group by user_interests.user_id) xusersports on userdim.id = " + + " xusersports.user_id", null, "group by usersports.name, xusersports.name, yusersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sports, xsports, ysports, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + hqlQuery = rewrite(query, hConf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testBridgeTablesWithDimTablePartitioning() throws Exception { + Configuration conf = new Configuration(hConf); + conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C2"); + String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + String hqlQuery = rewrite(query, conf); + String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", + " join " + getDbName() + "c2_usertable userdim ON basecube.userid = userdim.id and userdim.dt='latest' " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + + " from " + getDbName() + "c2_user_interests_tbl user_interests" + + " join " + getDbName() + "c2_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " and usersports.dt='latest and user_interests.dt='latest'" + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id ", + null, "group by usersports.name", null, + getWhereForDailyAndHourly2days("basecube", "c2_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sports, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + hqlQuery = rewrite(query, conf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } + + @Test + public void testBridgeTablesWithNormalJoins() throws Exception { + String query = "select usersports.name, cubestatecountry.name, cubecitystatecountry.name," + + " sum(msr2) from basecube where " + TWO_DAYS_RANGE; + String hqlQuery = rewrite(query, hConf); + String expected = getExpectedQuery("basecube", "select usersports.name, cubestatecountry.name, " + + "cubecitystatecountry.name, sum(basecube.msr2) FROM ", + " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " + + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + + " from " + getDbName() + "c1_user_interests_tbl user_interests" + + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" + + " group by user_interests.user_id) usersports" + + " on userdim.id = usersports.user_id " + + " join " + getDbName() + "c1_citytable citydim on basecube.cityid = citydim.id and (citydim.dt = 'latest')" + + " join " + getDbName() + + "c1_statetable statedim_0 on citydim.stateid=statedim_0.id and statedim_0.dt='latest'" + + " join " + getDbName() + + "c1_countrytable cubecitystatecountry on statedim_0.countryid=cubecitystatecountry.id" + + " join " + getDbName() + "c1_statetable statedim on basecube.stateid=statedim.id and (statedim.dt = 'latest')" + + " join " + getDbName() + "c1_countrytable cubestatecountry on statedim.countryid=cubestatecountry.id ", + null, "group by usersports.name, cubestatecountry.name, cubecitystatecountry.name", null, + getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); + TestCubeRewriter.compareQueries(hqlQuery, expected); + // run with chain ref column + query = "select sports, statecountry, citycountry, sum(msr2) from basecube where " + TWO_DAYS_RANGE; + hqlQuery = rewrite(query, hConf); + TestCubeRewriter.compareQueries(hqlQuery, expected); + } +} http://git-wip-us.apache.org/repos/asf/lens/blob/91ccec77/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 698f36c..e569fb1 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 @@ -306,7 +306,8 @@ public class TestCubeRewriter extends TestQueryRewrite { System.err.println("__FAILED__ " + method + "\n\tExpected: " + expected + "\n\t---------\n\tActual: " + actual); } - assertTrue(actualTrimmed.toLowerCase().contains(expectedTrimmed.toLowerCase())); + assertTrue(actualTrimmed.toLowerCase().contains(expectedTrimmed.toLowerCase()), "Expected:" + expected + + "Actual:" + actual); } @Test http://git-wip-us.apache.org/repos/asf/lens/blob/91ccec77/lens-cube/src/test/java/org/apache/lens/cube/parse/TestJoinResolver.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestJoinResolver.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestJoinResolver.java index 2cf92b9..6430ed1 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestJoinResolver.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestJoinResolver.java @@ -86,7 +86,7 @@ public class TestJoinResolver extends TestQueryRewrite { expectedClauses.add(getDbName() + "c1_testdim4tbl dim4chain on testdim3.testdim4id = dim4chain.id and (dim4chain.dt = 'latest')"); - List<String> actualClauses = new ArrayList<String>(); + List<String> actualClauses = new ArrayList<>(); for (String clause : StringUtils.splitByWholeSeparator(getAutoResolvedFromString(rewrittenQuery), "join")) { if (StringUtils.isNotBlank(clause)) { actualClauses.add(clause.trim()); @@ -135,13 +135,13 @@ public class TestJoinResolver extends TestQueryRewrite { String hqlQuery = rewrite(query, hconf); String expected = getExpectedQuery("testcube", "select citydim.name, testDim4.name, sum(testcube.msr2) FROM ", " left outer JOIN " + getDbName() + "c1_citytable citydim on testcube.cityid = citydim.id +" - + " and (( citydim . name ) = 'FOOBAR' ) and (citydim.dt = 'latest')" - + " right outer join " + getDbName() - + "c1_testdim2tbl testdim2 on testcube.dim2 = testdim2.id and (testdim2.dt = 'latest')" - + " right outer join " + getDbName() + "c1_testdim3tbl testdim3 on testdim2.testdim3id = testdim3.id and " - + "(testdim3.dt = 'latest') " - + " right outer join " + getDbName() + "c1_testdim4tbl testdim4 on testdim3.testdim4id = testdim4.id and " - + "(( testdim4 . name ) = 'TESTDIM4NAME' ) and (testdim4.dt = 'latest')", + + " and (( citydim . name ) = 'FOOBAR' ) and (citydim.dt = 'latest')" + + " right outer join " + getDbName() + + "c1_testdim2tbl testdim2 on testcube.dim2 = testdim2.id and (testdim2.dt = 'latest')" + + " right outer join " + getDbName() + "c1_testdim3tbl testdim3 on testdim2.testdim3id = testdim3.id and " + + "(testdim3.dt = 'latest') " + + " right outer join " + getDbName() + "c1_testdim4tbl testdim4 on testdim3.testdim4id = testdim4.id and " + + "(( testdim4 . name ) = 'TESTDIM4NAME' ) and (testdim4.dt = 'latest')", null, "group by citydim.name, testdim4.name", null, getWhereForDailyAndHourly2days("testcube", "c1_summary3")); TestCubeRewriter.compareQueries(hqlQuery, expected); @@ -177,7 +177,7 @@ public class TestJoinResolver extends TestQueryRewrite { // Check that aliases are preserved in the join clause String expected = getExpectedQuery("testcube", "select cubecity.name, sum(testcube.msr2) FROM ", " left outer join " + getDbName() - + "c1_citytable cubecity ON testcube.cityid = cubecity.id and (cubecity.dt = 'latest')", + + "c1_citytable cubecity ON testcube.cityid = cubecity.id and (cubecity.dt = 'latest')", null, " group by cubecity.name", null, getWhereForHourly2days("testcube", "c1_testfact2")); TestCubeRewriter.compareQueries(hqlQuery, expected); @@ -186,7 +186,7 @@ public class TestJoinResolver extends TestQueryRewrite { // Check that aliases are preserved in the join clause expected = getExpectedQuery("testcube", "select cubecity.name, sum(testcube.msr2) FROM ", " full outer join " + getDbName() - + "c1_citytable cubecity ON testcube.cityid = cubecity.id and (cubecity.dt = 'latest')", + + "c1_citytable cubecity ON testcube.cityid = cubecity.id and (cubecity.dt = 'latest')", null, " group by cubecity.name", null, getWhereForHourly2days("testcube", "c1_testfact2")); TestCubeRewriter.compareQueries(hqlQuery, expected); @@ -195,7 +195,7 @@ public class TestJoinResolver extends TestQueryRewrite { // Check that aliases are preserved in the join clause expected = getExpectedQuery("testcube", "select cubecity.name, sum(testcube.msr2) FROM ", " right outer join " + getDbName() - + "c1_citytable cubecity ON testcube.cityid = cubecity.id", + + "c1_citytable cubecity ON testcube.cityid = cubecity.id", null, " and (cubecity.dt = 'latest') group by cubecity.name", null, getWhereForHourly2days("testcube", "c1_testfact2")); TestCubeRewriter.compareQueries(hqlQuery, expected); @@ -224,7 +224,7 @@ public class TestJoinResolver extends TestQueryRewrite { // Check that aliases are preserved in the join clause String expected = getExpectedQuery("t", "select cubecity.name, sum(t.msr2) FROM ", " left outer join " + getDbName() - + "c1_citytable cubecity ON t.cityid = cubecity.id and (cubecity.dt = 'latest')", + + "c1_citytable cubecity ON t.cityid = cubecity.id and (cubecity.dt = 'latest')", null, " group by cubecity.name", null, getWhereForHourly2days("t", "c1_testfact2")); TestCubeRewriter.compareQueries(hqlQuery, expected); } @@ -237,7 +237,7 @@ public class TestJoinResolver extends TestQueryRewrite { String hqlQuery = rewrite(query, tConf); String expected = getExpectedQuery("citydim", "select citydim.name, citystate.name from ", " inner join " + getDbName() - + "c1_statetable citystate on citydim.stateid = citystate.id and (citystate.dt = 'latest')", + + "c1_statetable citystate on citydim.stateid = citystate.id and (citystate.dt = 'latest')", null, " limit 10", "c1_citytable", true); compareQueries(hqlQuery, expected); } @@ -252,7 +252,7 @@ public class TestJoinResolver extends TestQueryRewrite { String hqlQuery = rewrite(queryWithJoin, tConf); String expected = getExpectedQuery("citydim", "select citydim.name, statedim.name from ", " inner join " + getDbName() - + "c1_statetable statedim on citydim.stateid = statedim.id and citydim.dt='latest' and statedim.dt='latest'", + + "c1_statetable statedim on citydim.stateid = statedim.id and citydim.dt='latest' and statedim.dt='latest'", null, null, "c1_citytable", false); compareQueries(hqlQuery, expected); } @@ -264,7 +264,7 @@ public class TestJoinResolver extends TestQueryRewrite { String hqlQuery = rewrite(q1, hconf); String expected = getExpectedQuery("citydim", "select citydim.name, statedim.name from ", " left outer join " + getDbName() - + "c1_statetable statedim on citydim.stateid = statedim.id and citydim.dt='latest' and statedim.dt='latest'", + + "c1_statetable statedim on citydim.stateid = statedim.id and citydim.dt='latest' and statedim.dt='latest'", null, null, "c1_citytable", false); compareQueries(hqlQuery, expected); @@ -273,7 +273,7 @@ public class TestJoinResolver extends TestQueryRewrite { hqlQuery = rewrite(q2, hconf); expected = getExpectedQuery("citydim", "select citydim.name, statedim.name from ", " right outer join " + getDbName() - + "c1_statetable statedim on citydim.stateid = statedim.id and citydim.dt='latest' and statedim.dt='latest'", + + "c1_statetable statedim on citydim.stateid = statedim.id and citydim.dt='latest' and statedim.dt='latest'", null, null, "c1_citytable", false); compareQueries(hqlQuery, expected); @@ -282,8 +282,10 @@ public class TestJoinResolver extends TestQueryRewrite { hqlQuery = rewrite(q3, hconf); expected = getExpectedQuery("citydim", "select citydim.name, statedim.name from ", " full outer join " + getDbName() - + "c1_statetable statedim on citydim.stateid = statedim.id and citydim.dt='latest' and statedim.dt='latest'", + + "c1_statetable statedim on citydim.stateid = statedim.id and citydim.dt='latest' and statedim.dt='latest'", null, null, "c1_citytable", false); + compareQueries(hqlQuery, expected); + } @Test @@ -294,7 +296,7 @@ public class TestJoinResolver extends TestQueryRewrite { String hqlQuery = rewrite(q, conf); String expected = getExpectedQuery("citydim", "select citydim.name, citystate.name from ", " left outer join " + getDbName() - + "c1_statetable citystate on citydim.stateid = citystate.id and (citystate.dt = 'latest')", + + "c1_statetable citystate on citydim.stateid = citystate.id and (citystate.dt = 'latest')", null, " limit 10", "c1_citytable", true); compareQueries(hqlQuery, expected); @@ -302,7 +304,7 @@ public class TestJoinResolver extends TestQueryRewrite { hqlQuery = rewrite(q, conf); expected = getExpectedQuery("citydim", "select citydim.name, citystate.name from ", " right outer join " + getDbName() - + "c1_statetable citystate on citydim.stateid = citystate.id and (citydim.dt = 'latest')", + + "c1_statetable citystate on citydim.stateid = citystate.id and (citydim.dt = 'latest')", " citystate.dt='latest' ", "limit 10", "c1_citytable", false); compareQueries(hqlQuery, expected); @@ -451,8 +453,8 @@ public class TestJoinResolver extends TestQueryRewrite { // Test 4 Dim only query with join chains - List<String> expectedClauses = new ArrayList<String>(); - List<String> actualClauses = new ArrayList<String>(); + List<String> expectedClauses = new ArrayList<>(); + List<String> actualClauses = new ArrayList<>(); String dimOnlyQuery = "select testDim2.name, testDim2.cityStateCapital FROM testDim2 where " + TWO_DAYS_RANGE; CubeQueryRewriter driver = new CubeQueryRewriter(hconf, hconf); CubeQueryContext rewrittenQuery = driver.rewrite(dimOnlyQuery); @@ -636,7 +638,7 @@ public class TestJoinResolver extends TestQueryRewrite { CubeQueryContext rewrittenQuery = driver.rewrite(dimOnlyQuery); rewrittenQuery.toHQL(); Dimension citydim = CubeMetastoreClient.getInstance(hconf).getDimension("citydim"); - Set<String> cdimTables = new HashSet<String>(); + Set<String> cdimTables = new HashSet<>(); for (CandidateDim cdim : rewrittenQuery.getCandidateDims().get(citydim)) { cdimTables.add(cdim.getName()); } @@ -660,183 +662,4 @@ public class TestJoinResolver extends TestQueryRewrite { assertLensExceptionInRewrite("select unreachableDim_chain.name from testcube where " + TWO_DAYS_RANGE, hconf, LensCubeErrorCode.NO_FACT_HAS_COLUMN); } - - @Test - public void testBridgeTablesWithoutDimtablePartitioning() throws Exception { - Configuration conf = new Configuration(hconf); - conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1"); - String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; - String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", - " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" - + " from " + getDbName() + "c1_user_interests_tbl user_interests" - + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" - + " group by user_interests.user_id) usersports" - + " on userdim.id = usersports.user_id ", - null, "group by usersports.name", null, - getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); - TestCubeRewriter.compareQueries(hqlQuery, expected); - } - - @Test - public void testFlattenBridgeTablesOFF() throws Exception { - Configuration conf = new Configuration(hconf); - conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1"); - conf.setBoolean(CubeQueryConfUtil.ENABLE_FLATTENING_FOR_BRIDGETABLES, false); - String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; - String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", - " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join " + getDbName() + "c1_user_interests_tbl user_interests on userdim.id = user_interests.user_id" - + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id", - null, "group by usersports.name", null, - getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); - TestCubeRewriter.compareQueries(hqlQuery, expected); - } - - @Test - public void testFlattenBridgeTablesWithCustomAggregate() throws Exception { - Configuration conf = new Configuration(hconf); - conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1"); - conf.set(CubeQueryConfUtil.BRIDGE_TABLE_FIELD_AGGREGATOR, "custom_aggr"); - String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; - String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", - " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,custom_aggr(usersports.name) as name" - + " from " + getDbName() + "c1_user_interests_tbl user_interests" - + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" - + " group by user_interests.user_id) usersports" - + " on userdim.id = usersports.user_id ", - null, "group by usersports.name", null, - getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); - TestCubeRewriter.compareQueries(hqlQuery, expected); - } - - @Test - public void testBridgeTablesWithMegringChains() throws Exception { - Configuration conf = new Configuration(hconf); - conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1"); - String query = "select userInterestIds.sport_id, usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; - String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select userInterestIds.sport_id, usersports.name," - + " sum(basecube.msr2) FROM ", - " join " + getDbName() + "c1_usertable userdim on basecube.userid = userdim.id join (select userinterestids" - + ".user_id as user_id,collect_set(userinterestids.sport_id) as sport_id from " + getDbName() - + "c1_user_interests_tbl userinterestids group by userinterestids.user_id) userinterestids on userdim.id = " - + "userinterestids.user_id join (select userinterestids.user_id as user_id,collect_set(usersports.name) as name" - + " from " + getDbName() + "c1_user_interests_tbl userinterestids join " - + getDbName() + "c1_sports_tbl usersports on userinterestids.sport_id = usersports.id" - + " group by userinterestids.user_id) usersports on userdim.id = usersports.user_id", - null, "group by userInterestIds.sport_id, usersports.name", null, - getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); - TestCubeRewriter.compareQueries(hqlQuery, expected); - } - - @Test - public void testBridgeTablesWithMultipleFacts() throws Exception { - Configuration conf = new Configuration(hconf); - conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1"); - String query = "select usersports.name, sum(msr2), sum(msr12) from basecube where " + TWO_DAYS_RANGE; - String hqlQuery = rewrite(query, conf); - String expected1 = getExpectedQuery("basecube", - "select usersports.name as `name`, sum(basecube.msr2) as `msr2` FROM ", " join " + getDbName() - + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " - + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() - + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" - + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, - "group by usersports.name", null, getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); - String expected2 = getExpectedQuery("basecube", - "select usersports.name as `name`, sum(basecube.msr12) as `msr12` FROM ", " join " + getDbName() - + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" + " from " - + getDbName() + "c1_user_interests_tbl user_interests" + " join " + getDbName() - + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" - + " group by user_interests.user_id) usersports" + " on userdim.id = usersports.user_id ", null, - "group by usersports.name", null, - getWhereForDailyAndHourly2days("basecube", "c1_testfact2_base")); - TestCubeRewriter.compareContains(expected1, hqlQuery); - TestCubeRewriter.compareContains(expected2, hqlQuery); - String lower = hqlQuery.toLowerCase(); - assertTrue( - lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq2.msr2 msr2, mq1.msr12 msr12 from ") - || lower.startsWith("select coalesce(mq1.name, mq2.name) name, mq1.msr2 msr2, mq2.msr12 msr12 from "), hqlQuery); - - assertTrue(hqlQuery.contains("mq1 full outer join ") && hqlQuery.endsWith("mq2 on mq1.name <=> mq2.name"), - hqlQuery); - } - - @Test - public void testBridgeTablesWithMultipleChains() throws Exception { - Configuration conf = new Configuration(hconf); - conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1"); - String query = "select usersports.name, xusersports.name, yusersports.name, sum(msr2) from basecube where " - + TWO_DAYS_RANGE; - String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.name, xusersports.name, yusersports.name," - + " sum(basecube.msr2) FROM ", - " join " + getDbName() + "c1_usertable userdim_1 on basecube.userid = userdim_1.id " - + " join (select user_interests_1.user_id as user_id, collect_set(usersports.name) as name from " - + getDbName() + "c1_user_interests_tbl user_interests_1 join " + getDbName() + "c1_sports_tbl usersports on " - + "user_interests_1.sport_id = usersports.id group by user_interests_1.user_id) " - + "usersports on userdim_1.id = usersports.user_id" - + " join " + getDbName() + "c1_usertable userdim_0 on basecube.yuserid = userdim_0.id " - + " join (select user_interests_0.user_id as user_id,collect_set(yusersports.name) as name from " - + getDbName() + "c1_user_interests_tbl user_interests_0 join " + getDbName() + "c1_sports_tbl yusersports on " - + " user_interests_0.sport_id = yusersports.id group by user_interests_0.user_id) yusersports on userdim_0.id =" - + " yusersports.user_id join " + getDbName() + "c1_usertable userdim on basecube.xuserid = userdim.id" - + " join (select user_interests.user_id as user_id,collect_set(xusersports.name) as name from " - + getDbName() + "c1_user_interests_tbl user_interests join " + getDbName() + "c1_sports_tbl xusersports" - + " on user_interests.sport_id = xusersports.id group by user_interests.user_id) xusersports on userdim.id = " - + " xusersports.user_id", null, "group by usersports.name, xusersports.name, yusersports.name", null, - getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); - TestCubeRewriter.compareQueries(hqlQuery, expected); - } - @Test - public void testBridgeTablesWithDimTablePartitioning() throws Exception { - Configuration conf = new Configuration(hconf); - conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C2"); - String query = "select usersports.name, sum(msr2) from basecube where " + TWO_DAYS_RANGE; - String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.name, sum(basecube.msr2) FROM ", - " join " + getDbName() + "c2_usertable userdim ON basecube.userid = userdim.id and userdim.dt='latest' " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" - + " from " + getDbName() + "c2_user_interests_tbl user_interests" - + " join " + getDbName() + "c2_sports_tbl usersports on user_interests.sport_id = usersports.id" - + " and usersports.dt='latest and user_interests.dt='latest'" - + " group by user_interests.user_id) usersports" - + " on userdim.id = usersports.user_id ", - null, "group by usersports.name", null, - getWhereForDailyAndHourly2days("basecube", "c2_testfact1_base")); - TestCubeRewriter.compareQueries(hqlQuery, expected); - } - - @Test - public void testBridgeTablesWithNormalJoins() throws Exception { - Configuration conf = new Configuration(hconf); - conf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1"); - String query = "select usersports.name, cubestatecountry.name, cubecitystatecountry.name," - + " sum(msr2) from basecube where " + TWO_DAYS_RANGE; - String hqlQuery = rewrite(query, conf); - String expected = getExpectedQuery("basecube", "select usersports.name, cubestatecountry.name, " - + "cubecitystatecountry.name, sum(basecube.msr2) FROM ", - " join " + getDbName() + "c1_usertable userdim ON basecube.userid = userdim.id " - + " join (select user_interests.user_id as user_id,collect_set(usersports.name) as name" - + " from " + getDbName() + "c1_user_interests_tbl user_interests" - + " join " + getDbName() + "c1_sports_tbl usersports on user_interests.sport_id = usersports.id" - + " group by user_interests.user_id) usersports" - + " on userdim.id = usersports.user_id " - + " join " + getDbName() + "c1_citytable citydim on basecube.cityid = citydim.id and (citydim.dt = 'latest')" - + " join " + getDbName() - + "c1_statetable statedim_0 on citydim.stateid=statedim_0.id and statedim_0.dt='latest'" - + " join " + getDbName() - + "c1_countrytable cubecitystatecountry on statedim_0.countryid=cubecitystatecountry.id" - + " join " + getDbName() + "c1_statetable statedim on basecube.stateid=statedim.id and (statedim.dt = 'latest')" - + " join " + getDbName() + "c1_countrytable cubestatecountry on statedim.countryid=cubestatecountry.id ", - null, "group by usersports.name, cubestatecountry.name, cubecitystatecountry.name", null, - getWhereForDailyAndHourly2days("basecube", "c1_testfact1_base")); - TestCubeRewriter.compareQueries(hqlQuery, expected); - } }
