如何在SQL Server中将清理数据导出至本地CSV文件

要实现将待清理数据导出为CSV文件的需求，我们可以借助SQL Server的xp_cmdshell调用Linux下的bcp命令，在删除数据前将待清理数据导出到指定目录，具体步骤如下：

1. 前置准备 #

1.1 启用xp_cmdshell #

默认情况下xp_cmdshell是禁用的，需要先启用：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

1.2 创建归档目录并设置权限 #

在Linux服务器上创建用于存储CSV文件的目录，并赋予SQL Server运行用户（默认是mssql）写入权限：

sudo mkdir -p /var/mssql/archived_data
sudo chown mssql:mssql /var/mssql/archived_data
sudo chmod 755 /var/mssql/archived_data

2. 修改存储过程 #

修改后的存储过程会先将待清理的数据导出为CSV，再执行删除操作，确保数据一致性：

create or alter procedure [dp].[dp#purge_objects_data]
 @i_storage_period_days    bigint
 as 
 begin
 set nocount    on;
 set xact_abort on;

 -- 获取待清理的run_id集合
 drop table if exists #dp_root_run_ids;
 create table #dp_root_run_ids( run_id bigint, enddate datetime2);
 WITH cte_run_ids as (
 select distinct id, enddate from cp.run_ids where status='COMPLETED')
 insert into #dp_root_run_ids
         ( run_id
         , enddate
         )
    select rrun.id
         , rrun.enddate
    from cte_run_ids rrun
    where rrun.enddate < dateAdd( day, -1 * @i_storage_period_days, cast(getUTCDate() as date));

 -- 定义归档目录和日期变量
 declare @archive_dir nvarchar(256) = '/var/mssql/archived_data/'
 declare @current_date nvarchar(10) = convert(nvarchar(10), getUTCDate(), 23) -- 格式化为yyyy-MM-dd
 declare @bcp_path nvarchar(128) = '/opt/mssql-tools/bin/bcp'
 declare @cmd nvarchar(1000)

 -- 导出cp.info表的待清理数据
 set @cmd = @bcp_path + ' "SELECT * FROM cp.info WHERE run_id IN (SELECT run_id FROM #dp_root_run_ids)" queryout "' 
            + @archive_dir + 'info_' + @current_date + '.csv" -S localhost -T -c -t, -r\n'
 exec xp_cmdshell @cmd

 -- 导出cp.file_表的待清理数据
 set @cmd = @bcp_path + ' "SELECT * FROM cp.file_ WHERE run_id IN (SELECT run_id FROM #dp_root_run_ids)" queryout "' 
            + @archive_dir + 'file__' + @current_date + '.csv" -S localhost -T -c -t, -r\n'
 exec xp_cmdshell @cmd

 -- 执行删除操作
 DELETE FROM cp.info where run_id in (select run_id from #dp_root_run_ids);
 DELETE from cp.file_ where run_id in (select run_id from #dp_root_run_ids);
end

3. 关键说明 #

• 文件名处理：用getUTCDate()获取UTC日期并格式化为yyyy-MM-dd，确保文件名唯一且符合要求；file_表导出的文件名用file__（双下划线），避免与Linux系统的特殊命名规则冲突。
• bcp参数解释：
  • -c：使用字符格式导出，兼容CSV格式
  • -t,：指定字段分隔符为逗号
  • -r\n：指定行分隔符为换行符
  • -S localhost：连接本地SQL Server实例
  • -T：使用集成身份验证（Linux下SQL Server本地连接默认支持）
• 数据一致性：待清理的run_id提前存入临时表，先导出再删除，避免导出和删除过程中出现数据不一致的情况。

4. 注意事项 #

• 确认mssql用户对归档目录有写入权限，否则bcp命令会执行失败
• 如果需要导出表头，可以手动生成表头文件后用UNIX命令追加到CSV前，比如echo "col1,col2,..." > info_2024-03-29.csv && bcp ... >> info_2024-03-29.csv
• 可添加错误处理逻辑，比如检查bcp命令的执行结果，避免导出失败后仍执行删除操作

内容的提问来源于stack exchange，提问作者kalyan4uonly