{"id":3572,"date":"2018-10-02T08:10:13","date_gmt":"2018-10-02T08:10:13","guid":{"rendered":"http:\/\/bicortex.com\/?p=3572"},"modified":"2019-12-20T11:15:00","modified_gmt":"2019-12-20T01:15:00","slug":"data-acquisition-framework-using-custom-python-wrapper-for-concurrent-bcp-utility-execution","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/data-acquisition-framework-using-custom-python-wrapper-for-concurrent-bcp-utility-execution\/","title":{"rendered":"Data Acquisition Framework Using Custom Python Wrapper For Concurrent BCP Utility Execution"},"content":{"rendered":"

Although building a data acquisition framework for a data warehouse isn\u2019t nearly or as interesting as doing analytics or data mining on the already well-structured data, it\u2019s an essential part of any data warehousing project. I have already written extensively on how one could build a robust data acquisition framework in THIS<\/a> series of blog posts, however, given the rapidly growing ecosystem of data sources and data stores, more and more projects tend to gravitate to the more application-agnostic storage formats e.g. text files or column-oriented files e.g. parquet. As such, direct data store connection may not be a possibility or the most optimal way of exporting data, making flat file export\/import option a good alternative.<\/p>\n

Microsoft offers a few different tools to facilitate flat files imports and exports, the most commonly used being bcp, BULK INSERT and their own ETL tool, capable of connecting to and reading flat files \u2013 SSIS. In this demo, I will analyse the difference in performance for data export and import between using pure T-SQL (executed as a series of concurrently running SQL Agent jobs) and parallelised bcp operations (using custom Python wrapper). Below image depicts high-level architecture of the solution with a short video clip demonstrating the actual code execution on a sample dataset at the end of this post. Let\u2019s compare the pair and see if any of them is better at moving data across the wire.<\/p>\n

\"\"<\/a><\/p>\n

Database and Schema Setup<\/h3>\n

Most of data warehouses come with some form of metadata layer so for this exercise I have \u2018scrapped\u2019 the source metadata e.g. attributes such as object names, data types, row counts etc. into a Sqlite local database. I also copied over partial target database metadata (with a bit more structure imposed) from one of the systems I\u2019m currently working with. This resulted in creating the following schema of tables containing key information on source and target objects, their attributes, their state etc.<\/p>\n

\"\"<\/a><\/p>\n

Target metadata is contained to three tables which allow for a three-layer, hierarchical approach to data acquisition:<\/p>\n