BASE株式会社の技術ブログ

全587件

2025/12/18

BASE ADVENT CALENDAR 2025 DAY.18 はじめにこんにちは！Data Strategy teamでデータエンジニアをしているshota.imazekiです。昨今、業務の中でLLMを活用する場面が増えてきており、その流れを受けて弊社でもさまざまな取り組みを進めています。本記事では、その中の一つとして今年挑戦した「SQL自動生成」について紹介します。 SQL自動生成のスコープ読み進めるにあたって誤解が生じないよう、本記事における「SQL自動生成」のスコープをあらかじめ整理しておきます。分析基盤上で、分析者が分析目的で実行するSQLを自動生成の対象とします主に SELECT文の生成を対象とし、CREATE / DELETE / UPDATE といったDDL・DMLは対象外とします LLMを用い、自然言語を入力としてSQLを生成することを前提とします。BIツールのように、UI操作による分析体験を目指すものではありません取り組んだ背景 BASEでは、分析基盤としてBigQueryを採用し、BIツールにはLookerを利用しています。Lookerは社内で広く活用されており、現在では社員のおよそ3分の1が、毎週Lookerを通じて何らかのデータを確認している状況です。一方で、SQLを直接書いて自由に分析できるユーザーはごく一部に限られているという課題も抱えていました。Looker上の既存のExploreやダッシュボードでは十分でないケースにおいても、SQLを書くハードルの高さから、簡単なデータ抽出依頼であってもData Strategy teamに来ることが度々ありました。この状況は、分析者の裾野を広げたいという観点だけでなく、Data Strategy teamの負荷軽減という点でも、改善の余地があると感じていました。そこで今回、「SQLを書く」というボトルネックをLLMでどこまで解消できるのかを検証する取り組みとして、SQL自動生成に挑戦しました。 SQL自動生成における課題 SQL自動生成における課題は大きく分けて2つあると考えてます。 1. 膨大なテーブル構成に対する理解 BASEの分析基盤には数百個のテーブルが存在しており、分析を行う際には次のような知識が求められます。ある指標や事象を分析する際に、どのテーブルを参照すべきか複数のデータを組み合わせて分析したい場合に、どのようなキーでテーブルを結合するのかこれらの前提をLLMが理解できていない場合、関係のないテーブルを参照したり、そもそも存在しないテーブルを用いたSQLを生成してしまうことがあります。そのようなSQLは、当然ながら分析に利用することはできません。 2. KPIなどのビジネス指標への理解もう一つの課題は、BASE固有のビジネス指標（KPI）に対する理解です。例えば、BASEにおけるGMV（流通総額）は、生データとしてそのまま存在しているわけではなく、複数のテーブルを組み合わせた上で、特定の条件に基づいて集計することで初めて算出される指標です。そのため、事前情報がない状態で「GMVを出して」とLLMに指示した場合、BASEが定義するGMVとは異なる値が生成されてしまう可能性があります。課題の本質 LLM自体はSQLを書くための一般的な知識を十分に備えています。しかし、BASE固有のテーブル構成や業務ドメイン、指標の定義については、LLMが知り得るものではありません。この「ドメイン知識の欠如」こそが、SQL自動生成における最大の課題だと考えています。そして、これら2つの課題を解決するために、今回の取り組みではディメンショナル・モデリングを用いてテーブル構成を再整理するというアプローチを採用しました。ディメンショナル・モデリングを用いたSQL自動生成ディメンショナル・モデリングとはディメンショナル・モデリングは、分析用途を主眼に置いたデータモデリング手法の一つです。一般に、業務システムで利用されるトランザクション処理向けのデータベースでは、リレーショナルデータモデリングが採用されることが多く、データは正規化された形で設計されます。このような設計は、個々のトランザクションを正確かつ効率的に処理することを目的としており、その結果として、分析のしやすさよりも更新や整合性を重視した構造になりがちです。そのため、分析を行う際には多くのテーブルを結合する必要があったり、データの意味を理解するために多くの前提知識が求められるケースも少なくありません。一方、ディメンショナル・モデリングは、ユーザーがデータを分析しやすいことを重視してテーブル構造を設計することを目的としています。分析の軸となるディメンションと、数値を持つファクトを中心にデータを整理することで、クエリの記述や指標の理解がしやすい構造を実現することが、このディメンショナル・モデリングのゴールとなります。スタースキーマについてディメンショナル・モデリングを語る上で、代表的な構成としてスタースキーマがあります。スタースキーマは、分析の中心となるファクトテーブルと、その周囲に配置されるディメンションテーブルによって構成されるシンプルなデータ構造です。ファクトテーブルには、売上金額や注文件数といった集計対象となる数値データが格納され、ディメンションテーブルには、日付・商品・購入者など、分析の切り口となる属性情報がまとまった形で格納されます。これらが、ファクトテーブルを中心に放射状に結合されることから、スタースキーマと呼ばれています。出典：スタースキーマとは - Power BI｜Microsoft Learn https://learn.microsoft.com/ja-jp/power-bi/guidance/star-schema この構成の特徴は、テーブルの役割が明確であること結合パターンが限定されること分析クエリを直感的に記述しやすいことといった点にあります。そのため、分析者にとって理解しやすいだけでなく、どのテーブルをどのように結合すればよいかを推測しやすい構造になっています。今回のSQL自動生成の文脈においても、スタースキーマのように構造が明確なデータモデルは、LLMがテーブル間の関係性を誤解しにくく、安定したSQLを生成しやすいという点で相性が良いと考えました。出典：スタースキーマ (Star Schema)｜Databricks https://www.databricks.com/jp/glossary/star-schema なお、分析用途においては One Big Table のように、あらかじめすべてを結合したテーブルを用意する選択肢もあります。一方で今回は、まずはテーブルの役割や関係性を明確にし、段階的にモデルを育てていくことを重視し、スタースキーマから取り組むことにしました。ディメンショナル・モデリングの導入 BASEの分析基盤では、これまでディメンショナル・モデリングは採用されておらず、データレイクに近い生テーブルや、用途ごとに作成されたデータマートに対して直接クエリを実行するケースが多くありました。このような構成は柔軟性が高い一方で、どのテーブルをどのように使えばよいのかを理解するための前提知識が必要となり、SQLを直接書いて分析するハードルを高めていた要因の一つだと考えています。今回のSQL自動生成の取り組みでは、この課題に向き合うと同時に、分析者にとっても、LLMにとっても理解しやすいデータ構造を用意することが重要だと考えました。そのため、SQL自動生成の検証と並行して、ディメンショナル・モデリングを導入することにしました。過去にData Strategy teamへデータ抽出の依頼が集中していた時期があり、その過程で社内の主要なビジネス指標や集計ロジックについて一定の知見が蓄積されていたこともあり、モデリング自体は比較的スムーズに進めることができました。ディメンショナル・モデリングを導入したことで、結果としてLLMにとっては次のようなメリットが得られました。参照すべきテーブル数が、数百規模から数個にまで絞られた GMVのようなビジネス指標をあらかじめ集計済みのファクトテーブルとして定義することで、指標の算出ロジックを都度推論する必要がなくなった LLMの選定ディメンショナル・モデリングを導入した後、次に検討すべきポイントとなったのが、どのLLMを用いてSQL自動生成を行うかという点でした。今回の対象ユーザーは、SQLに習熟していない社内の分析者全般を想定しています。そのため、GitHub Copilotのようにエディタ上での利用を前提とするものや、ローカルLLMのように各自のPCで環境構築が必要となる手法は、利用のハードルが高いと判断しました。そこで、できる限り導入・利用のハードルが低く、日常業務の延長線上で使える選択肢として、 BigQuery上で直接利用できるGemini in BigQuery ブラウザから利用可能でプロンプトなどを柔軟にカスタマイズできるGPTs の2つに候補を絞って検討を進めることにしました。GPTsは有料プランの機能ではあるものの、社内の多くのユーザーがすでに利用可能な環境であったため、今回の取り組みにおける利用ハードルは低いと判断しています。 Gemini in BigQuery Gemini in BigQueryは、その名の通りBigQuery上で利用できるGeminiです。クエリエディタ内に自然言語をコメントとして記述することで、SQLを自動生成することができます。生成されたSQLはそのままBigQueryのコンソール上で実行されるため、利用ハードルという点では最も低い選択肢だと感じていました。検証時点では無料で利用できたことも、大きな魅力の一つでした。しかし、今回のディメンショナル・モデリングを前提としたSQL自動生成という文脈では、以下の点から相性が悪いと判断しました。クエリエディタで、最近表示またはクエリしたテーブルに関する SQL コメントを記述します。（公式ドキュメントより）この仕様上、ユーザーが直前に閲覧・クエリしていたテーブルがディメンショナル・モデリングされたテーブル以外であった場合、意図しないテーブルまで参照してSQLが生成されてしまう可能性があります。今回の取り組みでは、参照すべきテーブルを明確に制御することが重要でしたが、その制御方法が見つからなかったため、Gemini in BigQueryの採用は見送ることにしました。 GPTs GPTsは、ChatGPTを特定の用途や目的に合わせてカスタマイズできる機能です。指示（Instructions）や知識（Knowledge）を事前に与えることで、特定のドメインやユースケースに特化した振る舞いをさせることができます。以下のスクリーンショットのように、あらかじめ指示や知識を設定することで、SQL自動生成に特化したGPTを作成しました。今回の取り組みでは、GPTsに対して主に「指示」と「知識」の2つを設定しています。指示指示には、GPTがどのような役割を担い、どのように振る舞うべきかをまとめました。具体的には、以下のような内容を記述しています。あなたは BigQueryに精通したSQLエンジニアであることディメンショナル・モデリングされたテーブル構成や、想定される結合方法を前提にSQLを生成することユーザーからのリクエストが、ディメンショナル・モデリングされたテーブル群では対応できない場合は、「Data Strategy teamに問い合わせてください」といった旨の文言を返すことその他、補足しておきたいBASE固有のドメイン知識などここで重要なのは、「何でもSQLを生成する」のではなく、対応できないケースでは無理に生成せず、適切にエスカレーションさせる振る舞いを明示している点です。知識知識には、GPTが参照できる具体的な情報として、以下の2種類のファイルを設定しています。ディメンショナル・モデリングされたテーブル群のDDL 各カラムには必ず description を記載し、どのカラムが何を意味するのかをGPTが正確に把握できるようにしていますサンプルクエリディメンショナル・モデリングだけでは意図が伝わりづらい集計については、実際のSQL例を2つほど与え、生成されるクエリの方向性を補強していますこれにより、GPTはテーブル構造や指標の意味を単なるDDLの羅列としてではなく、「どのように使われるのか」という文脈込みで理解できるようになります。検証結果作成したGPTが実際の業務で利用可能かを確認するため、あらかじめ用意しておいた約10問の検証用SQL自動生成問題をGPTに解かせてみました。なお、以下に掲載するSQLは、テーブル名やカラム名をマスク、もしくは実際とは異なる名称に置き換えています。 2025年4月にGMVが100万円以上あったショップの抽出期間指定・集計・結合といった基本的な構文に加え、HAVING句を用いた条件指定まで含めたSQLを生成することができました。 SELECT u.shop_id, -- ショップID（user_idの文字列） SUM (f.gmv) AS total_gmv -- 合計GMV FROM ` xxx . xxx .fact_tables` f JOIN ` xxx . xxx .dim_users` u ON f.user_id = u.user_id WHERE f.ordered >= ' 2025-04-01 ' -- 2025年4月の開始 AND f.ordered < ' 2025-05-01 ' -- 2025年4月の終了 GROUP BY u.shop_id HAVING total_gmv >= 1000000 -- 100万円以上（単位：円） ORDER BY total_gmv DESC ; 都道府県別のユーザー数ランキング単純な集計にとどまらず、ウィンドウ関数（RANK）を用いたランキング処理も正しく生成できていることが確認できました。 SELECT prefecture, -- 都道府県 COUNT (*) AS user_count, -- ユーザー数 RANK () OVER ( ORDER BY COUNT (*) DESC ) AS rank -- ユーザー数の多い順に順位付け FROM ` xxx . xxx .dim_users` GROUP BY prefecture ORDER BY rank ; 上記の例以外にも、CASE文、WITH句、各種ウィンドウ関数などを含むクエリについても検証を行いましたが、分析用途でよく使われるSELECTクエリについては、ある程度の複雑さまで対応できそうという印象を持ちました。また、事前に用意していた10問の検証ケースについては、すべて意図どおりのSQLを生成することができています。そのため、本取り組みはPoCに留めるのではなく、社内向けに展開し、現在は分析者を中心に実際の業務で利用されています。今後の展望今回は、分析基盤におけるSQL自動生成というテーマで取り組みを紹介しました。ここでは、現時点で見えている今後の展望について整理します。テーブルやカラムの追加による対応範囲の拡充ディメンショナル・モデリングの導入によって、把握すべきテーブル数は大きく絞られました。一方で、その構成に含まれていないデータについては、現時点ではSQL自動生成の対象外となっているのも事実です。今後は、スタースキーマ型のテーブルを段階的に拡充していくことで、より多くの分析ニーズに対応できるようにしていきたいと考えています。対応範囲を広げつつも、テーブル構造の分かりやすさを保つことを意識しながら、モデルを育てていく予定です。ハルシネーション対策 LLMを利用する以上、ハルシネーションのリスクを完全に排除することはできません。ディメンショナル・モデリングの導入や指示文の工夫によって、一定の抑制は可能ですが、常に正しいSQLが生成されることを前提にするのは現実的ではないと考えています。そのため、利用者側にも最低限の前提知識は必要になります。高度なSQLを書くスキルまでは求めませんが、ディメンショナル・モデリングされたテーブル構成の理解生成されたSQLを読み、妥当性を判断できる力は重要だと考えています。今後は、社内勉強会などを通じてこれらの理解を深め、LLMを過信せず、うまく付き合っていくための土台作りにも取り組んでいきたいと思います。おわりに BASEでは、LLM活用に限らず、分析基盤全体の改善に継続的に取り組んでいます。こうした取り組みにご興味のある方は、ぜひお気軽にご応募ください！ A-1.Tech_データエンジニア / BASE株式会社明日のBASEアドベントカレンダーは大塚さんの記事です。お楽しみに！

BASE株式会社 の技術ブログ

コンテンツ

BASE株式会社の技術ブログ