将SQL Server表导出到多个文件

Question

我需要将一个相当大的SQL Server表（约100GB）导出为CSV文件。但与其输出单个csv文件，最好将其分成多个文件，例如每个文件大小为10GB的10个文件。

我看到BCP有一个batch_size参数，但这仍然将所有数据写入单个文件？是否有其他免费工具可以满足我的需求？可以指定文件大小（以字节为单位）或行数？

为了提供一些背景信息，这是为了将数据与Hive / Hadoop平台中的其他来源组合在一起，因此，如果有更好的导出数据的方法，我很乐意听取建议。

- Jon

---

你正在使用哪个版本的SQL Server？ - Hart CO

Answer 1

我认为你可以使用SQL 2012的分页函数OFFSET和FETCH与bcp结合使用:

SELECT *
FROM Table
ORDER BY ID --Primary Key
OFFSET 100000001 ROWS
FETCH NEXT 100000000 ROWS ONLY

- Hart CO

Answer 2

遗憾的是，BCP的batch_size参数不能控制输出。

我处理这种拆分的方式有：

1 - 简单但不可重复：创建一个命令文件（.cmd），对特定的行范围运行多个BCP。这可能需要在表上使用基于IDENTITY(1,1)的主键。

bcp "SELECT * FROM MyTable WHERE Id BETWEEN 0 AND 10000000" queryout …  
bcp "SELECT * FROM MyTable WHERE Id BETWEEN 10000000 AND 20000000" queryout …   

2 - 简单易行且可以重复使用，需要大量磁盘空间: BCP 将整个表导出到一个文件中，然后使用 split 创建所需数量的新文件，每个文件的大小都相同（注意：按行拆分可能是更好的选择）。使用'Cygwin'（GnuWin32不再维护）安装split和其他任何你需要的工具。

 bcp MyDb.MySchema.MyTable out C:\MyFile.csv -T -w  
 split -b 10737418240 C:\MyFile.csv C:\MySplitFile_  

生成以下文件。

 C:\MySplitFile_aaa
 C:\MySplitFile_aab
 …

3 - 复杂但可重复，需要可能不安全的 T-SQL: 使用 xp_cmdshell 函数在存储过程中调用 BCP 并通过迭代表来实现。

 DECLARE @loop AS INT;   
 --Use WHILE to loop as needed--   
 DECLARE @sql AS VARCHAR(MAX);   
 --Add code to generate dynamic SQL here--   
 DECLARE @bcp AS VARCHAR(MAX);   
 SELECT @bcp='BCP "'+@sql+'" queryout C:\MyFolder\MyFile_'+@loop+'.csv';   

最终说明：如果你的数据中使用了任何NVARCHAR字段，则需要使用-w标志，并且要注意输出将是UTF-16LE格式。我强烈建议在尝试在Hadoop中处理数据之前，使用iconv（再次来自'Cygwin'）将其转换为UTF-8格式。

- Joe Harris

Answer 3

如果您有一个可排序的单个主键字段，您可以找到定义所需行的边界的键，然后只需使用定义边界的WHERE选择记录。

这与Joe给出的＃1类似，但您的键不需要连续，也不需要是数字。以下是一个简单的示例：

DECLARE @maxrowsperfile AS bigint = 1048576
DECLARE boundaries CURSOR FOR
    SELECT the_sortable_key
    FROM
    (
        SELECT
            the_sortable_key
            , ROW_NUMBER() OVER(ORDER BY the_sortable_key) AS the_row_number
        FROM the_table
    ) AS t
    WHERE the_row_number % @maxrowsperfile = 0

OPEN boundaries

DECLARE @lowerbound AS [key type] = [value A]
DECLARE @upperbound AS [key type] = [value A]

FETCH NEXT FROM boundaries
INTO @upperbound

IF @lowerbound = @upperbound
    PRINT 'bcp "SELECT * FROM the_table" queryout file -w -T'
ELSE
    DECLARE @filecount AS int = 1
    BEGIN
        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'bcp "SELECT * FROM the_table WHERE key > ' + CAST(@lowerbound AS varchar) + ' AND key <= ' + CAST(@upperbound AS varchar) + ' queryout file_' + CAST(@filecount AS varchar) + ' -w -T'
            SET @filecount = @filecount + 1
            SET @lowerbound = @upperbound
            FETCH NEXT FROM boundaries
            INTO @upperbound
        END
        PRINT 'bcp "SELECT * FROM table WHERE key > ' + CAST(@lowerbound AS varchar) + ' queryout file_' + CAST(@filecount AS varchar) + ' -w -T'

    END
CLOSE boundaries
DEALLOCATE boundaries

- gazzman

Answer 4

我会先导出文件，然后在外部进行分割。假设您正在运行Windows机器，有几个免费的工具可以帮助您。请参见Superuser上的此答案了解更多信息。

- BellevueBob

---

分割文件外部运行的风险是否会导致数据行跨越两个文件？如果您正在提取然后进行摄取，那么这将是可以接受的。但是，我认为如果您想利用可扩展的导入，您的导入将单独处理每个文件并单独提取和导入，因此在压缩期间进行分割可能会将一行数据放入两个文件中，从而破坏您的可扩展的数据导入？ - Colorado Techie