参加したプロジェクトでhiveとpresto両方で確認する必要がある。同じ意味のクエリをhiveで走らせたり、prestoで走らせたりするのに、毎回違うSQLを実施するのが面倒である。また、同じSQLでも微妙に「方言」が違う。そこまでならばまだ大丈夫で、なれるか、hiveのmacroを用い、表現を統一するなどのやり方がある。しかし、たまに基本の考え方が違っていてはまりやすいところもある。
ここで、自分が引っかかったものを共有する。少しでも役に立てば嬉しい。
環境
今回、以下の環境を用いた。
Presto CLI 333
Beeline version 1.2.2 by Apache Hive
正規表現の違いについて
- 特殊文字（例えば$のエスケプ）の表現が hiveにおいて ¥¥$であるが、presto において ¥$である。この違うによって、特にエラーなどがなっていないためはまりやすい。
hiveの場合
hive
> select 'This is a $test' rlike '\\$test';
+-------+--+
| _c0 |
+-------+--+
| true |
+-------+--+
> select 'This is a $test' rlike '\$test';
+--------+--+
| _c0 |
+--------+--+
| false |
+--------+--+
> select 'This is a $test' rlike '$test';
+--------+--+
| _c0 |
+--------+--+
| false |
+--------+--+
prestoの場合
presto
presto> select regexp_like('This is a $test','\$test');;
_col0
-------
true
presto> select regexp_like('This is a $test','$test');;
_col0
-------
false
presto> select regexp_like('This is a $test','\\$test');;
_col0
-------
false
- 正規表現で文字列から特定のパターンを抽出する際、prestoではマッチしないときに、NULLを戻すが、hiveでは、空（文字列）を戻す！これははまりやすいところである。
hiveの場合
> select regexp_extract('This is a test','(test)',1);
+-------+--+
| _c0 |
+-------+--+
| test |
+-------+--+
> select regexp_extract('This is a test','(testx)',1);
+------+--+
| _c0 |
+------+--+
| |
+------+--+
> select regexp_extract('This is a test','(testx)',1)='';
+-------+--+
| _c0 |
+-------+--+
| true |
+-------+--+
prestoの場合
presto
presto> select regexp_extract('This is a test','(test)',1);
_col0
-------
test
presto> select regexp_extract('This is a test','(testx)',1);
_col0
-------
NULL
配列の違いについて
hiveの配列が 0 からカウントされるが、prestoがなぜか 1からカウントされる
hiveの場合
hive
> select split('1,2',',')[0];
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
prestoの場合
presto
presto> select split('1,2',',')[0];
Query 20201031_122624_05800_juuv8 failed: SQL array indices start at 1
presto> select split('1,2',',')[1];
_col0
-------
1
windows関数の違い
last_valueやfirst_valueを使いときに、NULLを無視する他場合、hiveではこのようにパラメターでしていできる
hive
> select last_value(item1,TRUE) over ...
一方、presto において、このように構文が変わる
presto
presto> select last_value(item1) ignore nulls over ...
ここの違いはまだ統一SQL文にな統一できていないところである
hive macroを用い、prestoと同じsql文
簡単なhiveマクロを使いhiveとprestoのSQLを合わせることによって、SQLクエリをあわせることができる。例えば、以下のmacroを先頭に入れれば、同じpresto用SQL文（to_unixtime, nullif, regexp_like,replace）でもhiveで使える。
hive
create temporary macro to_unixtime(s string) unix_timestamp(s);
create temporary macro nullif(src string,s string) if(src = s, null, src);
create temporary macro regexp_like(str string, pattern string) str rlike pattern;
create temporary macro `replace`(str string,src string,dst string) translate(str,src,dst);
ただし、regexp_likeのようなsql文は同じでも、中身のエスケプルールについて気をつけないといけない。