在使用Coral

在使用Coral

随着公司业务的发展，hive, kylin在用户的即时查询时，越来越难以满足用户快速的需求。另外最近Presto在大数据即时查询方面性能越来越来，很多企业都在接入和使用presto,网上相关的技术文章很多，因此，我们在接入Presto查询引擎以后，也不得不处理Sql路由的问题。现在数据分析师和数据ETL开发的同学已经习惯了Hive常用的写法，虽然prestoSql 与hiveSql在语法方法很接近，但是在部分语义和函数方面还有不相同的地方，这就给用户带来使用的困惑和不便。为了保证用户的平滑的接入Presto, 改善即时查询平台的性能和体验，我们调研了许多网页大牛的分析的技术博客和文章，其中一个大牛文章：Presto/Trino执行Hive SQL的方案探索 - 知乎，总结了hivesql翻译prestoSql的三种方案，并在文章最后提议使用Coral-trino开源的组件实现hiveSql2prestoSql的翻译功能。此外呢，B站在使用presto查询引擎时，也用到Coral-trino,来翻译hiveSql，具体文章：Presto在B站的实践 - 哔哩哔哩。下面说下Coral-trino的接入和遇见问题，以及解决方法。

一、Coral-trino的接入和使用

1.1 Coral-trino的接入

    1.1.1 Coral开源项目地址

          Coral 开源地址：，目前该项目的ljfgem 老兄一直在孜孜不倦的迭代，为我们解决问题。虽然这项目是gradle 开发的，对于习惯用Maven的同学接入起来有点麻烦（在下不才，现在也没搞通gradle），但是并不影响我们代码阅读。

      阿里云maven中央仓库,

  
maven地址：
<dependency><groupId>al</groupId><artifactId>coral-trino</artifactId><version>2.0.79</version>
</dependency>

     1.1.2 Coral的基础组件Apache Calcite

       Coral是由linkedin开源的，其基础是Apache Calcite，并对其做大量的优化，并将其开源出来，linkedin版的calcite 项目地址： GitHub - linkedin/linkedin-calcite: LinkedIn's version of Apache Calcite

1.2 HiveSql2PrestoSql翻译Demo

@Slf4j
public class CoralHiveTest {private static HiveConf hiveConf;private static HiveToRelConverter hiveToRelConverter;private static HiveToTrinoConverter hiveToTrinoConverter;@BeforeClasspublic static void beforeClass() throws Exception{hiveConf = new HiveConf();hiveConf.set(&#astore.uris", "thrift://(your hive metastore ip):9083");HiveMetaStoreClient metaStoreClient = new HiveMetaStoreClient(hiveConf);HiveMscAdapter hiveMscAdapteri = new HiveMscAdapter(metaStoreClient);List<String> dbNamses = AllDatabases();log.info("dbNamses size:{}", dbNamses.size());List<String> tablenames = AllTables("dw_dim");hiveToRelConverter = new HiveToRelConverter(hiveMscAdapteri);}@Testpublic void testLateralViewArray2() {String sql = "select collect_list(shop_name) from dw_dim.tb_dim_shop where " +"city_name in ('石家庄')";RelNode relNode = vertSql(sql);RelToTrinoConverter relToTrinoConverter = new RelToTrinoConverter();String expandedSql = vert(relNode);log.info("expandedSql:{}", expandedSql);}
}

 二、遇见的问题以及解决方法

2.1 hive版本低导致hive库表找不到异常

org.apache.calcite.runtime.CalciteContextException: At line 0, column 0: Object 't_dim_shop' not found within 'hive.dw_dim'wInstance0(Native Method)wInstance(NativeConstructorAccessorImpl.java:62)wInstance(DelegatingConstructorAccessorImpl.java:45)at wInstance(Constructor.java:423)at org.apache.calcite.runtime.(Resources.java:463)at org.apache.calcite.wContextException(SqlUtil.java:834)at org.apache.calcite.wContextException(SqlUtil.java:819)at org.apache.calcite.sql.wValidationError(SqlValidatorImpl.java:4867)at org.apache.calcite.sql.solveImpl(IdentifierNamespace.java:127)at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:177)at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1005)at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:965)at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3125)at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3379)at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1005)at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:965)at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:216)at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:940)at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:647)at al.hive.vertQuery(HiveSqlToRelConverter.java:59)at Rel(ToRelConverter.java:166)at vertSql(ToRelConverter.java:119)at com.luckincoffee.stLateralViewArray2(CoralHiveTest.java:56)flect.NativeMethodAccessorImpl.invoke0(Native Method)flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at flect.Method.invoke(Method.java:498)at org.del.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)at org.junit.del.ReflectiveCallable.run(ReflectiveCallable.java:12)at org.del.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)at org.junit.runners.ParentRunner.run(ParentRunner.java:363)at org.junit.runner.JUnitCore.run(JUnitCore.java:137)at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)at junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)at junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220)at junit.JUnitStarter.main(JUnitStarter.java:53)
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Object 't_dim_shop' not found within 'hive.dw_dim'wInstance0(Native Method)wInstance(NativeConstructorAccessorImpl.java:62)wInstance(DelegatingConstructorAccessorImpl.java:45)at wInstance(Constructor.java:423)at org.apache.calcite.runtime.(Resources.java:463)at org.apache.calcite.runtime.(Resources.java:572)... 44 more

  根据原因是因为我的本地hive版本太低，只是1.2.1版本，我的hive-metastore版本是2.3.6,

