生成AI + Text-to-SQL を活用したCMDBチャットボット機能の開発
はじめに
こんにちは。KINTOテクノロジーズ(以下KTC) プラットフォームグループ Platform Engineeringチームで内製ツールの開発・運用をおこなっている山田です。
Platform Engineeringチームが開発するCMDBについて詳しく知りたい方は以下の記事をご覧ください!
今回はCMDBの機能の一つであるチャットボットに、生成AIとText-to-SQLを活用したCMDBのデータ検索機能とcsv出力機能を実装したお話をしたいと思います。
CMDBのチャットボットではCMDBの利用方法の質問やCMDBで管理されたデータに関する質問が可能です。
CMDBのデータに関する質問は、元々はChromaDBを使ったRAGの仕組みで質問に対する回答を生成していましたが、以下の理由からText-to-SQLでの実装に移行しました。
RAGと比較したText-to-SQLのメリット
- データの正確性と即時性
- CMDBのデータベースから直接最新データをリアルタイムで取得が可能
- データの更新に追加の処理が不要
- システムの簡素化
- ベクトルDBやEmbedding処理のためのインフラが不要(ChromaDBとEmbeddingしたデータの追加用バッチが不要になった)
これらの理由から、CMDBのような構造化されたデータを扱うシステムにおいては、Text-to-SQLの方が適していると判断しました。
Text-to-SQL とは
Text-to-SQLは自然言語のクエリからSQLクエリに変換する技術のことです。そのためSQLの知識を持たないユーザーでもデータベースから必要な情報を簡単に抽出することが可能になります。
これにより、CMDBのデータベースで管理しているプロダクト、ドメイン、チーム、ユーザー、ECR, VMDR等の脆弱性情報などのデータを自然言語のクエリから取得できるようになります。
KTC内で活用できそうな例だと、
- 適切な管理がされていないドメイン(CMDBでプロダクトと紐づいていないドメイン)一覧を取得
- 全社員の Atlassian ID の取得
- MSP(Managed Service Provider)チームがPCの脆弱性対応などの依頼を、該当者をメンションした形でチケット作成するため
- グループごとに担当するプロダクトの関連リソースに検出された脆弱性数の集計
- AWSリソースの起動停止スケジュールが設定されていないプロダクトの抽出
以前までだとこのようなデータの抽出依頼がPlatform Engineeringチームに来た際、担当者がCMDBのデータベースから直接SQLクエリを実行し、データを抽出、加工して依頼者にデータを渡していました。
依頼者がCMDBのチャットボットでText-to-SQLを使ってデータを抽出することができるようになると、以下の図のようにわざわざ担当者に依頼をしなくても簡単にデータの抽出ができるようになります。
Text-to-SQLは便利な機能ですが、安全でないSQL生成のリスクに気を付けなければなりません。
以下の図の例は極端なケースですが、自然言語からSQLが生成されるため意図せずにデータの更新や削除、テーブル構造の変更をするSQL文が生成される恐れがあります。
そのため以下の方法で安全でないSQLの生成を回避する必要があります。
- Read OnlyのDBエンドポイントに接続する
- DBのユーザーを参照権限のみに設定する
- アプリケーションの実装で
SELECT
以外のコマンドは実行しないようバリデーションチェックをおこなう
システム構成
こちらがCMDBのアーキテクチャになります。今回のお話に関係のないリソースは除外してあります。
最初に説明した通り、元々ベクトルDBとしてChromaDB、CMDBの使い方に関する情報をConfluenceから取得(LlamaIndexで実装)、CMDBのデータをデータベースから取得(Spring AIで実装)してChromaDBに投入していました。
今回はCMDBのデータに関する質問の回答を Spring AI + ChromaDB でのRAG機能から、Text-to-SQLを使った機能に移行しました。
Text-to-SQLの実装
ここからは実際のコードをお見せしながら実装内容を説明したいと思います。
CMDBデータの検索機能
スキーマ情報を取得
まずはLLMにSQLを生成させるときに必要なスキーマ情報を取得します。
スキーマ情報は少ない方が精度は上がるため、必要なテーブルのみ指定する方法にしました。
また、LLMがSQL文を生成するときの判断材料としてテーブルのカラムのコメントが重要になるため、事前に全て追加しておく必要があります。
def fetch_db_schema():
cmdb_tables=['table1', 'table2', ...]
cmdb_tables_str = ', '.join([f"'{table}'" for table in cmdb_tables])
query = f"""
SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
t.TABLE_COMMENT,
c.COLUMN_NAME,
c.DATA_TYPE,
c.COLUMN_KEY,
c.COLUMN_COMMENT
FROM information_schema.COLUMNS c
INNER JOIN information_schema.TABLES t ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'cmdb' AND t.TABLE_NAME IN ({cmdb_tables_str})
ORDER BY
t.TABLE_SCHEMA,
t.TABLE_NAME,
c.COLUMN_NAME
"""
connection = get_db_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
return cursor.fetchall()
finally:
cursor.close()
connection.close()
取得結果例
TABLE_SCHEMA | TABLE_NAME | TABLE_COMMENT | COLUMN_NAME | DATA_TYPE | COLUMN_KEY | COLUMN_COMMENT |
---|---|---|---|---|---|---|
cmdb | product | プロダクトテーブル | product_id | bigint | PRI | プロダクトID |
cmdb | product | プロダクトテーブル | product_name | varchar | プロダクト名 | |
cmdb | product | プロダクトテーブル | group_id | varchar | プロダクトの担当部署(グループ)ID | |
cmdb | product | プロダクトテーブル | delete_flag | bit | 論理削除フラグ 1=削除, 0=未削除 |
取得したスキーマ情報をLLMに渡すプロンプト用のテキストにフォーマットする
def format_schema(schema_data):
schema_str = ''
for row in schema_data:
schema_str += f"Schema: {row[0]}, Table Name: {row[1]}, Table Comment: {row[2]}, Column Name: {row[3]}, Data Type: {row[4]}, Primary Key: {'yes' if row[5] == 'PRI' else 'no'}, Column Comment: {row[6]}\n"
return schema_str
カラムごとに以下のようなテキストに変換してLLMにスキーマ情報を渡します。
Schema: cmdb, Table Name: product, Table Comment: プロダクトテーブル, Column Name: product_id, Data Type: bigint, Primary Key: PRI, Column Comment: プロダクトID
Schema: cmdb, Table Name: product, Table Comment: プロダクトテーブル, Column Name: product_name, Data Type: varchar, Primary Key: no, Column Comment: プロダクト名
Schema: cmdb, Table Name: product, Table Comment: プロダクトテーブル, Column Name: group_id, Data Type: varchar, Primary Key: no, Column Comment: プロダクトの担当部署(グループ)ID
Schema: cmdb, Table Name: product, Table Comment: プロダクトテーブル, Column Name: delete_flag, Data Type: bit, Primary Key: no, Column Comment: 論理削除フラグ 1=削除, 0=未削除
CMDBのチャットボットからの質問とスキーマ情報からLLMでSQLクエリを生成する
ここが自然言語からSQLクエリを生成するText-to-SQLの部分です。
質問内容とスキーマ情報をもとにプロンプトで様々な条件を指定してLLMにSQLを生成させています。
例えば、
MySQL:8.0
で有効なクエリを生成する- ID以外の条件式はあいまい検索にする
- 基本的に論理削除されたデータは検索対象外とする
- SQL文以外は生成しない
- コンテキスト情報の追加
- 「KTCの~」、「CMDBの~」という質問は「全ての~」に変換する
- リージョンに関する質問はAWSのリージョンに変換する
東京リージョン
->ap-northeast-1
に変換
などがあり、特に「SQL文以外は生成しない」の指示が重要で、これがうまく伝わらないと
「頂いた情報をもとに次のSQLを生成しました。SELECT ~」
このような不要なテキストも含めた回答になってしまうことがよくありました。
したがって不要なテキスト、説明、マークダウン形式などが生成されない、「SELECT ~」のみのSQL文が生成されるようなプロンプトが必要です。
def generate_sql(schema_str, query):
prompt = f"""
Generate a SQL query based on the given MySQL database schema, system contexts, and question.
Follow these rules strictly:
1. Use MySQL 8.0 syntax.
2. Use `schema_name.table_name` format for all table references.
3. For WHERE clauses:
- Primarily use name fields for conditions, not ID fields
- Use LIKE '%value%' for non-ID fields (fuzzy search)
- Use exact matching for ID fields
- Include "delete_flag = 0" for normal searches
- Use "delete_flag = 1" only when the question specifically asks for "deleted" items
CRITICAL INSTRUCTIONS:
- Output MUST contain ONLY valid SQL query.
- DO NOT include any explanations, comments, or additional text.
- DO NOT use markdown formatting.
- DO NOT generate invalid SQL query.
Process:
1. Carefully review and understand the schema.
2. Generate the SQL query using ONLY existing tables and columns.
3. Double-check query against schema for validity.
System Contexts:
- Company: KINTO Technologies Corporation (KTC)
- System: Configuration Management Database (CMDB)
- Regions: AWS Regions (e.g., Tokyo region = ap-northeast-1)
Interpretation Rules:
- "KTC" or "CMDB" in query: Refer to all information in the database
Examples:
"Employees in KTC" -> "All users"
"KTC's products" -> "All products"
"Domains on CMDB" -> "All domains"
- Region mentions: Interpret as AWS Regions
Example:
"ECR repositories in Tokyo region" -> "ECR repositories in ap-northeast-1"
Database Schema:
{schema_str}
Question:
{query}
"""
return llm.complete(prompt).text.strip()
LLM, Text-to-SQLで生成したSQLに対して、SELECT
文のみ許可するようバリデーションチェックをおこなう
安全でないSQL生成リスクの対策としてRead OnlyのDBエンドポイントに接続していますが、クエリ以外のSQLが生成されていないか確認をします。
LLMで生成されたSQLクエリを実行する
LLMで生成されたSQLクエリとSQLの実行結果、質問内容からLLMで回答を生成する
最後に実行したSQLクエリ、SQLの実行結果、質問内容をLLMに渡して回答を生成します。
ここではText-to-SQLのときの指示が多いプロンプトとは違い、多くの指示はしていないですが、DBスキーマの構成や物理名は回答に含めないような指示は追加しています。
def generate_answer(executed_sql, sql_result, query):
prompt = f"""
Generate an answer based on the provided executed SQL, its result, and the question.
Ensure the answer does not include information about the database schema or the column names.
Respond in the same language as the question.
Executed SQL:
{executed_sql}
SQL Result:
{sql_result}
Question:
{query}
"""
return llm.stream_complete(prompt)
実行結果
質問:プラットフォームグループのプロダクトを教えて
この質問とデータベーススキーマからLLMが以下のようなSQLを生成します。
SELECT product_name FROM product WHERE group_name LIKE '%プラットフォーム%' AND delete_flag = 0;
そして、これらの情報とSQLの実行結果をLLMに渡して回答を生成しています。
これはECRスキャン結果の脆弱性情報の取得結果です。
CMDBデータのcsvファイル出力機能
スキーマ情報を取得
取得したスキーマ情報をLLMに渡すプロンプト用のテキストにフォーマットする
ここまではCMDBデータの検索機能と同じです。
CMDBのチャットボットからの出力依頼とスキーマ情報から、LLMでSQLクエリを含むJSONオブジェクトを生成する
ここでCMDBのデータをcsvとして出力したい内容の自然言語から、LLMを使って出力時のカラム名とそれを検索するためのSQL文のJSONオブジェクトを生成させます。
条件の内容は基本的にCMDBデータの検索機能のプロンプトと同じですが、テンプレート通りのJSONオブジェクトを生成させるための指示を強調しています。
以下がそのプロンプトです。
prompt = f"""
Generate a SQL query and column names based on the given MySQL database schema, system contexts and question.
Follow these rules strictly:
1. Use MySQL 8.0 syntax.
2. Use `schema_name.table_name` format for all table references.
3. For WHERE clauses:
- Primarily use name fields for conditions, not ID fields
- Use LIKE '%value%' for non-ID fields (fuzzy search)
- Use exact matching for ID fields
- Include "delete_flag = 0" for normal searches
- Use "delete_flag = 1" only when the question specifically asks for "deleted" items
Process:
1. Carefully review and understand the schema.
2. Generate the SQL query using ONLY existing tables and columns.
3. Extract the column names from the query.
4. Double-check query against schema for validity.
System Contexts:
- Company: KINTO Technologies Corporation (KTC)
- System: Configuration Management Database (CMDB)
- Regions: AWS Regions (e.g., Tokyo region = ap-northeast-1)
Interpretation Rules:
- "KTC" or "CMDB" in query: Refer to all information in the database
Examples:
"Employees in KTC" -> "All users"
"KTC's products" -> "All products"
"Domains on CMDB" -> "All domains"
- Region mentions: Interpret as AWS Regions
Example:
"ECR repositories in Tokyo region" -> "ECR repositories in ap-northeast-1"
Output Format:
Respond ONLY with a JSON object containing the SQL query and column names:
{{
"sql_query": "SELECT t.column1, t.column2, t.column3 FROM schema_name.table_name t WHERE condition;",
"column_names": ["column1", "column2", "column3"]
}}
CRITICAL INSTRUCTIONS:
- Output MUST contain ONLY the JSON object specified above.
- DO NOT include any explanations, comments, or additional text.
- DO NOT use markdown formatting.
Ensure:
- "sql_query" contains only valid SQL syntax.
- "column_names" array exactly matches the columns in the SQL query.
Database Schema:
{schema_str}
Question:
{query}
"""
LLM, Text-to-SQLで生成したSQLに対して、SELECT
文のみ許可するようバリデーションチェックをおこなう
LLMで生成されたSQLクエリを実行する
ここもCMDBデータの検索機能と同じです。
実行結果を使ってcsvファイルを出力する
LLMに生成してもらったSQLの結果とカラム名を使ってcsvファイルの出力をします。
column_names = response_json["column_names"] # LLMで生成したJSONオブジェクトからカラム名を取得
sql_result = execute_sql(response_json["sql_query"]) # LLMで生成したSQLの実行結果
csv_file_name = "output.csv"
with open(csv_file_name, mode="w", newline="", encoding="utf-8-sig") as file:
writer = csv.writer(file)
writer.writerow(column_names)
writer.writerows(sql_result)
return FileResponse(
csv_file_name,
media_type="text/csv",
headers={"Content-Disposition": 'attachment; filename="output.csv"'}
)
実行結果
出力したい内容とカラムを指定してチャットに投げると、以下の流れでcsvファイルを出力できるようになりました。
まず、チャットでのメッセージとデータベーススキーマからLLMが以下のようなJSONオブジェクトを作成します。
{
"sql_query": "SELECT service_name, group_name, repo_name, region, critical, high, total FROM ecr_scan_report WHERE delete_flag = 0;",
"column_names": ["プロダクト名", "部署名", "リポジトリ名", "リージョン名", "critical", "high", "total"]
}
これらの情報をもとにSQLを実行して、csvファイルを出力する流れです。
プロダクト名 | 部署名 | リポジトリ名 | リージョン名 | critical | high | total |
---|---|---|---|---|---|---|
CMDB | プラットフォーム | ××××× | ap-northeast-1 | 1 | 2 | 3 |
CMDB | プラットフォーム | ××××× | ap-northeast-1 | 1 | 1 | 2 |
CMDB | プラットフォーム | ××××× | ap-northeast-1 | 1 | 1 | 2 |
次のステップ
ここまで生成AIとText-to-SQLを活用して、CMDBデータの検索機能とcsvデータ出力機能を実装しましたが、以下のようにまだまだ改善の余地があります。
- CMDBデータの検索機能では2回LLMを呼び出しているため、速度が遅い
- 複雑、あいまいな質問に弱い
- 自然言語は本質的にあいまいなので、質問の内容に対して複数の解釈ができてしまう
- スキーマの正確な理解
- スキーマ情報は複雑で、テーブル間のカラムの関係を理解させるのが大変
- コンテキスト情報の追加
- 現状は最初のプロンプトで最低限のコンテキスト情報を追加しています。今後、より多くのコンテキスト情報を追加するとなると、最初のLLM呼び出しの前に質問内容を大量のコンテキスト情報から適切な質問に変換する処理方法や、fine-tuningでKTC特有のコンテキスト情報を含むデータセットで追加学習させる方法を検討しています
- Query Routingの実装
フロントエンドから呼び出すAPIはCMDBのデータ検索とcsv出力で2つ分かれているため、APIは1つに統一して、質問の内容からどちらのAPIを呼び出すべきか判断できるように改善したい
さいごに
今回は生成AIとText-to-SQLを活用したCMDBのデータ検索機能とcsv出力機能についてお話ししました。
生成AI関連の技術は日々新しいものが出続けているためキャッチアップが大変ですが、今後はこれまで以上にアプリケーション開発にAIが絡んでくるため、興味のあるものや社内のプロダクトに適用できそうなものは積極的に活用していきたいと思います。
関連記事 | Related Posts
We are hiring!
【データエンジニア】データエンジニアリングG/名古屋・大阪
データ分析部について※データ分析部は、データエンジニアリングGが所属している部門です。KINTOにおいて開発系部門発足時から設置されているチームであり、それほど経営としても注力しているポジションです。
生成AIエンジニア/生成AI活用PJT/東京・名古屋・大阪
生成AI活用PJTについて生成AIの活用を通じて、KINTO及びKINTOテクノロジーズへ事業貢献することをミッションに2024年1月に新設されたプロジェクトチームです。生成AI技術は生まれて日が浅く、その技術を業務活用する仕事には定説がありません。