ApsaraDB RDS:CLR 統合を使用した RDS for SQL Server の拡張

CLR を使用するタイミング

CLR は、T-SQL では効率的に解決できない、またはまったく解決できないタスクを処理します。

• 複雑な文字列処理 — .NET の正規表現ライブラリを使用して、診療記録テキストの解析、データクレンジングなどのタスクを実行します。

• 高性能計算 — 金融アプリケーションにおけるオプション価格算定など、複雑な数値計算を実行します。

• ファイルおよびネットワーク操作 — 物流におけるドキュメント処理の自動化など、システム間でデータをやり取りします。

• カスタム暗号化と復号 — エンドツーエンド暗号化ソリューションで機密データを保護します。

• 画像処理 — 小売パイプラインで商品画像を分析し、品質を検証します。

• 機械学習 (ML) の統合 — データベース内で直接、リアルタイムのレコメンド機能を構築します。

• 複雑なビジネスロジック — 与信限度額の評価など、多次元の判断をサポートします。

CLR は、T-SQL およびアプリケーション層を置き換えるものではなく、補完として使用してください。T-SQL はデータクエリと基盤的なビジネスロジックに適しています。アプリケーション層は複雑な相互作用やワークフローを処理します。T-SQL でロジックを実装できない場合、またはロジックをアプリケーション層へ移すことでパフォーマンス、デプロイ、セキュリティ上の制約が生じる場合に、CLR を使用してください。

CLR を通じてデータベース内でロジックを実行すると、次の 3 つの具体的なメリットがあります。

制限事項

CLR 統合は、共有 または サーバーレス の RDS SQL Server インスタンスではサポートされていません。

前提条件

開始する前に、次の項目を満たしていることを確認してください。

• ApsaraDB RDS for SQL Server インスタンス (共有またはサーバーレスではないこと)

• アセンブリをコンパイルしてエクスポートするための、自己管理型 SQL Server インスタンス (バージョン 2016 以降)

• ローカルマシンにインストール済みの SQL Server Management Studio (SSMS)

• C# コードの作成とコンパイル用にインストールされた Visual Studio 2022

• RDS インスタンスの対象データベースに対する dbo 権限

ステップ1：RDSインスタンスにおけるCLRの有効化

1. ApsaraDB RDS コンソールの [パラメーター] ページに移動します。

2. インスタンスパラメーター clr enabled を 1 に設定します (デフォルトは 0 です)。

ステップ2：CLRプログラムの作成とコンパイル

Visual Studio 2022 でクラスライブラリプロジェクトを作成し、次の C# コードを貼り付けます。ターゲットフレームワークを .NET Framework 4.7.2 以降に設定し、プロジェクトをビルドして DLL ファイル (例：CLRFuncDemo.dll) を生成します。

デモ関数 AnalyzeSentiment は、組み込みの感情辞書と単語を照合し、コメントをスコアリングします。ポジティブな意味合いの単語は 0 から 1 のスコアを返し、ネガティブな単語は -1 から 0 のスコアを返します。

public class SentimentAnalysis
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlDouble AnalyzeSentiment(SqlString text)
    {
        if (text.IsNull)
            return SqlDouble.Null;

        var sentimentDictionary = InitializeSentimentDictionary();

        string input = text.Value.ToLower();
        double sentimentScore = 0;
        int wordCount = 0;
        int i = 0;

        while (i < input.Length)
        {
            bool matched = false;
            // 現在位置から開始して、辞書内の最長単語に一致させます。
            foreach (var entry in sentimentDictionary.Keys.OrderByDescending(k => k.Length))
            {
                if (i + entry.Length <= input.Length && input.Substring(i, entry.Length) == entry)
                {
                    sentimentScore += sentimentDictionary[entry];
                    wordCount++;
                    i += entry.Length;  // 一致した単語の分だけ進めます。
                    matched = true;
                    break;
                }
            }

            // 認識されない文字はスキップします。
            if (!matched)
            {
                i++;
            }
        }

        return new SqlDouble(wordCount > 0 ? sentimentScore / wordCount : 0);
    }

    private static Dictionary<string, double> InitializeSentimentDictionary()
    {
        return new Dictionary<string, double>
        {
            {"good", 1.0}, {"like", 1.0}, {"excellent", 1.0}, {"great", 1.0}, {"satisfied", 0.8},
            {"nice", 0.6}, {"okay", 0.2}, {"average", 0},
            {"poor", -0.6}, {"terrible", -0.8}, {"disappointed", -0.8}, {"awful", -1.0}, {"hate", -1.0}
        };
    }
}

ビルド後に、出力 DLL のパス (例：E:\Backup\CLRFuncDemo.dll) を確認してください。

ステップ3：バイナリ文字列としてのアセンブリのエクスポート

