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

前言

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相关的函数修改为返回浮点数结果。比如:

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

类型隐式转换

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

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

过滤表达式隐式转换

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

Array Functions兼容

主要与Hive保持兼容,做了如下修改:

  • array index从0开始

    1
    2
    3
    4
    5
    presto:default> select array[1,2,3][0];
    _col0
    -------
    1
    (1 row)
  • 超过下标最大值时返回NULL

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

substr兼容

  • 下标从0开始
1
2
3
4
5
presto:default> select substr('123',0,2);
_col0
-------
12
(1 row)

in 操作符可包含字符串

1
2
3
4
5
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语法,事例:

1
2
3
4
5
presto:default> select cast (1 as string);
_col0
-------
1
(1 row)

三、新的语法支持

& 操作符

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

1
2
3
4
5
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函数

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

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

1
2
3
4
5
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里面的解决方法为请求时添加以下参数:

1
2
3
set hive.mapred.supports.subdirectories=true;

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

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

1
hive.recursive-directories=true

Key not present in map

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

1
deprecated.legacy-map-subscript=true

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