Examples with RelRoot org.apache.calcite.rel.RelRoot used on opensource projects

Example 11 with RelRoot

use of org.apache.calcite.rel.RelRoot in project samza by apache.

the class TestQueryPlanner method testRemoteJoinWithUdfAndFilterHelper.

void testRemoteJoinWithUdfAndFilterHelper(boolean enableOptimizer) throws SamzaSqlValidatorException {
    Map<String, String> staticConfigs = SamzaSqlTestConfig.fetchStaticConfigsWithFactories(1);
    String sql = "Insert into testavro.enrichedPageViewTopic " + "select pv.pageKey as __key__, pv.pageKey as pageKey, coalesce(null, 'N/A') as companyName," + "       p.name as profileName, p.address as profileAddress " + "from testRemoteStore.Profile.`$table` as p " + "join testavro.PAGEVIEW as pv " + " on p.__key__ = BuildOutputRecord('id', pv.profileId)" + " where p.name = 'Mike' and pv.profileId = 1 and p.name = pv.pageKey";
    staticConfigs.put(SamzaSqlApplicationConfig.CFG_SQL_STMT, sql);
    staticConfigs.put(SamzaSqlApplicationConfig.CFG_SQL_ENABLE_PLAN_OPTIMIZER, Boolean.toString(enableOptimizer));
    Config samzaConfig = new MapConfig(staticConfigs);
    DslConverter dslConverter = new SamzaSqlDslConverterFactory().create(samzaConfig);
    Collection<RelRoot> relRoots = dslConverter.convertDsl(sql);
    /*
      Query plan without optimization:
      LogicalProject(__key__=[$9], pageKey=[$9], companyName=['N/A'], profileName=[$2], profileAddress=[$4])
        LogicalFilter(condition=[AND(=($2, 'Mike'), =($10, 1), =($2, $9))])  ==> Only the second condition could be pushed down.
          LogicalProject(__key__=[$0], id=[$1], name=[$2], companyId=[$3], address=[$4], selfEmployed=[$5],
                                  phoneNumbers=[$6], mapValues=[$7], __key__0=[$8], pageKey=[$9], profileId=[$10])
                                  ==> ProjectMergeRule removes this redundant node.
            LogicalJoin(condition=[=($0, $11)], joinType=[inner])
              LogicalTableScan(table=[[testRemoteStore, Profile, $table]])
              LogicalProject(__key__=[$0], pageKey=[$1], profileId=[$2], $f3=[BuildOutputRecord('id', $2)])  ==> Filter is pushed above project.
                LogicalTableScan(table=[[testavro, PAGEVIEW]])

      Query plan with optimization:
      LogicalProject(__key__=[$9], pageKey=[$9], companyName=['N/A'], profileName=[$2], profileAddress=[$4])
          LogicalFilter(condition=[AND(=($2, 'Mike'), =($2, $9))])
            LogicalJoin(condition=[=($0, $11)], joinType=[inner])
              LogicalTableScan(table=[[testRemoteStore, Profile, $table]])
              LogicalFilter(condition=[=($2, 1)])
                LogicalProject(__key__=[$0], pageKey=[$1], profileId=[$2], $f3=[BuildOutputRecord('id', $2)])
                  LogicalTableScan(table=[[testavro, PAGEVIEW]])
     */
    assertEquals(1, relRoots.size());
    RelRoot relRoot = relRoots.iterator().next();
    RelNode relNode = relRoot.rel;
    assertTrue(relNode instanceof LogicalProject);
    relNode = relNode.getInput(0);
    assertTrue(relNode instanceof LogicalFilter);
    if (enableOptimizer) {
        assertEquals("AND(=($2, $9), =($2, 'Mike'))", ((LogicalFilter) relNode).getCondition().toString());
    } else {
        assertEquals("AND(=($2, $9), =(1, $10), =($2, 'Mike'))", ((LogicalFilter) relNode).getCondition().toString());
    }
    relNode = relNode.getInput(0);
    if (enableOptimizer) {
        assertTrue(relNode instanceof LogicalJoin);
        assertEquals(2, relNode.getInputs().size());
    } else {
        assertTrue(relNode instanceof LogicalProject);
        relNode = relNode.getInput(0);
    }
    LogicalJoin join = (LogicalJoin) relNode;
    RelNode left = join.getLeft();
    RelNode right = join.getRight();
    assertTrue(left instanceof LogicalTableScan);
    if (enableOptimizer) {
        assertTrue(right instanceof LogicalFilter);
        assertEquals("=(1, $2)", ((LogicalFilter) right).getCondition().toString());
        relNode = right.getInput(0);
    } else {
        relNode = right;
    }
    assertTrue(relNode instanceof LogicalProject);
    relNode = relNode.getInput(0);
    assertTrue(relNode instanceof LogicalTableScan);
}

Example 12 with RelRoot

