GitHub Copilot CLIを使ってSQL実行時リスクを評価してみた

GitHub Copilotは、OpenAIの技術を活用したAIコード補完・支援ツールで、コマンドラインベース(CLI、Command Line Interface)も利用できる。

今回は、GitHub Copilot CLIを使って、特定のSQL実行時リスクを評価してみたので、その手順を共有する。

前提条件

下記記事の「前提条件」の内容が完了していること。

Spring BootでSQL Serverに接続しMyBatisを利用してみた今回は、Spring Bootアプリケーションで接続するデータベースをSQL Serverに変更してみたので、そのサンプルプログラムを共...

また、GitHubアカウントがあり、Windows端末上で操作すること。

やってみたこと

1. GitHub Copilot Proの購入
2. GitHub Copilot CLI アクセストークンの作成
3. GitHub Copilot CLIのインストールと起動
4. テーブル情報の取得
5. GitHub Copilot CLIによるSQL実行時リスク評価

GitHub Copilot Proの購入

GitHub Copilot Proは、個人開発者やプログラマー向けの月額10ドルの有料サブスクリプションプランで、これを購入することで、GitHub Copilot CLIを利用できる。GitHub Copilot Proの購入手順は、以下の通り。

1) GitHub Copilotの料金体系は、以下を参照のこと。
https://github.com/features/copilot/plans?cft=copilot_lo.features_copilot

2) GitHubにログインするため、以下の画面にアクセスし、ユーザー名・パスワードを指定後、「Sign in」ボタンを押下する。
https://github.com/login

3) Authenticatorによる2段階認証を設定しているため、以下の画面が表示される。ここで、Authenticatorで表示される6桁の数値を指定すると、ログインできる。

4) 右上の個人アカウントをクリック後、「Copilot settings」メニューを押下する。

5)「try Copilot Pro」リンクを押下する。

6) 以下の画面が表示されるため、「Upgrade now」ボタンを押下する。

7) 以下の画面が表示されるため、各項目を指定し「Save billing information」ボタンを押下する。なお、「Country/Region」のプルダウン以外は、日本語で入力可能となっている。

8) 以下の画面が表示されるため、クレジットカード情報を入力し「Save Payment information」ボタンを押下する。

9) 画面右側に表示される利用規約を確認後チェックを入れ、「Activate now」ボタンを押下する。

10) GitHub Copilot Proを利用できる状態になると、下記画面が表示される。最後にサインアウトするため、左上のHomeボタンを押下する。

11) 右上の個人アカウントをクリック後、「Sign out」メニューを押下する。

12)「Sign out」ボタンを押下し、サインアウトする。

GitHub Copilot CLI アクセストークンの作成

GitHub Copilot CLI利用時に、/loginコマンドによる認証を省略するには、アクセストークンを作成しておく必要がある。その手順は、以下の通り。

1) 以下のサイトにアクセスし、「Fine-grained personal access tokens」リンクを押下する。
https://docs.github.com/en/copilot/how-tos/copilot-cli/install-copilot-cli

2) ユーザー名・パスワードを指定後、「Sign in」ボタンを押下する。

3) Authenticatorによる2段階認証を設定しているため、以下の画面が表示される。ここで、Authenticatorで表示される6桁の数値を指定すると、ログインできる。

4) 以下の画面が表示されるため、「Add permissions」ボタンを押下する。

5)「Copilot Requests」のチェックを追加する。

6) Token name(トークン名)を入力し、「Generate token」ボタンを押下する。

7) 以下のダイアログが表示されるため、「Generate token」ボタンを押下する。

8) 以下のように、アクセストークンが発行されることが確認できる。ここで、赤枠のボタンを押下することで、発行されたアクセストークンをコピーする。

9) 発行したアクセストークンを、以下のように、Windowsの環境変数(GITHUB_TOKEN)に設定する。なお、Windows11の場合、環境変数は「設定」＞「システム」＞「バージョン情報」＞「システムの詳細設定」＞「環境変数」から設定できる。

「EaseUS Todo Backup」は様々な形でバックアップ取得が行える便利ツールだったパソコン内のデータを、ファイル/パーティション/ディスク等の様々な単位でバックアップしたり、バックアップのスケジュール設定や暗号化設定も...

GitHub Copilot CLIのインストールと起動

