3 articles tagués avec « FastBCP »

Exporting data from SQL Server into a file format that's analytics-friendly is a common need: data lake ingestion, long-term storage, or feeding modern query engines.

In this tutorial, we'll export the TPC-H SF=10 orders table (~15M rows, 9 columns) from SQL Server into Parquet files using FastBCP.

Context​

CSV is universal, but it's not always the best format for analytics:

• larger files (no compression by default)
• weak typing (everything is "text")
• slower scans for columnar workloads

Parquet is a better fit for analytics pipelines:

• columnar layout (faster scans)
• typed columns
• efficient compression
• great interoperability (DuckDB, Spark, Trino, Polars…)

FastBCP makes it easy to export a SQL Server table into Parquet while leveraging parallelism on multi-core machines.

The dataset we will export (TPC-H SF=10 orders)​

We'll export the TPC-H SF=10 dataset, table orders.

• Table: orders
• Scale: SF=10
• Size: ~15 million rows
• Columns: 9 columns
• Source: tpch10.dbo.orders (SQL Server)
• Output directory: D:\temp\tpch10\orders\parquet

Source table in SQL Server​

SELECT COUNT(*)
FROM dbo.orders;

SELECT TOP 10 *
FROM dbo.orders;

What is FastBCP?​

FastBCP is a command-line tool focused on fast exports from SQL Server to files, with a strong emphasis on parallelism.

For SQL Server → Parquet exports, FastBCP is interesting because it can:

• export large tables efficiently
• split the export across multiple workers (parallel tiles)
• write a modern analytics format (Parquet)
• optionally keep the outputs separate files (or merge if needed)

Exporting orders to Parquet files with FastBCP​

To build the command line, we used the FastBCP Wizard: https://fastbcp.arpe.io/docs/latest/wizard

Here is the exact command we used to export the 15 million rows into Parquet:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost,11433" `
  --trusted `
  --database "tpch10" `
  --sourceschema "dbo" `
  --sourcetable "orders" `
  --fileoutput "orders.parquet" `
  --directory "D:\temp\tpch10\orders\parquet" `
  --parallelmethod "Ntile" `
  --paralleldegree -2 `
  --distributekeycolumn "o_orderkey" `
  --merge false

A few things to highlight:

• --parallelmethod "Ntile" splits the table into multiple tiles for parallel export.
• --paralleldegree -2 uses number of cores / 2 workers. On this machine we have 24 cores, so -2 means 12 parallel workers.
• With Ntile + 12 workers, the export is naturally split into 12 partitions, and because --merge false, FastBCP writes 12 output Parquet files (one per partition).
• --distributekeycolumn "o_orderkey" is a great key for partitioning TPC-H orders.
• --merge false keeps the output as multiple Parquet files, which is often ideal for downstream parallel processing.

1) Running the FastBCP command from PowerShell

2) FastBCP completion output (elapsed time and summary)

3) Verifying the Parquet files generated

Results​

On this machine, the export completed in 12.6 seconds, generating ~15 million rows (9 columns) from SQL Server split across 12 Parquet files.

For more details on all available options (parallel methods, degrees, mapping, sources, output format), see the documentation: https://fastbcp.arpe.io/docs/latest/

Conclusion​

If your goal is analytics-friendly exports (data lake, BI, query engines), Parquet is usually a better target than CSV.

With FastBCP, exporting a large SQL Server table into multiple Parquet files becomes a one-command workflow—while taking advantage of parallelism on multi-core machines.

Resources​

• FastBCP: https://fastbcp.arpe.io
• Docs: https://fastbcp.arpe.io/docs/latest/
• Wizard: https://fastbcp.arpe.io/docs/latest/wizard

The FastBCP Docker image allows you to easily integrate this high-performance bulk copy tool into your data integration workflows, without having to install FastBCP directly on your machines.

Exporting data from SQL Server to CSV is one of those tasks that feels straightforward… until the table gets large and you want the export to finish fast while fully using your machine.

In this tutorial, we'll start with the traditional approach (BCP) and then export the same dataset using FastBCP, a more powerful option when you want to leverage parallelism.

Context​

The classic tool for exporting data to files in the SQL Server ecosystem is BCP. It's stable, widely used, and easy to script.

But when your exports become heavy (large tables, tight windows, multi-core machines), a single-threaded pipeline can leave performance on the table.

That's where FastBCP comes in: it keeps the simplicity of a CLI workflow, while adding parallel execution and a pipeline designed for massive throughput.

The dataset we will export (TPC-H SF=10 orders)​

We'll export the TPC-H SF=10 dataset, table orders.

• Table: orders
• Scale: SF=10
• Size: ~15 million rows
• Columns: 9 columns
• Source: tpch10.dbo.orders (SQL Server)
• Output directory: D:\temp\tpch10\orders\csv

Source table in SQL Server​

SELECT COUNT(*)
FROM dbo.orders;

SELECT TOP 10 *
FROM dbo.orders;

What is BCP?​

BCP (Bulk Copy Program) is Microsoft's command-line utility for bulk data import/export with SQL Server.

Why people like it:

• it's native to the SQL Server ecosystem
• it's simple and script-friendly
• it's been used in production for years

Docs (Microsoft Learn): https://learn.microsoft.com/fr-fr/sql/relational-databases/import-export/import-and-export-bulk-data-by-using-the-bcp-utility-sql-server?view=sql-server-ver17

Exporting orders with BCP​

BCP can export a table (or query) to a file. Here is a practical baseline command:

bcp "SELECT o_orderdate, o_orderkey, o_custkey, o_orderpriority, o_shippriority, o_clerk, o_orderstatus, o_totalprice, o_comment FROM tpch10.dbo.orders" queryout "D:\temp\tpch10\orders\single_csv\orders.csv" `
  -S "localhost,11433" `
  -T `
  -c `
  -t "|" `
  -r "\n"

