将 SQL Server 表导出到多个部件文件

2021-12-28 00:00:00 sql database hive sql-server bcp

我需要将一个大约 100GB 的相当大的 SQL Server 表导出到 CSV 文件.但输出不是单个 csv 文件,理想情况下应该是多个文件,比如 10 个文件,每个 10GB.

I need to export a fairly large SQL Server table ~100GB to a CSV file. But rather than the output be a single csv file, it should ideally be multiple files say 10 files each 10GB.

我看到 BCP 有一个 batch_size 参数,但这仍然将所有数据写入单个文件?是否有其他免费实用程序可以满足我的要求?可以以字节或行数指定文件大小的地方?

I see BCP has a batch_size argument but this still writes all data to a single file? Are there other free utilities for doing what I require? Either where the size of file can be specified in bytes or number of rows?

就上下文而言,这是为了将数据与 Hive/Hadoop 平台中的其他来源相结合,因此如果有更好的导出数据的方法,我愿意接受建议.

For bit of context this is so the data can be combined with other sources in a Hive/Hadoop platform, so if there are better ways of exporting the data I'm open for suggestions.

推荐答案

我认为您可以将 SQL 2012 的分页函数 OFFSETFETCH 与 bcp 结合使用:

I think you could use SQL 2012's paging functions OFFSET and FETCH in conjunction with bcp:

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

相关文章