How to Export Azure Synapse Table to Local CSV using BCP?

It is common practice to offload large table to a flat file such as CSV, TSV or fixed width files. Many application stores data in the form of flat files such as CSV. Managing flat files such as CSV is easy and it is easy to transport using any electronic medium. In this article, we will check how to export Azure Synapse table to local CSV using BCP command?

In my other article, we have discussed how to export an Azure table to local CSV using SQLCMD command.

Azure Synapse Export Data using sqlcmd – Example

Export Azure Synapse Table to Local CSV using BCP Utility

The bulk copy program utility (bcp) bulk export data from an Azure Synapse dedicated SQL pool table into a user-specified flat file format such as CSV. The bcp utility can be used to export large tables from SQL Server or Azure synapse tables into a flat file.

How to Install BCP Utility?

The command-line tools are General Availability (GA). You can download same from following link

Download the latest version of bcp Utility

Follow the on-screen instructions to install the BCP utility. It will install SQLCMD and BCP.

BCP Utility Syntax to Export Data

Following is the syntax of BCP utility that you can use with Microsoft SQL Server and Azure Synapse Analytics data warehouse.

bcp "SQL Statements" queryout C:\path\file.csv -m 1 -F 1 -b 10 -a 4096 -e C:\path\error.log -S synapseserver.database.windows.net -q -d DATABASE  -U USERNAME -P PASSWORD -c -t ","

Execute following command on the command-line to check BCP utility options.

C:\Users\viths>bcp -help

Using BCP Utility to Export An Azure Synapse Table into Local CSV File

For a simplicity we are exporting already loaded TPCDS call_center table into CSV file.

Assuming that the table has been already loaded, following command will export table to a CSV flat file.

C:\Users\viths>bcp "select * from [TD].[call_center]" queryout C:\path\file.csv -m 1 -F 1 -b 10 -a 4096 -e C:\path\error.log -S synapseserver.database.windows.net -q -d testDB -U admin -P Password@123 -c -t ","

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Warning: BCP import with a format file will convert empty strings in delimited columns to NULL.

6 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 453    Average : (13.25 rows per sec.)

Sometimes, you may end up with BCP error such as “Cannot open server ‘sampleSynapse’ requested by the login. Client with IP address ‘100.200.97.87’ is not allowed to access the server.“. To resolve this issue, you must add your system IP to Azure synapse firewall IP list. Follow an official document.