@Test public void testUnionQuery() throws LensException { String query = "select fact.time_key,time_dim.day_of_week,time_dim.day," + "case when sum(fact.dollars_sold) = 0 then 0.0 else sum(fact.dollars_sold) end dollars_sold " + "from sales_fact fact " + "inner join time_dim time_dim on fact.time_key = time_dim.time_key " + "inner join location_dim location_dim on fact.location_key = location_dim.location_key " + "and location_dim.location_name = 'test123' " + "where time_dim.time_key between '2013-01-01' and '2013-01-05' " + "group by fact.time_key,time_dim.day_of_week,time_dim.day " + "order by dollars_sold " + "union all " + "select fact.time_key,time_dim.day_of_week,time_dim.day," + "case when sum(fact.dollars_sold) = 0 then 0.0 else sum(fact.dollars_sold) end dollars_sold " + "from sales_fact fact " + "inner join time_dim time_dim on fact.time_key = time_dim.time_key " + "inner join location_dim location_dim on fact.location_key = location_dim.location_key " + "and location_dim.location_name = 'test123' " + "where time_dim.time_key between '2013-02-01' and '2013-02-05' " + "group by fact.time_key,time_dim.day_of_week,time_dim.day " + "order by dollars_sold " + "union all " + "select fact.time_key,time_dim.day_of_week,time_dim.day," + "case when sum(fact.dollars_sold) = 0 then 0.0 else sum(fact.dollars_sold) end dollars_sold " + "from sales_fact fact " + "inner join time_dim time_dim on fact.time_key = time_dim.time_key " + "inner join location_dim location_dim on fact.location_key = location_dim.location_key " + "and location_dim.location_name = 'test123' " + "where time_dim.time_key between '2013-03-01' and '2013-03-05' " + "group by fact.time_key,time_dim.day_of_week,time_dim.day " + "order by dollars_sold "; String actual = qtest.rewrite(query, conf, hconf); String expected = "select ( sales_fact___fact . time_key ), ( time_dim___time_dim . day_of_week ), " + "( time_dim___time_dim . day ), case when (sum(alias2) = 0 ) then 0.0 " + "else sum(alias2) end as `dollars_sold` from (select sales_fact___fact.time_key, " + "sales_fact___fact.location_key,sum(( sales_fact___fact . dollars_sold )) as alias1, " + "sum(( sales_fact___fact . dollars_sold )) as alias2 from sales_fact sales_fact___fact " + "where sales_fact___fact.time_key in ( select time_dim .time_key from time_dim where " + "( time_dim. time_key ) between '2013-01-01' and '2013-01-05' ) and " + "sales_fact___fact.location_key in ( select location_dim .location_key from " + "location_dim where (( location_dim. location_name ) = 'test123' ) ) group by " + "sales_fact___fact.time_key, sales_fact___fact.location_key) sales_fact___fact " + "inner join (select time_key,day_of_week,day from time_dim) time_dim___time_dim " + "on (( sales_fact___fact . time_key ) = ( time_dim___time_dim . time_key )) " + "inner join (select location_key,location_name from location_dim) " + "location_dim___location_dim on ((( sales_fact___fact . location_key ) = " + "( location_dim___location_dim . location_key )) and (( location_dim___location_dim . " + "location_name ) = 'test123' )) where ( time_dim___time_dim . time_key ) " + "between '2013-01-01' and '2013-01-05' group by ( sales_fact___fact . time_key ), " + "( time_dim___time_dim . day_of_week ), ( time_dim___time_dim . day ) " + "order by dollars_sold asc union all select ( sales_fact___fact . time_key ), " + "( time_dim___time_dim . day_of_week ), ( time_dim___time_dim . day ), " + "case when (sum(alias2) = 0 ) then 0.0 else sum(alias2) end as `dollars_sold` " + "from (select sales_fact___fact.time_key, sales_fact___fact.location_key," + "sum(( sales_fact___fact . dollars_sold )) as alias1, sum(( sales_fact___fact . dollars_sold )) " + "as alias2 from sales_fact sales_fact___fact where sales_fact___fact.time_key in " + "( select time_dim .time_key from time_dim where ( time_dim. time_key ) between " + " '2013-02-01' and '2013-02-05' ) and sales_fact___fact.location_key in (" + " select location_dim .location_key from location_dim where (( location_dim. location_name ) " + "= 'test123' ) ) group by sales_fact___fact.time_key, sales_fact___fact.location_key) " + "sales_fact___fact inner join (select time_key,day_of_week,day from time_dim) " + "time_dim___time_dim on (( sales_fact___fact . time_key ) = ( time_dim___time_dim . time_key )) " + "inner join (select location_key,location_name from location_dim) location_dim___location_dim " + "on ((( sales_fact___fact . location_key ) = ( location_dim___location_dim . location_key )) " + "and (( location_dim___location_dim . location_name ) = 'test123' )) where " + "( time_dim___time_dim . time_key ) between '2013-02-01' and '2013-02-05' " + "group by ( sales_fact___fact . time_key ), ( time_dim___time_dim . day_of_week ), " + "( time_dim___time_dim . day ) order by dollars_sold asc union all select " + "( sales_fact___fact . time_key ), ( time_dim___time_dim . day_of_week ), " + "( time_dim___time_dim . day ), case when (sum(alias2) = 0 ) then 0.0 " + "else sum(alias2) end as `dollars_sold` from (select sales_fact___fact.time_key, " + "sales_fact___fact.location_key,sum(( sales_fact___fact . dollars_sold )) as alias1, " + "sum(( sales_fact___fact . dollars_sold )) as alias2 from sales_fact sales_fact___fact " + "where sales_fact___fact.time_key in ( select time_dim .time_key from time_dim where " + "( time_dim. time_key ) between '2013-03-01' and '2013-03-05' ) " + "and sales_fact___fact.location_key in ( select location_dim .location_key from " + "location_dim where (( location_dim. location_name ) = 'test123' ) ) " + "group by sales_fact___fact.time_key, sales_fact___fact.location_key) " + "sales_fact___fact inner join (select time_key,day_of_week,day from time_dim) " + "time_dim___time_dim on (( sales_fact___fact . time_key ) = " + "( time_dim___time_dim . time_key )) inner join (select location_key,location_name " + "from location_dim) location_dim___location_dim on ((( sales_fact___fact . location_key ) " + "= ( location_dim___location_dim . location_key )) and " + "(( location_dim___location_dim . location_name ) = 'test123' )) " + "where ( time_dim___time_dim . time_key ) between '2013-03-01' and '2013-03-05' " + "group by ( sales_fact___fact . time_key ), ( time_dim___time_dim . day_of_week ), " + "( time_dim___time_dim . day ) order by dollars_sold asc"; compareQueries(actual, expected); }
@Override public String rewrite(String query, Configuration conf, HiveConf metastoreConf) throws LensException { this.query = query; StringBuilder mergedQuery; rewrittenQuery.setLength(0); String queryReplacedUdf; reset(); try { String finalRewrittenQuery; if (query.toLowerCase().matches("(.*)union all(.*)")) { finalRewrittenQuery = ""; String[] queries = query.toLowerCase().split("union all"); for (int i = 0; i < queries.length; i++) { log.info("Union Query Part {} : {}", i, queries[i]); ast = HQLParser.parseHQL(queries[i], metastoreConf); buildQuery(conf, metastoreConf); mergedQuery = rewrittenQuery.append(" union all "); finalRewrittenQuery = mergedQuery.toString().substring(0, mergedQuery.lastIndexOf("union all")); reset(); } } else { ast = HQLParser.parseHQL(query, metastoreConf); buildQuery(conf, metastoreConf); finalRewrittenQuery = rewrittenQuery.toString(); } queryReplacedUdf = replaceUDFForDB(finalRewrittenQuery); log.info("Input Query : {}", query); log.info("Rewritten Query : {}", queryReplacedUdf); } catch (SemanticException e) { throw new LensException(e); } return queryReplacedUdf; }
ColumnarSQLRewriter implements QueryRewriter { @Override public String rewrite(String query, Configuration conf, HiveConf metastoreConf) throws LensException { this.query = query; StringBuilder mergedQuery; rewrittenQuery.setLength(0); String queryReplacedUdf; reset(); try { String finalRewrittenQuery; if (query.toLowerCase().matches("(.*)union all(.*)")) { finalRewrittenQuery = ""; String[] queries = query.toLowerCase().split("union all"); for (int i = 0; i < queries.length; i++) { log.info("Union Query Part {} : {}", i, queries[i]); ast = HQLParser.parseHQL(queries[i], metastoreConf); buildQuery(conf, metastoreConf); mergedQuery = rewrittenQuery.append(" union all "); finalRewrittenQuery = mergedQuery.toString().substring(0, mergedQuery.lastIndexOf("union all")); reset(); } } else { ast = HQLParser.parseHQL(query, metastoreConf); buildQuery(conf, metastoreConf); finalRewrittenQuery = rewrittenQuery.toString(); } queryReplacedUdf = replaceUDFForDB(finalRewrittenQuery); log.info("Input Query : {}", query); log.info("Rewritten Query : {}", queryReplacedUdf); } catch (SemanticException e) { throw new LensException(e); } return queryReplacedUdf; } }
ColumnarSQLRewriter implements QueryRewriter { @Override public String rewrite(String query, Configuration conf, HiveConf metastoreConf) throws LensException { this.query = query; StringBuilder mergedQuery; rewrittenQuery.setLength(0); String queryReplacedUdf; reset(); try { String finalRewrittenQuery; if (query.toLowerCase().matches("(.*)union all(.*)")) { finalRewrittenQuery = ""; String[] queries = query.toLowerCase().split("union all"); for (int i = 0; i < queries.length; i++) { log.info("Union Query Part {} : {}", i, queries[i]); ast = HQLParser.parseHQL(queries[i], metastoreConf); buildQuery(conf, metastoreConf); mergedQuery = rewrittenQuery.append(" union all "); finalRewrittenQuery = mergedQuery.toString().substring(0, mergedQuery.lastIndexOf("union all")); reset(); } } else { ast = HQLParser.parseHQL(query, metastoreConf); buildQuery(conf, metastoreConf); finalRewrittenQuery = rewrittenQuery.toString(); } queryReplacedUdf = replaceUDFForDB(finalRewrittenQuery); log.info("Input Query : {}", query); log.info("Rewritten Query : {}", queryReplacedUdf); } catch (SemanticException e) { throw new LensException(e); } return queryReplacedUdf; } ColumnarSQLRewriter(); }
ColumnarSQLRewriter implements QueryRewriter { @Override public String rewrite(String query, Configuration conf, HiveConf metastoreConf) throws LensException { this.query = query; StringBuilder mergedQuery; rewrittenQuery.setLength(0); String queryReplacedUdf; reset(); try { String finalRewrittenQuery; if (query.toLowerCase().matches("(.*)union all(.*)")) { finalRewrittenQuery = ""; String[] queries = query.toLowerCase().split("union all"); for (int i = 0; i < queries.length; i++) { log.info("Union Query Part {} : {}", i, queries[i]); ast = HQLParser.parseHQL(queries[i], metastoreConf); buildQuery(conf, metastoreConf); mergedQuery = rewrittenQuery.append(" union all "); finalRewrittenQuery = mergedQuery.toString().substring(0, mergedQuery.lastIndexOf("union all")); reset(); } } else { ast = HQLParser.parseHQL(query, metastoreConf); buildQuery(conf, metastoreConf); finalRewrittenQuery = rewrittenQuery.toString(); } queryReplacedUdf = replaceUDFForDB(finalRewrittenQuery); log.info("Input Query : {}", query); log.info("Rewritten Query : {}", queryReplacedUdf); } catch (SemanticException e) { throw new LensException(e); } return queryReplacedUdf; } ColumnarSQLRewriter(); @Override void init(Configuration conf); String getClause(); void analyzeInternal(Configuration conf, HiveConf hconf); String getTableFromTabRefNode(ASTNode tree); void getJoinCond(ASTNode node); StringBuilder constructJoinChain(); int getColumnCount(ASTNode node); boolean isExpressionsAnswerableFromFact(ASTNode node); void getFilterInJoinCond(ASTNode node); String getFactAlias(); void factFilterPushDown(ASTNode node); void getFactKeysFromNode(ASTNode node); void getAllFactKeys(); void getAllDimColumns(ASTNode node); void buildDimSubqueries(); void buildSubqueries(ASTNode node); String getTableOrAlias(String keyString, String type); ArrayList<String> getAggregateColumns(ASTNode node, MutableInt count); ArrayList<String> getTablesAndColumns(ASTNode node); String getLimitClause(ASTNode node); void getAllFilters(ASTNode node); String getFactNameAlias(ASTNode fromAST); void reset(); String replaceUDFForDB(String query); void replaceAliasInAST(); void buildQuery(Configuration conf, HiveConf hconf); @Override String rewrite(String query, Configuration conf, HiveConf metastoreConf); }
ColumnarSQLRewriter implements QueryRewriter { @Override public String rewrite(String query, Configuration conf, HiveConf metastoreConf) throws LensException { this.query = query; StringBuilder mergedQuery; rewrittenQuery.setLength(0); String queryReplacedUdf; reset(); try { String finalRewrittenQuery; if (query.toLowerCase().matches("(.*)union all(.*)")) { finalRewrittenQuery = ""; String[] queries = query.toLowerCase().split("union all"); for (int i = 0; i < queries.length; i++) { log.info("Union Query Part {} : {}", i, queries[i]); ast = HQLParser.parseHQL(queries[i], metastoreConf); buildQuery(conf, metastoreConf); mergedQuery = rewrittenQuery.append(" union all "); finalRewrittenQuery = mergedQuery.toString().substring(0, mergedQuery.lastIndexOf("union all")); reset(); } } else { ast = HQLParser.parseHQL(query, metastoreConf); buildQuery(conf, metastoreConf); finalRewrittenQuery = rewrittenQuery.toString(); } queryReplacedUdf = replaceUDFForDB(finalRewrittenQuery); log.info("Input Query : {}", query); log.info("Rewritten Query : {}", queryReplacedUdf); } catch (SemanticException e) { throw new LensException(e); } return queryReplacedUdf; } ColumnarSQLRewriter(); @Override void init(Configuration conf); String getClause(); void analyzeInternal(Configuration conf, HiveConf hconf); String getTableFromTabRefNode(ASTNode tree); void getJoinCond(ASTNode node); StringBuilder constructJoinChain(); int getColumnCount(ASTNode node); boolean isExpressionsAnswerableFromFact(ASTNode node); void getFilterInJoinCond(ASTNode node); String getFactAlias(); void factFilterPushDown(ASTNode node); void getFactKeysFromNode(ASTNode node); void getAllFactKeys(); void getAllDimColumns(ASTNode node); void buildDimSubqueries(); void buildSubqueries(ASTNode node); String getTableOrAlias(String keyString, String type); ArrayList<String> getAggregateColumns(ASTNode node, MutableInt count); ArrayList<String> getTablesAndColumns(ASTNode node); String getLimitClause(ASTNode node); void getAllFilters(ASTNode node); String getFactNameAlias(ASTNode fromAST); void reset(); String replaceUDFForDB(String query); void replaceAliasInAST(); void buildQuery(Configuration conf, HiveConf hconf); @Override String rewrite(String query, Configuration conf, HiveConf metastoreConf);  }