数据库-自动数据导出

缘起

我们使用数据库往往不仅仅是希望数据有个可靠的存储空间。如果数据只存不使用，那就像是一个人买了房不住又不租。
常见的数据使用有很多，比如做汇总，做筛选查询，做关键度量分析，还有其他程序的调用；如果是不太专业的人做的不满足一二三范式的数据，那在使用之前还需要做清洗。
最近有个工作，需要对数据做汇总分析后把结果数据对接给其他程序使用。下面就简单叙述一下我个人的思路以及最后的做法。

最直观的方式

什么最直观呢，就像是使用inline css.一样，把需要的数据逻辑直接写入程序。这么做首先我们说实现上绝对没有问题。只是不太优雅。
如果是单兵作战，相关逻辑一个人都很清晰，直接编写程序就好。如果是团队协助，那么负责处理数据的人需要花几倍于程序编写的时间对处理逻辑进行归档，有的时候还要花更多的时间对处理的具体实现进行培训。这也是人月神话提到团队协作的痛点。

个人推荐的方式

作为一个边学边做的码农，也许未来回头再看今天的想法仍然不是很成熟，不过当下的我认为有比直接把数据逻辑写入程序更好的处理方法。
以现在面对的这个需求来看，以SQL Server 为例，我的想法是数据汇总分析由数据库异步完成，其他程序直接对接生成的数据文件。那么进一步拆解数据库需要做的，基本就是：1.数据查询，2.数据汇总，3.数据导出。

• 关于数据查询与数据汇总，其实可以直接在一个Select 中完成，这部分工作我定义为一个独立模块放到对应数据库的储存过程中了，这样数据管理人员需要查看的时候也可以简便的运行并查看数据。相关储存过程定义如下：

USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbol.[GetSPData]
	@Year int,
	@Month int
AS
BEGIN
	SELECT
		B.[Name],
		COUNT(B.[Name])AS occount,
		SUM(DATEDIFF(MINUTE,A.StartTime, A.EndTime)) AS TotalT 
	FROM
		dbo.Jobs As A 
	INNER JOIN
		dbo.Releases AS B
	ON
		A.[ReleaseId] = B.[Id]
	WHERE
		MONTH(A.StartTime) = @Month AND YEAR(A.StartTime) = @Year 
	GROUP BY
		B.[Name]
	ORDER BY
		B.[Name];
END;

• 数据导出网上有很多思路。因为个人希望把这个操作作为数据库的定时任务并希望功能实现上画风可以保持一致，所以这里启用了xp_cmdshell组件并把相关操作定义在Job当中，用queryout的形式把数据导出到指定服务器共享文件夹。相关定义如下：

DECLARE @Year INT;
DECLARE @Month INT;
DECLARE @file_path varchar(200);
DECLARE @file_name varchar(200);
DECLARE @exec_sql varchar(200);

SELECT @Year=YEAR(DATEADD(MONTH,-1,GETDATE()));
SELECT @Month =MONTH(DATEADD(MONTH,-1,GETDATE()));
SET @file_path ='D:\1folder\Report';
SET @file_name=CAST(@Year AS VARCHAR(4))+'-'+CAST(@Month AS VARCHAR(2)）+ '.csv';
SET @exec_sq1='EXEC [DB1].[dbo].[GetSPData] @Year='+CAST(@Year AS VARCHAR(4))+ '，@Month='+CAST(@Month AS VARCHAR(2));
SET @exec_sql ='bcp "'+ @exec_sql +'"queryout "'+@file_path + '\' + @file_name + '" -w -T';
EXEC master..xp_cmdshell @exec_sql;

不过使用这个方式有几个小点需要留意

1. xp_cmdshell组件功能很强大，可以执行系统命令，所以默认是关闭的，打开后需要留意权限的分配。
2. queryout 的时候参数-c和-w的区分：

• -c 参数实际上是以字符形式导出数据，并且默认使用客户端的当前代码页（Code Page）进行编码，这通常是基于操作系统的区域设置，如果需要导出可以用Excel正常打开的csv文件，还需要用PowerShell脚本或者Notepad++将文件的编码转换为UTF-8或UTF-16。
• -w 参数使bcp以Unicode 格式（UTF-16LE）导出数据，导出的CSV文件使用Unicode编码（如 UTF-16），Exce可以正常打开。

3. 导出的数据不包含表头。如果是直接对接其他程序，没有表头一般影响不大，如果是提供给其他用户，那么xp_cmdshell组件方案不太合适。可以考虑用其他脚本对数据文件再次处理或者使用SSIS或其他第三方组件进行实现；不过作为数据库系统导出数据不包含表头也符合其设计思路，本身就是纯数据处理。

补充

数据库中的Job虽然是可以通过Schedule 自动定时，但是如果临时需要运行，也可以通过以下命令启动。

use msdb
EXEC sp_start_job @job_name= 'SPData'