org.apache.calcite.materialize.MaterializationService Java Examples

private void populateMaterializationsAndLattice(
    QuarkMaterializeCluster.RelOptPlannerHolder plannerHolder,
    CalciteSchema rootSchema) {
  if (materializations == null) {
    materializations =
        MaterializationService.instance().query(rootSchema);
  }
  Materializer materializer = new Materializer(materializations);

  materializer.populateMaterializations(context.getPrepareContext(), plannerHolder);

  List<CalciteSchema.LatticeEntry> lattices = Schemas.getLatticeEntries(rootSchema);

  for (CalciteSchema.LatticeEntry lattice : lattices) {
    final CalciteSchema.TableEntry starTable = lattice.getStarTable();
    final JavaTypeFactory typeFactory = context.getTypeFactory();
    final RelOptTableImpl starRelOptTable =
        RelOptTableImpl.create(catalogReader,
            starTable.getTable().getRowType(typeFactory), starTable, null);
    plannerHolder.getPlanner().addLattice(
        new RelOptLattice(lattice.getLattice(), starRelOptTable));
  }
}
 

/** Executes a {@code DROP MATERIALIZED VIEW} command. */
public void execute(SqlDropMaterializedView drop,
    CalcitePrepare.Context context) {
  final Pair<CalciteSchema, String> pair = schema(context, true, drop.name);
  final Table table = pair.left.plus().getTable(pair.right);
  if (table != null) {
    // Materialized view exists.
    execute((SqlDropObject) drop, context);
    if (table instanceof Wrapper) {
      final MaterializationKey materializationKey =
          ((Wrapper) table).unwrap(MaterializationKey.class);
      if (materializationKey != null) {
        MaterializationService.instance()
            .removeMaterialization(materializationKey);
      }
    }
  }
}
 

@Disabled("Creating mv for depts considering all its column throws exception")
@Test void testMultiMaterializationOnJoinQuery() {
  final String q = "select *\n"
      + "from \"emps\"\n"
      + "join \"depts\" using (\"deptno\") where \"empid\" < 300 "
      + "and \"depts\".\"deptno\" > 200";
  try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) {
    MaterializationService.setThreadLocal();
    CalciteAssert.that()
        .withMaterializations(HR_FKUK_MODEL,
            "m0", "select * from \"emps\" where \"empid\" < 500",
            "m1", "select * from \"depts\" where \"deptno\" > 100")
        .query(q)
        .enableMaterializations(true)
        .explainContains("EnumerableTableScan(table=[[hr, m0]])")
        .explainContains("EnumerableTableScan(table=[[hr, m1]])")
        .sameResultWithMaterializationsDisabled();
  }
}
 

@Test void testMultiMaterializationMultiUsage() {
  String q = "select *\n"
      + "from (select * from \"emps\" where \"empid\" < 300)\n"
      + "join (select \"deptno\", count(*) as c from \"emps\" group by \"deptno\") using (\"deptno\")";
  try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) {
    MaterializationService.setThreadLocal();
    CalciteAssert.that()
        .withMaterializations(HR_FKUK_MODEL,
            "m0", "select \"deptno\", count(*) as c, sum(\"empid\") as s from \"emps\" group by \"deptno\"",
            "m1", "select * from \"emps\" where \"empid\" < 500")
        .query(q)
        .enableMaterializations(true)
        .explainContains("EnumerableTableScan(table=[[hr, m0]])")
        .explainContains("EnumerableTableScan(table=[[hr, m1]])")
        .sameResultWithMaterializationsDisabled();
  }
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-761">[CALCITE-761]
 * Pre-populated materializations</a>. */
@Test void testPrePopulated() {
  String q = "select distinct \"deptno\" from \"emps\"";
  try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) {
    MaterializationService.setThreadLocal();
    CalciteAssert.that()
        .withMaterializations(
            HR_FKUK_MODEL, builder -> {
              final Map<String, Object> map = builder.map();
              map.put("table", "locations");
              String sql = "select distinct `deptno` as `empid`, '' as `name`\n"
                  + "from `emps`";
              final String sql2 = sql.replace("`", "\"");
              map.put("sql", sql2);
              return ImmutableList.of(map);
            })
        .query(q)
        .enableMaterializations(true)
        .sameResultWithMaterializationsDisabled();
  }
}
 

