2024年10月23日水曜日

分析に使う SQL を GitHub リポジトリで運用 & GitHubにSQLを認識させる方法 & Github Copilot Chat にて生成される SQL 文を Few-shot learning により改善する方法

0:07    No comments

 見出し画像

https://note.com/_jaruuu/n/ne6f134f8fde4



https://note.com/_jaruuu/n/ne6f134f8fde4

jaruuu(Ryosuke Hamaguchi)
2022年2月14日 11:06

Zaim の分析チームの濱口です。

分析チームでは各々が書いた SQL をドキュメントにまとめて GitHub リポジトリに貯めて共有しています。この試みを始めてから 1 年ほど経過したので、運用してみて感じたことや気づいたことを紹介しようと思います。

背景

SQL を GitHub リポジトリに貯め始めたのは、以下のような二つの問題意識があったからでした。

1. レビューの枠組みがなかった

SQL を複数人でレビューする確固たる枠組みがなく、タスク管理ツール上で雑にチェックしていたり、そもそもレビューを実施していなかったりしていました。このような状態が続いてしまうと、SQL のミスやデータの誤りにつながってしまい、データの信頼性が損なわれてしまいます。

2. 書いた SQL が集約されていなかった

分析チームのメンバーそれぞれが書いた SQL が、どこにあるのかが分かりづらい状態でした。他の人がやったことがある分析の SQL を参考にしたり再利用したりするのにも一苦労で、とても非効率です。

GitHub リポジトリに貯めるまでの流れ

リポジトリへの蓄積方法は、一般的なコードのレビューと同じく

  • プルリクエストを作成する

  • レビューをもらう

という手順になります。

1. プルリクエストを作成する

マークダウン形式のファイルに作成した SQL の概要、実際に書いた SQL などを記載したプルリクエストを作成します。ディスクリプションには、データ抽出の目的と SQL を実行して抽出したデータを記載し、データの数値についても確認できるようにしています。

2. レビューをもらう

チームメンバーからレビューをもらい、SQL やデータに誤りがないかチェックします。

ディスクリプションにレビューのチェック項目を設けており、その項目に沿ってレビューすることで漏れが出ないように対策しています。最低でも一人からは Approve もらい、問題なさそうであればマージします。

運用してみた感想・まだ残る課題

  • クエリのミスやデータのエラーに気づけるようになった

  • 他の人が書いた SQL を再利用しやすくなった

といったように、当初、抱えていた問題は解消できました。しかし、運用する上で、また以下のような別の課題が出てきました。

1. 参考にしたいファイルが見つからない

最初のうちは良かったのですが、ファイルがどんどん増えていくうちに参考にしたいファイルを見つけるのが困難になってきました。

解決策として、すべてのファイルを同じ一つのフォルダに貯めていたのを、会社外と会社内の案件別で分割した上で、さらにデータマートごとにクエリを分けるようにしました。

2. レビューが滞る

データの抽出内容によってはかなり長い SQL になることもあり、とにかくレビューが大変でした。

レビューが大変だと後回しにされがちで、レビューが滞るという問題が出てきました。この問題に関しては

  • SQL のコーディングスタイルを定める

  • 再利用できる箇所は切り出して共通化する

などを実施しており、現在も改善を続けています。その一部は、別の記事にまとめています。

まとめ

  • プルリクエストによるレビューによりデータの信頼性が向上した

  • GitHub のリポジトリに集約することで SQL を再利用しやすくなった

運用していて出てきた新たな課題についても、さらに改善していきたいと思います。

最後に

他のチームと協力しながら働ける Zaim では、一緒に働けるエンジニアを募集しています！

仕事を通じて色々なスキルを得たいという方は、ぜひ一度カジュアルにオンラインでお話しましょう。ご連絡、お待ちしてます。

株式会社 Zaim の会社情報 - Wantedly株式会社 Zaim の魅力を伝えるコンテンツと、住所や代表・従業員などの会社情報です。株式会社 Zaim は 2023 年www.wantedly.com
---
https://qiita.com/MasatoraAtarashi/items/72bad85ab41e65348358

https://qiita.com/MasatoraAtarashi/items/72bad85ab41e65348358

@MasatoraAtarashi(tora masa)

GitHubにSQLを認識させる方法

最終更新日 投稿日 2021年10月06日

SQLファイルをGitHubにプッシュしても認識してくれなかったので、認識させる方法をメモ

