I was posed with a challenge when trying to load XML files from Stack Exchange into a SQL Server database; some of these XML files are extremely large (largest one being a whopping 67 GB in a single XML file), so much so that the database just cannot handle them without throwing a
After some testing, I found out the optimal XML size for the database to handle efficiently (at least on my database) is ~20 MB. So I wrote this Python script to slice up any file larger than 20 MB into more-or-less equal files < 20 MB each.
It works pretty good, but my code looks very procedural, and since I am very inexperienced with Python I am not certain how it could be improved. I have added documentation throughout to make the script easy to follow, hopefully.
I am primarily interested in improving the performance, although all improvement suggestions are appreciated. Note that I did not use an XML module due to the very simple structure of these XML files, that is why I decided to just parse them as regular text. I used UTF-8 encoding for the output files to match the input.
The script prints this summary at the end:
Path: D:\Downloads\stackexchange\stackoverflow.com\Badges Size: 2016042158 bytes 17207172 total lines, split into 101 files = 170369 lines per input_file. Execution time: 0:01:16.416938
#!/usr/bin/python # -*- coding: utf-8 -*- import os import math from datetime import datetime __author__ = 'https://github.com/Phrancis' ''' The purpose of this script is to split up very large XML files from the Stack Exchange public data dump into small files which can then efficiently be loaded into a Microsoft SQL Server database for further processing. Testing with SQL Server on the author's database has proven that XML file sizes of ~20 MB have reasonable performance and processing time. Larger files have led to System.OutOfMemoryException on the database. The size of output files can be adjusted by changing the value of the SQL_SERVER_XML_SIZE_LIMIT variable. NOTE: This script was made using Python 3.5 and is not compatible with Python 2.x. This script assumes the format of the Stack Exchange XML files to be as following, and will not work correctly with differently-formatted XML. <?xml version="1.0" encoding="utf-8"?> <root_name> <row Id="1" ... /> <row Id="2" ... /> <row Id="3" ... /> ... <row Id="176685" ... /> </root_name> ''' # Clock to measure how long the script takes to execute start = datetime.now() SQL_SERVER_XML_SIZE_LIMIT = 20000000 # TODO Make this script iterate through all subdirectories instead of targeting just one file_path = 'D:\Downloads\stackexchange\stackoverflow.com\\' file_name = 'Badges' # get input_file size file_size_bytes = os.path.getsize(file_path + file_name + '.xml') # no splitting needed if XML input_file already fits within SQL Server limit if file_size_bytes <= SQL_SERVER_XML_SIZE_LIMIT: print('input_file size is less than 2 GB, no splitting needed.') print('Path:', file_path + file_name + '.xml') print('Size:', file_size_bytes, '/', SQL_SERVER_XML_SIZE_LIMIT) else: num_split_files_needed = math.ceil(file_size_bytes / SQL_SERVER_XML_SIZE_LIMIT) input_file = open(file_path + file_name + '.xml', 'r', encoding='utf-8') # get XML version, opening and closing root nodes, # and count the lines in order to determine how many lines to write to each split output input_file xml_ver = input_file.readline() root_open = input_file.readline() num_lines_in_file = 2 for line in input_file: root_close = line num_lines_in_file += 1 num_lines_per_split_file = math.ceil(num_lines_in_file / num_split_files_needed) # BEGIN SLICING OF INPUT FILE INTO SMALLER OUTPUT FILES # return stream to start of input_file input_file.seek(0) # skip top 2 lines of input_file as they contain the xml_var and root_open input_file.readline() input_file.readline() for current_file_num in range(1, num_split_files_needed + 1): with open(file_path + file_name + str(current_file_num) + '.xml', 'w+b') as output_file: print('Writing to:', file_path + file_name + str(current_file_num) + '.xml') # write XML header output_file.write(xml_ver.encode()) output_file.write(root_open.encode()) # start writing lines from the input to the output file output_line_num = 1 for line in input_file: # write lines until we reach the num_lines_per_split_file or the end of the input_file if output_line_num <= num_lines_per_split_file and line != root_close: output_file.write(line.encode()) output_line_num += 1 else: break # write the footer as the last line in the file output_file.write(root_close.encode()) # move on to the next output file current_file_num += 1 # Clean up and print results input_file.close() print('Path:', file_path + file_name) print('Size:', file_size_bytes, 'bytes') print(num_lines_in_file, 'total lines, split into', num_split_files_needed, 'files =', num_lines_per_split_file, 'lines per input_file.') print('Execution time:', datetime.now() - start)