It is common practice to offload large table to a flat file such as CSV, TSV or fixed width files. 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 load or import local flat file to Azure Synapse using the BCP command with some examples.
Load Local Flat File to Azure Synapse using BCP Utility
The bulk copy program utility (bcp) bulk copies data from a file in a user-specified format to an instance such as Azure Synapse dedicated SQL pool. The bcp utility can be used to import large numbers of new rows into SQL Server or Azure synapse tables.
How to Install BCP Utility?
The command-line tools are General Availability (GA). You can download same from following link
Follow the on-screen instructions to install the BCP utility. It will install SQLCMD and BCP.
BCP Utility Syntax
Following is the syntax of BCP utility that you can use with Microsoft SQL Server and Azure Synapse Analytics data warehouse.
bcp TABLE_NAME in C:\path\file.csv -S Azure_synapse_server -d DATABASE -U USERNAME -P PASSWORD -q -c -t "DELIMETER" -e C:\path\error.log -r "ROW_DELIMIETR"
Execute following command on the command-line to check BCP utility options.
C:\Users\viths>bcp -help
Using BCP Utility to Load CSV File into Azure Synapse Table
For a simplicity we are loading readily available TPCDS call_center.dat flat file into Azure Synapse.
Assuming table is already available, following command will load the CSV flat file into Azure Synapse analytics data warehouse.
bcp TD.call_center in C:\Users\viths\Desktop\temp\call_center.dat -S sampleSynapse.database.windows.net -d sampleDB -U admin -P Password@123 -q -c -t "|" -e C:\Users\viths\Desktop\temp\error.txt -r "0x0a"
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.
Following is the BCP output after successfully loading a flat file into an Azure Synapse table.
C:\Users\viths>bcp TD.call_center in C:\Users\viths\Desktop\temp\call_center.dat -S sampleSynapse.database.windows.net -d sampleDB -U admin -P Password@123 -q -c -t "|" -e C:\Users\viths\Desktop\temp\error.txt -r "0x0a"
Starting copy...
6 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1125 Average : (5.33 rows per sec.)
Related Articles,
- Azure Synapse Export Data using sqlcmd – Example
- How to Export Azure Synapse Table to Local CSV using BCP?
Hope this helps 🙂