前回の投稿の続きとして、少し長い（200行以上）T-SQLストアドプロシージャーのPL/SQLへの変換をChatGPT（gpt-4-1106-preview）にやってもらいました。その結果、結構な部分は問題なく変換できているのですが、
- 一部、PL/SQLでは無効な構文を使ってしまう。
- 細かいロジック間違いを混入させてしまう。
- 一部のブロックを丸っとスキップしてしまう。
など問題があったので、以下に詳細を記します。
1. 前置き
前回の投稿では、さまざまな構成要素（DMLやIF文、カーソルループ、例外処理、他のストアドの呼び出しなど）を含む高々40行程度の短いT-SQLストアドプロシージャー（SQL Server向け）をChatGPTを用いてPL/SQL（Oracle向け）に変換してもらいました。
プロンプトの調整次第で思った以上にうまく変換できたのですが、今後の課題として以下を挙げました。
- ChatGPT への指示の都度、若干コードスタイルや変換方法が異なる
- 長いストアドを変換したい場合、トークン数上限に抵触する
今回は、後者の課題について確認してみました。
ただ、利用するモデルが gpt-4からgpt-4-1106-previewになったことでトークン数上限が大幅にアップされたことから、トークン数上限の話は一旦忘れて（そもそもトークン数上限に達するような公開できるストアドを準備できない）、少し長め（200行以上）のT-SQLストアドプロシージャーを正しく変換できるかという点にフォーカスして確認します。
2. 変換元のT-SQLストアドプロシージャーの内容
まず、今回変換元としてChatGPTに渡すT-SQLストアドプロシージャーの処理概要を以下に示します。
- ① データチェック
- stg_ordersテーブルのデータについて以下のチェックを行う。
- 0件でないこと
- stg_ordersテーブルの最新注文日（max(order_datetime)を日単位に切り捨てたもの）がcustomers/itemsテーブルのvalid_dateに含まれていること（要はマスターテーブルが必要な日断面まで登録されていること）
- customers/itemsテーブルのレコードに紐づかないstg_ordersテーブルのレコードがないこと
- stg_ordersテーブルのデータについて以下のチェックを行う。
- ② ordersテーブル更新
- stg_ordersテーブルのデータを元にordersテーブルをUPSERT（order_idをキーになければINSERT、あればUPDATE）する。
- その際にitemsテーブルと結合してorder_costも計算して列追加する。
- ③ 該当顧客IDのordersデータを削除
- optoutテーブルに含まれる顧客IDに関するレコードをordersテーブルから削除する。
- ④ データマート作成（その１）
- order_date, item_categoryごとに集計したデータマートを作成する。
- 更新されるデータを見越して、指定した期間を洗い替え（期間は引数で指定）。
- ⑤ データマート作成（その１）
- order_date, customer_regionごとに集計したデータマートを作成する。
- 更新されるデータを見越して、指定した期間を洗い替え（期間は引数で指定）。
- 以上の処理が終了したらトランザクションをコミットする。
これを１つのT-SQLストアドプロシージャーとします。
create procedure update_tables(
@logical_date date,
@refresh_range_in_days integer
)
as
begin
-- stg_ordersの件数が0件でないことをチェック
begin
if not exists(select top(1) order_id from stg_orders)
throw 50001, '処理対象(stg_orders)の件数が0件です。', 1;
end;
-- items/customersのデータが最新注文日まで登録されていることをチェック
begin
declare @max_date date
select @max_date = max(cast(order_datetime as DATE)) from stg_orders;
declare @items_is_latest bit = 0;
if exists(select top(1) item_id from items where valid_date = @max_date)
set @items_is_latest = 1;
declare @customers_is_latest bit = 0;
if exists(select top(1) customer_id from customers where valid_date = @max_date)
set @customers_is_latest = 1;
declare @message varchar(100) = null;
if (@items_is_latest = 0) and (@customers_is_latest = 0)
set @message = 'items/customersのデータが最新ではありません。'
else if @items_is_latest = 0
set @message = 'itemsのデータが最新ではありません。'
else if @customers_is_latest = 0
set @message = 'customersのデータが最新ではありません。'
if @message is not null
throw 50002, @message, 1
end;
-- stg_orders => itemsの参照整合性をチェック
begin
select top(1)
o.order_id
from
stg_orders o
where
not exists(
select
1
from
items i
where
i.item_id = o.item_id
and
i.valid_date = cast(o.order_datetime as date)
)
;
if @@ROWCOUNT > 0
throw 50003, 'stg_ordersに不正な(item_id, order_datetime)が含まれています。', 1
end;
-- stg_orders => customersの参照整合性をチェック
begin
select top(1)
o.order_id
from
stg_orders o
where
not exists(
select
1
from
customers c
where
c.customer_id = o.customer_id
and
c.valid_date = cast(o.order_datetime as date)
)
;
if @@ROWCOUNT > 0
throw 50003, 'stg_ordersに不正な(customer_id, order_datetime)が含まれています。', 1
end;
-- 以下、テーブルの更新
begin try
begin transaction;
-- stg_ordersを基にordersをUPSERTし、成功すればstg_ordersを全削除
begin
-- 更新対象レコードを削除
delete from orders
where exists (
select 1
from
stg_orders o
where
orders.order_id = o.order_id
);
-- レコードを追加
insert into orders
select
o.order_id,
o.order_datetime,
o.customer_id,
o.item_id,
o.order_quantity,
o.order_amount,
o.order_quantity * i.item_cost order_cost
from
stg_orders o
inner join
items i on (
o.item_id = i.item_id
and cast(o.order_datetime as date) = i.valid_date
)
;
-- stg_ordersを全削除
delete from stg_orders;
end;
-- オプトアウトされたcustomer_idのデータをordersから削除
begin
delete from orders
where exists(
select 1
from
optout o
where
orders.customer_id = o.customer_id
);
end;
-- orders_summary_by_item_categoryの更新
-- @logical_date - @refresh_range_in_daysから@logical_dateまでの期間を洗い替える
begin
-- 洗い替え期間のデータを削除
delete from orders_summary_by_item_category
where order_date
between
dateadd(day, 1 - @refresh_range_in_days, @logical_date)
and @logical_date;
-- データ作成
insert into orders_summary_by_item_category (
order_date,
item_category,
total_order_quantity,
total_order_amount,
total_order_cost
)
select
cast(o.order_datetime as date),
i.item_category,
sum(o.order_quantity),
sum(o.order_amount),
sum(o.order_cost)
from
orders o
inner join
items i on (
o.item_id = i.item_id
and cast(o.order_datetime as date) = i.valid_date
)
where
o.order_datetime >=
dateadd(day, 1 - @refresh_range_in_days, @logical_date)
group by
cast(o.order_datetime as date),
i.item_category
;
end;
-- order_summary_by_customer_regionの更新
-- @logical_date - @refresh_range_in_daysから@logical_dateまでの期間を洗い替える
begin
-- 洗い替え期間のデータを削除
delete from orders_summary_by_customer_region
where order_date
between
dateadd(day, 1 - @refresh_range_in_days, @logical_date)
and @logical_date;
-- データ作成
insert into orders_summary_by_customer_region (
order_date,
customer_region,
total_order_quantity,
total_order_amount,
total_order_cost
)
select
cast(o.order_datetime as date),
c.customer_region,
sum(o.order_quantity),
sum(o.order_amount),
sum(o.order_cost)
from
orders o
inner join
customers c on (
o.customer_id = c.customer_id
and cast(o.order_datetime as date) = c.valid_date
)
where
o.order_datetime >=
dateadd(day, 1 - @refresh_range_in_days, @logical_date)
group by
cast(o.order_datetime as date),
c.customer_region
;
end;
-- 全てのテーブルの更新が成功してからコミットする
commit;
end try
begin catch
print error_message();
rollback;
throw 50004, 'テーブル更新が失敗しました。', 1
end catch;
end;
ちなみに、あるべき設計としては、このように多段にテーブルを更新していくデータパイプラインは、１つの処理にまとめず個別の処理に分割するべきです（リランやバックフィルを実行しやすくするため）。ただし、今回はとにかく長いT-SQLコードを用意したかったこと、実際にこのような好ましくないコードは良く遭遇するということで以上のようなコードになっています。その点ご容赦ください。
3. 変換時に渡すプロンプト
今回、以下のようなプロンプトでChatGPTに変換を依頼しています。
You are a DB engineer who is familiar with SQL Server's T-SQL and Oracle's PL/SQL.
Rewrite the T-SQL stored procedure code in the code block below into Oracle PL/SQL code that has the same behavior.
Follow these rules when rewriting your code:
- Do not add exception handling not included in the original T-SQL code
- Do not add transaction control processing that is not included in the original T-SQL code
- Do not specify a length for the data type in a PL/SQL formal parameter declaration.
- Do not use the "EXISTS" operator in the conditional expression of an "IF" statement in a PL/SQL code.
- Please rewrite the entire provided T-SQL code to PL/SQL. Do not omit it.
- １行目は役割設定を入れた方が良いらしいので入れています。
- ５つの箇条書きのうち１～４番目は前回の投稿でのトライを踏まえて指示しています。
- １番目と２番目のルールは、前回ChatpGPTが勝手に変換元のコードにない例外/トランザクション制御の処理を入れたので、それを防ぐために入れています。
- ３番目のルールは、PL/SQLの引数（formal paameter）のデータ型では長さ指定ができないことを指示しています（これがないとnumber(38)などコンパイルエラーなコードを生成します）。
- ４番目のルールは、PL/SQLではIF条件式でEXISTS句が使えないことを指示しています。
- ５番目は前回の投稿を受けてではないですが、元のコードが長いと「以下略」のような回答を生成するので、それを防ぐために指示しています。
4. 変換結果とその問題点
ChatGPTに上述のT-SQLストアドプロシージャーのコードをPL/SQLに変換させた結果が以下になります。
CREATE OR REPLACE PROCEDURE update_tables(
logical_date IN DATE,
refresh_range_in_days IN INTEGER
)
IS
max_date DATE;
items_is_latest NUMBER(1);
customers_is_latest NUMBER(1);
message VARCHAR2(100);
row_count INTEGER;
BEGIN
-- stg_ordersの件数が0件でないことをチェック
IF (SELECT COUNT(1) FROM stg_orders) = 0 THEN
RAISE_APPLICATION_ERROR(-20001, '処理対象(stg_orders)の件数が0件です。');
END IF;
-- items/customersのデータが最新注文日まで登録されていることをチェック
SELECT MAX(TRUNC(order_datetime)) INTO max_date FROM stg_orders;
items_is_latest := 0;
SELECT COUNT(1) INTO items_is_latest FROM items WHERE valid_date = max_date AND ROWNUM = 1;
customers_is_latest := 0;
SELECT COUNT(1) INTO customers_is_latest FROM customers WHERE valid_date = max_date AND ROWNUM = 1;
IF items_is_latest = 0 AND customers_is_latest = 0 THEN
message := 'items/customersのデータが最新ではありません。';
ELSIF items_is_latest = 0 THEN
message := 'itemsのデータが最新ではありません。';
ELSIF customers_is_latest = 0 THEN
message := 'customersのデータが最新ではありません。';
END IF;
IF message IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20002, message);
END IF;
-- stg_orders => itemsの参照整合性をチェック
SELECT COUNT(1) INTO row_count
FROM stg_orders o
WHERE NOT EXISTS (
SELECT 1
FROM items i
WHERE i.item_id = o.item_id
AND i.valid_date = TRUNC(o.order_datetime)
) AND ROWNUM = 1;
IF row_count > 0 THEN
RAISE_APPLICATION_ERROR(-20003, 'stg_ordersに不正な(item_id, order_datetime)が含まれています。');
END IF;
-- stg_orders => customersの参照整合性をチェック
SELECT COUNT(1) INTO row_count
FROM stg_orders o
WHERE NOT EXISTS (
SELECT 1
FROM customers c
WHERE c.customer_id = o.customer_id
AND c.valid_date = TRUNC(o.order_datetime)
) AND ROWNUM = 1;
IF row_count > 0 THEN
RAISE_APPLICATION_ERROR(-20003, 'stg_ordersに不正な(customer_id, order_datetime)が含まれています。');
END IF;
-- 以下、テーブルの更新
-- Autonomous transaction needed to commit or rollback independently from the main transaction
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- stg_ordersを基にordersをUPSERTし、成功すればstg_ordersを全削除
DELETE FROM orders
WHERE order_id IN (
SELECT order_id FROM stg_orders
);
INSERT INTO orders (
order_id,
order_datetime,
customer_id,
item_id,
order_quantity,
order_amount,
order_cost
)
SELECT
o.order_id,
o.order_datetime,
o.customer_id,
o.item_id,
o.order_quantity,
o.order_amount,
o.order_quantity * i.item_cost
FROM
stg_orders o
JOIN items i ON o.item_id = i.item_id AND TRUNC(o.order_datetime) = i.valid_date;
DELETE FROM stg_orders;
-- オプトアウトされたcustomer_idのデータをordersから削除
DELETE FROM orders
WHERE customer_id IN (
SELECT customer_id FROM optout
);
-- orders_summary_by_item_categoryの更新
-- logical_date - refresh_range_in_daysからlogical_dateまでの期間を洗い替える
DELETE FROM orders_summary_by_item_category
WHERE order_date BETWEEN (logical_date - refresh_range_in_days) AND logical_date;
INSERT INTO orders_summary_by_item_category (
order_date,
item_category,
total_order_quantity,
total_order_amount,
total_order_cost
)
SELECT
TRUNC(o.order_datetime),
i.item_category,
SUM(o.order_quantity),
SUM(o.order_amount),
SUM(o.order_cost)
FROM
orders o
JOIN items i ON o.item_id = i.item_id AND TRUNC(o.order_datetime) = i.valid_date
WHERE o.order_datetime >= (logical_date - refresh_range_in_days)
GROUP BY
TRUNC(o.order_datetime),
i.item_category;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ROLLBACK;
RAISE_APPLICATION_ERROR(-20004, 'テーブル更新が失敗しました。');
END;
END update_tables;
以下で、この生成されたコードの問題点を指摘していきます。
4-1. IF条件式内でSEELCT
PL/SQLではIF条件式内でSELECTは使えません。なので、以下の箇所はコンパイルエラーになります。
IF (SELECT COUNT(1) FROM stg_orders) = 0 THEN
プロンプトでIF条件式内のEXISTS句を禁止していましたが、SELECT自体を禁止すべきでしたね。これはプロンプトを修正すれば対応可能です。
4-2. AUTONOMOUS_TRANSACTIONプラグマの不適切な追加
AUTONOMOUS_TRANSACTIONを追加していますが、この位置では本来入れられないのでコンパイルエラーになります。これはT-SQLとPL/SQLにおけるトランザクションの違いからすると、そこに入れたくなる気持ちは分るのですが、全体を見ると不適切です。元コードが長いということが効いているのかもしれません。
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
4-3. 日付計算のロジック違い
データマート作成時に@logical_dateを起点に過去@refresh_range_in_day日分を洗い替える処理なのですが、１日ずれています。コンパイルエラーにはなりませんが、作成されるデータマートのデータが異なってきます。
- T-SQLコード（変換元）
where
o.order_datetime >=
dateadd(day, 1 - @refresh_range_in_days, @logical_date)
- PL/SQLコード（変換結果）
WHERE order_date BETWEEN (logical_date - refresh_range_in_days)
4-4. orders_summary_by_customer_regionテーブル更新処理の欠落
orders_summary_by_customer_regionテーブルに対する処理（DELETE文とINSERT文）が丸っと落とされています。マジか…
4-5. 件数チェックの非効率化
コンパイルエラーになる箇所ではあるのですが、stg_ordersテーブルのレコード存在チェックするためにcount(1)をして件数を取得しています。これはフルスキャンを回避できないので効率的ではありません。元のT-SQLコードでは top(1) を指定しているので、１件でも見つかればそこで終了でフルスキャンは発生しません。
IF (SELECT COUNT(1) FROM stg_orders) = 0 THEN
5. まとめ
変換コードの問題のうち、
- 4-1.と4-5.は上手く変換できることもあり、再現性が怪しい。プロンプトで制御できるかもだけど、再現性がないと評価もしづらい。
- 4-3. のような細かいロジックの誤りはあるので、入念なテストは必要。
- 4-2.と4-4.は入力が長いことが問題ではないかと想像。入力を細かく区切って変換させればうまくいくかも。
ということで、元コードが少し長いことに起因すると思われる問題もありつつ、それ以外の問題もあり、実用化までは遠いなぁという感想です。
コンパイルエラーは簡単に見つけられるのとプロンプトで対策の打ちようもあるのですが、ロジック誤りに関してはテストを充実させるというありきたりな解決策しかない気もします。生成AIのデータパイプラインテストへの応用はそれはそれで興味あるネタなのですが。
6. おまけ
ちなみに、②の処理は当初DELETE⇒INSERTではなくMERGE文としていました。その場合、ChatGPTはSQL ServerとOracleのMERGEは等価ではないといって、頑なに変換してくれませんでした。それも結構ショック… SQL ServerとOracleでどう違うのかは分かりませんが。