手順

  1. .gitattributesをリポジトリに追加
  2. .gitattributesの内容に以下を追加
.gitattributes
*.sql linguist-detectable=true
*.sql linguist-language=sql

結果

スクリーンショット 2021-10-06 23.12.22.png

無事認識してくれました。

参考




---

https://qiita.com/manabian/items/b139eaf21c1fff259008

https://qiita.com/manabian/items/b139eaf21c1fff259008


@manabian

Github Copilot Chat にて生成される SQL 文を Few-shot learning により改善する方法

最終更新日 投稿日 2023年06月07日

概要

Github Copilot Chat にて SQL 文（CREATE TABLE 文）を Few-shot learning により改善できたため、実施内容を共有します。データストアに最適化した SQL を生成するプロンプトに関する記事があまり見つけられなかったため、参考にしていただければ幸いです。

想定実行対象のデータストアは、Databricks(Spark) であり、Spark に関する情報を学習していないようで、想定通りの CREATE TABLE 文を生成できませんでした。

Create a new table with the following conditions: 

SQL Dialect: Spark SQL
schema name: default
table name: city
comlumns and data types: /*
    - ID bigint
    - NAME char(100)
    - cur_date date
options:
- IDENTITY columns:
    - ID


CREATE TABLE default.city (
  ID BIGINT,
  NAME CHAR(100),
  cur_date DATE
)
USING spark
OPTIONS (
  "identity" "ID"
)

image.png

上記の実行をすると、spark というデータソースがないとうエラーが表示されます。。Zero-shot では、想定通りの結果とならないようです。

org.apache.spark.SparkClassNotFoundException: [DATA_SOURCE_NOT_FOUND] Failed to find data source: spark. Please find packages at `https://spark.apache.org/third-party-projects.html`.

image.png

Few-shot learning による改善

基本方針

Spark における CREATE TABLE 文と自動採番（IDENTITY 列として設定）する文を例示してから質問することとします。

When you create new table with following conditions, you will get the following code.

conditions : /*
SQL Dialect: Spark SQL
schema name: default
table name: talbe_name
data source: delta 
comlumns and data types:
    - col_1 int
options: /**/

code : /*
CREATE TABLE default.talbe_name (
  col_1 int
)
USING delta;
*/

When you create new table with following conditions, you will get the following code.

conditions : /*
SQL Dialect: Spark SQL
schema name: default
table name: talbe_name
data source: delta 
comlumns and data types:
    - col_1 bigint
options:
- IDENTITY columns:
    - col_1

code : /*
CREATE TABLE default.talbe_name (
  col_1 bigint GENERATED ALWAYS AS IDENTITY
)
USING delta
;
*/

Github Copilot chat にて実行

下記のプロンプトを実行します。

/*
When you create new table with following conditions, you will get the following code.

conditions : /*
SQL Dialect: Spark SQL
schema name: default
table name: talbe_name
data source: delta 
comlumns and data types:
    - col_1 int
options: /**/

code : /*
CREATE TABLE default.talbe_name (
  col_1 int
)
USING delta;
*/


/*
When you create new table with following conditions, you will get the following code.

conditions : /*
SQL Dialect: Spark SQL
schema name: default
table name: talbe_name
data source: delta 
comlumns and data types:
    - col_1 bigint
options:
- IDENTITY columns:
    - col_1

code : /*
CREATE TABLE default.talbe_name (
  col_1 bigint GENERATED ALWAYS AS IDENTITY
)
USING delta
;
*/

Create a new table with the following conditions: 

SQL Dialect: Spark SQL
schema name: default
table name: city
comlumns and data types: /*
    - ID bigint
    - NAME char(100)
    - cur_date date
options:
- IDENTITY columns:
    - ID

image.png

上記プロンプトの実行後、次の SQL が生成され、Databricks にて想定通りにマネージドテーブルを作成することができました。

CREATE TABLE default.city (
  ID bigint GENERATED ALWAYS AS IDENTITY,
  NAME char(100),
  cur_date date
)
USING delta;

image.png

Bing Chat(GPT-4）での実行結果

Bing Chat(GPT-4）にて同じプロンプトを実行したところ、同等の結果となりました。

CREATE TABLE default.city (
  ID BIGINT GENERATED ALWAYS AS IDENTITY,
  NAME CHAR(100),
  cur_date DATE
)
USING delta;

image.png


0 コメント:

コメントを投稿