尹相志Allan's blog

Big Data的議題越來越盛行，也因此有越來越多的需求是在收集網頁資料，除了撰寫爬蟲程式之外，在此介紹使用SQL Server的CLR函數來實作抓取網頁資料，如此一來只需要利用Select 或Update的查詢語法，就能夠快速地抓取網頁資料或是寫入置資料庫中。

CLR (Common Language Runtime)是從SQL Server 2005之後提出的新功能，可以透過.Net程式的撰寫來延伸既有的SQL Server功能，同時還可以根據功能需求的差異來撰寫CLR自訂函數、預存程序、觸發…。在此是以SQL Server 2012為基礎示範，不過基本上SQL Server 2005以後的版本其實都支援，只是對應的.Net Feamework版本不同以及開發工具不同。

在SQL Server 2012中已經將CLR開發整合至新的開發平台(SQL Server Data Tool)，各位可以到以下網址下載，有同時提供整合Visual Studio 2010以及2012的版本。

http://msdn.microsoft.com/zh-tw/data/tools.aspx

 

首先新建一個資料庫專案

 

接著新增名為「GetWebData」的SQL CLR C#使用者定義函式。

 

輸入以下程式碼即可。基本上輸入引數是網頁的Url (proxy的位置與port若不指定輸入null與80即可)，然後只是透過CLR發出一個HttpWebRequest，然後將回傳內容轉換為SqlString資料型別而以，由於回傳內容可能很大，因此必須使用SQL Server中的nvarchar(max)型別來儲存，因此我們在函數上方標示[return: SqlFacet(MaxSize = -1)] 。

 

using System.Data.SqlTypes;
using System.IO;
using System.Net;
using System.Text;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

    {
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
    [return: SqlFacet(MaxSize = -1)]
    public static SqlString GetWebData(string url, string proxyHost, int proxyPort)
        {

        string UserAgent = "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.1.7) Gecko/20091221 Firefox/3.5.7";
        SqlString returnstring = "";
        Encoding ascii = Encoding.ASCII;
        Encoding unicode = Encoding.Unicode;


        byte[] unicodeBytes = unicode.GetBytes(url);
        byte[] asciiBytes = Encoding.Convert(unicode, ascii, unicodeBytes);

        char[] asciiChars = new char[ascii.GetCharCount(asciiBytes, 0, asciiBytes.Length)];
        ascii.GetChars(asciiBytes, 0, asciiBytes.Length, asciiChars, 0);
        string asciiString = new string(asciiChars);


               HttpWebRequest request = (HttpWebRequest) WebRequest.Create(url);
               if (!string.IsNullOrEmpty(proxyHost))
               {
                   request.Proxy = new WebProxy(proxyHost, proxyPort);
               }
               request.UserAgent = UserAgent;
        


               using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
               {
                   using (Stream responseStream = response.GetResponseStream())
                   {
                       using (StreamReader read = new StreamReader(responseStream))
                       {
                           returnstring = (SqlString)read.ReadToEnd();
                           responseStream.Flush();
                       }
                   }
               }


        return returnstring;

        }
    };

 

如此一來就完成開發了。在部署前需要注意幾件事。首先是適用的.net feamework版本，目前SQL Server 2012適用的是4.0，請要記得切換(預設是4.5)。同時因為我們需要發送對外HttpWebRequest，因此要將權限等級由「Safe」設定為「EXTERNAL_ACCESS」。以及將組件擁有者設為「dbo」

 

然而要部署的資料庫必須符合兩個要件:

1. 部署的SQL Server必須啟用CLR

2. 權限等級「EXTERNAL_ACCESS」要求部署的資料庫必須是Trustworthy

 

基本上預設部署時都會啟用CLR，至於要將資料庫設為可信任，請於「專案設定」頁籤中點選「資料庫設定」，並於彈出畫面的「其他」頁籤中勾選「可信任」選項。

 

接下來就只要發行專案，在談出的「發行資料庫」畫面中輸入你要發行的資料庫所在位置，即可點選「發行」。

 

部署成功後就可以在測試資料庫的「可程式性」區域看到新增的純量值函數GetWebData

 

 

接著只需要透過Select語法就可以抓取網頁資料，或是透過Update語法將網頁資料寫回至SQL Server資料庫中。

 

在這邊需要注意的是，CLR函數是以資料列的方式來執行，如果您傳入的URL引數是來自於資料表，而資料表中有大量的Url紀錄，此時SQL Server就會發出大量的HttpWebRequest出去，若是您查詢的都是同一個網站，就會變成類似阻斷式攻擊的效果。由於我們的目的僅是希望抓取網頁資料作分析，因此請使用時務必考慮分散查詢的密度，以免造成對方網站伺服器的嚴重負擔。

 

亞洲資採臉書粉絲團：www.facebook.com/AsiaMinerTW

 

Allan Yiin

CTO, AsiaMiner

Allan Yiin

CTO, AsiaMiner