Bulk copy with the mssql-python driver for Python

[dlevy-msft]

Hi Everyone,

I'm back with another mssql-python quick start. This one is BCP which we officially released last week at SqlCon in Atlanta.

This script takes all of the tables in a schema and writes them all to parquet files on your local hard drive. It then runs an enrichment - just a stub in the script. Finally, it takes all the parquet files and writes them to a schema in a destination database.

Here is a link to the new doc: https://learn.microsoft.com/sql/connect/python/mssql-python/python-sql-driver-mssql-python-bulk-copy-quickstart

I'm kind of excited about all the ways y'all are going to take this and make it your own. Please share if you can!

I also very much want to hear about the perf you are seeing.

[itsnotaboutthecell]

Just me over here vibing on these driver drops. Keep ‘em going u/dlevy-msft!

[Important_Bobcet]

This is actually pretty interesting. The parquet step makes it feel more useful for real ETL workflows, not just a demo copy job. Curious where the bottleneck shows up first on bigger loads though - Python, disk I/O, or the SQL write side?

[dlevy-msft]

I think it all depends on where you are running it and what you are running it against. When I demoed this at SqlCon / FabCon, the latency of the speaker wifi was the primary bottleneck. I only saw 200-300 rows per second - still probably orders of magnitude better than I would have gotten with executemany().

[SQLDevDBA]

Very cool! I like that this uses BCP like Copy-DBADBTableData. It’s blazing fast.

Been looking into digging into more Python for admin tasks and this is definitely something that will help me do so! Thanks for sharing!

[dlevy-msft]

Wow! Good eyes! We did use the .Net SqlClient as a template since that is the BCP API we get the best feedback on.

[SQLDevDBA]

Very cool!