@Test public void testRewriteWithExpressionsPass() throws LensException { String query = "select fact.time_key,time_dim.day_of_week,time_dim.day, ((sum( fact . item_count ) + 0 ) + " + "(sum(fact . product_count ) + 0 )) as expr1, " + "(sum(case when fact.status_id is null then 0 when fact.status_id = 1 then fact.item_sold else 0 end)" + " * 1000) / sum(case when fact.status_id is null then 0 when fact.status_id = 2 " + "then fact.dollars_sold else 0 end) as expr2 from db.sales_fact as fact " + "inner join time_dim as time_dim on fact.time_key = time_dim.time_key inner " + "join db.location_dim ld on fact.location_key = ld.location_key " + "and ld.location_name = 'test123' where time_dim.time_key between '2013-01-01' and '2013-01-31' " + "group by fact.time_key,time_dim.day_of_week,time_dim.day " + "order by dollars_sold desc"; String actual = qtest.rewrite(query, conf, hconf); String expected = "select ( sales_fact__db_sales_fact_fact . time_key ), ( time_dim___time_dim . day_of_week ), " + "( time_dim___time_dim . day ), ((sum(alias1) + 0 ) + (sum(alias2) + 0 )) as `expr1` , " + "((sum(alias3) * 1000 ) / sum(alias4)) as `expr2` from " + "(select sales_fact__db_sales_fact_fact.time_key, sales_fact__db_sales_fact_fact.location_key," + "sum(( sales_fact__db_sales_fact_fact . item_count )) as alias1, " + "sum(( sales_fact__db_sales_fact_fact . product_count )) as alias2, " + "sum( case when ( sales_fact__db_sales_fact_fact . status_id ) is null then 0 " + "when (( sales_fact__db_sales_fact_fact . status_id ) = 1 ) then " + "( sales_fact__db_sales_fact_fact . item_sold ) else 0 end ) as alias3, " + "sum( case when ( sales_fact__db_sales_fact_fact . status_id ) is null " + "then 0 when (( sales_fact__db_sales_fact_fact . status_id ) = 2 ) " + "then ( sales_fact__db_sales_fact_fact . dollars_sold ) else 0 end ) as " + "alias4 from db.sales_fact sales_fact__db_sales_fact_fact where " + "sales_fact__db_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-31' ) " + "and sales_fact__db_sales_fact_fact.location_key in ( select location_dim .location_key " + "from location_dim where (( location_dim. location_name ) = 'test123' ) ) " + "group by sales_fact__db_sales_fact_fact.time_key, sales_fact__db_sales_fact_fact.location_key) " + "sales_fact__db_sales_fact_fact inner join (select time_key,day_of_week,day from time_dim) " + "time_dim___time_dim on (( sales_fact__db_sales_fact_fact . time_key ) = " + "( time_dim___time_dim . time_key )) inner join (select location_key," + "location_name from db.location_dim) location_dim__db_location_dim_ld on " + "((( sales_fact__db_sales_fact_fact . location_key ) = " + "( location_dim__db_location_dim_ld . location_key )) and " + "(( location_dim__db_location_dim_ld . location_name ) = 'test123' )) " + "where ( time_dim___time_dim . time_key ) between '2013-01-01' and '2013-01-31' " + "group by ( sales_fact__db_sales_fact_fact . time_key ), ( time_dim___time_dim . day_of_week ), " + "( time_dim___time_dim . day ) order by dollars_sold desc"; 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);  }