本記事の目的

Amazon Athena 性能検証（簡易ETLケース）

この記事と同データ、同ロジックを用いてAurora PostgreSQLで性能評価するのが目的。

検証の前提

Athena検証で利用したテーブルと同じテーブル。
データは1000万件で合計サイズは約700MB

CREATE TABLE test6
(
  col1        VARCHAR(30),
  col2        VARCHAR(30),
  col3        VARCHAR(30),
  col4        VARCHAR(30),
  col5        VARCHAR(30),
  col6        VARCHAR(30),
  col7        VARCHAR(30)
);

Athena検証で利用したLambda UDFと同じPL/pgSQLは次の通り

drop function if exists dateconvert(text);

CREATE OR REPLACE FUNCTION dateconvert(date_text text)
RETURNS TEXT AS $$
DECLARE
  parsed_date text;
  regex_pattern text;
  BEGIN
    regex_pattern := '^\d{4}-\d{2}-\d{2}$';
    IF date_text IS NULL OR date_text = '' OR length(date_text) != 8 THEN
      RETURN '0001-01-01';
    ELSEIF date_text = '99999999' THEN
      RETURN '9999-12-31';
    ELSE
      parsed_date := concat(substring(date_text from 1 for 4), '-', substring(date_text from 5 for 2), '-', substring(date_text from 7 for 2));
        IF NOT(parsed_date ~ regex_pattern) THEN
          RETURN '0001-01-01';
        ELSIF isDate(parsed_date, 'YYYY-MM-DD') THEN
          RETURN parsed_date;
        ELSE
          RETURN '0001-01-01';
        END IF;
    END IF;
  END;
$$ LANGUAGE plpgsql;

drop function if exists isDate(text, text);

create or replace function isDate(date_text text, format text)
returns boolean as $$
declare
  parsed_date date;
begin
  begin
    parsed_date := to_date(date_text, format);
  exception
    when others then
      return false;
    end;
    return true;
end;
$$ language plpgsql;

CTAS処理
基本パターンは下記の通りでテストケースによってselect文を入れ替えて実行している。

#!/bin/bash

su postgres <<-EOF
psql -d test -U postgres -c "drop table test6_converted"
EOF

su postgres <<-EOF
psql -d test -U postgres -c "create table test6_converted as select trim(both from col1) as col1, dateconvert(col2) as col2, col3, col4, trim(both from col5) as col5, concat(lpad(col6, 7, '0'), lpad(col7,7,'0')) as col6 from test6"
EOF

検証結果

AuroraはAthenaと比較して一桁遅い結果となった。
UDF呼び出し回数は一レコード一回。
後述するがUDF実行回数によってこの差は大きく広がる（＝Auroraがさらに性能劣化する。）
本検証ではLimitlessの想定効果（実行時間が1/16になる）がなかった。
ACUのメモリサイズ2GiBを超えるデータを対象にACUごとの並列処理となっているものと想像している。後日検証予定。

サービス	条件	処理時間	備考
Athena		17秒	Athenaの場合S3import/export処理不要
Java(XFS)	C5.4xlarge	24秒	S3処理無し（追い風参考記録）
Aurora Serverless v2	0-256ACU	201秒	S3import/export合計が約40秒
Aurora Limitless	16-64ACU	202秒	※S3処理無し（追い風参考記録）
Glue PySpark	2DPU	197秒	UDF実行回数による性能劣化はない。

※記事執筆時点ではLimitlessでのS3import/export設定を実行すると処理が終わらない不具合に遭遇した。関連するリソースを削除することで処理が元に戻ったが何回トライしてもServerlessと同じ設定はできなかった。この件についてはAWS社のバグfixを待ちたい。下手するとLimitless実行中のまま何もできない時間を過ごすことになり、割高なLimitless従量課金が無駄になるのでS3連携機能は避けたほうがよい。

PL/pgSQLの呼び出し回数観点での性能比較

Athenaの場合はUDF実行回数によって性能劣後しないがPL/pgSQLの性能劣化は下記の通り大きい。この検証は手元のノートPCでの結果のため前述の実行時間とは比較できない。
AthenaのUDFが性能劣化しない理由はAthena内でのLambda最適化による。Athenaでは一レコード単位で一回Lambdaを呼び出すのではなくまとまったレコード単位で関数実行するとドキュメントに明記されている。

PL/pgSQL実行回数	処理時間
0	7秒
1	83秒
2	165秒
3	234秒

まとめ

AWSからでているETLに関する記事で、Athenaを簡易ETLで使えるというものがある。本記事で記載したとおりS3を中心とするデータレイク環境においてAthenaは最速のツールとなる。
AWSからはAuroraを簡易ETLに使えるとの主張はないが、CTASならAuroraでもいけるのではと思うエンジニアは多いと想像できる。その際に注意したいのはCTAS中でのPL/pgSQL多用。本記事で検証した通り、性能面でアンチパターンとなることだけは覚えておきたい。

おまけ

Auroraの実行時間の悪さを受けてOracle最強説がもちあがったが、PL/SQLも同様に性能がでなかった。

実行したOracleのPL/SQLは下記の通り

CREATE OR REPLACE FUNCTION dateconvert(date_text varchar2) return varchar2
IS
  parsed_date varchar2(30);

BEGIN
  IF date_text IS NULL OR date_text = '' OR length(date_text) != 8 THEN
    RETURN '0001-01-01';
  ELSIF date_text = '99999999' THEN
    RETURN '9999-12-31';
  ELSE
    parsed_date := SUBSTR(date_text,1,4) || '-' || SUBSTR(date_text,5, 2) || '-' || SUBSTR(date_text, 7, 2);
      IF isDate(parsed_date, 'YYYY-MM-DD') THEN
        RETURN parsed_date;
      ELSE
        RETURN '0001-01-01';    
      END IF;


  END IF;
END;
/


create or replace function isDate(date_text varchar2, format varchar2) RETURN boolean
IS
  parsed_date date;
begin
  begin
    parsed_date := to_date(date_text, format);
  exception
    when others then
      return false;
    end;
    return true;
end;
/

※このPL/SQLを実際に書いて思ったのはOracleからPostgreSQLへの移行の難易度。PL/SQLとPL/pgSQLの違いは地味に大きく賢い移行ツールがないと簡単に移行できないと予想。PostgreSQL開発者がまじめにOracle互換を意識して開発していればよかったが、残念な結果である。

製品	自前function実行回数	処理時間
Oracle23ai	1	113秒
PostgreSQL	1	106秒

標準SQL関数は製品側で最適化されており、性能への影響は軽微。下記はPostgreSQLで自前function実行なしでの計測結果

標準のSQL関数	実行回数	処理時間
trim	0	9.3秒
trim	1	9.3秒
trim	2	9.7秒
trim	3	11秒