在使用Coral-trino翻译Hivesql2PrestoSql遇见的问题和解决方法

本文主要是介绍在使用Coral-trino翻译Hivesql2PrestoSql遇见的问题和解决方法，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

随着公司业务的发展，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 开源地址：https://github.com/linkedin/coral，目前该项目的ljfgem 老兄一直在孜孜不倦的迭代，为我们解决问题。虽然这项目是gradle 开发的，对于习惯用Maven的同学接入起来有点麻烦（在下不才，现在也没搞通gradle），但是并不影响我们代码阅读。

阿里云maven中央仓库, https://developer.aliyun.com/mvn/search

  
maven地址：
<dependency><groupId>com.linkedin.coral</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("hive.metastore.uris", "thrift://(your hive metastore ip):9083");HiveMetaStoreClient metaStoreClient = new HiveMetaStoreClient(hiveConf);HiveMscAdapter hiveMscAdapteri = new HiveMscAdapter(metaStoreClient);List<String> dbNamses = hiveMscAdapteri.getAllDatabases();log.info("dbNamses size:{}", dbNamses.size());List<String> tablenames = hiveMscAdapteri.getAllTables("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 = hiveToRelConverter.convertSql(sql);RelToTrinoConverter relToTrinoConverter = new RelToTrinoConverter();String expandedSql = relToTrinoConverter.convert(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'at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)at java.lang.reflect.Constructor.newInstance(Constructor.java:423)at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:834)at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:819)at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4867)at org.apache.calcite.sql.validate.IdentifierNamespace.resolveImpl(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 com.linkedin.coral.hive.hive2rel.HiveSqlToRelConverter.convertQuery(HiveSqlToRelConverter.java:59)at com.linkedin.coral.common.ToRelConverter.toRel(ToRelConverter.java:166)at com.linkedin.coral.common.ToRelConverter.convertSql(ToRelConverter.java:119)at com.luckincoffee.dataq.CoralHiveTest.testLateralViewArray2(CoralHiveTest.java:56)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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)at org.junit.runners.model.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 com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220)at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:53)
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Object 't_dim_shop' not found within 'hive.dw_dim'at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)at java.lang.reflect.Constructor.newInstance(Constructor.java:423)at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)at org.apache.calcite.runtime.Resources$ExInst.ex(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&'\77f3\5bb6\5e84'))at org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$0(ReflectiveConvertletTable.java:86)at org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(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.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4656)at org.apache.calcite.sql2rel.SqlToRelConverter.convertWhere(SqlToRelConverter.java:981)at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:649)at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:627)at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3181)at com.linkedin.coral.hive.hive2rel.HiveSqlToRelConverter.convertQuery(HiveSqlToRelConverter.java:63)at com.linkedin.coral.common.ToRelConverter.toRel(ToRelConverter.java:166)at com.linkedin.coral.common.ToRelConverter.convertSql(ToRelConverter.java:119)at com.luckincoffee.dataq.CoralHiveTest.testCnWhere(CoralHiveTest.java:70)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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)at org.junit.runners.model.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 com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220)at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:53)
Caused by: java.lang.reflect.InvocationTargetExceptionat 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.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 com.linkedin.coral.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.sql.SqlNode.toSqlString(SqlNode.java:153)at org.apache.calcite.sql.SqlNode.toSqlString(SqlNode.java:158)at org.apache.calcite.sql.SqlNode.toString(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.sql2rel.SqlNodeToRexConverterImpl.convertCall(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.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4656)at org.apache.calcite.sql2rel.StandardConvertletTable.convertExpressionList(StandardConvertletTable.java:793)at org.apache.calcite.sql2rel.StandardConvertletTable.convertCall(StandardConvertletTable.java:769)at org.apache.calcite.sql2rel.StandardConvertletTable.convertCall(StandardConvertletTable.java:756)... 41 more

首先，我们会发现，汉字 '石家庄' 被unicode 为 'u&'\77f3\5bb6\5e84''。

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

然后，我通过这篇博客，也是在calcite 处理中文出现乱码（Unicode）。Calcite中文字符串toSqlString()变为乱码(Unicode)，重写SqlDialect类中的quoteStringLiteral()方法解决_黄飞666的博客-CSDN博客使用Calcite，中文字符串toSqlString()时会变成乱码（unicode），可以新建一个方言类，重写quoteStringLiteral()方法解决。没有重写quoteStringLiteral()时：重写quoteStringLiteral()后：原因在SqlDialect类中的quoteStringLiteral()方法：...https://blog.csdn.net/weixin_39133753/article/details/115470036最后在coral项目的issue中找到相同问题的反馈：https://github.com/linkedin/coral/issues/152