Repository: lens Updated Branches: refs/heads/master bbf2154ee -> 1f12ce55a
LENS-1144 : Fix writing multi storage queries when storage start and end time is configured Project: http://git-wip-us.apache.org/repos/asf/lens/repo Commit: http://git-wip-us.apache.org/repos/asf/lens/commit/1f12ce55 Tree: http://git-wip-us.apache.org/repos/asf/lens/tree/1f12ce55 Diff: http://git-wip-us.apache.org/repos/asf/lens/diff/1f12ce55 Branch: refs/heads/master Commit: 1f12ce55a74fd81edfb334f625c214ca370db9c1 Parents: bbf2154 Author: Amareshwari Sriramadasu <[email protected]> Authored: Wed May 18 10:53:21 2016 +0530 Committer: Amareshwari Sriramadasu <[email protected]> Committed: Wed May 18 10:53:21 2016 +0530 ---------------------------------------------------------------------- .../lens/cube/metadata/CubeMetastoreClient.java | 43 ++- .../lens/cube/parse/StorageTableResolver.java | 26 +- .../apache/lens/cube/metadata/DateFactory.java | 2 + .../cube/metadata/TestCubeMetastoreClient.java | 28 +- .../apache/lens/cube/parse/CubeTestSetup.java | 33 +- .../lens/cube/parse/TestCubeRewriter.java | 302 +-------------- .../lens/cube/parse/TestUnionQueries.java | 374 +++++++++++++++++++ .../lens/server/api/LensServerAPITestUtil.java | 5 +- 8 files changed, 496 insertions(+), 317 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/lens/blob/1f12ce55/lens-cube/src/main/java/org/apache/lens/cube/metadata/CubeMetastoreClient.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/metadata/CubeMetastoreClient.java b/lens-cube/src/main/java/org/apache/lens/cube/metadata/CubeMetastoreClient.java index c8190bc..71f6b39 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/metadata/CubeMetastoreClient.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/metadata/CubeMetastoreClient.java @@ -2383,27 +2383,58 @@ public class CubeMetastoreClient { return storageTimes; } + /* + * Storage table is not a candidate for range (t0, tn) : + * if start_time is after tn; or end date is before t0. + */ public boolean isStorageTableCandidateForRange(String storageTableName, Date fromDate, Date toDate) throws HiveException, LensException { + List<Date> storageEndDates = getStorageTimes(storageTableName, MetastoreUtil.getStoragetableEndTimesKey()); + for(Date endDate : storageEndDates) { + // endDate is exclusive + if (endDate.before(fromDate) || endDate.equals(fromDate)) { + log.debug("from date {} is after validity end time: {}, hence discarding {}", + fromDate, endDate, storageTableName); + return false; + } + } + List<Date> storageStartDates = getStorageTimes(storageTableName, MetastoreUtil.getStoragetableStartTimesKey()); for(Date startDate : storageStartDates) { - if (fromDate.before(startDate)) { - log.info("from date {} is before validity start time: {}, hence discarding {}", - fromDate, startDate, storageTableName); + // toDate is exclusive on the range + if (startDate.after(toDate) || startDate.equals(toDate)) { + log.debug("to date {} is before validity start time: {}, hence discarding {}", + toDate, startDate, storageTableName); + return false; + } + } + return true; + } + + // Check if partition is valid wrt start and end dates + public boolean isStorageTablePartitionACandidate(String storageTableName, Date partDate) throws + HiveException, LensException { + List<Date> storageStartDates = getStorageTimes(storageTableName, MetastoreUtil.getStoragetableStartTimesKey()); + for(Date startDate : storageStartDates) { + if (partDate.before(startDate)) { + log.info("part date {} is before validity start time: {}, hence discarding {}", + partDate, startDate, storageTableName); return false; } } List<Date> storageEndDates = getStorageTimes(storageTableName, MetastoreUtil.getStoragetableEndTimesKey()); for(Date endDate : storageEndDates) { - if (toDate.after(endDate)) { - log.info("to date {} is after validity end time: {}, hence discarding {}", - toDate, endDate, storageTableName); + // end date should be exclusive + if (partDate.after(endDate) || partDate.equals(endDate)) { + log.info("part date {} is after validity end time: {}, hence discarding {}", + partDate, endDate, storageTableName); return false; } } return true; } + public boolean isStorageTableCandidateForRange(String storageTableName, String fromDate, String toDate) throws HiveException, LensException { Date now = new Date(); http://git-wip-us.apache.org/repos/asf/lens/blob/1f12ce55/lens-cube/src/main/java/org/apache/lens/cube/parse/StorageTableResolver.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/main/java/org/apache/lens/cube/parse/StorageTableResolver.java b/lens-cube/src/main/java/org/apache/lens/cube/parse/StorageTableResolver.java index 46b6bb7..f685500 100644 --- a/lens-cube/src/main/java/org/apache/lens/cube/parse/StorageTableResolver.java +++ b/lens-cube/src/main/java/org/apache/lens/cube/parse/StorageTableResolver.java @@ -668,9 +668,13 @@ class StorageTableResolver implements ContextRewriter { // Add non existing partitions for all cases of whether we populate all non existing or not. missingPartitions.add(part); if (!failOnPartialData) { + Set<String> st = getStorageTablesWithoutPartCheck(part, storageTbls); + if (st.isEmpty()) { + log.info("No eligible storage tables"); + return false; + } partitions.add(part); - // add all storage tables as the answering tables - part.getStorageTables().addAll(storageTbls); + part.getStorageTables().addAll(st); } } else { log.info("No finer granual partitions exist for {}", part); @@ -687,10 +691,26 @@ class StorageTableResolver implements ContextRewriter { updatePeriods, addNonExistingParts, failOnPartialData, skipStorageCauses, missingPartitions); } + private Set<String> getStorageTablesWithoutPartCheck(FactPartition part, + Set<String> storageTableNames) throws LensException, HiveException { + Set<String> validStorageTbls = new HashSet<>(); + for (String storageTableName : storageTableNames) { + // skip all storage tables for which are not eligible for this partition + if (client.isStorageTablePartitionACandidate(storageTableName, part.getPartSpec())) { + validStorageTbls.add(storageTableName); + } else { + log.info("Skipping {} as it is not valid for part {}", storageTableName, part.getPartSpec()); + } + } + return validStorageTbls; + } + private void updateFactPartitionStorageTablesFrom(CubeFactTable fact, FactPartition part, Set<String> storageTableNames) throws LensException, HiveException, ParseException { for (String storageTableName : storageTableNames) { - if (client.factPartitionExists(fact, part, storageTableName)) { + // skip all storage tables for which are not eligible for this partition + if (client.isStorageTablePartitionACandidate(storageTableName, part.getPartSpec()) + && (client.factPartitionExists(fact, part, storageTableName))) { part.getStorageTables().add(storageTableName); part.setFound(true); } http://git-wip-us.apache.org/repos/asf/lens/blob/1f12ce55/lens-cube/src/test/java/org/apache/lens/cube/metadata/DateFactory.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/metadata/DateFactory.java b/lens-cube/src/test/java/org/apache/lens/cube/metadata/DateFactory.java index e7b9403..3bdc047 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/metadata/DateFactory.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/metadata/DateFactory.java @@ -156,6 +156,7 @@ public class DateFactory { public static final String THIS_YEAR_RANGE; public static final String LAST_YEAR_RANGE; public static final String TWO_MONTHS_RANGE_UPTO_MONTH; + public static final String TWO_MONTHS_RANGE_UPTO_DAYS; public static final String TWO_MONTHS_RANGE_UPTO_HOURS; public static final String TWO_DAYS_RANGE_BEFORE_4_DAYS; private static boolean zerothHour; @@ -193,6 +194,7 @@ public class DateFactory { THIS_YEAR_RANGE = getTimeRangeString(YEARLY, 0, 1); LAST_YEAR_RANGE = getTimeRangeString(YEARLY, -1, 0); TWO_MONTHS_RANGE_UPTO_MONTH = getTimeRangeString(MONTHLY, -2, 0); + TWO_MONTHS_RANGE_UPTO_DAYS = getTimeRangeString(MONTHLY, -2, 0, DAILY); TWO_MONTHS_RANGE_UPTO_HOURS = getTimeRangeString(MONTHLY, -2, 0, HOURLY); // calculate LAST_HOUR_TIME_RANGE http://git-wip-us.apache.org/repos/asf/lens/blob/1f12ce55/lens-cube/src/test/java/org/apache/lens/cube/metadata/TestCubeMetastoreClient.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/metadata/TestCubeMetastoreClient.java b/lens-cube/src/test/java/org/apache/lens/cube/metadata/TestCubeMetastoreClient.java index d992967..e06ff5b 100644 --- a/lens-cube/src/test/java/org/apache/lens/cube/metadata/TestCubeMetastoreClient.java +++ b/lens-cube/src/test/java/org/apache/lens/cube/metadata/TestCubeMetastoreClient.java @@ -20,6 +20,7 @@ package org.apache.lens.cube.metadata; import static org.apache.lens.cube.metadata.DateFactory.*; +import static org.apache.lens.cube.metadata.DateUtil.resolveDate; import static org.apache.lens.cube.metadata.MetastoreUtil.*; import static org.apache.lens.cube.metadata.UpdatePeriod.*; import static org.apache.lens.server.api.util.LensUtil.getHashMap; @@ -1014,8 +1015,8 @@ public class TestCubeMetastoreClient { StorageTableDesc s1 = new StorageTableDesc(TextInputFormat.class, HiveIgnoreKeyTextOutputFormat.class, datePartSingleton, datePartKeySingleton); - s1.getTblProps().put(MetastoreUtil.getStoragetableStartTimesKey(), "2015, now-10 days"); - s1.getTblProps().put(MetastoreUtil.getStoragetableEndTimesKey(), "now - 1 day"); + s1.getTblProps().put(MetastoreUtil.getStoragetableStartTimesKey(), "2015, now.day -10 days"); + s1.getTblProps().put(MetastoreUtil.getStoragetableEndTimesKey(), "now.day - 1 day"); Map<String, Set<UpdatePeriod>> updatePeriods = getHashMap(c1, Sets.newHashSet(HOURLY, DAILY)); Map<String, StorageTableDesc> storageTables = getHashMap(c1, s1); @@ -1143,14 +1144,31 @@ public class TestCubeMetastoreClient { private void assertRangeValidityForStorageTable(String storageTable) throws HiveException, LensException { Object[][] testCases = new Object[][] { {"now - 15 days", "now - 11 days", false}, - {"now - 15 days", "now - 1 hour", false}, - {"now - 9 days", "now - 1 hour", false}, + {"now - 15 days", "now.day - 10 days", false}, + {"now - 15 days", "now - 1 hour", true}, + {"now - 9 days", "now - 1 hour", true}, {"now - 3 hour", "now - 1 hour", false}, + {"now - 10 days", "now - 1 hour", true}, {"now - 9 days", "now - 2 days", true}, + {"now - 9 days", "now.day - 1 days", true}, + {"now.day - 1 days", "now - 1 hour", false}, }; for(Object[] testCase: testCases) { assertEquals(client.isStorageTableCandidateForRange(storageTable, testCase[0].toString(), testCase[1].toString()), - testCase[2]); + testCase[2], "Failed for " + Arrays.asList(testCase).toString()); + } + Object[][] partTimes = new Object[][] { + {"now - 15 days", false}, + {"now - 10 days", true}, + {"now - 1 hour", false}, + {"now.day - 1 day", false}, + {"now.day - 10 days", true}, + {"now - 9 days", true}, + {"now - 2 days", true}, + }; + for(Object[] partTime : partTimes) { + assertEquals(client.isStorageTablePartitionACandidate(storageTable, resolveDate(partTime[0].toString(), + new Date())), partTime[1], "Failed for " + Arrays.asList(partTime).toString()); } } http://git-wip-us.apache.org/repos/asf/lens/blob/1f12ce55/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 b9694d7..2ab3fd0 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 @@ -101,6 +101,7 @@ public class CubeTestSetup { private static String c2 = "C2"; private static String c3 = "C3"; private static String c4 = "C4"; + private static String c5 = "C5"; private static String c99 = "C99"; private static Map<String, String> factValidityProperties = Maps.newHashMap(); @Getter @@ -439,6 +440,14 @@ public class CubeTestSetup { return storageTableToWhereClause; } + public static Map<String, String> getWhereForDays(String dailyTable, Date startDay, Date endDay) { + Map<String, String> storageTableToWhereClause = new LinkedHashMap<>(); + List<String> parts = new ArrayList<>(); + addParts(parts, DAILY, startDay, DateUtil.getFloorDate(endDay, DAILY)); + storageTableToWhereClause.put(getDbName() + dailyTable, + StorageUtil.getWherePartClause("dt", TEST_CUBE_NAME, parts)); + return storageTableToWhereClause; + } public static Map<String, String> getWhereForHourly2days(String hourlyTable) { return getWhereForHourly2days(TEST_CUBE_NAME, hourlyTable); } @@ -1471,16 +1480,33 @@ public class CubeTestSetup { s2.setPartCols(s2PartCols); s2.setTimePartCols(Arrays.asList("ttd", "ttd2")); + StorageTableDesc s3 = new StorageTableDesc(); + s3.setInputFormat(TextInputFormat.class.getCanonicalName()); + s3.setOutputFormat(HiveIgnoreKeyTextOutputFormat.class.getCanonicalName()); + s3.setPartCols(partCols); + s3.setTimePartCols(timePartCols); + s3.getTblProps().put(MetastoreUtil.getStoragetableStartTimesKey(), "now.day - 90 days"); + s3.getTblProps().put(MetastoreUtil.getStoragetableEndTimesKey(), "now.day - 10 days"); + + StorageTableDesc s5 = new StorageTableDesc(); + s5.setInputFormat(TextInputFormat.class.getCanonicalName()); + s5.setOutputFormat(HiveIgnoreKeyTextOutputFormat.class.getCanonicalName()); + s5.setPartCols(partCols); + s5.setTimePartCols(timePartCols); + s5.getTblProps().put(MetastoreUtil.getStoragetableStartTimesKey(), "now.day - 10 days"); + storageAggregatePeriods.put(c1, updates); storageAggregatePeriods.put(c2, updates); storageAggregatePeriods.put(c3, updates); storageAggregatePeriods.put(c4, updates); + storageAggregatePeriods.put(c5, updates); Map<String, StorageTableDesc> storageTables = new HashMap<String, StorageTableDesc>(); storageTables.put(c1, s1); storageTables.put(c4, s2); storageTables.put(c2, s1); - storageTables.put(c3, s1); + storageTables.put(c3, s3); + storageTables.put(c5, s5); //add storage with continuous update period updates.add(CONTINUOUS); @@ -1499,7 +1525,7 @@ 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 (UpdatePeriod period : Lists.newArrayList(MINUTELY, HOURLY, DAILY, MONTHLY, YEARLY, QUARTERLY)) { for (String partCol : Lists.newArrayList("dt")) { assertTimeline(client, fact.getName(), c1, period, partCol, EndsAndHolesPartitionTimeline.class); } @@ -1507,7 +1533,7 @@ public class CubeTestSetup { 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 (UpdatePeriod period : Lists.newArrayList(MINUTELY, HOURLY, DAILY, MONTHLY, YEARLY, QUARTERLY)) { for (String partCol : Lists.newArrayList("ttd", "ttd2")) { assertTimeline(client, fact.getName(), c4, period, partCol, EndsAndHolesPartitionTimeline.class); } @@ -2778,6 +2804,7 @@ public class CubeTestSetup { client.createStorage(new HDFSStorage(c2)); client.createStorage(new HDFSStorage(c3)); client.createStorage(new HDFSStorage(c4)); + client.createStorage(new HDFSStorage(c5)); client.createStorage(new HDFSStorage(c99)); createCube(client); createBaseAndDerivedCubes(client); http://git-wip-us.apache.org/repos/asf/lens/blob/1f12ce55/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 9053e21..87799eb 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 @@ -55,7 +55,6 @@ 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; @@ -390,255 +389,6 @@ public class TestCubeRewriter extends TestQueryRewrite { } @Test - public void testUnionQueries() throws Exception { - Configuration conf = getConf(); - 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"); - conf.set(CubeQueryConfUtil.DISABLE_AUTO_JOINS, "false"); - ArrayList<String> storages = Lists.newArrayList("c1_testfact", "c2_testfact"); - try { - getStorageToUpdatePeriodMap().put("c1_testfact", Lists.newArrayList(HOURLY, DAILY)); - getStorageToUpdatePeriodMap().put("c2_testfact", Lists.newArrayList(MONTHLY)); - - // Union query - 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 ascii(cityname) as `City Name`, msr8, msr7 as `Third measure` " - + "from testCube where ascii(cityname) = 'c' and cityname = 'a' and zipcode = 'b' and " - + TWO_MONTHS_RANGE_UPTO_HOURS, conf); - expected = getExpectedUnionQuery(TEST_CUBE_NAME, storages, provider, - "SELECT testcube.alias0 as `City Name`, 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 ascii(cubecity.name) 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`", " join " - + getDbName() + "c1_citytable cubecity on testcube.cityid = cubecity.id and (cubecity.dt = 'latest')", - "ascii(cubecity.name) = 'c' and cubecity.name = 'a' and testcube.zipcode = 'b'", - "group by ascii(cubecity.name))"); - compareQueries(hqlQuery, expected); - hqlQuery = rewrite("select asciicity as `City Name`, msr8, msr7 as `Third measure` " - + "from testCube where asciicity = 'c' and cityname = 'a' and zipcode = 'b' and " - + TWO_MONTHS_RANGE_UPTO_HOURS, conf); - compareQueries(hqlQuery, expected); - - hqlQuery = rewrite("select ascii(cityid) as `City ID`, msr8, msr7 as `Third measure` " - + "from testCube where ascii(cityid) = 'c' and cityid = 'a' and zipcode = 'b' and " - + TWO_MONTHS_RANGE_UPTO_HOURS, conf); - - expected = getExpectedUnionQuery(TEST_CUBE_NAME, 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 ascii(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`", - "ascii(testcube.cityid) = 'c' and testcube.cityid = 'a' and testcube.zipcode = 'b'", - "group by ascii(testcube.cityid)"); - - compareQueries(hqlQuery, expected); - - hqlQuery = rewrite("select cityid as `City ID`, msr8, msr7 as `Third measure` " - + "from testCube where cityid = 'a' and zipcode = 'b' and " + TWO_MONTHS_RANGE_UPTO_HOURS, conf); - - expected = getExpectedUnionQuery(TEST_CUBE_NAME, 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`", - "testcube.cityid = 'a' and testcube.zipcode = 'b'", "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(TEST_CUBE_NAME, 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(TEST_CUBE_NAME, 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(TEST_CUBE_NAME, 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(TEST_CUBE_NAME, 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(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(TEST_CUBE_NAME, 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 { - getStorageToUpdatePeriodMap().clear(); - } - - } - - @Test - public void testMultiFactMultiStorage() throws ParseException, LensException { - Configuration conf = LensServerAPITestUtil.getConfiguration( - CubeQueryConfUtil.ENABLE_STORAGES_UNION, true, - CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1,C2", - getValidStorageTablesKey("testfact"), "C1_testFact,C2_testFact", - getValidUpdatePeriodsKey("testfact", "C1"), "HOURLY", - getValidUpdatePeriodsKey("testfact", "C2"), "DAILY", - getValidUpdatePeriodsKey("testfact2_raw", "C1"), "YEARLY", - getValidUpdatePeriodsKey("testfact2_raw", "C2"), "YEARLY"); - CubeTestSetup.getStorageToUpdatePeriodMap().put("c1_testfact", Lists.newArrayList(HOURLY)); - CubeTestSetup.getStorageToUpdatePeriodMap().put("c2_testfact", Lists.newArrayList(DAILY)); - String whereCond = "zipcode = 'a' and cityid = 'b' and (" + TWO_DAYS_RANGE_SPLIT_OVER_UPDATE_PERIODS + ")"; - String hqlQuery = rewrite("select zipcode, count(msr4), sum(msr15) from testCube where " + whereCond, conf); - System.out.println(hqlQuery); - String possibleStart1 = "SELECT COALESCE(mq1.zipcode, mq2.zipcode) zipcode, mq1.expr2 `count(msr4)`," - + " mq2.expr3 `sum(msr15)` FROM "; - String possibleStart2 = "SELECT COALESCE(mq1.zipcode, mq2.zipcode) zipcode, mq2.expr2 `count(msr4)`," - + " mq1.expr3 `sum(msr15)` FROM "; - - assertTrue(hqlQuery.startsWith(possibleStart1) || hqlQuery.startsWith(possibleStart2)); - compareContains(rewrite("select zipcode as `zipcode`, sum(msr15) as `expr3` from testcube where " + whereCond, - conf), hqlQuery); - compareContains(rewrite("select zipcode as `zipcode`, count(msr4) as `expr2` from testcube where " + whereCond, - conf), hqlQuery); - assertTrue(hqlQuery.endsWith("on mq1.zipcode <=> mq2.zipcode")); - // No time_range_in should be remaining - assertFalse(hqlQuery.contains("time_range_in")); - //TODO: handle having after LENS-813, also handle for order by and limit - } - - @Test - public void testCubeWhereQueryWithMultipleTables() throws Exception { - Configuration conf = getConf(); - 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"); - - getStorageToUpdatePeriodMap().put("c1_testfact", Lists.newArrayList(DAILY)); - getStorageToUpdatePeriodMap().put("c2_testfact", Lists.newArrayList(HOURLY)); - StoragePartitionProvider provider = new StoragePartitionProvider() { - @Override - public Map<String, String> providePartitionsForStorage(String storage) { - return getWhereForDailyAndHourly2days(TEST_CUBE_NAME, storage); - } - }; - try { - // Union query - String hqlQuery = rewrite("select SUM(msr2) from testCube" + " where " + TWO_DAYS_RANGE, conf); - System.out.println("HQL:" + hqlQuery); - - String expected = getExpectedUnionQuery(TEST_CUBE_NAME, - 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 { - getStorageToUpdatePeriodMap().clear(); - } - } - - @Test - public void testCubeWhereQueryWithMultipleTablesForMonth() throws Exception { - Configuration conf = getConf(); - conf.set(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"); - - getStorageToUpdatePeriodMap().put("c1_testfact", Lists.newArrayList(HOURLY)); - getStorageToUpdatePeriodMap().put("c2_testfact", Lists.newArrayList(DAILY)); - 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); - ArrayList<String> storages = Lists.newArrayList("c1_testfact", "c2_testfact", "c3_testfact"); - String expected = getExpectedUnionQuery(TEST_CUBE_NAME, storages, provider, - "select sum(testcube.alias0)", null, null, - "select sum(testcube.msr2) as `alias0` from ", null, null - ); - compareQueries(hqlQuery, expected); - } finally { - getStorageToUpdatePeriodMap().clear(); - } - } - - @Test public void testPartColAsQueryColumn() throws Exception { Configuration conf = getConf(); conf.setBoolean(CubeQueryConfUtil.FAIL_QUERY_ON_PARTIAL_DATA, false); @@ -810,53 +560,6 @@ public class TestCubeRewriter extends TestQueryRewrite { } @Test - public void testConvertDimFilterToFactFilterForMultiFact() throws Exception { - Configuration conf = getConf(); - 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"); - conf.setBoolean(DISABLE_AUTO_JOINS, false); - conf.setBoolean(REWRITE_DIM_FILTER_TO_FACT_FILTER, true); - ArrayList<String> storages = Lists.newArrayList("c1_testfact", "c2_testfact"); - try { - getStorageToUpdatePeriodMap().put("c1_testfact", Lists.newArrayList(HOURLY, DAILY)); - getStorageToUpdatePeriodMap().put("c2_testfact", Lists.newArrayList(MONTHLY)); - - // Union query - String hqlQuery; - 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 asciicity as `City Name`, msr8, msr7 as `Third measure` " - + "from testCube where asciicity = 'c' and cityname = 'a' and zipcode = 'b' and " - + TWO_MONTHS_RANGE_UPTO_HOURS, conf); - - String filter1 = "testcube.cityid in ( select id from TestQueryRewrite.c1_citytable cubecity " - + "where (ascii((cubecity.name)) = 'c') and (cubecity.dt = 'latest') )"; - String filter2 = "testcube.cityid in ( select id from TestQueryRewrite.c1_citytable cubecity " - + "where ((cubecity.name) = 'a') and (cubecity.dt = 'latest') )"; - - assertTrue(hqlQuery.contains(filter1)); - assertTrue(hqlQuery.contains(filter2)); - - } finally { - getStorageToUpdatePeriodMap().clear(); - } - } - @Test public void testCubeGroupbyWithConstantProjected() throws Exception { // check constants Configuration conf = getConf(); @@ -1210,7 +913,7 @@ public class TestCubeRewriter extends TestQueryRewrite { assertEquals( pruneCauses.getBrief().substring(0, MISSING_PARTITIONS.errorFormat.length() - 3), MISSING_PARTITIONS.errorFormat.substring(0, - MISSING_PARTITIONS.errorFormat.length() - 3)); + MISSING_PARTITIONS.errorFormat.length() - 3), pruneCauses.getBrief()); Set<String> expectedSet = Sets.newTreeSet(Arrays.asList("summary1", "summary2", "testfact2_raw", "summary3", "testfact")); @@ -1223,7 +926,8 @@ public class TestCubeRewriter extends TestQueryRewrite { missingPartitionCause = true; } } - assertTrue(missingPartitionCause, MISSING_PARTITIONS + " error does not occur for facttables set " + expectedSet); + assertTrue(missingPartitionCause, MISSING_PARTITIONS + " error does not occur for facttables set " + expectedSet + + " Details :" + pruneCauses.getDetails()); assertEquals(pruneCauses.getDetails().get("testfactmonthly").iterator().next().getCause(), NO_FACT_UPDATE_PERIODS_FOR_GIVEN_RANGE); assertEquals(pruneCauses.getDetails().get("testfact2").iterator().next().getCause(), http://git-wip-us.apache.org/repos/asf/lens/blob/1f12ce55/lens-cube/src/test/java/org/apache/lens/cube/parse/TestUnionQueries.java ---------------------------------------------------------------------- diff --git a/lens-cube/src/test/java/org/apache/lens/cube/parse/TestUnionQueries.java b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestUnionQueries.java new file mode 100644 index 0000000..5eea123 --- /dev/null +++ b/lens-cube/src/test/java/org/apache/lens/cube/parse/TestUnionQueries.java @@ -0,0 +1,374 @@ +/** + * 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.*; +import static org.apache.lens.cube.metadata.UpdatePeriod.*; +import static org.apache.lens.cube.parse.CubeQueryConfUtil.*; +import static org.apache.lens.cube.parse.CubeTestSetup.*; +import static org.apache.lens.cube.parse.TestCubeRewriter.*; + +import static org.testng.Assert.*; + +import java.util.*; + +import org.apache.lens.cube.error.LensCubeErrorCode; +import org.apache.lens.server.api.LensServerAPITestUtil; +import org.apache.lens.server.api.error.LensException; + +import org.apache.hadoop.conf.Configuration; +import org.apache.hadoop.hive.ql.parse.ParseException; + +import org.testng.annotations.BeforeTest; +import org.testng.annotations.Test; + +import com.google.common.collect.Lists; +import lombok.extern.slf4j.Slf4j; + +@Slf4j +public class TestUnionQueries extends TestQueryRewrite { + + private Configuration testConf; + + @BeforeTest + public void setupDriver() throws Exception { + testConf = LensServerAPITestUtil.getConfiguration( + DRIVER_SUPPORTED_STORAGES, "C0,C1,C2", + DISABLE_AUTO_JOINS, false, + ENABLE_SELECT_TO_GROUPBY, true, + ENABLE_GROUP_BY_TO_SELECT, true, + DISABLE_AGGREGATE_RESOLVER, false, + ENABLE_STORAGES_UNION, true); + } + + @Override + public Configuration getConf() { + return new Configuration(testConf); + } + + @Test + public void testUnionQueries() throws Exception { + Configuration conf = getConf(); + 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"); + conf.setBoolean(CubeQueryConfUtil.ENABLE_STORAGES_UNION, false); + ArrayList<String> storages = Lists.newArrayList("c1_testfact", "c2_testfact"); + try { + getStorageToUpdatePeriodMap().put("c1_testfact", Lists.newArrayList(HOURLY, DAILY)); + getStorageToUpdatePeriodMap().put("c2_testfact", Lists.newArrayList(MONTHLY)); + + // Union query + 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 ascii(cityname) as `City Name`, msr8, msr7 as `Third measure` " + + "from testCube where ascii(cityname) = 'c' and cityname = 'a' and zipcode = 'b' and " + + TWO_MONTHS_RANGE_UPTO_HOURS, conf); + expected = getExpectedUnionQuery(TEST_CUBE_NAME, storages, provider, + "SELECT testcube.alias0 as `City Name`, 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 ascii(cubecity.name) 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`", " join " + + getDbName() + "c1_citytable cubecity on testcube.cityid = cubecity.id and (cubecity.dt = 'latest')", + "ascii(cubecity.name) = 'c' and cubecity.name = 'a' and testcube.zipcode = 'b'", + "group by ascii(cubecity.name))"); + compareQueries(hqlQuery, expected); + hqlQuery = rewrite("select asciicity as `City Name`, msr8, msr7 as `Third measure` " + + "from testCube where asciicity = 'c' and cityname = 'a' and zipcode = 'b' and " + + TWO_MONTHS_RANGE_UPTO_HOURS, conf); + compareQueries(hqlQuery, expected); + + hqlQuery = rewrite("select ascii(cityid) as `City ID`, msr8, msr7 as `Third measure` " + + "from testCube where ascii(cityid) = 'c' and cityid = 'a' and zipcode = 'b' and " + + TWO_MONTHS_RANGE_UPTO_HOURS, conf); + + expected = getExpectedUnionQuery(TEST_CUBE_NAME, 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 ascii(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`", + "ascii(testcube.cityid) = 'c' and testcube.cityid = 'a' and testcube.zipcode = 'b'", + "group by ascii(testcube.cityid)"); + + compareQueries(hqlQuery, expected); + + hqlQuery = rewrite("select cityid as `City ID`, msr8, msr7 as `Third measure` " + + "from testCube where cityid = 'a' and zipcode = 'b' and " + TWO_MONTHS_RANGE_UPTO_HOURS, conf); + + expected = getExpectedUnionQuery(TEST_CUBE_NAME, 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`", + "testcube.cityid = 'a' and testcube.zipcode = 'b'", "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(TEST_CUBE_NAME, 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(TEST_CUBE_NAME, 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(TEST_CUBE_NAME, 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(TEST_CUBE_NAME, 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(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(TEST_CUBE_NAME, 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 { + getStorageToUpdatePeriodMap().clear(); + } + + } + + @Test + public void testMultiFactMultiStorage() throws ParseException, LensException { + Configuration conf = LensServerAPITestUtil.getConfigurationWithParams(getConf(), + CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C1,C2", + getValidStorageTablesKey("testfact"), "C1_testFact,C2_testFact", + getValidUpdatePeriodsKey("testfact", "C1"), "HOURLY", + getValidUpdatePeriodsKey("testfact", "C2"), "DAILY", + getValidUpdatePeriodsKey("testfact2_raw", "C1"), "YEARLY", + getValidUpdatePeriodsKey("testfact2_raw", "C2"), "YEARLY"); + CubeTestSetup.getStorageToUpdatePeriodMap().put("c1_testfact", Lists.newArrayList(HOURLY)); + CubeTestSetup.getStorageToUpdatePeriodMap().put("c2_testfact", Lists.newArrayList(DAILY)); + String whereCond = "zipcode = 'a' and cityid = 'b' and (" + TWO_DAYS_RANGE_SPLIT_OVER_UPDATE_PERIODS + ")"; + String hqlQuery = rewrite("select zipcode, count(msr4), sum(msr15) from testCube where " + whereCond, conf); + System.out.println(hqlQuery); + String possibleStart1 = "SELECT COALESCE(mq1.zipcode, mq2.zipcode) zipcode, mq1.expr2 `count(msr4)`," + + " mq2.expr3 `sum(msr15)` FROM "; + String possibleStart2 = "SELECT COALESCE(mq1.zipcode, mq2.zipcode) zipcode, mq2.expr2 `count(msr4)`," + + " mq1.expr3 `sum(msr15)` FROM "; + + assertTrue(hqlQuery.startsWith(possibleStart1) || hqlQuery.startsWith(possibleStart2)); + compareContains(rewrite("select zipcode as `zipcode`, sum(msr15) as `expr3` from testcube where " + whereCond, + conf), hqlQuery); + compareContains(rewrite("select zipcode as `zipcode`, count(msr4) as `expr2` from testcube where " + whereCond, + conf), hqlQuery); + assertTrue(hqlQuery.endsWith("on mq1.zipcode <=> mq2.zipcode")); + // No time_range_in should be remaining + assertFalse(hqlQuery.contains("time_range_in")); + //TODO: handle having after LENS-813, also handle for order by and limit + } + + @Test + public void testCubeWhereQueryWithMultipleTables() throws Exception { + Configuration conf = getConf(); + 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"); + + getStorageToUpdatePeriodMap().put("c1_testfact", Lists.newArrayList(DAILY)); + getStorageToUpdatePeriodMap().put("c2_testfact", Lists.newArrayList(HOURLY)); + StoragePartitionProvider provider = new StoragePartitionProvider() { + @Override + public Map<String, String> providePartitionsForStorage(String storage) { + return getWhereForDailyAndHourly2days(TEST_CUBE_NAME, storage); + } + }; + try { + // Union query + String hqlQuery = rewrite("select SUM(msr2) from testCube" + " where " + TWO_DAYS_RANGE, conf); + System.out.println("HQL:" + hqlQuery); + + String expected = getExpectedUnionQuery(TEST_CUBE_NAME, + 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 { + getStorageToUpdatePeriodMap().clear(); + } + } + + @Test + public void testCubeWhereQueryWithMultipleTablesForMonth() throws Exception { + Configuration conf = getConf(); + conf.set(DRIVER_SUPPORTED_STORAGES, "C1,C2,C3"); + 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"); + + getStorageToUpdatePeriodMap().put("c1_testfact", Lists.newArrayList(HOURLY)); + getStorageToUpdatePeriodMap().put("c2_testfact", Lists.newArrayList(DAILY)); + 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); + ArrayList<String> storages = Lists.newArrayList("c1_testfact", "c2_testfact", "c3_testfact"); + String expected = getExpectedUnionQuery(TEST_CUBE_NAME, storages, provider, + "select sum(testcube.alias0)", null, null, + "select sum(testcube.msr2) as `alias0` from ", null, null + ); + compareQueries(hqlQuery, expected); + } finally { + getStorageToUpdatePeriodMap().clear(); + } + } + + @Test + public void testConvertDimFilterToFactFilterForMultiFact() throws Exception { + Configuration conf = getConf(); + 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"); + conf.setBoolean(REWRITE_DIM_FILTER_TO_FACT_FILTER, true); + try { + getStorageToUpdatePeriodMap().put("c1_testfact", Lists.newArrayList(HOURLY, DAILY)); + getStorageToUpdatePeriodMap().put("c2_testfact", Lists.newArrayList(MONTHLY)); + + String hqlQuery = rewrite("select asciicity as `City Name`, msr8, msr7 as `Third measure` " + + "from testCube where asciicity = 'c' and cityname = 'a' and zipcode = 'b' and " + + TWO_MONTHS_RANGE_UPTO_HOURS, conf); + + String filter1 = "testcube.cityid in ( select id from TestQueryRewrite.c1_citytable cubecity " + + "where (ascii((cubecity.name)) = 'c') and (cubecity.dt = 'latest') )"; + String filter2 = "testcube.cityid in ( select id from TestQueryRewrite.c1_citytable cubecity " + + "where ((cubecity.name) = 'a') and (cubecity.dt = 'latest') )"; + + assertTrue(hqlQuery.contains(filter1)); + assertTrue(hqlQuery.contains(filter2)); + + } finally { + getStorageToUpdatePeriodMap().clear(); + } + } + + @Test + public void testSingleFactMultiStorage() throws Exception { + Configuration conf = LensServerAPITestUtil.getConfigurationWithParams(getConf(), + CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C3,C5", + getValidFactTablesKey("testcube"), "testfact", + getValidUpdatePeriodsKey("testfact", "C3"), "DAILY", + getValidUpdatePeriodsKey("testfact", "C5"), "DAILY", + FAIL_QUERY_ON_PARTIAL_DATA, false); + + String hqlQuery = rewrite("select count(msr4) from testCube where " + TWO_MONTHS_RANGE_UPTO_DAYS, conf); + System.out.println(hqlQuery); + + // No time_range_in should be remaining + assertFalse(hqlQuery.contains("time_range_in")); + ArrayList<String> storages = Lists.newArrayList("c3_testfact", "c5_testfact"); + StoragePartitionProvider provider = new StoragePartitionProvider() { + @Override + public Map<String, String> providePartitionsForStorage(String storage) { + if (storage.contains("c3")) { + return getWhereForDays(storage, TWO_MONTHS_BACK, getDateWithOffset(DAILY, -10)); + } else if (storage.contains("c5")) { + return getWhereForDays(storage, getDateWithOffset(DAILY, -10), NOW); + } + return null; + } + }; + String expected = getExpectedUnionQuery(TEST_CUBE_NAME, storages, provider, + "select count(testcube.alias0)", null, null, + "select count(testcube.msr4) as `alias0` from ", null, null + ); + compareQueries(hqlQuery, expected); + } +} http://git-wip-us.apache.org/repos/asf/lens/blob/1f12ce55/lens-server-api/src/test/java/org/apache/lens/server/api/LensServerAPITestUtil.java ---------------------------------------------------------------------- diff --git a/lens-server-api/src/test/java/org/apache/lens/server/api/LensServerAPITestUtil.java b/lens-server-api/src/test/java/org/apache/lens/server/api/LensServerAPITestUtil.java index 84139a4..1a92888 100644 --- a/lens-server-api/src/test/java/org/apache/lens/server/api/LensServerAPITestUtil.java +++ b/lens-server-api/src/test/java/org/apache/lens/server/api/LensServerAPITestUtil.java @@ -29,13 +29,16 @@ public class LensServerAPITestUtil { public static Configuration getConfiguration(Object... args) { Configuration conf = new Configuration(false); + return getConfigurationWithParams(conf, args); + } + + public static Configuration getConfigurationWithParams(Configuration conf, Object... args) { assert (args.length % 2 == 0); for (int i = 0; i < args.length; i += 2) { conf.set(args[i].toString(), args[i + 1].toString()); } return conf; } - public static LensConf getLensConf(Object... args) { assert (args.length % 2 == 0); LensConf conf = new LensConf();
