@Test public void testSchemaCompletion() throws SqlParseException { String sql1 = "SELECT a \n" + "FROM a.KYLIN_SALES as KYLIN_SALES\n" + "INNER JOIN \"A\".KYLIN_ACCOUNT as BUYER_ACCOUNT\n" + "ON KYLIN_SALES.BUYER_ID = BUYER_ACCOUNT.ACCOUNT_ID\n" + "INNER JOIN \"KYLIN_COUNTRY\" as BUYER_COUNTRY\n" + "ON BUYER_ACCOUNT.ACCOUNT_COUNTRY = BUYER_COUNTRY.COUNTRY LIMIT 5"; String sql2 = "select * from DB2.t,DB2.tt,ttt"; String sql3 = "SELECT t1.week_beg_dt, t1.sum_price, t2.cnt\n" + "FROM (\n" + " select test_cal_dt.week_beg_dt, sum(price) as sum_price\n" + " from DB1.\"test_kylin_fact\"\n" + " inner JOIN test_cal_dt as test_cal_dt\n" + " ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt\n" + " inner JOIN test_category_groupings\n" + " ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id\n" + " inner JOIN test_sites as test_sites\n" + " ON test_kylin_fact.lstg_site_id = test_sites.site_id\n" + " where price > 100\n" + " group by test_cal_dt.week_beg_dt\n" + " having sum(price) > 1000\n" + " order by sum(price)\n" + ") t1\n" + "inner join (\n" + " select test_cal_dt.week_beg_dt, count(*) as cnt\n" + " from DB1.test_kylin_fact\n" + " inner JOIN test_cal_dt as test_cal_dt\n" + " ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt\n" + " inner JOIN test_category_groupings\n" + " ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id\n" + " inner JOIN test_sites as test_sites\n" + " ON test_kylin_fact.lstg_site_id = test_sites.site_id\n" + " group by test_cal_dt.week_beg_dt\n" + ") t2\n" + "on t1.week_beg_dt=t2.week_beg_dt limit 5"; String exceptSQL1 = "SELECT a \n" + "FROM a.KYLIN_SALES as KYLIN_SALES\n" + "INNER JOIN \"A\".KYLIN_ACCOUNT as BUYER_ACCOUNT\n" + "ON KYLIN_SALES.BUYER_ID = BUYER_ACCOUNT.ACCOUNT_ID\n" + "INNER JOIN EDW.\"KYLIN_COUNTRY\" as BUYER_COUNTRY\n" + "ON BUYER_ACCOUNT.ACCOUNT_COUNTRY = BUYER_COUNTRY.COUNTRY LIMIT 5"; String exceptSQL2 = "select * from DB2.t,DB2.tt,EDW.ttt"; String exceptSQL3 = "SELECT t1.week_beg_dt, t1.sum_price, t2.cnt\n" + "FROM (\n" + " select test_cal_dt.week_beg_dt, sum(price) as sum_price\n" + " from DB1.\"test_kylin_fact\"\n" + " inner JOIN EDW.test_cal_dt as test_cal_dt\n" + " ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt\n" + " inner JOIN EDW.test_category_groupings\n" + " ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id\n" + " inner JOIN EDW.test_sites as test_sites\n" + " ON test_kylin_fact.lstg_site_id = test_sites.site_id\n" + " where price > 100\n" + " group by test_cal_dt.week_beg_dt\n" + " having sum(price) > 1000\n" + " order by sum(price)\n" + ") t1\n" + "inner join (\n" + " select test_cal_dt.week_beg_dt, count(*) as cnt\n" + " from DB1.test_kylin_fact\n" + " inner JOIN EDW.test_cal_dt as test_cal_dt\n" + " ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt\n" + " inner JOIN EDW.test_category_groupings\n" + " ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id\n" + " inner JOIN EDW.test_sites as test_sites\n" + " ON test_kylin_fact.lstg_site_id = test_sites.site_id\n" + " group by test_cal_dt.week_beg_dt\n" + ") t2\n" + "on t1.week_beg_dt=t2.week_beg_dt limit 5"; Assert.assertEquals(exceptSQL1, PushDownUtil.schemaCompletion(sql1, "EDW")); Assert.assertEquals(exceptSQL2, PushDownUtil.schemaCompletion(sql2, "EDW")); Assert.assertEquals(exceptSQL3, PushDownUtil.schemaCompletion(sql3, "EDW")); }
static String schemaCompletion(String inputSql, String schema) throws SqlParseException { if (inputSql == null || inputSql.equals("")) { return ""; } SqlNode node = CalciteParser.parse(inputSql); PushDownUtil.FromTablesVisitor ftv = new PushDownUtil.FromTablesVisitor(); node.accept(ftv); List<SqlNode> tablesWithoutSchema = ftv.getTablesWithoutSchema(); if (tablesWithoutSchema.isEmpty()) { return inputSql; } List<Pair<Integer, Integer>> tablesPos = new ArrayList<>(); for (SqlNode tables : tablesWithoutSchema) { tablesPos.add(CalciteParser.getReplacePos(tables, inputSql)); } Collections.sort(tablesPos, new Comparator<Pair<Integer, Integer>>() { @Override public int compare(Pair<Integer, Integer> o1, Pair<Integer, Integer> o2) { int r = o2.getFirst() - o1.getFirst(); return r == 0 ? o2.getSecond() - o1.getSecond() : r; } }); StrBuilder afterConvert = new StrBuilder(inputSql); for (Pair<Integer, Integer> pos : tablesPos) { String tableWithSchema = schema + "." + inputSql.substring(pos.getFirst(), pos.getSecond()); afterConvert.replace(pos.getFirst(), pos.getSecond(), tableWithSchema); } return afterConvert.toString(); }
PushDownUtil { static String schemaCompletion(String inputSql, String schema) throws SqlParseException { if (inputSql == null || inputSql.equals("")) { return ""; } SqlNode node = CalciteParser.parse(inputSql); PushDownUtil.FromTablesVisitor ftv = new PushDownUtil.FromTablesVisitor(); node.accept(ftv); List<SqlNode> tablesWithoutSchema = ftv.getTablesWithoutSchema(); if (tablesWithoutSchema.isEmpty()) { return inputSql; } List<Pair<Integer, Integer>> tablesPos = new ArrayList<>(); for (SqlNode tables : tablesWithoutSchema) { tablesPos.add(CalciteParser.getReplacePos(tables, inputSql)); } Collections.sort(tablesPos, new Comparator<Pair<Integer, Integer>>() { @Override public int compare(Pair<Integer, Integer> o1, Pair<Integer, Integer> o2) { int r = o2.getFirst() - o1.getFirst(); return r == 0 ? o2.getSecond() - o1.getSecond() : r; } }); StrBuilder afterConvert = new StrBuilder(inputSql); for (Pair<Integer, Integer> pos : tablesPos) { String tableWithSchema = schema + "." + inputSql.substring(pos.getFirst(), pos.getSecond()); afterConvert.replace(pos.getFirst(), pos.getSecond(), tableWithSchema); } return afterConvert.toString(); } }
PushDownUtil { static String schemaCompletion(String inputSql, String schema) throws SqlParseException { if (inputSql == null || inputSql.equals("")) { return ""; } SqlNode node = CalciteParser.parse(inputSql); PushDownUtil.FromTablesVisitor ftv = new PushDownUtil.FromTablesVisitor(); node.accept(ftv); List<SqlNode> tablesWithoutSchema = ftv.getTablesWithoutSchema(); if (tablesWithoutSchema.isEmpty()) { return inputSql; } List<Pair<Integer, Integer>> tablesPos = new ArrayList<>(); for (SqlNode tables : tablesWithoutSchema) { tablesPos.add(CalciteParser.getReplacePos(tables, inputSql)); } Collections.sort(tablesPos, new Comparator<Pair<Integer, Integer>>() { @Override public int compare(Pair<Integer, Integer> o1, Pair<Integer, Integer> o2) { int r = o2.getFirst() - o1.getFirst(); return r == 0 ? o2.getSecond() - o1.getSecond() : r; } }); StrBuilder afterConvert = new StrBuilder(inputSql); for (Pair<Integer, Integer> pos : tablesPos) { String tableWithSchema = schema + "." + inputSql.substring(pos.getFirst(), pos.getSecond()); afterConvert.replace(pos.getFirst(), pos.getSecond(), tableWithSchema); } return afterConvert.toString(); } private  PushDownUtil(); }
PushDownUtil { static String schemaCompletion(String inputSql, String schema) throws SqlParseException { if (inputSql == null || inputSql.equals("")) { return ""; } SqlNode node = CalciteParser.parse(inputSql); PushDownUtil.FromTablesVisitor ftv = new PushDownUtil.FromTablesVisitor(); node.accept(ftv); List<SqlNode> tablesWithoutSchema = ftv.getTablesWithoutSchema(); if (tablesWithoutSchema.isEmpty()) { return inputSql; } List<Pair<Integer, Integer>> tablesPos = new ArrayList<>(); for (SqlNode tables : tablesWithoutSchema) { tablesPos.add(CalciteParser.getReplacePos(tables, inputSql)); } Collections.sort(tablesPos, new Comparator<Pair<Integer, Integer>>() { @Override public int compare(Pair<Integer, Integer> o1, Pair<Integer, Integer> o2) { int r = o2.getFirst() - o1.getFirst(); return r == 0 ? o2.getSecond() - o1.getSecond() : r; } }); StrBuilder afterConvert = new StrBuilder(inputSql); for (Pair<Integer, Integer> pos : tablesPos) { String tableWithSchema = schema + "." + inputSql.substring(pos.getFirst(), pos.getSecond()); afterConvert.replace(pos.getFirst(), pos.getSecond(), tableWithSchema); } return afterConvert.toString(); } private  PushDownUtil(); static Pair<List<List<String>>, List<SelectedColumnMeta>> tryPushDownSelectQuery(String project, String sql,
            String defaultSchema, SQLException sqlException, boolean isPrepare); static Pair<List<List<String>>, List<SelectedColumnMeta>> tryPushDownNonSelectQuery(String project,
            String sql, String defaultSchema, boolean isPrepare); }
