Distributed computing (Apache Spark, Hadoop, Kafka, …) Advent Calendar 2021 23日目です。

今年も Hue について書こうかと考えていましたが、最近 SQL(Presto)やEmbulk,Digdagを使う機会が増えているので趣向を変えてみます。

————

SQLレシピ本とは？

ビッグデータ系の良書の一冊でもある「ビッグデータ 分析のためのSQLレシピ本」、出版から4年経過しても色あせることはありません。

この書籍では、SQLの記述方法から分析手法まで広く扱われおり、写経するだけでも大きな知見が得られるでしょう。一方、扱われているミドルウェアのバージョンが若干古くなっていることと、書籍中で扱われていない、本ブログでも時々紹介している分散クエリエンジンがあります。今回は先日リリースされたImpala (4.0.0) と Presto (350) を利用してクエリを検証し、実行できないクエリに関しては代替方法をまとめました。

そのまま実行できるSQLが大半でしたが、大きく書き換えが必要だったり、代替方法がないクエリもありました。プラットフォームの置き換え等でこれらのクエリエンジンを利用する際の参考になれば幸いです。

検証環境とセットアップ

データセット

公式サポートページで提供しているダウンロードデータを利用しています。このデータにはImpala / Presto用の書き換えが必要なクエリがあるため、それぞれ後述します。

また、ダウンロードできるデータセットの不備（？）により、書籍中の以下のコードは実行エラーになります。ご注意ください。

• コード 3.2.5.1 mst_users_with_dates テーブルがないため実行できない
• コード3.3.4.7 product_ids列がないため実行できない
• コード3.4.5.2 usersテーブルがないため実行できない

Impala 4.0.0

Impala 4.0.0は今年リリースされたMPP SQL クエリエンジンです。（過去のブログ）。検証用クラスターの準備が面倒だったので、今回は Dockerイメージを利用しました。Quickstartページに記載された通りに行うことで簡単に実行できるので興味がある方はお試しください。基本的にはQuickstartの順序に沿って実行すれば Impala シェルが起動するはずですが、検証時点では次の環境変数の設定のみ指定する必要がありました。

export IMPALA_QUICKSTART_IMAGE_PREFIX="apache/impala:4.0.0-"

また、注意点として、DockerでImpalaとKuduの複数のコンテナが実行されるため、PCにはそれなりにリソースが必要です。

データセットの変更箇所

1. varchar(255) を string に変更（varcharのままでもテーブルは作成可能だがデータのINSERT時にキャストが必要で手間がかかるため）
2. Numeric 型をサポートしていないため、decimal(2,1)に変更
3. 3-3-4.sql のカラム名の indicator が予約語でエラーになったため indicator1 に変更

Presto 3.5.0

Prestoも分散SQLエンジンです。PrestoSQLとTrinoの違いについてはブログをご覧ください。当初はTrinoのDockerイメージを用いて検証する予定でしたが、検証環境の構築で失敗したため、今回はTreasure Data CDPの環境で12月から利用できるようになった Presto 350 を使用しています。

データセットの変更箇所

1. Numeric 型をサポートしていないため、double に変更
2. text型はvarcher(255)に変更
3. Treasure Dataの環境に合わせて、テーブル作成時にtime列を追加してダミーの数値を挿入

３章. データ加工のためのSQL

さて、SQLレシピ本の３章は「データ加工のためのSQL」として、基本的なSQLを扱っています。ImpalaとPrestoでクエリを実行し、エラーになったコードとその原因、修正方法を列挙します。

SPLIT関数の変更 : Impala

問題

コード: 3.1.3.1のHiveとSparkSQLクエリの例では split() を使用しているが、Impalaはsplit関数をサポートしていない。

split(parse_url(url, 'PATH'), '/1')[1] AS part1

対処方法

Postgresと同様に split_part() を使用する。書き換え後のクエリは次の通り。

SELECT
   stamp
 , url
 , split_part(parse_url(url, 'PATH'), '/', 2) AS path1
 , split_part(parse_url(url, 'PATH'), '/',3) AS path2
FROM access_log;

コード 3-2-6-3, 3-2-6-4,3-2-6-5 も同じ

SELECT
  CAST(split_part(ip, '.', 1) AS int) AS ip_part_1
 ,CAST(split_part(ip, '.', 2) AS int) AS ip_part_2
 ,CAST(split_part(ip, '.', 3) AS int) AS ip_part_3
 ,CAST(split_part(ip, '.', 4) AS int) AS ip_part_4
FROM
  (SELECT '192.168.0.1' AS ip) AS t
;

