Hi Stamatis,
I have converted my testcase to UT, please find it in the attachment.
-Regards
Kumar Vishal
On Sat, Dec 22, 2018 at 1:32 PM Stamatis Zampetakis <[email protected]>
wrote:
> Hi Kumar,
>
> I think it would be helpful if you could shape your code into a unit test
> and share it with us. It will be easier for people to understand what is
> happening
> and possibly give you some hints.
>
> Best,
> Stamatis
>
> Στις Παρ, 21 Δεκ 2018 στις 6:17 μ.μ., ο/η Kumar Vishal <
> [email protected]> έγραψε:
>
> > Hi Stamatis,
> >
> > I have referred MaterializationTest as you suggested, I have created a
> > Schema similar to one present in the testcase and added Materialized
> View.
> > I have created HepPlanner with all the rules present in
> > AbstractMaterializedViewRule but when I am applying rules it's able to
> > select the Materialized View but not able to rewrite the query.
> >
> > Can you please suggest me any clue to proceed?
> >
> >
> > - Regards
> > Kumar Vishal
> >
> > On Tue, Dec 11, 2018 at 12:51 PM Kumar Vishal <[email protected]
> >
> > wrote:
> >
> > > Hi Stamatis,
> > >
> > > Thanks for the info, I will check this and get back to you.
> > >
> > > -Regards
> > > Kumar Vishal
> > >
> > > On Sun, Dec 9, 2018 at 6:46 PM Stamatis Zampetakis <[email protected]>
> > > wrote:
> > >
> > >> Hi Kumar,
> > >>
> > >> Yes, it is possible.
> > >>
> > >> As long as you have your schema defined you can create a Planner
> > (Volcano,
> > >> or Heuristic) with the appropriate rules for view based rewritting and
> > >> obtain a plan that is using the views. Then you can pass from a plan
> > back
> > >> to sql using RelToSqlConverter.
> > >>
> > >> You might find interesting the discussion in [1]. You can also have a
> > look
> > >> in the MaterializationTest which can serve as an entry point in order
> to
> > >> understand how the rewritting works.
> > >>
> > >> Best,
> > >> Stamatis
> > >>
> > >> [1]
> > >>
> > >>
> >
> http://mail-archives.apache.org/mod_mbox/calcite-dev/201810.mbox/%3CAM0PR07MB5185C3F75488AA768815CF4FCCFD0%40AM0PR07MB5185.eurprd07.prod.outlook.com%3E
> > >>
> > >> Στις Παρ, 7 Δεκ 2018 στις 5:15 μ.μ., ο/η Kumar Vishal <
> > >> [email protected]> έγραψε:
> > >>
> > >> > Hi Dev,
> > >> >
> > >> > I am a new bee and analysing Calcite Materialized View. In my
> > scenario I
> > >> > have multiple execution engines which have their own parser and
> > >> > optimizer(Some do not have integration with calcite). I want to
> > register
> > >> > all the tables and Materialized Views to Calcite and handle commonly
> > for
> > >> > all execution engines.
> > >> >
> > >> > i.e. I want to redirect User queries to calcite and get back the
> > >> rewritten
> > >> > sql (by selecting best fit Materialized View), so rewritten sql can
> be
> > >> > executed by execution engine.
> > >> >
> > >> > Is there anyway to get the rewritten sql based on Materialized
> View??
> > >> >
> > >> > -Regards
> > >> > Kumar Vishal
> > >> >
> > >>
> > >
> >
>
package org.apache.calcite.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Properties;
import java.util.function.Function;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import org.apache.calcite.adapter.java.ReflectiveSchema;
import org.apache.calcite.avatica.ConnectionProperty;
import org.apache.calcite.config.CalciteConnectionConfig;
import org.apache.calcite.config.CalciteConnectionProperty;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.jdbc.CalciteSchema;
import org.apache.calcite.jdbc.JavaTypeFactoryImpl;
import org.apache.calcite.model.JsonMaterialization;
import org.apache.calcite.plan.Context;
import org.apache.calcite.plan.Contexts;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptLattice;
import org.apache.calcite.plan.RelOptMaterialization;
import org.apache.calcite.plan.RelOptPlanner;
import org.apache.calcite.plan.RelTraitSet;
import org.apache.calcite.plan.hep.HepPlanner;
import org.apache.calcite.plan.hep.HepProgram;
import org.apache.calcite.plan.hep.HepProgramBuilder;
import org.apache.calcite.prepare.CalciteCatalogReader;
import org.apache.calcite.prepare.Prepare;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.RelReferentialConstraint;
import org.apache.calcite.rel.RelReferentialConstraintImpl;
import org.apache.calcite.rel.RelRoot;
import org.apache.calcite.rel.rules.AbstractMaterializedViewRule;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeSystem;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.runtime.FlatLists;
import org.apache.calcite.schema.QueryableTable;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.schema.TranslatableTable;
import org.apache.calcite.schema.impl.MaterializedViewTable;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlOperatorTable;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.type.SqlTypeFactoryImpl;
import org.apache.calcite.sql.validate.SqlConformance;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.apache.calcite.sql.validate.SqlNameMatchers;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql.validate.SqlValidatorCatalogReader;
import org.apache.calcite.sql.validate.SqlValidatorImpl;
import org.apache.calcite.sql2rel.SqlToRelConverter;
import org.apache.calcite.sql2rel.StandardConvertletTable;
import org.apache.calcite.tools.Program;
import org.apache.calcite.util.JsonBuilder;
import org.apache.calcite.util.Smalls;
import org.apache.calcite.util.mapping.IntPair;
import org.junit.Test;
public class MyTestCase {
public static final CalciteAssert.ConnectionFactory EMPTY_CONNECTION_FACTORY =
new MapConnectionFactory(ImmutableMap.of(), ImmutableList.of());
private CalciteAssert.ConnectionFactory connectionFactory;
public MyTestCase() {
this.connectionFactory = Objects.requireNonNull(EMPTY_CONNECTION_FACTORY);
}
private static final String HR_FKUK_SCHEMA =
"{\n" + " type: 'custom',\n" + " name: 'hr',\n" + " factory: '"
+ ReflectiveSchema.Factory.class.getName() + "',\n" + " operand: {\n"
+ " class: '" + HrFKUKSchema.class.getName() + "'\n" + " }\n" + " }\n";
private static final String HR_FKUK_MODEL =
"{\n" + " version: '1.0',\n" + " defaultSchema: 'hr',\n" + " schemas: [\n"
+ HR_FKUK_SCHEMA + " ]\n" + "}";
/**
* Adds materializations to the schema.
*/
public final void withMaterializations(String model,
Function<JsonBuilder, List<Object>> materializations) {
final JsonBuilder builder = new JsonBuilder();
final List<Object> list = materializations.apply(builder);
final String buf = "materializations: " + builder.toJsonString(list);
final String model2;
if (model.contains("defaultSchema: 'foodmart'")) {
int endIndex = model.lastIndexOf(']');
model2 = model.substring(0, endIndex) + ", \n{ name: 'mat', " + buf + "}\n" + "]" + model
.substring(endIndex + 1);
} else if (model.contains("type: ")) {
model2 = model
.replaceFirst("type: ", java.util.regex.Matcher.quoteReplacement(buf + ",\n" + "type: "));
} else {
throw new AssertionError("do not know where to splice");
}
withModel(model2);
}
public final void withModel(String model) {
with(CalciteConnectionProperty.MODEL, "inline:" + model);
}
@Test public void testFilter() throws SQLException {
withMaterializations(HR_FKUK_MODEL, "m0",
"select * from \"emps\" where \"deptno\" = 10");
Connection connection = null;
try {
connection = connectionFactory.createConnection();
} catch (SQLException e) {
e.printStackTrace();
}
final CalciteConnection calciteConnection = (CalciteConnection) connection;
final SchemaPlus rootSchema = calciteConnection.getRootSchema();
SchemaPlus schema = rootSchema.getSubSchema("hr");
JsonMaterialization jsonMaterialization = new JsonMaterialization();
jsonMaterialization.view = "m1v";
jsonMaterialization.table = "m1";
jsonMaterialization.sql = "select \"empid\" + 1 from \"emps\" where \"deptno\" = 10";
// visit(jsonMaterialization, schema, "hr");
final CalciteConnectionConfig config = calciteConnection.config();
final Context context = Contexts.of(config);
CalciteSchema calciteSchema = CalciteSchema.from(schema);
final DiffRepository lookup = DiffRepository.lookup(SqlToRelConverterTest.class);
String sql = "select deptno from emps where deptno = 10";
String sql2 = lookup.expand("sql", sql);
final RelRoot project = convertSqlToRel(sql2, calciteSchema, config, context, schema, connection);
HepProgramBuilder programBuilder = HepProgram.builder();
programBuilder.addRuleClass(AbstractMaterializedViewRule.INSTANCE_AGGREGATE.getClass());
programBuilder.addRuleClass(AbstractMaterializedViewRule.INSTANCE_FILTER.getClass());
programBuilder.addRuleClass(AbstractMaterializedViewRule.INSTANCE_JOIN.getClass());
programBuilder.addRuleClass(AbstractMaterializedViewRule.INSTANCE_PROJECT_AGGREGATE.getClass());
programBuilder.addRuleClass(AbstractMaterializedViewRule.INSTANCE_PROJECT_FILTER.getClass());
programBuilder.addRuleClass(AbstractMaterializedViewRule.INSTANCE_PROJECT_JOIN.getClass());
// programBuilder.addRuleClass(AbstractMaterializedViewRule.INSTANCE_AGGREGATE.getClass());
HepPlanner planner = new HepPlanner(programBuilder.build());
planner.addRule(AbstractMaterializedViewRule.INSTANCE_AGGREGATE);
planner.addRule(AbstractMaterializedViewRule.INSTANCE_FILTER);
planner.addRule(AbstractMaterializedViewRule.INSTANCE_JOIN);
planner.addRule(AbstractMaterializedViewRule.INSTANCE_PROJECT_AGGREGATE);
planner.addRule(AbstractMaterializedViewRule.INSTANCE_PROJECT_FILTER);
planner.addRule(AbstractMaterializedViewRule.INSTANCE_PROJECT_JOIN);
planner.setRoot(project.rel);
String sql3 = "select deptno from emps where deptno = 10";
String sql4 = lookup.expand("sql", sql3);
RelNode tableRel = convertSqlToRel(sql4, calciteSchema, config, context, schema, connection).rel;
RelNode queryRel = tableRel;
RelOptMaterialization mat1 = new RelOptMaterialization(
project.rel, project.rel, null, ImmutableList.of("m0", "m0"));
planner.addMaterialization(mat1);
final RelNode bestExp = planner.findBestExp();
System.out.println();
}
public SqlNode parseQuery(String sql) throws Exception {
final SqlParser.Config config =
SqlParser.configBuilder().setConformance(SqlConformanceEnum.DEFAULT).build();
SqlParser parser = SqlParser.create(sql, config);
return parser.parseQuery();
}
private static class MockCatalogReader extends CalciteCatalogReader {
static final String DEFAULT_SCHEMA = "hr";
static final List<String> PREFIX = ImmutableList.of(DEFAULT_SCHEMA);
public MockCatalogReader(CalciteSchema calciteSchema, RelDataTypeFactory relDataTypeFactory) {
super(calciteSchema,
SqlNameMatchers.withCaseSensitive(false), ImmutableList.of(PREFIX, ImmutableList.of()),
relDataTypeFactory, null);
}
}
public SqlValidator createValidator(SqlValidatorCatalogReader catalogReader,
RelDataTypeFactory typeFactory) {
return new FarragoTestValidator(createOperatorTable(), catalogReader, typeFactory,
SqlConformanceEnum.DEFAULT);
}
/**
* Validator for testing.
*/
private static class FarragoTestValidator extends SqlValidatorImpl {
FarragoTestValidator(SqlOperatorTable opTab, SqlValidatorCatalogReader catalogReader,
RelDataTypeFactory typeFactory, SqlConformance conformance) {
super(opTab, catalogReader, typeFactory, conformance);
}
// override SqlValidator
public boolean shouldExpandIdentifiers() {
return true;
}
}
public RelRoot convertSqlToRel(String sql, CalciteSchema calciteSchema,
CalciteConnectionConfig calciteConfig, Context context, SchemaPlus schemaPlus, Connection connection) {
Objects.requireNonNull(sql);
final SqlNode sqlQuery;
final SqlToRelConverter.Config localConfig;
try {
sqlQuery = parseQuery(sql);
} catch (RuntimeException | Error e) {
throw e;
} catch (Exception e) {
throw new RuntimeException(e);
}
final RelDataTypeFactory typeFactory = new JavaTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
final Prepare.CatalogReader catalogReader = new MockCatalogReader(calciteSchema, typeFactory);
final SqlValidator validator = createValidator(catalogReader, typeFactory);
if (calciteConfig != null) {
validator.setDefaultNullCollation(calciteConfig.defaultNullCollation());
}
SqlToRelConverter.Config config = SqlToRelConverter.Config.DEFAULT;
if (config == SqlToRelConverter.Config.DEFAULT) {
localConfig =
SqlToRelConverter.configBuilder().withTrimUnusedFields(true).withExpand(true).build();
} else {
localConfig = config;
}
final SqlToRelConverter converter =
createSqlToRelConverter(validator, catalogReader, typeFactory, localConfig, context, schemaPlus, connection);
final SqlNode validatedQuery = validator.validate(sqlQuery);
RelRoot root = converter.convertQuery(validatedQuery, false, true);
return root;
}
protected RelDataTypeFactory createTypeFactory() {
return new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
}
public RelOptPlanner createPlanner(Context context, Connection connection, SchemaPlus schemaPlus) {
return new MockRelOptPlanner(context, connection, schemaPlus);
}
protected SqlToRelConverter createSqlToRelConverter(
final SqlValidator validator,
final Prepare.CatalogReader catalogReader,
final RelDataTypeFactory typeFactory,
final SqlToRelConverter.Config config,
Context context,
SchemaPlus schemaPlus,
Connection connection) {
final RexBuilder rexBuilder = new RexBuilder(typeFactory);
RelOptCluster cluster =
RelOptCluster.create(createPlanner(context, connection, schemaPlus), rexBuilder);
// cluster.traitSetOf(new RelTrait[] {EnumerableConvention.INSTANCE});
// if (clusterFactory != null) {
// cluster = clusterFactory.apply(cluster);
// }
return new SqlToRelConverter(null, validator, catalogReader, cluster,
StandardConvertletTable.INSTANCE, config);
}
/**
* Creates an operator table.
*
* @return New operator table
*/
protected SqlOperatorTable createOperatorTable() {
final MockSqlOperatorTable opTab =
new MockSqlOperatorTable(SqlStdOperatorTable.instance());
MockSqlOperatorTable.addRamp(opTab);
return opTab;
}
private void visit(JsonMaterialization jsonMaterialization, SchemaPlus schema, String name) {
try {
if (!schema.isMutable()) {
throw new RuntimeException("Cannot define materialization; parent schema '" + name
+ "' is not a SemiMutableSchema");
}
CalciteSchema calciteSchema = CalciteSchema.from(schema);
final String viewName;
final boolean existing;
if (jsonMaterialization.view == null) {
// If the user did not supply a view name, that means the materialized
// view is pre-populated. Generate a synthetic view name.
viewName = "$" + schema.getTableNames().size();
existing = true;
} else {
viewName = jsonMaterialization.view;
existing = false;
}
List<String> viewPath = calciteSchema.path(viewName);
final MaterializedViewTable.MaterializedViewTableMacro materializedViewTableMacro =
MaterializedViewTable.create(calciteSchema, jsonMaterialization.getSql(),
jsonMaterialization.viewSchemaPath, viewPath, jsonMaterialization.table, existing);
schema.add(viewName, MaterializedViewTable
.create(calciteSchema, jsonMaterialization.getSql(), jsonMaterialization.viewSchemaPath,
viewPath, jsonMaterialization.table, existing));
} catch (Exception e) {
throw new RuntimeException("Error instantiating " + jsonMaterialization, e);
}
}
public final void withMaterializations(String model, final String... materializations) {
withMaterializations(model, false, materializations);
}
public void with(ConnectionProperty property, Object value) {
if (!property.type().valid(value, property.valueClass())) {
throw new IllegalArgumentException();
}
connectionFactory = connectionFactory.with(property, value);
}
/**
* Adds materializations to the schema.
*/
public final void withMaterializations(String model, final boolean existing,
final String... materializations) {
withMaterializations(model, builder -> {
assert materializations.length % 2 == 0;
final List<Object> list = builder.list();
for (int i = 0; i < materializations.length; i++) {
String table = materializations[i++];
final Map<String, Object> map = builder.map();
map.put("table", table);
if (!existing) {
map.put("view", table + "v");
}
String sql = materializations[i];
final String sql2 = sql.replaceAll("`", "\"");
map.put("sql", sql2);
list.add(map);
}
return list;
});
}
/**
* Hr schema with FK-UK relationship.
*/
public static class HrFKUKSchema {
@Override public String toString() {
return "HrFKUKSchema";
}
public final JdbcTest.Employee[] emps = { new JdbcTest.Employee(100, 10, "Bill", 10000, 1000),
new JdbcTest.Employee(200, 20, "Eric", 8000, 500),
new JdbcTest.Employee(150, 10, "Sebastian", 7000, null),
new JdbcTest.Employee(110, 10, "Theodore", 10000, 250), };
public final JdbcTest.Department[] depts =
{ new JdbcTest.Department(10, "Sales", Arrays.asList(emps[0], emps[2], emps[3]),
new JdbcTest.Location(-122, 38)),
new JdbcTest.Department(30, "Marketing", ImmutableList.of(),
new JdbcTest.Location(0, 52)),
new JdbcTest.Department(20, "HR", Collections.singletonList(emps[1]), null), };
public final JdbcTest.Dependent[] dependents =
{ new JdbcTest.Dependent(10, "Michael"), new JdbcTest.Dependent(10, "Jane"), };
public final JdbcTest.Dependent[] locations =
{ new JdbcTest.Dependent(10, "San Francisco"), new JdbcTest.Dependent(20, "San Diego"), };
public final JdbcTest.Event[] events =
{ new JdbcTest.Event(100, new Timestamp(0)), new JdbcTest.Event(200, new Timestamp(0)),
new JdbcTest.Event(150, new Timestamp(0)), new JdbcTest.Event(110, null), };
public final RelReferentialConstraint rcs0 = RelReferentialConstraintImpl
.of(ImmutableList.of("hr", "emps"), ImmutableList.of("hr", "depts"),
ImmutableList.of(IntPair.of(1, 0)));
public QueryableTable foo(int count) {
return Smalls.generateStrings(count);
}
public TranslatableTable view(String s) {
return Smalls.view(s);
}
public TranslatableTable matview() {
return Smalls.strView("noname");
}
}
/**
* Connection factory that uses a given map of (name, value) pairs and
* optionally an initial schema.
*/
private static class MapConnectionFactory extends CalciteAssert.ConnectionFactory {
private final ImmutableMap<String, String> map;
private final ImmutableList<CalciteAssert.ConnectionPostProcessor> postProcessors;
private MapConnectionFactory(ImmutableMap<String, String> map,
ImmutableList<CalciteAssert.ConnectionPostProcessor> postProcessors) {
this.map = Objects.requireNonNull(map);
this.postProcessors = Objects.requireNonNull(postProcessors);
}
@Override public boolean equals(Object obj) {
return this == obj
|| obj.getClass() == MapConnectionFactory.class && ((MapConnectionFactory) obj).map
.equals(map) && ((MapConnectionFactory) obj).postProcessors.equals(postProcessors);
}
@Override public int hashCode() {
return Objects.hash(map, postProcessors);
}
public Connection createConnection() throws SQLException {
final Properties info = new Properties();
for (Map.Entry<String, String> entry : map.entrySet()) {
info.setProperty(entry.getKey(), entry.getValue());
}
Connection connection = DriverManager.getConnection("jdbc:calcite:", info);
for (CalciteAssert.ConnectionPostProcessor postProcessor : postProcessors) {
connection = postProcessor.apply(connection);
}
return connection;
}
public CalciteAssert.ConnectionFactory with(String property, Object value) {
return new MapConnectionFactory(FlatLists.append(this.map, property, value.toString()),
postProcessors);
}
public CalciteAssert.ConnectionFactory with(ConnectionProperty property, Object value) {
if (!property.type().valid(value, property.valueClass())) {
throw new IllegalArgumentException();
}
return with(property.camelName(), value.toString());
}
public CalciteAssert.ConnectionFactory with(
CalciteAssert.ConnectionPostProcessor postProcessor) {
ImmutableList.Builder<CalciteAssert.ConnectionPostProcessor> builder =
ImmutableList.builder();
builder.addAll(postProcessors);
builder.add(postProcessor);
return new MapConnectionFactory(map, builder.build());
}
}
}