@Test void testTableModify() {
  final String m = "select \"deptno\", \"empid\", \"name\""
      + "from \"emps\" where \"deptno\" = 10";
  final String q = "upsert into \"dependents\""
      + "select \"empid\" + 1 as x, \"name\""
      + "from \"emps\" where \"deptno\" = 10";

  final List<List<List<String>>> substitutedNames = new ArrayList<>();
  try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) {
    MaterializationService.setThreadLocal();
    CalciteAssert.that()
        .withMaterializations(HR_FKUK_MODEL,
            "m0", m)
        .query(q)
        .withHook(Hook.SUB, (Consumer<RelNode>) r ->
            substitutedNames.add(new TableNameVisitor().run(r)))
        .enableMaterializations(true)
        .explainContains("hr, m0");
  } catch (Exception e) {
    // Table "dependents" not modifiable.
  }
  assertThat(substitutedNames, is(list3(new String[][][]{{{"hr", "m0"}}})));
}
 

@Test void testViewMaterialization() {
  try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) {
    MaterializationService.setThreadLocal();
    String materialize = "select \"depts\".\"name\"\n"
        + "from \"depts\"\n"
        + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")";
    String query = "select \"depts\".\"name\"\n"
        + "from \"depts\"\n"
        + "join \"emps\" on (\"emps\".\"deptno\" = \"depts\".\"deptno\")";

    CalciteAssert.that()
        .withMaterializations(HR_FKUK_MODEL, true, "matview", materialize)
        .query(query)
        .enableMaterializations(true)
        .explainMatches(
            "", CalciteAssert.checkResultContains(
            "EnumerableValues(tuples=[[{ 'noname' }]])")).returnsValue("noname");
  }
}
 

private MaterializedViewTableMacro(CalciteSchema schema, String viewSql,
    List<String> viewSchemaPath, List<String> viewPath, String suggestedTableName,
    boolean existing) {
  super(schema, viewSql,
      viewSchemaPath != null ? viewSchemaPath : schema.path(null), viewPath,
      Boolean.TRUE);
  this.key = Objects.requireNonNull(
      MaterializationService.instance().defineMaterialization(
          schema, null, viewSql, schemaPath, suggestedTableName, true,
          existing));
}
 

private void checkTileAlgorithm(String statisticProvider,
    String expectedExplain) {
  final RelOptRule[] rules = {
      MaterializedViewProjectFilterRule.INSTANCE,
      MaterializedViewOnlyFilterRule.INSTANCE,
      MaterializedViewProjectJoinRule.INSTANCE,
      MaterializedViewOnlyJoinRule.INSTANCE,
      MaterializedViewProjectAggregateRule.INSTANCE,
      MaterializedViewOnlyAggregateRule.INSTANCE
  };
  MaterializationService.setThreadLocal();
  MaterializationService.instance().clear();
  foodmartLatticeModel(statisticProvider)
      .query("select distinct t.\"the_year\", t.\"quarter\"\n"
          + "from \"foodmart\".\"sales_fact_1997\" as s\n"
          + "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n")
      .enableMaterializations(true)

  // Disable materialization rules from this test. For some reason, there is
  // a weird interaction between these rules and the lattice rewriting that
  // produces non-deterministic rewriting (even when only lattices are present).
  // For more context, see
  // <a href="https://issues.apache.org/jira/browse/CALCITE-2953">[CALCITE-2953]</a>.
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner ->
          Arrays.asList(rules).forEach(planner::removeRule))

  // disable for MySQL; times out running star-join query
  // disable for H2; it thinks our generated SQL has invalid syntax
      .enable(CalciteAssert.DB != CalciteAssert.DatabaseInstance.MYSQL
          && CalciteAssert.DB != CalciteAssert.DatabaseInstance.H2)
      .explainContains(expectedExplain)
      .returnsUnordered("the_year=1997; quarter=Q1",
          "the_year=1997; quarter=Q2",
          "the_year=1997; quarter=Q3",
          "the_year=1997; quarter=Q4");
}
 

