Presto兼容Hive SQL的一些改造工作

2019-03-31

前言

Presto是一款优秀的分布式SQL查询引擎，适用于即席查询和报表分析等业务，其使用了ANSI SQL语法和语义，使用标准是SQL-92和SQL:2016。但是因为很多业务方一直使用Hive离线引擎来做SQL分析，而Hive使用类似SQL的语法（HQL）。为了使用户能平滑的将业务迁移到Presto上或者能让SQL同时跑到Presto及Hive引擎上，我们对Presto语法及一些算子等做了二次兼容开发，来最大限度降低用户迁移成本。接下来我们介绍下我们的主要兼容工作。

一、权限认证

Presto默认如果使用Password，那么必须使用Kerberos协议。因为公司离线数据没有Kerberos协议，所以我们改进了权限认证机制，与离线复用一套权限机制，用户只需要去离线申请权限，指定用户名和密码，即可查询Presto引擎和访问离线数据，并且提供了以下几种方式访问公司Presto，且与公司离线权限体系打通，分别为JDBC、Cli、Go、Python、R、NodeJS。

二、行为修改

整数相除返回浮点数

Presto整数相除沿用了Java整数相除的特性，我们需要修改函数算子行为，如IntegerOperators，将divide相关的函数修改为返回浮点数结果。比如：

presto:default> select 5/2;
 _col0
 -------
    2.5
(1 row)

类型隐式转换

Presto类型比较与Java一样，使用的是强类型，即int与varchar是无法比较的，但是Hive是支持隐式转换的，所以这种隐式类型转换的问题，用户遇到的比较多。我们做了修改，在对应的算子里，添加了新的语义行为，事例如下：

presto:default> select '1' > 2;
 _col0
 -------
  false
 (1 row)

过滤表达式隐式转换

大量的SQL，where条件里会带有比较语句，可能会出现int与varchar比较的场景，所以我们做了过滤表达式隐式转换，方法是在ExpressionAnalyzer里通过visitComparisonExpression来cast左右表达式为同一类型。

Array Functions兼容

主要与Hive保持兼容，做了如下修改：

array index从0开始

presto:default> select array[1,2,3][0];
 _col0
 -------
      1
(1 row)

超过下标最大值时返回NULL

presto:default> select array[1,2,3][4];
 _col0
 -------
  NULL
(1 row)

substr兼容

下标从0开始

presto:default> select substr('123',0,2);
_col0
 -------
  12
(1 row)

in 操作符可包含字符串

presto:default> select id from test where id in ('1001',1002) limit 1;
_col0
 -------
  1001
(1 row)

sum 支持字符串相加

sum的参数包含字符串时，可进行计算, 即 select sum(id) from test; 如果id为varchar也可以进行计算了。

JOIN条件隐式转换

实现了StatementRewrite子类，通过visit JOIN来获得左右表达式进行cast，因为一些财务业务涉及到大精度问题，所以将精度范围改为DECIMAL(38,10)。

cast

因为Presto字符串类型为varchar，Hive为String，所以支持了cast as string语法，事例：

presto:default> select cast (1 as string);
 _col0
 -------
  1
(1 row)

三、新的语法支持

& 操作符

SqlBase.g4文件里添加新的 & 语法，然后实现对应的Operator。事例为：

presto:default> select 1 & 3;
 _col0
 -------
      1
(1 row)

rlike

与like相似，实现了rlike，比如查询select id from test rlike ‘xxx’; 目前还有部分功能需要完善。

四、UDF

UDF除了添加了一些特殊业务的UDF外，比如isInPolygon多边形计算，安全函数等，还添加了一些常用的Hive UDF，分别为：

字符串相关

md5
concat_ws
nvl
json

日期相关

unix_timestamp
fromUtcTimestamp
toUtcTimestamp
dateSub
diffDate

int/bigint函数

presto:default> select int(2.1);
 _col0
 -------
      2
(1 row)

等等，具体想查看udf是否支持，可以直接操作连接Presto，如查询md5函数：

presto:default> select md5('1');
_col0
----------------------------------
c4ca4238a0b923820dcc509a6f75849b
(1 row)

有结果即表示支持该函数。

五、其他兼容

Hive视图

在visitTable里获得table信息，判断是否是视图，如果是视图，那么获取原始SQL查询，递归调用Analyze。

插入数据临时文件目录

由于涉及到权限问题，insert数据时，用户临时数据插入到临时目录会没有权限，所以修改了可配置临时文件插入目录参数。

支持可读取Hive递归子分区

正常表存储格式为 /table/partition/file，这种数据是可以正常访问的，但是如果数据存储格式为 /table/partition/directory/file 时，Presto访问时返回数据为空。而Hive里面的解决方法为请求时添加以下参数：

set hive.mapred.supports.subdirectories=true;

set mapreduce.input.fileinputformat.input.dir.recursive=true;

我们设置了参数，让默认即可递归子目录，参数为：

1 2	hive.recursive-directories=true

Key not present in map

Presto在处理MAP时，当KEY 不存在时，会报错，而Hive返回NULL，通过Presto内部参数解决：

1 2	deprecated.legacy-map-subscript=true

当然整体兼容Hive并不止这些，比如函数兼容类似Java里面的多态，需要找到合适的函数；还有least/greast等函数支持，还有一些函数null处理问题，太细节了，这里就不一一介绍了。由于Hive兼容是一个任重而道远的事情，这里还是推荐用户最好使用ANSI SQL以及管理好自己数据，这以后无论迁移还是使用，都会方便不少，减少不必要的麻烦。