@Test public void testOrderByAlias() throws LensException { String query = "SELECT (dim1 . date) dim1_date , sum((f . msr1)) msr1 , (dim2 . name) dim2_name " + "FROM fact f INNER JOIN dim1 dim1 ON f.dim1_id = dim1.id and f.m2 = '1234' " + "INNER JOIN dim2 dim2 ON f.dim2_id = dim2.id and f.m3 > 3000 " + "WHERE ((dim1 . date) = '2014-11-25 00:00:00') and f.m4 is not null " + "GROUP BY (dim1 . date), (dim2 . name) ORDER BY dim1_date"; String actual = qtest.rewrite(query, conf, hconf); String expected = "select ( dim1___dim1 . date ) as `dim1_date` , sum(alias1) as `msr1` , " + "( dim2___dim2 . name ) as `dim2_name` " + "from (select fact___f.dim1_id, fact___f.m2, fact___f.dim2_id, fact___f.m3, fact___f.m4" + "sum(( fact___f . msr1 )) as alias1 from fact fact___f where ( fact___f . m4 ) " + "is not null and (( fact___f . m2 ) = '1234' ) and (( fact___f . m3 ) > 3000 ) " + "and fact___f.dim1_id in ( select dim1 .id from dim1 where (( dim1. date ) = '2014-11-25 00:00:00' ) ) " + "group by fact___f.dim1_id, fact___f.m2, fact___f.dim2_id, fact___f.m3, fact___f.m4) fact___f " + "inner join (select id, date from dim1) dim1___dim1 on ((( fact___f . dim1_id ) = ( dim1___dim1 . id )) " + "and (( fact___f . m2 ) = '1234' )) inner join (select id, name from dim2) " + "dim2___dim2 on ((( fact___f . dim2_id ) " + "= ( dim2___dim2 . id )) and (( fact___f . m3 ) > 3000 )) " + "where ((( dim1___dim1 . date ) = '2014-11-25 00:00:00' ) and ( fact___f . m4 ) is not null ) " + "group by ( dim1___dim1 . date ), ( dim2___dim2 . name ) order by dim1_date 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);  }