テーブル定義書の構造化抽出して、生成AIでDWH分析を試してみた / devio2025tokyo

DevelopersIO 2025 Tokyo 登壇資料

イベントサイト:
https://classmethod.connpass.com/event/366750/

Transcript

1. テーブル定義書の構造化抽出して、生成AIでDWH分析を試し てみた DevelopersIO 2025 Tokyo 2025/10/18 (Sat.) データ事業本部 笠原 宏
2. 笠原 宏 (@kasacchiful) クラスメソッド株式会社 データ事業本部 ソリューションアーキテクト 新潟県新潟市在住 JAWS-UG新潟 / Python機械学習勉強会 in 新潟 / JaSST Niigata / ASTER / SWANII / Cloudflare Meetup Niigata / AI CRAFT Hacks Niigata / KomeKaigi AWS Community Builder (Serverless) 2025 Japan AWS Top Engineer / 2025 Japan All AWS Certifications Engineer 自己紹介 2
3. テーブル定義等、必要なメタデータを渡せば、言語モデルがSQLを返してくれる 各DWHに対応したMCPサーバを用いれば、自然言語でクエリ実行までできる テーブル定義等は、データカタログに定義できていれば、取得は容易 テーブル定義書がファイル (特にExcel) に記載されたまま、データカタログ使ってないですけど、 どうすればいい？ 自然言語でクエリ実行したい！ 3
4. Excelで書かれたテーブル定義をなんとか抽出したい
5. 1. Excel→PDFに変換 2. LLMにて、PDFのテーブル定義からCreate Tableクエリを生成 3. Create Tableクエリをプロンプトに仕込んで、自然言語クエリ生成 利用するモデルは、Amazon Bedrock上の「Anthropic Claude Sonnet 4.5」とします。 試してみた 5
6. 1シートに1テーブル定義 ただのテキスト抽出だと罫線の情報が抜けるの で、表を画像として認識させるためにPDF化 事前にExcelシートの印刷範囲を調整して、表 が1ページに収まるように設定しておく 1. Excel→PDF化 6
7. 今回は xlwings ライブラリを利用。 import xlwings as xw import os import pprint excel_path = './table_definitions.xlsx' App = xw.App() wb = App.books.open(excel_path) try: ## シート毎にPDF化 for sheet in wb.sheets: pdf_path = os.path.join('.', f'table_definitions_{sheet.name}.pdf') sheet.to_pdf(pdf_path) finally: wb.close() App.quit() Excel→PDF サンプルコード 7
8. 各PDFファイルから、Create Tableクエリを生成。 import boto3 import json with open("table_definitions_sensors.pdf", mode="rb") as f: pdf_data = f.read() client = boto3.client("bedrock-runtime", region_name="ap-northeast-1") model_id = "jp.anthropic.claude-sonnet-4-5-20250929-v1:0" prompt = """\ 次ページにて記載 """ response = client.converse( modelId=model_id, messages=[ { "role": "user", "content": [ { "text": prompt }, { "document": { "name": "PDF", "format": "pdf", "source": { "bytes": pdf_data }, "citations": { "enabled": True }, }}, ], } ], ) print(json.dumps(response["output"]["message"]["content"], indent=4, ensure_ascii=False)) 2. LLMにて、PDFのテーブル定義からCreate Tableクエリを生成 8
9. prompt = """\ PDFドキュメントに書かれている内容はデータベースのテーブル定義書です。 このテーブル定義書を元に、以下の条件の下でRedshiftのCreate Tableクエリおよびコメントクエリを作成してください。 markdown形式のテキストで出力し、SQLクエリの部分のみを出力してください。 - 「テーブル名」の欄には、実際のテーブルの物理名が記載されています。 - 「テーブル概要」の欄には、テーブルに対するコメントが記載されています。 - 「カラム名」の欄には、テーブルで定義されているカラムの物理名が記載されています。 - 「データ型」の欄には、各カラムの型が記載されています。Create Tableクエリを作成する際は、Redshiftの型に合わせて変換してください。 - 「Not Null」の欄には、各カラムのNot Null制約が有効かどうか記載されています。「◦」と記載されているカラムはNot Null制約をつけてください。 - 「説明」および「備考」の欄には、各カラムの説明や補足情報が記載されています。「説明」および「備考」の内容は、各カラムのコメントに記載してください。 - 「カラムNo」の欄は不要なので、無視してください。 """ プロンプト例: Amazon Redshift 9
10. -- テーブル作成 CREATE TABLE sensors ( sensor_id VARCHAR NOT NULL, sensor_name VARCHAR NOT NULL, location VARCHAR, is_enabled NUMERIC NOT NULL, latitude DOUBLE PRECISION, longitude DOUBLE PRECISION); -- テーブルコメント COMMENT ON TABLE sensors IS 'センサー情報を管理するテーブル'; -- カラムコメント COMMENT ON COLUMN sensors.sensor_id IS 'センサーの一意識別子 主キー'; COMMENT ON COLUMN sensors.sensor_name IS 'センサーの名称'; COMMENT ON COLUMN sensors.location IS 'センサーの設置場所'; COMMENT ON COLUMN sensors.is_enabled IS 'センサー利用有無 (1: 有効, 2: 無効)'; COMMENT ON COLUMN sensors.latitude IS 'センサー設置緯度 10進数表記'; COMMENT ON COLUMN sensors.longitude IS 'センサー設置経度 10進数表記'; 出力結果例: Amazon Redshift (1) 10
11. -- テーブル作成 CREATE TABLE sensor_timelines ( sensor_id VARCHAR NOT NULL, timestamp TIMESTAMP NOT NULL, temperature DOUBLE PRECISION, humidity DOUBLE PRECISION); -- テーブルコメント COMMENT ON TABLE sensor_timelines IS 'センサーから収集された時系列データを管理するテーブル'; -- カラムコメント COMMENT ON COLUMN sensor_timelines.sensor_id IS 'センサーの一意識別子 外部キー (sensors.sensor_id)'; COMMENT ON COLUMN sensor_timelines.timestamp IS 'データ取得時刻 ISO 8601形式 (タイムゾーン付き)'; COMMENT ON COLUMN sensor_timelines.temperature IS '温度 摂氏 (℃)'; COMMENT ON COLUMN sensor_timelines.humidity IS '湿度 パーセント (%)'; 出力結果例: Amazon Redshift (2) 11
12. prompt = """\ PDFドキュメントに書かれている内容はデータベースのテーブル定義書です。 このテーブル定義書を元に、以下の条件の下でAmazon AthenaのCreate Tableクエリを作成してください。 テーブルはAmazon S3 Tablesに格納されたApache Iceberg形式とします。 markdown形式のテキストで出力し、SQLクエリの部分のみを出力してください。 - 「テーブル名」の欄には、実際のテーブルの物理名が記載されています。 - 「テーブル概要」の欄には、テーブルに対するコメントが記載されています。 - 「カラム名」の欄には、テーブルで定義されているカラムの物理名が記載されています。 - 「データ型」の欄には、各カラムの型が記載されています。Create Tableクエリを作成する際は、AthenaおよびIcebergの型に合わせて変換してください。 - 「Not Null」の欄には、各カラムのNot Null制約が有効かどうか記載されています。「◦」と記載されているカラムはNot Null制約をつけてください。 ただし、AthenaのCreate Tableクエリの場合は「◦」の有無に関わらずNot Null制約をつけないでください。 - 「説明」および「備考」の欄には、各カラムの説明や補足情報が記載されています。「説明」および「備考」の内容は、各カラムのコメントに記載してください。 - 「カラムNo」の欄は不要なので、無視してください。 """ プロンプト例: Amazon S3 Tables (クエリ実行はAthena) 12
13. CREATE TABLE sensors ( sensor_id string COMMENT 'センサーの一意識別子 主キー', sensor_name string COMMENT 'センサーの名称', location string COMMENT 'センサーの設置場所', is_enabled int COMMENT 'センサー利用有無 (1: 有効, 2: 無効)', latitude double COMMENT 'センサー設置緯度 10進数表記', longitude double COMMENT 'センサー設置経度 10進数表記' ) COMMENT 'センサー情報を管理するテーブル' LOCATION 's3://your-bucket/your-table-namespace/sensors/' TBLPROPERTIES ( 'table_type' = 'ICEBERG', 'format' = 'parquet' ); 出力結果例: Amazon S3 Tables (クエリ実行はAthena) (1) 13
14. CREATE TABLE sensor_timelines ( sensor_id string COMMENT 'センサーの一意識別子 外部キー (sensors.sensor_id)', timestamp timestamp COMMENT 'データ取得時刻 ISO 8601形式 (タイムゾーン付き)', temperature double COMMENT '温度 摂氏 (℃)', humidity double COMMENT '湿度 パーセント (%)' ) COMMENT 'センサーから収集された時系列データを管理するテーブル' LOCATION 's3://your-bucket-name/your-table-location/' TBLPROPERTIES ( 'table_type' = 'ICEBERG', 'format' = 'parquet' ); 出力結果例: Amazon S3 Tables (クエリ実行はAthena) (2) 14
15. import boto3 import json client = boto3.client("bedrock-runtime", region_name="ap-northeast-1") model_id = "jp.anthropic.claude-sonnet-4-5-20250929-v1:0" prompt = """\ 次ページにて記載 """ response = client.converse( modelId=model_id, messages=[ { "role": "user", "content": [ {"text": prompt}, ], } ], ) print(json.dumps(response["output"]["message"]["content"], indent=4, ensure_ascii=False)) 3. Create Tableクエリをプロンプトに仕込んで、自然言語クエリ生成 15
16. prompt = """\ テーブル定義を元に、以下のデータを抽出するSQLを作成してください。 テーブルはAmazon S3 Tablesに格納されたApache Iceberg形式とします。 markdown形式のテキストで出力し、SQLクエリの部分のみを出力してください。 ## データ抽出条件 「東京オフィス3」にあるセンサーにおいて、2024年の第二四半期の平均気温と平均湿度を求めてください。 ## テーブル定義 ```sql CREATE TABLE sensors ( sensor_id STRING NOT NULL COMMENT 'センサーの一意識別子 主キー', sensor_name STRING NOT NULL COMMENT 'センサーの名称', location STRING COMMENT 'センサーの設置場所', is_enabled INT NOT NULL COMMENT 'センサー利用有無 (1: 有効, 2: 無効)', latitude DOUBLE COMMENT 'センサー設置緯度 10進数表記', longitude DOUBLE COMMENT 'センサー設置経度 10進数表記' ) COMMENT 'センサー情報を管理するテーブル' LOCATION 's3://your-bucket/your-database/sensors/' TBLPROPERTIES ( 'table_type' = 'ICEBERG', 'format' = 'parquet' ); ...<以下略>... """ プロンプト例: ある地点の四半期平均を出したい 16
17. テーブルのJOINもある程度可能 SELECT AVG(st.temperature) AS avg_temperature, AVG(st.humidity) AS avg_humidity FROM sensor_timelines st INNER JOIN sensors s ON st.sensor_id = s.sensor_id WHERE s.location = '東京オフィス3' AND st.timestamp >= TIMESTAMP '2024-04-01 00:00:00' AND st.timestamp < TIMESTAMP '2024-07-01 00:00:00' 複雑なJOINが絡む場合は、マートテーブルを事前 に用意して、マートテーブルに問い合わせするよ うにした方が賢明 出力結果例: ある地点の四半期平均を出したい 17
18. prompt = """\ テーブル定義を元に、以下のデータを抽出するSQLを作成してください。 テーブルはAmazon S3 Tablesに格納されたApache Iceberg形式とします。 markdown形式のテキストで出力し、SQLクエリの部分のみを出力してください。 ## データ抽出条件 無効になっているセンサーの一覧を出力してください。 ## テーブル定義 ```sql CREATE TABLE sensors ( sensor_id STRING NOT NULL COMMENT 'センサーの一意識別子 主キー', sensor_name STRING NOT NULL COMMENT 'センサーの名称', location STRING COMMENT 'センサーの設置場所', is_enabled INT NOT NULL COMMENT 'センサー利用有無 (1: 有効, 2: 無効)', latitude DOUBLE COMMENT 'センサー設置緯度 10進数表記', longitude DOUBLE COMMENT 'センサー設置経度 10進数表記' ) COMMENT 'センサー情報を管理するテーブル' LOCATION 's3://your-bucket/your-database/sensors/' TBLPROPERTIES ( 'table_type' = 'ICEBERG', 'format' = 'parquet' ); ...<以下略>... """ プロンプト例: 無効になっているセンサー一覧取得 18
19. テーブル定義に沿って、無効の場合 "2" を指定し ている。 SELECT sensor_id, sensor_name, location, latitude, longitude FROM sensors WHERE is_enabled = 2 出力結果例: 無効になっているセンサー一覧取得 19
20. Excel→PDF→メタデータ抽出→カラムやテーブルのコメント付与 Excel→PDF→メタデータ抽出→データカタログへ登録 Excelでテーブル定義管理したまま、Excel更新→メタデータ更新まで自動化 応用編 20
21. Excelのテーブル定義書から、Create Tableクエリを生成して、それを元に自然言語でデータ抽出 クエリを生成することを確認 Excelで定義されていても、PDF化さえすればメタデータ抽出は何とかなりそう データカタログへのメタデータ登録にも、応用が効く まとめ 21