@Test void testMaterializationSubstitution2() {
  String q = "select *\n"
      + "from (select * from \"emps\" where \"empid\" < 300)\n"
      + "join (select * from \"emps\" where \"empid\" < 200) using (\"empid\")";

  final String[][][] expectedNames = {
      {{"hr", "emps"}, {"hr", "m0"}},
      {{"hr", "emps"}, {"hr", "m1"}},
      {{"hr", "emps"}, {"hr", "m2"}},
      {{"hr", "m0"}, {"hr", "emps"}},
      {{"hr", "m0"}, {"hr", "m0"}},
      {{"hr", "m0"}, {"hr", "m1"}},
      {{"hr", "m0"}, {"hr", "m2"}},
      {{"hr", "m1"}, {"hr", "emps"}},
      {{"hr", "m1"}, {"hr", "m0"}},
      {{"hr", "m1"}, {"hr", "m1"}},
      {{"hr", "m1"}, {"hr", "m2"}},
      {{"hr", "m2"}, {"hr", "emps"}},
      {{"hr", "m2"}, {"hr", "m0"}},
      {{"hr", "m2"}, {"hr", "m1"}},
      {{"hr", "m2"}, {"hr", "m2"}}};

  try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) {
    MaterializationService.setThreadLocal();
    final List<List<List<String>>> substitutedNames = new ArrayList<>();
    CalciteAssert.that()
        .withMaterializations(HR_FKUK_MODEL,
            "m0", "select * from \"emps\" where \"empid\" < 300",
            "m1", "select * from \"emps\" where \"empid\" < 600",
            "m2", "select * from \"m1\"")
        .query(q)
        .withHook(Hook.SUB, (Consumer<RelNode>) r ->
            substitutedNames.add(new TableNameVisitor().run(r)))
        .enableMaterializations(true)
        .sameResultWithMaterializationsDisabled();
    substitutedNames.sort(CASE_INSENSITIVE_LIST_LIST_COMPARATOR);
    assertThat(substitutedNames, is(list3(expectedNames)));
  }
}
 

@Test void testMaterializationSubstitution() {
  String q = "select *\n"
      + "from (select * from \"emps\" where \"empid\" < 300)\n"
      + "join (select * from \"emps\" where \"empid\" < 200) using (\"empid\")";

  final String[][][] expectedNames = {
      {{"hr", "emps"}, {"hr", "m0"}},
      {{"hr", "emps"}, {"hr", "m1"}},
      {{"hr", "m0"}, {"hr", "emps"}},
      {{"hr", "m0"}, {"hr", "m0"}},
      {{"hr", "m0"}, {"hr", "m1"}},
      {{"hr", "m1"}, {"hr", "emps"}},
      {{"hr", "m1"}, {"hr", "m0"}},
      {{"hr", "m1"}, {"hr", "m1"}}};

  try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) {
    MaterializationService.setThreadLocal();
    final List<List<List<String>>> substitutedNames = new ArrayList<>();
    CalciteAssert.that()
        .withMaterializations(HR_FKUK_MODEL,
            "m0", "select * from \"emps\" where \"empid\" < 300",
            "m1", "select * from \"emps\" where \"empid\" < 600")
        .query(q)
        .withHook(Hook.SUB, (Consumer<RelNode>) r ->
            substitutedNames.add(new TableNameVisitor().run(r)))
        .enableMaterializations(true)
        .sameResultWithMaterializationsDisabled();
    substitutedNames.sort(CASE_INSENSITIVE_LIST_LIST_COMPARATOR);
    assertThat(substitutedNames, is(list3(expectedNames)));
  }
}
 

