@Test
public void testMaterializedExpressionPickingMaterializedValue() throws Exception {
Configuration newConf = new Configuration(conf);
newConf.set(CubeQueryConfUtil.DRIVER_SUPPORTED_STORAGES, "C2");
newConf.set(CubeQueryConfUtil.getValidFactTablesKey(cubeName), "testFact");
String hqlQuery = rewrite("select msr5 from testCube where " + TWO_DAYS_RANGE, newConf);
String expected =
getExpectedQuery(
cubeName,
"select testcube.msr5 FROM ",
null,
null,
getWhereForDailyAndHourly2days(cubeName, "C2_testfact"));
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);
}