今回の検証内容・ゴール
この記事では以下の内容について検証し手順の説明を行います。
- ローカルに立てたMySQLサーバにSAPの売上請求データ（VBRP: 請求明細伝票）のCSVファイル（vbrp.csv）をLoadする
- データを取り込むVBRPテーブル定義とテーブル作成クエリをChatGPTにより自動生成を行う
- 生成したクエリによりテーブルの作成を行う
- データloadを行ったVBRPテーブルに対し、ステータス等チェックを行うクエリを生成する
- チェッククエリはChatGPTにより自動生成を行う
- 実行結果によりデータロード結果が想定通りであることを確認する
環境準備
- Windows 11ローカルPC上にMySQLサーバをインストール
- バージョン: MySQL Installer 8.0.40
検証用データ入手
今回は実際の企業のデータ分析の検証の精度をある程度再現するため、KaggleからSAP DATASET | BigQuery Datasetというデータセットをダウンロードしました。BigQueryのクラウドトレーニング用データとのことで、実際的な検証ができることを期待します。
このデータセットには78個のCSVファイルが含まれており、フラットファイル展開時には2.34GBのデータが含まれていることを確認しました。データ分析デモとしてはある程度のサイズが含まれている必要があり、適当と判断しました。
検証対象テーブルとしてVBRP（請求明細伝票）を採用
「VBRP」は請求伝票の明細データを格納するテーブルです。主な項目として、請求伝票番号、明細番号、品目コード、請求数量、サービス提供日などの項目があります。
今回の検証では以下のカラムを取り込むことにしました。
（より実用劇な業務分析用には請求明細ヘッダのテーブルであるVBRKと組み合わせる必要がありそうですが、今回は検証をシンプルにするためにVBRPのみを対象とします。）
- MATNR: 製品番号（マテリアル番号）
- FKIMG: 請求数量
- NETWR: 明細の正味金額
- FBUDA: サービス提供日（Date of services rendered）
- WERKS: プラント
- LGORT: 保管場所
- KZWI1: 条件金額1
- KZWI2: 条件金額2
ChatGPTによりMySQLサーバスキーマ上にテーブルを作成
ChatGPTへデータ取り込み用のテーブル作成を依頼しました。以下のプロンプトにより依頼しました。
プロンプト:
VBRPテーブルを作成してvbrp.csvファイルからデータを取り込みたい
対象カラムは以下に絞りたい。
テーブル定義を作成してほしい。
MATNR（資材番号）: SKU単位での売上分析に必須。
FKIMG（請求数量）: 実際に販売された数量を把握。
NETWR（明細純金額）: 売上金額の集計に使用。
FBUDA（伝票日付）: 期間ごとの売上分析に使用。
WERKS（プラント）とLGORT（保管場所）: 販売場所や在庫出庫先を特定。
KZWI1、KZWI2: 割引や特別条件を含めた販売金額分析。以下のテーブル作成のクエリが作成されました。（下記は修正を加えずそのまま掲載）
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE TABLE VBRP ( MATNR CHAR(18) NOT NULL, -- 資材番号 FKIMG DECIMAL(15, 3), -- 請求数量 NETWR DECIMAL(15, 2), -- 明細純金額 FBUDA DATE, -- 伝票日付 WERKS CHAR(4), -- プラント LGORT CHAR(4), -- 保管場所 KZWI1 DECIMAL(15, 2), -- 割引や特別条件1 KZWI2 DECIMAL(15, 2), -- 割引や特別条件2 PRIMARY KEY (MATNR, FBUDA) -- 主キーとして資材番号と伝票日付を設定);-- コメント:-- 1. `MATNR` は資材番号（SKU）で、18桁の固定長文字型。-- 2. `FKIMG`（請求数量）は数量情報で、小数点以下3桁まで対応。-- 3. `NETWR`（明細純金額）は金額情報で、小数点以下2桁まで対応。-- 4. `FBUDA`（伝票日付）は日付型（DATE）。-- 5. `WERKS`（プラント）と `LGORT`（保管場所）は固定長文字型（4桁）。-- 6. `KZWI1`、`KZWI2` は販売金額分析用の金額カラム。-- 7. 主キーは `MATNR` と `FBUDA` の複合キーを設定。 |
コマンドプロンプトからMySQLサーバに接続してテーブルを作成します。
ローカルファイルをMySQLにロードする場合、MySQLへ接続時以下のオプションを設定する必要があります。
1 | mysql -u <ユーザ名> -p --local-infile |
後述の検証時にキーの制約等によりロードが大量にskipされる行を確認ました。ローデータとしてすべての行をそのまま取り込むことを目的とするため、NOT NULLとPRIMARY KEYの指定を外してテーブルを作成しました。
実行したテーブル作成クエリ:
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE VBRP ( MATNR CHAR(18), FKIMG DECIMAL(15, 3), NETWR DECIMAL(15, 2), FBUDA DATE, WERKS CHAR(4), LGORT CHAR(4), KZWI1 DECIMAL(15, 2), KZWI2 DECIMAL(15, 2)); |
テーブル作成クエリ実行結果:
1 2 3 4 5 6 7 8 9 10 11 | mysql> CREATE TABLE VBRP ( -> MATNR CHAR(18), -> FKIMG DECIMAL(15, 3), -> NETWR DECIMAL(15, 2), -> FBUDA DATE, -> WERKS CHAR(4), -> LGORT CHAR(4), -> KZWI1 DECIMAL(15, 2), -> KZWI2 DECIMAL(15, 2) -> );Query OK, 0 rows affected (0.03 sec) |
作成されたテーブルの定義を確認します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> desc vbrp;+-------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------------+------+-----+---------+-------+| MATNR | char(18) | YES | | NULL | || FKIMG | decimal(15,3) | YES | | NULL | || NETWR | decimal(15,2) | YES | | NULL | || FBUDA | date | YES | | NULL | || WERKS | char(4) | YES | | NULL | || LGORT | char(4) | YES | | NULL | || KZWI1 | decimal(15,2) | YES | | NULL | || KZWI2 | decimal(15,2) | YES | | NULL | |+-------+---------------+------+-----+---------+-------+8 rows in set (0.00 sec) |
請求明細検証用CSVファイル（vbrp.csv）をVBRPテーブルにLoadする
ローカルのvbrp.csvファイルをMySQLサーバ上VBRPテーブル上にloadします。
ローカルファイルをMySQLファイルにLoadするにあたり、local_infileの値がONになっている必要があります。以下のクエリで設定を確認してください。
1 2 3 4 5 6 7 | mysql> SHOW VARIABLES LIKE 'local_infile';+---------------+-------+| Variable_name | Value |+---------------+-------+| local_infile | ON |+---------------+-------+1 row in set, 1 warning (0.03 sec) |
以下のクエリがCSVファイル（vbrp.csv）をVBRPテーブルにLoadするクエリです。
vbrp.csvには全部で227カラム含まれていますが、必要なカラムに絞ってデータを取り込みます。@dummyを挿入することで不要カラムの取り込みをスキップします。カラム名を指定することでテーブル上のどのカラムに値をセットするかマッピングを行っています。
これにより元ファイルの加工を行うことなくファイルのロードを行うことができました。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | LOAD DATA LOCAL INFILE 'C:/myDevWork001/vbrp.csv'INTO TABLE VBRPFIELDS TERMINATED BY ',' -- CSVのフィールド区切り文字がカンマの場合ENCLOSED BY '"' -- フィールドを囲む文字（ダブルクォート）LINES TERMINATED BY '\n' -- 行の終端文字（改行）IGNORE 1 ROWS -- ヘッダー行を無視( @dummy, -- @dummyを挿入することで不要からむの取り込みをスキップする @dummy, @dummy, @dummy, FKIMG, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, fbuda, @dummy, netwr, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, MATNR, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, werks, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, lgort, @dummy, @dummy, kzwi1, kzwi2); |
以下が実行結果です。すべての行をエラーなく取り込むことができました。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 | mysql> LOAD DATA LOCAL INFILE 'C:/myDevWork001/vbrp.csv' -> INTO TABLE VBRP -> FIELDS TERMINATED BY ',' -- CSVのフィールド区切り文字がカンマの場合 -> ENCLOSED BY '"' -- フィールドを囲む文字（ダブルクォート） -> LINES TERMINATED BY '\n' -- 行の終端文字（改行） -> IGNORE 1 ROWS -- ヘッダー行を無視 -> ( -> @dummy, -- @dummyを挿入することで不要からむの取り込みをスキップする -> @dummy, -> @dummy, -> @dummy, -> FKIMG, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> fbuda, -> @dummy, -> netwr, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> MATNR, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> werks, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> @dummy, -> lgort, -> @dummy, -> @dummy, -> kzwi1, -> kzwi2 -> );Query OK, 119282 rows affected, 65535 warnings (7.97 sec)Records: 119282 Deleted: 0 Skipped: 0 Warnings: 127511 |
VBRPテーブルへのCSVファイル（vbrp.csv）Load結果を確認する。取り込み結果確認クエリをChatGPTさんに自動作成してもらう。
ロード結果について正常に行われたかクエリを発行してテーブルのステータスを確認します。
今回の検証のミッションは、自然言語でクエリーを駆使することです。
VBRPテーブルへロードされた結果についてChatGPTさんにチェッククエリを作成してもらいます。以下のプロンプトにより依頼を行いました。
プロンプト:
MySQLに取り込まれたVBRPテーブルのデータを分析したい。
CSVファイルからデータを取り込んだ結果やステータスをチェックするためのSQLを作成してほしい。
テーブル定義は下記の通り。
---
mysql> desc vbrp;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| MATNR | char(18) | YES | | NULL | |
| FKIMG | decimal(15,3) | YES | | NULL | |
| NETWR | decimal(15,2) | YES | | NULL | |
| FBUDA | date | YES | | NULL | |
| WERKS | char(4) | YES | | NULL | |
| LGORT | char(4) | YES | | NULL | |
| KZWI1 | decimal(15,2) | YES | | NULL | |
| KZWI2 | decimal(15,2) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+データの中身の分析まで含むチェッククエリが作成されましたが、ロード結果の確認に効果的だったものを抜粋して紹介します。
以下は自動作成された確認の目的、クエリとその実行結果です。
総レコード数を確認
1 2 3 4 5 6 7 | mysql> SELECT COUNT(*) AS total_records FROM vbrp;+---------------+| total_records |+---------------+| 119282 |+---------------+1 row in set (0.04 sec) |
各カラムのNULL値の件数を確認
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> SELECT -> SUM(CASE WHEN MATNR IS NULL THEN 1 ELSE 0 END) AS null_matnr, -> SUM(CASE WHEN FKIMG IS NULL THEN 1 ELSE 0 END) AS null_fkimg, -> SUM(CASE WHEN NETWR IS NULL THEN 1 ELSE 0 END) AS null_netwr, -> SUM(CASE WHEN FBUDA IS NULL THEN 1 ELSE 0 END) AS null_fbuda, -> SUM(CASE WHEN WERKS IS NULL THEN 1 ELSE 0 END) AS null_werks, -> SUM(CASE WHEN LGORT IS NULL THEN 1 ELSE 0 END) AS null_lgort, -> SUM(CASE WHEN KZWI1 IS NULL THEN 1 ELSE 0 END) AS null_kzwi1, -> SUM(CASE WHEN KZWI2 IS NULL THEN 1 ELSE 0 END) AS null_kzwi2 -> FROM vbrp;+------------+------------+------------+------------+------------+------------+------------+------------+| null_matnr | null_fkimg | null_netwr | null_fbuda | null_werks | null_lgort | null_kzwi1 | null_kzwi2 |+------------+------------+------------+------------+------------+------------+------------+------------+| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |+------------+------------+------------+------------+------------+------------+------------+------------+1 row in set (0.42 sec) |
日付データの範囲を確認
1 2 3 4 5 6 7 8 | mysql> SELECT MIN(FBUDA) AS earliest_date, MAX(FBUDA) AS latest_date -> FROM vbrp;+---------------+-------------+| earliest_date | latest_date |+---------------+-------------+| 1995-12-31 | 2022-04-25 |+---------------+-------------+1 row in set (0.14 sec) |
プラント(WERKS)ごとのデータ件数を確認
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | mysql> SELECT WERKS, COUNT(*) AS record_count -> FROM vbrp -> GROUP BY WERKS -> ORDER BY record_count DESC;+-------+--------------+| WERKS | record_count |+-------+--------------+| 1200 | 16414 || R310 | 11968 || R311 | 11944 || R312 | 11740 || R313 | 11606 || 3000 | 9747 || 1000 | 8392 || R110 | 6702 || R111 | 6594 || R112 | 6458 || 3200 | 5142 || 5000 | 3468 || 4000 | 2672 || 2400 | 1740 || 2500 | 1740 || 2000 | 894 || 3800 | 816 || | 798 || XLP1 | 80 || 2800 | 52 || 1100 | 47 || CPB1 | 34 || 6000 | 24 || 2222 | 22 || 1300 | 18 || IN47 | 18 || 9000 | 16 || 4300 | 14 || 2200 | 12 || 4110 | 12 || 7500 | 10 || 2223 | 10 || R300 | 10 || SL31 | 10 || 3700 | 8 || 3500 | 8 || 8500 | 8 || 3750 | 6 || 2220 | 6 || CPF1 | 6 || 8591 | 4 || 0005 | 4 || 2730 | 4 || 3600 | 2 || SL39 | 2 |+-------+--------------+45 rows in set (0.25 sec) |
必須項目（例：MATNR, FBUDA）がNULLのレコードを確認
1 2 3 4 | mysql> SELECT * -> FROM vbrp -> WHERE MATNR IS NULL OR FBUDA IS NULL;Empty set (0.18 sec) |
まとめ・ChatGPTさん恐るべし！！
自動生成されたチェッククエリの実行により、CSVに含まれる全ての行の必要なカラムのデータについて取り込みが想定通り完了できたことを確認しました。明細の件数、各レコードの必須項目NULLチェック、レコードの日付範囲の取得、プラント毎のデータ件数などを難なく完了することができました。
ChatGPTにテーブル定義をプロンプト入力により認識させ、チェッククエリを自動生成できたことは自然言語による今後のテストシナリオの自動生成と実行にも大きな期待を予感させます。
また今回、環境構築からMySQLサーバの設定、クエリ実行時のエラー解決などもChatGPT、Google AI Studio、Claudを駆使することによりかなり短時間で完了することができました。
今後の課題・取り組みテーマ
- 今回はテーブル定義の作成やテーブルのデプロイ、チェッククエリの生成をChatGPTにより行い、ターミナルにコピペで貼り付けることにより実行を行いました。今後、CursorなどのAIエージェント機能により、自然言語によりターミナル上にクエリやコマンドを発行し、作業の効率化と自動化にトライする計画です。
- 自然言語によるデータ分析にトライする
- 今回、自然言語によりテーブル定義、テーブル作成、データロード結果のチェックのためのクエリの自動生成を行うことができました。今後自然言語によるテーブル仕様概要の解析やクエリ生成自動実行によるデータ分析にトライしたいと考えています。
1件のコメント
コメントはできません。