use of org.apache.calcite.rel.RelRoot in project samza by apache.

the class TestQueryPlanner method testLocalStreamTableInnerJoinFilterOptimization.

@Test
public void testLocalStreamTableInnerJoinFilterOptimization() throws Exception {
    Map<String, String> staticConfigs = SamzaSqlTestConfig.fetchStaticConfigsWithFactories(1);
    String sql = "Insert into testavro.enrichedPageViewTopic " + "select pv.pageKey as __key__, pv.pageKey as pageKey, p.name as companyName, p.name as profileName," + "       p.address as profileAddress " + "from testavro.PROFILE.`$table` as p " + "join testavro.PAGEVIEW as pv " + " on p.id = pv.profileId " + "where p.name = 'Mike'";
    staticConfigs.put(SamzaSqlApplicationConfig.CFG_SQL_STMT, sql);
    staticConfigs.put(SamzaSqlApplicationConfig.CFG_SQL_ENABLE_PLAN_OPTIMIZER, Boolean.toString(true));
    Config samzaConfig = new MapConfig(staticConfigs);
    DslConverter dslConverter = new SamzaSqlDslConverterFactory().create(samzaConfig);
    Collection<RelRoot> relRootsWithOptimization = dslConverter.convertDsl(sql);
    staticConfigs.put(SamzaSqlApplicationConfig.CFG_SQL_ENABLE_PLAN_OPTIMIZER, Boolean.toString(false));
    samzaConfig = new MapConfig(staticConfigs);
    dslConverter = new SamzaSqlDslConverterFactory().create(samzaConfig);
    Collection<RelRoot> relRootsWithoutOptimization = dslConverter.convertDsl(sql);
    // We do not yet have any join filter optimizations for local joins. Hence the plans with and without optimization
    // should be the same.
    assertEquals(RelOptUtil.toString(relRootsWithOptimization.iterator().next().rel, SqlExplainLevel.EXPPLAN_ATTRIBUTES), RelOptUtil.toString(relRootsWithoutOptimization.iterator().next().rel, SqlExplainLevel.EXPPLAN_ATTRIBUTES));
}

Example 13 with RelRoot

use of org.apache.calcite.rel.RelRoot in project samza by apache.

the class TestQueryPlanner method testRemoteJoinFilterPushDownWithUdfInFilterAndOptimizer.

@Test
public void testRemoteJoinFilterPushDownWithUdfInFilterAndOptimizer() throws SamzaSqlValidatorException {
    Map<String, String> staticConfigs = SamzaSqlTestConfig.fetchStaticConfigsWithFactories(1);
    String sql = "Insert into testavro.enrichedPageViewTopic " + "select pv.pageKey as __key__, pv.pageKey as pageKey, coalesce(null, 'N/A') as companyName," + "       p.name as profileName, p.address as profileAddress " + "from testRemoteStore.Profile.`$table` as p " + "join testavro.PAGEVIEW as pv " + " on p.__key__ = pv.profileId" + " where p.name = pv.pageKey AND p.name = 'Mike' AND pv.profileId = MyTest(pv.profileId)";
    staticConfigs.put(SamzaSqlApplicationConfig.CFG_SQL_STMT, sql);
    staticConfigs.put(SamzaSqlApplicationConfig.CFG_SQL_ENABLE_PLAN_OPTIMIZER, Boolean.toString(true));
    Config samzaConfig = new MapConfig(staticConfigs);
    DslConverter dslConverter = new SamzaSqlDslConverterFactory().create(samzaConfig);
    Collection<RelRoot> relRoots = dslConverter.convertDsl(sql);
    /*
      Query plan without optimization:
      LogicalProject(__key__=[$9], pageKey=[$9], companyName=['N/A'], profileName=[$2], profileAddress=[$4])
        LogicalFilter(condition=[AND(=($2, $9), =($2, 'Mike'), =($10, CAST(MyTest($10)):INTEGER))])
          LogicalJoin(condition=[=($0, $10)], joinType=[inner])
            LogicalTableScan(table=[[testRemoteStore, Profile, $table]])
            LogicalTableScan(table=[[testavro, PAGEVIEW]])

      Query plan with optimization:
      LogicalProject(__key__=[$9], pageKey=[$9], companyName=['N/A'], profileName=[$2], profileAddress=[$4])
        LogicalFilter(condition=[AND(=($2, $9), =($2, 'Mike'))])
          LogicalJoin(condition=[=($0, $10)], joinType=[inner])
            LogicalTableScan(table=[[testRemoteStore, Profile, $table]])
            LogicalFilter(condition=[=($2, CAST(MyTest($2)):INTEGER)])
              LogicalTableScan(table=[[testavro, PAGEVIEW]])
     */
    assertEquals(1, relRoots.size());
    RelRoot relRoot = relRoots.iterator().next();
    RelNode relNode = relRoot.rel;
    assertTrue(relNode instanceof LogicalProject);
    relNode = relNode.getInput(0);
    assertTrue(relNode instanceof LogicalFilter);
    assertEquals("AND(=($2, $9), =($2, 'Mike'))", ((LogicalFilter) relNode).getCondition().toString());
    relNode = relNode.getInput(0);
    assertTrue(relNode instanceof LogicalJoin);
    assertEquals(2, relNode.getInputs().size());
    LogicalJoin join = (LogicalJoin) relNode;
    RelNode left = join.getLeft();
    RelNode right = join.getRight();
    assertTrue(left instanceof LogicalTableScan);
    assertTrue(right instanceof LogicalFilter);
    assertEquals("=($2, CAST(MyTest($2)):INTEGER)", ((LogicalFilter) right).getCondition().toString());
    assertTrue(right.getInput(0) instanceof LogicalTableScan);
}