改用1.2.1版本以后就解决了，库表找不到问题。

2.2 sql中汉字被unicode编码后报错问题

   当我执行这样的sql时：

 String sql = "select * from dw_dim.tb_dim_shop where dt= '2022-05-28' and city_name in ('石家庄')";

即当hivesql中包含中文时，就会抛出这样的错：

java.lang.RuntimeException: while converting `tb_dim_shop`.`dt` = '2022-05-28' AND `dim_shop_d_his`.`city_name` IN ((u&'77f35bb65e84'))at org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$0(ReflectiveConvertletTable.java:86)at org.apache.calcite.vertCall(SqlNodeToRexConverterImpl.java:63)at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4787)at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4092)at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)at org.apache.calcite.sql2rel.vertExpression(SqlToRelConverter.java:4656)at org.apache.calcite.vertWhere(SqlToRelConverter.java:981)at org.apache.calcite.vertSelectImpl(SqlToRelConverter.java:649)at org.apache.calcite.vertSelect(SqlToRelConverter.java:627)at org.apache.calcite.vertQueryRecursive(SqlToRelConverter.java:3181)at al.hive.vertQuery(HiveSqlToRelConverter.java:63)at Rel(ToRelConverter.java:166)at vertSql(ToRelConverter.java:119)at com.luckincoffee.stCnWhere(CoralHiveTest.java:70)flect.NativeMethodAccessorImpl.invoke0(Native Method)flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at flect.Method.invoke(Method.java:498)at org.del.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)at org.junit.del.ReflectiveCallable.run(ReflectiveCallable.java:12)at org.del.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)at org.junit.runners.ParentRunner.run(ParentRunner.java:363)at org.junit.runner.JUnitCore.run(JUnitCore.java:137)at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)at junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)at junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220)at junit.JUnitStarter.main(JUnitStarter.java:53)
Caused by: flect.flect.NativeMethodAccessorImpl.invoke0(Native Method)flect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)flect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at flect.Method.invoke(Method.java:498)at org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$0(ReflectiveConvertletTable.java:83)... 36 more
Caused by: java.lang.RuntimeException: No list startedat org.apache.calcite.sql.pretty.SqlPrettyWriter.sep(SqlPrettyWriter.java:958)at org.apache.calcite.sql.pretty.SqlPrettyWriter.sep(SqlPrettyWriter.java:953)at al.hive.hive2rel.functions.HiveInOperator.unparse(HiveInOperator.java:67)at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:437)at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:104)at org.apache.calcite.SqlString(SqlNode.java:153)at org.apache.calcite.SqlString(SqlNode.java:158)at org.apache.calcite.String(SqlNode.java:125)at java.lang.String.valueOf(String.java:2994)at java.lang.StringBuilder.append(StringBuilder.java:131)at org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerOpTypeMethod$1(ReflectiveConvertletTable.java:127)at org.apache.calcite.vertCall(SqlNodeToRexConverterImpl.java:63)at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4787)at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4092)at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)at org.apache.calcite.sql2rel.vertExpression(SqlToRelConverter.java:4656)at org.apache.calcite.vertExpressionList(StandardConvertletTable.java:793)at org.apache.calcite.vertCall(StandardConvertletTable.java:769)at org.apache.calcite.vertCall(StandardConvertletTable.java:756)... 41 more

 首先，我们会发现，汉字 '石家庄'  被unicode 为 'u&'77f35bb65e84''。

 其次，我们看到报错的类都是calsite当中。

 然后，我通过 这篇博客，也是在calcite 处理中文出现乱码（Unicode）。Calcite中文字符串toSqlString()变为乱码(Unicode)，重写SqlDialect类中的quoteStringLiteral()方法解决_黄飞666的博客-CSDN博客使用Calcite，中文字符串toSqlString()时会变成乱码（unicode），可以新建一个方言类，重写quoteStringLiteral()方法解决。没有重写quoteStringLiteral()时：重写quoteStringLiteral()后：原因在SqlDialect类中的quoteStringLiteral()方法：...：

根据贡献人，提示：

 我们需要在项目的resource文件下中添加saffron.properties 配置文件，并增加属性配置：

calcite.default.charset = utf8

 设置calsite的默认charset = utf8, 通过这样就解决了，中文unicode编码的带来的异常。

2.3 hive的udf问题

在我们数据开发中，我们会开发一些hive的udf函数。这些udf 不能被calsite直接解析，就会抛出这样的报错：

almon.functions.UnknownSqlFunctionException: Unknown function name: ifnull

目前针对hive udf 函数注册到calsite的hive解析器中，还在探索。最近学习了，yuqi,提供的一个开源calsite 函数注册的项目：GitHub - yuqi1129/calcite-test: Test code for apache calcite

后续文章将陆续分享验证结果。敬请期待哦。