@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);
}