Example 14 with RelRoot

use of org.apache.calcite.rel.RelRoot in project beam by apache.

the class ZetaSQLQueryPlanner method convertToBeamRelInternal.

private BeamRelNode convertToBeamRelInternal(String sql, QueryParameters queryParams) {
    RelRoot root = plannerImpl.rel(sql, queryParams);
    RelTraitSet desiredTraits = root.rel.getTraitSet().replace(BeamLogicalConvention.INSTANCE).replace(root.collation).simplify();
    // beam physical plan
    root.rel.getCluster().setMetadataProvider(ChainedRelMetadataProvider.of(org.apache.beam.vendor.calcite.v1_28_0.com.google.common.collect.ImmutableList.of(NonCumulativeCostImpl.SOURCE, RelMdNodeStats.SOURCE, root.rel.getCluster().getMetadataProvider())));
    root.rel.getCluster().setMetadataQuerySupplier(BeamRelMetadataQuery::instance);
    RelMetadataQuery.THREAD_PROVIDERS.set(JaninoRelMetadataProvider.of(root.rel.getCluster().getMetadataProvider()));
    root.rel.getCluster().invalidateMetadataQuery();
    try {
        BeamRelNode beamRelNode = (BeamRelNode) plannerImpl.transform(0, desiredTraits, root.rel);
        LOG.info("BEAMPlan>\n" + RelOptUtil.toString(beamRelNode));
        return beamRelNode;
    } catch (RelOptPlanner.CannotPlanException e) {
        throw new SqlConversionException("Failed to produce plan for query " + sql, e);
    }
}

Example 15 with RelRoot

use of org.apache.calcite.rel.RelRoot in project beam by apache.

the class CalciteQueryPlanner method convertToBeamRel.

/**
 * It parses and validate the input query, then convert into a {@link BeamRelNode} tree. Note that
 * query parameters are not yet supported.
 */
@Override
public BeamRelNode convertToBeamRel(String sqlStatement, QueryParameters queryParameters) throws ParseException, SqlConversionException {
    Preconditions.checkArgument(queryParameters.getKind() == Kind.NONE, "Beam SQL Calcite dialect does not yet support query parameters.");
    BeamRelNode beamRelNode;
    try {
        SqlNode parsed = planner.parse(sqlStatement);
        TableResolutionUtils.setupCustomTableResolution(connection, parsed);
        SqlNode validated = planner.validate(parsed);
        LOG.info("SQL:\n" + validated);
        // root of original logical plan
        RelRoot root = planner.rel(validated);
        LOG.info("SQLPlan>\n" + RelOptUtil.toString(root.rel));
        RelTraitSet desiredTraits = root.rel.getTraitSet().replace(BeamLogicalConvention.INSTANCE).replace(root.collation).simplify();
        // beam physical plan
        root.rel.getCluster().setMetadataProvider(ChainedRelMetadataProvider.of(ImmutableList.of(NonCumulativeCostImpl.SOURCE, RelMdNodeStats.SOURCE, root.rel.getCluster().getMetadataProvider())));
        root.rel.getCluster().setMetadataQuerySupplier(BeamRelMetadataQuery::instance);
        RelMetadataQuery.THREAD_PROVIDERS.set(JaninoRelMetadataProvider.of(root.rel.getCluster().getMetadataProvider()));
        root.rel.getCluster().invalidateMetadataQuery();
        beamRelNode = (BeamRelNode) planner.transform(0, desiredTraits, root.rel);
        LOG.info("BEAMPlan>\n" + RelOptUtil.toString(beamRelNode));
    } catch (RelConversionException | CannotPlanException e) {
        throw new SqlConversionException(String.format("Unable to convert query %s", sqlStatement), e);
    } catch (SqlParseException | ValidationException e) {
        throw new ParseException(String.format("Unable to parse query %s", sqlStatement), e);
    } finally {
        planner.close();
    }
    return beamRelNode;
}