SELECT
  ip
 , CAST(split_part(ip, '.', 1) AS int)  * pow(2, 24)
 +CAST(split_part(ip, '.', 2) AS int)  * pow(2, 16)
 +CAST(split_part(ip, '.', 3) AS int)  * pow(2, 8)
 +CAST(split_part(ip, '.', 4) AS int)  * pow(2, 0)
 AS ip_integer
FROM
  (SELECT '192.168.0.1' AS ip) AS t
;

SELECT
  ip
 , CONCAT(
   lpad(split_part(ip, '.', 1) , 3, '0')
 , lpad(split_part(ip, '.', 2) , 3, '0')
 , lpad(split_part(ip, '.', 3) , 3, '0')
 , lpad(split_part(ip, '.', 4) , 3, '0')
  ) AS ip_padding
FROM
  (SELECT '192.168.0.1' AS ip) AS t
;

NULLIF: Impala

補足

コード3.2.3.2では、0での除算を避けるために HiveでCASE WHENを使用しているが、ImpalaはNULLIFが利用できる

「row」カラム名の扱い: Impala

問題

コード 3.3.2.1, 3.3.2.2他。Impalaでは、列のエイリアスとして row という名前が利用できない。（予約語のためエラーというメッセージが出るが、これは対処してほしい・・・）
ROW_NUMBER() OVER(ORDER BY score DESC) AS row

ワークアラウンド

このクエリでは row の代わりに row1 のような名前を使う必要がある

ARRAY関連が未サポート:Impala

コード3.3.2.3、3.3.4.2他

ImpalaはARRAY_AGG()、ARRAY()をサポートしていない。対処方法はない？

分析関数でのDISTINCTが使えない: Impala

問題

コード3.3.2.6。Impalaでは SELECT DISTINCT …. WINDOW関数という呼び出し方はエラーになる。

ワークアラウンド

次のようにWITHで書き換えることにより実行可能。

WITH tmp_tbl AS
(SELECT
  category
, FIRST_VALUE(product_id)
  OVER(PARTITION BY category ORDER BY score DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  AS product_id
FROM
  popular_products
)
SELECT DISTINCT *
FROM tmp_tbl
;

collect_list関数は未サポート: Impala、Presto

補足

コード3.3.3.2: Impalaではcollect_list()関数をサポートしていないが、group_concat()を利用できる

SELECT
  purchase_id
--, concat_ws(',', collect_list(product_id)) AS product_ids
, group_concat(product_id) AS product_ids
, SUM(price) AS amount
FROM purchase_detail_log
GROUP BY purchase_id
ORDER BY purchase_id
;

Prestoではarray_agg()を使用する（参考)

SELECT
  purchase_id
--, concat_ws(',', collect_list(product_id)) AS product_ids
--, concat_ws(',', collect_list(product_id)) AS product_ids
-- , group_concat(product_id) AS product_ids
 , ARRAY_AGG(product_id) AS product_ids
, SUM(price) AS amount
FROM purchase_detail_log
GROUP BY purchase_id
ORDER BY purchase_id
;

LATERAL VIEWは未サポート: Impala、Presto

コード3.3.4.2: ImpalaはLATERAL VIEWは未サポート。

サブクエリで1件以上返ってくる場合はエラー:Impala

コード3.4.2.3、相関サブクエリの例で、サブクエリからの結果が1件以上返ってくる場合はエラーになる。代替手段は？

parse_urlの代わりにurl_extract_xxx関数を使用: Presto

補足

コード3.1.2.1他。HiveやImpala、SparkSQLではparse_url()関数をサポートしているが、Prestoはurl_extract_host, url_extract_pathなどの関数が用意されている(ドキュメント）

SELECT
  stamp
 --, parse_url(referrer, 'HOST') AS referrer_host
 , url_extract_host(referrer) AS referrer_host
FROM access_log
;

SELECT
  stamp
 , url
, url_extract_path(url) AS path
, url_extract_parameter(url, 'id') AS id
FROM access_log
;

CURRENT_DATE定数: Presto

補足

コード3.1.4.1: HiveやImpaaではCURRENT_DATE()関数、CURRENT_DATE定数が両方とも使えるが、Prestoでは定数のみサポート

連番作成: Presto

補足

コード3.4.5.7の手順では連番が作成できないため、次のようなクエリを実行する

SELECT x FROM UNNEST(SEQUENCE(1,5,1)) AS t(x)

なおImpalaはunnest()をサポートしていないため上記の方法は使用できない

調査中: Prestoの相関サブクエリの結果

コード3.4.2.3の結果が異なる

まとめ

上記の検証結果から、総じてPrestoの方が他のクエリエンジンからの移行コストが低そうに思います。Impalaもパフォーマンスは高いので、Hiveと併用（データはHDFSやKudu、S3に置く）ことで棲み分けすれば強みを活かせそうですね。