RDS SQL Server は DLL の直接アップロードをサポートしていないため、まず自己管理型 SQL Server インスタンス (バージョン 2016 以降) で DLL をアセンブリとして登録し、その後、RDS にインポートするためにバイナリ文字列としてエクスポートします。

1. 自己管理型 SQL Server インスタンスで、次を実行します。

   CREATE ASSEMBLY [CLRFuncDemo]
   FROM 'E:\Backup\CLRFuncDemo.dll'
   WITH PERMISSION_SET = SAFE

   PERMISSION_SET オプションは、アセンブリがアクセスできるリソースを制御します。SAFE は最も制限の厳しいオプションで、外部システムへのアクセスなしにローカルのデータベースリソースのみにアクセスできます。アセンブリがファイルの読み取りや Web サービスの呼び出しを必要とする場合は EXTERNAL_ACCESS を使用し、アンマネージドコードが必要な場合に限り UNSAFE を使用してください。いずれもセキュリティリスクが高く、より強い正当性が必要です。

2. SSMS を使用してアセンブリをバイナリ文字列としてエクスポートし、次のステップで使用するために結果をコピーしてください。

ステップ4：CLRアセンブリのRDSインスタンスへのインポート

SSMS を使用して RDS SQL Server インスタンスに接続し、対象データベースに対する dbo 権限を持つアカウントでログインしてから、次を実行します。

-- バイナリ文字列からアセンブリを作成します
CREATE ASSEMBLY [CLRFuncDemo]
FROM 0x4D5A9000... -- 実際のバイナリ文字列に置き換えてください
WITH PERMISSION_SET = SAFE;

-- アセンブリにより実装されるCLR関数を作成します
CREATE FUNCTION dbo.AnalyzeSentiment(@text NVARCHAR(MAX))
RETURNS FLOAT
AS EXTERNAL NAME CLRFuncDemo.[SentimentAnalysis].AnalyzeSentiment;

ステートメントが成功すると、データベース内にアセンブリと関数が表示されます。

ステップ5：CLR関数のテスト

テストテーブルを作成し、サンプルレビューを挿入して、感情スコアをクエリします。

CREATE TABLE UserReviews (
    ReviewID INT IDENTITY(1,1) PRIMARY KEY,
    Username NVARCHAR(50),
    ProductName NVARCHAR(100),
    ReviewContent NVARCHAR(MAX),
    ReviewDate DATETIME
);

INSERT INTO UserReviews (Username, ProductName, ReviewContent, ReviewDate)
VALUES
    ('田中 太郎', 'Smartphone E', N'This phone is terrible, battery life is awful, I am very disappointed!', '2024-03-25 13:00:00'),
    ('鈴木 次郎', 'Laptop F', N'Very poor experience, bad cooling, terrible performance, not recommended!', '2024-03-26 14:30:00'),
    ('佐藤 三郎', 'Headphones G', N'Sound quality is terrible, these headphones are awful, very disappointing!', '2024-03-27 16:10:00'),
    ('伊藤 四郎', 'Tablet H', N'This tablet is okay, average performance, usable.', '2024-03-28 10:00:00'),
    ('渡辺 五郎', 'Smartphone I', N'The phone is average, nothing particularly good or bad, just mediocre.', '2024-03-29 11:20:00'),
    ('山本 花子', 'Laptop B', N'This laptop is excellent! Great performance, I love it, extremely satisfied!', '2024-03-20 09:15:00'),
    ('中村 恵子', 'Headphones C', N'Sound is great, good build quality, excellent user experience, I am very satisfied!', '2024-03-22 11:00:00'),
    ('小林 直樹', 'Tablet D', N'This tablet is really good, powerful performance, good battery life, I really like it!', '2024-03-24 15:45:00'),
    ('加藤 健太', 'Smartphone K', N'The phone is very user-friendly, excellent camera performance, I really like it!', '2024-03-31 12:05:00');

SELECT
    ReviewID,
    Username,
    ProductName,
    ReviewContent,
    dbo.AnalyzeSentiment(ReviewContent) AS SentimentScore,
    CASE
        WHEN dbo.AnalyzeSentiment(ReviewContent) > 0.3  THEN 'Positive'
        WHEN dbo.AnalyzeSentiment(ReviewContent) < -0.3 THEN 'Negative'
        ELSE 'Neutral'
    END AS SentimentCategory
FROM UserReviews
ORDER BY SentimentScore DESC;

このクエリは、各レビューを Positive (スコア > 0.3)、Negative (スコア < -0.3)、または Neutral に分類し、最もポジティブなものから最もネガティブなものの順に並べ替えます。

トラブルシューティング

Msg 10343, Level 14, State 1, Line 12
CREATE or ALTER ASSEMBLY for assembly 'CLRFuncDemo' with the SAFE or EXTERNAL_ACCESS option failed
because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you
sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE
ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.