@Test void testViewSchemaPath() {
  try (TryThreadLocal.Memo ignored = Prepare.THREAD_TRIM.push(true)) {
    MaterializationService.setThreadLocal();
    final String m = "select empno, deptno from emp";
    final String q = "select deptno from scott.emp";
    final List<String> path = ImmutableList.of("SCOTT");
    final JsonBuilder builder = new JsonBuilder();
    final String model = "{\n"
        + "  version: '1.0',\n"
        + "  defaultSchema: 'hr',\n"
        + "  schemas: [\n"
        + JdbcTest.SCOTT_SCHEMA
        + "  ,\n"
        + "    {\n"
        + "      materializations: [\n"
        + "        {\n"
        + "          table: 'm0',\n"
        + "          view: 'm0v',\n"
        + "          sql: " + builder.toJsonString(m) + ",\n"
        + "          viewSchemaPath: " + builder.toJsonString(path)
        + "        }\n"
        + "      ],\n"
        + "      type: 'custom',\n"
        + "      name: 'hr',\n"
        + "      factory: 'org.apache.calcite.adapter.java.ReflectiveSchema$Factory',\n"
        + "      operand: {\n"
        + "        class: 'org.apache.calcite.test.JdbcTest$HrSchema'\n"
        + "      }\n"
        + "    }\n"
        + "  ]\n"
        + "}\n";
    CalciteAssert.that()
        .withModel(model)
        .query(q)
        .enableMaterializations(true)
        .explainMatches("", CONTAINS_M0)
        .sameResultWithMaterializationsDisabled();
  }
}
 

@Override public RelNode toRel(RelOptTable.ToRelContext context,
    RelOptTable relOptTable) {
  final CalciteSchema.TableEntry tableEntry =
      MaterializationService.instance().checkValid(key);
  if (tableEntry != null) {
    Table materializeTable = tableEntry.getTable();
    if (materializeTable instanceof TranslatableTable) {
      TranslatableTable table = (TranslatableTable) materializeTable;
      return table.toRel(context, relOptTable);
    }
  }
  return super.toRel(context, relOptTable);
}
 

@Override protected List<Materialization> getMaterializations() {
  final List<Prepare.Materialization> materializations =
      context.config().materializationsEnabled()
          ? MaterializationService.instance().query(schema)
          : ImmutableList.of();
  for (Prepare.Materialization materialization : materializations) {
    prepare.populateMaterializations(context, cluster, materialization);
  }
  return materializations;
}
 

/** Called after the constructor has completed and the model has been
 * loaded. */
void init() {
  final MaterializationService service = MaterializationService.instance();
  for (CalciteSchema.LatticeEntry e : Schemas.getLatticeEntries(rootSchema)) {
    final Lattice lattice = e.getLattice();
    for (Lattice.Tile tile : lattice.computeTiles()) {
      service.defineTile(lattice, tile.bitSet(), tile.measures, e.schema,
          true, true);
    }
  }
}
 

/** Executes a {@code CREATE MATERIALIZED VIEW} command. */
public void execute(SqlCreateMaterializedView create,
    CalcitePrepare.Context context) {
  final Pair<CalciteSchema, String> pair = schema(context, true, create.name);
  if (pair.left.plus().getTable(pair.right) != null) {
    // Materialized view exists.
    if (!create.ifNotExists) {
      // They did not specify IF NOT EXISTS, so give error.
      throw SqlUtil.newContextException(create.name.getParserPosition(),
          RESOURCE.tableExists(pair.right));
    }
    return;
  }
  final SqlNode q = renameColumns(create.columnList, create.query);
  final String sql = q.toSqlString(CalciteSqlDialect.DEFAULT).getSql();
  final List<String> schemaPath = pair.left.path(null);
  final ViewTableMacro viewTableMacro =
      ViewTable.viewMacro(pair.left.plus(), sql, schemaPath,
          context.getObjectPath(), false);
  final TranslatableTable x = viewTableMacro.apply(ImmutableList.of());
  final RelDataType rowType = x.getRowType(context.getTypeFactory());

  // Table does not exist. Create it.
  final MaterializedViewTable table =
      new MaterializedViewTable(pair.right, RelDataTypeImpl.proto(rowType));
  pair.left.add(pair.right, table);
  populate(create.name, create.query, context);
  table.key =
      MaterializationService.instance().defineMaterialization(pair.left, null,
          sql, schemaPath, pair.right, true, true);
}
 