Notes:

• Using queryout gives you full control over the column order.
• This exports into a single file (orders.csv).
• If you want a header row, you typically have to add it yourself (BCP doesn't automatically emit headers for queryout).

1) Running the BCP command from PowerShell

2) BCP completion output (elapsed time and summary)

3) Verifying the CSV file generated

On this machine, the export completed in 62 seconds to export ~15 million rows with 9 columns from SQL Server into CSV file.

What is FastBCP?​

FastBCP is a command-line tool focused on fast exports from SQL Server to files, with a strong emphasis on parallelism.

For SQL Server → CSV exports, FastBCP is interesting because it can:

• export large tables efficiently
• split the export across multiple workers (parallel tiles)
• optionally keep the outputs separate files (or merge if needed)

Exporting orders to CSV files with FastBCP​

To build the command line, we used the FastBCP Wizard: https://fastbcp.arpe.io/docs/latest/wizard

Here is the exact command we used to export the 15 millions lines (in parallel) from SQL Server into 12 CSV files:

.\FastBCP.exe `
  --connectiontype "mssql" `
  --server "localhost,11433" `
  --trusted `
  --database "tpch10" `
  --sourceschema "dbo" `
  --sourcetable "orders" `
  --fileoutput "orders.csv" `
  --directory "D:\temp\tpch10\orders\csv" `
  --parallelmethod "Ntile" `
  --paralleldegree -2 `
  --distributekeycolumn "o_orderkey" `
  --merge false

A few things to highlight:

• --parallelmethod "Ntile" splits the table into multiple tiles for parallel export.
• --paralleldegree -2 uses number of cores / 2 workers. On this machine we have 24 cores, so -2 means 12 parallel workers.
• With Ntile + 12 workers, the export is naturally split into 12 partitions, and because --merge false, FastBCP writes 12 output CSV files (one per partition).
• --distributekeycolumn "o_orderkey" is a great key for partitioning TPC-H orders.
• --merge false keeps the output as multiple files (one per partition), which is often ideal for downstream parallel ingestion.

1) Running the FastBCP command from PowerShell

2) BCP completion output (elapsed time and summary)

3) Verifying the CSV files generated

On this machine, the export completed in 6.6 seconds, generating ~15 million rows (9 columns) from SQL Server split across 12 CSV files.

For more details on all available options (parallel methods, degrees, mapping, sources, output format), see the documentation: https://fastbcp.arpe.io/docs/latest/

Conclusion​

If you only need a quick export and you're fine with a traditional workflow, BCP is still a solid option.

But if you want fast exports on large tables and you want to take advantage of your CPU using parallelism, FastBCP is a compelling approach especially when exporting into multiple CSV files for downstream parallel processing.

Resources​

• FastBCP: https://fastbcp.arpe.io
• Docs: https://fastbcp.arpe.io/docs/latest/
• Wizard: https://fastbcp.arpe.io/docs/latest/wizard
• Microsoft BCP docs: https://learn.microsoft.com/fr-fr/sql/relational-databases/import-export/import-and-export-bulk-data-by-using-the-bcp-utility-sql-server?view=sql-server-ver17