PushDownUtil { static String schemaCompletion(String inputSql, String schema) throws SqlParseException { if (inputSql == null || inputSql.equals("")) { return ""; } SqlNode node = CalciteParser.parse(inputSql); PushDownUtil.FromTablesVisitor ftv = new PushDownUtil.FromTablesVisitor(); node.accept(ftv); List<SqlNode> tablesWithoutSchema = ftv.getTablesWithoutSchema(); if (tablesWithoutSchema.isEmpty()) { return inputSql; } List<Pair<Integer, Integer>> tablesPos = new ArrayList<>(); for (SqlNode tables : tablesWithoutSchema) { tablesPos.add(CalciteParser.getReplacePos(tables, inputSql)); } Collections.sort(tablesPos, new Comparator<Pair<Integer, Integer>>() { @Override public int compare(Pair<Integer, Integer> o1, Pair<Integer, Integer> o2) { int r = o2.getFirst() - o1.getFirst(); return r == 0 ? o2.getSecond() - o1.getSecond() : r; } }); StrBuilder afterConvert = new StrBuilder(inputSql); for (Pair<Integer, Integer> pos : tablesPos) { String tableWithSchema = schema + "." + inputSql.substring(pos.getFirst(), pos.getSecond()); afterConvert.replace(pos.getFirst(), pos.getSecond(), tableWithSchema); } return afterConvert.toString(); } private  PushDownUtil(); static Pair<List<List<String>>, List<SelectedColumnMeta>> tryPushDownSelectQuery(String project, String sql,
            String defaultSchema, SQLException sqlException, boolean isPrepare); static Pair<List<List<String>>, List<SelectedColumnMeta>> tryPushDownNonSelectQuery(String project,
            String sql, String defaultSchema, boolean isPrepare);  }