【转载】SqlBulkCopy.批量导入日志文件中的内容到SQL SERVER

单叙述了使用正则表达式分割Apache日志文件中每条记录的信息，现在解决如何批量导入日志文件中的内容到SQL SERVER数据库。思路如下：

1.利用SqlBulkCopy.WriteToServer(IDataReader reader)方法批量导入日志文件中的记录到SQL SERVER数据库。

2.自定义的TxtDataReader类实现IDataReader接口用于传递给SqlBulkCopy.WriteToServer使用。

3.在TxtDataReader的实现中利用正则表达式分组捕获需要的信息。

第一步：实现自定义的TxtDataReader类

1.代码中的未列出实现的IDateReader接口成员对当前实现的功能没有影响。 

2.TxtDataReader构造函数的pattren参数格式必须是：命名分组_类型，类型只简单的匹配了int|date|string三种。

 

partial class TxtDataReader : System.Data.IDataReader
{
    string pattern;
    int fieldCount;
    TextReader reader;
    NameValueCollection FieldInfo;
    NameValueCollection items;
    bool isClosed;
    /// <summary>
    /// 私有构造函数，内部调用创建TxtDataReader的实例
    /// </summary>
    /// <param name="file">要处理的文本</param>
    /// <param name="pattern">匹配每行文本的正则表达式</param>
     private TxtDataReader(string file,string pattern)
    {
        this.pattern=pattern;
        MatchCollection matches = Regex.Matches(pattern, "\\(\\?<([^<>]+)_(int|date|string)>");
        this.fieldCount = matches.Count;
        this.FieldInfo = new NameValueCollection();
        for (int i = 0; i < matches.Count; i++)
        {
            this.FieldInfo.Add(matches[i].Groups[1].Value, matches[i].Groups[2].Value);
        }
        this.reader = new StreamReader(file);
        this.isClosed = false;
    }
    /// <summary>
    /// 静态方法返回TxtDataReader的实例
    /// </summary>
     public static TxtDataReader ExecuteReader(string file,string pattern)
    {
        return new TxtDataReader(file, pattern);
    }
    /// <summary>
    /// 列数
    /// </summary>
     public int FieldCount
    {
        get { return this.fieldCount; }
    }
    /// <summary>
    /// 读取并处理文件的下一行
    /// </summary>
     public bool Read()
    {
        if (this.isClosed)
        {
            return false;
        }
        string line = this.reader.ReadLine();
        if (line == null)
        {
            this.isClosed = true;
            return false;
        }
        GroupCollection groups = Regex.Match(line, this.pattern).Groups;
        items = new NameValueCollection();
        for (int i = 0; i < this.FieldInfo.Count; i++)
        {
            string value = groups[FieldInfo.GetKey(i)+"_"+this.FieldInfo[i]].Value;
            if (this.FieldInfo[i] == "date")
            {
                string format = "[dd/MMM/yyyy:HH:mm:ss zzzz]";
                value = DateTime.ParseExact(value, format, new CultureInfo("en-US", true)).ToString();
            }
            if (this.FieldInfo[i] == "int")
            {
                try
                {
                    value = Convert.ToInt32(value).ToString();
                }
                catch (Exception ex)
                {
                    value = "0";
                }
            }
            items.Add(FieldInfo.GetKey(i), value);
        }

        return true;
    }
    /// <summary>
    /// 根据列索引返回列值
    /// </summary>
     public object GetValue(int i)
    {
        return items[i];
    }
    /// <summary>
    /// 返回读取器的状态
    /// </summary>
     public bool IsClosed
    {
        get { return this.isClosed; }
    }
    /// <summary>
    /// 关闭读取器
    /// </summary>
     public void Close()
    {
        this.reader.Close(); ;
    }
    #region IDisposable 成员

    public void Dispose()
    {
        this.Close();
    }

    #endregion
}

 

 

 

第二步：构造用于 TxtDataReader解析日志每行记录的正则表达式