GitHub Copilot CLIのインストールには、node.jsを利用する。GitHub Copilot CLIのインストールと起動手順は、以下の通り。

1) node.jsをインストールしていない場合は、以下のサイトの2-6)～2-14)を実施する。

Azure Functions上で動作するPythonアプリケーションを作成してみたAzureが提供するサービスに、さまざまなイベントによって駆動し、サーバーの構築や保守をすることなくプログラムを実行できる「Azure ...

2) コマンドプロンプトで「npm install -g @github/copilot」コマンドを実行し、node.jsを利用してGitHub Copilot CLIをインストールする。

3) copilotを起動するには、GitHub Copilot CLIが読取可能なフォルダに移動後、「copilot」コマンドを実行する。

4) 以下の画面が表示されるため、「1. Yes」が選択された状態で「Enter」ボタンを押下する。

5) 以下のように、Copilot向けのプロンプトを入力できる状態になる。

6) GitHub Copilot CLIを終了するには、「/exit」を指定し、Enterボタンを押下する。

ウズウズカレッジJavaコースはわかりやすい動画教材と充実した就業サポートで優良企業を目指せるプログラミングスクールだったJavaは、世界中で広く使われていて、現在の需要が高く将来性もある開発言語になります。 https://www.acrovision....

テーブル情報の取得

特定のSQL実行時リスクを評価するには、そのSQLが参照するテーブルのレコード数・構成を取得する必要がある。その手順は、以下の通り。

1) 特定のSQLが参照するテーブルのレコード数・構成を取得するSQLファイルを作成する。

上記「select_table_info.sql」の内容は、以下の通り。

-- USER_DATAテーブルのレコード数を取得
SELECT t.name AS table_name, SUM(p.rows) AS cnt
FROM sys.tables t
JOIN sys.partitions p
  ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
AND t.name = 'USER_DATA'
GROUP BY t.name;

-- USER_DATAテーブルのテーブル定義を取得
SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    type_name(c.user_type_id) ASvDataType,
    c.max_length AS MaxLength,
    c.is_nullable AS IsNullable
FROM sys.columns c
INNER JOIN sys.tables t
    ON c.object_id = t.object_id
INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
WHERE t.name = 'USER_DATA' 
ORDER BY SchemaName, TableName, c.column_id;

-- USER_DATAテーブルのインデックス情報を取得
SELECT
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ic.key_ordinal AS ColumnPosition,
    t.name AS TableName,
    c.name AS ColumnName,
    ic.is_included_column AS IsIncludedColumn,
    i.is_unique AS IsUnique,
    i.is_primary_key AS IsPrimaryKey
FROM sys.indexes i
INNER JOIN sys.index_columns ic
  ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c
  ON ic.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.tables t
  ON i.object_id = t.object_id
WHERE
     t.name = 'USER_DATA'; 

なお、上記SQLの詳細は、以下のサイトを参照のこと。

Azure SQL Databaseで各テーブルのレコード数とテーブル定義を取得してみたAzure SQL Databaseで特定のSQL文を実行することで、各テーブルのレコード数とテーブル・インデックス情報をまとめて出力す...

2) 以下のコマンドを実行することで、特定のSQLが参照するテーブルのレコード数・構成をログファイル(select_table_info.log)に出力できる。なお、sqlcmdコマンドで「-W」オプションを指定することで余分なスペースを削除することができる。

＜実行コマンド＞

sqlcmd -S (サーバー名) -U (ユーザー名) -P (パスワード) -d (データベース名) -i (インプットファイルパス) -o (アウトプットファイルパス) -s (区切り文字) -W

上記コマンドを実行すると、結果が「select_table_info.log」に出力される。

GitHub Copilot CLIによるSQL実行時リスク評価

GitHub Copilot CLIによるSQL実行時リスク評価手順は、以下の通り。

1) 評価対象となるSQL(update_user_data_chunk.sql)、そのSQLが参照するテーブルのレコード数・構成を取得するSQLファイル(select_table_info.sql)とその実行結果(select_table_info.log)を、同一フォルダに配置する。

なお、update_user_data_chunk.sqlの内容は、以下の通り。

DECLARE @RowCount INT = 1;  -- 現在の処理件数
DECLARE @BatchSize INT = 1000; -- 一括処理件数(最大値：1000)
DECLARE @RoopCount INT = 1; -- ループカウント数

