この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
データアナリティクス事業本部の鈴木です。
今回はかなり実験的な内容ですが、日頃関わりの強いデータ分析基盤の業務でChatGPTを使って業務効率化できないかと思い、考えたことを検証をしてみたのでご共有します。
背景
データ分析基盤の保守業務の悩みについて
データ分析基盤では、同じようなETLパイプラインを数多く作成していくケースが多いです。例えば、データソースのテーブルからデータを抽出し、必要な前処理をした後に、データウェアハウスのテーブルに入れるといったものです。
このような場合に、ETLツールに仕様変更が発生すると、多くのリソースに影響が発生します。特にテーブル定義とパイプラインの仕様が合わなくなり、データが取得できない・格納できない、という悩みは遭遇したことがある方もいるのではないでしょうか。
この現象を事前に検知するためには、できる限り早く変更があることを知り、影響範囲を調査する必要があります。一方で、このような保守業務を行おうとすると、どうしてもある程度は人間による作業の工数が必要になります。
特に中央集権的なデータ分析基盤の開発・保守では、例えば上記のようなことが要因でデータエンジニアがボトルネックになってしまうことが一般的に知られており、より良い方法を目指してデータメッシュのような考え方も生まれていることは以前のブログでご紹介しました。
ChatGPTで解決したい点
人間による作業が必要な理由として、このような変更は大抵の場合、リリースノートや変更のお知らせという形の自然言語で公開されるため、影響を受けるテーブルなどの各種リソースを表すコードや文書と、機械的な突き合わせが難しいことが考えられました。
今回はこの課題に着目して、ChatGPTを使い、変更内容を表す自然言語とテーブル定義のようなコードの突き合わせを自動化し、影響があるかどうか検知できないかと考えました。
まずは題材として、ETLパイプラインの仕様変更が、どのテーブルに影響を与えるかの判定を自動化できそうか試してみました。
検証の流れ・準備
まずは、ブラウザからアクセスしてすぐに試せるWebサービス版で、ChatGPTによる変更内容とテーブル定義の比較が上手くいきそうか確認しました。その後、Pythonスクリプトから複数のテーブル定義に対するチェックの自動化を目指して、サンプルのスクリプトを作成・実行しました。
検証に先立ち、Pythonスクリプトから試すため、『OpenAI Platformことはじめ 〜Organizationメンバーに招待されたら』を参考に、APIキーを発行しました。
また、APIキーはスクリプトにハードコードせず、『[初心者向き] OpenAI APIを使ってPythonでChatGPT遊びするための最初の三歩くらい』を参考に、コマンドラインの環境変数に設定しました。
export OPENAI_API_KEY="sk-xxxx"やってみる
Webサービス版から試してみる
まず、実現したいことができそうか、Webサービス版（ChatGPT Mar 14 Version）で確認してみました。
今回は、ETLツールの仕様変更があり、Decimal型の精度がETLパイプライン内のアプリケーションの方がテーブルよりも小さくなる制限ができてしまった、というケースです（これは私が適当に考えた架空のものです）。
以下のような質問をしてみました。
プロンプト
今回ETLパイプラインのBigQueryに向けの仕様変更があります。この変更では、ETLパイプライン中で扱える実数の最大精度が20から15に変更になります。
ETLパイプラインで、データソースのテーブルから、ターゲットのテーブルにデータを格納します。この２つのテーブルでカラムの型は同じです。ETLパイプラインの処理によりデータの値が変わってはいけません。
ETLパイプラインで、以下の定義のターゲットのテーブルにデータを格納する際、仕様変更の影響の有無を端的に教えてください。
CREATE TABLE sample_dataset.item_table(
id INTEGER OPTIONS(description="商品ID"),
name STRING OPTIONS(description="商品名"),
price NUMERIC(18, 2) OPTIONS(description="商品価格")
);上記のようにプロンプトは「質問文 + テーブル定義」としてみました。「20桁の精度」というところは少し前提と混ざった文章になっていますが、影響の有無が十分分かる結果を出力できていそうです。ここまで分かれば、目星がついているテーブルに対してまとめて処理を実行しておいて、後で処理結果をざーっとみるだけでも、影響を受けるテーブルとその概要が把握できそうです。
補足
ちなみに、上記のプロンプトは結構作り込みを行いました。例えば以下のように一見同じようなことを聞いているものでも、結果が変わる場合があります。
プロンプト
今回ETLパイプラインのBigQueryに向けの仕様変更があります。この変更では、ETLパイプライン中で扱える実数の最大精度が15に変更になります。
テーブルはBigQueryにあります。
以下の定義のテーブルに影響があるか端的に教えてください。
CREATE TABLE sample_dataset.item_table(
id INTEGER OPTIONS(description="商品ID"),
name STRING OPTIONS(description="商品名"),
price NUMERIC(18, 2) OPTIONS(description="商品価格")
);何回か入力してみて、多くの場合は影響があることを教えてくれるのですが、たまに上の画像のように、影響がないような出力がされます。これは恐らく影響があるかどうかの主語がETLパイプラインになっており、確かにETLパイプラインへの入力に異常がないのであれば継続して処理は実行できそうなので、必ずしも間違いとは言えなさそうです。
知りたい以上の情報を出力してくれている気もするので、その点でもプロンプトは調整する必要がありそうです。
スクリプトからAPIで試してみる
次にPythonスクリプトからまとめて影響の有無をチェックしてみました。今回は、検討材料としてプロジェクト管理ツールなどに添付することを想定し、マークダウン形式のレポートに出力できそうかも試しました。回答部分はChatGPTに作ってもらい、スクリプト内でマークダウンの表に整形しました。
まず、できたサンプルのマークダウン文書からみてみましょう。
以下がMarkdown Preview Enhancedでのプレビュー表示です。
マークダウンの文書は以下です。
report.md
## 影響範囲の調査結果
| | filename | answer |
|---:|:----------------|:-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 0 | sample_sql2.sql | 仕様変更により、実数の最大精度が15に変更されたため、col_doubleのような浮動小数点数型のカラムがある場合、データの値が丸められてしまう可能性があります。しかし、提供されたテーブル定義には浮動小数点数型のカラムが含まれていないため、この仕様変更は影響を与えません。ただし、定義されたカラムの精度には注意して作業する必要があります。 |
| 1 | sample_sql3.sql | 仕様変更の影響はあります。ETLパイプライン中で扱える実数の最大精度が15に変更になるため、priceカラムの精度にも影響がでます。現在の定義では、priceカラムはNUMERIC(15,2)と定義されていますが、最大精度が15に変更されることにより、小数点以下の桁数を含めた最大の桁数が15に制限されます。したがって、priceカラムに格納される値の精度に制限がかかることになります。 |
| 2 | sample_sql1.sql | 仕様変更により、ETLパイプラインで処理される商品価格の最大精度が15に制限されることになります。したがって、price列のNUMERIC型の定義(18, 2)は、ETLパイプラインによって扱うことのできる最大精度より大きいため、影響を受けます。これにより、ETLパイプラインがこのテーブルからデータを読み取り、ターゲットテーブルに格納する際に、price列の値が丸められる可能性があります。このような場合、価格情報の精度が低下したり、誤った価格情報が収集される可能性があります。したがって、新しい仕様に合わせて、price列の定義をNUMERIC(15, 2)に変更する必要があります。 |Backlogなどマークダウン形式が使えるサービスを使っている場合は、ここまでできていればチケットやWikiにぺたっと貼って共有ができそうです。
続いて、どのように作ったか説明します。今回は以下のような階層でコード類を準備しました。
.
├── ddl # 影響を確認したいテーブル定義
│ ├── sample_sql1.sql
│ ├── sample_sql2.sql
│ └── sample_sql3.sql
├── sample_question.txt # ChatGPTに投げる質問のテンプレート
├── estimate_effect.py # マークダウン形式の結果を出力するスクリプト
└── report.md # script.pyの結果をリダイレクトするマークダウン形式の結果を出力するスクリプトはestimate_effect.pyで、以下のように実行しました。
# レポートの生成
python3 estimate_effect.py > report.mdスクリプトの内容は以下のようにしており、ddl配下のSQL文を読み出し、同じく読み出したChatGPTに投げる質問のテンプレートと結合して、ChatGPTにリクエストを送ります。回答はまとめてPandasのデータフレームに変換し、to_markdown関数でマークダウン形式の表に変換しました。
openai・pandas・tabulate（to_markdown関数で必要）は事前にpipでインストールしておきました。
estimate_effect.py
import os
import openai
import pandas as pd
# 変数の設定
openai.api_key = os.environ["OPENAI_API_KEY"]
starting_point_directory = "./ddl"
answers = []
def ask_question(question_sentence):
""" ChatGPTに質問する。
"""
response = openai.ChatCompletion.create(
model="gpt-3.5-turbo",
messages=[
{"role": "user", "content": question_sentence},
],
)
return response.choices[0]["message"]["content"].strip()
# 質問文を開く
with open("./sample_question.txt", 'r') as question_f:
question = question_f.read()
# 影響を受けるか判定する
for filename in os.listdir(starting_point_directory):
# 確認するSQLを開く
with open(f"{starting_point_directory}/{filename}", 'r') as sql_f:
sql = sql_f.read()
question_sentence = f"{question}\n{sql}"
answer = ask_question(question_sentence)
answer_dict = {}
answer_dict["filename"] = filename
answer_dict["answer"] = answer
answers.append(answer_dict)
# マークダウン形式でレポートを表示する
df_answers = pd.json_normalize(answers)
print("## 影響範囲の調査結果")
print(df_answers.to_markdown())質問のテンプレートはUIから入力したものと同様です。
sample_question.txt
今回ETLパイプラインのBigQueryに向けの仕様変更があります。この変更では、ETLパイプライン中で扱える実数の最大精度が20から15に変更になります。
ETLパイプラインで、データソースのテーブルから、ターゲットのテーブルにデータを格納します。この２つのテーブルでカラムの型は同じです。ETLパイプラインの処理によりデータの値が変わってはいけません。
ETLパイプラインで、以下の定義のターゲットのテーブルにデータを格納する際、仕様変更の影響の有無を端的に教えてください。テーブル定義については、UIで入力したものに加え、過去のDevelopersIOのブログで記載されていたテーブル定義を拝借してきました。
長くなるのでこちらは折りたたんでおきます。
チェックしたテーブル定義
-- sample_sql1.sql
CREATE TABLE sample_dataset.item_table(
id INTEGER OPTIONS(description="商品ID"),
name STRING OPTIONS(description="商品名"),
price NUMERIC(18, 2) OPTIONS(description="商品価格")
);
-- sample_sql2.sql
CREATE TABLE `dataset_1.table_A1_cp`
(
col_string STRING NOT NULL OPTIONS(description="STRING型カラム"),
col_integer INT64 NOT NULL OPTIONS(description="INTEGER型カラム"),
col_date DATE OPTIONS(description="DATE型カラム"),
col_datetime DATETIME OPTIONS(description="DATETIME型カラム"),
col_timestamp TIMESTAMP OPTIONS(description="TIMESTAMP型カラム"),
col_record STRUCT<a ARRAY<STRING> OPTIONS(description="レコードA"), b BOOL OPTIONS(description="レコードB")> OPTIONS(description="RECORD型カラム")
)
PARTITION BY TIMESTAMP_TRUNC(col_timestamp, MONTH)
CLUSTER BY col_string, col_integer
OPTIONS(
partition_expiration_days=90.0,
expiration_timestamp=TIMESTAMP "2021-12-31T14:59:59.000Z",
description="table_Aから作成",
labels=[("sample", "table_a"), ("type", "partition")]
);
-- sample_sql3.sql
CREATE TABLE sample_dataset.new_item_table(
id INTEGER OPTIONS(description="商品ID"),
name STRING OPTIONS(description="商品名"),
price NUMERIC(15, 2) OPTIONS(description="商品価格")
);出力の内容は概ね問題ないように思いました。sample_sql3.sqlに対する結果については、本当は影響がないように思いますが、「ETLパイプラインで、データソースのテーブルから、ターゲットのテーブルにデータを格納します。この２つのテーブルでカラムの型は同じです。」という制限が漏れた結果、スケールが異なるデータが来るとデータが変わってしまうという内容を出力しているように思われます。いまいまは質問を変えることで解決していくことになりそうですが、今後APIで使うモデルをより高性能なものに変えることで解決する可能性がありそうです。
気をつけた点
データ分析基盤の場合、似たようなリソースが多数存在しがちということを記載しましたが、それが理由でChatGPTに質問するときに、「以下のテーブルに影響がないか教えてください。後、○○のテーブル定義と△△テーブル定義と......□□も！」のようなプロンプトを投げてしまいたくなりました。一方で、以下の記事のようにトークンの長さ制限があるので、それを超過することがないよう、個別のテーブル定義に対して質問するようにスクリプトを作りました。
ちなみに一番最初に掲載したWebサービス版で試したプロンプトは、上記ブログの方法で数えたところ、トークン数が400強くらいでした。
また、あまり質問文が長いと記載内容が自分でもよく分からなくなってしまったり、以下の記事の紹介内容にあるようなテクニックも入れ込みにくくなってしまうので、個別のテーブル定義に対して質問してプロンプトが簡潔になるようにしました。
試してみての感想
今回は簡単なSQLのテーブル定義と変更内容の文章で、人間が納得できる程度に影響の有無の判定ができるか試してみましたが、ある程度上手くいっているように思いました。
変更内容の文章がより抽象的になってしまうと出力も悪くなってしまいそうですが、テーブル定義はSQLで書かれている限りはもう少し大きなものになったとしても大丈夫そうな印象を受けました。sample_sql2.sqlの中身はある程度の数のカラムがあり、オプションもいろいろ書いてありますが、影響範囲についてある程度期待できる内容が説明されていました。
事前の影響範囲の説明やリリースノートレベルであればかなり具体的に記載されていることが多く、テーブル定義もSQLで明確に表現されているので、今後もこのようなケースであれば良い結果が出てくることを推測しています。
一方で、比較対象がSQLほど一般的ではない独自の言語やフォーマットであったり、影響範囲の説明やリリースノートが抽象的であったりする場合は、結果が悪くなることが予想されます。実際、今回試してみたプロンプトもたまに意図しない結果が返ってくることがあり、試行錯誤をしました。プロンプトについてはチェックを実行する側が変更内容をより具体的に理解し、より適切なものを考えていく必要がありそうです。
それができるためにも、今後、技術選択をしたり自分でプロダクトを開発したりする中で、リリースノートなどをより具体的に書いたり、比較的広く使われているフォーマットでエクスポートできることが、重要になってくるかもしれないなと思いました。
最後に
データ分析基盤のETLパイプラインの仕様変更内容とETLの対象となるテーブル定義（SQL文）をChatGPTで比較して、仕様変更がどのテーブルに影響を出しそうか確認してみました。
今回の例に限らず、似たような課題は多くあると思いますので、参考になりましたら幸いです。
6
