@Test public void testCubeQueryContinuousUpdatePeriod() throws Exception { LensException th = null; try { rewrite("cube select" + " SUM(msr2) from testCube where " + TWO_DAYS_RANGE, conf); } catch (LensException e) { th = e; log.error("Semantic exception while testing cube query.", e); } if (!CubeTestSetup.isZerothHour()) { Assert.assertNotNull(th); Assert.assertEquals( th.getErrorCode(), LensCubeErrorCode.CANNOT_USE_TIMERANGE_WRITER.getValue()); } // hourly partitions for two days conf.setBoolean(CubeQueryConfUtil.FAIL_QUERY_ON_PARTIAL_DATA, true); DateFormat qFmt = new SimpleDateFormat("yyyy-MM-dd-HH:mm:ss"); Calendar qCal = Calendar.getInstance(); Date toDate = qCal.getTime(); String qTo = qFmt.format(toDate); qCal.setTime(TWODAYS_BACK); Date from2DaysBackDate = qCal.getTime(); String qFrom = qFmt.format(from2DaysBackDate); String twoDaysInRangeClause = " time_range_in(d_time, '" + qFrom + "', '" + qTo + "')"; String hqlQuery = rewrite("select SUM(msr2) from testCube" + " where " + twoDaysInRangeClause, conf); Map<String, String> whereClauses = new HashMap<String, String>(); whereClauses.put( CubeTestSetup.getDbName() + "c1_testfact", TestBetweenTimeRangeWriter.getBetweenClause( cubeName, "dt", from2DaysBackDate, toDate, UpdatePeriod.CONTINUOUS.format())); String expected = getExpectedQuery(cubeName, "select sum(testcube.msr2) FROM ", null, null, whereClauses); System.out.println("HQL:" + hqlQuery); TestCubeRewriter.compareQueries(hqlQuery, expected); // multiple range query // from date 4 days back qCal.setTime(BEFORE_4_DAYS_START); Date from4DaysBackDate = qCal.getTime(); String qFrom4Days = qFmt.format(from4DaysBackDate); String fourDaysInRangeClause = " time_range_in(d_time, '" + qFrom4Days + "', '" + qTo + "')"; hqlQuery = rewrite( "select SUM(msr2) from testCube" + " where " + twoDaysInRangeClause + " OR " + fourDaysInRangeClause, conf); whereClauses = new HashMap<String, String>(); whereClauses.put( CubeTestSetup.getDbName() + "c1_testfact", TestBetweenTimeRangeWriter.getBetweenClause( cubeName, "dt", from2DaysBackDate, toDate, UpdatePeriod.CONTINUOUS.format()) + " OR" + TestBetweenTimeRangeWriter.getBetweenClause( cubeName, "dt", from4DaysBackDate, toDate, UpdatePeriod.CONTINUOUS.format())); expected = getExpectedQuery(cubeName, "select sum(testcube.msr2) FROM ", null, null, whereClauses); System.out.println("HQL:" + hqlQuery); TestCubeRewriter.compareQueries(hqlQuery, expected); // format option in the query conf.set(CubeQueryConfUtil.PART_WHERE_CLAUSE_DATE_FORMAT, "yyyy-MM-dd HH:mm:ss"); hqlQuery = rewrite("select SUM(msr2) from testCube" + " where " + TWO_DAYS_RANGE, conf); whereClauses = new HashMap<String, String>(); whereClauses.put( CubeTestSetup.getDbName() + "c1_testfact", TestBetweenTimeRangeWriter.getBetweenClause( cubeName, "dt", getUptoHour(CubeTestSetup.TWODAYS_BACK), getUptoHour(CubeTestSetup.NOW), TestTimeRangeWriter.DB_FORMAT)); expected = getExpectedQuery(cubeName, "select sum(testcube.msr2) FROM ", null, null, whereClauses); System.out.println("HQL:" + hqlQuery); TestCubeRewriter.compareQueries(hqlQuery, expected); }
@Test public void testCubeQueryWithTimeDimThruChain() throws Exception { // hourly partitions for two days Configuration tconf = new Configuration(conf); tconf.setBoolean(CubeQueryConfUtil.FAIL_QUERY_ON_PARTIAL_DATA, true); tconf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C4"); tconf.setBoolean(CubeQueryConfUtil.REPLACE_TIMEDIM_WITH_PART_COL, false); tconf.set(CubeQueryConfUtil.PART_WHERE_CLAUSE_DATE_FORMAT, "yyyy-MM-dd HH:mm:ss"); tconf.set(CubeQueryConfUtil.getValidUpdatePeriodsKey("testfact", "C4"), "MONTHLY,DAILY,HOURLY"); String query = "SELECT test_time_dim2, msr2 FROM testCube where " + "time_range_in(test_time_dim2, '" + CubeTestSetup.getDateUptoHours(TWODAYS_BACK) + "','" + CubeTestSetup.getDateUptoHours(NOW) + "')"; String hqlQuery = rewrite(query, tconf); Map<String, String> whereClauses = new HashMap<String, String>(); whereClauses.put( CubeTestSetup.getDbName() + "c4_testfact2", TestBetweenTimeRangeWriter.getBetweenClause( "timehourchain", "full_hour", getUptoHour(CubeTestSetup.TWODAYS_BACK), getUptoHour(getOneLess(CubeTestSetup.NOW, UpdatePeriod.HOURLY.calendarField())), TestTimeRangeWriter.DB_FORMAT)); System.out.println("HQL:" + hqlQuery); String expected = getExpectedQuery( cubeName, "select timehourchain.full_hour, sum(testcube.msr2) FROM ", " join " + getDbName() + "c4_hourDimTbl timehourchain on testcube.test_time_dim_hour_id2 = timehourchain.id", null, " GROUP BY timehourchain.full_hour", null, whereClauses); TestCubeRewriter.compareQueries(hqlQuery, expected); query = "SELECT msr2 FROM testCube where " + "time_range_in(test_time_dim2, '" + CubeTestSetup.getDateUptoHours(TWODAYS_BACK) + "','" + CubeTestSetup.getDateUptoHours(NOW) + "')"; hqlQuery = rewrite(query, tconf); System.out.println("HQL:" + hqlQuery); expected = getExpectedQuery( cubeName, "select sum(testcube.msr2) FROM ", " join " + getDbName() + "c4_hourDimTbl timehourchain on testcube.test_time_dim_hour_id2 = timehourchain.id", null, null, null, whereClauses); TestCubeRewriter.compareQueries(hqlQuery, expected); query = "SELECT msr2 FROM testCube where testcube.cityid > 2 and " + "time_range_in(test_time_dim2, '" + CubeTestSetup.getDateUptoHours(TWODAYS_BACK) + "','" + CubeTestSetup.getDateUptoHours(NOW) + "') and testcube.cityid != 5"; hqlQuery = rewrite(query, tconf); System.out.println("HQL:" + hqlQuery); expected = getExpectedQuery( cubeName, "select sum(testcube.msr2) FROM ", " join " + getDbName() + "c4_hourDimTbl timehourchain on testcube.test_time_dim_hour_id2 = timehourchain.id", " testcube.cityid > 2 ", " and testcube.cityid != 5", null, whereClauses); TestCubeRewriter.compareQueries(hqlQuery, expected); // multiple range query hqlQuery = rewrite( "select SUM(msr2) from testCube" + " where time_range_in(test_time_dim2, '" + CubeTestSetup.getDateUptoHours(TWODAYS_BACK) + "','" + CubeTestSetup.getDateUptoHours(NOW) + "')" + " OR time_range_in(test_time_dim2, '" + CubeTestSetup.getDateUptoHours(BEFORE_4_DAYS_START) + "','" + CubeTestSetup.getDateUptoHours(BEFORE_4_DAYS_END) + "')", tconf); whereClauses = new HashMap<String, String>(); whereClauses.put( CubeTestSetup.getDbName() + "c4_testfact2", TestBetweenTimeRangeWriter.getBetweenClause( "timehourchain", "full_hour", getUptoHour(CubeTestSetup.TWODAYS_BACK), getUptoHour(getOneLess(CubeTestSetup.NOW, UpdatePeriod.HOURLY.calendarField())), TestTimeRangeWriter.DB_FORMAT) + " OR " + TestBetweenTimeRangeWriter.getBetweenClause( "timehourchain", "full_hour", getUptoHour(BEFORE_4_DAYS_START), getUptoHour(getOneLess(BEFORE_4_DAYS_END, UpdatePeriod.HOURLY.calendarField())), TestTimeRangeWriter.DB_FORMAT)); expected = getExpectedQuery( cubeName, "select sum(testcube.msr2) FROM ", " join " + getDbName() + "c4_hourDimTbl timehourchain on testcube.test_time_dim_hour_id2 = timehourchain.id", null, null, null, whereClauses); System.out.println("HQL:" + hqlQuery); TestCubeRewriter.compareQueries(hqlQuery, expected); hqlQuery = rewrite( "select to_date(test_time_dim2), SUM(msr2) from testCube" + " where time_range_in(test_time_dim2, '" + CubeTestSetup.getDateUptoHours(TWODAYS_BACK) + "','" + CubeTestSetup.getDateUptoHours(NOW) + "')" + " OR time_range_in(test_time_dim2, '" + CubeTestSetup.getDateUptoHours(BEFORE_4_DAYS_START) + "','" + CubeTestSetup.getDateUptoHours(BEFORE_4_DAYS_END) + "')", tconf); expected = getExpectedQuery( cubeName, "select to_date(timehourchain.full_hour), sum(testcube.msr2) FROM ", " join " + getDbName() + "c4_hourDimTbl timehourchain on testcube.test_time_dim_hour_id2 = timehourchain.id", null, " group by to_date(timehourchain.full_hour)", null, whereClauses); System.out.println("HQL:" + hqlQuery); TestCubeRewriter.compareQueries(hqlQuery, expected); }