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