sql - 在 Apache Calcite 中将带有聚合函数的 SQL 查询转换为关系代数表达式 - 找不到函数签名的匹配项
问题描述
我正在尝试使用 Apache Calcite 将 SQL 查询转换为关系代数表达式SqlToRelConverter
。
它适用于此查询(引号用于确保小写):
queryToRelationalAlgebraRoot("SELECT \"country\" FROM \"mytable\"")
但是在这个查询中它失败了:
queryToRelationalAlgebraRoot("SELECT \"country\", SUM(\"salary\") FROM \"mytable\" GROUP BY \"country\"")
出现此错误:
org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature SUM(<NUMERIC>)
似乎 SQL 验证器以某种方式没有注册 sum 或 count 之类的聚合函数。
case class Income(id: Int, salary: Double, country: String)
class SparkDataFrameTable(df: DataFrame) extends AbstractTable {
def getRowType(typeFactory: RelDataTypeFactory): RelDataType = {
val typeList = df.schema.fields.map {
field => field.dataType match {
case t: StringType => typeFactory.createSqlType(SqlTypeName.VARCHAR)
case t: IntegerType => typeFactory.createSqlType(SqlTypeName.INTEGER)
case t: DoubleType => typeFactory.createSqlType(SqlTypeName.DOUBLE)
}
}.toList.asJava
val fieldNameList = df.schema.fieldNames.toList.asJava
typeFactory.createStructType(typeList, fieldNameList)
}
}
object RelationalAlgebra {
def queryToRelationalAlgebraRoot(query: String): RelRoot = {
val sqlParser = SqlParser.create(query)
val sqlParseTree = sqlParser.parseQuery()
val frameworkConfig = Frameworks.newConfigBuilder().build()
val planner = new PlannerImpl(frameworkConfig)
val rootSchema = CalciteSchema.createRootSchema(true, true)
// some sample data for testing
val inc1 = new Income(1, 100000, "USA")
val inc2 = new Income(2, 110000, "USA")
val inc3 = new Income(3, 80000, "Canada")
val spark = SparkSession.builder().master("local").getOrCreate()
import spark.implicits._
val df = Seq(inc1, inc2, inc3).toDF()
rootSchema.add("mytable", new SparkDataFrameTable(df))
val defaultSchema = List[String]().asJava
val calciteConnectionConfigProperties = new Properties()
val calciteConnectionConfigImpl = new CalciteConnectionConfigImpl(calciteConnectionConfigProperties)
val sqlTypeFactoryImpl = new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT)
val calciteCatelogReader = new CalciteCatalogReader(rootSchema, defaultSchema, sqlTypeFactoryImpl, calciteConnectionConfigImpl)
val defaultValidator = SqlValidatorUtil.newValidator(new SqlStdOperatorTable(), calciteCatelogReader, sqlTypeFactoryImpl, SqlConformanceEnum.LENIENT)
val relExpressionOptimizationCluster = RelOptCluster.create(new VolcanoPlanner(), new RexBuilder(sqlTypeFactoryImpl))
val sqlToRelConfig = SqlToRelConverter.configBuilder().build()
val sqlToRelConverter = new SqlToRelConverter(planner, defaultValidator, calciteCatelogReader, relExpressionOptimizationCluster, StandardConvertletTable.INSTANCE, sqlToRelConfig)
sqlToRelConverter.convertQuery(sqlParseTree, true, true)
}
}
解决方案
代码的问题在于new SqlStdOperatorTable()
创建了一个未初始化的验证器。正确的使用方法SqlStdOperatorTable
是使用SqlStdOperatorTable.instance()
.
在通过电子邮件发送到 dev@calcite.apache.org 邮件列表后,我找到了解决方案。我要感谢 Yuzhao Chen 调查我的问题并指出我的代码存在的问题。
推荐阅读
- apache-spark - 当master是k8s时,Spark 2.4.4是否支持转发Delegation Tokens?
- r - broom::augment 返回矩阵不能取消嵌套
- php - 如何使用 laravel 中的会话变量在编辑页面上显示选中的单选按钮?
- html - 为什么不能显示div的滚动?
- wordpress - 无法在旋转滑块中加载图像
- database - 从 SQL 数据库中读取星型模式数据文件
- deep-learning - 有人可以用简单的方式解释 FlowNet 中的相关层吗?
- python - 使用 Flask 将值传递给函数
- shopify - 将特色博客区域添加到 Shopify 文章模板
- oracle - 如何基于 Directroy 向公共 SYNONYM 提供 READ、WRITE 授权