sqlserver tempdb 增大容量 sql server 增量数据

1. 背景

最近公司有一个项目需要对sqlserver的增量数据进行一个数据迁移

2. 解决方案:针对时间戳进行增量回滚

参考了一个博客因为链接太长我就放在文章末了。（参考博客的篇文章主要是针对的mysql数据库，而我遇到的问题是sqlserver的增量数据同步，在调试过程中遇到很多坑，我会在后面一一道出）

参考了Azure的数据工厂中的增量复制模板（这个是找一个参考列，而这个参考列模板里给的是时间，我的个人理解还可以是其他的比如自增长的id那一列）

其实这两个都是找到上次同步表的参考列的那个值，记录到中间表，然后找到新增数据参考列的最大值，将这个值与中间表中的上次同步的值做一个对比，多出来的数据就是新增的

3. 工作开始时做一个说明

这个一共涉及了三张表：

源表：需要被同步的表，第一时间增加的表（可能是生产表）

中间表：记录参考列的一张表（如果只同步一张表的话，那就只有一条数据，有两个表那就是两条数据，同理·········）

目标表：同步的那张表，第二更新的表（可能是开发使用的表）

4. 源表

新建表

USE [qstest]
GO

/****** Object:  Table [dbo].[im_message]    Script Date: 2021/4/26 14:52:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[im_message](
	[id] [int] NOT NULL,
	[sender] [varchar](45) NULL,
	[send_time] [datetime] NOT NULL,
	[receiver] [varchar](45) NOT NULL,
	[content] [varchar](255) NOT NULL,
	[is_read] [tinyint] NULL,
	[read_time] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[im_message] ADD  DEFAULT (NULL) FOR [read_time]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息发送者：SYSTEM' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'im_message', @level2type=N'COLUMN',@level2name=N'sender'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息接受者：SYSTEM' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'im_message', @level2type=N'COLUMN',@level2name=N'receiver'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息内容：SYSTEM' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'im_message', @level2type=N'COLUMN',@level2name=N'content'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息时候被读取：0-未读；非0-已读：SYSTEM' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'im_message', @level2type=N'COLUMN',@level2name=N'is_read'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'im_message'
GO

插入数据，每秒插入1条，一共插入10条 ，模拟生产数据

#!/usr/local/python/bin/python
# coding=utf-8
'''
@IDE ：PyCharm
@Author ：Cayon_L
'''

import pymssql
import time


class TestDB:
    # --------------------------------------连接数据库------------------------------------------
    def __init__(self):
        try:
            self.conn  =pymssql.connect(
                host='127.0.0.1',    # mysql的主机ip
                user='sa',    # 用户名
                password='***',  # 数据库密码
                database='****',  # 数据库名
                charset='utf8',  # 字符集
            )
            self.cursor = self.conn.cursor()
            print('数据库链接成功！')
        except Exception as e:
            print('数据库链接错误：', e)
    def query(self):
        # --------------------------------------SQL语句块【开始】------------------------------------------
        try:

            sql2 = "INSERT INTO im_message VALUES(%s,'system', CONVERT(varchar,GETDATE(),120),'etl1','KettleTest',0,NULL);"
            info = i
            if i <800:
                self.cursor.execute(sql2,info)
            # SQL提交
                self.conn.commit()
                print('数据库操作成功')
            else:
                print('数据库操作失败')
        except Exception as e:
            print('数据库操作失败', e)
    # --------------------------------------SQL语句块【结束】------------------------------------------
 
    # --------------------------------------数据库连接关闭------------------------------------------
    def end(self):
        try:
            self.cursor.close()
            self.conn.close()
            print('数据库成功关闭！')
        except Exception as e:
            print('数据库关闭失败：', e)
 
 
if __name__ == '__main__':
    j=0
    i = 328  
    while True :
        if j<10:
            test = TestDB()
            test.query()
            test.end()
            j = j+1
            i = i + 1
            time.sleep(1)
        else:
            break

5. 作业流程

1. 开始组件
2. 建时间戳中间表
3. 获取中间表的时间戳，并设置为全局变量
4. 删除目标表中时间戳及时间戳以后的数据
5. 抽取两个数据表的时间戳及时间戳以后的数据进行比对，并根据比对结果进行删除、新增或修改操作
6. 更新时间戳

6. 作业概览

使用的kettle版本：8.2，具体安装流程可以百度

6.1 连接数据库

打开Spoon工具，新建作业，然后在左侧主对象树DB连接中新建DB连接。创建连接并测试通过后可以在左侧DB连接下右键共享出来。

6.2 建时间戳中间表

创建中间表etl_temp,并插入初始的时间戳字段。因为该作业在生产环境是循环调用的，该步骤在每一个同步周期中都会调用，所以在建表时需要判断该表是否已经存在，如果不存在才建表。

PS：如果要设置多张表同步，那么需要将id改为name，并将值改为表名即可

6.3 获取时间戳并设为变量

新建一个转换，在转换中使用表输入、字段选择和设置变量三个组件

表输入：找到中间表源表参考列最大值的那条（下面这个是一张表的同步，如果是多张表那么根据上面的ps操作即可）

这里面用到了一个字段选择，因为kettle转换时间的时候后面多了很多0（标准时间戳也是会有很多0，目前没找到为什么）

下面这个是将输入的那条最大值作为变量传出来

6.4  删除目标表中时间戳及时间戳以后的数据

  防止机器不稳定宕机，这样数据没更新成功，重启之后就会把目标表宕机没更新时间戳的那部分数据给删掉，开始数据的重新增量复制，保证了作业的稳定性

6.5 增量数据的抽取，删除，更新

原始表输入：

目标表输入：

如果有新插入的，那就执行表输出

“Kettle有一个插入/更新组件，但是据网友介绍这个组件性能低下，每秒最多只能同步几百条数据，所有我对插入和更新分别作了不同的处理。插入使用表输出组件；更新使用更新组件。
为了进一步提升同步效率，我在表输出组件使用了多线程（右键>改变开始复制的数量），使同步速度达到每秒12000条。Switch组件和表输出组件中间的虚拟组件（空操作）也是为了使用多线程添加的。”

如果有更新那就执行更新：

如果有删除，那就执行删除操作

6.6.  更新时间戳

update etl_temp set time_stamp=(SELECT top(1)  SEND_TIME FROM im_message ORDER BY SEND_TIME desc) where id='1';

7. 发送邮箱

此处附上调试邮箱时收到的邮件

8. 调度

9. 测试

具体测试结果如下：

10. 参考

我主要是参考使用Kettle实现数据实时增量同步，这篇博客做的是mysql数据的增量复制

mysql和sqlserver有一些不一样的地方：

1，时间戳，sqlserver中是datetime类型

2，时间类型传变量的时候不知道为啥kettl读取出来的数据会在后面加好几个0（费解）

还有一些不一样的地方具体忘了