BCP to quick move data between SQL and Data Warehouse

A common need from real scenario is to sync data between SQL(either SQL Server in private environment or SQL Database on cloud) and SQL Data Warehouse, given SQL Data Warehouse decoupled computing(engine for running SQL queries and SQL  requests) and storage(data storage and indexing), using SQL Data Warehouse lets users have option to stop computing anytime for saving cost and scale computing for faster querying, here it is typical practical tips for moving data between SQL  and data warehouse by BCP.

regarding BCP: https://msdn.microsoft.com/en-us/library/ms162802.aspx and https://www.microsoft.com/en-us/download/details.aspx?id=36433

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors]            [-f formatfile]          [-e errfile]
[-F firstrow]             [-L lastrow]             [-b batchsize]
[-n native type]          [-c character type]      [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier]  [-t field terminator]    [-r row terminator]
[-i inputfile]            [-o outfile]             [-a packetsize]
[-S server name]          [-U username]            [-P password]
[-T trusted connection]   [-v version]             [-R regional enable]
[-k keep null values]     [-E keep identity values]
[-h “load hints”]         [-x generate xml format file]
[-d database name]        [-K application intent]

# sql db –> local file
BCP DimDate2 out c:\Temp\test.txt -c -S servername.database.chinacloudapi.cn -d dbname -U username -P password
# local file –> sql db
bcp DimDate2 in C:\Temp\test.txt -S servername.database.chinacloudapi.cn -d dbname -U username -P password -q -c
# local file –> sql dw (SH)
bcp DimDate2 in C:\Temp\test.txt -S servername.database.chinacloudapi.cn -d DWdbname -U username -P password -q -c
# local file –> sql dw (BJ)
bcp DimDate2 in C:\Temp\test.txt -S servername.database.chinacloudapi.cn -d DWdbname -U username -P password -q -c
# sql dw –> local file
BCP DimDate2 out c:\Temp\test2.txt -c -S servername.database.chinacloudapi.cn -d DWdbname -U username -P password -q
BCP DimDate2 out c:\Temp\test2.txt -c -S servername.database.chinacloudapi.cn -d DWdbname -U username -P password -q
#BCP Queries
BCP  “select top 3 * from DimDate2” queryout c:\Temp\test.txt -c -S servername.database.chinacloudapi.cn -d username -U username -P password

sample result and performance:

SQL Azure(Shanghai) à local file

C:\WINDOWS\system32>BCP DimDate2 out c:\Temp\test.txt -c -S servername.database.chinacloudapi.cn -d dbname -U sqllogin -P password

Starting copy…

1000 rows successfully bulk-copied to host-file. Total received: 1000

1000 rows successfully bulk-copied to host-file. Total received: 2000

1000 rows successfully bulk-copied to host-file. Total received: 3000

….

 

294912 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 4547   Average : (64858.59 rows per sec.)

 

For Multi national companies, bcp can help export cloud data to local environment and then import to public cloud oversea. that is it.

Leave a Reply

Your email address will not be published. Required fields are marked *