クエリを移行する

Sigma のクエリを Presto から Trino に移行します。

Sigma のクエリインフラを Presto v334 から Trino v414 にアップグレードしました。このアップグレードにより、ほとんどのクエリは高速で実行されますが、一部のクエリでは予期しないエラーが発生したり、異なる形式の結果が生成されることがあります。

以下の推奨事項を使用して、Sigma クエリが Treno v414 との互換性を持つようにします。

無効なタイムゾーン

Treno v414 では、AMERICA/NEW_YORK タイムゾーンは無効になりました。

-- FAILED: Presto error: NOT_SUPPORTED: Time zone not supported: AMERICA/NEW_YORK
select
    date_format(
        c.created AT TIME ZONE 'AMERICA/NEW_YORK',
        '%Y-%m-%d'
    )
from
    charges c

代わりに、America/New_York を使用します。

-- VALID
select
    date_format(
        c.created AT TIME ZONE 'America/New_York',
        '%Y-%m-%d'
    )
from
    charges c

無効な列参照

Treno v414 では、範囲外の元のサブクエリまたは条件付きテーブル式 (CTE) を使用して列名を参照することはできません。

-- FAILED: Presto error: COLUMN_NOT_FOUND: Column 'c.created' cannot be resolved
select c.created from (select created from charges c)

サブクエリ c は最上位レベルで定義されていませんが、定義された範囲の外で最上位レベルで参照されているため、以前のクエリは無効です。

-- VALID
select created from (select created from charges c)

-- VALID
select c.created from (select created from charges) c

サブクエリを使用せずに列を参照するか、参照と同じレベルでサブクエリを定義します。

科学的表記

Trino v414 で double を varchar にキャストすると、Presto v334 のような 10 進法による表記ではなく、科学的表記で結果が生成されます。

-- RESULT: 1.0E2
select cast(100.0 as varchar)

この 10 進法による表記を維持するには、double を decimal としてキャストしてから、varchar としてキャストします。

-- RESULT: 100.0
select cast(cast(100.0 as decimal(18,1)) as varchar)

タイムスタンプ関数

FROM_UNIXTIME

from_unixtime を使用すると、Treno v414 は結果のタイムスタンプが UTC であると想定し、末尾に「UTC」を追加します。

-- Trino v414 RESULT: 1970-01-01 00:00:00.000 UTC
-- Presto v334 RESULT: 1970-01-01 00:00:00 +0000
select from_unixtime(0)

末尾の「UTC」を削除するには、from_unixtime の結果を timestamp としてキャストします。

-- RESULT: 1970-01-01 00:00:00 +0000
select cast(from_unixtime(0) as timestamp)

TO_ISO8601

Presto v334 では、to_iso8601 を使用すると Zulu タイムゾーンのサフィックス (「Z」) をタイムゾーンのないタイムスタンプの末尾に追加しますが、Trino v414 では追加されません。

-- Presto v334 RESULT: 2024-04-01T00:00:00.000Z
-- Trino v414 RESULT: 2024-04-01T00:00:00
select to_iso8601(timestamp '2024-04-01')

末尾に Zulu タイムゾーンのサフィックスが追加されていることを確認するには、UTC のタイムスタンプを解釈してから to_iso8601 を呼び出します。

-- RESULT: 2024-04-01T00:00:00Z
select to_iso8601(timestamp '2024-04-01' at time zone 'UTC')

クエリの非決定性

Sigma バージョンに関係なく、クエリが確定的でない場合、得られる結果が実行ごとに異なる可能性があります。非決定性の結果が生成される可能性のある一般的なクエリパターンを以下に示します。

上位 K 件のクエリ

-- POTENTIALLY NON-DETERMINISTIC
select * from charges order by created DESC limit 10

最新の 10 番目と 11 番目の支払いが同時に作成された場合、どちらの支払いが返されるかは保証されません。確定的な結果を得るには、必ず一意の識別子で分類してください。

-- DETERMINISTIC
select * from charges order by created DESC, id limit 10

ウィンドウ関数による集計

-- POTENTIALLY NON-DETERMINISTIC
select
    *
from
    (
        select
            c.*,
            row_number() over (
                partition by c.customer_id
                order by
                    c.amount DESC
            ) as row_rank
        from
            charges c
    )
where
    row_rank = 1

上記のクエリは、row_number を使用して各顧客の最大の支払いを返します。ウィンドウ関数をご覧ください。顧客が同一の最大金額で複数の支払いを行っている場合、どの支払いが返されるかは保証されません。

確定的な結果を得るために、ウィンドウの順序を一意の識別子で並べ替えます。

-- DETERMINISTIC
select
    *
from
    (
        select
            c.*,
            row_number() over (
                partition by c.customer_id
                order by
                    c.amount DESC, c.created DESC, c.id
            ) as row_rank
        from
            charges c
    )
where
    row_rank = 1