SSIS

  

今年5月份的时候，微软为SQL SERVER 2008中的Intergration service 发布了一篇白皮书，描述了如何使用SSIS包在半小时内将1TB的数据从平面文件中导入到SQL SERVER 2008 数据库。（微软原文地址：We Loaded 1TB in 30 Minutes with SSIS, and So Can You）

   

前段时间做过一个涉及大数据量的数据仓库项目， 其中的ETL部分用到了这篇白皮书中实验的方案，现在将实施过程中的步骤分享给大家，同时求批评求指证。

   

首先是项目背景。我们需要为某公司的内网安全部门收集内部Exchange服务器上的IIS日志（IISW3C格式，平面文件）和NetMON服务器上的NetMON日志（.cap压缩文件格式，可转换成平面文件），并导入到数据库中；客户要求保存90天约27TB历史数据，ETL频率为每天一次，平均下来每次ETL过程需要导入约310GB的数据。

   

   

High Level Concept

   

如是，我们ETL分成两个场景，第一个是初始化数据，需要一次性将90天的数据导入数据库中；另一种是初始化ETL完成之后的每天导入当天的新数据，并删除最老的那一天数据。但不管是哪个场景，第一步都需要将源数据清洗一遍，将IIS日志中会干扰的文件头去掉以及将NetMON日志解析成文本。实现这一步的话推荐使用微软免费的工具LogParser, 脚本如下：

   

解析IIS日志：

   

   

解析NetMON日志：

   

 

 

  

关于LogParser工具的使用以及效率的测试，我会另开一篇文章为大家介绍。

   

下一步就是对不同的两个场景分开处理了。

   

首先是初始化场景，这里我们完全仿照了白皮书We Loaded 1TB in 30 Minutes with SSIS, and So Can You中提及的技术，多个线程向不同的临时表中导数，其中每个临时表都对应主表中的一个分区，导数完成后再将临时表中的数据switch到主表中相对应的分区里。大家可能听得不是很明白，我一步步的理清楚。首先，这里面涉及到的技术如下：

   

    
1.       
   SQL Server分区表；分区表是个什么概念呢？简单的说就是给大表分文件（一般20G以上才考虑），但是逻辑上还是一张表。在这个项目中，我们的主表按天分区.90天历史数据会对应90个分区。
       
     
2.       
   SSIS中的数据流；
       
  

   

然后我们一步步具体实现：

   

第一步：分区表的设计；分区的实现分为三个部分，首先需要建立文件组和文件（Filegroup and File），其次申明分区方法（Partition Function），最后申明分区计划（Partition Scheme）。这里面有4个名词可能有些人不明白，我这也解释一下：

   

    
• Filegroup
  

   

非专业数据库工程师可能会看不太明白这个词，但是我相信数据库对象申明中的 ON PRIMARY 大家一定都熟悉，比如下面这个表申明语句

   

Code Snippet

1. CREATE TABLE [dbo].[MSreplication_options](
2.     [optname] [sysname] NOT NULL,
3.     [value] [bit] NOT NULL,
4.     [major_version] [int] NOT NULL,
5.     [minor_version] [int] NOT NULL,
6.     [revision] [int] NOT NULL,
7.     [install_failures] [int] NOT NULL
8. ) ON [PRIMARY]

   

这里的PRIMARY就是一个文件组，只不过这个文件组不需要我们去申明，它会在数据库创建的时候就会产生。所有的数据库文件（file）都会放到文件组(filegroup)中, 而数据库对象都存放在数据文件里，所以上面代码中的ON PRIMARY的意思就是将[dbo].[MSreplication_options]这张表建立在PRIMARY文件组上的文件里，默认为数据库主文件（数据库名.mdf）。大家可能还见过没有指定文件组的表申明语句，这中情况下SQL Server会默认为PRIMARY文件组。申明文件组的语句如下

   

Code Snippet

1. ALTER DATABASE databaseName ADD FILEGROUP fileGroupName

   

其中fileGroupName就是文件组名，databaseName为文件组所对应的数据库名

   

    
• File
  

   

文件file是和filegroup相对应的，每申明一个file都必须指定一个相对应的filegroup，每个filegroup可以有多个file，如果将一数据库对象指定到一个含有多个文件的文件组上，SQL Server自己会决定哪些数据存放到哪个文件当中。申明语句如下：

   

Code Snippet

1. ALTER DATABASE databaseName ADD FILE
2. (
3.     NAME = _fileName,
4.     FILENAME = _fullFileName,
5.     SIZE = 10GB,
6.     MAXSIZE = 50GB,
7.     FILEGROWTH = 20%
8. )
9. TO FILEGROUP fileGroupName

   

更多file和filegroup的相关资料可以参照ALTER DATABASE File and Filegroup Options (Transact-SQL)

   

    
• Partition Function
  

   

分区方法是用来指定每个分区的边界，示例代码如下：

   

Code Snippet

1. CREATE PARTITION FUNCTION part_Function(DATETIME)
2.     AS RANGE RIGHT
3.     FOR VALUES ('2009-9-1', '2009-9-2', '2009-9-3')

   

第一个括号中的DATETIME是指边界类型为DATETIME，然后在后面那个FOR VALUES的括号里面申明具体的边界值。上面那个例子中用的是右边界，它一共申明了4个值范围：1.所有小于2009年9月1号的时间；2.大于等于2009年9月1号且小于2009年9月2号；3.大于等于2009年9月2号且小于2009年9月3号；4.所有大于等于2009年9月3号的时间。（还有一种左分区AS RANGE LEFT, 更多这方面的信息可以参照Understanding Partitioning）    

   

    
• Partition Scheme
  

   

分区计划的主要用途是将分区方法中所定义的值范围关联到各个文件组上，即阐明哪个范围的数据会放在哪个分区上。示例代码如下：

   

    

Code Snippet

1. CREATE PARTITION SCHEME part_Scheme
2.     AS PARTITION part_Function
3.     TO (fg1, fg2, fg3, fg4)

  

   

这里注意，申明分区计划的时候需要指定一个分区方法。

   

名词解释完了，这篇先写到这，后面的明天更新~。~