-- 更新前に、user_data.memoをNULLにリセットする
UPDATE dbo.user_data SET memo = NULL;

WHILE (@RowCount > 0)
BEGIN
    -- memoが未設定で、@BatchSizeに設定した件数分のidを取得し、[target_id]に格納
    -- [target_id]に格納したレコードのmemoを一括更新する
    WITH [target_id] AS (
        SELECT TOP (@BatchSize) id 
        FROM dbo.user_data
        WHERE memo IS NULL
    )
    UPDATE u
    SET u.memo = N'ループカウント数：' + CAST(@RoopCount AS NVARCHAR) 
               + N'、実行時刻：' + FORMAT(DATEADD(HOUR, 9, GETUTCDATE()), 'yyyy/MM/dd HH:mm:ss')
    FROM dbo.user_data u
    INNER JOIN [target_id] ti
      ON u.id = ti.id;

    -- 先ほどのupdate文での処理件数を格納
    SET @RowCount = @@ROWCOUNT;
    -- ループカウント数を更新
    SET @RoopCount = @RoopCount + 1;
END

2) テーブル情報(select_table_info.log)のレコード数を、以下のように、10000に変更する。

3) GitHub Copilot CLIを起動し、評価対象となるSQL(update_user_data_chunk.sql)を評価してほしい旨を記載し、「Enter」ボタンを押下する。

4) 以下のように、コマンドプロンプトに、評価結果が出力されることが確認できる。

5) 評価対象となるSQL(update_user_data_chunk.sql)を、以下のように変更する。

DECLARE @RowCount INT = 1;    -- 現在の処理件数
DECLARE @BatchSize INT = 100; -- 一括処理件数
DECLARE @RoopCount INT = 1;   -- ループカウント数

-- エラーが起こった場合に、トランザクションの開始位置までロールバックさせる設定を追加
SET XACT_ABORT ON;

WHILE (@RowCount > 0)
BEGIN
    -- エラー制御を追加
    BEGIN TRY
        -- トランザクションを開始
        BEGIN TRANSACTION;
            -- memoが未設定で、@BatchSizeに設定した件数分のidを取得し、[target_id]に格納
            -- [target_id]に格納したレコードのmemoを一括更新する
            WITH [target_id] AS (
                SELECT TOP (@BatchSize) id 
                FROM dbo.user_data WITH (READPAST) -- ロックスキップ
                WHERE memo IS NULL
                ORDER BY id ASC   -- ロック順序を固定
            )
            UPDATE u
            SET u.memo = N'ループカウント数：' + CAST(@RoopCount AS NVARCHAR) 
                       + N'、実行時刻：' + FORMAT(DATEADD(HOUR, 9, GETUTCDATE()), 'yyyy/MM/dd HH:mm:ss')
            FROM dbo.user_data u
            INNER JOIN [target_id] ti
              ON u.id = ti.id;

            -- 先ほどのupdate文での処理件数を格納
            SET @RowCount = @@ROWCOUNT;
            -- ループカウント数を更新
            SET @RoopCount = @RoopCount + 1;

        -- トランザクションをコミット
        COMMIT TRANSACTION;
        -- コミット後に1秒待機
        WAITFOR DELAY '00:00:01.000';
    END TRY
    -- 例外処理を追加
    BEGIN CATCH
        -- トランザクションをロールバック
        ROLLBACK TRANSACTION;
        -- ループを抜けるため更新件数を0に設定
        SET @RowCount = 0;

        -- エラーメッセージを出力
        SELECT
            ERROR_NUMBER() AS ErrorNumber
          , ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;
END;

6) GitHub Copilot CLIを終了後起動し、評価対象となるSQL(update_user_data_chunk.sql)を評価した結果は以下の通りで、評価結果も改善されたことが確認できる。

なお、memo列へのインデックス追加は、以下のような警告が表示される影響で、実行していない。

CREATE NONCLUSTERED INDEX IX_USER_DATA_memo ON dbo.user_data(memo) WHERE memo IS NULL

要点まとめ

• GitHub Copilot CLIは、OpenAIの技術を活用したAIコード補完・支援ツールで、コマンドラインベース(CLI、Command Line Interface)も利用できる。
• GitHub Copilot CLIを使うと、SQL実行時のリスク評価を行える。