正则表达式	类型	备注	描述
^			匹配每一行的开头。
(?<ip_string>[0-9.]+)\s	string	not null	匹配IP地址。
(?<identity_string>[\w.-]+)\s	string	可能为"-"	匹配identity，由数字字母下划线或点分隔符组成。
(?<userid_string>[\w.-]+)\s	string	可能为"-"	匹配userid，由数字字母下划线或点分隔符组成。
(?<date_date>\[[^\[\]]+\])\s"	datetime	not null	匹配时间。
(?<request_string>(?:[^"]|\")*)"\s	string	可能为""	匹配请求信息。
(?<status_int>\d{3})\s	int	3个整数	匹配状态码。
(?<bytes_int>\d+|-)\s"	int	可能为"-"	匹配响应字节数或-。
(?<ref_string>(?:[^"]|\")*)"\s"	string	可能为"-"	匹配"Referer"请求头，双引号中可能出现转义的双引号\"。
(?<useragent_string>(?:[^"]|\")*)"	string	可能为"-"	匹配"User-Agent"请求头，双引号中可能出 现转义的双引号\"。
$			匹配行尾。

完整的正则表达式如下：

^(?<ip_string>[0-9.]+)\s(?<identity_string>[\w.-]+)\s(?<userid_string>[\w.-]+)\s(?<date_date>\[[^\[\]]+\])\s"(?<request_string>(?:[^"]|\")*)"\s(?<status_int>\d{3})\s(?<bytes_int>\d+|-)\s"(?<ref_string>(?:[^"]|\")*)"\s"(?<useragent_string>(?:[^"]|\")*)"$

第三步：创建用于导入的表格

 

CREATE TABLE [dbo].[test](
    [ip] [nvarchar](64),
    [identity] [nvarchar](64),
    [userid] [nvarchar](64),
    [date] [datetime] NULL,
    [request] [nvarchar](2000),
    [status] [int] NULL,
    [bytes] [int] NULL,
    [ref] [nvarchar](2000),
    [useragent] [nvarchar](2000) NULL
)

 

 

 

 

第四步：使用SqlBulkCopy导入数据

 

static void Main(string[] args)
{
    string pattern = "^(?<ip_string>[0-9.]+)\\s(?<identity_string>[\\w.-]+)\\s(?<userid_string>[\\w.-]+)\\s(?<date_date>\\[[^\\[\\]]+\\])\\s\"(?<request_string>(?:[^\"]|\\\")+)\"\\s(?<status_int>\\d{3})\\s(?<bytes_int>\\d+|-)\\s\"(?<ref_string>(?:[^\"]|\\\")+)\"\\s\"(?<useragent_string>(?:[^\"]|\\\")+)\"$";
    string conn = "Data Source=.;Initial Catalog=seodb;Integrated Security=True";
    using (SqlBulkCopy sbc = new SqlBulkCopy(conn))
    {
        TxtDataReader reader = TxtDataReader.ExecuteReader(file, pattern);
        sbc.DestinationTableName = "dbo.test";
        try
        {
            sbc.WriteToServer(reader);
        }
        catch (Exception ex)
        {

        }
        finally
        {
            reader.Close();
        }
    }
    
}

备注：

 

1.如果其他场合需要以reader[int]或reader[string]形式获取读取器重的方法，需实现索引器。如需使用其他IDataReader接口定义的方法，同样。

参考代码：

 

public object this[string name]
{
    get { return items[name]; }
}

 public object this[int i]
{
    get { return items[i]; }
}
//Console.WriteLine(reader["ip"]);

2.为让示例代码简单清晰，其他未提供具体实现的IDataReader代码放在了分部类中。

3.当前所述解决的问题是解析日志文件并导入数据库中，只提供了满足当前需求的精简代码，甚至不包含必要的异常处理，数据截断、溢出等处理，如在实际用途中使用，需自行扩展和添加代码。

4.简单的、仅具参考价值的测试：windows xp\.net 2.0 \sql server 2005\2G内存 217M大小正式站日志文件，处理十次，平均时间35秒。