Other Databases

Examples for exporting data from Clickhouse, SAP HANA, Netezza and Teradata databases.

Clickhouse

Export data from Clickhouse database.

Single-threaded export:

.\FastBCP.exe `
  --connectiontype "clickhouse" `
  --server "localhost" `
  --port "9000" `
  --database "default" `
  --user "default" `
  --password "password" `
  --query "SELECT * FROM orders LIMIT 1000" `
  --directory "C:\temp" `
  --fileoutput "clickhouse_orders.csv" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "None"

Parallel export with Random method:

.\FastBCP.exe `
  --connectiontype "clickhouse" `
  --server "localhost" `
  --port "9000" `
  --database "default" `
  --user "default" `
  --password "password" `
  --sourceschema "default" `
  --sourcetable "orders" `
  --directory "C:\temp" `
  --fileoutput "clickhouse_orders.csv" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "Random" `
  --distributekeycolumn "order_id" `
  --paralleldegree 8 `
  --merge "False"

SAP HANA

Export data from SAP HANA database using two connection types.

HANA Connection Type

Standard HANA connection for general exports.

Single-threaded export:

.\FastBCP.exe `
  --connectiontype "hana" `
  --server "hanaserver:30015" `
  --user "SYSTEM" `
  --password "password" `
  --query "SELECT * FROM ORDERS WHERE ORDER_DATE >= '2024-01-01'" `
  --directory "C:\temp" `
  --fileoutput "hana_orders.csv" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "None"

Parallel export with Random method:

.\FastBCP.exe `
  --connectiontype "hana" `
  --server "hanaserver:30015" `
  --user "SYSTEM" `
  --password "password" `
  --sourceschema "SYSTEM" `
  --sourcetable "ORDERS" `
  --directory "C:\temp" `
  --fileoutput "hana_orders.csv" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "Random" `
  --distributekeycolumn "ORDER_ID" `
  --paralleldegree 8 `
  --merge "False"

HANA Export Connection Type

Optimized HANA connection using native EXPORT functionality for faster bulk exports.

HANA Export

The hanaexport connection type uses SAP HANA's native EXPORT statement for optimized bulk data transfer. This is recommended for large datasets.

Single-threaded export:

.\FastBCP.exe `
  --connectiontype "hanaexport" `
  --server "hanaserver:30015" `
  --user "SYSTEM" `
  --password "password" `
  --query "SELECT * FROM ORDERS WHERE ORDER_DATE >= '2024-01-01'" `
  --directory "C:\temp" `
  --fileoutput "hana_orders.csv" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "None"

Parallel export with Random method:

.\FastBCP.exe `
  --connectiontype "hanaexport" `
  --server "hanaserver:30015" `
  --user "SYSTEM" `
  --password "password" `
  --sourceschema "SYSTEM" `
  --sourcetable "ORDERS" `
  --directory "C:\temp" `
  --fileoutput "hana_orders.csv" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "Random" `
  --distributekeycolumn "ORDER_ID" `
  --paralleldegree 8 `
  --merge "False"

Netezza

Export data from Netezza database using OLEDB provider.

Single-threaded export:

.\FastBCP.exe `
  --connectiontype "oledb" `
  --provider "NZOLEDB" `
  --server "netezza_server" `
  --database "SYSTEM" `
  --user "admin" `
  --password "password" `
  --query "SELECT * FROM orders" `
  --directory "C:\temp" `
  --fileoutput "netezza_orders.csv" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "None"

Parallel export with Random method:

.\FastBCP.exe `
  --connectiontype "oledb" `
  --provider "NZOLEDB" `
  --server "netezza_server" `
  --database "SYSTEM" `
  --user "admin" `
  --password "password" `
  --query "SELECT * FROM orders" `
  --directory "C:\temp" `
  --fileoutput "netezza_orders.csv" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "Random" `
  --distributekeycolumn "order_id" `
  --paralleldegree 6 `
  --merge "False"

Parallel export with DataDriven method:

.\FastBCP.exe `
  --connectiontype "oledb" `
  --provider "NZOLEDB" `
  --server "netezza_server" `
  --database "SYSTEM" `
  --user "admin" `
  --password "password" `
  --query "SELECT * FROM orders" `
  --directory "C:\temp" `
  --fileoutput "netezza_orders.csv" `
  --decimalseparator "." `
  --delimiter "|" `
  --dateformat "yyyy-MM-dd HH:mm:ss" `
  --encoding "UTF-8" `
  --parallelmethod "DataDriven" `
  --distributekeycolumn "region" `
  --paralleldegree 6 `
  --merge "False"

Parallel export with Physical method (native Netezza connection):

With a native Netezza connection type (nzsql, nzcopy, nzoledb, adbc_netezza), --parallelmethod Physical resolves to the Netezza data slices (formerly NZDataSlice, now deprecated). It does not work through the generic oledb connection type, because FastBCP cannot know which engine is behind the provider.

.\FastBCP.exe `
  --connectiontype "nzsql" `
  --server "netezza_server" `
  --database "SYSTEM" `
  --user "admin" `
  --password "password" `
  --sourceschema "ADMIN" `
  --sourcetable "ORDERS" `
  --directory "C:\temp" `
  --fileoutput "netezza_orders.parquet" `
  --parallelmethod "Physical" `
  --paralleldegree 8 `
  --merge "False"

Teradata

Export data from Teradata with the embedded .NET provider (teradata) or the embedded ArpeTeradata ADBC driver (adbc_teradata, Parquet output only).

note

--parallelmethod Physical is not available for Teradata. Use Ntile, RangeId, Random, DataDriven, Timepartition or DBPartition.

Single-threaded export (teradata):

.\FastBCP.exe `
  --connectiontype "teradata" `
  --server "td-host:443" `
  --database "TPCH" `
  --user "FastUser" `
  --password "FastPassword" `
  --query "SELECT * FROM TPCH.ORDERS" `
  --directory "C:\temp\teradata" `
  --fileoutput "teradata_orders.parquet" `
  --parallelmethod "None"

Parallel export with RangeId (teradata):

.\FastBCP.exe `
  --connectiontype "teradata" `
  --server "td-host:1025" `
  --database "TPCH" `
  --user "FastUser" `
  --password "FastPassword" `
  --query "SELECT * FROM TPCH.ORDERS" `
  --directory "C:\temp\teradata" `
  --fileoutput "teradata_orders.parquet" `
  --parallelmethod "RangeId" `
  --distributekeycolumn "o_orderkey" `
  --paralleldegree 16

Parallel export with Ntile (adbc_teradata):

.\FastBCP.exe `
  --connectiontype "adbc_teradata" `
  --server "td-host" `
  --database "TPCH" `
  --user "FastUser" `
  --password "FastPassword" `
  --sourceschema "TPCH" `
  --sourcetable "ORDERS" `
  --directory "C:\temp\teradata" `
  --fileoutput "teradata_orders.parquet" `
  --parallelmethod "Ntile" `
  --distributekeycolumn "o_orderkey" `
  --paralleldegree 8

The default adbc_teradata logon uses TLS and needs no Teradata client software. For the legacy-port logon (no TLS on the server, or SSL Mode=disable), see FASTBCP_TTU_PATH and Connection Parameters.

tip

Build your command with the Wizard