[
https://issues.apache.org/jira/browse/CALCITE-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Water Cut Off updated CALCITE-3301:
-----------------------------------
Description:
* *{color:#00875a}I want to use calcite to parse view, and convert view to my
own RelNode, but I am not sure how to use ViewExpander ,can you give me an
example to solve this problem, thanks ?{color}*
public class PrestoView implements RelOptTable.ViewExpander {
private final SqlValidator validator;
private final Prepare.CatalogReader catalogReader;
private final RelOptCluster cluster;
private final SqlToRelConverter.Config config;
public PrestoView(SqlValidator validator, Prepare.CatalogReader catalogReader,
RelOptCluster cluster, SqlToRelConverter.Config config)
{ this.validator = validator; this.catalogReader = catalogReader; this.cluster
= cluster; this.config = config; }
@Override
public RelRoot expandView(RelDataType rowType, String queryString,
List<String> schemaPath, List<String> viewPath) {
try
{ SqlNode parsedNode = SqlParser.create(queryString).parseStmt(); SqlNode
validatedNode = validator.validate(parsedNode); SqlToRelConverter converter =
new SqlToRelConverter(this, validator, catalogReader, cluster,
StandardConvertletTable.INSTANCE, config); return
converter.convertQuery(validatedNode, false, true); }
catch (SqlParseException e)
{ throw new RuntimeException("Error happened while expanding view.", e); }
}
--------------------------------------------------------------
public class PrestoViewTable extends AbstractTable implements TranslatableTable
{
private final String vName;
private final String viewSql;
public PrestoViewTable(String vName, String viewSql)
{ super(); this.vName = vName; this.viewSql = viewSql; }
public String getVName()
{ return vName; }
@Override
public RelNode toRel(RelOptTable.ToRelContext context, RelOptTable relOptTable)
{ return expandView(context, relOptTable.getRowType(), viewSql).rel; }
@Override
public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
RelDataTypeFactory.Builder builder = typeFactory.builder();
builder.add("empid", new BasicSqlType(new RelDataTypeSystemImpl() {
}, SqlTypeName.INTEGER));
builder.add("deptno", new BasicSqlType(new RelDataTypeSystemImpl() {
}, SqlTypeName.VARCHAR));
return builder.build();
}
@Override
public Schema.TableType getJdbcTableType()
{ return Schema.TableType.VIEW; }
private RelRoot expandView(RelOptTable.ToRelContext context, RelDataType
rowType, String queryString) {
try {
final RelRoot root = context.expandView(rowType, queryString, null, null);
final RelNode rel = RelOptUtil.createCastRel(root.rel, rowType, true);
// Expand any views
final RelNode rel2 = rel.accept(new RelShuttleImpl() {
@Override
public RelNode visit(TableScan scan) {
final RelOptTable table = scan.getTable();
final TranslatableTable translatableTable =
table.unwrap(TranslatableTable.class);
if (translatableTable != null)
{ return translatableTable.toRel(context, table); }
return super.visit(scan);
}
});
return root.withRel(rel2);
} catch (Exception e)
{ throw new RuntimeException("Error while parsing view definition: " +
queryString, e); }
}
}
---------------------------------------------------------------------------
public static SchemaPlus registerRootSchema(List<PrestoTable> tabList,
List<PrestoViewTable> viewTables) {
SchemaPlus rootSchema = Frameworks.createRootSchema(true);
for (PrestoTable table : tabList)
{ rootSchema.add(table.getTableName(), table); }
List<PrestoViewTable> vts = new ArrayList<>();
PrestoViewTable pt = new PrestoViewTable("V_EMP", "select * from emp where
empid>10");
vts.add(pt);
//
// for (PrestoViewTable pv : viewTables)
{ // rootSchema.add(pv.getVName(), pv); // }
return rootSchema;
}
{color:#00875a}----------------------------*Test----*---------------------------------------------------------------------------------------------------{color}
public class CalciteTest {
public static void main(String[] args)
{ // String sql = "select * from dept "; String sql1 = "select a.* from dept a
left join emp b on a.deptno=b.deptno where a.name in('compute','aab') and
b.deptno='aa' order by a.deptno limit 10"; String sql2 = "select a.name from
dept a left join emp b on a.deptno=b.deptno where a.name in('compute','aab')
and b.deptno='aa'"; String sql3 = "select count(distinct deptno) from dept
where deptno='aa'"; String sql4 = "select * from v_emp"; // sqlToRelNode(sql);
sqlToRelNode(sql4); }
private static RelNode sqlToRelNode(String sql) {
SchemaPlus rootSchema = CalciteUtils.registerRootSchema(createTables(),
createVTables());
//expandView(rootSchema);
final FrameworkConfig fromworkConfig =
Frameworks.newConfigBuilder().parserConfig(SqlParser.Config.DEFAULT).defaultSchema(rootSchema).traitDefs(ConventionTraitDef.INSTANCE,
RelDistributionTraitDef.INSTANCE).build();
try {
//---------------------SQL to SqlNode---------------------------------
SqlTypeFactoryImpl factory = new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
// sql parser
SqlParser parser = SqlParser.create(sql, SqlParser.Config.DEFAULT);
SqlNode parsed = parser.parseStmt();
CalciteCatalogReader calciteCatalogReader = new
CalciteCatalogReader(CalciteSchema.from(rootSchema),
CalciteSchema.from(rootSchema).path(null), factory, new
CalciteConnectionConfigImpl(new Properties()));
// sql validate
SqlValidator validator =
SqlValidatorUtil.newValidator(SqlStdOperatorTable.instance(),
calciteCatalogReader, factory, CalciteUtils.conformance(fromworkConfig));
SqlNode validated = validator.validate(parsed);
//---------------------Planner---------------------------------
VolcanoPlanner planner = new PrestoVolcanoPlanner();
planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
planner.addRelTraitDef(RelDistributionTraitDef.INSTANCE);
final RelOptCluster relOptCluster = RelOptCluster.create(planner, new
RexBuilder(new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT)));
//add RelMetadataProvider
relOptCluster.setMetadataProvider(PrestoRelMetaDataProvider.INSTANCE);
// SqlNode toRelNode
final SqlToRelConverter.Config config =
SqlToRelConverter.configBuilder().withConfig(fromworkConfig.getSqlToRelConverterConfig()).withTrimUnusedFields(false).withExpand(true).withConvertTableAccess(false).build();
final SqlToRelConverter sqlToRelConverter = new SqlToRelConverter(new
PrestoView(validator, calciteCatalogReader, relOptCluster, config), validator,
calciteCatalogReader, relOptCluster, fromworkConfig.getConvertletTable(),
config);
RelRoot root = sqlToRelConverter.convertQuery(validated, false, true);
root = root.withRel(sqlToRelConverter.flattenTypes(root.rel, true));
RelNode relNode = root.rel;
HepProgramBuilder builder =
HepProgram.builder().addRuleInstance(FilterJoinRule.FilterIntoJoinRule.FILTER_ON_JOIN).addRuleInstance(FilterProjectTransposeRule.INSTANCE).addRuleInstance(FilterJoinRule.FILTER_ON_JOIN).addRuleInstance(ProjectMergeRule.INSTANCE).addRuleInstance(SemiJoinRule.PROJECT);
HepPlanner hepPlanner = new HepPlanner(builder.build());
hepPlanner.setRoot(relNode);
relNode = hepPlanner.findBestExp();
RelTraitSet desiredTraits = relOptCluster.traitSetOf(PrestoRel.CONVENTION);
if (!relNode.getTraitSet().equals(desiredTraits))
{ relNode = planner.changeTraits(relNode, desiredTraits); }
planner.setRoot(relNode);
relNode = planner.findBestExp();
System.out.println("-----------------------------------------------------------");
System.out.println("The Best relational expression string:");
System.out.println(RelOptUtil.toString(relNode,
SqlExplainLevel.ALL_ATTRIBUTES));
System.out.println("-----------------------------------------------------------");
RelOptCost cost = planner.getCost(relNode, PrestoRelMetadataQuery.INSTANCE);
System.out.println(cost.toString());
System.out.println("-----------------------------------------------------------");
} catch (Exception e)
{ e.printStackTrace(); }
return null;
}
private static List<PrestoTable> createTables()
{ List<PrestoTable> tabList = new ArrayList<>(); String[] fields = new
String[]\\{"DEPTNO", "NAME", "LEADER"}
;
String[] fieldTypes = new String[]\{"int", "string", "string"};
List<ColStatistics> cols = new ArrayList<>();
cols.add(new ColStatistics("DEPTNO", 5));
cols.add(new ColStatistics("NAME", 5));
cols.add(new ColStatistics("LEADER", 5));
PrestoTableInfo info1 = new PrestoTableInfo();
info1.setFields(fields);
info1.setFieldTypes(fieldTypes);
info1.setRowCount(5d);
info1.setTableName("DEPT");
info1.setStatistics(cols);
PrestoTable t1 = new PrestoTable(info1);
String[] fields2 = new String[]\{"EMPID", "NAME", "DEPTNO", "SALARY"};
String[] fieldTypes2 = new String[]\{"int", "string", "int", "double"};
List<ColStatistics> cols2 = new ArrayList<>();
cols2.add(new ColStatistics("EMPID", 50));
cols2.add(new ColStatistics("NAME", 50));
cols2.add(new ColStatistics("DEPTNO", 5));
cols2.add(new ColStatistics("SALARY", 5));
PrestoTableInfo info2 = new PrestoTableInfo();
info2.setFields(fields2);
info2.setFieldTypes(fieldTypes2);
info2.setRowCount(50d);
info2.setTableName("EMP");
info2.setStatistics(cols2);
PrestoTable t2 = new PrestoTable(info2);
tabList.add(t1);
tabList.add(t2);
return tabList;
}
===============================================
*{color:#ff0000}ERROR:{color}*
Exception in thread "main" java.lang.AssertionErrorException in thread "main"
java.lang.AssertionError at org.apache.calcite.util.Pair.zip(Pair.java:202) at
org.apache.calcite.rex.RexUtil.generateCastExpressions(RexUtil.java:134) at
org.apache.calcite.rex.RexUtil.generateCastExpressions(RexUtil.java:116) at
org.apache.calcite.plan.RelOptUtil.createCastRel(RelOptUtil.java:749) at
org.apache.calcite.plan.RelOptUtil.createCastRel(RelOptUtil.java:722) at
com.niwodai.pangu.datasearch.calcite.PrestoViewTable.expandView(PrestoViewTable.java:64)
at
com.niwodai.pangu.datasearch.calcite.PrestoViewTable.toRel(PrestoViewTable.java:42)
at org.apache.calcite.prepare.RelOptTableImpl.toRel(RelOptTableImpl.java:269)
at
org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewriteRel(RelStructuredTypeFlattener.java:694)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498) at
org.apache.calcite.util.ReflectUtil.invokeVisitorInternal(ReflectUtil.java:257)
at org.apache.calcite.util.ReflectUtil.invokeVisitor(ReflectUtil.java:214) at
org.apache.calcite.util.ReflectUtil$1.invokeVisitor(ReflectUtil.java:464) at
org.apache.calcite.sql2rel.RelStructuredTypeFlattener$RewriteRelVisitor.visit(RelStructuredTypeFlattener.java:768)
at org.apache.calcite.rel.SingleRel.childrenAccept(SingleRel.java:72) at
org.apache.calcite.rel.RelVisitor.visit(RelVisitor.java:44) at
org.apache.calcite.sql2rel.RelStructuredTypeFlattener$RewriteRelVisitor.visit(RelStructuredTypeFlattener.java:763)
at
org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewrite(RelStructuredTypeFlattener.java:195)
at
org.apache.calcite.sql2rel.SqlToRelConverter.flattenTypes(SqlToRelConverter.java:468)
at
com.niwodai.pangu.datasearch.calcite.CalciteTest.sqlToRelNode(CalciteTest.java:97)
at com.niwodai.pangu.datasearch.calcite.CalciteTest.main(CalciteTest.java:59)
was:
public class PrestoView implements RelOptTable.ViewExpander {
private final SqlValidator validator;
private final Prepare.CatalogReader catalogReader;
private final RelOptCluster cluster;
private final SqlToRelConverter.Config config;
public PrestoView(SqlValidator validator, Prepare.CatalogReader catalogReader,
RelOptCluster cluster, SqlToRelConverter.Config config) {
this.validator = validator;
this.catalogReader = catalogReader;
this.cluster = cluster;
this.config = config;
}
@Override
public RelRoot expandView(RelDataType rowType, String queryString,
List<String> schemaPath, List<String> viewPath) {
try {
SqlNode parsedNode = SqlParser.create(queryString).parseStmt();
SqlNode validatedNode = validator.validate(parsedNode);
SqlToRelConverter converter = new SqlToRelConverter(this, validator,
catalogReader, cluster, StandardConvertletTable.INSTANCE, config);
return converter.convertQuery(validatedNode, false, true);
} catch (SqlParseException e) {
throw new RuntimeException("Error happened while expanding view.", e);
}
}
--------------------------------------------------------------
public class PrestoViewTable extends AbstractTable implements TranslatableTable
{
private final String vName;
private final String viewSql;
public PrestoViewTable(String vName, String viewSql) {
super();
this.vName = vName;
this.viewSql = viewSql;
}
public String getVName() {
return vName;
}
@Override
public RelNode toRel(RelOptTable.ToRelContext context, RelOptTable
relOptTable) {
return expandView(context, relOptTable.getRowType(), viewSql).rel;
}
@Override
public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
RelDataTypeFactory.Builder builder = typeFactory.builder();
builder.add("empid", new BasicSqlType(new RelDataTypeSystemImpl() {
}, SqlTypeName.INTEGER));
builder.add("deptno", new BasicSqlType(new RelDataTypeSystemImpl() {
}, SqlTypeName.VARCHAR));
return builder.build();
}
@Override
public Schema.TableType getJdbcTableType() {
return Schema.TableType.VIEW;
}
private RelRoot expandView(RelOptTable.ToRelContext context, RelDataType
rowType, String queryString) {
try {
final RelRoot root = context.expandView(rowType, queryString, null, null);
final RelNode rel = RelOptUtil.createCastRel(root.rel, rowType, true);
// Expand any views
final RelNode rel2 = rel.accept(new RelShuttleImpl() {
@Override
public RelNode visit(TableScan scan) {
final RelOptTable table = scan.getTable();
final TranslatableTable translatableTable =
table.unwrap(TranslatableTable.class);
if (translatableTable != null) {
return translatableTable.toRel(context, table);
}
return super.visit(scan);
}
});
return root.withRel(rel2);
} catch (Exception e) {
throw new RuntimeException("Error while parsing view definition: " +
queryString, e);
}
}
}
---------------------------------------------------------------------------
public static SchemaPlus registerRootSchema(List<PrestoTable> tabList,
List<PrestoViewTable> viewTables) {
SchemaPlus rootSchema = Frameworks.createRootSchema(true);
for (PrestoTable table : tabList) {
rootSchema.add(table.getTableName(), table);
}
List<PrestoViewTable> vts = new ArrayList<>();
PrestoViewTable pt = new PrestoViewTable("V_EMP", "select * from emp where
empid>10");
vts.add(pt);
//
// for (PrestoViewTable pv : viewTables) {
// rootSchema.add(pv.getVName(), pv);
// }
return rootSchema;
}
--------------------------------------------------------------------------
MyTest:
public class CalciteTest {
public static void main(String[] args) {
// String sql = "select * from dept ";
String sql1 = "select a.* from dept a left join emp b on a.deptno=b.deptno
where a.name in('compute','aab') and b.deptno='aa' order by a.deptno limit 10";
String sql2 = "select a.name from dept a left join emp b on a.deptno=b.deptno
where a.name in('compute','aab') and b.deptno='aa'";
String sql3 = "select count(distinct deptno) from dept where deptno='aa'";
String sql4 = "select * from v_emp";
// sqlToRelNode(sql);
sqlToRelNode(sql4);
}
private static RelNode sqlToRelNode(String sql) {
SchemaPlus rootSchema = CalciteUtils.registerRootSchema(createTables(),
createVTables());
//expandView(rootSchema);
final FrameworkConfig fromworkConfig =
Frameworks.newConfigBuilder().parserConfig(SqlParser.Config.DEFAULT).defaultSchema(rootSchema).traitDefs(ConventionTraitDef.INSTANCE,
RelDistributionTraitDef.INSTANCE).build();
try {
//---------------------SQL to SqlNode---------------------------------
SqlTypeFactoryImpl factory = new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
// sql parser
SqlParser parser = SqlParser.create(sql, SqlParser.Config.DEFAULT);
SqlNode parsed = parser.parseStmt();
CalciteCatalogReader calciteCatalogReader = new
CalciteCatalogReader(CalciteSchema.from(rootSchema),
CalciteSchema.from(rootSchema).path(null), factory, new
CalciteConnectionConfigImpl(new Properties()));
// sql validate
SqlValidator validator =
SqlValidatorUtil.newValidator(SqlStdOperatorTable.instance(),
calciteCatalogReader, factory, CalciteUtils.conformance(fromworkConfig));
SqlNode validated = validator.validate(parsed);
//---------------------Planner---------------------------------
VolcanoPlanner planner = new PrestoVolcanoPlanner();
planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
planner.addRelTraitDef(RelDistributionTraitDef.INSTANCE);
final RelOptCluster relOptCluster = RelOptCluster.create(planner, new
RexBuilder(new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT)));
//add RelMetadataProvider
relOptCluster.setMetadataProvider(PrestoRelMetaDataProvider.INSTANCE);
// SqlNode toRelNode
final SqlToRelConverter.Config config =
SqlToRelConverter.configBuilder().withConfig(fromworkConfig.getSqlToRelConverterConfig()).withTrimUnusedFields(false).withExpand(true).withConvertTableAccess(false).build();
final SqlToRelConverter sqlToRelConverter = new SqlToRelConverter(new
PrestoView(validator, calciteCatalogReader, relOptCluster, config), validator,
calciteCatalogReader, relOptCluster, fromworkConfig.getConvertletTable(),
config);
RelRoot root = sqlToRelConverter.convertQuery(validated, false, true);
root = root.withRel(sqlToRelConverter.flattenTypes(root.rel, true));
RelNode relNode = root.rel;
//使用启发式实现谓词下推
HepProgramBuilder builder =
HepProgram.builder().addRuleInstance(FilterJoinRule.FilterIntoJoinRule.FILTER_ON_JOIN).addRuleInstance(FilterProjectTransposeRule.INSTANCE).addRuleInstance(FilterJoinRule.FILTER_ON_JOIN).addRuleInstance(ProjectMergeRule.INSTANCE).addRuleInstance(SemiJoinRule.PROJECT);
HepPlanner hepPlanner = new HepPlanner(builder.build());
hepPlanner.setRoot(relNode);
relNode = hepPlanner.findBestExp();
//关键逻辑
RelTraitSet desiredTraits = relOptCluster.traitSetOf(PrestoRel.CONVENTION);
if (!relNode.getTraitSet().equals(desiredTraits)) {
relNode = planner.changeTraits(relNode, desiredTraits);
}
planner.setRoot(relNode);
relNode = planner.findBestExp();
System.out.println("-----------------------------------------------------------");
System.out.println("The Best relational expression string:");
System.out.println(RelOptUtil.toString(relNode,
SqlExplainLevel.ALL_ATTRIBUTES));
System.out.println("-----------------------------------------------------------");
RelOptCost cost = planner.getCost(relNode, PrestoRelMetadataQuery.INSTANCE);
System.out.println(cost.toString());
System.out.println("-----------------------------------------------------------");
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
private static List<PrestoTable> createTables() {
List<PrestoTable> tabList = new ArrayList<>();
String[] fields = new String[]\{"DEPTNO", "NAME", "LEADER"};
String[] fieldTypes = new String[]\{"int", "string", "string"};
List<ColStatistics> cols = new ArrayList<>();
cols.add(new ColStatistics("DEPTNO", 5));
cols.add(new ColStatistics("NAME", 5));
cols.add(new ColStatistics("LEADER", 5));
PrestoTableInfo info1 = new PrestoTableInfo();
info1.setFields(fields);
info1.setFieldTypes(fieldTypes);
info1.setRowCount(5d);
info1.setTableName("DEPT");
info1.setStatistics(cols);
PrestoTable t1 = new PrestoTable(info1);
String[] fields2 = new String[]\{"EMPID", "NAME", "DEPTNO", "SALARY"};
String[] fieldTypes2 = new String[]\{"int", "string", "int", "double"};
List<ColStatistics> cols2 = new ArrayList<>();
cols2.add(new ColStatistics("EMPID", 50));
cols2.add(new ColStatistics("NAME", 50));
cols2.add(new ColStatistics("DEPTNO", 5));
cols2.add(new ColStatistics("SALARY", 5));
PrestoTableInfo info2 = new PrestoTableInfo();
info2.setFields(fields2);
info2.setFieldTypes(fieldTypes2);
info2.setRowCount(50d);
info2.setTableName("EMP");
info2.setStatistics(cols2);
PrestoTable t2 = new PrestoTable(info2);
tabList.add(t1);
tabList.add(t2);
return tabList;
}
===============================================
*{color:#FF0000}ERROR:{color}*
Exception in thread "main" java.lang.AssertionErrorException in thread "main"
java.lang.AssertionError at org.apache.calcite.util.Pair.zip(Pair.java:202) at
org.apache.calcite.rex.RexUtil.generateCastExpressions(RexUtil.java:134) at
org.apache.calcite.rex.RexUtil.generateCastExpressions(RexUtil.java:116) at
org.apache.calcite.plan.RelOptUtil.createCastRel(RelOptUtil.java:749) at
org.apache.calcite.plan.RelOptUtil.createCastRel(RelOptUtil.java:722) at
com.niwodai.pangu.datasearch.calcite.PrestoViewTable.expandView(PrestoViewTable.java:64)
at
com.niwodai.pangu.datasearch.calcite.PrestoViewTable.toRel(PrestoViewTable.java:42)
at org.apache.calcite.prepare.RelOptTableImpl.toRel(RelOptTableImpl.java:269)
at
org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewriteRel(RelStructuredTypeFlattener.java:694)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498) at
org.apache.calcite.util.ReflectUtil.invokeVisitorInternal(ReflectUtil.java:257)
at org.apache.calcite.util.ReflectUtil.invokeVisitor(ReflectUtil.java:214) at
org.apache.calcite.util.ReflectUtil$1.invokeVisitor(ReflectUtil.java:464) at
org.apache.calcite.sql2rel.RelStructuredTypeFlattener$RewriteRelVisitor.visit(RelStructuredTypeFlattener.java:768)
at org.apache.calcite.rel.SingleRel.childrenAccept(SingleRel.java:72) at
org.apache.calcite.rel.RelVisitor.visit(RelVisitor.java:44) at
org.apache.calcite.sql2rel.RelStructuredTypeFlattener$RewriteRelVisitor.visit(RelStructuredTypeFlattener.java:763)
at
org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewrite(RelStructuredTypeFlattener.java:195)
at
org.apache.calcite.sql2rel.SqlToRelConverter.flattenTypes(SqlToRelConverter.java:468)
at
com.niwodai.pangu.datasearch.calcite.CalciteTest.sqlToRelNode(CalciteTest.java:97)
at com.niwodai.pangu.datasearch.calcite.CalciteTest.main(CalciteTest.java:59)
> View test when it occur java.lang.AssertionError
> --------------------------------------------------
>
> Key: CALCITE-3301
> URL: https://issues.apache.org/jira/browse/CALCITE-3301
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Water Cut Off
> Priority: Major
>
> * *{color:#00875a}I want to use calcite to parse view, and convert view to my
> own RelNode, but I am not sure how to use ViewExpander ,can you give me an
> example to solve this problem, thanks ?{color}*
>
> public class PrestoView implements RelOptTable.ViewExpander {
> private final SqlValidator validator;
> private final Prepare.CatalogReader catalogReader;
> private final RelOptCluster cluster;
> private final SqlToRelConverter.Config config;
> public PrestoView(SqlValidator validator, Prepare.CatalogReader
> catalogReader, RelOptCluster cluster, SqlToRelConverter.Config config)
> { this.validator = validator; this.catalogReader = catalogReader;
> this.cluster = cluster; this.config = config; }
> @Override
> public RelRoot expandView(RelDataType rowType, String queryString,
> List<String> schemaPath, List<String> viewPath) {
> try
> { SqlNode parsedNode = SqlParser.create(queryString).parseStmt(); SqlNode
> validatedNode = validator.validate(parsedNode); SqlToRelConverter converter =
> new SqlToRelConverter(this, validator, catalogReader, cluster,
> StandardConvertletTable.INSTANCE, config); return
> converter.convertQuery(validatedNode, false, true); }
> catch (SqlParseException e)
> { throw new RuntimeException("Error happened while expanding view.", e); }
> }
> --------------------------------------------------------------
> public class PrestoViewTable extends AbstractTable implements
> TranslatableTable {
> private final String vName;
> private final String viewSql;
> public PrestoViewTable(String vName, String viewSql)
> { super(); this.vName = vName; this.viewSql = viewSql; }
> public String getVName()
> { return vName; }
> @Override
> public RelNode toRel(RelOptTable.ToRelContext context, RelOptTable
> relOptTable)
> { return expandView(context, relOptTable.getRowType(), viewSql).rel; }
> @Override
> public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
> RelDataTypeFactory.Builder builder = typeFactory.builder();
> builder.add("empid", new BasicSqlType(new RelDataTypeSystemImpl() {
> }, SqlTypeName.INTEGER));
> builder.add("deptno", new BasicSqlType(new RelDataTypeSystemImpl() {
> }, SqlTypeName.VARCHAR));
> return builder.build();
> }
> @Override
> public Schema.TableType getJdbcTableType()
> { return Schema.TableType.VIEW; }
> private RelRoot expandView(RelOptTable.ToRelContext context, RelDataType
> rowType, String queryString) {
> try {
> final RelRoot root = context.expandView(rowType, queryString, null, null);
> final RelNode rel = RelOptUtil.createCastRel(root.rel, rowType, true);
> // Expand any views
> final RelNode rel2 = rel.accept(new RelShuttleImpl() {
> @Override
> public RelNode visit(TableScan scan) {
> final RelOptTable table = scan.getTable();
> final TranslatableTable translatableTable =
> table.unwrap(TranslatableTable.class);
> if (translatableTable != null)
> { return translatableTable.toRel(context, table); }
> return super.visit(scan);
> }
> });
> return root.withRel(rel2);
> } catch (Exception e)
> { throw new RuntimeException("Error while parsing view definition: " +
> queryString, e); }
> }
> }
> ---------------------------------------------------------------------------
> public static SchemaPlus registerRootSchema(List<PrestoTable> tabList,
> List<PrestoViewTable> viewTables) {
> SchemaPlus rootSchema = Frameworks.createRootSchema(true);
> for (PrestoTable table : tabList)
> { rootSchema.add(table.getTableName(), table); }
> List<PrestoViewTable> vts = new ArrayList<>();
> PrestoViewTable pt = new PrestoViewTable("V_EMP", "select * from emp where
> empid>10");
> vts.add(pt);
> //
> // for (PrestoViewTable pv : viewTables)
> { // rootSchema.add(pv.getVName(), pv); // }
> return rootSchema;
> }
>
> {color:#00875a}----------------------------*Test----*---------------------------------------------------------------------------------------------------{color}
>
> public class CalciteTest {
> public static void main(String[] args)
> { // String sql = "select * from dept "; String sql1 = "select a.* from dept
> a left join emp b on a.deptno=b.deptno where a.name in('compute','aab') and
> b.deptno='aa' order by a.deptno limit 10"; String sql2 = "select a.name from
> dept a left join emp b on a.deptno=b.deptno where a.name in('compute','aab')
> and b.deptno='aa'"; String sql3 = "select count(distinct deptno) from dept
> where deptno='aa'"; String sql4 = "select * from v_emp"; //
> sqlToRelNode(sql); sqlToRelNode(sql4); }
> private static RelNode sqlToRelNode(String sql) {
> SchemaPlus rootSchema = CalciteUtils.registerRootSchema(createTables(),
> createVTables());
> //expandView(rootSchema);
> final FrameworkConfig fromworkConfig =
> Frameworks.newConfigBuilder().parserConfig(SqlParser.Config.DEFAULT).defaultSchema(rootSchema).traitDefs(ConventionTraitDef.INSTANCE,
> RelDistributionTraitDef.INSTANCE).build();
> try {
> //---------------------SQL to SqlNode---------------------------------
> SqlTypeFactoryImpl factory = new
> SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
> // sql parser
> SqlParser parser = SqlParser.create(sql, SqlParser.Config.DEFAULT);
> SqlNode parsed = parser.parseStmt();
> CalciteCatalogReader calciteCatalogReader = new
> CalciteCatalogReader(CalciteSchema.from(rootSchema),
> CalciteSchema.from(rootSchema).path(null), factory, new
> CalciteConnectionConfigImpl(new Properties()));
> // sql validate
> SqlValidator validator =
> SqlValidatorUtil.newValidator(SqlStdOperatorTable.instance(),
> calciteCatalogReader, factory, CalciteUtils.conformance(fromworkConfig));
> SqlNode validated = validator.validate(parsed);
> //---------------------Planner---------------------------------
> VolcanoPlanner planner = new PrestoVolcanoPlanner();
> planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
> planner.addRelTraitDef(RelDistributionTraitDef.INSTANCE);
> final RelOptCluster relOptCluster = RelOptCluster.create(planner, new
> RexBuilder(new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT)));
> //add RelMetadataProvider
> relOptCluster.setMetadataProvider(PrestoRelMetaDataProvider.INSTANCE);
> // SqlNode toRelNode
> final SqlToRelConverter.Config config =
> SqlToRelConverter.configBuilder().withConfig(fromworkConfig.getSqlToRelConverterConfig()).withTrimUnusedFields(false).withExpand(true).withConvertTableAccess(false).build();
> final SqlToRelConverter sqlToRelConverter = new SqlToRelConverter(new
> PrestoView(validator, calciteCatalogReader, relOptCluster, config),
> validator, calciteCatalogReader, relOptCluster,
> fromworkConfig.getConvertletTable(), config);
> RelRoot root = sqlToRelConverter.convertQuery(validated, false, true);
> root = root.withRel(sqlToRelConverter.flattenTypes(root.rel, true));
> RelNode relNode = root.rel;
> HepProgramBuilder builder =
> HepProgram.builder().addRuleInstance(FilterJoinRule.FilterIntoJoinRule.FILTER_ON_JOIN).addRuleInstance(FilterProjectTransposeRule.INSTANCE).addRuleInstance(FilterJoinRule.FILTER_ON_JOIN).addRuleInstance(ProjectMergeRule.INSTANCE).addRuleInstance(SemiJoinRule.PROJECT);
> HepPlanner hepPlanner = new HepPlanner(builder.build());
> hepPlanner.setRoot(relNode);
> relNode = hepPlanner.findBestExp();
> RelTraitSet desiredTraits = relOptCluster.traitSetOf(PrestoRel.CONVENTION);
> if (!relNode.getTraitSet().equals(desiredTraits))
> { relNode = planner.changeTraits(relNode, desiredTraits); }
> planner.setRoot(relNode);
> relNode = planner.findBestExp();
> System.out.println("-----------------------------------------------------------");
> System.out.println("The Best relational expression string:");
> System.out.println(RelOptUtil.toString(relNode,
> SqlExplainLevel.ALL_ATTRIBUTES));
>
> System.out.println("-----------------------------------------------------------");
> RelOptCost cost = planner.getCost(relNode, PrestoRelMetadataQuery.INSTANCE);
> System.out.println(cost.toString());
>
> System.out.println("-----------------------------------------------------------");
> } catch (Exception e)
> { e.printStackTrace(); }
> return null;
> }
> private static List<PrestoTable> createTables()
> { List<PrestoTable> tabList = new ArrayList<>(); String[] fields = new
> String[]\\{"DEPTNO", "NAME", "LEADER"}
> ;
> String[] fieldTypes = new String[]\{"int", "string", "string"};
> List<ColStatistics> cols = new ArrayList<>();
> cols.add(new ColStatistics("DEPTNO", 5));
> cols.add(new ColStatistics("NAME", 5));
> cols.add(new ColStatistics("LEADER", 5));
> PrestoTableInfo info1 = new PrestoTableInfo();
> info1.setFields(fields);
> info1.setFieldTypes(fieldTypes);
> info1.setRowCount(5d);
> info1.setTableName("DEPT");
> info1.setStatistics(cols);
> PrestoTable t1 = new PrestoTable(info1);
> String[] fields2 = new String[]\{"EMPID", "NAME", "DEPTNO", "SALARY"};
> String[] fieldTypes2 = new String[]\{"int", "string", "int", "double"};
> List<ColStatistics> cols2 = new ArrayList<>();
> cols2.add(new ColStatistics("EMPID", 50));
> cols2.add(new ColStatistics("NAME", 50));
> cols2.add(new ColStatistics("DEPTNO", 5));
> cols2.add(new ColStatistics("SALARY", 5));
> PrestoTableInfo info2 = new PrestoTableInfo();
> info2.setFields(fields2);
> info2.setFieldTypes(fieldTypes2);
> info2.setRowCount(50d);
> info2.setTableName("EMP");
> info2.setStatistics(cols2);
> PrestoTable t2 = new PrestoTable(info2);
> tabList.add(t1);
> tabList.add(t2);
> return tabList;
> }
> ===============================================
> *{color:#ff0000}ERROR:{color}*
> Exception in thread "main" java.lang.AssertionErrorException in thread "main"
> java.lang.AssertionError at org.apache.calcite.util.Pair.zip(Pair.java:202)
> at org.apache.calcite.rex.RexUtil.generateCastExpressions(RexUtil.java:134)
> at org.apache.calcite.rex.RexUtil.generateCastExpressions(RexUtil.java:116)
> at org.apache.calcite.plan.RelOptUtil.createCastRel(RelOptUtil.java:749) at
> org.apache.calcite.plan.RelOptUtil.createCastRel(RelOptUtil.java:722) at
> com.niwodai.pangu.datasearch.calcite.PrestoViewTable.expandView(PrestoViewTable.java:64)
> at
> com.niwodai.pangu.datasearch.calcite.PrestoViewTable.toRel(PrestoViewTable.java:42)
> at
> org.apache.calcite.prepare.RelOptTableImpl.toRel(RelOptTableImpl.java:269) at
> org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewriteRel(RelStructuredTypeFlattener.java:694)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498) at
> org.apache.calcite.util.ReflectUtil.invokeVisitorInternal(ReflectUtil.java:257)
> at org.apache.calcite.util.ReflectUtil.invokeVisitor(ReflectUtil.java:214)
> at org.apache.calcite.util.ReflectUtil$1.invokeVisitor(ReflectUtil.java:464)
> at
> org.apache.calcite.sql2rel.RelStructuredTypeFlattener$RewriteRelVisitor.visit(RelStructuredTypeFlattener.java:768)
> at org.apache.calcite.rel.SingleRel.childrenAccept(SingleRel.java:72) at
> org.apache.calcite.rel.RelVisitor.visit(RelVisitor.java:44) at
> org.apache.calcite.sql2rel.RelStructuredTypeFlattener$RewriteRelVisitor.visit(RelStructuredTypeFlattener.java:763)
> at
> org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewrite(RelStructuredTypeFlattener.java:195)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.flattenTypes(SqlToRelConverter.java:468)
> at
> com.niwodai.pangu.datasearch.calcite.CalciteTest.sqlToRelNode(CalciteTest.java:97)
> at com.niwodai.pangu.datasearch.calcite.CalciteTest.main(CalciteTest.java:59)
>
--
This message was sent by Atlassian Jira
(v8.3.2#803003)
