@Test
public void testDimensionQueryWithTableAliasColumnAlias() throws Exception {
String hqlQuery =
rewrite("select ct.name cname, ct.cityaddress caddr from" + " citydim ct", conf);
String joinExpr =
""
+ " join "
+ getDbName()
+ "c1_statetable citystate on ct.stateid = citystate.id and (citystate.dt = 'latest')"
+ " join "
+ getDbName()
+ "c1_countrytable citycountry on citystate.countryid = citycountry.id"
+ " join "
+ getDbName()
+ "c1_ziptable cityzip on ct.zipcode = cityzip.code and (cityzip.dt = 'latest')";
String expected =
getExpectedQuery(
"ct",
"SELECT ct.name as `cname`, concat((ct.name), \":\", (citystate.name ),"
+ " \":\",(citycountry.name), \":\" , ( cityzip . code )) as `caddr` FROM ",
joinExpr,
null,
null,
"c1_citytable",
true);
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testDimensionQueryWithExpression() throws Exception {
String hqlQuery = rewrite("select citydim.name, cityaddress from" + " citydim", conf);
String joinExpr;
String join1 =
" join "
+ getDbName()
+ "c1_ziptable cityzip on"
+ " citydim.zipcode = cityzip.code and (cityzip.dt = 'latest')";
String join2 =
" join "
+ getDbName()
+ "c1_statetable citystate on"
+ " citydim.stateid = citystate.id and (citystate.dt = 'latest')";
String join3 =
" join "
+ getDbName()
+ "c1_countrytable citycountry on"
+ " citystate.countryid = citycountry.id";
joinExpr = join2 + join3 + join1;
String expected =
getExpectedQuery(
"citydim",
"SELECT citydim.name, concat((citydim.name), \":\", (citystate.name ),"
+ " \":\",(citycountry.name), \":\" , ( cityzip . code )) FROM ",
joinExpr,
null,
null,
"c1_citytable",
true);
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExpressionToJoin() throws Exception {
// expression which results in join
String hqlQuery =
rewrite(
"select cityAndState, avgmsr from testCube"
+ " where "
+ TWO_DAYS_RANGE
+ " and substrexpr != 'XYZ'",
conf);
String join1 =
" join "
+ getDbName()
+ "c1_citytable cubecity"
+ " on testcube.cityid = cubecity.id and (cubecity.dt = 'latest') ";
String join2 =
" join"
+ getDbName()
+ "c1_statetable cubestate on"
+ " testcube.stateid = cubestate.id and (cubestate.dt = 'latest')";
String expected =
getExpectedQuery(
cubeName,
"select concat(cubecity.name, \":\", cubestate.name),"
+ " avg(testcube.msr1 + testcube.msr2) FROM ",
join2 + join1,
null,
" and substr(testcube.dim1, 3) != 'XYZ'"
+ " group by concat(cubecity.name, \":\", cubestate.name)",
null,
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testMultipleExpressionsPickingSecondExpression() throws Exception {
String hqlQuery = rewrite("select equalsums from testCube where " + TWO_DAYS_RANGE, conf);
String expected =
getExpectedQuery(
cubeName,
"select (max(testCube.msr3) + sum(testCube.msr2))/100 FROM ",
null,
null,
getWhereForHourly2days(cubeName, "C1_testfact2"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExprDimAttribute() throws Exception {
// select with expression
String hqlQuery = rewrite("select substrexpr from testCube where " + TWO_DAYS_RANGE, conf);
String expected =
getExpectedQuery(
cubeName,
"select distinct substr(testCube.dim1, 3) FROM ",
null,
null,
getWhereForDailyAndHourly2days(cubeName, "c1_summary1"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testCubeQueryExpressionSelection() throws Exception {
// select with expression
String hqlQuery = rewrite("cube select avgmsr from testCube where " + TWO_DAYS_RANGE, conf);
String expected =
getExpectedQuery(
cubeName,
"select avg(testCube.msr1 + testCube.msr2) FROM ",
null,
null,
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testCubeQueryExpressionSelectionAlongWithColumn() throws Exception {
String hqlQuery =
rewrite("select dim1, roundedmsr2 from testCube" + " where " + TWO_DAYS_RANGE, conf);
String expected =
getExpectedQuery(
cubeName,
"select testcube.dim1, round(sum(testcube.msr2)/1000) FROM ",
null,
" group by testcube.dim1",
getWhereForDailyAndHourly2days(cubeName, "c1_summary1"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testMaterializedExpressionPickingExpression() throws Exception {
// select with expression
String hqlQuery = rewrite("select msr5 from testCube where " + TWO_DAYS_RANGE, conf);
String expected =
getExpectedQuery(
cubeName,
"select sum(testCube.msr2) + max(testCube.msr3) FROM ",
null,
null,
getWhereForHourly2days(cubeName, "C1_testfact2"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testDimensionQueryExpressionInSelectToGroupby() throws Exception {
String hqlQuery = rewrite("select id, AggrExpr from citydim", conf);
String expected =
getExpectedQuery(
"citydim",
"select citydim.id, count(citydim.name) FROM ",
null,
null,
" group by citydim.id",
"c1_citytable",
true);
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testNestedExpressionInWhere() throws Exception {
String hqlQuery =
rewrite(
"select avgmsr from testCube" + " where " + TWO_DAYS_RANGE + " and indiasubstr = true",
conf);
String expected =
getExpectedQuery(
cubeName,
"select avg(testCube.msr1 + testCube.msr2) FROM ",
null,
" and (substr(testCube.dim1, 3) = 'INDIA') = true",
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@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 testExpressionInWhereBeforeTimerange() throws Exception {
String hqlQuery =
rewrite(
"select SUM(msr2) from testCube" + " where substrexpr != 'XYZ' and " + TWO_DAYS_RANGE,
conf);
String expected =
getExpectedQuery(
cubeName,
"select sum(testcube.msr2) FROM ",
"substr(testCube.dim1, 3) != 'XYZ'",
null,
getWhereForDailyAndHourly2days(cubeName, "c1_summary1"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExpressionInSelectAndWhere() throws Exception {
// expression in select and where
String hqlQuery =
rewrite(
"select avgmsr from testCube" + " where " + TWO_DAYS_RANGE + " and substrexpr != 'XYZ'",
conf);
String expected =
getExpectedQuery(
cubeName,
"select avg(testCube.msr1 + testCube.msr2) FROM ",
null,
" and substr(testCube.dim1, 3) != 'XYZ'",
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testCubeExpressionWithColumnAlias() throws Exception {
// expression with column alias
String hqlQuery =
rewrite(
"select TC.substrexpr as subdim1, TC.avgmsr from testCube TC"
+ " where "
+ TWO_DAYS_RANGE
+ " and subdim1 != 'XYZ'",
conf);
String expected =
getExpectedQuery(
"tc",
"select substr(tc.dim1, 3) as `subdim1`, avg(tc.msr1 + tc.msr2) FROM ",
null,
" and subdim1 != 'XYZ' group by substr(tc.dim1, 3)",
getWhereForHourly2days("tc", "C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExpressionInSelectToGroupbyWithComplexExpression() throws Exception {
String hqlQuery =
rewrite(
"select booleancut, summsrs from testCube"
+ " where "
+ TWO_DAYS_RANGE
+ " and substrexpr != 'XYZ'",
conf);
String expected =
getExpectedQuery(
cubeName,
"select testCube.dim1 != 'x' AND testCube.dim2 != 10 ,"
+ " ((1000 + sum(testCube.msr1) + sum(testCube.msr2))/100) FROM ",
null,
" and substr(testCube.dim1, 3) != 'XYZ' group by testCube.dim1 != 'x' AND testCube.dim2 != 10",
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExpressionInGroupbyToSelect() throws Exception {
// expression with groupby
String hqlQuery =
rewrite(
"select avgmsr from testCube"
+ " where "
+ TWO_DAYS_RANGE
+ " and substrexpr != 'XYZ' group by booleancut",
conf);
String expected =
getExpectedQuery(
cubeName,
"select testCube.dim1 != 'x' AND testCube.dim2 != 10 ,"
+ " avg(testCube.msr1 + testCube.msr2) FROM ",
null,
" and substr(testCube.dim1, 3) != 'XYZ'"
+ " group by testCube.dim1 != 'x' AND testCube.dim2 != 10",
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExpressionInHaving() throws Exception {
// expression with having clause
String hqlQuery =
rewrite(
"cube select booleancut, avgmsr from testCube"
+ " where "
+ TWO_DAYS_RANGE
+ " and substrexpr != 'XYZ'"
+ " having msr6 > 100.0",
conf);
String expected =
getExpectedQuery(
cubeName,
"select testCube.dim1 != 'x' AND testCube.dim2 != 10 ,"
+ " avg(testCube.msr1 + testCube.msr2) FROM ",
null,
" and substr(testCube.dim1, 3) != 'XYZ' "
+ " group by testCube.dim1 != 'x' AND testCube.dim2 != 10"
+ " having (sum(testCube.msr2) + max(testCube.msr3))/ count(testcube.msr4) > 100.0",
getWhereForHourly2days("C1_testfact2_raw"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@Test
public void testExpressionWithAliasInOrderby() throws Exception {
String hqlQuery =
rewrite(
"cube select booleancut bc, msr2 from testCube"
+ " where "
+ TWO_DAYS_RANGE
+ " and substrexpr != 'XYZ'"
+ " having msr6 > 100.0 order by bc",
conf);
String expected =
getExpectedQuery(
cubeName,
"select testCube.dim1 != 'x' AND testCube.dim2 != 10 as `bc`,"
+ " sum(testCube.msr2) FROM ",
null,
" and substr(testCube.dim1, 3) != 'XYZ' "
+ " group by testCube.dim1 != 'x' AND testCube.dim2 != 10"
+ " having (sum(testCube.msr2) + max(testCube.msr3))/ count(testcube.msr4) > 100.0"
+ " order by bc asc",
getWhereForDailyAndHourly2days(cubeName, "c1_summary2"));
TestCubeRewriter.compareQueries(hqlQuery, expected);
}
@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);
}