public RelNode run(RelOptPlanner planner, RelNode rel,
                   RelTraitSet requiredOutputTraits,
                   List<RelOptMaterialization> materializations,
                   List<RelOptLattice> lattices) {
  planner.clear();

  planner.addRelTraitDef(ConventionTraitDef.INSTANCE);

  planner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
  //((VolcanoPlanner) planner).registerAbstractRelationalRules();

  RelOptUtil.registerAbstractRels(planner);
  for (RelOptRule rule : ruleSet) {
    planner.addRule(rule);
  }

  planner.addRule(Bindables.BINDABLE_TABLE_SCAN_RULE);
  planner.addRule(ProjectTableScanRule.INSTANCE);
  planner.addRule(ProjectTableScanRule.INTERPRETER);
  planner.addRule(EnumerableInterpreterRule.INSTANCE);

  final CalciteSchema rootSchema = CalciteSchema.from(context.getRootSchema());
  planner.setExecutor(new RexExecutorImpl(null));
  planner.setRoot(rel);

  MaterializationService.setThreadLocal(materializationService);
  plannerHolder.setPlanner(planner);
  populateMaterializationsAndLattice(plannerHolder, rootSchema);
  if (!rel.getTraitSet().equals(requiredOutputTraits)) {
    rel = planner.changeTraits(rel, requiredOutputTraits);
    planner.setRoot(rel);
  }

  RelOptPlanner planner2 = planner.chooseDelegate();
  return planner2.findBestExp();
}
 

@BeforeEach
public void setup() {
  MaterializationService.setThreadLocal();
}
 

@BeforeEach public void setup() {
  MaterializationService.setThreadLocal();
}
 

/** Retrieves a materialized table that will satisfy an aggregate query on
 * the star table.
 *
 * <p>The current implementation creates a materialization and populates it,
 * provided that {@link Lattice#auto} is true.
 *
 * <p>Future implementations might return materializations at a different
 * level of aggregation, from which the desired result can be obtained by
 * rolling up.
 *
 * @param planner Current planner
 * @param groupSet Grouping key
 * @param measureList Calls to aggregate functions
 * @return Materialized table
 */
public Pair<CalciteSchema.TableEntry, TileKey> getAggregate(
    RelOptPlanner planner, ImmutableBitSet groupSet,
    List<Lattice.Measure> measureList) {
  final CalciteConnectionConfig config =
      planner.getContext().unwrap(CalciteConnectionConfig.class);
  if (config == null) {
    return null;
  }
  final MaterializationService service = MaterializationService.instance();
  boolean create = lattice.auto && config.createMaterializations();
  final CalciteSchema schema = starRelOptTable.unwrap(CalciteSchema.class);
  return service.defineTile(lattice, groupSet, measureList, schema, create,
      false);
}
 

/** Retrieves a materialized table that will satisfy an aggregate query on
 * the star table.
 *
 * <p>The current implementation creates a materialization and populates it,
 * provided that {@link Lattice#auto} is true.
 *
 * <p>Future implementations might return materializations at a different
 * level of aggregation, from which the desired result can be obtained by
 * rolling up.
 *
 * @param planner Current planner
 * @param groupSet Grouping key
 * @param measureList Calls to aggregate functions
 * @return Materialized table
 */
public Pair<CalciteSchema.TableEntry, TileKey> getAggregate(
    RelOptPlanner planner, ImmutableBitSet groupSet,
    List<Lattice.Measure> measureList) {
  final CalciteConnectionConfig config =
      planner.getContext().unwrap(CalciteConnectionConfig.class);
  if (config == null) {
    return null;
  }
  final MaterializationService service = MaterializationService.instance();
  boolean create = lattice.auto && config.createMaterializations();
  final CalciteSchema schema = starRelOptTable.unwrap(CalciteSchema.class);
  return service.defineTile(lattice, groupSet, measureList, schema, create,
      false);
}